文章预览
mybatis中的多表查询
表之间的关系有几种:
一对多
多对一
一对一
多对多
举例:
用户和订单就是一对多
订单和用户就是多对一
一个用户可以下多个订单
多个订单属于同一个用户
人和身份证号就是一对一
一个人只能有一个身份证号
一个身份证号只能属于一个人
老师和学生之间就是多对多
一个学生可以被多个老师教过
一个老师可以交多个学生
特例:
如果拿出每一个订单,他都只能属于一个用户。
所以Mybatis就把多对一看成了一对一。
mybatis中的多表查询:
示例:用户和账户
一个用户可以有多个账户
一个账户只能属于一个用户(多个账户也可以属于同一个用户)
步骤:
1、建立两张表:用户表,账户表
让用户表和账户表之间具备一对多的关系:需要使用外键在账户表中添加
2、建立两个实体类:用户实体类和账户实体类
让用户和账户的实体类能体现出来一对多的关系
3、建立两个配置文件
用户的配置文件
账户的配置文件
4、实现配置:
当我们查询用户时,可以同时得到用户下所包含的账户信息
当我们查询账户时,可以同时得到账户的所属用户信息
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 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
数据库准备
sql文件在这篇文章中:
Mybatis 框架快速入门(超详细)
项目目录
一对一查询(多对一)
需求:
查询所有账户,同时还要获取当前账户的所属用户信息。
注意:
因为一个账户信息只能供某个用户使用,所以从查询账户信息出发关联查询用户信息为一对一查询。如果从用户信息出发查询用户下的账户信息则为一对多查询,因为一个用户可以有多个账户。
方式一(通过Accunt的子类方式查询–不常用)
定义账户信息的实体类
Account:
package com. keafmd. domain;
import java. io. Serializable;
public class Account implements Serializable {
private Integer id;
private Integer uid;
private Double money;
public Integer getId ( ) {
return id;
}
public void setId ( Integer id) {
this . id = id;
}
public Integer getUid ( ) {
return uid;
}
public void setUid ( Integer uid) {
this . uid = uid;
}
public Double getMoney ( ) {
return money;
}
public void setMoney ( Double money) {
this . money = money;
}
@Override
public String toString ( ) {
return "Account{" +
"id=" + id +
", uid=" + uid +
", money=" + money +
'}' ;
}
}
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 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
编写 Sql 语句
实现查询账户信息时,也要查询账户所对应的用户信息。
select a. * , u. username, u. address from account a , user u where a. uid= u. id;
查询结果:
select u. * , a. id as aid, a. uid, a. money from account a , user u where a. uid= u. id;
查询结果:
定义 AccountUser 类
为了能够封装上面 SQL 语句的查询结果,定义 AccountCustomer 类中要包含账户信息同时还要包含用户信息,所以我们要在定义 AccountUser 类时可以继承 User 类。
package com. keafmd. domain;
public class AccountUser extends Account {
private String username;
private String address;
public String getUsername ( ) {
return username;
}
public void setUsername ( String username) {
this . username = username;
}
public String getAddress ( ) {
return address;
}
public void setAddress ( String address) {
this . address = address;
}
@Override
public String toString ( ) {
return super . toString ( ) + " AccountUser{" +
"username='" + username + '\'' +
", address='" + address + '\'' +
'}' ;
}
}
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 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
定义账户的持久层 Dao 接口
IAccountDao:
package com. keafmd. dao;
import com. keafmd. domain. Account;
import com. keafmd. domain. AccountUser;
import java. util. List;
public interface IAccountDao {
List< AccountUser> findAllAccount ( ) ;
}
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 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
定义 IAccountDao.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.IAccountDao" >
< select id = " findAllAccount" resultType = " accountuser" >
select a.*,u.username,u.address from account a ,user u where a.uid=u.id;
</ select>
</ mapper>
注意:因为上面查询的结果中包含了账户信息同时还包含了用户信息,所以我们的返回值类型 returnType的值设置为 AccountUser 类型,这样就可以接收账户信息和用户信息了。
测试代码
package com. keafmd. test;
import com. keafmd. dao. IAccountDao;
import com. keafmd. dao. IUserDao;
import com. keafmd. domain. Account;
import com. keafmd. domain. AccountUser;
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. ArrayList;
import java. util. List;
public class AccountTest {
private InputStream in;
private SqlSession sqlsession;
private IAccountDao accountDao;
@Before
public void init ( ) throws Exception{
in = Resources. getResourceAsStream ( "SqlMapConfig.xml" ) ;
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder ( ) ;
SqlSessionFactory factory = builder. build ( in) ;
sqlsession = factory. openSession ( ) ;
accountDao = sqlsession. getMapper ( IAccountDao. class ) ;
}
@After
public void destory ( ) throws Exception{
sqlsession. commit ( ) ;
sqlsession. close ( ) ;
in. close ( ) ;
}
@Test
public void testfindAllAccount ( ) {
List< AccountUser> accountusers = accountDao. findAllAccount ( ) ;
for ( AccountUser accountuser : accountusers) {
System. out. println ( accountuser) ;
}
}
}
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 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
运行testfindAllAccount()的结果:
方式二(建立实体类关系的方式–常用)
使用 resultMap,定义专门的 resultMap 用于映射一对一查询结果。
通过面向对象的(has a)关系可以得知,我们可以在 Account 类中加入一个 User 类的对象来代表这个账户是哪个用户的。
修改 Account 类
Account:
package com. keafmd. domain;
import java. io. Serializable;
public class Account implements Serializable {
private Integer id;
private Integer uid;
private Double money;
private User user;
public User getUser ( ) {
return user;
}
public void setUser ( User user) {
this . user = user;
}
public Integer getId ( ) {
return id;
}
public void setId ( Integer id) {
this . id = id;
}
public Integer getUid ( ) {
return uid;
}
public void setUid ( Integer uid) {
this . uid = uid;
}
public Double getMoney ( ) {
return money;
}
public void setMoney ( Double money) {
this . money = money;
}
@Override
public String toString ( ) {
return "Account{" +
"id=" + id +
", uid=" + uid +
", money=" + money +
'}' ;
}
}
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 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
修改 AccountDao 接口中的方法
package com. keafmd. dao;
import com. keafmd. domain. Account;
import com. keafmd. domain. AccountUser;
import java. util. List;
public interface IAccountDao {
List< Account> findAll ( ) ;
}
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
将返回值改 为了 Account 类型,因为 Account 类中包含了一个 User 类的对象,它可以封装账户所对应的用户信息。
重新定义 IAccountDao.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.IAccountDao" >
< resultMap id = " accountUserMap" type = " account" >
< id property = " id" column = " aid" > </ id>
< result property = " uid" column = " uid" > </ result>
< result property = " money" column = " money" > </ result>
< association property = " user" column = " uid" javaType = " user" >
< id property = " id" column = " 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>
</ association>
</ resultMap>
< select id = " findAll" resultMap = " accountUserMap" >
select u.*,a.id as aid,a.uid,a.money from account a ,user u where a.uid=u.id;
</ 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
测试代码
package com. keafmd. test;
import com. keafmd. dao. IAccountDao;
import com. keafmd. dao. IUserDao;
import com. keafmd. domain. Account;
import com. keafmd. domain. AccountUser;
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. ArrayList;
import java. util. List;
public class AccountTest {
private InputStream in;
private SqlSession sqlsession;
private IAccountDao accountDao;
@Before
public void init ( ) throws Exception{
in = Resources. getResourceAsStream ( "SqlMapConfig.xml" ) ;
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder ( ) ;
SqlSessionFactory factory = builder. build ( in) ;
sqlsession = factory. openSession ( ) ;
accountDao = sqlsession. getMapper ( IAccountDao. class ) ;
}
@After
public void destory ( ) throws Exception{
sqlsession. commit ( ) ;
sqlsession. close ( ) ;
in. close ( ) ;
}
@Test
public void testFindAll ( ) {
List< Account> accounts = accountDao. findAll ( ) ;
for ( Account account : accounts) {
System. out. println ( "------------每个account的信息-------" ) ;
System. out. println ( account) ;
System. out. println ( account. getUser ( ) ) ;
}
}
}
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 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
运行结果:
一对多查询
需求:
查询所有用户,同时获取到用户下所有账户的信息。
分析:
用户信息和他的账户信息为一对多关系,并且查询过程中如果用户没有账户信息,此时也要将用户信息查询出来,我们想到了左外连接查询比较合适。
编写User实体类
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< Account> accounts;
public List< Account> getAccounts ( ) {
return accounts;
}
public void setAccounts ( List< Account> accounts) {
this . accounts = accounts;
}
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
编写 SQL 语句
select u. * , a. id as aid, a. uid, a. money from user u left outer join account a on u. id = a. uid
执行结果:
用户持久层 Dao 接口
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
用户持久层 Dao 映射文件配置
<?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 = " userAccountMap" 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 = " accounts" ofType = " account" >
< id column = " aid" property = " id" > </ id>
< result column = " uid" property = " uid" > </ result>
< result column = " money" property = " money" > </ result>
</ collection>
</ resultMap>
< select id = " findAll" resultMap = " userAccountMap" >
select u.* ,a.id as aid,a.uid,a.money from user u left outer join account a on u.id =a.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
测试代码
package com. keafmd. test;
import com. keafmd. dao. IAccountDao;
import com. keafmd. dao. IUserDao;
import com. keafmd. domain. Account;
import com. keafmd. domain. AccountUser;
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. getAccounts ( ) ) ;
}
}
}
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 - 12 23 : 14 : 27 , 911 278 [ main] DEBUG ansaction. jdbc. JdbcTransaction - Opening JDBC Connection
2021 - 02 - 12 23 : 14 : 28 , 402 769 [ main] DEBUG source. pooled. PooledDataSource - Created connection 1783047508.
2021 - 02 - 12 23 : 14 : 28 , 405 772 [ main] DEBUG ansaction. jdbc. JdbcTransaction - Setting autocommit to false on JDBC Connection [ com. mysql. jdbc. JDBC4Connection@6a472554 ]
2021 - 02 - 12 23 : 14 : 28 , 410 777 [ main] DEBUG om. keafmd. dao. IUserDao. findAll - == > Preparing: select u. * , a. id as aid, a. uid, a. money from user u left outer join account a on u. id = a. uid
2021 - 02 - 12 23 : 14 : 28 , 453 820 [ main] DEBUG om. keafmd. dao. IUserDao. findAll - == > Parameters:
2021 - 02 - 12 23 : 14 : 28 , 481 848 [ main] DEBUG om. keafmd. dao. IUserDao. findAll - <= = Total: 10
-- -- -- -- 每个用户的信息-- -- -- -- -
User{ id= 41 , username= '老王' , sex= '男' , address= '北京' , birthday= Tue Feb 27 17 : 47 : 08 CST 2018 }
[ Account{ id= 1 , uid= 41 , money= 1000.0 } , Account{ id= 3 , uid= 41 , money= 2000.0 } ]
-- -- -- -- 每个用户的信息-- -- -- -- -
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 }
[ Account{ id= 2 , uid= 45 , money= 1000.0 } ]
-- -- -- -- 每个用户的信息-- -- -- -- -
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 - 12 23 : 14 : 28 , 483 850 [ main] DEBUG ansaction. jdbc. JdbcTransaction - Resetting autocommit to true on JDBC Connection [ com. mysql. jdbc. JDBC4Connection@6a472554 ]
2021 - 02 - 12 23 : 14 : 28 , 484 851 [ main] DEBUG ansaction. jdbc. JdbcTransaction - Closing JDBC Connection [ com. mysql. jdbc. JDBC4Connection@6a472554 ]
2021 - 02 - 12 23 : 14 : 28 , 484 851 [ main] DEBUG source. pooled. PooledDataSource - Returned connection 1783047508 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
………………………………