阅读 225

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

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

大家都知道mybatisplus不支持关联查询，后来学习研究发现mybatisplus的SQL拦截器可以实现这一操作，下面小编给大家分享我的demo实现基本的关联查询功能没有问题，对mybatisplus关联查询相关知识感兴趣的朋友一起看看吧

由于项目中经常会使用到一些简单地关联查询，但是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的构造

类关系图如下：

在这里插入图片描述

代码实现

实现连接条件构造器

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
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<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;
    }
 
 
    /**
     * @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) {
                return ((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
         * 这个时候一样要遵循左边为主表，右边为关联表的规则.
         * 例如
         * <p>
         * 1、 and里面的条件 age字段是存在在主表里面的 那么就写成 eq("age",1)
         * 2、如果age字段是在关联表里面的，那么应该写成 eq(1,"age")
         * <p>
         * 其他的条件语句例如，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的方法用来添加关联查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
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<T> extends QueryWrapper<T> {
    /**
     * 关联查询构造器
     */
    private final List<JoinBuilder> joinBuilder = new ArrayList<>();
 
 
    /**
     * 获取 columnName
     *
     * @param column
     */
    @Override
    protected String columnToString(String column) {
        return StringUtils.camelToUnderline(column);
    }
 
 
    public static<T> MyQueryWrapper<T> query(){
        return new MyQueryWrapper<T>();
    }
 
 
    /**
     * 关联查询构造
    * @param builder
     * @return
     */
    public  MyQueryWrapper<T> addJoin(JoinBuilder builder){
        this.joinBuilder.add(builder);
        return this;
    }
 
    public List<JoinBuilder> getJoinBuilder() {
        return joinBuilder;
    }
    /**
     * 排序
     * @param sorts
     * @return
     */
    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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
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 ThreadLocal<List<JoinBuilder>> joinBuilderThreadLocal = new ThreadLocal<>();
     
    /**
     * 操作前置处理
     * <p>
     * 改改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<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);
            }
        }
    }
     
    /**
     *
     * @param on
     * @param subTable
     * @param joinSelectFields
     * @param sourceTable
     * @param isLeft 是否是左侧列，如果是那么就是主表，如果false，那么就是关联表，如果为null，那么就需要根据join字段判断
     */
    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构造关联查询

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
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：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
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 = ?)

到此这篇关于基于mybatisplus 的SQL拦截器实现关联查询的文章就介绍到这了

您可能感兴趣的文章:

  • mybatis-plus分页查询的实现示例

  • 完美解决MybatisPlus插件分页查询不起作用总是查询全部数据问题

  • MyBatis-Plus 分页查询以及自定义sql分页的实现

  • mybatis-plus QueryWrapper自定义查询条件的实现

  • MyBatis-Plus 如何实现连表查询的示例代码

  • 结合mybatis-plus实现简单不需要写sql的多表查询


原文链接：https://blog.csdn.net/youzi1394046585/article/details/117451377


文章分类
后端
文章标签
mybatisplus
sql
拦截器
功能
版权声明：本站是系统测试站点，无实际运营。本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 XXXXXXo@163.com 举报，一经查实，本站将立刻删除。
相关推荐