Shiro学习(三) Shiro JDBCRealm

1、创建三张表 users/user_roles/roles_permissions 并插入数据

#创建users表
CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `password` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

#插入数据
INSERT INTO `test`.`users`(`id`, `username`, `password`) VALUES (1, 'zjc', '123456');

#创建user_roles表
CREATE TABLE `user_roles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `role_name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

#插入数据
INSERT INTO `test`.`user_roles`(`id`, `username`, `role_name`) VALUES (1, 'zjc', 'admin');

#创建roles_permissions 表
CREATE TABLE `roles_permissions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `role_name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `permission` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

#插入数据
INSERT INTO `test`.`roles_permissions`(`id`, `role_name`, `permission`) VALUES (1, 'admin', 'user:select');

2、创建JdbcRealmTest类

package com.zjc.test;


import com.alibaba.druid.pool.DruidDataSource;
import org.apache.shiro.SecurityUtils;
import org.apache.shiro.authc.UsernamePasswordToken;
import org.apache.shiro.mgt.DefaultSecurityManager;
import org.apache.shiro.realm.jdbc.JdbcRealm;
import org.apache.shiro.subject.Subject;
import org.junit.Test;

public class JdbcRealmTest {

    DruidDataSource dataSource = new DruidDataSource();

    {
        dataSource.setUrl("jdbc:mysql://localhost:3306/test?serverTimezone=GMT&characterEncoding=utf8");
        dataSource.setUsername("root");
        dataSource.setPassword("123456");
    }

    @Test
    public void jdbcRealmTest(){
        DefaultSecurityManager securityManager = new DefaultSecurityManager();

        JdbcRealm jdbcRealm = new JdbcRealm();

        jdbcRealm.setDataSource(dataSource);

        //注意 需要设置角色权限的开关 默认为false
        jdbcRealm.setPermissionsLookupEnabled(true);

        securityManager.setRealm(jdbcRealm);

        SecurityUtils.setSecurityManager(securityManager);

        Subject subject = SecurityUtils.getSubject();

        UsernamePasswordToken token = new UsernamePasswordToken("zjc","123456");

        subject.login(token);

        System.out.println("isAuthenticated:"+subject.isAuthenticated());

        subject.checkRole("admin");

        subject.checkPermission("user:select");
    }
}

3、使用自己创建的表 进行操作

3-1、创建test_user和test_user_roles表

CREATE TABLE `test_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `password` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `test`.`test_user`(`id`, `user_name`, `password`) VALUES (1, 'xxm', '123456');


CREATE TABLE `test_user_roles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `role_name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `test`.`test_user_roles`(`id`, `user_name`, `role_name`) VALUES (1, 'xxm', 'user');

3-3、使用自己的sql语句进行查询

package com.zjc.test;


import com.alibaba.druid.pool.DruidDataSource;
import org.apache.shiro.SecurityUtils;
import org.apache.shiro.authc.UsernamePasswordToken;
import org.apache.shiro.mgt.DefaultSecurityManager;
import org.apache.shiro.realm.jdbc.JdbcRealm;
import org.apache.shiro.subject.Subject;
import org.junit.Test;

public class JdbcRealmTest {

    DruidDataSource dataSource = new DruidDataSource();

    {
        dataSource.setUrl("jdbc:mysql://localhost:3306/test?serverTimezone=GMT&characterEncoding=utf8");
        dataSource.setUsername("root");
        dataSource.setPassword("123456");
    }

    @Test
    public void jdbcRealmTest(){
        DefaultSecurityManager securityManager = new DefaultSecurityManager();

        JdbcRealm jdbcRealm = new JdbcRealm();

        jdbcRealm.setDataSource(dataSource);

        //注意 需要设置角色权限的开关 默认为false
        jdbcRealm.setPermissionsLookupEnabled(true);

        securityManager.setRealm(jdbcRealm);

        String sql = "select password from test_user where user_name = ? ";

        String roleSql = "select role_name from test_user_roles where user_name = ?";

        jdbcRealm.setAuthenticationQuery(sql);

        jdbcRealm.setUserRolesQuery(roleSql);

        SecurityUtils.setSecurityManager(securityManager);

        Subject subject = SecurityUtils.getSubject();

        UsernamePasswordToken token = new UsernamePasswordToken("xxm","123456");

        subject.login(token);

        System.out.println("isAuthenticated:"+subject.isAuthenticated());

        subject.checkRole("user");

    }
}