mybatis关联查询

1、一对一查询

user和order两个实体一对多关系。

目的：查询order，然后获得user的相关信息

（1）方法一：继承，添加属性

新建一个pojo继承order，然后在新建的pojo里面添加user的属性，属性名和user里的属名一致。

mapper.xml中直接写连接语句。

（2）方法二：使用resultMap

 

1）Orders类：添加一个user属性

public class Orders  implements Serializable{
	private static final long serialVersionUID = 1L;
	private Integer id;
    private String number;
    private Date createtime;
    private String note; 
    private User user;

2）orderMapper.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="club.ityuchao.mapper.OrdersMapper">

	<!-- 使用了association后，未写的属性不会匹配 -->
	<resultMap type="orders" id="ordersResultMap">
		<!-- 用于主键，当主键为多个值时，写多个id -->
		<id property="id" column="id"/>
		<!-- 普通列 -->
		<result property="userId" column="user_id" />
		<result property="number" column="number" />
		<result property="createtime" column="createtime" />
		<result property="note" column="note" />
		
		<association property="user" javaType="User">
			<id property="id" column="user_id"/>
			<result property="username" column="username"/>
			<result property="sex" column="sex"/>
			<result property="birthday" column="birthday"/>
			<result property="address" column="address"/>
		</association>
		
	</resultMap>
	
	<select id="queryOrdersAndUser" resultMap="ordersResultMap">
		select
		o.id,
		o.user_id,
		o.number,
		o.createtime,
		o.note,
		u.username,
		u.sex,
		u.birthday,
		u.address
		from orders o left join user u on o.user_id = u.id
	</select>
	
</mapper>

注意：使用了association后，未配置的属性不会匹配

当orders表和user表有列名一样时，可以为其中一个添加别名。

3）给ordersMapper接口添加相应的方法

List<Orders> queryOrdersAndUser();

2、一对多查询

（1）User类：添加一个List<Orders>属性

public class User implements Serializable {
	
	private static final long serialVersionUID = 1L;
	private Integer id;
	private String username;// 用户姓名
	private String sex;// 性别
	private Date birthday;// 生日
	private String address;// 地址
	private List<Orders> ordersList;

（2）userMapper.xml：

<resultMap type="User" id="userRusultMap">
	<id property="id" column="id"/>
	<result property="username" column="username"/>
	<result property="sex" column="sex"/>
	<result property="birthday" column="birthday"/>
	<result property="address" column="address"/>
	<collection property="ordersList" javaType="List" ofType="Orders">
		<id property="id" column="oid"/>
		<result property="number" column="number"/>
		<result property="createtime" column="createtime"/>
		<result property="note" column="note"/>
	</collection>
</resultMap>
	
<select id="queryUserAndOrders" resultMap="userRusultMap">
	select
	u.id,
	u.username,
	u.sex,
	u.birthday,
	u.address,
	o.id oid,
	o.number,
	o.createtime,
	o.note
	from user u
	left join orders o on u.id=o.user_id
</select>

（3）UserMapper中添加相应的抽象方法

List<User> queryUserAndOrders();