SSM--MyBatis中#和$区别

47. SSM--MyBatis中#和$区别

String Substitution

By default, using the #{} syntax will cause MyBatis to generate PreparedStatement properties and set the values safely against the PreparedStatementparameters (e.g. ?). While this is safer, faster and almost always preferred, sometimes you just want to directly inject an unmodified string into the SQL Statement. For example, for ORDER BY, you might use something like this:

ORDER BY ${columnName}

Here MyBatis won't modify or escape the string.

NOTE  It's not safe to accept input from a user and supply it to a statement unmodified in this way. This leads to potential SQL Injection attacks and therefore you should either disallow user input in these fields, or always perform your own escapes and checks.

字符串替换

默认情况下,使用#{}格式的语法会导致 MyBatis 创建PreparedStatement语句属性并安全地设置值（比如?）。这样做更安全，更迅速，通常也是首选做法，不过有时你只是想直接在 SQL 语句中插入一个不改变的字符串。比如，像 ORDER BY，你可以这样来使用：

ORDER BY ${columnName}

这里 MyBatis 不会修改或转义字符串。

NOTE  以这种方式接受从用户输出的内容并提供给语句中不变的字符串是不安全的，会导致潜在的 SQL 注入攻击，因此要么不允许用户输入这些字段，要么自行转义并检验。

---

---

#{}解析为JDBC预编译语句（PreparedStatement）的参数标记符（？）。

select * from student where name=#{name}

解析为：

select * from student where name=?

一个#{}被解析为一个参数占位符?。

DEBUG [main] - ==>  Preparing: select id,name,age,gender,address from student where name=?

DEBUG [main] - ==> Parameters: lisi(String)

DEBUG [main] - <==      Total: 2 

select * from student where name='张三'

${}仅仅为一个 纯粹的字符串替换

select * from student where name='${name}'

解析为

select * from student where name='lisi'

< select id= "selectByName" parameterType= "string" resultType= "Student" >

   select id,name,age,gender,address

   from student

   where name= '${value}'

</ select >

DEBUG [main] - ==>  Preparing: select id,name,age,gender,address from student where name='lisi'

DEBUG [main] - ==> Parameters:

DEBUG [main] - <==      Total: 2

< select id= "selectByName" parameterType= "Map" resultType= "Student" >

   select id,name,age,gender

   from student

   where name='${name}' and gender=#{gender}

</ select >

DEBUG  [http-nio-8080-exec-5] -  ==>  Preparing: SELECT  * FROM student  WHERE name = '李四' AND  gender = ?

DEBUG  [http-nio-8080-exec-5] -  ==> Parameters: n(String)

DEBUG  [http-nio-8080-exec-5] -  <==      Total: 1

---

如何使用：

1、能使用#{}地方就使用#{}

• 为了性能的考虑，相同的预编译sql可以重复使用。
• ${}在预编译之前就被变量替换了，这样就会存在sql注入的问题，例如如下的sql。

select * from ${_tableName} where name='${name}'

假如，传递tableName参数的值为： student; delete student;-- 。预编译之后的sql就变为：

select * from student; delete student;--  where name='${name}'

--之后的sql将作为注释，不起作用，本来的一条查询语句偷偷包含了一条删除表数据的sql。

2、表名作为变量或者ORDER BY，必须要用${}

< select id= "selectByName" resultType= "Student" >

   select id,name,age,gender,address

   from #{tableName}

   where name=#{name}

</ select >

''student' where name='lisi''

select id,name,age,gender,address from   'student' where name='lisi'

上述 sql 语句是存在语法错误的，表名不能加单引号 ''

PreparedStatement如果是字符串？会自动加上引号，如果是整数就不做处理。

< select id= "selectByName" resultType= "Student" >

   select id,name,age,gender,address

   from ${tableName}

   where name= '${name}'

</ select >

 select id,name,age,gender,address from student where name= 'lisi'

Order By:

< select id= "selectAll" parameterType= "string" resultType= "Student" >

      select id,name,age,gender,address

      from student

      order by #{column} desc

    </ select >

select id,name,age,gender,address from student order by 'id' desc

没有报错， 但是结果是不对，后面的order by id desc没有起作用。

< select id= "selectAll" parameterType= "string" resultType= "Student" >

   select id,name,age,gender,address

   from student

   order by ${value} desc

</ select >

select id,name,age,gender,address from student order by id desc

---

---

#和$在like中使用： where name like '%张%'

< select id= "selectByName" resultType= "Student" >

   select id,name,age,gender,address

   from ${tableName}

   where name like '%${name}%'

</ select >

select id,name,age,gender,address from student where name like '%li%'

MySQL的 CONCAT（）函数用于将多个字符串连接成一个字符串，是最重要的mysql函数之一。

CONCAT(str1,str2,...)

< select id= "selectByName" resultType= "Student" >

   select id,name,age,gender,address

   from ${tableName}

   where name like concat('%', #{name}, '%')

</ select >

DEBUG [main] - ==>  Preparing: select id,name,age,gender,address from student where name like concat('%', ?, '%')

DEBUG [main] - ==> Parameters: li(String)

< select id= "selectByName" resultType= "Student" >

   select id,name,age,gender,address

   from ${tableName}

   where name like "%"#{name}"%"

</ select >

DEBUG [main] - ==>  Preparing: select id,name,age,gender,address from student where name like "% "? "%"

DEBUG [main] - ==> Parameters: li(String)

---

---

---

在MyBaits的sql中如何进行变量的运算：

limit子句中是不允许运算的

< select id= "selectPageList" parameterType= "Map" resultType= "Student" >

      select id,name,age,gender,address

      from student

      limit (#{pageNo}-1)*#{pageSize},#{pageSize}

    </ select >

select id,name,age,gender,address    from student   limit (?-1)*?,?

< select id= "selectPageList" parameterType= "Map" resultType= "Student" >

   select id,name,age,gender,address

   from student

   limit (${pageNo}-1)*${pageSize},${pageSize}

</ select >

select id,name,age,gender,address    from student   limit (1-1)*3,3

将#{}变成${},也就是相当于limit后面的值是定值，sql语句是拼接而成的而不是占位符赋值运算：

< select id= "selectPageList" parameterType= "Map" resultType= "Student" >

   select id,name,age,gender,address

   from student

   limit ${(pageNo-1)*pageSize}, ${pageSize}

</ select >

select id,name,age,gender,address from student limit 0,3