【MyBatis】高级映射

【一对一查询】

      需求：查询订单信息，关联查询创建订单的用户信息

方式一：resultType

1.创建pojo类（包括除了主表实体类Orders.java中包含的字段外，其他需要查询的字段）：

package cn.itcast.mybatis.po;

//通过此类映射订单和用户查询的结果，让此类继承包括 字段较多的pojo类
public class OrdersCustom extends Orders {

	// 添加用户属性
	/*
	 * USER.username, USER.sex, USER.address
	 */

	private String username;
	private String sex;
	private String address;
        //get和set方法省略
}

2.配置mapper.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="cn.itcast.mybatis.mapper.OrdersMapperCustom">

	<!-- 查询订单关联查询用户信息 -->
	<select id="findOrdersUser" resultType="cn.itcast.mybatis.po.OrdersCustom">
		select
		orders.*,user.username,user.sex,user.address from orders,user where
		orders.user_id= user.id
	</select>

</mapper>

3.创建对应的mapper.java接口

// 查询订单关联查询用户信息
publicList<OrdersCustom> findOrdersUser() throws Exception;

4.测试方法

package cn.itcast.mybatis.mapper;

import java.io.InputStream;
import java.util.List;

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 cn.itcast.mybatis.po.OrdersCustom;

public class OrdersMapperCustomTest {
	private SqlSessionFactory sqlSessionFactory;

	@Before
	public void setUp() throws Exception {
		// 创建sqlSessionFactory

		// mybatis配置文件
		String resource = "SqlMapConfig.xml";
		// 得到配置文件流
		InputStream inputStream = Resources.getResourceAsStream(resource);

		// 创建会话工厂，传入mybatis的配置文件信息
		sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
	}

	@Test
	public void testFindOrdersUser() throws Exception {
		SqlSession sqlSession = sqlSessionFactory.openSession();
		OrdersMapperCustom ordersMapperCustom = sqlSession
				.getMapper(OrdersMapperCustom.class);
		List<OrdersCustom> list = ordersMapperCustom.findOrdersUser();
		System.out.println(list);
		sqlSession.close();
	}
}

方式二：resultMap

       使用ResultMap将查询结果在红的用户信息映射到Orders对象中，在orders类中添加User属性，将关联查询出来的用户信息映射到orders对象中的user属性中。

1.在Orders类中添加user属性

package cn.itcast.mybatis.po;
import java.util.Date;
import java.util.List;
public class Orders {
    private Integer id;

    private Integer userId;

    private String number;

    private Date createtime;

    private String note;
    
    //用户信息
    private User user;
    
    //get和set方法省略
}

2.配置mapper.xml文件（ResultMap定义和Statement定义）

<?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="cn.itcast.mybatis.mapper.OrdersMapperCustom">

	<!-- 定义ResultMap start -->
	<!-- 订单查询关联用户的ResultMap -->
	<resultMap type="cn.itcast.mybatis.po.Orders" id="OrdersUserResultMap">
		<id column="id" property="id" />
		<result column="user_id" property="userId" />
		<result column="number" property="number" />
		<result column="createtime" property="createtime" />
		<result column="note" property="note" />
		<association property="user" javaType="cn.itcast.mybatis.po.User">
			<id column="user_id" property="id" />
			<result column="username" property="username" />
			<result column="sex" property="sex" />
			<result column="address" property="address" />
		</association>
	</resultMap>
	<!-- 定义ResultMap end -->

	<!-- 查询订单关联查询用户信息 -->
	<select id="findOrdersUser" resultType="cn.itcast.mybatis.po.OrdersCustom">
		select
		orders.*,user.username,user.sex,user.address from orders,user where
		orders.user_id= user.id
	</select>

	<!-- 查询订单关联查询用户信息,使用resultmap -->
	<select id="findOrdersUserResultMap" resultMap="OrdersUserResultMap">
		select
		orders.*,user.username,user.sex,user.address from orders,user where
		orders.user_id= user.id
	</select>
</mapper>

