1.mybatis通用查询框架

1.1.该框架的设计背景

在使用数据库时，我们经常需要根据用户的输入条件来查询内容，所以代码中各个业务模块都有着相同的逻辑代码，仅仅是业务不一样，所以我们怎么抽象出变与不变，形成一个基础组件，使得各个业务模块通用呢？

通过抽象，我们可以观察到，上面的核心问题在于不变，比如一个系统有50个模块，每个模块仅仅是对应的业务不一样(表不一样)，但是查询的过程是不变的。

• 变，指的是业务模块
• 不变，指的是根据用户的输入，去数据库查询的过程

1.2.常用的解决方案

• 使用mybatis的dynamic sql
• 在业务层捕获param，然后构建where sql

1.3.如何做到动态扩展查询条件

在使用mybatis的dynamic sql时，有一个很明显的缺点，那就是mapper文件的中的查询条件是固定的，如果要动态的添加查询条件时，需要同时更新mapper.xml以及parameterType的查询对象
假设我们为下面的sql添加一个新的查询条件，age > 25

select * from student
<where>
    <if test="name != null and name != ''">
        name = #{name}
    </if>
    <if test="tel != null and tel != ''">
        and tel = #{tel}
    </if>
</where>

那么我们需要将上面的sql修改为如下的样子

select * from student
<where>
    <if test="name != null and name != ''">
        name = #{name}
    </if>
    <if test="tel != null and tel != ''">
        and tel = #{tel}
    </if>
    <if test="age != null">
        and age > #{age}
    </if>
</where>

2.mybatis # $的区别

• #{} 解析为一个 JDBC 预编译语句（prepared statement）的参数标记符
• ${} 解析为一个存粹的字符串替换

比如下面的例子，我们假设name = 'luohong'

name = #{name}，将会被解析为name = ?，然后jdbc PreparedStatement会动态的将luohong的值替换掉?，也就是name = 'luohong'
name = ${name}，那么将会被直接解析为name = 'luohong'

2.1

既然mybatis的dynamic sql有两个占位符，那么我们该使用哪一个呢？

大部分的情况下，我们都是用#{}，因为这种方式，可以避免一些sql注入的问题

3.利用mybatis的$特性来创建一个动态查询sql框架

还是上面的sql，假设我们将该sql修改为下面的格式

select * from student
<where>
    ${whereSql}
</where>

其中whereSql代表的是业务层已经处理好的查询条件sql，那么在mapper文件这一侧，我们就实现了动态增加、删除查询条件的特性了

• 比如A模块：whereSql = "name = 'luohong'";
• 比如B模块：whereSql = "name = 'luohong' or name = 'xiemeijiao'";
• 比如C模块：whereSql = "name = 'luohong' and tel = '15013338888'";
• 比如D模块：whereSql = "name = 'luohong' and age > 25";

4.设计思路与实现

sql常用的比较操作

• 等于
• 不等于
• 大于
• 大于等于
• 小于
• 小于等于
• between ... and ...
• LIKE
• IN

支持数据类型，后续感兴趣的同学可以自行扩展，比如TimeStamp等

• String
• Date
• Number

5.核心代码

先看一个类图

20180823191055.png

ICondition，抽象出一个条件表达式，比如：name = 'luohong', age > 15

package luohong;

/**
 * 抽象的查询条件
 * 这里面使用模板设计模式，目前支持Date,Number,String三种数据类型
 * @author luohong
 */
public interface ICondition {
    /**
     * 查询条件的名字
     * @return
     */
    String name();

    /**
     * 查询条件的比较类型
     * @return
     */
    OperationEnum operation();

    /**
     * 查询条件的值
     * @return
     */
    Object value();

    /**
     * 查询条件生成的sql
     * @return
     */
    String sql();
}

OperationEnum的抽象，这里面使用枚举

package luohong;

public enum OperationEnum {
    EQ("="),
    NEQ("!="),
    GTE(">="),
    GT(">"),
    LTE("<="),
    LT("<"),
    LIKE("LIKE"),
    IN("IN");

    private String value;

    /**
     * 比较类型
     * @param operation
     */
    OperationEnum(String operation){
        value = operation;
    }

    public String getValue() {
        return value;
    }

    public void setValue(String value) {
        this.value = value;
    }
}

DateCondition的实现

package luohong;
import java.text.SimpleDateFormat;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

public class DateCondition implements ICondition {
    private String name;
    private Date value;
    private List<Date> values;
    private OperationEnum operation;

