${}
$是将传入的数据直接显示生成sql语句
${}: 仅仅为一个纯碎的 string 替换,在动态 SQL 解析阶段将会进行变量替换
#{}
#{}: 解析为一个 JDBC 预编译语句(prepared statement)的参数标记符,一个 #{ } 被解析为一个参数占位符 。
使用#可以很大程度上防止sql注入。(语句的拼接),但是如果使用在order by 中就需要使用$.
在大多数情况下还是经常使用#,但在不同情况下必须使用$.
select
#{selectClause} as id,sum(total) as total
from task_count
where type =#{type } province_id = #{provinceId } and city_id = #{cityId}
group by #{groupClause} having 1=1
Mybatis xml中有以上类似sql,运行后报错
###Error querying database. Cause: org.postgresql.util.PSQLException: ERROR: column “dept_id” must appear in the GROUP BY clause or be used in an aggregate function
发现预编译时sql如下:
select dept_id as id, sum(total) as total
from task_count
where type = ? and province_id = ? and city_id = ?
group by ? having 1=1