mybatis中的多表查询:
示例:用户和角色
一个用户可以有多个角色
一个角色可以赋予多个用户
步骤:
1、建立两张表:用户表,角色表
让用户表和角色表具有多对多的关系。需要使用中间表,中间表中包含各自的主键,在中间表中是外键。
2、建立两个实体类:用户实体类和角色实体类
让用户和角色的实体类能体现出来多对多的关系
各自包含对方一个集合引用
3、建立两个配置文件
用户的配置文件
角色的配置文件
4、实现配置:
当我们查询用户时,可以同时得到用户所包含的角色信息
当我们查询角色时,可以同时得到角色的所赋予的用户信息
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
项目目录结构
实现 Role 到 User 多对多
多对多关系其实我们看成是双向的一对多关系。
业务要求
需求:
当我们查询角色时,可以同时得到角色的所赋予的用户信息。
分析:
查询角色我们需要用到Role表,但角色分配的用户的信息我们并不能直接找到用户信息,而是要通过中间表(USER_ROLE 表)才能关联到用户信息。
用户与角色的关系模型
用户与角色的多对多关系模型如下:
角色表:
用户表:
用户角色中间表:
编写角色实体类
Role:
package com. keafmd. domain;
import java. io. Serializable;
import java. util. List;
public class Role implements Serializable {
private Integer roleId;
private String roleName;
private String roleDesc;
private List< User> users;
public List< User> getUsers ( ) {
return users;
}
public void setUsers ( List< User> users) {
this . users = users;
}
public Integer getRoleId ( ) {
return roleId;
}
public void setRoleId ( Integer roleId) {
this . roleId = roleId;
}
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{" +
"roleId=" + roleId +
", roleName='" + roleName + '\'' +
", roleDesc='" + roleDesc + '\'' +
'}' ;
}
}
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63
编写 Role 持久层接口
IRoleDao:
package com. keafmd. dao;
import com. keafmd. domain. Role;
import java. util. List;
public interface IRoleDao {
List< Role> findAll ( ) ;
}
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
实现的 SQL 语句
select u. * , r. id as rid , r. role_name , r. role_desc from role r
left outer join user_role ur on r. id = ur. rid
left outer join user u on u. id= ur. uid
执行结果:
注意
:sql语句换行的时候最好在每行的末尾或开头添加空格,这样可以防止合并成一行时发生错误。
编写映射文件
IRoleDao.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.keafmd.dao.IRoleDao" >
< resultMap id = " roleMap" type = " role" >
< id property = " roleId" column = " rid" > </ id>
< result property = " roleName" column = " role_name" > </ result>
< result property = " roleDesc" column = " role_desc" > </ result>
< collection property = " users" ofType = " user" >
< id column = " id" property = " id" > </ id>
< result column = " username" property = " username" > </ result>
< result column = " address" property = " address" > </ result>
< result column = " sex" property = " sex" > </ result>
< result column = " birthday" property = " birthday" > </ result>
</ collection>
</ resultMap>
< select id = " findAll" resultMap = " roleMap" >
select u.*,r.id as rid ,r.role_name ,r.role_desc from role r
left outer join user_role ur on r.id = ur.rid
left outer join user u on u.id=ur.uid
</ select>
</ mapper>
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28
测试代码
RoleTest:
package com. keafmd. test;
import com. keafmd. dao. IRoleDao;
import com. keafmd. dao. IUserDao;
import com. keafmd. domain. Role;
import com. keafmd. domain. User;
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. After;
import org. junit. Before;
import org. junit. Test;
import java. io. InputStream;
import java. util. List;
public class RoleTest {
private InputStream in;
private SqlSession sqlsession;
private IRoleDao roleDao;
@Before
public void init ( ) throws Exception{
in = Resources. getResourceAsStream ( "SqlMapConfig.xml" ) ;
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder ( ) ;
SqlSessionFactory factory = builder. build ( in) ;
sqlsession = factory. openSession ( ) ;
roleDao = sqlsession. getMapper ( IRoleDao. class ) ;
}
@After
public void destory ( ) throws Exception{
sqlsession. commit ( ) ;
sqlsession. close ( ) ;
in. close ( ) ;
}
@Test
public void testFindAll ( ) {
List< Role> roles = roleDao. findAll ( ) ;
for ( Role role : roles) {
System. out. println ( "--------每个角色的信息---------" ) ;
System. out. println ( role) ;
System. out. println ( role. getUsers ( ) ) ;
}
}
}
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71
运行结果:
2021 - 02 - 13 00 : 05 : 47 , 481 349 [ main] DEBUG ansaction. jdbc. JdbcTransaction - Opening JDBC Connection
2021 - 02 - 13 00 : 05 : 47 , 784 652 [ main] DEBUG source. pooled. PooledDataSource - Created connection 1027007693.
2021 - 02 - 13 00 : 05 : 47 , 784 652 [ main] DEBUG ansaction. jdbc. JdbcTransaction - Setting autocommit to false on JDBC Connection [ com. mysql. jdbc. JDBC4Connection@3d36e4cd ]
2021 - 02 - 13 00 : 05 : 47 , 791 659 [ main] DEBUG om. keafmd. dao. IRoleDao. findAll - == > Preparing: select u. *, r. id as rid , r. role_name , r. role_desc from role r left outer join user_role ur on r. id = ur. rid left outer join user u on u. id= ur. uid
2021 - 02 - 13 00 : 05 : 47 , 842 710 [ main] DEBUG om. keafmd. dao. IRoleDao. findAll - == > Parameters:
2021 - 02 - 13 00 : 05 : 47 , 869 737 [ main] DEBUG om. keafmd. dao. IRoleDao. findAll - <= = Total: 4
-- -- -- -- 每个角色的信息-- -- -- -- -
Role{ roleId= 1 , roleName= '院长' , roleDesc= '管理整个学院' }
[ User{ id= 41 , username= '老王' , sex= '男' , address= '北京' , birthday= Tue Feb 27 17 : 47 : 08 CST 2018 } , User{ id= 45 , username= '新一' , sex= '男' , address= '北京' , birthday= Sun Mar 04 12 : 04 : 06 CST 2018 } ]
-- -- -- -- 每个角色的信息-- -- -- -- -
Role{ roleId= 2 , roleName= '总裁' , roleDesc= '管理整个公司' }
[ User{ id= 41 , username= '老王' , sex= '男' , address= '北京' , birthday= Tue Feb 27 17 : 47 : 08 CST 2018 } ]
-- -- -- -- 每个角色的信息-- -- -- -- -
Role{ roleId= 3 , roleName= '校长' , roleDesc= '管理整个学校' }
[ ]
2021 - 02 - 13 00 : 05 : 47 , 871 739 [ main] DEBUG ansaction. jdbc. JdbcTransaction - Resetting autocommit to true on JDBC Connection [ com. mysql. jdbc. JDBC4Connection@3d36e4cd ]
2021 - 02 - 13 00 : 05 : 47 , 872 740 [ main] DEBUG ansaction. jdbc. JdbcTransaction - Closing JDBC Connection [ com. mysql. jdbc. JDBC4Connection@3d36e4cd ]
2021 - 02 - 13 00 : 05 : 47 , 872 740 [ main] DEBUG source. pooled. PooledDataSource - Returned connection 1027007693 to pool.
Process finished with exit code 0
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
实现 User 到 Role 的多对多
业务要求
需求:
当我们查询用户时,可以同时得到用户所包含的角色信息。
分析:
相比上面的实现 Role 到 User 多对多,主要变化就是sql语句的变化。
编写用户实体类
User:
package com. keafmd. domain;
import java. io. Serializable;
import java. util. Date;
import java. util. List;
public class User implements Serializable {
private Integer id;
private String username;
private String sex;
private String address;
private Date birthday;
private List< Role> roles;
public List< Role> getRoles ( ) {
return roles;
}
public void setRoles ( List< Role> roles) {
this . roles = roles;
}
public Integer getId ( ) {
return id;
}
public void setId ( Integer id) {
this . id = id;
}
public String getUsername ( ) {
return username;
}
public void setUsername ( String username) {
this . username = username;
}
public String getSex ( ) {
return sex;
}
public void setSex ( String sex) {
this . sex = sex;
}
public String getAddress ( ) {
return address;
}
public void setAddress ( String address) {
this . address = address;
}
public Date getBirthday ( ) {
return birthday;
}
public void setBirthday ( Date birthday) {
this . birthday = birthday;
}
@Override
public String toString ( ) {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", sex='" + sex + '\'' +
", address='" + address + '\'' +
", birthday=" + birthday +
'}' ;
}
}
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84
编写 User持久层接口
IUserDao:
package com. keafmd. dao;
import com. keafmd. domain. User;
import java. util. List;
public interface IUserDao {
List< User> findAll ( ) ;
}
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
实现的 SQL 语句
select u. * , r. id as rid , r. role_name , r. role_desc from user u
left outer join user_role ur on u. id = ur. uid
left outer join role r on r. id= ur. rid
执行结果:
编写映射文件
IUserDao.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.keafmd.dao.IUserDao" >
< resultMap id = " userMap" type = " user" >
< id property = " id" column = " id" > </ id>
< result property = " username" column = " username" > </ result>
< result property = " address" column = " address" > </ result>
< result property = " sex" column = " sex" > </ result>
< result property = " birthday" column = " birthday" > </ result>
< collection property = " roles" ofType = " role" >
< id property = " roleId" column = " rid" > </ id>
< result property = " roleName" column = " role_name" > </ result>
< result property = " roleDesc" column = " role_desc" > </ result>
</ collection>
</ resultMap>
< select id = " findAll" resultMap = " userMap" >
select u.*,r.id as rid ,r.role_name ,r.role_desc from user u
left outer join user_role ur on u.id = ur.uid
left outer join role r on r.id=ur.rid
</ select>
</ mapper>
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
测试代码
UserTest :
package com. keafmd. test;
import com. keafmd. dao. IUserDao;
import com. keafmd. domain. User;
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. After;
import org. junit. Before;
import org. junit. Test;
import java. io. InputStream;
import java. util. List;
public class UserTest {
private InputStream in;
private SqlSession sqlsession;
private IUserDao userDao;
@Before
public void init ( ) throws Exception{
in = Resources. getResourceAsStream ( "SqlMapConfig.xml" ) ;
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder ( ) ;
SqlSessionFactory factory = builder. build ( in) ;
sqlsession = factory. openSession ( ) ;
userDao = sqlsession. getMapper ( IUserDao. class ) ;
}
@After
public void destory ( ) throws Exception{
sqlsession. commit ( ) ;
sqlsession. close ( ) ;
in. close ( ) ;
}
@Test
public void testFindAll ( ) {
List< User> users = userDao. findAll ( ) ;
for ( User user : users) {
System. out. println ( "--------每个用户的信息---------" ) ;
System. out. println ( user) ;
System. out. println ( user. getRoles ( ) ) ;
}
}
}
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68
运行结果:
2021 - 02 - 13 00 : 17 : 32 , 971 422 [ main] DEBUG ansaction. jdbc. JdbcTransaction - Opening JDBC Connection
2021 - 02 - 13 00 : 17 : 33 , 275 726 [ main] DEBUG source. pooled. PooledDataSource - Created connection 1027007693.
2021 - 02 - 13 00 : 17 : 33 , 275 726 [ main] DEBUG ansaction. jdbc. JdbcTransaction - Setting autocommit to false on JDBC Connection [ com. mysql. jdbc. JDBC4Connection@3d36e4cd ]
2021 - 02 - 13 00 : 17 : 33 , 280 731 [ main] DEBUG om. keafmd. dao. IUserDao. findAll - == > Preparing: select u. *, r. id as rid , r. role_name , r. role_desc from user u left outer join user_role ur on u. id = ur. uid left outer join role r on r. id= ur. rid
2021 - 02 - 13 00 : 17 : 33 , 319 770 [ main] DEBUG om. keafmd. dao. IUserDao. findAll - == > Parameters:
2021 - 02 - 13 00 : 17 : 33 , 343 794 [ main] DEBUG om. keafmd. dao. IUserDao. findAll - <= = Total: 10
-- -- -- -- 每个用户的信息-- -- -- -- -
User{ id= 41 , username= '老王' , sex= '男' , address= '北京' , birthday= Tue Feb 27 17 : 47 : 08 CST 2018 }
[ Role{ roleId= 1 , roleName= '院长' , roleDesc= '管理整个学院' } , Role{ roleId= 2 , roleName= '总裁' , roleDesc= '管理整个公司' } ]
-- -- -- -- 每个用户的信息-- -- -- -- -
User{ id= 42 , username= 'update' , sex= '男' , address= 'XXXXXXX' , birthday= Mon Feb 08 19 : 37 : 31 CST 2021 }
[ ]
-- -- -- -- 每个用户的信息-- -- -- -- -
User{ id= 43 , username= '小二王' , sex= '女' , address= '北京' , birthday= Sun Mar 04 11 : 34 : 34 CST 2018 }
[ ]
-- -- -- -- 每个用户的信息-- -- -- -- -
User{ id= 45 , username= '新一' , sex= '男' , address= '北京' , birthday= Sun Mar 04 12 : 04 : 06 CST 2018 }
[ Role{ roleId= 1 , roleName= '院长' , roleDesc= '管理整个学院' } ]
-- -- -- -- 每个用户的信息-- -- -- -- -
User{ id= 50 , username= 'Keafmd' , sex= '男' , address= 'XXXXXXX' , birthday= Mon Feb 08 15 : 44 : 01 CST 2021 }
[ ]
-- -- -- -- 每个用户的信息-- -- -- -- -
User{ id= 51 , username= 'update DAO' , sex= '男' , address= 'XXXXXXX' , birthday= Tue Feb 09 11 : 31 : 38 CST 2021 }
[ ]
-- -- -- -- 每个用户的信息-- -- -- -- -
User{ id= 52 , username= 'Keafmd DAO' , sex= '男' , address= 'XXXXXXX' , birthday= Tue Feb 09 11 : 29 : 41 CST 2021 }
[ ]
-- -- -- -- 每个用户的信息-- -- -- -- -
User{ id= 53 , username= 'Keafmd laset insertid 1' , sex= '男' , address= 'XXXXXXX' , birthday= Fri Feb 12 20 : 53 : 46 CST 2021 }
[ ]
-- -- -- -- 每个用户的信息-- -- -- -- -
User{ id= 54 , username= 'Keafmd laset insertid 2 auto' , sex= '男' , address= 'XXXXXXX' , birthday= Fri Feb 12 21 : 02 : 12 CST 2021 }
[ ]
2021 - 02 - 13 00 : 17 : 33 , 345 796 [ main] DEBUG ansaction. jdbc. JdbcTransaction - Resetting autocommit to true on JDBC Connection [ com. mysql. jdbc. JDBC4Connection@3d36e4cd ]
2021 - 02 - 13 00 : 17 : 33 , 346 797 [ main] DEBUG ansaction. jdbc. JdbcTransaction - Closing JDBC Connection [ com. mysql. jdbc. JDBC4Connection@3d36e4cd ]
2021 - 02 - 13 00 : 17 : 33 , 346 797 [ main] DEBUG source. pooled. PooledDataSource - Returned connection 1027007693 to pool.
Process finished with exit code 0
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38
以上就是Mybatis的多表关联查询(多对多)的全部内容。
看完如果对你有帮助,感谢点赞支持!
如果你是电脑端的话,看到右下角的 “
一键三连
” 了吗,没错点它[哈哈]
加油!
共同努力!
Keafmd