参考
https://mybatis.org/mybatis-3/zh/sqlmap-xml.html
https://mybatis.net.cn/sqlmap-xml.html#Result_Maps
基础表sql
订单表
CREATE TABLE `test_order` (
`order_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '订单id',
`order_name` varchar(255) NOT NULL DEFAULT '' COMMENT '订单名字',
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COMMENT='订单表';
INSERT INTO test_order (`order_id`, `order_name`) VALUES (1, '订单1');
INSERT INTO test_order (`order_id`, `order_name`) VALUES (2, '订单2');
支付表
CREATE TABLE `test_pay` (
`pay_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '支付id',
`pay_name` varchar(255) NOT NULL DEFAULT '' COMMENT '支付名字',
`order_id` bigint(20) NOT NULL COMMENT '订单id',
PRIMARY KEY (`pay_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COMMENT='付款记录表';
INSERT INTO test_pay (`pay_id`, `pay_name`, `order_id`) VALUES (1, '支付名字1', 1);
INSERT INTO test_pay (`pay_id`, `pay_name`, `order_id`) VALUES (2, '支付名字2', 2);
物流表(一个订单有多个阶段的物流信息)
CREATE TABLE `test_flow` (
`flow_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '物流id',
`flow_begin_name` varchar(255) NOT NULL DEFAULT '' COMMENT '物流开始名字',
`flow_end_name` varchar(255) NOT NULL DEFAULT '' COMMENT '物流结束名字',
`order_id` bigint(20) NOT NULL COMMENT '订单id',
PRIMARY KEY (`flow_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COMMENT='物流表';
INSERT INTO test_flow (`flow_id`, `flow_begin_name`, `flow_end_name`, `order_id`) VALUES (1, '北京', '上海', 1);
INSERT INTO test_flow (`flow_id`, `flow_begin_name`, `flow_end_name`, `order_id`) VALUES (2, '上海', '浦东新区', 1);
INSERT INTO test_flow (`flow_id`, `flow_begin_name`, `flow_end_name`, `order_id`) VALUES (3, '浦东新区', '川沙新政', 1);
INSERT INTO test_flow (`flow_id`, `flow_begin_name`, `flow_end_name`, `order_id`) VALUES (4, '西藏', '黑龙江', 2);
INSERT INTO test_flow (`flow_id`, `flow_begin_name`, `flow_end_name`, `order_id`) VALUES (5, '黑龙江', '漠河', 2);
INSERT INTO test_flow (`flow_id`, `flow_begin_name`, `flow_end_name`, `order_id`) VALUES (6, '漠河', '百合路35号', 2);
1个订单对应1个支付信息
1个订单对应n个阶段的物流信息
存储过程
DELIMITER $$
CREATE PROCEDURE `selectOrderAndFlow`(IN `orderId` bigint)
BEGIN
select order_id, order_name from test_order ;
select order_id, pay_id, pay_name from test_pay ;
select order_id, flow_id, flow_begin_name, flow_end_name from test_flow ;
END $$
java 文件
vo文件
package cn.fox.mydemo.domain;
import lombok.Data;
@Data
public class TestOrder {
private Long orderId;
private String orderName;
}
package cn.fox.mydemo.domain;
import lombok.Data;
@Data
public class TestPay {
/**
* 支付id
*/
private Long payId;
/**
* 支付名字
*/
private String payName;
/**
* 订单id
*/
private Long orderId;
}
package cn.fox.mydemo.domain;
import lombok.Data;
@Data
public class TestFlow {
private static final long serialVersionUID = 1L;
/**
* 物流id
*/
private Long flowId;
/**
* 物流开始名字
*/
private String flowBeginName;
/**
* 物流结束名字
*/
private String flowEndName;
/**
* 订单id
*/
private Long orderId;
}
package cn.fox.mydemo.domain.vo;
import cn.fox.mydemo.domain.TestFlow;
import cn.fox.mydemo.domain.TestOrder;
import cn.fox.mydemo.domain.TestPay;
import lombok.Data;
import java.util.List;
@Data
public class TestOrderVo2 {
/** 订单对象 */
private TestOrder testOrder;
/** 支付对象 */
private TestPay testPay;
/** 物流list */
private List<TestFlow> testFlowList;
}
mapper.java文件
package cn.fox.mydemo.mapper;
import cn.fox.mydemo.domain.vo.TestOrderVo2;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;
@Mapper
public interface TestOrderMapper {
List<TestOrderVo2> getOrderAndFlow(Long id);
}
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.fox.mydemo.mapper.TestOrderMapper">
<resultMap type="cn.fox.mydemo.domain.vo.TestOrderVo2" id="testOrderVo2">
<result property="testOrder.orderId" column="order_id" />
<result property="testOrder.orderName" column="order_name" />
<association property="testPay"
javaType="cn.fox.mydemo.domain.TestPay"
resultSet="testPay"
column="order_id"
foreignColumn="order_id">
<id property="payId" column="pay_id"/>
<result property="payName" column="pay_name"/>
<result property="orderId" column="order_id"/>
</association>
<collection property="testFlowList"
ofType="cn.fox.mydemo.domain.TestFlow"
resultSet="testFlowList"
column="order_id"
foreignColumn="order_id">
<id property="flowId" column="flow_id"/>
<result property="flowBeginName" column="flow_begin_name"/>
</collection>
</resultMap>
<select id="getOrderAndFlow"
resultSets="testOrder,testPay,testFlowList"
statementType="CALLABLE"
resultMap="testOrderVo2">
{call selectOrderAndFlow(#{id,jdbcType=INTEGER,mode=IN})}
</select>
</mapper>
测试文件
package cn.fox.mydemo.mybatis;
import cn.fox.mydemo.domain.vo.TestOrderVo2;
import cn.fox.mydemo.mapper.TestOrderMapper;
import com.alibaba.fastjson.JSONObject;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import javax.annotation.Resource;
import java.util.List;
@SpringBootTest
@RunWith(SpringRunner.class)
public class MybatisDemo2 {
@Resource
private TestOrderMapper testOrderMapper;
@Test
public void testMybatis() {
List<TestOrderVo2> orderAndFlow = testOrderMapper.getOrderAndFlow(3L);
System.out.println(JSONObject.toJSONString(orderAndFlow));
}
}
测试结果
一次性执行3条sql,并且根据关联字段进行复杂关系映射
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@48a46b0f] was not registered for synchronization because synchronization is not active
JDBC Connection [com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl@60c96eb4] will not be managed by Spring
==> Preparing: {call selectOrderAndFlow(?)}
==> Parameters: 3(Long)
<== Columns: order_id, order_name
<== Row: 1, 订单1
<== Row: 2, 订单2
<== Total: 2
<== Columns: order_id, pay_id, pay_name
<== Row: 1, 1, 支付名字1
<== Row: 2, 2, 支付名字2
<== Total: 2
<== Columns: order_id, flow_id, flow_begin_name, flow_end_name
<== Row: 1, 1, 北京, 上海
<== Row: 1, 2, 上海, 浦东新区
<== Row: 1, 3, 浦东新区, 川沙新政
<== Row: 2, 4, 西藏, 黑龙江
<== Row: 2, 5, 黑龙江, 漠河
<== Row: 2, 6, 漠河, 百合路35号
<== Total: 6
<== Updates: 0
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@48a46b0f]
结果集自动映射
[
{
"testFlowList":[
{
"flowBeginName":"北京",
"flowEndName":"上海",
"flowId":1,
"orderId":1
},
{
"flowBeginName":"上海",
"flowEndName":"浦东新区",
"flowId":2,
"orderId":1
},
{
"flowBeginName":"浦东新区",
"flowEndName":"川沙新政",
"flowId":3,
"orderId":1
}
],
"testOrder":{
"orderId":1,
"orderName":"订单1"
},
"testPay":{
"orderId":1,
"payId":1,
"payName":"支付名字1"
}
},
{
"testFlowList":[
{
"flowBeginName":"西藏",
"flowEndName":"黑龙江",
"flowId":4,
"orderId":2
},
{
"flowBeginName":"黑龙江",
"flowEndName":"漠河",
"flowId":5,
"orderId":2
},
{
"flowBeginName":"漠河",
"flowEndName":"百合路35号",
"flowId":6,
"orderId":2
}
],
"testOrder":{
"orderId":2,
"orderName":"订单2"
},
"testPay":{
"orderId":2,
"payId":2,
"payName":"支付名字2"
}
}
]