本文将介绍简单的select查询
整个工程结构如下:
1. 需求,一个简单的权限控制需求:用户-角色-权限,表关系如下图所示
建表语句
CREATE TABLE sys_user( id BIGINT AUTO_INCREMENT COMMENT '用户ID', user_name VARCHAR(50) COMMENT '用户名', user_password VARCHAR(50) COMMENT '密码', user_email VARCHAR(50) COMMENT '邮箱', user_info TEXT COMMENT '简介', head_img BLOB COMMENT '头像', create_time DATETIME COMMENT '创建时间', PRIMARY KEY(id) )DEFAULT CHARSET=utf8; ALTER TABLE sys_user COMMENT '用户表'; CREATE TABLE sys_role( id BIGINT AUTO_INCREMENT COMMENT '角色ID', role_name VARCHAR(50) COMMENT '角色名', enabled INT COMMENT '有效标志', create_by BIGINT COMMENT '创建人', create_time DATETIME COMMENT '创建时间', PRIMARY KEY(id) )DEFAULT CHARSET=utf8; ALTER TABLE sys_role COMMENT '角色表'; CREATE TABLE sys_privilege( id BIGINT AUTO_INCREMENT COMMENT '权限ID', privilege_name VARCHAR(50) COMMENT '权限名称', privilege_url INT COMMENT '权限URL', PRIMARY KEY(id) )DEFAULT CHARSET=utf8; ALTER TABLE sys_role COMMENT '权限表'; CREATE TABLE sys_user_role( user_id BIGINT COMMENT '用户ID', role_id BIGINT COMMENT '角色ID' )DEFAULT CHARSET=utf8; ALTER TABLE sys_user_role COMMENT '用户角色关联表'; CREATE TABLE sys_role_privilege( role_id BIGINT COMMENT '角色ID', privilege_id BIGINT COMMENT '权限ID' )DEFAULT CHARSET=utf8; ALTER TABLE sys_role_privilege COMMENT '角色权限关联表'; insert into `sys_user` values('1','admin','123456','admin@mybatis.tk','管理员',null,'2018-04-24 17:08:34'); insert into `sys_user` values('1001','test','123456','test@mybatis.tk','测试用户',null,'2018-04-24 17:08:34'); insert into `sys_role` values('1','管理员','1','1','2018-04-24 14:34:34'); INSERT INTO `sys_role` VALUES('2','普通用户','1','1','2018-04-24 15:08:34'); insert into `sys_user_role` values('1','1'); insert into `sys_user_role` values('1','2'); insert into `sys_user_role` values('1001','2'); insert into `sys_privilege` values('1','用户管理','/users'); insert into `sys_privilege` values('2','角色管理','/roles'); insert into `sys_privilege` values('3','系统日志','/logs'); insert into `sys_privilege` values('4','人员维护','/persons'); insert into `sys_privilege` values('5','单位维护','/companies'); insert into `sys_role_privilege` values('1','1'); insert into `sys_role_privilege` values('1','3'); insert into `sys_role_privilege` values('1','2'); insert into `sys_role_privilege` values('2','4'); insert into `sys_role_privilege` values('2','5'); #添加外键约束 ALTER TABLE sys_user_role ADD FOREIGN KEY(user_id) REFERENCES sys_user(id) ; ALTER TABLE sys_user_role ADD FOREIGN KEY(role_id) REFERENCES sys_role(id) ; ALTER TABLE sys_role_privilege ADD FOREIGN KEY(role_id) REFERENCES sys_role(id) ; ALTER TABLE sys_role_privilege ADD FOREIGN KEY(privilege_id) REFERENCES sys_privilege(id) ;
实体类创建
SysUser、SysRole、Sysprivilege、SysUserRole、SysRolePrivilege代码如下:
package tk.mybatis.simple.model; import java.util.Date; /** * 用户表 * 命名方式:下划线转驼峰 * 具体采用什么方式不重要, * 可以通过resultMap对数据库的列和类的字段配置映射关系 */ public class SysUser { /** * 用户ID * 由于java中的基本类型有默认值,例如当某个类中存在 private int age 字段时, * 创建这个类时,age会有默认值0,当使用age属性时,它总会有值。因此在某些情况下, * 便无法实现使age为null。并且在动态SQL的部分,如果使用age!=null进行判断, * 结果总会为true,因而会导致很多隐藏的问题 * * 所以,在实体类中不要使用基本数据类型 */ private Long id; /** * 用户名 */ private String userName; /** * 用户密码 */ private String userPassword; /** * 用户邮箱 */ private String userEmail; /** * 简介 */ private String userInfo; /** * 头像 * 在MyBatis中, 关于数据库字符按和java类型的对应关系,不需要可以去记, * 但是需要注意一个特殊类型 "byte[]",这个类型一般对应数据库中的BLOB、 * LONGVARBINARY以及一些和二进制流有关的字段类型。 */ private Byte[] headImg; /** * 创建时间 */ private Date createTime; //省去了get和set方法 }
public class SysRole { private Long id; private String roleName; private Integer enabled; private Long creatBy; private Date createTime; //省去了get和set方法 }
public class Sysprivilege { /** * 权限ID */ private Long id; /** * 权限名称 */ private String privilegeName; /** * 权限URL */ private String privilegeUrl; //省去了get和set方法 }
/** * 用户角色表 */ public class SysUserRole { /** * 用户ID */ private Long userId; /** * 角色ID */ private Long roleId; //省去了get和set方法 }
public class SysRolePrivilege { private Long roleId; private Long privilegeId; //省去了get和set方法 }
xml配置方式
在src/main/resources目录下的tk/mybatis/simple/mapper目录下 创建UserMapper.xml,RoleMapper.xml,PrivilegeMapper.xml,UserRoleMapper.xml,RolePrivilegeMapper.xml。
然后再src/main/java目录下的tk.mybatis.simple.mapper包下面创建UserMapper.java, RoleMapper.java,PrivilegeMapper.java,UserRoleMapper.java,RolePrivilegeMapper.java
这里仅仅以UserMapper.xml和UserMapper.java为例说明:
UserMapper.xml
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <!-- 注意:namespace属性 当xml文件和接口关联的时候,namespace属性必须写接口的全限定名 mybatis内部就是通过这个namespace属性将两者关联起来的 映射XML和接口的命名需要符合如下规则: 1. 当只使用XML而不使用接口的时候,namespace的值可以设置为任意不重复的名称 2. 标签id属性值在任何时候都不能出现英文句号".",并且同一个命名空间下不能出现重复的id 3. 因为接口中方法可以重载,所以接口中可以出现多个同名不同参数的方法,但是XML中的id不能重复 因而接口中的所有的同名方法会对应着XML中的同一个id的方法。 最常见的用法就是:同名方法中其中一个方法增杰一个RowBunds类型的参数用于实现分页查询 --> <mapper namespace="tk.mybatis.simple.mapper.UserMapper"> </mapper>
再在mybatis-config.xml文件中引入这五个XML配置文件 (这种配置比较繁琐,因为每次添加新的配置文件,都要重新在这里添加可以配置成扫描包的方式)
<mappers> <mapper resource="tk/mybatis/simple/mapper/CountryMapper.xml"/> <mapper resource="tk/mybatis/simple/mapper/UserMapper.xml"/> <mapper resource="tk/mybatis/simple/mapper/RoleMapper.xml"/> <mapper resource="tk/mybatis/simple/mapper/PrivilegeMapper.xml"/> <mapper resource="tk/mybatis/simple/mapper/UserRoleMapper.xml"/> <mapper resource="tk/mybatis/simple/mapper/RolePrivilegeMapper.xml"/> </mappers>
替代上面的配置如下:(两个方式可以共存)
<mappers> <mapper resource="tk/mybatis/simple/mapper/CountryMapper.xml"/> <package name="tk.mybatis.simple.mapper"/> </mappers>
配置为扫描包的方式添加mapper.xml配置文件,循环对接口进行如下操作 1. 判断接口对应的命名空间是否已经存在,如果存在就抛出异常, 不存在就继续进行接下来的操作 2. 加载接口对应的XML映射文件,将接口全限定名转换为路径,例如: 将接口tk.mybatis.simple.mapper.UserMapper 转换为 tk/mybatis/simple/mapper/UserMapper.xml,以.xml为后缀搜索 XML资源,如果找到就解析XML 3. 处理接口中的注解方法 因为这里的接口和XML映射文件完全符合上面操作的第2点,因此直接配置包名就能 自动扫描包下的接口和XML映射文件,省去了很多麻烦
- select的用法一
在UserMapper.java中添加如下方法:
/** * 通过id查询用户 * 该返回值类型必须和配置文件中的返回值类型一致,否则报错 * @param id * @return */ SysUser selectById(Long id); /** * 查询全部用户 * @return */ List<SysUser> selectAll();
UserMapper.xml配置文件中添加如下配置:
<!-- resultMap:用于配置java对象的属性和查询列的对应关系,必须熟练掌握 标签id:必填,唯一 type: 必填,用于配置java对象的属性和查询列的对应关系 另外两个没有写的属性: extends="" :选填,可以配置当前resultMap继承自其他的resultMap,属性值为继承resultMap的id autoMapping="":选填,可选值为true或false,用于配置是否启动非映射字段(没有在resultMap中配置的字段) 的自动映射功能,该配置可以覆盖全局的autoMappingBehavior配置 --> <resultMap id="userMap" type="tk.mybatis.simple.model.SysUser" autoMapping="false"> <!-- <constructor> <idArg></idArg> <arg ></arg> </constructor> constructor标签:配置使用构造方法注入结果,包含idArg和arg两个标签 idArg:id参数,标记结果作为id(唯一值),可以帮助提高整体性能 arg:注入到构造方法的一个普通结果 --> <!-- id参数,代表的主键值(唯一值) column:从数据库中得到的列名,或者是列的别名 property:映射到列结果的属性,可以映射简单的,如"username"这样的属性 也可以映射复杂的属性,例如"address.street.number", 通过"."的方式的属性嵌套赋值 javaType:一个java类的全限定名,或者一个类型别名 如果映射到JavaBean,MyBatis通常可以自动判断属性类型 如果映射到HashMap,则需要明确指定javaType属性 jdbcType:列对应的数据库类型(针对插入,更新,删除操作可能为空的列进行) 这是JDBC jdbcType的需要,而不是MyBatis的需要 --> <id property="id" column="id"/> <result property="userName" column="user_name"/> <result property="userPassword" column="user_password"/> <result property="userEmail" column="user_email"/> <result property="headImg" column="head_img" jdbcType="BLOB"/> <result property="createTime" column="create_time" jdbcType="TIMESTAMP"/> </resultMap> <!-- selectById:通过id查询用户 select:映射查询语句使用的标签, 此处的id必须和接口中方法的名字一样 如果接口方法没有和XML中的id属性值对应,程序启动便会报错。 resultMap:是上面定义的resultMap标签里面的id属性值,通过id引用需要的resultMap /* 查询语句 #{id}:MyBatis SQL中使用的预编译参数的一种方式,id是传入的参数名 */ --> <select id="selectById" resultMap="userMap"> SELECT * FROM sys_user WHERE id = #{id} </select> <!-- 这里通过设置别名的方式来和java属性值匹配 实际上:别名的大小写和实际java属性的大小写并不敏感 因为Mybatis会将两者都转换成大写做比较 但是书写还是要规范 --> <select id="selectAll" resultType="tk.mybatis.simple.model.SysUser"> SELECT id, user_name userName, user_password userPassword, user_email userEmai, user_info userInfo, head_Img headImg, create_time createTime FROM sys_user </select>
第二个select语句,也可以开启驼峰映射,在mybatis-config.xml文件中配置如下:
<settings> <!-- 开启驼峰映射 --> <setting name="mapUnderscoreToCamelCase" value="true"/> </settings>
这样第二个select语句可以写为如下格式:
<select id="selectAll" resultType="tk.mybatis.simple.model.SysUser"> SELECT id, user_name, user_password, user_email, user_info, head_Img, create_time FROM sys_user </select>
测试代码:
package tk.mybatis.simple.mapper; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import org.junit.BeforeClass; import java.io.IOException; import java.io.InputStream; import java.io.Reader; /** * 基础测试类 * @author snow */ public class BaseMapperTest { private static SqlSessionFactory sqlSessionFactory; @BeforeClass public static void init(){ try { InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml"); sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); inputStream.close(); } catch (IOException e) { e.printStackTrace(); } } public SqlSession getSqlSession(){ return sqlSessionFactory.openSession(); } }
修改CountryMapperTest为如下代码:
package tk.mybatis.simple.mapper; import org.apache.ibatis.session.SqlSession; import org.junit.Test; import tk.mybatis.simple.model.Country; import java.util.List; /** * @author snow */ public class CountryMapperTest extends BaseMapperTest{ @Test public void testSelect(){ //创建session SqlSession sqlSession = getSqlSession(); System.out.println(sqlSession); try { List<Country> countryList = sqlSession.selectList("tk.mybatis.simple.mapper.CountryMapper.selectAll"); printCountryList(countryList); }finally { //千万不要忘记关闭sqlSession sqlSession.close(); } } private static void printCountryList(List<Country> countrylist){ countrylist.forEach((country)->{ System.out.printf("%-4d%4s%4s\n",country.getId(),country.getCountryname(),country.getCountrycode()); }); } } /*--------------------------------------------------------------------------- 测试结果: [DEBUG] 2018-04-25 21:27:56,538 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159) ==> Preparing: SELECT id, countryname, countrycode from country [DEBUG] 2018-04-25 21:27:56,614 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159) ==> Parameters: [TRACE] 2018-04-25 21:27:56,666 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.trace(BaseJdbcLogger.java:165) <== Columns: id, countryname, countrycode [TRACE] 2018-04-25 21:27:56,667 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.trace(BaseJdbcLogger.java:165) <== Row: 1, 中国, CN [TRACE] 2018-04-25 21:27:56,669 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.trace(BaseJdbcLogger.java:165) <== Row: 2, 美国, US [TRACE] 2018-04-25 21:27:56,669 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.trace(BaseJdbcLogger.java:165) <== Row: 3, 俄罗斯, RU [TRACE] 2018-04-25 21:27:56,670 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.trace(BaseJdbcLogger.java:165) <== Row: 4, 英国, GB [TRACE] 2018-04-25 21:27:56,670 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.trace(BaseJdbcLogger.java:165) <== Row: 5, 法国, FR [DEBUG] 2018-04-25 21:27:56,670 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159) <== Total: 5 1 中国 CN 2 美国 US 3 俄罗斯 RU 4 英国 GB 5 法国 FR ----------------------------------------------------------------------------------*/
package tk.mybatis.simple.mapper; import org.apache.ibatis.session.SqlSession; import org.junit.Assert; import org.junit.Test; import tk.mybatis.simple.model.SysRole; import tk.mybatis.simple.model.SysUser; import java.util.List; import static org.junit.Assert.*; public class UserMapperTest extends BaseMapperTest{ @Test public void testSelectById() { SqlSession sqlSession = getSqlSession(); try { UserMapper userMapper = sqlSession.getMapper(UserMapper.class); SysUser sysUser = userMapper.selectById(1L); //sysUser不为空 Assert.assertNotNull(sysUser); //用户名为admin Assert.assertEquals("admin",sysUser.getUserName()); }finally { sqlSession.close(); } } /*---------------------------------------------------------------------------------- 测试结果: [DEBUG] 2018-04-25 21:30:03,057 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159) ==> Preparing: SELECT * FROM sys_user WHERE id = ? [DEBUG] 2018-04-25 21:30:03,107 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159) ==> Parameters: 1(Long) [TRACE] 2018-04-25 21:30:03,153 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.trace(BaseJdbcLogger.java:165) <== Columns: id, user_name, user_password, user_email, user_info, head_img, create_time [TRACE] 2018-04-25 21:30:03,153 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.trace(BaseJdbcLogger.java:165) <== Row: 1, admin, 123456, admin@mybatis.tk, <<BLOB>>, <<BLOB>>, 2018-04-24 17:08:34.0 [DEBUG] 2018-04-25 21:30:03,159 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159) <== Total: 1 ----------------------------------------------------------------------------------*/ @Test public void testSelectAll() { SqlSession sqlSession = getSqlSession(); try { UserMapper userMapper = sqlSession.getMapper(UserMapper.class); List<SysUser> list = userMapper.selectAll(); //sysUser不为空 Assert.assertNotNull(list); //用户名为admin Assert.assertTrue(list.size()>0); }finally { sqlSession.close(); } } } /*---------------------------------------------------------------------------------- 测试结果: [DEBUG] 2018-04-25 21:30:55,468 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159) ==> Preparing: SELECT id, user_name userName, user_password userPassword, user_email userEmai, user_info userInfo, head_Img headImg, create_time createTime FROM sys_user [DEBUG] 2018-04-25 21:30:55,524 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159) ==> Parameters: [TRACE] 2018-04-25 21:30:55,894 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.trace(BaseJdbcLogger.java:165) <== Columns: id, userName, userPassword, userEmai, userInfo, headImg, createTime [TRACE] 2018-04-25 21:30:55,896 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.trace(BaseJdbcLogger.java:165) <== Row: 1, admin, 123456, admin@mybatis.tk, <<BLOB>>, <<BLOB>>, 2018-04-24 17:08:34.0 [TRACE] 2018-04-25 21:30:55,905 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.trace(BaseJdbcLogger.java:165) <== Row: 1001, test, 123456, test@mybatis.tk, <<BLOB>>, <<BLOB>>, 2018-04-24 17:08:34.0 [DEBUG] 2018-04-25 21:30:55,908 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159) <== Total: 2 ----------------------------------------------------------------------------------*/
select用法二(复杂查询:根据用户id来获取对应的角色信息,要求查询出来角色的同时,将用户的用户名和邮箱查询出来)
SysRole.java修改如下:
/** * 增加一个字段,提供set和get方法 */ private SysUser user;
UserMapper.java里面添加如下方法:
/** * 根据用户id来获取对应的角色信息 * @param id * @return */ List<SysRole> selectRolesByUserId(Long id);
UserMapper.xml添加如下配置:
需要注意的是:这里通过user.userName就可以给实体类SysRole里面的user属性的userName属性赋值成功
<select id="selectRolesByUserId" resultType="tk.mybatis.simple.model.SysRole"> SELECT r.id, r.role_name roleName, r.enabled, r.create_by createBy, r.create_time createTime, u.user_name AS "user.userName", u.user_email AS "user.userEmail" FROM sys_user u INNER JOIN sys_user_role ur ON u.id = ur.user_id INNER JOIN sys_role r ON ur.role_id = r.id where u.id = #{userId} </select>
测试方法代码添加如下:
@Test public void testSelectRolesByUserId() { SqlSession sqlSession = getSqlSession(); try { UserMapper userMapper = sqlSession.getMapper(UserMapper.class); List<SysRole> list = userMapper.selectRolesByUserId(1L); //sysUser不为空 Assert.assertNotNull(list); //用户名为admin Assert.assertTrue(list.size()>0); }finally { sqlSession.close(); } } /*---------------------------------------------------------------------------------- [DEBUG] 2018-04-25 22:00:12,678 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159) ==> Preparing: SELECT r.id, r.role_name roleName, r.enabled, r.create_by createBy, r.create_time createTime, u.user_name AS "user.userName", u.user_email AS "user.userEmail" FROM sys_user u INNER JOIN sys_user_role ur ON u.id = ur.user_id INNER JOIN sys_role r ON ur.role_id = r.id where u.id = ? [DEBUG] 2018-04-25 22:00:12,742 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159) ==> Parameters: 1(Long) [TRACE] 2018-04-25 22:00:12,811 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.trace(BaseJdbcLogger.java:165) <== Columns: id, roleName, enabled, createBy, createTime, user.userName, user.userEmail [TRACE] 2018-04-25 22:00:12,812 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.trace(BaseJdbcLogger.java:165) <== Row: 1, 管理员, 1, 1, 2018-04-24 14:34:34.0, admin, admin@mybatis.tk [TRACE] 2018-04-25 22:00:12,812 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.trace(BaseJdbcLogger.java:165) <== Row: 2, 普通用户, 1, 1, 2018-04-24 15:08:34.0, admin, admin@mybatis.tk [DEBUG] 2018-04-25 22:00:12,812 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159) <== Total: 2 ----------------------------------------------------------------------------------*/