Java SpringBoot 数据库多表联查Join操作

方法1、多表联查Join操作可分页，XML写筛选条件

Dao层

// 分页使用
List<Map> orderList(Page<OrderEntity> page, com.alibaba.fastjson.JSONObject query);

// 不分页使用
List<Map> orderList(com.alibaba.fastjson.JSONObject query);

Xml

<select id="orderList" resultType="java.util.Map">
        SELECT
        any_value(o.id) as id
        FROM
        `order` AS o
        JOIN `order_pro` AS p ON o.id = p.order_id
        <where>
            <if test="query.order_number!=null and query.order_number!=''">
                and o.order_number =#{query.order_number}
            </if>
            <if test="query.start_at!=null and query.start_at!=''">
                and o.finish_at >= #{query.start_at}
            </if>
            <if test="query.end_at!=null and query.end_at!=''">
                and o.finish_at <![CDATA[<=#{query.end_at}]]>
            </if>
            <if test="query.consumer_number!=null and query.consumer_number!=''">
                and o.consumer_number like CONCAT('%',#{query.consumer_number},'%')
            </if>
        </where>
        order by o.id DESC
    </select>

分页使用

Query query= new Query();
query.setStart_at(16512312322);
com.alibaba.fastjson.JSONObject jsonObject = com.alibaba.fastjson.JSONObject.parseObject(com.alibaba.fastjson.JSONObject.toJSONString(query));
Page<OrderEntity> page = new Page<>(query.getPage(), query.getPer_page());
List<Map> lists = baseDao.orderList(page, jsonObject);

long count = page.getTotal();

不分页使用

List<Map> lists = baseDao.queryOrderList(null, jsonObject);

方法2、多表联查Join操作外部写筛选条件

Dao层

List<Map> orderList(Page<OrderEntity> page,@Param(Constants.WRAPPER) QueryWrapper queryWrapper);

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="com.xx.dao.OrderDao">

    <select id="orderList" resultType="java.util.Map">
        SELECT o.id
        FROM order o
                 LEFT JOIN order_pro as op ON op.order_id = o.id
            ${ew.customSqlSegment}
    </select>
</xml>

分页使用

QueryWrapper<TZlkjOrderEntity> wrapper = new QueryWrapper<>();
        wrapper.eq("o.is_delete",0);
Page<OrderEntity> page = new Page<>(query.getPage(), query.getPer_page());
List<Map> lists = baseDao.orderList(page, wrapper);

long count = page.getTotal();

不分页使用

List<Map> lists = baseDao.queryOrderList(null, wrapper);