    public DateCondition(String name, OperationEnum operation, Date value){
        if(operation == OperationEnum.IN){
            throw new RuntimeException("Not support IN operation for single value, please use new StringCondition(String name, List<String> values)");
        }

        if(operation == OperationEnum.LIKE){
            throw new RuntimeException("Not supper LIKE operation for date condition");
        }

        this.name = name;
        this.operation = operation;
        this.value = value;
    }

    public DateCondition(String name, List<Date> values){
        this.name = name;
        this.operation = OperationEnum.IN;
        this.values = values;
    }

    @Override
    public String name() {
        return name;
    }

    @Override
    public OperationEnum operation() {
        return operation;
    }

    @Override
    public Object value() {
        return value;
    }

    @Override
    public String sql() {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

        switch (operation()){
            case EQ:
            case NEQ:
            case GT:
            case GTE:
            case LT:
            case LTE:
                return name + " " +  operation.getValue() +  " '" + sdf.format(value) + "'";
            case IN:
                StringBuilder sb = new StringBuilder();
                sb.append(name() + " " + operation().getValue() + " (");
                for(int i=0; i<values.size(); i++){
                    if(i == values.size() - 1){
                        sb.append("'" + sdf.format(values.get(i)) + "'");
                    }else{
                        sb.append("'" + sdf.format(values.get(i)) + "',");
                    }
                }
                sb.append(")");
                return sb.toString();
        }
        throw new RuntimeException(name() + operation() + value() + " generate sql fail");
    }

    public static void main(String[] args) {
        System.out.println(new DateCondition("name", OperationEnum.EQ, new Date()).sql());
        System.out.println(new DateCondition("name", OperationEnum.NEQ, new Date()).sql());
        System.out.println(new DateCondition("name", OperationEnum.LT, new Date()).sql());
        System.out.println(new DateCondition("name", OperationEnum.LTE, new Date()).sql());
        System.out.println(new DateCondition("name", OperationEnum.GT, new Date()).sql());
        System.out.println(new DateCondition("name", OperationEnum.GTE, new Date()).sql());
        System.out.println(new DateCondition("name", Arrays.asList(new Date(), new Date())).sql());
    }
}

NumberCondition的实现

package luohong;
import java.util.Arrays;
import java.util.List;

public class NumberCondition implements ICondition {
    private String name;
    private Number value;
    private List<Number> values;
    private OperationEnum operation;

    public NumberCondition(String name, OperationEnum operation, Number value){
        if(operation == OperationEnum.IN){
            throw new RuntimeException("Not support IN operation for single value, please use new StringCondition(String name, List<String> values)");
        }
        if(operation == OperationEnum.LIKE){
            throw new RuntimeException("Not support LIKE operation for number condition");
        }

        this.name = name;
        this.operation = operation;
        this.value = value;
    }

    public NumberCondition(String name, List<Number> values){
        this.name = name;
        this.operation = OperationEnum.IN;
        this.values = values;
    }

    @Override
    public String name() {
        return name;
    }

    @Override
    public OperationEnum operation() {
        return operation;
    }

    @Override
    public Object value() {
        return value;
    }

    @Override
    public String sql() {
        switch (operation()){
            case EQ:
            case NEQ:
            case GT:
            case GTE:
            case LT:
            case LTE:
                return name + " " +  operation.getValue() + " " + value;
            case IN:
                StringBuilder sb = new StringBuilder();
                sb.append(name() + " " + operation().getValue() + " (");
                for(int i=0; i<values.size(); i++){
                    if(i == values.size() - 1){
                        sb.append("" + values.get(i) + "");
                    }else{
                        sb.append("" + values.get(i) + ",");
                    }
                }
                sb.append(")");
                return sb.toString();
        }
        throw new RuntimeException(name() + operation() + value() + " generate sql fail");
    }

    public static void main(String[] args) {
        System.out.println(new NumberCondition("name", OperationEnum.EQ, 10).sql());
        System.out.println(new NumberCondition("name", OperationEnum.NEQ, 100L).sql());
        System.out.println(new NumberCondition("name", OperationEnum.LT, 1).sql());
        System.out.println(new NumberCondition("name", OperationEnum.LTE, 100.2).sql());
        System.out.println(new NumberCondition("name", OperationEnum.GT, 1000).sql());
        System.out.println(new NumberCondition("name", OperationEnum.GTE, 100F).sql());
        System.out.println(new NumberCondition("name", Arrays.asList(1, 2, 3, 4, 5)).sql());
    }
}

StringCondition的实现

package luohong;

