mybatisplus 的SQL拦截器实现关联查询功能

mybatisplus 的SQL-实现关联查询功能

由于项目中经常会使用到一些简单地关联查询，但是mybatisplus还不支持关联查询，不过在看官方文档的时候发现了mybatisplus的SQL-（其实也是mybatis的）就想着能不能在SQL执行的时候做一些处理以至于可以支持关联查询，于是就动手开始了，目前还只是一个初步的demo，但是一些基本的关联查询功能经过验证是没有问题的

环境信息

jdk： 1.8

springboot： 2.3.4.RELEASE

mybatisplus： 3.4.2

lombok：1.18.12

代码设计

代码涉及四个关键的类：

JoinBuilder

这是一个建造者类，主要适用于生成关联查询的语句

CaseBuilder

这也是一个建造者类，主要是用来生成连接查询的条件语句

MyQueryWrapper

这是查询器，这里继承了官方的QueryWrapper，然后扩展了一些功能。添加了关联查询的功能

JoinQueryInterceptor

这是SQL-，在上面使用自定义的查询器添加了关联查询之后就可以使用SQL-进行sql的构造

类关系图如下：

代码实现

实现连接条件构造器

package com.jenkin.common.config;

import cn.hutool.core.util.ArrayUtil;

import com.baomidou.mybatisplus.core.toolkit.StringUtils;

import lombok.extern.slf4j.Slf4j;

import net.sf.jsqlparser.JSQLParserException;

import net.sf.jsqlparser.parser.CCJSqlParserManager;

import net.sf.jsqlparser.statement.select.Join;

import net.sf.jsqlparser.statement.select.PlainSelect;

import net.sf.jsqlparser.statement.select.Select;

import java.io.StringReader;

import java.util.HashSet;

import java.util.Set;

/**

* @author jenkin

* @Since 2021年4月12日14:45:58

* @Version 1.0

* @Description : 关联查询join构造器

*/

@Slf4j

