Mybatis 动态SQL

@Select 与 @SelectProvider 只是在定义注解的方式上有所不同, 一个是动态SQL一个是静态SQL。

1 @SelectProvider 使用

1 TestMapper.java

package com.xu.test.mybatis;

import java.util.List;

import org.apache.ibatis.annotations.SelectProvider;

/**
 * @author Administrator
 * @date 2022年11月26日12点09分
 */
public interface TestMapper {

    /**
     * 根据性别获取老师信息
     *
     * @param sex
     * @return
     * @date 2022年11月26日12点09分
     */
    @SelectProvider(type = Test.class, method = "list")
    List<Teacher> list(Integer sex);

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

2 Test.java

package com.xu.test.mybatis;

import org.apache.ibatis.jdbc.SQL;

/**
 * @author Administrator
 * @date 2022年11月26日12点09分
 */
public class Test {

    /**
     * 根据性别获取老师信息
     *
     * @param sex
     * @return
     * @date 2022年11月26日12点09分
     */
    public String list(Integer sex) {
        return new SQL() {
            {
                SELECT("*");
                FROM("teacher");
                if (null != sex) {

                    WHERE(" 1 = 1 sex = " + sex);

                } else {
                    WHERE(" 1 = 1 ");
                }
                ORDER_BY("create_time desc");
            }
        }.toString();
    }

}
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

其他注解使用方法类似

2 模拟 Mybatis Plus

可以使用 SelectProvider 写一个适用于所有表的查询的方法。

1 BasicWrapper.java

import java.util.LinkedList;
import java.util.List;

/**
 * @author Administrator
 */
public class BasicWrapper<T> {

    public String last;

    public Class<T> bean;

    public String table;

    public String[] field;

    public List<String> condition = new LinkedList<>();

    public String orderBy;

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

2 QueryWrapper.java

import java.util.Arrays;

import org.apache.ibatis.jdbc.SQL;

import cn.hutool.core.collection.CollectionUtil;

/**
 * @author Administrator
 */
public class QueryWrapper<T> extends BasicWrapper {

    public QueryWrapper() {
    }

    public QueryWrapper(String table, String... field) {
        super.bean = null;
        super.table = table;
        super.field = (null == field || field.length == 0) ? new String[]{"*"} : field;
    }

    public String list(QueryWrapper wrapper) {

        Query query = wrapper.build();

        return new SQL() {
            {
                SELECT(query.getFields());
                FROM(query.getTable());
                WHERE(" 1 = 1 " + query.getCondition());
                ORDER_BY(query.getOrderBy());
            }
        }.toString();

    }

    public Query build() {
        Query query = new Query();
        query.setTable(super.table);
        query.setFields((null == super.field || super.field.length == 0) ? "*" : String.join(", ", Arrays.asList(super.field)));
        String condition = CollectionUtil.isEmpty(super.condition) ? " 1 = 1 " : String.join(" ", super.condition);
        String last = null == super.last ? "" : super.last;
        query.setCondition(condition + " " + last);
        return query;
    }

    public QueryWrapper orderBy(String sql) {
        super.orderBy = sql;
        return this;
    }

    public QueryWrapper orderBy(boolean condition, String sql) {
        if (condition) {
            super.orderBy = sql;
        }
        return this;
    }

    public QueryWrapper apply(String sql) {
        super.condition.add(" and (" + sql + ") ");
        return this;
    }

    public QueryWrapper apply(boolean condition, String sql) {
        if (condition) {
            super.condition.add(" and (" + sql + ") ");
        }
        return this;
    }

    public QueryWrapper eq(String filed, Object value) {
        super.condition.add(" and " + filed + " = " + value);
        return this;
    }

    public QueryWrapper eq(boolean condition, String filed, Object value) {
        if (condition) {
            if (value instanceof String) {
                super.condition.add(" and " + filed + " = '" + value + "'");
            } else {
                super.condition.add(" and " + filed + " = " + value);
            }
        }
        return this;
    }

    public QueryWrapper last(String value) {
        super.last = " " + value;
        return this;
    }

    public QueryWrapper last(boolean condition, String value) {
        if (condition) {
            super.last = " " + value;
        }
        return this;
    }

    public QueryWrapper like(String filed, Object value) {
        super.condition.add(" and " + filed + " like %" + value + "%");
        return this;
    }

    public QueryWrapper likeLeft(String filed, Object value) {
        super.condition.add(" and " + filed + " like %" + value + "%");
        return this;
    }

    public QueryWrapper likeRight(String filed, Object value) {
        super.condition.add(" and " + filed + " like %" + value + "%");
        return this;
    }

    public QueryWrapper like(boolean condition, String filed, Object value) {
        if (condition) {
            super.condition.add(" and " + filed + " like %" + value + "%");
        }
        return this;
    }

    public QueryWrapper likeLeft(boolean condition, String filed, Object value) {
        if (condition) {
            super.condition.add(" and " + filed + " like %" + value + "%");
        }
        return this;
    }

    public QueryWrapper likeRight(boolean condition, String filed, Object value) {
        if (condition) {
            super.condition.add(" and " + filed + " like %" + value + "%");
        }
        return this;
    }

}

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

3 Query.java

import lombok.Data;

/**
 * @author Administrator
 */
@Data
public class Query {

    private String table;

    private String fields;

    private String orderBy;

    private String condition;

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

4 Test.java

package com.xu.test.mybatis;

import org.apache.ibatis.jdbc.SQL;

/**
 * @author Administrator
 * @date 2022年11月26日12点09分
 */
public class Test {
	public static void main(String agrs[]) {
		    QueryWrapper<Teacher> wrapper = new QueryWrapper<>("teacher");
            wrapper.eq("sex", 1);
            wrapper.eq(StringUtils.isNotBlank(name), "name", name);
            wrapper.orderBy("create_time desc");
            List<Teacher> list = processMapper.list(wrapper);
	}

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

1