【Mybatis源码分析 10】多条件组合查询又不想写很多@Select+dao方法，通过SqlProvider实现动态sql增强dao方法的复用性

最新推荐文章于 2024-04-16 10:00:09 发布

OneTask

最新推荐文章于 2024-04-16 10:00:09 发布

阅读量431

点赞数

分类专栏： Mybatis

本文链接：https://blog.csdn.net/weixin_38247439/article/details/106492902

版权

Mybatis 专栏收录该内容

15 篇文章 1 订阅

订阅专栏

@Select、@Insert、@Update、@Delete指定sql

@SelectProvider、@InsertProvider、@UpdateProvider、@DeleteProvider引用SqlProvider类的方法根据参数生成动态sql

SqlProvider实现动态sql源码分析，关键对象ProviderSqlSource

@Select、@Insert、@Update、@Delete指定sql

数据库准备：假设体育比赛项目报名，添加form表格，记录报名学生的id，姓名pname，班级class，是否报名跑步run，是否报名跳远jump，是否报名篮球basketball和是否报名游泳swim

# 建表
CREATE TABLE form (

id int(255) NOT NULL AUTO_INCREMENT PRIMARY KEY,
pname VARCHAR(255) NOT NULL,
pclass VARCHAR(255) NOT NULL,
run enum('0','1') DEFAULT '0',
jump enum('0','1') DEFAULT '0' ,
basketball enum('0','1') DEFAULT '0' ,
swim enum('0','1') DEFAULT '0'

)


#添加数据
INSERT form(id,pname,pclass,run,jump,basketball,swim) 
values
(1,'p1','c1','0','1','1','0'),
(2,'p2','c1','0','1','0','1'),
(3,'p3','c1','1','0','1','0'),
(4,'p4','c1','0','1','1','0'),
(5,'p5','c1','1','1','0','1'),
(6,'p6','c2','1','1','1','0'),
(7,'p7','c2','0','0','1','0'),
(8,'p8','c2','1','1','1','1'),
(9,'p9','c2','0','0','1','0'),
(10,'p10','c3','1','1','0','0'),
(11,'p11','c3','0','1','1','1'),
(12,'p12','c3','0','1','1','0')

pojo对象的类定义：Student

package Entity;

public class Student {

    private Integer id;
    private String pname;
    private String pclass;

    //为了方便，这里run,jump,basketball和swim属性定义为Boolean类型，
    //为了于数据库form表中的枚举类型的run,jump,basketball和swim映射，需要借助typehandler类型转换器（mybatis的type包下自带BooleanTypeHandler类型转换器）
    private Boolean run;
    private Boolean jump;
    private Boolean basketball;
    private Boolean swim;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getPname() {
        return pname;
    }

    public void setPname(String pname) {
        this.pname = pname;
    }

    public String getPclass() {
        return pclass;
    }

    public void setPclass(String pclass) {
        this.pclass = pclass;
    }

    public Boolean getRun() {
        return run;
    }

    public void setRun(Boolean run) {
        this.run = run;
    }

    public Boolean getJump() {
        return jump;
    }

    public void setJump(Boolean jump) {
        this.jump = jump;
    }

    public Boolean getBasketball() {
        return basketball;
    }

    public void setBasketball(Boolean basketball) {
        this.basketball = basketball;
    }

    public Boolean getSwim() {
        return swim;
    }

    public void setSwim(Boolean swim) {
        this.swim = swim;
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", pname='" + pname + '\'' +
                ", pclass='" + pclass + '\'' +
                ", run=" + run +
                ", jump=" + jump +
                ", basketball=" + basketball +
                ", swim=" + swim +
                '}';
    }
}

模拟前端通过复选框进行多条件查询，查询报名某一项或者多项项目的学生，比如勾选✔跑步和✔跳远

后端会接收一个参数名为options的String数组，存储"run"和"jump"两个字符串，经过判断后，执行下面这个

package DAO;

import Entity.Student;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface StudentDAO {

    @Select("select * from form where run='1' and jump='1'")
    List<Student> selectWithRandJ();

    // 其他查询组合的方法


}

//后端关键代码
 
//从前端得到的复选款选中选项
String[] options = new String[]{"run","jump"};

//if 选中 run和jump
List<Student> list = mapper.selectWithRandJ();
for(Student s : list) System.out.println(s);

// else if 其它查询组合
// 调用相应的dao方法