import java.util.Arrays;
import java.util.List;

public class StringCondition implements ICondition {
    private String name;
    private String value;
    private List<String> values;
    private OperationEnum operation;

    public StringCondition(String name, OperationEnum operation, String value){
        if(operation == OperationEnum.IN){
            throw new RuntimeException("Not support IN operation for single value, please use new StringCondition(String name, List<String> values)");
        }

        this.name = name;
        this.operation = operation;
        this.value = value;
    }

    public StringCondition(String name, List<String> values){
        this.name = name;
        this.operation = OperationEnum.IN;
        this.values = values;
        this.value = values.toString();
    }

    @Override
    public String name() {
        return name;
    }

    @Override
    public OperationEnum operation() {
        return operation;
    }

    @Override
    public Object value() {
        return value;
    }

    @Override
    public String sql() {
        switch (operation()){
            case EQ:
            case NEQ:
            case GT:
            case GTE:
            case LT:
            case LTE:
                return name + " " +  operation.getValue() +  " '" + value + "'";
            case LIKE:
                return name + " " + operation.getValue() +  " '%" + value + "%'";
            case IN:
                StringBuilder sb = new StringBuilder();
                sb.append(name() + " " + operation().getValue() + " (");
                for(int i=0; i<values.size(); i++){
                    if(i == values.size() - 1){
                        sb.append("'" + values.get(i) + "'");
                    }else{
                        sb.append("'" + values.get(i) + "',");
                    }
                }
                sb.append(")");
                return sb.toString();
        }
        throw new RuntimeException(name() + operation() + value() + " generate sql fail");
    }

    public static void main(String[] args) {
        System.out.println(new StringCondition("name", OperationEnum.EQ, "luohong").sql());
        System.out.println(new StringCondition("name", OperationEnum.NEQ, "luohong").sql());
        System.out.println(new StringCondition("name", OperationEnum.LT, "luohong").sql());
        System.out.println(new StringCondition("name", OperationEnum.LTE, "luohong").sql());
        System.out.println(new StringCondition("name", OperationEnum.GT, "luohong").sql());
        System.out.println(new StringCondition("name", OperationEnum.GTE, "luohong").sql());
        System.out.println(new StringCondition("name", OperationEnum.LIKE, "luohong").sql());
        System.out.println(new StringCondition("name", Arrays.asList("luohong", "luoyan")).sql());
    }
}

为了简化调用，我们使用一个工厂类，ConditionFactory

package luohong;

import java.util.Date;
import java.util.List;

/**
 * 工厂模式
 * 用于创建condition
 */
public class ConditionFactory {
    public static ICondition newInstance(String name, OperationEnum operation, String value){
        return new StringCondition(name, operation, value);
    }

    public static ICondition newInstance(String name, OperationEnum operation, Number value){
        return new NumberCondition(name, operation, value);
    }

    public static ICondition newInstance(String name, OperationEnum operation, Date value){
        return new DateCondition(name, operation, value);
    }

    public static ICondition newInstanceDates(String name, List<Date> values){
        return new DateCondition(name, values);
    }

    public static ICondition newInstanceStrings(String name, List<String> values){
        return new StringCondition(name, values);
    }

    public static ICondition newInstanceNumbers(String name, List<Number> values){
        return new NumberCondition(name, values);
    }
}

最后，我们提供一个聚合类，并且提供用户自定义扩展sql的方式，比如用户可以直接添加age = 15这种扩展，无需了解condition的设计，这个特性是开闭原则的应用点

package luohong;

import java.util.*;

/**
 * 用于解析condition，然后生成whereSql
 */
public class ConditionHandler {
    private Set<ICondition> conditions = new LinkedHashSet<>();
    /**
     * 用户自定义的sql片段，提供一个扩展点，由用户注入动态的condtion条件
     */
    private Set<String> conditionSqls = new LinkedHashSet<>();

    public Set<ICondition> getConditions(){
        return conditions;
    }

    public void addCondition(ICondition condition){
        this.conditions.add(condition);
    }

    public void addConditionSql(String sql){
        this.conditionSqls.add(sql);
    }

    /**
     * 便利所有的condition，然后生成所有一个sql片段
     * @return
     */
    public String genWhereSql(){
        StringBuilder sb = new StringBuilder();
        for(ICondition condition: conditions){
            sb.append(condition.sql());
            sb.append(" AND ");
        }

        for(String conditionSql: conditionSqls){
            sb.append(conditionSql);
            sb.append(" AND ");
        }

        String whereSql = sb.toString();
        if(whereSql != null && !whereSql.equals("")){
            whereSql = whereSql.substring(0, whereSql.length() - 4);
        }

        return whereSql;
    }

