映射文件的sql语句中 #{} 和 ${} 区别以及实现模糊查询

目录

• sql 语句中的 #{}
• #{} 模糊查询错误用法
• #{} 实现模糊查询
• sql 语句中的 ${}
• ${} 实现模糊查询
• #{} 与 ${} 对比

sql 语句中的 #{}

• 表示一个占位符号，通过 #{} 可以实现 preparedStatement 向占位符中设置值。
• 自动进行 java 类型和 jdbc 类型转换。
• 可以有效防止 sql 注入。
• 可以接收简单类型值或 pojo 属性值。
• 如果 parameterType 传输单个简单类型值，#{} 括号中可以是任意名称。

#{} 模糊查询错误用法

在模糊查询中，#{} 在以下用法是错误的，查询名字中带有李字的人：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="myTest">

<!--根据用户名模糊查询客户-->
<select id="queryCustomerByName" parameterType="String" resultType="com.itlike.domain.Customer">
    SELECT * FROM customer WHERE cust_name LIKE '%#{cust_name}%';
</select>

</mapper>

查询代码如下：

public void test3(){
    SqlSession sqlSession = MybatisUtils.openSession();
    List<Customer> customers = sqlSession.selectList("queryCustomerByName", "李");
    for (Customer customer : customers) {
        System.out.println(customer);
    }
    sqlSession.close();
}

由执行结果可见，这样写的话，运行时的 sql 语句如下：

SELECT * FROM customer WHERE cust_name LIKE '%?%'

传入的参数为 “李” ，由于 #{} 会自动进行类型转换（给 ？添加单引号，替换成 ’ ？’），所以实际的 sql 语句如下：

SELECT * FROM customer WHERE cust_name LIKE '%'李'%'

很明显这个是错误的 sql 语句，所以模糊查询时无法这么实现。

#{} 实现模糊查询

如果一定要用 #{} 实现模糊查询，必须以下面的方式实现，查询名字中带有李字的人：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="myTest">

<!--根据用户名模糊查询客户-->
<select id="queryCustomerByName" parameterType="String" resultType="com.itlike.domain.Customer">
    SELECT * FROM customer WHERE cust_name LIKE #{cust_name}
</select>

</mapper>

查询代码如下：

 @Test
    public void test3(){
        SqlSession sqlSession = MybatisUtils.openSession();
        List<Customer> customers = sqlSession.selectList("queryCustomerByName", "%李%");
        for (Customer customer : customers) {
            System.out.println(customer);
        }
        sqlSession.close();
    }

运行效果：成功查询出表中姓名有李的人，并且封装成对象。

==>  Preparing: SELECT * FROM customer WHERE cust_name LIKE ? 
==> Parameters: %李%(String)
<==    Columns: cust_id, cust_name, cust_profession, cust_phone, email
<==        Row: 2, 李白, 刺客, 18977665521, libai@163.com
<==        Row: 11, 李信, 战士, 13728964922, lixin@qq.com
<==      Total: 2
Customer{cust_id=2, cust_name='李白', cust_profession='刺客', cust_phone='18977665521', email='libai@163.com'}
Customer{cust_id=11, cust_name='李信', cust_profession='战士', cust_phone='13728964922', email='lixin@qq.com'}

sql 语句中的 ${}

• 表示拼接字符串，不防 sql 注入。
• 通过 ${} 可以将 parameterType 传入的内容拼接在 sql 中并且不进行 jdbc 类型转换。
• 可以接收简单类型或 pojo 属性值。
• 如果 parameterType 传输单个简单类型值，${} 括号中只能是 value。

${} 实现模糊查询

用 ${} 实现模糊查询方法如下，查询名字中带有李字的人：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="myTest">

<!--根据用户名模糊查询客户-->
<select id="queryCustomerByName" parameterType="String" resultType="com.itlike.domain.Customer">
    SELECT * FROM customer WHERE cust_name LIKE '%${value}%';
</select>

</mapper>

查询代码如下：

public void test3(){
    SqlSession sqlSession = MybatisUtils.openSession();
    List<Customer> customers = sqlSession.selectList("queryCustomerByName", "李");
    for (Customer customer : customers) {
        System.out.println(customer);
    }
    sqlSession.close();
}

运行结果：成功查询出表中姓名中有李的人，并且封装成对象。·

==>  Preparing: SELECT * FROM customer WHERE cust_name LIKE '%李%'; 
==> Parameters: 
<==    Columns: cust_id, cust_name, cust_profession, cust_phone, email
<==        Row: 2, 李白, 刺客, 18977665521, libai@163.com
<==        Row: 11, 李信, 战士, 13728964922, lixin@qq.com
<==      Total: 2
Customer{cust_id=2, cust_name='李白', cust_profession='刺客', cust_phone='18977665521', email='libai@163.com'}
Customer{cust_id=11, cust_name='李信', cust_profession='战士', cust_phone='13728964922', email='lixin@qq.com'}

#{} 与 ${} 对比

#{} 不可行方法：
映射文件中的sql：SELECT * FROM customer WHERE cust_name LIKE '%#{name}%'; // 没有固定参数名，可以不叫 name
传入参数："李"
运行时显示的sql：SELECT * FROM customer WHERE cust_name LIKE '%?%';
实际的sql：SELECT * FROM customer WHERE cust_name LIKE '%'李'%';

#{} 可行方法：
映射文件中的sql：SELECT * FROM customer WHERE cust_name LIKE #{name} // 没有固定参数名，可以不叫 name
传入参数："%李%"
运行时显示的sql：SELECT * FROM customer WHERE cust_name LIKE ? 
实际的sql：SELECT * FROM customer WHERE cust_name LIKE '%李%';


${} 可行方法：
映射文件中的sql：SELECT * FROM customer WHERE cust_name LIKE '%${value}%'; // 参数名必须叫 value
传入参数："李"
运行时显示的sql：SELECT * FROM customer WHERE cust_name LIKE '%李%';