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;}@Overridepublic 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;}@Overridepublic 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 mapperPUBLIC "-//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_idFROM mybatis.order INNER JOIN mybatis.order_detailwhere 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 configurationPUBLIC "-//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;@Beforepublic void setup(){sessionFactory = MyBatisUtils.getSqlSessionFactory();}@Testpublic 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 ConnectionDEBUG [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: 3Order [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不能够自动的去除重复记录。