实现动态SQL语句:
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--绑定名称空间-->
<mapper namespace="mybatis.dao.EmployeeMapperDynamicSQL">
<!--测试动态SQL的xml文件-->
<!--实现动态SQL的标签
if:判断
choose(when,otherwise);分支选择
trim(where(封装),set(封装条件)):字符截取
foreach
-->
<!--查询员工,要求,携带了哪个字段查询条件就带上这个字段的值
//按照条件查询
public List<Employee> getEmpsByCondutionIf(Employee employee);
-->
<select id="getEmpsByCondutionIf" resultType="mybatis.bean.Employee">
select id,last_name lastName,gender,email FROM tbl_employee
<where><!--where标签代替sql语句中的where防止出现id=null但是后面有不等于null而出现的sql语法错误 也可以用1=1代替-->
<!--test:判断表达式(OGNL)-->
<if test="id!=null">
id=#{id}
</if>
<if test="lastName !=null and lastName != '' ">
AND last_name like #{lastName}
</if>
<!-- email.trim-->
<if test="email !=null and email.trim()!='' ">
AND email = #{email}
</if>
<if test="gender == 0 or gender == 1">
AND gender = #{gender}
</if>
</where>
</select>
<!--
//测试trim标签
public List<Employee> getEmpsByCondutionIfAndTrim(Employee employee);
-->
<select id="getEmpsByCondutionIfAndTrim" resultType="mybatis.bean.Employee">
select id,last_name lastName,gender,email
FROM tbl_employee
<!--trim 标签 sql语句中后面多出的and 和 or where标签不能解决
trim标签体中是整个字符串拼接后的结果
prefix:给拼串后的字符串加一个前缀
prefixOverrides:前缀覆盖;前面多了什么给去掉
suffix:给拼串后的字符串加一个后缀
suffixOverrides:后缀覆盖;后面多了什么给去掉
-->
<trim prefix="where" suffixOverrides="and">
<if test="id!=null">
id=#{id} AND
</if>
<if test="lastName !=null and lastName != '' ">
last_name like #{lastName} AND
</if>
<!-- email.trim-->
<if test="email !=null and email.trim()!='' ">
email = #{email} AND
</if>
<if test="gender == 0 or gender == 1">
gender = #{gender}
</if>
</trim>
</select>
<!--
查询员工:如果带了id用id查,带了lastName 用lastName查.....多选一 都不带 查所有
//按照id或者lastName查询员工
public List<Employee> getEmpsByCondutionChoose(Employee employee);
-->
<select id="getEmpsByCondutionChoose" resultType="mybatis.bean.Employee">
<!--bind 绑定 -->
<bind name="my_lastname" value="'%'+lastName+'%'"></bind>
SELECT id,last_name lastName,gender,email
FROM tbl_employee
<where>
<choose>
<when test="id != null">
id=#{id}
</when>
<when test="lastName != null and lastName != '' ">
last_name LIKE #{my_lastname}
</when>
<when test="email != null and email != '' ">
email LIKE #{email}
</when>
<when test="gender == 0 and gender == 1 ">
gender = #{gender}
</when>
<otherwise >
1=1
</otherwise>
</choose>
</where>
</select>
<!--
要求,传入的employee带了什么参数则更新哪个参数(区别之前的全更新)
//employee 传入什么则更新什么
public void updateEmp(Employee employee);
-->
<update id="updateEmp">
UPDATE tbl_employee
<set>
<if test="lastName!=null and lastName !='' ">
last_name = #{lastName},
</if>
<if test="email !=null and email !='' ">
email = #{email},
</if>
<if test="gender == 0 or gender == 1">
gender = #{gender},
</if>
</set>
<where>
id=#{id}
</where>
</update>
<!--
//测试foreach 传入一个id的list集合查询员工
public List<Employee> getEmpsByList (List<Integer> list);
-->
<select id="getEmpsByList" resultType="mybatis.bean.Employee">
SELECT id,last_name lastName,gender,email
FROM tbl_employee
WHERE id IN
<!--
foreach:遍历集合
collection:需要遍历哪个集合
item:将遍历出的数据赋值给谁
separator:每个遍历中间用什么隔开
open:遍历的集合用什么开始
close:遍历的集合用什么结束
-->
<foreach collection="list" item="ltem_id" separator="," open="(" close=")">
#{ltem_id}
</foreach>
</select>
<!--
//测试利用foreach标签实现批量插入
public void addEmpsByForeach(@Param("emps")List<Employee> employees);
-->
<insert id="addEmpsByForeach">
INSERT INTO tbl_employee( last_name,gender,email)
VALUES
<foreach collection="emps" item="item_emps" separator=",">
(#{item_emps.lastName},#{item_emps.gender},#{item_emps.email})
</foreach>
</insert>
<!--
两个内置参数:
_parameter:代表整个参数
单个参数:_parameter就是代表这个参数
多个参数:_parameter就是代表这个map
_databaseId:如果配置了databaseIdProvider标签
_databaseId就是代表当前数据库的别名
-->
</mapper>