XML映射文件
演示:
XML文件内容
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.itheima.mapper.EmpMapper">
<select id="list" resultType="com.itheima.pojo.Emp">
select *
from emp
where name like concat('%', #{name}, '%')
and gender = #{gender}
and entrydate between #{begin} and #{end}
order by update_time desc
</select>
</mapper>
接口内list方法:
Test方法:
@Test
public void testList() {
List<Emp> empList = empMapper.list("张", (short) 1, LocalDate.of(2010, 1, 1), LocalDate.of(2023, 8, 17));
}
XML文件与EmpMapper文件名一致:
结果:
总结:简单的SQL语句使用注解可使代码更加简洁易懂,较为复杂的SQL使用XML可避免代码臃肿。
动态SQL
<if>、<where>
演示:
XML文档内容:
<select id="list" resultType="com.itheima.pojo.Emp">
select *
from emp
<where>
<if test="name != null">
name like concat('%', #{name}, '%')
</if>
<if test="gender != null and name !=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>
Test测试方法:
@Test
public void testList() {
// List<Emp> empList = empMapper.list("张", (short) 1, LocalDate.of(2010, 1, 1), LocalDate.of(2023, 8, 17));
List<Emp> empList1=empMapper.list(null,(short)1,null,null);
}
结果是返回所有男士的数据。
foreach :
演示:
批量删除数据
XML文件代码:
<delete id="deleteById">
delete from emp where id in
<foreach collection="idList" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</delete>
接口中方法:
测试方法:
@Test
public void testDeleteById(){
List<Integer> idList= Arrays.asList(13,14,15);
empMapper.deleteById(idList);
}
结果:表中id 为13,14,15的行数据删除。
SQL片段
以上内容均学自b站黑马JavaWeb教程