【mysql】 mybatis实现 主从表 left join 1:n 一对多 分页查询 主表从表都有查询条件 【mybatis】count 统计+JSON查询
mybatis实现 主从表 left join 1:n 一对多 分页查询 主表从表都有查询条件+count
需求:
========================================
1.主从表数据 是 1:m
2.主从表各自都有查询条件
3.最后查询结果 需要分页,并统计总数
注意:
=======================================
1.查询的分页,必须在数据库做,否则分页没有意义
解决方法:
注意 下面的入参中 [第一页的10条]
pageNum=0
PageSize=10
实际入参应该是处理过的
pageNum = pageNum*10
pageSize = 10
##############################有对应实体接收查询结果的情况下################################
1.mapper.xml应该这么写
<resultMap type="com.lsrsjava.daywork.domain.week.自定义Bean" id="myResultMap"> <id column="id" property="id"/> <!--一堆的主表 属性--> <result column="userId" property="userId"/> <result column="userName" property="userName"/> <result column="userImg" property="userImg"/> <!--子表对应的属性 封装在list中 即接收了 主表对子表的 1:N --> <collection property="listData" javaType="com.lsrsjava.daywork.domain.week.子表Bean" columnPrefix="slaveTable_"> <id column="id" property="id"/> <result column="rowId" property="rowId"/> <result column="colName" property="colName"/> <result column="val" property="val"/> </collection> </resultMap> <select id="pageFind" resultMap="myResultMap" parameterType="com.lqjava.daywork.api.beans.WorksheetDataSaveBean"> SELECT base.id, t.id slaveTable_id, t.row_id slaveTable_rowId, t.col_name slaveTable_colName, t.val slaveTable_val FROM ( SELECT * FROM worksheet_data_${dataId} WHERE -- 此处之后加 主表的 where查询条件拼接 LIMIT #{pageNum}, #{pageSize} ) base LEFT JOIN worksheet_data_table_data t ON base.id = t.row_id WHERE <!-- 此处之后加 子表单的 where查询拼接 --> </select>
2.mapper.java应该这么写
List<自定义的Bean> pageFind(WorksheetDataSaveBean queryBean);
############################### 不确定返回字段类型[即表中属性是动态的,没有对应实体的情况下]###################################
1.mapper.xml中应该这么写
[下面的示例中:因为我不确定返回的字段,所以用HashMap直接接收查询结果后 自己处理的结果集]
<select id="pageFind" resultType="java.util.HashMap" parameterType="com.lsrjava.daywork.api.beans.WorksheetDataSaveBean"> SELECT base.*, t.id slaveTable_id, t.row_id slaveTable_rowId, t.col_name slaveTable_colName, t.val slaveTable_val FROM ( SELECT * FROM worksheet_data_${dataId}
WHERE -- 此处之后加 主表的 where查询条件拼接 LIMIT #{pageNum}, #{pageSize} ) base LEFT JOIN worksheet_data_table_data t ON base.id = t.row_id
WHERE
<!-- 此处之后加 子表单的 where查询拼接 --> </select>
2.mapper.java应该这么写
List<Map<String,String>> pageFind(WorksheetDataSaveBean queryBean);
===================================count=============================================
count 是什么?count就是页面的 总共total条数
1.mapper.xml应该这么写
<select id="count" parameterType="com.lqjava.daywork.api.beans.WorksheetDataSaveBean" resultType="java.lang.Long"> SELECT count( DISTINCT base.id ) count FROM worksheet_data_${dataId} base LEFT JOIN worksheet_data_table_data c ON c.row_id = base.id -- 拼接条件的地方 </select>
2.mapper.java应该这么写
Long count(WorksheetDataSaveBean queryBean);
====================================附录,完整的 分页+left join+count+不确认返回列+Map接收+mybatis标签嵌套+json字段查询+字符串转日期+字符串转数值+结果集封装处理========================================
需求:
1.数据表 列是动态的多列,因此不确定查询返回是哪些列【因此使用Map接收】
2.主表一行 关联 子表的多行 【因此需要left join】
3.对于主表和子表的所有列,需要提供查询功能【因此需要使用mybatis标签拼接查询条件】
4.主表是正常数据,子表是JSON数据存储【因此需要提供有关JSON字段查询处理的操作】
5.查询出的List<Map>结果集 size=主size*子size 【因此,结果集需要将子表数据封装进主表数据集 java处理】
6.上述结果集条数不能作为分页查询的count统计,返回总页码【因此需要额外count()查询,以返回正确的total】
代码参考:
1.Mapper.xml【一个page查询 一个count查询】
<select id="pageFind" resultType="java.util.HashMap" parameterType="com.lqjava.daywork.api.beans.WorksheetDataSaveBean"> SELECT base.*, u.name create_by_name, u2.name update_by_name, t.id slaveTable_id, t.row_id slaveTable_rowId, t.col_name slaveTable_colName, t.val slaveTable_val FROM ( SELECT d.* FROM worksheet_data_${dataId} d <!-- 此处之后加 主表的 where查询条件拼接 --> <where> <if test="list != null"> <foreach collection="list" item="item" index="index" separator="AND" open="(" close=")"> <choose> <when test='item.cname.contains("date")'> <choose> <when test='item.operator.contains("between") and item.endValue != null'> DATE_FORMAT(${item.cname} , ${item.dateFormat} ) BETWEEN DATE_FORMAT( #{item.value}, ${item.dateFormat} ) AND DATE_FORMAT( #{item.endValue}, ${item.dateFormat} ) </when> <otherwise> DATE_FORMAT( ${item.cname}, ${item.dateFormat} ) = DATE_FORMAT( #{item.value}, ${item.dateFormat} ) </otherwise> </choose> </when> <when test='item.cname.contains("input-number")'> <choose> <when test='item.operator.contains("between") and item.endValue != null'> CAST(${item.cname} AS DECIMAL) BETWEEN #{item.value} AND #{item.endValue} </when> <otherwise> CAST(${item.cname} AS DECIMAL) = #{item.value} </otherwise> </choose> </when> <when test='item.cname.contains("checkbox") or item.cname.contains("select")'> <if test="item.valueList != null"> <choose> <when test=' "OR".equals(item.reOperator) '> <foreach collection="item.valueList " item="v" index="i" separator="OR" open="(" close=")"> FIND_IN_SET( #{v},${item.cname} ) </foreach> </when> <otherwise> <foreach collection="item.valueList " item="v" index="i" separator="AND" open="(" close=")"> FIND_IN_SET( #{v},${item.cname} ) </foreach> </otherwise> </choose> </if> </when> <when test='item.cname.contains("dept-user") or item.cname.contains("dept-base")'> <if test="item.valueList != null"> <choose> <when test=' "OR".equals(item.reOperator) '> <foreach collection="item.valueList " item="v" index="i" separator="OR" open="(" close=")"> <choose> <when test='item.operator.contains("like") '> ${item.cname} -> '$[*].name' like '%${v}%' </when> <otherwise> JSON_CONTAINS( ${item.cname} ->'$[*].name' , '"${v}"', '$') </otherwise> </choose> </foreach> </when> <otherwise> <foreach collection="item.valueList " item="v" index="i" separator="AND" open="(" close=")"> <choose> <when test='item.operator.contains("like") '> ${item.cname} -> '$[*].name' like '%${v}%' </when> <otherwise> JSON_CONTAINS( ${item.cname} ->'$[*].name' , '"${v}"', '$') </otherwise> </choose> </foreach> </otherwise> </choose> </if> </when> <otherwise> <choose> <when test='item.operator.contains("like") '> ${item.cname} like '%${item.value}%' </when> <otherwise> ${item.cname} = #{item.value} </otherwise> </choose> </otherwise> </choose> </foreach> </if> </where> LIMIT #{pageNum}, #{pageSize} ) base LEFT JOIN (SELECT * from worksheet_data_table_data where data_id = #{dataId}) t ON base.id = t.row_id LEFT JOIN dept_user u ON base.create_by = u.id LEFT JOIN dept_user u2 ON base.update_by = u2.id <!-- 此处之后加 子表单的 where查询拼接 --> <where> <if test="slaveList != null"> <foreach collection="slaveList" item="item" index="index" separator="AND" open="(" close=")"> t.col_name = #{item.tableName} AND <choose> <when test='item.cname.contains("date")'> <choose> <when test='item.operator.contains("between") and item.endValue != null'> STR_TO_DATE(val -> '$.${item.cname}','"%Y-%m-%d %H:%i:%s"') between #{item.value} AND date_add(#{item.endValue}, interval 1 day) </when> <otherwise> STR_TO_DATE(val -> '$.${item.cname}','"%Y-%m-%d %H:%i:%s"') between #{item.value} AND date_add(#{item.value}, interval 1 day) </otherwise> </choose> </when> <when test='item.cname.contains("input-number")'> <choose> <when test='item.operator.contains("between") and item.endValue != null'> CAST(val -> '$."${item.cname}"' AS DECIMAL) BETWEEN #{item.value} AND #{item.endValue} </when> <otherwise> CAST(val -> '$."${item.cname}"' AS DECIMAL) = #{item.value} </otherwise> </choose> </when> <when test='item.cname.contains("checkbox") or item.cname.contains("select")'> <if test="item.valueList != null"> <choose> <when test=' "OR".equals(item.reOperator) '> <foreach collection="item.valueList " item="v" index="i" separator="OR" open="(" close=")"> val -> '$.${item.cname}' like '%,${v},%' </foreach> </when> <otherwise> <foreach collection="item.valueList " item="v" index="i" separator="AND" open="(" close=")"> val -> '$.${item.cname}' like '%,${v},%' </foreach> </otherwise> </choose> </if> </when> <when test='item.cname.contains("dept-user") or item.cname.contains("dept-base")'> <if test="item.valueList != null"> <choose> <when test=' "OR".equals(item.reOperator) '> <foreach collection="item.valueList " item="v" index="i" separator="OR" open="(" close=")"> val -> '$."${item.cname}"' like '%${v}%' </foreach> </when> <otherwise> <foreach collection="item.valueList " item="v" index="i" separator="AND" open="(" close=")"> val -> '$."${item.cname}"' like '%${v}%' </foreach> </otherwise> </choose> </if> </when> <otherwise> <choose> <when test='item.operator.contains("like") '> val -> '$.${item.cname}' like '%${item.value}%' </when> <otherwise> JSON_CONTAINS( val ->'$.${item.cname}' , '"${item.value}"', '$') </otherwise> </choose> </otherwise> </choose> </foreach> </if> </where> </select> <select id="count" parameterType="com.lqjava.daywork.api.beans.WorksheetDataSaveBean" resultType="java.lang.Long"> SELECT count( DISTINCT base.id ) count FROM worksheet_data_${dataId} base LEFT JOIN (SELECT * from worksheet_data_table_data where data_id = #{dataId}) c ON c.row_id = base.id <!-- 主表 + 子表 查询条件拼接 --> <where> <if test="list != null"> <foreach collection="list" item="item" index="index" separator="AND" open="(" close=")"> <choose> <when test='item.cname.contains("date")'> <choose> <when test='item.operator.contains("between") and item.endValue != null'> DATE_FORMAT(${item.cname} , ${item.dateFormat} ) BETWEEN DATE_FORMAT( #{item.value}, ${item.dateFormat} ) AND DATE_FORMAT( #{item.endValue}, ${item.dateFormat} ) </when> <otherwise> DATE_FORMAT( ${item.cname}, ${item.dateFormat} ) = DATE_FORMAT( #{item.value}, ${item.dateFormat} ) </otherwise> </choose> </when> <when test='item.cname.contains("input-number")'> <choose> <when test='item.operator.contains("between") and item.endValue != null'> CAST(${item.cname} AS DECIMAL) BETWEEN #{item.value} AND #{item.endValue} </when> <otherwise> CAST(${item.cname} AS DECIMAL) = #{item.value} </otherwise> </choose> </when> <when test='item.cname.contains("checkbox") or item.cname.contains("select")'> <if test="item.valueList != null"> <choose> <when test=' "OR".equals(item.reOperator) '> <foreach collection="item.valueList " item="v" index="i" separator="OR" open="(" close=")"> FIND_IN_SET( #{v},${item.cname} ) </foreach> </when> <otherwise> <foreach collection="item.valueList " item="v" index="i" separator="AND" open="(" close=")"> FIND_IN_SET( #{v},${item.cname} ) </foreach> </otherwise> </choose> </if> </when> <when test='item.cname.contains("dept-user") or item.cname.contains("dept-base")'> <if test="item.valueList != null"> <choose> <when test=' "OR".equals(item.reOperator) '> <foreach collection="item.valueList " item="v" index="i" separator="OR" open="(" close=")"> <choose> <when test='item.operator.contains("like") '> ${item.cname} -> '$[*].name' like '%${v}%' </when> <otherwise> JSON_CONTAINS( ${item.cname} ->'$[*].name' , '"${v}"', '$') </otherwise> </choose> </foreach> </when> <otherwise> <foreach collection="item.valueList " item="v" index="i" separator="AND" open="(" close=")"> <choose> <when test='item.operator.contains("like") '> ${item.cname} -> '$[*].name' like '%${v}%' </when> <otherwise> JSON_CONTAINS( ${item.cname} ->'$[*].name' , '"${v}"', '$') </otherwise> </choose> </foreach> </otherwise> </choose> </if> </when> <otherwise> <choose> <when test='item.operator.contains("like") '> ${item.cname} like '%${item.value}%' </when> <otherwise> ${item.cname} = #{item.value} </otherwise> </choose> </otherwise> </choose> </foreach> </if> <if test="slaveList != null"> AND <foreach collection="slaveList" item="item" index="index" separator="AND" open="(" close=")"> c.col_name = #{item.tableName} AND <choose> <when test='item.cname.contains("date")'> <choose> <when test='item.operator.contains("between") and item.endValue != null'> STR_TO_DATE(val -> '$.${item.cname}','"%Y-%m-%d %H:%i:%s"') between #{item.value} AND date_add(#{item.endValue}, interval 1 day) </when> <otherwise> STR_TO_DATE(val -> '$.${item.cname}','"%Y-%m-%d %H:%i:%s"') between #{item.value} AND date_add(#{item.value}, interval 1 day) </otherwise> </choose> </when> <when test='item.cname.contains("input-number")'> <choose> <when test='item.operator.contains("between") and item.endValue != null'> CAST(val -> '$."${item.cname}"' AS DECIMAL) BETWEEN #{item.value} AND #{item.endValue} </when> <otherwise> CAST(val -> '$."${item.cname}"' AS DECIMAL) = #{item.value} </otherwise> </choose> </when> <when test='item.cname.contains("checkbox") or item.cname.contains("select")'> <if test="item.valueList != null"> <choose> <when test=' "OR".equals(item.reOperator) '> <foreach collection="item.valueList " item="v" index="i" separator="OR" open="(" close=")"> val -> '$.${item.cname}' like '%,${v},%' </foreach> </when> <otherwise> <foreach collection="item.valueList " item="v" index="i" separator="AND" open="(" close=")"> val -> '$.${item.cname}' like '%,${v},%' </foreach> </otherwise> </choose> </if> </when> <when test='item.cname.contains("dept-user") or item.cname.contains("dept-base")'> <if test="item.valueList != null"> <choose> <when test=' "OR".equals(item.reOperator) '> <foreach collection="item.valueList " item="v" index="i" separator="OR" open="(" close=")"> val -> '$."${item.cname}"' like '%${v}%' </foreach> </when> <otherwise> <foreach collection="item.valueList " item="v" index="i" separator="AND" open="(" close=")"> val -> '$."${item.cname}"' like '%${v}%' </foreach> </otherwise> </choose> </if> </when> <otherwise> <choose> <when test='item.operator.contains("like") '> val -> '$.${item.cname}' like '%${item.value}%' </when> <otherwise> JSON_CONTAINS( val ->'$.${item.cname}' , '"${item.value}"', '$') </otherwise> </choose> </otherwise> </choose> </foreach> </if> </where> </select>
2.Mapper.java
List<Map<String,String>> pageFind(WorksheetDataSaveBean queryBean);
Long count(WorksheetDataSaveBean queryBean);
3.入参数据结构
public class WorksheetDataSaveBean { private Long dataId; private Long rowId; private List<WorksheetData> list; //入参集合 private List<WorksheetData> resultList;//结果列集合 要返回哪些列信息 private List<WorksheetData> slaveList;//子表单查询条件 private Integer pageNum = 0; private Integer pageSize = 10;