mybatis高级映射一对多查询(一)

最近一直在研究mybatis,查询是并不可少的研究内容。mybatis的一对多的查询，个人觉得比hibernate简单的很多。好了，废话不多说了，下面以一个简单的例子解释一下mybatis的一对多的查询。

我准备的例子是订单和订单明细的案例，订单与订单明细是一对多的关系。

一, 数据库表的准备。

<span style="font-size:18px;">-- ----------------------------
-- Table structure for `order`
-- ----------------------------
DROP TABLE IF EXISTS `order`;
CREATE TABLE `order` (
  `order_id` int(11) NOT NULL AUTO_INCREMENT,
  `order_time` date NOT NULL,
  `order_person` varchar(255) COLLATE utf8_bin NOT NULL,
  `cancel` tinyint(4) NOT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- ----------------------------
-- Records of order
-- ----------------------------
INSERT INTO `order` VALUES ('1', '2016-03-01', '张三', '0');
INSERT INTO `order` VALUES ('2', '2016-03-02', '李四', '1');</span>

<span style="font-size:18px;">-- ----------------------------
-- Table structure for `order_detail`
-- ----------------------------
DROP TABLE IF EXISTS `order_detail`;
CREATE TABLE `order_detail` (
  `order_detail_id` int(11) NOT NULL AUTO_INCREMENT,
  `number` int(11) NOT NULL,
  `price` double NOT NULL,
  `order_id` int(11) NOT NULL,
  PRIMARY KEY (`order_detail_id`),
  KEY `order_detail_foreign_key` (`order_id`),
  CONSTRAINT `order_detail_foreign_key` FOREIGN KEY (`order_id`) REFERENCES `order` (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- ----------------------------
-- Records of order_detail
-- ----------------------------
INSERT INTO `order_detail` VALUES ('1', '12', '100.5', '1');
INSERT INTO `order_detail` VALUES ('2', '15', '89', '1');
INSERT INTO `order_detail` VALUES ('3', '67', '890', '2');</span>

二，与数据库表对应的entity的类结构

<span style="font-size:18px;">package com.npf.entity;

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

/**
 * 
 * @author Jack
 *
 */
public class Order implements Serializable{

	private static final long serialVersionUID = -6742540989795435522L;
	
	private Integer orderId;
	
	private Date orderTime; 
	
	private String orderPerson;
	
	private boolean cancel;
	
	private List<OrderDetail> orderDetails;

	public Integer getOrderId() {
		return orderId;
	}

	public void setOrderId(Integer orderId) {
		this.orderId = orderId;
	}

	public Date getOrderTime() {
		return orderTime;
	}

	public void setOrderTime(Date orderTime) {
		this.orderTime = orderTime;
	}

	public String getOrderPerson() {
		return orderPerson;
	}

	public void setOrderPerson(String orderPerson) {
		this.orderPerson = orderPerson;
	}

	public boolean isCancel() {
		return cancel;
	}

	public void setCancel(boolean cancel) {
		this.cancel = cancel;
	}

	public List<OrderDetail> getOrderDetails() {
		return orderDetails;
	}

	public void setOrderDetails(List<OrderDetail> orderDetails) {
		this.orderDetails = orderDetails;
	}

	@Override
	public String toString() {
		return "Order [orderId=" + orderId + ", orderTime=" + orderTime
				+ ", orderPerson=" + orderPerson + ", cancel=" + cancel
				+ ", orderDetails=" + orderDetails + "]";
	}
}
</span>

<span style="font-size:18px;">package com.npf.entity;

import java.io.Serializable;

/**
 * 
 * @author Jack
 *
 */
public class OrderDetail implements Serializable{

	private static final long serialVersionUID = 1691097264687172875L;
	
	private Integer orderDetailId;
	
	private Integer number;
	
	private double price;
	
	private Integer orderId;

	public Integer getOrderDetailId() {
		return orderDetailId;
	}

	public void setOrderDetailId(Integer orderDetailId) {
		this.orderDetailId = orderDetailId;
	}

	public Integer getNumber() {
		return number;
	}

	public void setNumber(Integer number) {
		this.number = number;
	}

	public double getPrice() {
		return price;
	}

	public void setPrice(double price) {
		this.price = price;
	}

	public Integer getOrderId() {
		return orderId;
	}

	public void setOrderId(Integer orderId) {
		this.orderId = orderId;
	}

	@Override
	public String toString() {
		return "OrderDetail [orderDetailId=" + orderDetailId + ", number="
				+ number + ", price=" + price + ", orderId=" + orderId + "]";
	} 
}
</span>

三，mapper接口和mapper.xml配置文件(注意这两个文件需要放在同一个包下面)

1.OrderMapper.xml

<span style="font-size:18px;"><?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.npf.order.mapper.OrderMapper">

	<resultMap type="com.npf.entity.Order" id="findOrderAndOrderDetailResultMap">
		<id column="order_id" property="orderId"/>
		<result column="order_time" property="orderTime"/>
		<result column="order_person" property="orderPerson"/>
		<result column="cancel" property="cancel"/>
		<collection property="orderDetails" ofType="com.npf.entity.OrderDetail">
			<id column="order_detail_id" property="orderDetailId"/>
			<result column="price" property="price"/>
			<result column="number" property="number"/>
			<result column="order_id" property="orderId"/>
		</collection>
	</resultMap>
	
	<select id="findOrderAndOrderDetail" resultMap="findOrderAndOrderDetailResultMap">
		SELECT 
			order.order_id,
		  	order.order_time,
		  	order.order_person,
		  	order.cancel,
			order_detail.number,
			order_detail.price,
			order_detail.order_detail_id
		FROM mybatis.order INNER JOIN mybatis.order_detail
		where order.order_id = order_detail.order_id;
	</select>
	
</mapper>

</span>

2.OrderMapper接口

<span style="font-size:18px;">package com.npf.order.mapper;

import java.util.List;

import com.npf.entity.Order;

public interface OrderMapper {
	
	public List<Order> findOrderAndOrderDetail();

}
</span>

四,mybatis的核心配置文件

1.SqlMapConfig.xml

<span style="font-size:18px;"><?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
	
	<properties resource="db.properties"></properties>

	<!-- 和spring整合后 environments配置将废除-->
	<environments default="development">
		<environment id="development">
			<!-- 使用jdbc事务管理，事务控制由mybatis-->
			<transactionManager type="JDBC" />
			<!-- 数据库连接池，由mybatis管理-->
			<dataSource type="POOLED">
				<property name="driver" value="${jdbc.driver}" />
				<property name="url" value="${jdbc.url}" />
				<property name="username" value="${jdbc.username}" />
				<property name="password" value="${jdbc.password}" />
			</dataSource>
		</environment>
	</environments>
	
	<!-- 加载映射文件 -->
	<mappers>
		<!-- 批量加载mapper
		指定mapper接口的包名，mybatis自动扫描包下边所有mapper接口进行加载
		遵循一些规范：需要将mapper接口类名和mapper.xml映射文件名称保持一致，且在一个目录 中
		上边规范的前提是：使用的是mapper代理方法
		 -->
		<package name="com.npf.order.mapper"/>
	</mappers>
</configuration>
</span>

五,获取SqlSessionFactory的工具类

<span style="font-size:18px;">package com.npf.utils;

import java.io.IOException;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public final class MyBatisUtils {
	
	private static SqlSessionFactory sqlSessionFactory;
	
	private static final String resource = "SqlMapConfig.xml";
	
	private MyBatisUtils(){}
	
	public static SqlSessionFactory getSqlSessionFactory(){
		if(sqlSessionFactory==null){
			synchronized (MyBatisUtils.class) {
				if(sqlSessionFactory==null){
					try {
						sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream(resource));
					} catch (IOException e) {
						e.printStackTrace();
					}
				}
			}
		}
		return sqlSessionFactory;
	}

}
</span>

六，测试

<span style="font-size:18px;">package com.npf.test;

import java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.junit.Before;
import org.junit.Test;

import com.npf.entity.Order;
import com.npf.order.mapper.OrderMapper;
import com.npf.utils.MyBatisUtils;

public class OrderMapperTest {

	private SqlSessionFactory sessionFactory;
	
	@Before
	public void setup(){
		sessionFactory = MyBatisUtils.getSqlSessionFactory();
	}
	
	@Test
	public void findOrderAndOrderDetailTest() throws Exception{
		SqlSession session = sessionFactory.openSession();
		OrderMapper orderMapper = session.getMapper(OrderMapper.class);
		List<Order> orders = orderMapper.findOrderAndOrderDetail();
		for(Order order:orders){
			System.out.println(order);
		}
		session.close();
	}
	
	
}
</span>

七，运行结果

<span style="font-size:18px;">DEBUG [main] - Opening JDBC Connection
DEBUG [main] - Created connection 547558035.
DEBUG [main] - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@20a31293]
DEBUG [main] - ==>  Preparing: SELECT order.order_id, order.order_time, order.order_person, order.cancel, order_detail.number, order_detail.price, order_detail.order_detail_id FROM mybatis.order INNER JOIN mybatis.order_detail where order.order_id = order_detail.order_id; 
DEBUG [main] - ==> Parameters: 
DEBUG [main] - <==      Total: 3
Order [orderId=1, orderTime=Tue Mar 01 00:00:00 CST 2016, orderPerson=张三, cancel=false, orderDetails=[OrderDetail [orderDetailId=1, number=12, price=100.5, orderId=1], OrderDetail [orderDetailId=2, number=15, price=89.0, orderId=1]]]
Order [orderId=2, orderTime=Wed Mar 02 00:00:00 CST 2016, orderPerson=李四, cancel=true, orderDetails=[OrderDetail [orderDetailId=3, number=67, price=890.0, orderId=2]]]
DEBUG [main] - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@20a31293]
DEBUG [main] - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@20a31293]
DEBUG [main] - Returned connection 547558035 to pool.</span>

八，总结

1.mybatis的一对多查询不要使用ResultType, 因为会出现重复记录，ResultType不能够自动的去除重复记录。