当前位置: 开发笔记 > 编程语言 > 正文

mysql多条件分页查询_【mysql】mybatis实现主从表leftjoin1:n一对多分页查询主表从表都有查询条件【mybatis】count统计+JSON查询...

作者：是不是有谁代替我陪在你身旁 | 来源：互联网 | 2023-09-04 16:00

mybatis实现主从表leftjoin1:n一对多分页查询主表从表都有查询条件count需求：1.主从表数据是1：m2.主从表各自都有查询条件3.最后

mybatis实现主从表 left join 1:n 一对多分页查询主表从表都有查询条件&＃43;count

需求&＃xff1a;

1.主从表数据是 1&＃xff1a;m

2.主从表各自都有查询条件

3.最后查询结果需要分页&＃xff0c;并统计总数

注意&＃xff1a;

1.查询的分页&＃xff0c;必须在数据库做&＃xff0c;否则分页没有意义

解决方法&＃xff1a;

注意下面的入参中 [第一页的10条]

pageNum&＃61;0PageSize&＃61;10

实际入参应该是处理过的

pageNum &＃61; pageNum*10pageSize&＃61; 10

##############################有对应实体接收查询结果的情况下################################

1.mapper.xml应该这么写

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

base.id &＃61; t.row_id

WHERE

2.mapper.java应该这么写

List pageFind(WorksheetDataSaveBean queryBean);

############################### 不确定返回字段类型[即表中属性是动态的&＃xff0c;没有对应实体的情况下]###################################

1.mapper.xml中应该这么写

[下面的示例中&＃xff1a;因为我不确定返回的字段&＃xff0c;所以用HashMap直接接收查询结果后自己处理的结果集]

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

base.id &＃61; t.row_id

WHERE

2.mapper.java应该这么写

List> pageFind(WorksheetDataSaveBean queryBean);

&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;count&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;

count 是什么&＃xff1f;count就是页面的总共total条数

1.mapper.xml应该这么写

SELECT

count( DISTINCT base.id ) count

FROM

worksheet_data_${dataId} base

LEFT JOIN

worksheet_data_table_data c

c.row_id &＃61; base.id

-- 拼接条件的地方

2.mapper.java应该这么写

Long count(WorksheetDataSaveBean queryBean);

&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;附录&＃xff0c;完整的分页&＃43;left join&＃43;count&＃43;不确认返回列&＃43;Map接收&＃43;mybatis标签嵌套&＃43;json字段查询&＃43;字符串转日期&＃43;字符串转数值&＃43;结果集封装处理&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;

需求&＃xff1a;

1.数据表列是动态的多列&＃xff0c;因此不确定查询返回是哪些列【因此使用Map接收】

2.主表一行关联子表的多行【因此需要left join】

3.对于主表和子表的所有列&＃xff0c;需要提供查询功能【因此需要使用mybatis标签拼接查询条件】

4.主表是正常数据&＃xff0c;子表是JSON数据存储【因此需要提供有关JSON字段查询处理的操作】

5.查询出的List结果集 size&＃61;主size*子size 【因此&＃xff0c;结果集需要将子表数据封装进主表数据集 java处理】

6.上述结果集条数不能作为分页查询的count统计&＃xff0c;返回总页码【因此需要额外count()查询&＃xff0c;以返回正确的total】

代码参考&＃xff1a;

1.Mapper.xml【一个page查询一个count查询】

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

