MyBatis多表查询

可以使用专门定义一个PO类作为输出类型，并在SQL中查询所有字段信息。

本文主要介绍使用resultMap，定义专门的 resultMap

一对一查询 association用法

需求：本次案例主要以最为简单的用户和账户的模型来分析Mybatis多表关系。用户为User 表，账户为Account 表。一个用户（User）可以有多个账户（Account）。具体关系如下：
用户表： user

字段名	字段描述	数据类型
id	用户主键	int
name	用户名	varchar(20)
sex	性别	varchar(10)
birthday	出生日期	date
address	地址	varchar(60)

账户表： account

字段名	字段描述	数据类型
id	编号	int <pk>
uid	用户编号	int<fk>
money	金额	double

通过面向对象的(has a)关系可以得知，可以在 Account 类中加入一个 User 类的对象来代表这个账户是哪个用户的。

• 定义Account类

public class Account implements Serializable{
	private Integer id;
	private Integer uid;
	private Double money;
	//加入User对象
	private User user;
	/*此处省略getter setter方法*/
}

• 定义User类

public class User implements Serializable{
	private Integer id;
	private String name;
	private String sex;
	private Date birthday;
	private String address;
	/*此处省略getter setter方法*/
}

• 定义AccountDao接口，查询所有账户方法

public interface AccountDao{
	List<Account> findAll();
}

定义AccountDao.xml文件 方式一：

<mapper namespace="top.wayneliu.dao.IAccountDao">
	<!-- 建立对应关系 --> 
	<resultMap type="account" id="accountMap">
		<id column="aid" property="id"/>
		<result column="uid" property="uid"/> 
		<result column="money" property="money"/> 
		<association property="user" javaType="user"> 
			<id column="id" property="id"/> 
			<result column="username" property="username"/>
			<result column="sex" property="sex"/>
			<result column="birthday" property="birthday"/>
			<result column="address" property="address"/>
		</association>
	</resultMap>
	<select id="findAll" resultMap="accountMap"> 
		select u.*,a.id as aid,a.uid,a.money from account a,user u where a.uid =u.id;
	</select> 
</mapper>

定义AccountDao.xml 方式二：

<mapper namespace="top.wayneliu.dao.IAccountDao">
	<!-- 建立user对象对应关系 --> 
	<resultMap type="user" id="userMap">
		<id column="id" property="id"/> 
		<result column="username" property="username"/>
		<result column="sex" property="sex"/>
		<result column="birthday" property="birthday"/>
		<result column="address" property="address"/>
	</resultMap>
	<!--建立account对象对应关系-->
	<resultMap type="account" id="accountMap">
		<id column="aid" property="id"/>
		<result column="uid" property="uid"/> 
		<result column="money" property="money"/> 
		<!--通过assosiation引入user对象的resultMap-->
		<association property="user" resultMap=“userMap”/>
	</resultMap>
	<select id="findAll" resultMap="accountMap"> 
		select u.*,a.id as aid,a.uid,a.money from account a,user u where a.uid =u.id;
	</select> 
</mapper>

- 嵌套select 查询方式定义

这种方式是使用一条单独的select语句来加载关联的实体(本例中就是Account实体)，然后在association元素中引用此select语句(注：此方法会产生N+1问题，尽量不要用嵌套的select语句)

<mapper namespace="top.wayneliu.dao.IAccountDao">
	<!-- 建立user对象对应关系 --> 
	<resultMap type="user" id="userMap">
		<id column="id" property="id"/> 
		<result column="username" property="username"/>
		<result column="sex" property="sex"/>
		<result column="birthday" property="birthday"/>
		<result column="address" property="address"/>
	</resultMap>
	<!--建立account对象对应关系-->
	<resultMap type="account" id="accountMap">
		<id column="id" property="id"/>
		<result column="uid" property="uid"/> 
		<result column="money" property="money"/> 
		<!--通过assosiation引入user对象的resultMap-->
		<association property="user" column="uid" select="findUserByid"/>
	</resultMap>
	<select id="findAll" resultMap="accountMap"> 
		select * from account
	</select> 
	<select id="findUserByid" parameterType="int" resultMap="userMap">
		select * from user where id = #{id}
	</select>
</mapper>

使用select嵌套方式控制台输出：