3.创建对应的mapper.java接口

publicList<OrdersCustom> findOrdersUserResultMap() throws Exception;

4.测试方法

package cn.itcast.mybatis.mapper;

import java.io.InputStream;
import java.util.List;

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 cn.itcast.mybatis.po.OrdersCustom;

public class OrdersMapperCustomTest {
	private SqlSessionFactory sqlSessionFactory;

	@Before
	public void setUp() throws Exception {
		// 创建sqlSessionFactory

		// mybatis配置文件
		String resource = "SqlMapConfig.xml";
		// 得到配置文件流
		InputStream inputStream = Resources.getResourceAsStream(resource);

		// 创建会话工厂，传入mybatis的配置文件信息
		sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
	}

	@Test
	public void testFindOrdersUser() throws Exception {
		SqlSession sqlSession = sqlSessionFactory.openSession();
		OrdersMapperCustom ordersMapperCustom = sqlSession
				.getMapper(OrdersMapperCustom.class);
		List<OrdersCustom> list = ordersMapperCustom.findOrdersUser();
		System.out.println(list);
		sqlSession.close();
	}

	@Test
	public void findOrdersUserResultMap() throws Exception {
		SqlSession sqlSession = sqlSessionFactory.openSession();
		OrdersMapperCustom ordersMapperCustom = sqlSession
				.getMapper(OrdersMapperCustom.class);
		List<OrdersCustom> list = ordersMapperCustom.findOrdersUserResultMap();
		System.out.println(list);
		sqlSession.close();
	}

}

比较小结：

       当一对一查询时，使用ResultMap和resultType都可以实现。但是resultType较为简单，如果pojo中更没有包括查询出来的列名，需要增加列名对应的属性即可完成映射。而ResultMap需要单独定义ResultMap，相较来说有点麻烦。

【一对多查询】

      需求：查询订单及订单明细的信息。

1.在orders中添加List<Orderdetail>属性

package cn.itcast.mybatis.po;

import java.util.Date;
import java.util.List;

public class Orders {
    private Integer id;

    private Integer userId;

    private String number;

    private Date createtime;

    private String note;
    
    //用户信息
    private User user;
    
    //订单明细
    private List<Orderdetail> orderdetails;

   //get和set方法略
}

2.配置mapper.xml（ResultMap定义和Statement定义）

<?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="cn.itcast.mybatis.mapper.OrdersMapperCustom">

	<!-- 定义ResultMap start -->
	<!-- 1.订单查询关联用户的ResultMap -->
	<resultMap type="cn.itcast.mybatis.po.Orders" id="OrdersUserResultMap">
		<id column="id" property="id" />
		<result column="user_id" property="userId" />
		<result column="number" property="number" />
		<result column="createtime" property="createtime" />
		<result column="note" property="note" />
		<association property="user" javaType="cn.itcast.mybatis.po.User">
			<id column="user_id" property="id" />
			<result column="username" property="username" />
			<result column="sex" property="sex" />
			<result column="address" property="address" />
		</association>
	</resultMap>

	<!-- 2.订单及订单明细的ResultMap -->
	<resultMap type="cn.itcast.mybatis.po.Orders" id="OrdersAndOrderDetailResultMap"
		extends="OrdersUserResultMap">
		<!-- 订单信息 -->
		<!-- 用户信息 -->
		<!-- 使用extends继承，不用在中配置订单信息和用户信息的映射 -->


		<!-- 订单明细信息 一个订单关联查询出了多条明细，要使用collection进行映射 collection：对关联查询到多条记录映射到集合对象中 
			property：将关联查询到多条记录映射到cn.itcast.mybatis.po.Orders哪个属性 ofType：指定映射到list集合属性中pojo的类型 -->
		<collection property="orderdetails" ofType="cn.itcast.mybatis.po.Orderdetail">
			<!-- id：订单明细唯 一标识 property:要将订单明细的唯 一标识 映射到cn.itcast.mybatis.po.Orderdetail的哪个属性 -->
			<id column="orderdetail_id" property="id" />
			<result column="items_id" property="itemsId" />
			<result column="items_num" property="itemsNum" />
			<result column="orders_id" property="ordersId" />
		</collection>
	</resultMap>
	<!-- 定义ResultMap end -->

	<!-- Statement定义 begin -->
	<!-- 查询订单关联查询用户信息 -->
	<select id="findOrdersUser" resultType="cn.itcast.mybatis.po.OrdersCustom">
		select
		orders.*,user.username,user.sex,user.address from orders,user where
		orders.user_id= user.id
	</select>

	<!-- 1.查询订单关联查询用户信息,使用resultmap -->
	<select id="findOrdersUserResultMap" resultMap="OrdersUserResultMap">
		select
		orders.*,user.username,user.sex,user.address from orders,user where
		orders.user_id= user.id
	</select>

	<!-- 2.查询订单关联查询用户及订单明细，使用resultmap -->
	<select id="findOrdersAndOrderDetailResultMap" resultMap="OrdersAndOrderDetailResultMap">
		select
		orders.*,
		user.username,
		user.sex,
		user.address,
		orderdetail.id
		orderdetail_id,
		orderdetail.items_id,
		orderdetail.items_num,
		orderdetail.orders_id
		from
		orders,
		user,
		orderdetail
		where orders.user_id =
		user.id and orderdetail.orders_id=orders.id
	</select>
	<!-- Statement定义 end -->
