Mybatis 动态SQL

注解	作用
@SelectProvider	动态查询SQL
@InsertProvider	动态新增SQL
@UpdateProvider	动态更新SQL
@DeleteProvider	动态删除SQL

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

@SelectProvider 是 MyBatis 中的一个注解，用于指定一个类或者类的某个方法提供 SQL 查询语句。该注解常用于动态 SQL 的场景，例如根据不同的参数生成不同的查询语句。 使用 @SelectProvider 注解的方式可以让 MyBatis 在运行时根据注解指定的类或方法来生成对应的 SQL 查询语句，从而实现动态 SQL 功能。注解的语法如下：

@SelectProvider(type = XxxProvider.class, method = "xxxMethod")

其中，type 属性表示提供 SQL 查询语句的类，method 属性表示类中提供查询语句的方法。在查询时，MyBatis 会调用指定类中的指定方法来生成 SQL 查询语句，然后执行该查询语句并返回结果。 需要注意的是，提供 SQL 查询语句的类需要实现 org.apache.ibatis.builder.annotation.ProviderMethodResolver 接口，该接口中定义了查找提供 SQL 查询的方法的逻辑。同时，提供查询语句的方法需要返回一个字符串类型的 SQL 查询语句。 使用 @SelectProvider 注解可以让 MyBatis 的查询语句更加灵活，适用于各种复杂的查询场景。

1 @SelectProvider 使用

1 TestMapper.java

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

2 Test.java

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();}}

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;}

2 QueryWrapper.java

import java.util.Arrays;import org.apache.ibatis.jdbc.SQL;import cn.hutool.core.collection.CollectionUtil;/*** @author */
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;}}

3 Query.java

import lombok.Data;/*** @author Administrator*/
@Data
public class Query {private String table;private String fields;private String orderBy;private String condition;}

4 Test.java

import org.apache.ibatis.jdbc.SQL;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);}}