实现复杂的SQL功能,建议使用XML来配置映射语句
SQL映射配置文件的规范:
1.XML映射文件的名称与Mapper接口名称一致,并且将XML映射文件和Mapper接口放在同一个包下面(同包同名)
2.XML映射文件的namespace属性为Mapper接口全限定名一致
3.两个地方的方法名要保持一致,并且保持参数类型和返回值类型一致
映射文件模板
<?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=" 接口全限定名 ">
<select id="接口方法名" resultType=" 返回值类型 ">
<!-- sql语句 -->
</select>
</mapper>
if(用于判断条件是否成立,使用test属性进行条件判断,条件为true,则会拼接SQL)
Mapper接口方法:
public List<Emp> list(String name, Short gender , LocalDate begin ,LocalDate end);
xml:
<select id="list" resultType="com.itheima.pojo.Emp">
select id, username, password, name, gender, image, job, entrydate, dept_id, create_time, update_time from emp
where
<if test="name != null">
name like concat('%',#{name},'%')
</if>
<if test="gender != null">
and gender = #{gender}
</if>
<if test="begin != null and end != null">
and entrydate between #{begin} and #{end}
</if>
order by update_time desc
</select>
但是如果没有传第一个参数就会多一个where和and ,我们只需要通过where改造上述功能
<select id="list" resultType="com.itheima.pojo.Emp">
select id, username, password, name, gender, image, job, entrydate, dept_id, create_time, update_time from emp
<where>
<if test="name != null">
name like concat('%',#{name},'%')
</if>
<if test="gender != null">
and gender = #{gender}
</if>
<if test="begin != null and end != null">
and entrydate between #{begin} and #{end}
</if>
</where>
order by update_time desc
</select>
更新员工(动态更新员工信息,如果字段有值,则更新,如果字段没有值,则不更新)
接口方法
public void update(Emp emp);
xml:
<update id="update">
update emp set
<if test="username != null">
username=#{username},
</if>
<if test="name != null">
name=#{name},
</if>
<if test="gender != null">
gender=#{gender},
</if>
<if test="image != null">
image=#{image},
</if>
<if test="job != null">
job=#{job},
</if>
<if test="entrydate != null">
entrydate=#{entrydate},
</if>
<if test="deptId != null">
dept_id=#{deptId},
</if>
<if test="updateTime != null">
update_time=#{updateTime}
</if>
where id=#{id}
</update>
测试
@Test
public void testUpdate2(){
Emp emp = new Emp();
emp.setId(19);
emp.setUsername("syq");
empMapper.update(emp);
}
在输出的SQl末尾出现了一个多余的逗哈”,“造成SQL语句语法错误报错。而解决这个问题,我可以通过标签解决。添加标签<set>
<update id="update">
update emp
<set>
<if test="username != null">
username=#{username},
</if>
<if test="name != null">
name=#{name},
</if>
<if test="gender != null">
gender=#{gender},
</if>
<if test="image != null">
image=#{image},
</if>
<if test="job != null">
job=#{job},
</if>
<if test="entrydate != null">
entrydate=#{entrydate},
</if>
<if test="deptId != null">
dept_id=#{deptId},
</if>
<if test="updateTime != null">
update_time=#{updateTime}
</if>
</set>
where id=#{id}
</update>
foreach:对于员工的删除功能,既支持删除单条记录,又支持批量删除。
SQL语句:
delete from emp where id in (1,2,3);
接口方法:
//批量删除
public void deleteByIds(List<Integer> ids);
xml映射文件
<delete id="deleteByIds">
delete from emp where id in
<foreach collection="ids" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</delete>
foreach属性介绍:
- collection:集合名称
- item:集合遍历出来的元素/项
- separator:每一次遍历使用的分隔符
- open:遍历开始前拼接的片段
- close:遍历结束后拼接的片段
sql/include
SQL对重复代码进行抽取
<sql id="commonSelect">
include 引用抽取的代码
<include refid="commonSelect"/>