13:11:31.564 [main] DEBUG DAO.StudentDAO.selectWithRandJ - ==> Preparing: select * from form where run='1' and jump='1' 
13:11:31.607 [main] DEBUG DAO.StudentDAO.selectWithRandJ - ==> Parameters: 
13:11:31.646 [main] DEBUG DAO.StudentDAO.selectWithRandJ - <== Total: 4
Student{id=5, pname='p5', pclass='c1', run=true, jump=true, basketball=false, swim=true}
Student{id=6, pname='p6', pclass='c2', run=true, jump=true, basketball=true, swim=false}
Student{id=8, pname='p8', pclass='c2', run=true, jump=true, basketball=true, swim=true}
Student{id=10, pname='p10', pclass='c3', run=true, jump=true, basketball=false, swim=false}

如果这样写的话就得写c(4,1)+c(4,2)+c(4,3)+c(4,4)=15个dao方法了，这肯定是不行的

@SelectProvider、@InsertProvider、@UpdateProvider、@DeleteProvider引用SqlProvider类的方法根据参数生成动态sql

mybatis提供@Select、@Insert、@Update、@Delete的动态sql注解@SelectProvider、@InsertProvider、@UpdateProvider、@DeleteProvider. SqlProvider类定义动态生成sql语句的方法，@SelectProvider等注解中method标签引用SqlProvider类的方法实现动态sql

package DAO;

import java.util.Map;

public class SqlProvider {

    // 根据参数生成多条件查询sql语句
    public String getSqlByOptionsMap(Map<String, Boolean> optionsMap){

        String strsql="select * from form where";

        if(optionsMap.get("run"))
            strsql+=" and run='1'";
        if(optionsMap.get("jump"))
            strsql+=" and jump='1'";
        if(optionsMap.get("basketball"))
            strsql+=" and basketball='1'";
        if(optionsMap.get("swim"))
            strsql+=" and swim='1'";

        //校正sql语句
        if(strsql.endsWith("where"))
            strsql=strsql.replace("where","");
        if(strsql.indexOf("where and")>0)
            strsql=strsql.replace("where and","where");
        return strsql;
    }
}

package DAO;

import Entity.Student;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

@Mapper
public interface StudentDAO {

//    @Select("select * from form where run='1' and jump='1'")
//    List<Student> selectWithRandJ();
//
//    // 其他查询组合的方法


    // 动态sql
    @SelectProvider(type = DAO.SqlProvider.class,method = "getSqlByOptionsMap")
    List<Student> selectWithOptions(Map<String,Boolean> optionsMap);

}

我们只需要写一个dao方法，通过参数动态生成sql语句，查询的关键代码就只需要

        Map<String, Boolean> optionsMap = new HashMap<>();
        optionsMap.put("run",false);
        optionsMap.put("junp",false);
        optionsMap.put("basketball",false);
        optionsMap.put("swim",false);

        //从前端得到的复选款选中选项
        String[] options = new String[]{"run","jump"};

        //设置选中选项为true
        for(String o : options)
            optionsMap.put(o,true);

        List<Student> list = mapper.selectWithOptions(optionsMap);
        for(Student s : list) System.out.println(s);

SqlProvider实现动态sql源码分析，关键对象ProviderSqlSource

我们知道接口dao方法是通过MapperProxy代理对象调用MapperMethod的execute方法，最终调用sqlSession的select等方法执行，在sqlSession执行select等方法之前会先对@Select等注解的sql语句中的${}和#{}解析。

改成使用@SelectProvider等注解时，注解中只写SqlProvider的类型和生成sql语句的方法，所以没有解析sql语句直接到了sqlSession调用Select等方法这里

// SqlSession

    public <E> List<E> selectList(String statement, Object parameter, RowBounds rowBounds) {
        List var5;
        try {
            // 获取statement的映射
            MappedStatement ms = this.configuration.getMappedStatement(statement);
            // this.wrapCollection(parameter)对statment类型加工，将collection\数组都转换为map集合的参数，执行query
            var5 = this.executor.query(ms, this.wrapCollection(parameter), rowBounds, Executor.NO_RESULT_HANDLER);
        } catch (Exception var9) {
            throw ExceptionFactory.wrapException("Error querying database.  Cause: " + var9, var9);
        } finally {
            ErrorContext.instance().reset();
        }

        return var5;
    }

    public <E> List<E> query(MappedStatement ms, Object parameterObject, RowBounds rowBounds, ResultHandler resultHandler) throws SQLException {
        //BoundSql是其保存Sql语句的对象
        BoundSql boundSql = ms.getBoundSql(parameterObject);
        CacheKey key = this.createCacheKey(ms, parameterObject, rowBounds, boundSql);
        return this.query(ms, parameterObject, rowBounds, resultHandler, key, boundSql);
    }