DATE_FORMAT(${item.cname} , ${item.dateFormat} ) BETWEEN DATE_FORMAT( #{item.value}, ${item.dateFormat} ) AND DATE_FORMAT( #{item.endValue}, ${item.dateFormat} )

DATE_FORMAT( ${item.cname}, ${item.dateFormat} ) &＃61; DATE_FORMAT( #{item.value}, ${item.dateFormat} )

CAST(${item.cname} AS DECIMAL) BETWEEN #{item.value} AND #{item.endValue}

CAST(${item.cname} AS DECIMAL) &＃61; #{item.value}

FIND_IN_SET( #{v},${item.cname} )

${item.cname} -> &＃39;$[*].name&＃39; like &＃39;%${v}%&＃39;

JSON_CONTAINS( ${item.cname} ->&＃39;$[*].name&＃39; , &＃39;"${v}"&＃39;, &＃39;$&＃39;)

${item.cname} -> &＃39;$[*].name&＃39; like &＃39;%${v}%&＃39;

JSON_CONTAINS( ${item.cname} ->&＃39;$[*].name&＃39; , &＃39;"${v}"&＃39;, &＃39;$&＃39;)

${item.cname} like &＃39;%${item.value}%&＃39;

${item.cname} &＃61; #{item.value}

LIMIT #{pageNum}, #{pageSize}

) base

LEFT JOIN

(SELECT * from worksheet_data_table_data where data_id &＃61; #{dataId}) t

base.id &＃61; t.row_id

LEFT JOIN

dept_user u

base.create_by &＃61; u.id

LEFT JOIN

dept_user u2

base.update_by &＃61; u2.id

t.col_name &＃61; #{item.tableName}

AND

STR_TO_DATE(val -> &＃39;$.${item.cname}&＃39;,&＃39;"%Y-%m-%d %H:%i:%s"&＃39;) between #{item.value} AND date_add(#{item.endValue}, interval 1 day)

STR_TO_DATE(val -> &＃39;$.${item.cname}&＃39;,&＃39;"%Y-%m-%d %H:%i:%s"&＃39;) between #{item.value} AND date_add(#{item.value}, interval 1 day)

CAST(val -> &＃39;$."${item.cname}"&＃39; AS DECIMAL) BETWEEN #{item.value} AND #{item.endValue}

CAST(val -> &＃39;$."${item.cname}"&＃39; AS DECIMAL) &＃61; #{item.value}

val -> &＃39;$.${item.cname}&＃39; like &＃39;%,${v},%&＃39;

val -> &＃39;$."${item.cname}"&＃39; like &＃39;%${v}%&＃39;

val -> &＃39;$.${item.cname}&＃39; like &＃39;%${item.value}%&＃39;

JSON_CONTAINS( val ->&＃39;$.${item.cname}&＃39; , &＃39;"${item.value}"&＃39;, &＃39;$&＃39;)

SELECT

count( DISTINCT base.id ) count

FROM

worksheet_data_${dataId} base

LEFT JOIN

(SELECT * from worksheet_data_table_data where data_id &＃61; #{dataId}) c

c.row_id &＃61; base.id

DATE_FORMAT(${item.cname} , ${item.dateFormat} ) BETWEEN DATE_FORMAT( #{item.value}, ${item.dateFormat} ) AND DATE_FORMAT( #{item.endValue}, ${item.dateFormat} )

DATE_FORMAT( ${item.cname}, ${item.dateFormat} ) &＃61; DATE_FORMAT( #{item.value}, ${item.dateFormat} )

CAST(${item.cname} AS DECIMAL) BETWEEN #{item.value} AND #{item.endValue}

CAST(${item.cname} AS DECIMAL) &＃61; #{item.value}

FIND_IN_SET( #{v},${item.cname} )

${item.cname} -> &＃39;$[*].name&＃39; like &＃39;%${v}%&＃39;

JSON_CONTAINS( ${item.cname} ->&＃39;$[*].name&＃39; , &＃39;"${v}"&＃39;, &＃39;$&＃39;)

${item.cname} -> &＃39;$[*].name&＃39; like &＃39;%${v}%&＃39;

JSON_CONTAINS( ${item.cname} ->&＃39;$[*].name&＃39; , &＃39;"${v}"&＃39;, &＃39;$&＃39;)

${item.cname} like &＃39;%${item.value}%&＃39;

${item.cname} &＃61; #{item.value}

ANDc.col_name &＃61; #{item.tableName}

AND

STR_TO_DATE(val -> &＃39;$.${item.cname}&＃39;,&＃39;"%Y-%m-%d %H:%i:%s"&＃39;) between #{item.value} AND date_add(#{item.endValue}, interval 1 day)

STR_TO_DATE(val -> &＃39;$.${item.cname}&＃39;,&＃39;"%Y-%m-%d %H:%i:%s"&＃39;) between #{item.value} AND date_add(#{item.value}, interval 1 day)

CAST(val -> &＃39;$."${item.cname}"&＃39; AS DECIMAL) BETWEEN #{item.value} AND #{item.endValue}

CAST(val -> &＃39;$."${item.cname}"&＃39; AS DECIMAL) &＃61; #{item.value}

val -> &＃39;$.${item.cname}&＃39; like &＃39;%,${v},%&＃39;

val -> &＃39;$."${item.cname}"&＃39; like &＃39;%${v}%&＃39;

val -> &＃39;$.${item.cname}&＃39; like &＃39;%${item.value}%&＃39;

JSON_CONTAINS( val ->&＃39;$.${item.cname}&＃39; , &＃39;"${item.value}"&＃39;, &＃39;$&＃39;)

View Code

2.Mapper.java

List>pageFind(WorksheetDataSaveBean queryBean);

Long count(WorksheetDataSaveBean queryBean);

3.入参数据结构

public classWorksheetDataSaveBean {privateLong dataId;privateLong rowId;private List list; //入参集合

private List resultList;//结果列集合要返回哪些列信息

private List slaveList;//子表单查询条件

private Integer pageNum &＃61; 0;private Integer pageSize &＃61; 10;

View Code

public classWorksheetData {public static final Integer DATE_UNIT_SECOND &＃61; 1;public static final Integer DATE_UNIT_MINUTE &＃61; 2;public static final Integer DATE_UNIT_HOUR &＃61; 3;public static final Integer DATE_UNIT_DAY &＃61; 4;public static final Integer DATE_UNIT_MONTH &＃61; 5;public static final Integer DATE_UNIT_YEAR &＃61; 6;public static final String RELATED_OPERATOR_AND &＃61; "AND";public static final String RELATED_OPERATOR_OR &＃61; "OR";privateLong id;privateString cname;privateString value;private String tableName;//子表单cname 对应的子表单列在主表单中的列名例如&＃xff1a;table_0 table_1

private String operator &＃61; "equals";//操作符 [equals] / [between and] / [like] /

private String reOperator &＃61; RELATED_OPERATOR_AND;//查询条件[条件内] 关联符 AND(默认) OR 提供给select/checkbox/dept-user/dept-base使用

private String endValue;//区间操作结束区间值提供给date/input-number 字段使用

private Integer dateUnit &＃61; DATE_UNIT_DAY;//按秒、分、时、天(默认)、月、年提供给date字段查询使用

privateString dateFormat;private List valueList;//对checkbox、select 提供多值查询功能

publicLong getId() {returnid;

}public voidsetId(Long id) {this.id &＃61;id;

}publicString getCname() {returncname;

}public voidsetCname(String cname) {this.cname &＃61;cname;

}publicString getValue() {returnvalue;

}public voidsetValue(String value) {this.value &＃61;value;

initValueList();

}publicString getOperator() {returnoperator;

}public voidsetOperator(String operator) {this.operator &＃61;operator;

}publicString getEndValue() {returnendValue;

}public voidsetEndValue(String endValue) {this.endValue &＃61;endValue;

}publicString getTableName() {returntableName;

}public voidsetTableName(String tableName) {this.tableName &＃61;tableName;

}publicString getReOperator() {returnreOperator;

}public voidsetReOperator(String reOperator) {this.reOperator &＃61;reOperator;

}publicInteger getDateUnit() {returndateUnit;

}public voidsetDateUnit(Integer dateUnit) {this.dateUnit &＃61;dateUnit;

initDateFormat();

}publicString getDateFormat() {returndateFormat;

}public voidsetDateFormat(String dateFormat) {this.dateFormat &＃61;dateFormat;

}public ListgetValueList() {returnvalueList;

}public void setValueList(ListvalueList) {this.valueList &＃61;valueList;

}privateString initDateFormat(){

dateFormat&＃61; "&＃39;%Y-%m-%d %H:%i:%S&＃39;";switch(dateUnit){case 1:dateFormat &＃61; "&＃39;%Y-%m-%d %H:%i:%S&＃39;";break;case 2:dateFormat &＃61; "&＃39;%Y-%m-%d %H:%i&＃39;";break;case 3:dateFormat &＃61; "&＃39;%Y-%m-%d %H&＃39;";break;case 4:dateFormat &＃61; "&＃39;%Y-%m-%d&＃39;";break;case 5:dateFormat &＃61; "&＃39;%Y-%m&＃39;";break;case 6:dateFormat &＃61; "&＃39;%Y&＃39;";break;default:dateFormat &＃61; "&＃39;%Y-%m-%d&＃39;";

}returndateFormat;

}private voidinitValueList(){

List list &＃61; null;if (this.cname.contains("select")|| this.cname.contains("checkbox")|| this.cname.contains("dept-user")|| this.cname.contains("dept-base")){

String[] split&＃61; this.value.split(",");

list&＃61;Arrays.asList(split);

}this.valueList &＃61;list;

}

View Code

4.controller

&＃64;RequestMapping(value &＃61; "/pageFindTableData",method &＃61; RequestMethod.POST,name&＃61;"表单数据分页全字段查询")public PageResultBean>pageFindTableData(&＃64;RequestBody WorksheetDataSaveBean bean){

PageResultBean> res &＃61; new PageResultBean<>();

Long dataId&＃61;bean.getDataId();int pageNum &＃61;bean.getPageNum();int pageSize &＃61;bean.getPageSize();

pageNum&＃61; pageNum *pageSize;

bean.setPageNum(pageNum);/*** 1.表名验证 &＃43; 列名验证*/

if (dataId !&＃61; null){

String tableName&＃61; DDLCreater.TABLE_NAME&＃43;dataId;//表名验证

String exist &＃61;mapper.checkTableExist(tableName);if(StringUtils.isNotBlank(exist)){//列名验证

boolean flag &＃61; true;

List paramList &＃61;bean.getList();if (paramList !&＃61; null && paramList.size() > 0){

flag&＃61; checkColName(dataId,paramList,true);

}/*** 2.区分主表子表查询条件 &＃43; DB查询 &＃43; 组装结果集*/

if(flag){//主子拆分

diffSlaveList(bean);//DB查询

List> maps &＃61;tableDataMapper.pageFind(bean);//组装结果

WorksheetPageFindMap map &＃61; newWorksheetPageFindMap();

List> result &＃61;map.dealMap(maps);//返回

res.initTrue(result,tableDataMapper.count(bean));

}else{

res.initFalse("列名不合法");

}

}else{

res.initFalse("数据表不存在");

}

}else{

res.initFalse("必填参数缺失");

}returnres;

}

View Code

检查表是否存在的sql

SELECT

table_name

FROM

information_schema.TABLES

WHERE

table_name &＃61; #{tableName};

View Code

列名检查以及区分主表和子表的查询条件[因为主表是正常数据&＃xff0c;子表是JSON数据&＃xff0c;查询方式不同&＃xff0c;因此需要区分处理]

/*** 列名合法性检查

* [并处理列名加上&＃96;&＃96;符号]

* &＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;处理列名注意&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;

* 只有主表单字段需要处理列名

* 子表单查询字段列名无需处理【json中查询字段不能带 &＃96;&＃96;查询】

*&＃64;paramdataId dataId

*&＃64;paramparamList 入参列名集合

*&＃64;paramincludeSlaveTable 是否包含子表单列

*&＃64;return列名是否合法*/

private boolean checkColName(Long dataId,List paramList,booleanincludeSlaveTable){int size &＃61;paramList.size();

WorksheetColBase check&＃61; newWorksheetColBase();

check.setDataid(dataId);

check.setState(includeSlaveTable? null: 0);

List byDataIdCheckList &＃61;mapper.findByDataId(check);for(WorksheetData data : paramList) {

String colName&＃61;data.getCname();

String tableName&＃61;data.getTableName();for(WorksheetColBase worksheetColBase : byDataIdCheckList) {if(colName.equals(worksheetColBase.getColName())){

String tableColName&＃61;worksheetColBase.getTableColName();if (tableName &＃61;&＃61; null){if (tableName &＃61;&＃61;tableColName){//处理列名

data.setCname("&＃96;"&＃43;colName&＃43;"&＃96;");

size--;

}

}else{if(tableName.equals(tableColName)){

size--;

}

}return size &＃61;&＃61; 0 ? true : false;

}/*** 区分子表单查询条件和主表查询条件

*&＃64;parambean*/

private voiddiffSlaveList(WorksheetDataSaveBean bean){

List list &＃61; bean.getList(); //入参查询集合

if (list !&＃61; null){

List slaveList &＃61; new ArrayList<>(); //子表单查询集合

for (int i &＃61; 0; i

WorksheetData data&＃61;list.get(i);

String tableName&＃61;data.getTableName();if (tableName !&＃61; null){

slaveList.add(data);

list.remove(data);

i--;

}

bean.setSlaveList(slaveList);

}

View Code

组装数据集的工具类

public classWorksheetPageFindMap {private Map> resultMap &＃61; new HashMap<>();private Long rowId;//行ID

private Map rowMap;//行Map

private List> slaveList;//子表单List

private Map slaveMap;//子表单Map

public List> dealMap(List>list){for (MapoldMap : list) {

rowMap&＃61; new HashMap<>();

slaveMap&＃61; new HashMap<>();

Set keySet &＃61;oldMap.keySet();for(String key : keySet) {

String value&＃61;String.valueOf(oldMap.get(key));if (key.equals("id")){

rowId&＃61;Long.valueOf(value);

init();

append(key,rowId);

}else{if (key.contains("slaveTable_")){

dealSlaveMap(key.split("slaveTable_")[1],value);

}else{if (key.contains("date")){

value&＃61; value.split("\\.")[0];

}

append(key,value);

}

dealSlaveList();

}

List> resultList &＃61; new ArrayList<>();for (MapstringObjectMap : resultMap.values()) {

resultList.add(stringObjectMap);

}returnresultList;

}//初始化行方法

private voidinit(){

Map oldRowMap &＃61;resultMap.get(rowId);if (oldRowMap !&＃61; null){

List> oldSlaveList &＃61; (List) oldRowMap.get("table");if (oldSlaveList !&＃61; null){if (rowMap.get("table") &＃61;&＃61; null){

slaveList&＃61; new ArrayList<>();

}

slaveList.addAll(oldSlaveList);

append("table",slaveList);

}

resultMap.put(rowId,rowMap);

}//行数据追加方法

private voidappend(String key,Object value){

rowMap.put(key,value);

}//子表单集合初始化

private voiddealSlaveMap(String key,String value){

slaveMap.put(key,value);

}//子表单List 处理

private voiddealSlaveList(){//说明有子表单数据

if (slaveMap.size() > 0){

slaveList&＃61; (List) rowMap.get("table");if (slaveList &＃61;&＃61; null){

slaveList&＃61; new ArrayList<>();

append("table",slaveList);

}

slaveList.add(slaveMap);

}

View Code

最后conut &＃xff0c;拿到total&＃xff0c;一起返回结果即可

返回结果集

mysql多条件分页查询_【mysql】mybatis实现主从表leftjoin1:n一对多分页查询主表从表都有查询条件【mybatis】count统计+JSON查询...

org.eclipse.jetty.io.AbstractConnection.onFillInterestedFailed()方法的使用及代码示例

【JVM技术专题】深入分析CG管理和原理查缺补漏「番外篇」

JavaBean和Map 转换用反射方法实现

JavaScript对象的打印内容?(复制) - Print content of JavaScript object? [duplicate]

Java SimpleDateFormat详细介绍

CAS 介绍单点登录认证系统

com.jme3.math.Vector4f.hashCode()方法的使用及代码示例

org.assertj.core.api.AbstractCharSequenceAssert.hasSize()方法的使用及代码示例

mysql join 算法_【MySQL】之join算法详解

准备面试三

第十四篇：JAVA之集合框架

常用Python库整理

聊聊nacos ServiceManager的removeInstance

vscode 新版eslint自动修复_vscode下的eslint自动修复配置

实战分析SpringBoot整合JSON，面试题附答案