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

需求：

========================================

1.主从表数据 是 1：m

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

3.最后查询结果 需要分页，并统计总数

注意：

=======================================

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

解决方法：

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

pageNum=0PageSize=10

实际入参应该是处理过的

pageNum = pageNum*10pageSize= 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

ON

base.id = t.row_id

WHERE

2.mapper.java应该这么写

List pageFind(WorksheetDataSaveBean queryBean);

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

1.mapper.xml中应该这么写

[下面的示例中：因为我不确定返回的字段，所以用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

ON

base.id = t.row_id

WHERE

2.mapper.java应该这么写

List> pageFind(WorksheetDataSaveBean queryBean);

===================================count=============================================

count 是什么？count就是页面的 总共total条数

1.mapper.xml应该这么写

SELECT

count( DISTINCT base.id ) count

FROM

worksheet_data_${dataId} base

LEFT JOIN

worksheet_data_table_data c

ON

c.row_id = base.id

-- 拼接条件的地方

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结果集 size=主size*子size 【因此，结果集需要将子表数据封装进主表数据集 java处理】

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

代码参考：

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} ) = DATE_FORMAT( #{item.value}, ${item.dateFormat} )

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

CAST(${item.cname} AS DECIMAL) = #{item.value}

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

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

${item.cname} -> '$[*].name' like '%${v}%'

JSON_CONTAINS( ${item.cname} ->'$[*].name' , '"${v}"', '$')

${item.cname} -> '$[*].name' like '%${v}%'

JSON_CONTAINS( ${item.cname} ->'$[*].name' , '"${v}"', '$')

${item.cname} like '%${item.value}%'

${item.cname} = #{item.value}

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

t.col_name = #{item.tableName}

AND

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

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

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

CAST(val -> '$."${item.cname}"' AS DECIMAL) = #{item.value}

val -> '$.${item.cname}' like '%,${v},%'

val -> '$.${item.cname}' like '%,${v},%'

val -> '$."${item.cname}"' like '%${v}%'

val -> '$."${item.cname}"' like '%${v}%'

val -> '$.${item.cname}' like '%${item.value}%'

JSON_CONTAINS( val ->'$.${item.cname}' , '"${item.value}"', '$')

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

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} ) = DATE_FORMAT( #{item.value}, ${item.dateFormat} )

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

CAST(${item.cname} AS DECIMAL) = #{item.value}

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

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

${item.cname} -> '$[*].name' like '%${v}%'

JSON_CONTAINS( ${item.cname} ->'$[*].name' , '"${v}"', '$')

${item.cname} -> '$[*].name' like '%${v}%'

JSON_CONTAINS( ${item.cname} ->'$[*].name' , '"${v}"', '$')

${item.cname} like '%${item.value}%'

${item.cname} = #{item.value}

ANDc.col_name = #{item.tableName}

AND

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

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

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

CAST(val -> '$."${item.cname}"' AS DECIMAL) = #{item.value}

val -> '$.${item.cname}' like '%,${v},%'

val -> '$.${item.cname}' like '%,${v},%'

val -> '$."${item.cname}"' like '%${v}%'

val -> '$."${item.cname}"' like '%${v}%'

val -> '$.${item.cname}' like '%${item.value}%'

JSON_CONTAINS( val ->'$.${item.cname}' , '"${item.value}"', '$')

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 = 0;private Integer pageSize = 10;

View Code

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

private String operator = "equals";//操作符 [equals] / [between and] / [like] /

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

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

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

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

publicLong getId() {returnid;

}public voidsetId(Long id) {this.id =id;

}publicString getCname() {returncname;

}public voidsetCname(String cname) {this.cname =cname;

}publicString getValue() {returnvalue;

}public voidsetValue(String value) {this.value =value;

initValueList();

}publicString getOperator() {returnoperator;

}public voidsetOperator(String operator) {this.operator =operator;

}publicString getEndValue() {returnendValue;

}public voidsetEndValue(String endValue) {this.endValue =endValue;

}publicString getTableName() {returntableName;

}public voidsetTableName(String tableName) {this.tableName =tableName;

}publicString getReOperator() {returnreOperator;

}public voidsetReOperator(String reOperator) {this.reOperator =reOperator;

}publicInteger getDateUnit() {returndateUnit;

}public voidsetDateUnit(Integer dateUnit) {this.dateUnit =dateUnit;

initDateFormat();

}publicString getDateFormat() {returndateFormat;

}public voidsetDateFormat(String dateFormat) {this.dateFormat =dateFormat;

}public ListgetValueList() {returnvalueList;

}public void setValueList(ListvalueList) {this.valueList =valueList;

}privateString initDateFormat(){

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

}returndateFormat;

}private voidinitValueList(){

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

String[] split= this.value.split(",");

list=Arrays.asList(split);

}this.valueList =list;

}

}

View Code

4.controller

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

PageResultBean> res = new PageResultBean<>();

Long dataId=bean.getDataId();int pageNum =bean.getPageNum();int pageSize =bean.getPageSize();

pageNum= pageNum *pageSize;

bean.setPageNum(pageNum);/*** 1.表名验证 + 列名验证*/

if (dataId != null){

String tableName= DDLCreater.TABLE_NAME+dataId;//表名验证

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

boolean flag = true;

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

flag= checkColName(dataId,paramList,true);

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

if(flag){//主子拆分

diffSlaveList(bean);//DB查询

List> maps =tableDataMapper.pageFind(bean);//组装结果

WorksheetPageFindMap map = newWorksheetPageFindMap();

List> result =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 = #{tableName};

View Code

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

/*** 列名合法性检查

* [并处理 列名 加上``符号]

*

* =======处理列名注意=======

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

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

*

*

*@paramdataId dataId

*@paramparamList 入参列名集合

*@paramincludeSlaveTable 是否包含子表单列

*@return列名是否合法*/

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

WorksheetColBase check= newWorksheetColBase();

check.setDataid(dataId);

check.setState(includeSlaveTable? null: 0);

List byDataIdCheckList =mapper.findByDataId(check);for(WorksheetData data : paramList) {

String colName=data.getCname();

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

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

data.setCname("`"+colName+"`");

size--;

}

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

size--;

}

}

}

}

}return size == 0 ? true : false;

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

*@parambean*/

private voiddiffSlaveList(WorksheetDataSaveBean bean){

List list = bean.getList(); //入参查询集合

if (list != null){

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

for (int i = 0; i < list.size(); i++) {

WorksheetData data=list.get(i);

String tableName=data.getTableName();if (tableName != null){

slaveList.add(data);

list.remove(data);

i--;

}

}

bean.setSlaveList(slaveList);

}

}

View Code

组装数据集的工具类

public classWorksheetPageFindMap {private Map> resultMap = 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= new HashMap<>();

slaveMap= new HashMap<>();

Set keySet =oldMap.keySet();for(String key : keySet) {

String value=String.valueOf(oldMap.get(key));if (key.equals("id")){

rowId=Long.valueOf(value);

init();

append(key,rowId);

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

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

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

value= value.split("\\.")[0];

}

append(key,value);

}

}

}

dealSlaveList();

}

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

resultList.add(stringObjectMap);

}returnresultList;

}//初始化行方法

private voidinit(){

Map oldRowMap =resultMap.get(rowId);if (oldRowMap != null){

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

slaveList= 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= (List) rowMap.get("table");if (slaveList == null){

slaveList= new ArrayList<>();

append("table",slaveList);

}

slaveList.add(slaveMap);

}

}

}

View Code

最后conut ，拿到total，一起返回结果即可

返回结果集