</mapper>

3.mapper.java

package cn.itcast.mybatis.mapper;

import java.util.List;

import cn.itcast.mybatis.po.Orders;
import cn.itcast.mybatis.po.OrdersCustom;

public interface OrdersMapperCustom {
	// 查询订单关联查询用户信息
	public List<OrdersCustom> findOrdersUser() throws Exception;

	public List<OrdersCustom> findOrdersUserResultMap() throws Exception;

	// 查询订单（关联用户）及订单明细
	public List<Orders> findOrdersAndOrderDetailResultMap() throws Exception;
}

4.测试方法

package cn.itcast.mybatis.mapper;

import java.io.InputStream;
import java.util.List;

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 cn.itcast.mybatis.po.Orders;
import cn.itcast.mybatis.po.OrdersCustom;

public class OrdersMapperCustomTest {
	private SqlSessionFactory sqlSessionFactory;

	@Before
	public void setUp() throws Exception {
		// 创建sqlSessionFactory

		// mybatis配置文件
		String resource = "SqlMapConfig.xml";
		// 得到配置文件流
		InputStream inputStream = Resources.getResourceAsStream(resource);

		// 创建会话工厂，传入mybatis的配置文件信息
		sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
	}


	@Test
	public void findOrdersAndOrderDetailResultMap() throws Exception {
		SqlSession sqlSession = sqlSessionFactory.openSession();
		OrdersMapperCustom ordersMapperCustom = sqlSession
				.getMapper(OrdersMapperCustom.class);
		List<Orders> list = ordersMapperCustom
				.findOrdersAndOrderDetailResultMap();
		System.out.println(list);
		sqlSession.close();
	}

}

比较小结：

      与上面一对一查询不同，一条订单记录对应一个用户，但是订单和订单明细这两张表的关系是，一个订单中可能有多个商品，这样在orderDetail表中就可能出现订单ID相同的记录，这明显是一对多的关系。如果用resultType实现，就会出现重复数据，例如订单表中有两条订单，并且每个订单中都买了两个商品，用resultType实现，最后返回的数据就是4条，我们还需要自己处理数据，大大的不明智。所以使用上例中所示的ResultMap。

【多对多查询】

     需求：查询用户及用户购买商品信息。

1.映射思路

       将用户信息表映射到user类中。在user类中添加订单列表属性List<Orders>，将用户创建的订单映射到ordersList。在Orders中添加订单明细列表属性List<OrderDetail>，将订单的明细映射到orderdetails（这一步在一对多查询中已经实现）。在OrderDetail中添加items属性，将订单明细所对应的商品银蛇到items。

