1.update/insert/select/delete
标签要和SQL语句要执行的类型对应好
2.#{}表示一个占位符(将参数内容加引号,以字符串形式拼接到SQL中),可以防止SQL注入;${}将内容直接拼接在SQL中,不能防止SQL注入。
eg:
参数形式为:1 union select user,password from users
select * from users WHERE user_id = #{id};=>select * from users WHERE user_id = '1 union select user,password from users';
将参数作为整个参数,没有满足要求则不会返回
select * from users WHERE user_id = ${id};=>select * from users WHERE user_id = 1 union select user,password from users
加上参数后实则为两个SQL语句,可以在参数中输入union关联想要查询的任何内容
3.where条件动态拼接(where子句能够自动消除第一个and)
<sql id="where1">
<if test="consno != null and consno != ''">
and cons_no = #{consno}
</if>
<if test="usertype != null and usertype !=''">
and user_type=#{usertype}
</if>
</sql>
<select id="consNo" resultType="User">
select * from user
<where>
<include refid="where1"/>
</where>
</select>
4.set标签
set标签中内容为空时,整个set标签去掉
<update id="update1">
update user
<set>
<if test="name != null">
NAME = #{name},
</if>
</set>
</update>
如果name=null,则语句为update user
5.foreach标签
在SQL中需要用到 in 时,在xml中可以用foreach循环来组合需要满足的 in 的数组
<foreach>标签中 collection :变量所在数组,index 位置,item 元素,open 在sql语句中变量开始位置符号,separator 在sql语句中变量间分隔的符号, close 在sql语句中变量结束位置符号
例子一:循环数组List
<!-- 数据库中 -->
select * from TB_SET
where plantid in ('300','301')
<!-- xml中 -->
select * from TB_SET
where plantid in
<foreach collection="List" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
例子二:循环map
<!-- 数据库中 -->
delete from table_name
where version = 'v0' and (
(pdate = '2022-01-02' and subject = '12312') or
(pdate = '2022-01-03' and subject = '12352')
)
<!-- xml中 -->
delete from ${tableName}
where
version = 'v0'
<if test="map !=null">
and
<foreach collection="map" item="item" separator="or" open="(" close=")">
(pdate = #{item.pdate} and subject = #{item.subjectNo})
</foreach>
</if>
6.choose标签
<choose>
<when test="name != null">
order by ${name}
</when>
<otherwise>
order by id desc
</otherwise>
</choose>