public class JoinBuilder {

private StringBuilder sb = new StringBuilder();

/**

* 关联表里面的查询字段，比如要查询关联的用户表里面的用户名称

*/

private String[] selectFields;

/**

* 关联表

*/

private String joinTable;

/**

* 查询字段去重

*/

Set set = new HashSet<>();

/**

* 主表

*/

private String mainTable;

/**

* 关联类型

*/

private String joinType;

private static final String LEFT_BRACKET = " ( ";

private static final String RIGHT_BRACKET = " ) ";

private static final String AND = " AND ";

private static final String OR = " OR ";

/**

* 左连接

*/

public static final String LEFT = " left ";

/**

* 右连接

*/

public static final String RIGHT = " right ";

/**

* 内连接

*/

public static final String INNER = " inner ";

public JoinBuilder selectField(String... fields) {

this.selectFields = fields;

if (!ArrayUtil.isEmpty(this.selectFields)) {

for (int i = 0; i < this.selectFields.length; i++) {

this.selectFields[i] = StringUtils.camelToUnderline(this.selectFields[i]);

set.add(this.selectFields[i].toUpperCase());

}

}

return this;

}

public Set getSelectFields() {

return set;

}

public String getMainTable() {

return mainTable;

}

public String getSubTable() {

return this.joinTable;

}

/**

* @param joinType 关联类型 JoinBuilder.LEFT,JoinBuilder.RIGHT,JoinBuilder.INNER

* @param mainTable 主表

* @param joinTable 关联表

* @return

*/

public JoinBuilder join(String joinType, String mainTable, String joinTable) {

mainTable = StringUtils.camelToUnderline(mainTable);

;

joinTable = StringUtils.camelToUnderline(joinTable);

;

this.joinTable = joinTable;

this.mainTable = mainTable;

this.joinType = joinType;

return this;

}

public static JoinBuilder build() {

return new JoinBuilder();

}

public JoinBuilder and() {

sb.append(AND);

return this;

}

public JoinBuilder or() {

sb.append(OR);

return this;

}

public StringBuilder getSql() {

return sb;

}

public JoinBuilder on(CaseBuilder builder) {

sb.append(LEFT_BRACKET).append(builder.getSql()).append(RIGHT_BRACKET);

return this;

}

public Join getJoin() {

CCJSqlParserManager pm = new CCJSqlParserManager();

String sql = "select * from " + mainTable + " " + joinType + " join " + joinTable + " on " + sb;

try {

net.sf.jsqlparser.statement.Statement parse = pm.parse(new StringReader(sql));

if (parse instanceof Select) {

returylaymqJQsn ((PlainSelect) ((Select) parse).getSelectBody()).getJoins().get(0);

}

return null;

} catch (JSQLParserException e) {

log.warn(sql);

e.printStackTrace();

}

return null;

}

/**

* @author jenkin

* @Since 2021年4月12日14:45:58

* @Version 1.0

* @Description : 条件构造器，局限于关联查询

*/

public static class CaseBuilder {

/**

* SQL语句

*/

private StringBuilder sb = new StringBuilder();

private static final String LEFT_BRACKET = " ( ";

private static final String RIGHT_BRACKET = " ) ";

private static final String EQ = "=";

private static final String NE = "<>";

private static final String GT = ">";

private static final String LT = "<";

private static final String GT_EQ = ">=";

private static final String LT_EQ = "<=";

private static final String AND = " AND ";

private static final String OR = " OR ";

public static CaseBuilder build() {

return new CaseBuilder();

}

public StringBuilder getSql() {

return sb;

}

/**

* 把条件表达式用括号包裹起来

*

* @param builder

* @return

*/

public CaseBuilder brackets(CaseBuilder builder) {

sb.append(LEFT_BRACKET).append(builder.sb).append(RIGHT_BRACKET);

return this;

}

public CaseBuilder and() {

sb.append(AND);

return this;

}

public CaseBuilder or() {

sb.append(OR);

return this;

}

/**

* 规定左侧为主表的列

* ，右侧为从表的列，不可以写反

* 注意，在使用定值查询的时候 例如 on a.name = b.name and age = 1

* 这个时候一样要遵循左边为主表，右边为关联表的规则.

* 例如

*

* 1、 and里面的条件 age字段是存在在主表里面的 那么就写成 eq("age",1)

* 2、如果age字段是在关联表里面的，那么应该写成 eq(1,"age")

*

* 其他的条件语句例如，ne，gt，lt等等也适用这个逻辑

*

* @param left 左侧列名称

* @param right 右侧列名称

* @return

*/

public CaseBuilder eq(Object left, Object right) {

if (left instanceof String) {

left = StringUtils.camelToUnderline((String) left);

}

if (right instanceof String) {

right = StringUtils.camelToUnderline(String.valueOf(right));

}

sb.append(left).append(EQ).append(right);

return this;

}

/**

* 规定左侧为主表的列

* ，右侧为从表的列，不可以写反

*

* @param left 左侧列名称

* @param right 右侧列名称

* @return

*/

public CaseBuilder ne(String left, Object right) {

left = StringUtils.camelToUnderline(left);

if (right instanceof String) {

right = StringUtils.camelToUnderline(String.valueOf(right));

}

sb.append(left).append(NE).append(right);

return this;

}

/**

* 关联查询一般是列关联，如果条件里面有值等式，要做特殊处理，目前还不支持

*

* @param left

* @param right

* @return

*/

@Deprecated

public CaseBuilder gt(String left, Object right) {

sb.append(left).append(GT).append(right);

return this;

}

/**

* 关联查询一般是列关联，如果条件里面有值等式，要做特殊处理，目前还不支持

*

* @param left

* @param right

* @return

*/

@Deprecated

public CaseBuilder gtEq(String left, Object right) {

sb.append(left).append(GT_EQ).append(right);

return this;

}

/**

* 关联查询一般是列关联，如果条件里面有值等式，要做特殊处理，目前还不支持

*

* @param left

* @param right

* @return

*/

@Deprecated

public CaseBuilder lt(String left, Object right) {

sb.append(left).append(LT).append(right);

return this;

}

/**

* 关联查询一般是列关联，如果条件里面有值等式，要做特殊处理，目前还不支持

*

* @param left

* @param right

* @return

*/

@Deprecated

public CaseBuilder ltEq(String left, Object right) {

sb.append(left).append(LT_EQ).append(right);

return this;

}

}

}

定制化QueryWrapper

在这个定制化的查询器里面添加了一个addJoin的方法用来添加关联查询

package com.jenkin.common.config;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;

import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;

import com.baomidou.mybatisplus.core.toolkit.StringUtils;

import com.jenkin.common.entity.qos.Sort;

import java.util.ArrayList;

import java.util.List;

