【MyBatis】#{} 和 ${} 区别

取值引用

#{}

自动给其添加引号

<select id="findByName" parameterType="String" resultMap="studentResultMap">
  SELECT * FROM user WHERE username=#{value}
</select>

SELECT * FROM user WHERE username="Amy"

${}

<select id="findByName" parameterType="String" resultMap="studentResultMap">
  SELECT * FROM user WHERE username=${value}
</select>

SELECT * FROM user WHERE username=Amy

SQL 注入

#{}可以防止SQL注入而${}却不行

<select id="findByName" parameterType="String" resultMap="studentResultMap">
  SELECT * FROM user WHERE username='${value}'
</select>

SELECT * FROM user WHERE username='' OR 1=1 OR '';

适用场景

#{} 和 ${} 均适用场景——模糊查询

${}——模糊查询

<select id="findAddByName" parameterType="String" resultMap="studentResultMap">
  SELECT * FROM user WHERE username LIKE '%${value}%'
</select>

SELECT * FROM user WHERE username LIKE '%Amy%';

#{}——模糊查询

<select id="findAddByName" parameterType="String" resultMap="studentResultMap">
  SELECT * FROM USER WHERE username LIKE CONCAT('%', #{username}, '%')
</select>

SELECT * FROM USER WHERE username LIKE CONCAT('%', 'Amy','%');

只能使用${}的场景——字段、表明（不需要加引号的）

使用#{}——错误

<select id="findAddByName3" parameterType="String" resultMap="studentResultMap">
  SELECT * FROM USER WHERE username LIKE '%Am%' ORDER BY #{value} ASC
</select>

SELECT * FROM USER WHERE username LIKE '%Am%' ORDER BY 'sex' ASC;

改为${}——正确

<select id="findAddByName3" parameterType="String" resultMap="studentResultMap">
  SELECT * FROM USER WHERE username LIKE '%Am%' ORDER BY ${value} ASC
</select>

SELECT * FROM USER WHERE username LIKE '%Am%' ORDER BY sex ASC;