BoundSql是其保存Sql语句的对象

    public BoundSql getBoundSql(Object parameterObject) {
        SqlSource sqlSource = this.createSqlSource(parameterObject);
        return sqlSource.getBoundSql(parameterObject);
    }

    private SqlSource createSqlSource(Object parameterObject) {
        try {
            String sql;
            if (parameterObject instanceof Map) {
                // 判断参数是map类型的，正常会执行到这里，因为前面wrapCollection已经对参数加工过
                int bindParameterCount = this.providerMethodParameterTypes.length - (this.providerContext == null ? 0 : 1);
                if (bindParameterCount == 1 && this.providerMethodParameterTypes[Integer.valueOf(0).equals(this.providerContextIndex) ? 1 : 0].isAssignableFrom(parameterObject.getClass())) {
                    // 动态代理执行Provider方法，获取根据参数parameterObject生成的sql语句
                    sql = this.invokeProviderMethod(this.extractProviderMethodArguments(parameterObject));
                } else {
                    Map<String, Object> params = (Map)parameterObject;
                    sql = this.invokeProviderMethod(this.extractProviderMethodArguments(params, this.providerMethodArgumentNames));
                }
            } else if (this.providerMethodParameterTypes.length == 0) {
                sql = this.invokeProviderMethod();
            } else if (this.providerMethodParameterTypes.length == 1) {
                if (this.providerContext == null) {
                    sql = this.invokeProviderMethod(parameterObject);
                } else {
                    sql = this.invokeProviderMethod(this.providerContext);
                }
            } else {
                if (this.providerMethodParameterTypes.length != 2) {
                    throw new BuilderException("Cannot invoke SqlProvider method '" + this.providerMethod + "' with specify parameter '" + (parameterObject == null ? null : parameterObject.getClass()) + "' because SqlProvider method arguments for '" + this.mapperMethod + "' is an invalid combination.");
                }

                sql = this.invokeProviderMethod(this.extractProviderMethodArguments(parameterObject));
            }

            Class<?> parameterType = parameterObject == null ? Object.class : parameterObject.getClass();

            //对SqlProvider提供的sql语句解析${}和#{}
            return this.languageDriver.createSqlSource(this.configuration, sql, parameterType);
        } catch (BuilderException var5) {
            throw var5;
        } catch (Exception var6) {
            throw new BuilderException("Error invoking SqlProvider method '" + this.providerMethod + "' with specify parameter '" + (parameterObject == null ? null : parameterObject.getClass()) + "'.  Cause: " + this.extractRootCause(var6), var6);
        }
    }

动态代理执行Provider方法

    private String invokeProviderMethod(Object... args) throws Exception {
        Object targetObject = null;
        if (!Modifier.isStatic(this.providerMethod.getModifiers())) {
            // 如果引用Provider方法，先获取一个方法类型SqlProvider的对象
            targetObject = this.providerType.getDeclaredConstructor().newInstance();
        }

        // 经典反射，前面根据方法名反射得到SqlProvider类的getSqlByOptionsMap方法的providerMethod对象，通过SqlProvider类实例targetObject和入参args，执行getSqlByOptionsMap方法，该方法返回根据入参生成的sql语句
        CharSequence sql = (CharSequence)this.providerMethod.invoke(targetObject, args);
        return sql != null ? sql.toString() : null;
    }

OneTask

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
【Mybatis源码分析 10】多条件组合查询又不想写很多@Select+dao方法，通过SqlProvider实现动态sql增强dao方法的复用性

目录@Select、@Insert、@Update、@Delete指定sql@SelectProvider、@InsertProvider、@UpdateProvider、@DeleteProvider引用SqlProvider类的方法根据参数生成动态sqlSqlProvider实现动态sql源码分析，关键对象ProviderSqlSource@Select、@Insert、@Update、@Delete指定sql数据库准备：假设体育比赛项目报名，添加form表格，记录报名学生的..
复制链接

扫一扫