一、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 mapper
PUBLIC "-//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. 测试结果如下