/**

* @author ：jenkin

* @date ：Created at 2020/3/13 12:07

* @description：条件构造器，重写字符串转换方法

* @modified By：

* @version: 1.0

*/

public class

MyQueryWrapper extends QueryWrapper {

/**

* 关联查询构造器

*/

private final List joinBuilder = new ArrayList<>();

/**

* 获取 columnName

*

* @param column

*/

@Override

protected String columnToString(String column) {

return StringUtils.camelToUnderline(column);

}

public static MyQueryWrapper query(){

return new MyQueryWrapper();

}

/**

* 关联查询构造

* @param builder

* @return

*/

public MyQueryWrapper addJoin(JoinBuilder builder){

this.joinBuilder.add(builder);

return this;

}

public List getJoinBuilder() {

return joinBuilder;

}

/**

* 排序

* @param sorts

* @return

*/

public QueryWrapper sort(List sorts){

if(!CollectionUtils.isEmpty(sorts)){

sorts.forEach(item->{

orderBy(item.getSortField()!=null,"asc".equals(item.getSortValue()),item.getSortField());

});

}

return this;

}

}

定义SQL-

通过自定义的SQL-去拦截我们写好的关联查询，然后生成对应的SQL

package com.jenkin.common.config;

import com.alibaba.nacos.client.naming.utils.CollectionUtils;

import com.baomidou.mybatisplus.core.toolkit.PluginUtils;

import com.baomidou.mybatisplus.extension.parser.JsqlParserSupport;

import com.baomidou.mybatisplus.extension.plugins.inner.InnerInterceptor;

import net.sf.jsqlparser.expression.Alias;

import net.sf.jsqlparser.expression.BinaryExpression;

import net.sf.jsqlparser.expression.Expression;

import net.sf.jsqlparser.expression.Parenthesis;

import net.sf.jsqlparser.expression.operators.relational.Between;

import net.sf.jsqlparser.expression.operators.relational.IsNullExpression;

import net.sf.jsqlparser.schema.Column;

import net.sf.jsqlparser.schema.Table;

import net.sf.jsqlparser.statement.delete.Delete;

import net.sf.jsqlparser.statement.insert.Insert;

import net.sf.jsqlparser.statement.select.*;

import net.sf.jsqlparser.statement.update.Update;

import org.apache.ibatis.binding.MapperMethod;

import org.apache.ibatis.executor.Executor;

import org.apache.ibatis.mapping.BoundSql;

import org.apache.ibatis.mapping.MappedStatement;

import org.apache.ibatis.session.ResultHandler;

import org.apache.ibatis.session.RowBounds;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.HashSet;

import java.util.List;

import java.util.Set;

/**

* @author jenkin

* @className JoinQueryInterceptor

* @description TODO

* @date 2021/4/12 14:58

*/

