一、Mybatis的多对多关系的mapper映射
(一个用户具有多个角色,一个角色可以被多个用户拥有)
1. 建表sql语句
CREATE TABLE `sys_role` ( `id` bigint ( 20 ) NOT NULL AUTO_INCREMENT, `roleName` varchar ( 50 ) DEFAULT NULL, `roleDesc` varchar ( 50 ) DEFAULT NULL, PRIMARY KEY ( `id`)
) ; insert into `sys_role`( `id`, `roleName`, `roleDesc`) values ( 1 , '院长' , '负责全面工作' ) , ( 2 , '研究员' , '课程研发工作' ) , ( 3 , '讲师' , '授课工作' ) , ( 4 , '助教' , '协助解决学生的问题') ; DROP TABLE IF EXISTS `sys_user`; CREATE TABLE `sys_user` ( `id` bigint ( 20 ) NOT NULL AUTO_INCREMENT, `username` varchar ( 50 ) DEFAULT NULL, `email` varchar ( 50 ) DEFAULT NULL, `password` varchar ( 80 ) DEFAULT NULL, `phoneNum` varchar ( 20 ) DEFAULT NULL, PRIMARY KEY ( `id`)
) ; insert into `sys_user`( `id`, `username`, `email`, `password`, `phoneNum`) values ( 1 , 'zhangsan',' zhangsan@itcast.cn ',' 123 ',' 13888888888 '),(2,' lisi',' lisi@itcast.cn ',' 123 ',' 13999999999 '),(3,' wangwu',' wangwu@itcast.cn ',' 123 ',' 18599999999 ') ; DROP TABLE IF EXISTS `sys_user_role`; CREATE TABLE `sys_user_role` ( `userId` bigint ( 20 ) NOT NULL, `roleId` bigint ( 20 ) NOT NULL, PRIMARY KEY ( `userId`, `roleId`) , KEY `roleId` ( `roleId`) , CONSTRAINT `sys_user_role_ibfk_1` FOREIGN KEY ( `userId`) REFERENCES `sys_user` ( `id`) , CONSTRAINT `sys_user_role_ibfk_2` FOREIGN KEY ( `roleId`) REFERENCES `sys_role` ( `id`)
) ; insert into `sys_user_role`( `userId`, `roleId`) values ( 1 , 1 ) , ( 1 , 2 ) , ( 2 , 2 ) , ( 2 , 3 ) ;
2. 构建相应的User类和Role类
package com. bean ; import java. util. List ; public class User { private Long id; private String username; private String email; private String password; private String phoneNum; private List < Role > roles; public Long getId ( ) { return id; } public void setId ( Long id) { this . id = id; } public String getUsername ( ) { return username; } public void setUsername ( String username) { this . username = username; } public String getEmail ( ) { return email; } public void setEmail ( String email) { this . email = email; } public String getPassword ( ) { return password; } public void setPassword ( String password) { this . password = password; } public String getPhoneNum ( ) { return phoneNum; } public void setPhoneNum ( String phoneNum) { this . phoneNum = phoneNum; } public List < Role > getRoles ( ) { return roles; } public void setRoles ( List < Role > roles) { this . roles = roles; } @Override public String toString ( ) { return "User{" + "id=" + id + ", username='" + username + '\'' + ", email='" + email + '\'' + ", password='" + password + '\'' + ", phoneNum='" + phoneNum + '\'' + ", rolist='" + roles + '\'' + '}' ; }
}
package com. bean ;
public class Role { private Long id; private String roleName; private String roleDesc; public Long getId ( ) { return id; } public void setId ( Long id) { this . id = id; } public String getRoleName ( ) { return roleName; } public void setRoleName ( String roleName) { this . roleName = roleName; } public String getRoleDesc ( ) { return roleDesc; } public void setRoleDesc ( String roleDesc) { this . roleDesc = roleDesc; } @Override public String toString ( ) { return "Role{" + "id=" + id + ", roleName='" + roleName + '\'' + ", roleDesc='" + roleDesc + '\'' + '}' ; }
}
3. 编写UserMapper接口
package com. mapper ; import com. bean. User ; import java. io. IOException ;
import java. util. List ; public interface UserMapper { List < User > findUserAndRoleAll ( ) throws IOException ; }
4. 编写UserMapper.xml文件
< ? xml version= "1.0" encoding= "UTF-8" ? >
< ! DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
< mapper namespace= "com.mapper.UserMapper" > < resultMap id= "UserRoleMap" type= "user" > < ! -- user的信息-- > < id column= "userId" property= "id" > < / id> < result column= "username" property= "username" > < / result> < result column= "email" property= "email" > < / result> < result column= "password" property= "password" > < / result> < result column= "phoneNum" property= "phoneNum" > < / result> < ! -- 内部的role信息-- > < collection property= "roles" ofType= "role" > < id column= "roleId" property= "id" > < / id> < result column= "roleName" property= "roleName" > < / result> < result column= "roleDesc" property= "roleDesc" > < / result> < / collection> < / resultMap> < select id= "findUserAndRoleAll" resultMap= "UserRoleMap" > SELECT * FROM sys_user u, sys_user_role ur, sys_role r WHERE u. id= ur. userId and ur. roleId= r. id< / select> < / mapper>
5. 编写测试类
package com ; import com. bean. User ;
import com. mapper. UserMapper ;
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. Test ;
import java. io. IOException ;
import java. io. InputStream ;
import java. util. List ;
public class TestDemo { @Test public void test ( ) throws IOException { InputStream resourceAsStream = Resources . getResourceAsStream ( "SqlMapConfig.xml" ) ; SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder ( ) . build ( resourceAsStream) ; SqlSession session = sessionFactory. openSession ( true ) ; UserMapper userMapper = session. getMapper ( UserMapper . class ) ; List < User > all = userMapper. findUserAndRoleAll ( ) ; for ( User user : all) { System . out. println ( user) ; } } }
二、Mybatis的多对多关系的注解开发
常用注解:@Result(实现结果集封装),@Results(与Result一起使用,封装多个结果集),@One(实现一对一结果集封装),@Many(实现一对多结果集封装)
1. 删除UserMapper.xml文件,修改SqlMapConfig.xml文件如下
< ! -- 指定映射关系 -- > < mappers> < ! -- 指定接口所在的包-- > < package name = "com.mapper" > < / package > < / mappers>
2. 创建UserMapper.java和RoleMapper.java接口
package com. mapper ;
import com. bean. User ;
import org. apache. ibatis. annotations. Many ;
import org. apache. ibatis. annotations. Result ;
import org. apache. ibatis. annotations. Results ;
import org. apache. ibatis. annotations. Select ;
import java. io. IOException ;
import java. util. List ;
public interface UserMapper { @Select ( "select * from sys_user" ) @Results ( { @Result ( id = true , column = "id" , property = "id" ) , @Result ( id = true , column = "username" , property = "username" ) , @Result ( id = true , column = "email" , property = "email" ) , @Result ( id = true , column = "password" , property = "password" ) , @Result ( id = true , column = "phoneNum" , property = "phoneNum" ) , @Result ( javaType = List . class , property = "roles" , column = "id" , many = @Many ( select = "com.mapper.RoleMapper.findByUserId" ) ) } ) List < User > findUserAndRoleAll ( ) throws IOException ;
}
package com. mapper ;
import com. bean. Role ;
import org. apache. ibatis. annotations. Select ;
import java. util. List ;
public interface RoleMapper { @Select ( "select * from sys_user_role ur,sys_role r where ur.roleId = r.id and ur.userId = #{uid}" ) List < Role > findByUserId ( int uid) ;
}
3. 编写测试代码
package com ;
import com. bean. User ;
import com. mapper. UserMapper ;
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. Before ;
import org. junit. Test ;
import java. io. IOException ;
import java. io. InputStream ;
import java. util. List ; public class TestDemo { private UserMapper userMapper; @Before public void before ( ) throws IOException { InputStream resourceAsStream = Resources . getResourceAsStream ( "SqlMapConfig.xml" ) ; SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder ( ) . build ( resourceAsStream) ; SqlSession session = sessionFactory. openSession ( true ) ; userMapper = session. getMapper ( UserMapper . class ) ; } @Test public void test ( ) throws IOException { List < User > all = userMapper. findUserAndRoleAll ( ) ; for ( User user : all) { System . out. println ( user) ; } } }
4. 测试结果如下