最近一直在研究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不能够自动的去除重复记录。