public class JoinQueryInterceptor extends JsqlParserSupport implements InnerInterceptor {

/**

* 保存我们的关联查询的上下文信息

*/

static ThreadLylaymqJQsocal> joinBuilderThreadLocal = new ThreadLocal<>();

/**

* 操作前置处理

*

* 改改sql啥的

*

* @param executor Executor(可能是代理对象)

* @param ms MappedStatement

* @param parameter parameter

* @param rowBounds rowBounds

* @param resultHandler resultHandler

* @param boundSql boundSql

*/

@Override

public void beforeQuery(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds,

ResultHandler resultHandler, BoundSql boundSql) throws SQLException {

if (parameter instanceof MapperMethod.ParamMap) {

for (Object value : ((MapperMethod.ParamMap) parameter).values()) {

if (value instanceof MyQueryWrapper) {

List joinBuilders = ((MyQueryWrapper) value).getJoinBuilder();

if(!CollectionUtils.isEmpty(joinBuilders)){

joinBuilderThreadLocal.set(joinBuilders);

try {

logger.debug("开始添加关联查询SQL");

String s = this.parserSingle(boundSql.getSql(), parameter);

logger.debug("加了关联查询的SQL ： "+ s);

PluginUtils.MPBoundSql mpBs = PluginUtils.mpBoundSql(boundSql);

mpBs.sql(s);

}finally {

joinBuilderThreadLocal.remove();

}

return;

}

}

}

}

}

/**

* 查询

*/

@Override

protected void processSelect(Select select, int index, String sql, Object obj) {

List joinBuilders = joinBuilderThreadLocal.get();

PlainSelect selectBody = (PlainSelect) select.getSelectBody();

if (selectBody.getFromItem().getAlias()==null) {

selectBody.getFromItem().setAlias(new Alias("mainjointable"));

}

setJoins(selectBody,joinBuilders);

}

private void setJoins(PlainSelect selectBody,List joinBuilders) {

List selectItems = selectBody.getSelectItems();

Expression where = selectBody.getWhere();

List joins = new ArrayList<>();

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

JoinBuilder joinBuilder = joinBuilders.get(i);

Join builderJoin = joinBuilder.getJoin();

Set selectFields = new HashSet<>(joinBuilder.getSelectFields());

Join join = new Join();

join.setLeft(builderJoin.isLeft());

join.setRight(builderJoin.isRight());

join.setInner(builderJoin.isInner());

Table table = new Table(joinBuilder.getSubTable());

table.setAlias( new Alias("subjointable"+i));

setSelectItems(table,selectFields,selectItems,selectBody);

join.setRightItem(table);

// Expression expression = getOnExpressionWithTable(joinBuilder);

Expression onExpression = joinBuilder.getJoin().getOnExpression();

selectFields = new HashSet<>(joinBuilder.getSelectFields());

setOnCase(onExpression,table,selectFields,(Table) selectBody.getFromItem(),false);

join.setOnExpression(onExpression);

joins.add(join);

selectFields = new HashSet<>(joinBuilder.getSelectFields());

setWhere(where,table,selectFields,(Table) selectBody.getFromItem());

}

selectBody.setJoins(joins);

}

// private Expression getOnExpressionWithTable(JoinBuilder joinBuilder) {

setWhere(joinBuilder.getJoin().getOnExpression(),);

// return joinBuilder.getJoin().getOnExpression();

// }

private void setSelectItems(Table table, Set selectFields, List selectItems, PlainSelect selectBody) {

for (SelectItem selectItem : selectItems) {

if (selectItem instanceof SelectExpressionItem) {

if (((SelectExpressionItem) selectItem).getExpression() instanceof Column && selectBody.getFromItem() instanceof Table) {

Column expression = (Column) ((SelectExpressionItem) selectItem).getExpression();

if (expression.getTable()==null&&selectFields.contains(expression.getColumnName().toUpperCase())){

expression.setTable(table);

selectFields.remove(expression.getColumnName().toUpperCase());

}else if(expression.getTable()==null){

expression.setTable((Table) selectBody.getFromItem());

}

}

}

}

if (!selectFields.isEmpty()){

for (String selectField : selectFields) {

SelectExpressionItem selectExpressionItem = new SelectExpressionItem();

Column column = new Column();

column.setTable(table);

column.setColumnName(selectField);

selectExpressionItem.setExpression(column);

selectItems.add(selectExpressionItem);

}

}

}

/**

*

* @param on

* @param subTable

* @param joinSelectFields

* @param sourceTable

* @param isLeft 是否是左侧列，如果是那么就是主表，如果false，那么就是关联表，如果为null，那么就需要根据join字段判断

*/

private void setOnCase(Object on, Table subTable, Set joinSelectFields , Table sourceTable,Boolean isLeft) {

if (on==null) {

return;

}

if (on instanceof Column) {

Column column = (Column) on;

if((column).getTable()==null &&isLeft!=null){

(column).setTable(isLeft?sourceTable:subTable);

}

if (isLeft==null&&column.getTable()==null){

(column).setTable(joinSelectFields.contains(column.getColumnName().toUpperCase())?subTable:sourceTable);

}

}else if (on instanceof BinaryExpression){

setOnCase(((BinaryExpression) on).getLeftExpression(),subTable,joinSelectFields,sourceTable,true);

setOnCase(((BinaryExpression) on).getRightExpression(),subTable,joinSelectFields,sourceTable,false);

}else if (on instanceof Parenthesis){

setOnCase(((Parenthesis) on).getExpression(),subTable,joinSelectFields,sourceTable,false);

}else if(on instanceof IsNullExpression){

setOnCase(((IsNullExpression) on).getLeftExpression(),subTable,joinSelectFields,sourceTable,null);

}else if (on instanceof Between){

setOnCase(((Between) on).getLeftExpression(),subTable,joinSelectFields,sourceTable,null);

}

//有其他条件再补充

}