    public static void main(String[] args) {
        ConditionHandler requestParamSqlHandler = new ConditionHandler();
        requestParamSqlHandler.addConditionSql("age > 15");
        requestParamSqlHandler.addConditionSql("name = 'luohong'");
        requestParamSqlHandler.addConditionSql("tel = '15013336884'");
        requestParamSqlHandler.addCondition(ConditionFactory.newInstance("name", OperationEnum.EQ, "luohong"));
        requestParamSqlHandler.addCondition(ConditionFactory.newInstance("name", OperationEnum.NEQ, "luohong"));
        requestParamSqlHandler.addCondition(ConditionFactory.newInstance("name", OperationEnum.LT, "luohong"));
        requestParamSqlHandler.addCondition(ConditionFactory.newInstance("name", OperationEnum.LTE, "luohong"));
        requestParamSqlHandler.addCondition(ConditionFactory.newInstance("name", OperationEnum.GT, "luohong"));
        requestParamSqlHandler.addCondition(ConditionFactory.newInstance("name", OperationEnum.GTE, "luohong"));
        requestParamSqlHandler.addCondition(ConditionFactory.newInstance("name", OperationEnum.LIKE, "luohong"));

        requestParamSqlHandler.addCondition(ConditionFactory.newInstance("name", OperationEnum.EQ, 100));
        requestParamSqlHandler.addCondition(ConditionFactory.newInstance("name", OperationEnum.LT, 100));
        requestParamSqlHandler.addCondition(ConditionFactory.newInstance("name", OperationEnum.LTE, 100));
        requestParamSqlHandler.addCondition(ConditionFactory.newInstance("name", OperationEnum.GTE, 100));
        requestParamSqlHandler.addCondition(ConditionFactory.newInstance("name", OperationEnum.NEQ, 100));

        requestParamSqlHandler.addCondition(ConditionFactory.newInstance("name", OperationEnum.EQ, new Date()));
        requestParamSqlHandler.addCondition(ConditionFactory.newInstance("name", OperationEnum.NEQ, new Date()));
        requestParamSqlHandler.addCondition(ConditionFactory.newInstance("name", OperationEnum.GT, new Date()));
        requestParamSqlHandler.addCondition(ConditionFactory.newInstance("name", OperationEnum.GTE, new Date()));
        requestParamSqlHandler.addCondition(ConditionFactory.newInstance("name", OperationEnum.LT, new Date()));
        requestParamSqlHandler.addCondition(ConditionFactory.newInstance("name", OperationEnum.LTE, 100));

        requestParamSqlHandler.addCondition(ConditionFactory.newInstanceDates("name", Arrays.asList(new Date())));
        requestParamSqlHandler.addCondition(ConditionFactory.newInstanceStrings("name", Arrays.asList("luohong", "xiemeijiao")));
        requestParamSqlHandler.addCondition(ConditionFactory.newInstanceNumbers("name", Arrays.asList(1, 2, 3, 4)));
        System.out.println(requestParamSqlHandler.genWhereSql());
    }
}

程序输出结果

name = 'luohong' 
AND name != 'luohong' 
AND name < 'luohong' 
AND name <= 'luohong' 
AND name > 'luohong' 
AND name >= 'luohong' 
AND name LIKE '%luohong%' 
AND name = 100 
AND name < 100 
AND name <= 100 
AND name >= 100 
AND name != 100 
AND name = '2018-08-23' 
AND name != '2018-08-23' 
AND name > '2018-08-23' 
AND name >= '2018-08-23' 
AND name < '2018-08-23' 
AND name <= 100 
AND name IN ('2018-08-23') 
AND name IN ('luohong','xiemeijiao') 
AND name IN (1,2,3,4) 
AND age > 15 
AND name = 'luohong' 
AND tel = '15013336884'

6.总结

上面的代码虽然简单，但是却使用了<b>策略设计模式</b>，<b>工厂设计模式</b>，以及应用了开闭原则使得扩展性大大提高。

7.扩展性

由于上面的demo仅仅是花了一小点时间制作，还有些不完善的地方，大家有兴趣的可以进行这些方面的思考

• 支持更多的数据类型扩展，比如Timestame，boolean等
• 比较类型的扩展，比如between ... and ...
• AND OR扩展，上面默认都是AND规则，这里面可以继续加入OR的规则
• sql注入的规避
• ...