动态SQL
if
请求地址: http://localhost:8080/dept/dynamic/condition/if
如果不带id会报错
解决:添加 where 1=1 或者 使用where标签
where
where标签会替换掉第一个and,如果and在后边就会说sql语句有错误,这个时候就要和trim标签一起来使用mapper.xml
<select id="getEmpsConditionWhere" resultType="com.banana.mybatis.bean.Employee">
SELECT
id,last_name,email,gender
FROM
tbl_employee
<where>
<if test="id != null">
id = #{id} AND
</if>
<if test="lastName != null and lastName != ''">
last_name LIKE #{lastName} AND
</if>
<if test="email != null and email != ''">
email = #{email} AND
</if>
<if test="gender == 1 or gender == 0">
gender = #{gender}
</if>
</where>
</select>
请求地址:http://localhost:8080/dept/dynamic/condition/where
请求参数:{“id”:1}
sql执行出错,执行的sql语句为
SELECT id,last_name,email,gender FROM tbl_employee WHERE id = ? AND
可以明显看到sql出了问题,所以我们需要把后边的and去掉,这时我们就可以使用到trim标签,把and去掉
trim
trim共有四个属性:
prefix:加前缀,
prefixOverrides:去前缀,
suffix:加后缀,
suffixOverrides:去后缀
在where标签内添加一个trim标签,标明我们要去掉and后缀即可,sql执行就不会出错了
<select id="getEmpsConditionWhere" resultType="com.banana.mybatis.bean.Employee">
SELECT
id,last_name,email,gender
FROM
tbl_employee
<where>
<trim suffixOverrides="AND">
<if test="id != null">
id = #{id} AND
</if>
<if test="lastName != null and lastName != ''">
last_name LIKE #{lastName} AND
</if>
<if test="email != null and email != ''">
email = #{email} AND
</if>
<if test="gender == 1 or gender == 0">
gender = #{gender}
</if>
</trim>
</where>
</select>
choose
按照条件查询,相当于switch case,从前向后判断,如果有id,就直接按id查,如果有lastName,就按lastName查,不多赘述
<select id="getEmpsConditionChoose" resultType="com.banana.mybatis.bean.Employee">
SELECT
id,last_name,email,gender
FROM
tbl_employee
WHERE
<choose>
<when test="id != null">
id = #{id}
</when>
<when test="lastName != null and lastName != ''">
last_name LIKE #{lastName}
</when>
<when test="email != null and email != ''">
email = #{email}
</when>
<otherwise>
gender = #{gender}
</otherwise>
</choose>
</select>
set
set用于更新的sql语句中
在update时,多条件更新,每个属性后面要加逗号“,”,这个时候可能会出现多一个“,”的情况,此时我们就可以使用set去掉后边的“,”,除此之外我们使用前面说的trim也可实现当前的操作
<update id="updateEmpsConditionSet">
UPDATE tbl_employee
<set>
<if test="id != null">
id = #{id},
</if>
<if test="lastName != null and lastName != ''">
last_name LIKE #{lastName},
</if>
<if test="email != null and email != ''">
email = #{email},
</if>
<if test="gender == 1 or gender == 0">
gender = #{gender}
</if>
</set>
</update>
foreach
查询
foreach用来查参数是一个列表,比如说使用IN操作符就会用到
foreach的几个属性:
collection:元素集合,
item_id:取出来的每一项,
separator:间隔符,
open:前缀,close:后缀,
index:遍历list的时候是索引,遍历map的时候是key
<select id="getEmpsConditionForeach" resultType="com.banana.mybatis.bean.Employee">
SELECT
id,last_name,gender,email
FROM
tbl_employee
WHERE id IN
<foreach collection="ids" item="item_id" separator="," open="(" close=")">
#{item_id}
</foreach>
</select>
插入
插入多条记录
<insert id="insertEmps">
insert into tbl_employee(last_name,gender,email,d_id)
VALUES
<foreach collection="emps" item="emp" separator=",">
(#{emp.lastName},#{emp.gender},#{emp.email},#{emp.dept.id})
</foreach>
</insert>
_databaseId
可以通过这个参数来判断当前是何种环境或者种类的数据库,然后据此来执行对应数据库的sql
bind
可以修改OGNL表达式的值,下面这个例子解释了_databasedId和bind,如果使用了bind,创建一个新的变量_lastName在lastName的基础上做修改,达到想要的形式
<select id="getEmpsTestInnerParameter" resultType="com.atguigu.mybatis.bean.Employee">
<!-- bind:可以将OGNL表达式的值绑定到一个变量中,方便后来引用这个变量的值 -->
<bind name="_lastName" value="'%'+lastName+'%'"/>
<if test="_databaseId=='mysql'">
select * from tbl_employee
<if test="_parameter!=null">
where last_name like #{_lastName}
</if>
</if>
<if test="_databaseId=='oracle'">
select * from employees
<if test="_parameter!=null">
where last_name like #{_parameter.lastName}
</if>
</if>
</select>
sql片段
添加sql片段,后续的xml中的标签用到时就可以直接引入
<sql id="cols">
id,last_name,email,gender
</sql>
<select id="getEmpsConditionIf" resultType="com.banana.mybatis.bean.Employee">
SELECT
<include refid="cols"/>
FROM
tbl_employee
WHERE
<if test="id != null">
id = #{id}
</if>
<if test="lastName != null and lastName != ''">
AND last_name LIKE #{lastName}
</if>
<if test="email != null and email != ''">
AND email = #{email}
</if>
<if test="gender == 1 or gender == 0">
AND gender = #{gender}
</if>
</select>