private void setWhere(Object where, Table subTable, Set joinSelectFields , Table sourceTable) {

if (where==null) {

return;

}

if (where instanceof Column) {

Column column = (Column) where;

if((column).getTable()==null&&joinSelectFields.contains((column).getColumnName().toUpperCase())){

(column).setTable(subTable);

}else if((column).getTable()==null){

(column).setTable(sourceTable);

}

}else if (where instanceof BinaryExpression){

setWhere(((BinaryExpression) where).getLeftExpression(),subTable,joinSelectFields,sourceTable);

setWhere(((BinaryExpression) where).getRightExpression(),subTable,joinSelectFields,sourceTable);

}elsehttp:// if (where instanceof Parenthesis){

setWhere(((Parenthesis) where).getExpression(),subTable,joinSelectFields,sourceTable);

}else if(where instanceof IsNullExpression){

setWhere(((IsNullExpression) where).getLeftExpression(),subTable,joinSelectFields,sourceTable);

}else if(where instanceof Between){

setWhere(((Between) where).getLeftExpression(),subTable,joinSelectFields,sourceTable);

}

//有其他条件再补充

}

}

注入-

紧接着只需要在mybatisplus的配置文件里面注入这个-就可以了

使用示例

使用的过程我们分为两步：

添加字段到主表PO

如图，红框中的部分是我们添加的需要从其他表里面关联查询的字段，注意这些字段需要使用@TableField注解标注，并且 select字段和exist字段都要为false，不然会影响新增和修改操作

queryWrapper构造关联查询

把字段添加好之后就可以开始写关联查询了，下奶的示例应该是一个涵盖了大部分场景的示例了，多表关联，多条件关联，等等

MyQueryWrapper queryWrapper = new MyQueryWrapper<>();

//添加一个关联表查询，关联用户表

queryWrapper.addJoin(

JoinBuilder.build()

//查询用户表里面的用户名称和用户邮箱字段

.selectField(MenuPo.Fields.userName, MenuPo.Fields.userEmail)

//使用左连接关联

.join(JoinBuilder.LEFT, MenuPo.class, UserPo.class)

//设置关联条件

.on(JoinBuilder.CaseBuilder.build()

//主表的创建人字段等于关联表的用户编码字段

// 注意，在条件中默认是第一个参数为主表的字段，第二个参数为关联表的字段

.eq(BasePo.Fields.createdBy, UserPo.Fields.userCode)

)

//再添加一个关联查询，关联角色表

).addJoin(

JoinBuilder.build()

//查血角色表里面的角色名称

.selectField(MenuPo.Fields.roleName)

//左连接

.join(JoinBuilder.LEFT,MenuPo.class, RolePo.class)

//关联条件

.on(JoinBuilder.CaseBuilder.build()

//code等于角色code

.eq(MenuPo.Fields.code, RolePo.Fields.roleCode)

//并且

.and()

//括号

.brackets(

//parent =-1 or parent =1

JoinBuilder.CaseBuilder.build()

.eq(MenuPo.Fields.parent,-1)

.or()

.eq(MenuPo.Fields.parent,1)

)

)

//外层筛选条件，用户名=jenkin

).eq(MenuPo.Fields.userName,"jenkin");

//执行查询

menuService.list(queryWrapper);

可以在控制台看到执行的SQL：

SELECT

mainjointable.id,

mainjointable.NAME,

mainjointable.CODE,

mainjointable.parent,

mainjointable.menu_level,

mainjointable.permissions,

mainjointable.menu_url,

mainjointable.menu_icon,

mainjointable.menu_order,

mainjointable.menu_type,

mainjointable.delete_flag,

mainjointable.created_by,

mainjointable.creation_date,

mainjointable.last_update_date,

mainjointable.last_updated_by,

mainjointable.version_number,

subjointable0.USER_EMAIL,

subjointable0.USER_NAME,

subjointable1.ROLE_NAME

FROM

lsc_menu AS mainjointable

LEFT JOIN lsc_user AS subjointable0 ON ( mainjointable.created_by = subjointable0.user_code )

LEFT JOIN lsc_role AS subjointable1 ON (

mainjointable.CODE = subjointable1.role_code

AND ( mainjointable.parent = - 1 OR mainjointable.parent = 1 )

)

WHERE

mainjointable.delete_flag = 0

AND (

subjointable0.user_name = ?)

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。