package cn.itcast.mybatis.po;

public class Orderdetail {
	private Integer id;

	private Integer ordersId;

	private Integer itemsId;

	private Integer itemsNum;

	// 明细对应的商品信息
	private Items items;

	// get和set方法略
	
}

package cn.itcast.mybatis.po;

import java.io.Serializable;
import java.util.Date;
import java.util.List;

public class User implements Serializable {
	
	//属性名和数据库表的字段对应
	private int id;
	private String username;// 用户姓名
	private String sex;// 性别
	private Date birthday;// 生日
	private String address;// 地址
	
	//用户创建的订单列表
	private List<Orders> ordersList;
	//get和set方法略
	
}

2.配置mapper.xml（ResultMap定义和Statement定义）

<?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="cn.itcast.mybatis.mapper.OrdersMapperCustom">

	<!-- 定义ResultMap *****************************************************start -->
	<!-- 1.订单查询关联用户的ResultMap -->
	<resultMap type="cn.itcast.mybatis.po.Orders" id="OrdersUserResultMap">
		<id column="id" property="id" />
		<result column="user_id" property="userId" />
		<result column="number" property="number" />
		<result column="createtime" property="createtime" />
		<result column="note" property="note" />
		<association property="user" javaType="cn.itcast.mybatis.po.User">
			<id column="user_id" property="id" />
			<result column="username" property="username" />
			<result column="sex" property="sex" />
			<result column="address" property="address" />
		</association>
	</resultMap>

	<!-- 2.订单及订单明细的ResultMap -->
	<resultMap type="cn.itcast.mybatis.po.Orders" id="OrdersAndOrderDetailResultMap"
		extends="OrdersUserResultMap">
		<!-- 订单信息 -->
		<!-- 用户信息 -->
		<!-- 使用extends继承，不用在中配置订单信息和用户信息的映射 -->


		<!-- 订单明细信息 一个订单关联查询出了多条明细，要使用collection进行映射 collection：对关联查询到多条记录映射到集合对象中 
			property：将关联查询到多条记录映射到cn.itcast.mybatis.po.Orders哪个属性 ofType：指定映射到list集合属性中pojo的类型 -->
		<collection property="orderdetails" ofType="cn.itcast.mybatis.po.Orderdetail">
			<!-- id：订单明细唯 一标识 property:要将订单明细的唯 一标识 映射到cn.itcast.mybatis.po.Orderdetail的哪个属性 -->
			<id column="orderdetail_id" property="id" />
			<result column="items_id" property="itemsId" />
			<result column="items_num" property="itemsNum" />
			<result column="orders_id" property="ordersId" />
		</collection>
	</resultMap>

	<!-- 3.查询用户及购买的商品 -->
	<resultMap type="cn.itcast.mybatis.po.User" id="UserAndItemsResultMap">
		<!-- 用户信息 -->
		<id column="user_id" property="id" />
		<result column="username" property="username" />
		<result column="sex" property="sex" />
		<result column="address" property="address" />

		<!-- 订单信息 一个用户对应多个订单，使用collection映射 -->
		<collection property="ordersList" ofType="cn.itcast.mybatis.po.Orders">
			<id column="id" property="id" />
			<result column="user_id" property="userId" />
			<result column="number" property="number" />
			<result column="createtime" property="createtime" />
			<result column="note" property="note" />

			<!-- 订单明细 一个订单包括 多个明细 -->
			<collection property="orderdetails" ofType="cn.itcast.mybatis.po.Orderdetail">
				<id column="orderdetail_id" property="id" />
				<result column="items_id" property="itemsId" />
				<result column="items_num" property="itemsNum" />
				<result column="orders_id" property="ordersId" />

				<!-- 商品信息 一个订单明细对应一个商品 -->
				<association property="items" javaType="cn.itcast.mybatis.po.Items">
					<id column="items_id" property="id" />
					<result column="items_name" property="name" />
					<result column="items_detail" property="detail" />
					<result column="items_price" property="price" />
				</association>

			</collection>

		</collection>
	</resultMap>

	<!-- 定义ResultMap *****************************************************end -->

	<!-- Statement定义 *****************************************************begin -->
	<!-- 查询订单关联查询用户信息 -->
	<select id="findOrdersUser" resultType="cn.itcast.mybatis.po.OrdersCustom">
		select
		orders.*,user.username,user.sex,user.address from orders,user where
		orders.user_id= user.id
	</select>

	<!-- 1.查询订单关联查询用户信息,使用resultmap -->
	<select id="findOrdersUserResultMap" resultMap="OrdersUserResultMap">
		select
		orders.*,user.username,user.sex,user.address from orders,user where
		orders.user_id= user.id
	</select>

	<!-- 2.查询订单关联查询用户及订单明细，使用resultmap -->
	<select id="findOrdersAndOrderDetailResultMap" resultMap="OrdersAndOrderDetailResultMap">
		select
		orders.*,
		user.username,
		user.sex,
		user.address,
		orderdetail.id
		orderdetail_id,
		orderdetail.items_id,
		orderdetail.items_num,
		orderdetail.orders_id
		from
		orders,
		user,
		orderdetail
		where orders.user_id =
		user.id and orderdetail.orders_id=orders.id
	</select>

	<!-- 3.查询用户及购买的商品信息，使用resultmap -->
	<select id="findUserAndItemsResultMap" resultMap="UserAndItemsResultMap">
		select
		orders.*,
		user.username,
		user.sex,
		user.address,
		orderdetail.id
		orderdetail_id,
		orderdetail.items_id,
		orderdetail.items_num,
		orderdetail.orders_id,
		items.name items_name,
		items.detail items_detail,
		items.price items_price
		from
		orders,
		user,
		orderdetail,
		items
		where
		orders.user_id = user.id and orderdetail.orders_id=orders.id and
		orderdetail.items_id = items.id
	</select>
	<!-- Statement定义 *****************************************************end -->
