SQL之解决where 1=1 问题及优化多条件查询

Dao中实现多条件查询。

public List<Product> query(String name ,BigDecimal minSalePrice,
        BigDecimal maxSalePrice){
    QueryRunner runner = new QueryRunner(C3p0Utils.getDateSource());

    StringBuilder sql = new StringBuilder("select  * from product where 1=1 ");
    List<product > params = new ArrayList<>();//存放参数
    if(hasLength(name)){
        sql.append(" and productName like ?");//注意空格
        params.add("%"+name+"%");
    }
    if(hasLength(minSalePrice)){
        sql.append(" and salePrice >= ?");//注意空格
        params.add(minSalePrice);
    }
    if(hasLength(maxSalePrice)){
        sql.append(" and salePrice <= ?");//注意空格
        params.add(maxSalePrice);
    }

    return runner.query(sql,new BeanListHandler(Product.class),params.toArray());
}

但是使用WHERE 1=1 之后,就不能使用索引了,从MySQL5.7开始,会自动默认取消掉SQL中第一个为true的条件，而且每一条数据都得查询一遍，效率低。

如何解决WHERE 1=1 的问题

(加个flag)
boolean isFirst = ture;

if(hasLength(条件1)){
    if(isFirst){
        sql.append(" where ");//注意空格
        isFirst=false;
    }else{
        sql.append(" and "); //注意空格
    }
    sql.append("productName like ? ");
    params.add("%"+name+"%");
}
if(hasLength(条件2)){
    if(isFirst){
        sql.append(" where ");//注意空格
        isFirst=false;
    }else{
        sql.append(" and ");//注意空格
    }
    sql.append("salePrice >= ?");
    params.add(minSalePrice);
}
if(hasLength(条件3)){
    if(isFirst){
        sql.append(" where ");//注意空格
        isFirst=false;
    }else{
        sql.append(" and ");//注意空格
    }
    sql.append("salePrice <= ?");
    params.add(maxSalePrice);
}

好多重复的 if else 好烦人。

所以换个思想，我把查询条件放在一个集合中，我用的时候自己取，取出来的第一个 就是第一个我拼上“where”，之后的我都拼上“and”。

//封装查询条件
private List<String> conditions = new ArrayLIst<>();

if(hasLength(条件1)){
    conditions.add("pName like ?");
    params.add("%"+name+"%");
}
if(hasLength(条件2)){
    conditions.add("maxSalePrice <= ?");
    params.add(minSalePrice);
}
if(hasLength(条件3)){
    conditions.add("minSalePrice >= ?");
    params.add(maxSalePrice);
}
for(int i=0 ; i< conditions.size() ; i++){
    if(i=0){
        sql.append(" where ");//注意加空格 
    }else{
        sql.append(" and ");//注意加空格
    }
    sql.append(conditions.get(i));//拼接条件
}

你以为结束了吗？并没有，问题仍然存在：

1. 如果查询参数较多，此时query方法的参数 会出现爆炸式增长。
2. 本着责任分离规则，dao应该只做CRUD，拼接sql 应该交给别人去做。

所以，
新建query包 放置处理查询问题 的类来封装查询信息。

public class ProductQuery{
    private String name;
    private BigDecimal minSalePrice;
    private BigDecimal maxSalePrice;
    //省略getter&&setter&&toString
    //封装查询条件
    private List<String> conditions = new ArrayLIst<>();
    //封装查询参数（list 允许重复 且 存取有序）
    private List<Object> parameters = new ArrayList<>();
    //返回拼接好的查询条件：where 条件1 and 条件2 ...
    public String getQuery(){
        StringBuilder sql = new StringBuilder(200);
        if(hasLength(条件1)){
            conditions.add("pName like ?");
            parameters.add("%"+name+"%");
        }
        if(hasLength(条件2)){
            conditions.add("maxSalePrice <= ?");
            parameters.add(minSalePrice);
        }
        if(hasLength(条件3)){
            conditions.add("minSalePrice >= ?");
            parameters.add(maxSalePrice);
        }
        for(int i=0 ; i< conditions.size() ; i++){
            if(i=0){
                sql.append(" where ");//注意加空格 
            }else{
                sql.append(" and ");//注意加空格
            }
            sql.append(conditions.get(i));//拼接条件
        }
        return sql.toString();
    }
    //返回查询参数
    public List<Object> getParamters(){
        return this.parameters;
    }
}

此时的Dao：

public List<Product> query(ProductQuery q){
    QueryRunner runner = new QueryRunner(C3p0Utils.getDateSource());
    String sql="select * from product"+q.getQuery();
    return runner.query(sql,q.getParameters().toArray);
}

有没有很舒服？反正博主很喜欢 （- -）。

---

另外告诉大家上面的hasLength()方法：

public boolean hasLength(String str){
    return str!=null && !"".equals(str.trim());
}