工作小记 -- sql中使用函数会导致预编译失效吗？

前几天有个模糊查询的需求，我还是像往常一样写，xml例子如下：

<if test="keyword != null">
    and t1.customer_name like concat("%"#{keyword}"%")
</if>

新来的组长review我写的代码时候，反手提问了我几个问题：

1. 这样写有没有什么问题？
2. sql的执行过程是什么？
3. 啥是预编译？
4. 用过<bind>标签吗？

反手给我打了个措手不及，因为原来自己一直是上面这样写的。后来组长跟我说上面的写法中用到了concat函数，会导致预编译失效。（真的是这样吗？）

什么是预编译？

预编译就是数据库接受到sql语句之后，需要词法和语义解析，优化sql语句，制定执行计划。

预编译语句就是将这类语句中的值用占位符替代，可以视为将sql语句模板化或者说参数化。一次编译、多次运行，省去了解析优化等过程。

预编译语句被DB的编译器编译后的执行代码被缓存下来,那么下次调用时只要是相同的预编译语句就不需要编译,只要将参数直接传入编译过的语句执行代码中(相当于一个涵数)就会得到执行。

使用<bind>标签替换concat函数，可以避免预编译失效：

<if test="keyword != null">
     <bind name="wordValue" value="'%'+keyword+'%'" />
     and t1.customer_name like #{wordValue}
</if>

开启预编译的参数为useServerPrepStmts=true，配置在url上：如：

“jdbc:mysql://localhost:3306/db1?user=root&password=123456&" +
    "useSSL=false&allowMultiQueries=true&" +
    "useUnicode=true&" +
    "characterEncoding=UTF-8&" +
    "serverTimezone=Asia/Shanghai&" +
    "useServerPrepStmts=true"

我写了如下测试类：

public class PreparedStatementTest {
    public static void main(String[] args) throws Throwable {
        String sql = "insert into user select ?,?,?";
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection conn = null;
        try{
            conn = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/db1?user=root&password=12345678&" +
                    "useSSL=false&allowMultiQueries=true&" +
                    "useUnicode=true&" +
                    "characterEncoding=UTF-8&" +
                    "serverTimezone=Asia/Shanghai&" +
                    "useServerPrepStmts=true"
            );
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setInt(1,14);
            stmt.setString(2, "张14");
            stmt.setInt(3,26);
            stmt.executeUpdate();
            stmt.close();
            PreparedStatement stmt2 = conn.prepareStatement(sql);
            stmt2.setInt(1,15);
            stmt2.setString(2, "张15");
            stmt2.setInt(3,27);
            stmt2.executeUpdate();
            stmt2.close();
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            conn.close();
        }
    }
}

查看日志如下：先后两个插入都进行了预编译 

 查资料后得知是因为没有开启预编译结果缓存：

我们在url上配置开启缓存： "cachePrepStmts=true"，再次连续执行两条相同的插入语句，日志如下：

通过日志可以看出系统只进行了一次预编译。

再写个查询的！来验证一下sql语句中如果有函数的话是否会影响预编译呢？

public static void main(String[] args) throws Throwable {
        String sql = "select * from user where `name` = ?";
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection conn = null;
        try{
            conn = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/db1?user=root&password=chaoyi123&" +
                    "useSSL=false&allowMultiQueries=true&" +
                    "useUnicode=true&" +
                    "characterEncoding=UTF-8&" +
                    "serverTimezone=Asia/Shanghai&" +
                    "useServerPrepStmts=true&" +
                    "cachePrepStmts=true"
            );
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setString(1, "张三");
            ResultSet rs1 = stmt.executeQuery();//第一次执行
            rs1.close();
            stmt.setString(1, "李四");
            ResultSet rs2 = stmt.executeQuery();//第二次执行
            rs2.close();
            stmt.close();
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            conn.close();
        }
    }

可以看到正常情况下是只进行了一次预编译。 

下面把sql语句加个函数！把上面的sql改成如下：

public static void main(String[] args) throws Throwable {
        String sql = "select * from user where `name` = concat(?,?)";
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection conn = null;
        try{
            conn = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/db1?user=root&password=chaoyi123&" +
                    "useSSL=false&allowMultiQueries=true&" +
                    "useUnicode=true&" +
                    "characterEncoding=UTF-8&" +
                    "serverTimezone=Asia/Shanghai&" +
                    "useServerPrepStmts=true&" +
                    "cachePrepStmts=true"
            );
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setString(1, "张");
            stmt.setString(2, "三");
            ResultSet rs1 = stmt.executeQuery();//第一次执行
            rs1.close();
            stmt.setString(1, "李");
            stmt.setString(2, "四");
            ResultSet rs2 = stmt.executeQuery();//第二次执行
            rs2.close();
            stmt.close();
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            conn.close();
        }
    }

sql中用concat()函数做个拼接，运行后再来看日志！

还是只进行了一次预编译！ 

所以：函数不会影响预编译的效果。

看来组长也有翻车的时候～～嘿嘿！