在构建动态SQL时使用模糊查询遇到的易错点

需求：查询功能。收到前端传来的一个字符串参数，将这个关键字与表中的多项数据进行比对，搜索出每一条有任一列包含该关键字的数据。比如，用户搜索“22”，公司名称是“22公司”的数据要能找到，合同时间是“2022-02-02”的也要能找到。

 

正确代码（dao层）：

    public List<Record> selectRecord1(String keyword) {
        //1.定义模板初始化sql
        String sql="select * from contract where 1 = 1 ";
        StringBuilder sb = new StringBuilder(sql);

        List<Object> params = new ArrayList<Object>();

            //判断value是否有值
            if(keyword!=null && !"".equals(keyword)){
                //有值
                sb.append(" and (id like  ?  or part_a like ? or part_b like ? or start_date like ? or expiry_date like ? or doc_name like ? or remarks like ? )");
                params.add("%"+keyword+"%");
                params.add("%"+keyword+"%");
                params.add("%"+keyword+"%");
                params.add("%"+keyword+"%");
                params.add("%"+keyword+"%");
                params.add("%"+keyword+"%");
                params.add("%"+keyword+"%");
                //?条件的值
            }

        sql=sb.toString();

        System.out.println(sql);
        System.out.println(params);

        //执行sql语句，将返回值打包
        List<Record> records=template.query(sql,params.toArray(), new BeanPropertyRowMapper<Record>(Record.class));


        return records;
    }

 

易错：

我第一次写的时候，把通配符%全写到了sb.append()里，也就是为了params.add()里只写一个keyword，图省事。

然后就报错了，sql语句无法解析。

这是因为当我把“%”写到占位符“？”的前面，SQL解析器会将SQL语句中的占位符（?）作为参数占位符来解析，而将通配符（%）作为模糊查询的关键字来解析，因此如果将%放在占位符的两侧，“？”就会被解析器误认为是模糊查询的关键字，而无法正确解析查询条件。

 

解析器：

这里的SQL 解析器是指 PreparedStatement 中的参数解析器。PreparedStatement 是继承自 Statement 的一个接口，用于执行带有参数的 SQL 语句。在创建 PreparedStatement 对象时，会先将 SQL 语句进行预编译，并将其中的占位符（?）标记为参数占位符。在执行 SQL 语句时，可以通过 setXXX() 方法为占位符设置具体的参数值。

如果在 SQL 语句中的占位符（?）两侧添加了通配符（%），PreparedStatement 会将其作为参数值的一部分，而不是 SQL 语句的一部分来解析。因此，当试图将包含通配符的参数作为 PreparedStatement 的参数时，会抛出 SQL 语法错误的异常。

 

本质：

问题是：如果PreparedStatement 会将其作为参数值的一部分，而不是 SQL 语句的一部分来解析，那得到的也只是一个在查询包含“？”字段的SQL语句。SQL 语法错误到底在哪？这个异常到底是什么呢？

答案是：SQL语句中需要的参数数量与实际提供的参数数量不匹配，从而导致SQL语法错误。例如，如果SQL语句中有三个占位符（?），但是只向PreparedStatement中传递了两个参数值，那么就会出现SQL语法错误，因为PreparedStatement无法为第三个占位符提供参数值。如果提供的参数数量多于SQL语句中的占位符数量，也会报错。这时候，程序会抛出SQLException异常，提示参数个数不匹配。