目录
1. 什么是$和#
Mybatis支持sql标签和参数替换,支持动态sql拼接和静态参数替换.
${}用于动态sql构建,属于mybatis的动态sql构建过程, 在数据库操作之前
#{}解析为一个 JDBC 预编译语句(prepared statement)的参数标记符。sql 预编译指的是数据库驱动在发送 sql 语句和参数给 DBMS 之前对 sql 语句进行编译,这样 DBMS 执行 sql 时,就不需要重新编译。
2. 如何使用
一种是mybatis插件自动生成的,如下图:
<update id="updateByExampleSelective" parameterType="map">
<!--
WARNING - @mbg.generated
This element is automatically generated by MyBatis Generator, do not modify.
This element was generated on Tue Apr 09 19:43:24 CST 2019.
-->
update app_info
<set>
<if test="record.appId != null">
app_id = #{record.appId,jdbcType=BIGINT},
</if>
<if test="record.appVersionCode != null">
app_version_code = #{record.appVersionCode,jdbcType=INTEGER},
</if>
<if test="record.appVersionName != null">
app_version_name = #{record.appVersionName,jdbcType=VARCHAR},
</if>
<if test="record.appName != null">
app_name = #{record.appName,jdbcType=CHAR},
</if>
<if test="record.appSecret != null">
app_secret = #{record.appSecret,jdbcType=CHAR},
</if>
</set>
<if test="_parameter != null">
<include refid="Update_By_Example_Where_Clause"/>
</if>
</update>
一种是自定义的,如下图:
<select id="listAppInfoWithVersionStateByPage" parameterType="com.xiaomi.mina.center.base.AppInfoQUERY"
resultMap="BaseResultMap">
select A.*,E.aliases from (
select
D.*,
if(B.min_app_version_code=D.app_version_code,'0',
if(ifnull(C.online_min_app_version_code,-1) < D.app_version_code,'1','2')) as version_state
from app_info D
join (select app_id,min(app_version_code) as min_app_version_code from app_info group by app_id) B on
D.app_id=B.app_id
left join (select app_id,min(app_version_code) as online_min_app_version_code from app_info where status=4 group
by app_id) C on D.app_id=C.app_id ) A left join app_aliases E on A.app_id=E.app_id
where 1=1
<if test="appId != null">
and A.app_id=#{appId}
</if>
<if test="versionState != null">
and A.version_state=#{versionState}
</if>
<if test="appVersionCode != null">
and A.app_version_code=#{appVersionCode}
</if>
<if test="order != null and prop != null">
order by A.${prop} ${order}
</if>
</select>
3. $和#的原理
${}仅仅为一个纯碎的 string 替换,用于sql动态拼接, 参数可以作为sql的一部分,而不仅仅是值, 发生在mybatis动态SQL解析过程, 容易造成sql注入
#{}被解析为一个参数占位符 ?。使用PreparedStatement,不会存在sql注入
3.1. 举例说明
$使用, 以排序为例, 可以指定数据库排序字段
<if test="order != null and prop != null">
order by A.${prop} ${order}
</if>
#使用
<if test="type != null">
and A.type=#{type}
</if>
select * from user where name = #{name};
解析为
select * from user where name = ?;