</mapper>

3.mapper.java

package cn.itcast.mybatis.mapper;

import java.util.List;

import cn.itcast.mybatis.po.Orders;
import cn.itcast.mybatis.po.OrdersCustom;
import cn.itcast.mybatis.po.User;

public interface OrdersMapperCustom {
	// 查询订单关联查询用户信息
	public List<OrdersCustom> findOrdersUser() throws Exception;

	public List<OrdersCustom> findOrdersUserResultMap() throws Exception;

	// 查询订单（关联用户）及订单明细
	public List<Orders> findOrdersAndOrderDetailResultMap() throws Exception;

	// 查询用户购买商品信息
	public List<User> findUserAndItemsResultMap() throws Exception;
}

4.测试方法

package cn.itcast.mybatis.mapper;

import java.io.InputStream;
import java.util.List;

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 cn.itcast.mybatis.po.Orders;
import cn.itcast.mybatis.po.OrdersCustom;
import cn.itcast.mybatis.po.User;

public class OrdersMapperCustomTest {
	private SqlSessionFactory sqlSessionFactory;

	@Before
	public void setUp() throws Exception {
		// 创建sqlSessionFactory

		// mybatis配置文件
		String resource = "SqlMapConfig.xml";
		// 得到配置文件流
		InputStream inputStream = Resources.getResourceAsStream(resource);

		// 创建会话工厂，传入mybatis的配置文件信息
		sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
	}

	@Test
	public void findUserAndItemsResultMap() throws Exception {
		SqlSession sqlSession = sqlSessionFactory.openSession();
		OrdersMapperCustom ordersMapperCustom = sqlSession
				.getMapper(OrdersMapperCustom.class);
		List<User> list = ordersMapperCustom.findUserAndItemsResultMap();
		System.out.println(list);
		sqlSession.close();
	}

}

比较小结：

       对于多个表的级联查询，主要是要分析清楚哪个是主表，我们可以通过在此基础上的映射和包装，来实践这些特殊要求的查询。