怎么在mybatisplus 中使用SQL拦截器实现关联查询功能

本篇文章为大家展示了怎么在mybatisplus 中使用sql拦截器实现关联查询功能，内容简明扼要并且容易理解，绝对能使你眼前一亮，通过这篇文章的详细介绍希望你能有所收获。

环境信息

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;@Slf4jpublic class JoinBuilder {    private StringBuilder sb = new StringBuilder();        private String[] selectFields;        private String joinTable;        Set<String> 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<String> getSelectFields() {        return set;    }    public String getMainTable() {        return mainTable;    }    public String getSubTable() {        return this.joinTable;    }        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) {                return ((PlainSelect) ((Select) parse).getSelectBody()).getJoins().get(0);            }            return null;        } catch (JSQLParserException e) {            log.warn(sql);            e.printStackTrace();        }        return null;    }        public static class CaseBuilder {                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;        }                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;        }                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;        }                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;        }                @Deprecated        public CaseBuilder gt(String left, Object right) {            sb.append(left).append(GT).append(right);            return this;        }                @Deprecated        public CaseBuilder gtEq(String left, Object right) {            sb.append(left).append(GT_EQ).append(right);            return this;        }                @Deprecated        public CaseBuilder lt(String left, Object right) {            sb.append(left).append(LT).append(right);            return this;        }                @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;public claSSMyQueryWrapper<T> extends QueryWrapper<T> {        private final List<JoinBuilder> joinBuilder = new ArrayList<>();        @Override    protected String columnToString(String column) {        return StringUtils.camelToUnderline(column);    }    public static<T> MyQueryWrapper<T> query(){        return new MyQueryWrapper<T>();    }        public  MyQueryWrapper<T> addJoin(JoinBuilder builder){        this.joinBuilder.add(builder);        return this;    }    public List<JoinBuilder> getJoinBuilder() {        return joinBuilder;    }        public QueryWrapper<T> sort(List<Sort> 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;public class JoinQueryInterceptor extends JsqlParserSupport implements InnerInterceptor {        static ThreadLocal<List<JoinBuilder>> joinBuilderThreadLocal = new ThreadLocal<>();            @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<JoinBuilder> 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<JoinBuilder> 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<JoinBuilder> joinBuilders) {        List<SelectItem> selectItems = selectBody.getSelectItems();        Expression where = selectBody.getWhere();        List<Join> joins = new ArrayList<>();        for (int i = 0; i < joinBuilders.size(); i++) {            JoinBuilder joinBuilder = joinBuilders.get(i);            Join builderJoin = joinBuilder.getJoin();            Set<String> 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<String> selectFields, List<SelectItem> 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);            }        }    }            private void setOnCase(Object on, Table subTable, Set<String> 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<String> 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);        }else 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<MenuPo> 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：

SELECTmainjointable.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 FROMlsc_menu AS mainjointableLEFT 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 )) WHEREmainjointable.delete_flag = 0 AND (subjointable0.user_name = ?)

上述内容就是怎么在mybatisplus 中使用SQL拦截器实现关联查询功能，你们学到知识或技能了吗？如果还想学到更多技能或者丰富自己的知识储备，欢迎关注编程网精选频道。