2020-03-04 15:43:27,345 1332   [           main] DEBUG ansaction.jdbc.JdbcTransaction  - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@1e7c7811]
2020-03-04 15:43:27,387 1374   [           main] DEBUG ayneliu.dao.AccountDao.findAll  - ==>  Preparing: select * from account 
2020-03-04 15:43:27,461 1448   [           main] DEBUG ayneliu.dao.AccountDao.findAll  - ==> Parameters: 
2020-03-04 15:43:27,519 1506   [           main] DEBUG iu.dao.AccountDao.findUserByid  - ====>  Preparing: select * from user where id = ? 
2020-03-04 15:43:27,520 1507   [           main] DEBUG iu.dao.AccountDao.findUserByid  - ====> Parameters: 41(Integer)
2020-03-04 15:43:27,564 1551   [           main] DEBUG iu.dao.AccountDao.findUserByid  - <====      Total: 1
2020-03-04 15:43:27,565 1552   [           main] DEBUG iu.dao.AccountDao.findUserByid  - ====>  Preparing: select * from user where id = ? 
2020-03-04 15:43:27,565 1552   [           main] DEBUG iu.dao.AccountDao.findUserByid  - ====> Parameters: 45(Integer)
2020-03-04 15:43:27,610 1597   [           main] DEBUG iu.dao.AccountDao.findUserByid  - <====      Total: 1
2020-03-04 15:43:27,612 1599   [           main] DEBUG ayneliu.dao.AccountDao.findAll  - <==      Total: 3
Account{id=1, uid=41, money=1000.0, user=User{id=41, username='老王', birthday=Tue Feb 27 17:47:08 CST 2018, sex='男', address='北京'}}
Account{id=2, uid=45, money=1000.0, user=User{id=45, username='传智播客', birthday=Sun Mar 04 12:04:06 CST 2018, sex='男', address='北京金燕龙'}}
Account{id=3, uid=41, money=2000.0, user=User{id=41, username='老王', birthday=Tue Feb 27 17:47:08 CST 2018, sex='男', address='北京'}}
2020-03-04 15:43:27,613 1600   [           main] DEBUG ansaction.jdbc.JdbcTransaction  - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@1e7c7811]
2020-03-04 15:43:27,656 1643   [           main] DEBUG ansaction.jdbc.JdbcTransaction  - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@1e7c7811]
2020-03-04 15:43:27,656 1643   [           main] DEBUG source.pooled.PooledDataSource  - Returned connection 511473681 to pool.

使用select嵌套的方式可以看出，MyBtis首先查询了账户表，之后多次查询用户表。

一对多查询 collection用法

需求：查询所有用户信息及用户关联的账户信息。
分析：用户信息和他的账户信息为一对多关系，并且查询过程中如果用户没有账户信息，此时也要将用户信息查询出来，这里可以使用左外连接查询比较合适。

• 在User类中加入List<Account>属性

public class User implements Serializable{
	private Integer id;
	private String name;
	private String sex;
	private Date birthday;
	private String address;
	private List<Account> accounts
	/*此处省略getter setter方法*/
}

• 定义UserDao接口和查询所有用户及账户方法

public interface UserDao {
	public List<User> findAll();
}

• 定义持久层Dao映射文件配置

<mapper namespace="top.wayneliu.dao.UserDao">
    <resultMap  type="account" id="accountMap">
        <id column="aid" property="id"/>
        <result column="uid" property="uid"/>
        <result column="money" property="money"/>
    </resultMap>
    <resultMap type="user" id="userMap">
        <id column="id" property="id"/>
        <result column="username" property="username"/>
        <result column="sex" property="sex"/>
        <result column="birthday" property="birthday"/>
        <result column="address" property="address"/>
        <collection property="accounts" resultMap="accountMap"/>
    </resultMap>
    <select id="findAll" resultMap="userMap">
        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>

控制台输出：

2020-03-04 16:19:49,571 1634   [           main] DEBUG p.wayneliu.dao.UserDao.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 
2020-03-04 16:19:49,662 1725   [           main] DEBUG p.wayneliu.dao.UserDao.findAll  - ==> Parameters: 
2020-03-04 16:19:49,738 1801   [           main] DEBUG p.wayneliu.dao.UserDao.findAll  - <==      Total: 7
User{id=41, username='老王', birthday=Tue Feb 27 17:47:08 CST 2018, sex='男', address='北京', accounts=[Account{id=1, uid=41, money=1000.0, user=null}, Account{id=3, uid=41, money=2000.0, user=null}]}
User{id=45, username='传智播客', birthday=Sun Mar 04 12:04:06 CST 2018, sex='男', address='北京金燕龙', accounts=[Account{id=2, uid=45, money=1000.0, user=null}]}
User{id=42, username='小二王', birthday=Fri Mar 02 15:09:37 CST 2018, sex='女', address='北京金燕龙', accounts=[]}
User{id=43, username='小二王', birthday=Sun Mar 04 11:34:34 CST 2018, sex='女', address='北京金燕龙', accounts=[]}
User{id=46, username='老王', birthday=Wed Mar 07 17:37:26 CST 2018, sex='男', address='北京', accounts=[]}
User{id=48, username='小马宝莉', birthday=Thu Mar 08 11:44:00 CST 2018, sex='女', address='北京修正', accounts=[]}

---

涉及关键词：

association
collection