网站开发公司网站收录申请
目录
Mybatis动态SQL介绍编辑
一、案例
①Mapper层
②测试类
③EmpMapper.xml
④结果
二、标签
（一）if where标签
①EmpMapper.xml
②案例
③总结
（二）foreach标签
①SQL语句
②Mapper层
③EmpMapper.xml
④测试类
⑤结果
（三）sql&include标签
①EmpMapper.xml
②总结
XML映射文件（配置文件）
①EmpMapper.xml
②Mapper层
③测试类
④思考
⑤总结
Mybatis动态SQL介绍
一、案例
ctrl+alt+l将SQL语句格式化
List<Emp> empList= empMapper.list("z",null,null,null);
当查询条件不完整时，会查询不到数据，因此就需要编写动态SQL
①Mapper层
package com.itheima.mapper;import com.itheima.pojo.Emp;
import org.apache.ibatis.annotations.*;import java.time.LocalDate;
import java.util.List;@Mapper
public interface EmpMapper {public List<Emp> list(String name, Short gender, LocalDate begin,LocalDate end);}②测试类
package com.itheima;import com.itheima.mapper.EmpMapper;
import com.itheima.pojo.Emp;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.List;@SpringBootTest
class SpringbootMybatisCrudApplicationTests {@Autowiredprivate EmpMapper empMapper;@Testpublic void testList(){List<Emp> empList= empMapper.list("z",null,null,null);System.out.println(empList);}
}③EmpMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.itheima.mapper.EmpMapper"><!-- resultType:单条记录所封装的类型-->
<!-- <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>--><select id="list" resultType="com.itheima.pojo.Emp">select *from empwhere<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>
</mapper>④结果
二、标签
（一）if where标签
①EmpMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.itheima.mapper.EmpMapper"><!-- resultType:单条记录所封装的类型-->
<!-- <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>--><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 gender=#{gender}</if><if test="begin!=null and end!=null">and entrydate between #{begin} and #{end}</if>order by update_time desc</where></select>
</mapper>②案例
③总结
（二）foreach标签
批量删除员工信息
①SQL语句
delete from emp where id in(18,19,20);
②Mapper层
EmpMapper.java
package com.itheima.mapper;import com.itheima.pojo.Emp;
import org.apache.ibatis.annotations.*;
import java.util.List;@Mapper
public interface EmpMapper {//根据ID批量删除员工信息public void deleteByIds(List<Integer> ids);}③EmpMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.itheima.mapper.EmpMapper"><!--批量删除员工--><delete id="deleteByIds">delete from empwhere id in<foreach collection="ids" item="id" open="(" close=")" separator=",">#{id}</foreach></delete>
</mapper>④测试类
package com.itheima;import com.itheima.mapper.EmpMapper;
import com.itheima.pojo.Emp;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.Arrays;
import java.util.List;@SpringBootTest
class SpringbootMybatisCrudApplicationTests {@Autowiredprivate EmpMapper empMapper;@Testpublic void testDeleteByIds(){List<Integer> ids= Arrays.asList(13,14,15);empMapper.deleteByIds(ids);}
}⑤结果
（三）sql&include标签
查询的时候不建议使用select *，而是把所有的字段罗列出来
①EmpMapper.xml
②总结
XML映射文件（配置文件）
源文件放在java中，而配置文件放在resources中
官网：mybatis – MyBatis 3 | 简介
①EmpMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.itheima.mapper.EmpMapper">
<!-- resultType:单条记录所封装的类型--><select id="list" resultType="com.itheima.pojo.Emp">select * from emp where name like concat('%',#{name},'%') and gender=#{gender} andentrydate between #{begin} and #{end} order by update_time desc</select>
</mapper>②Mapper层
package com.itheima.mapper;import com.itheima.pojo.Emp;
import org.apache.ibatis.annotations.*;import java.time.LocalDate;
import java.util.List;@Mapper
public interface EmpMapper {public List<Emp> list(String name, Short gender, LocalDate begin,LocalDate end);}③测试类
package com.itheima;import com.itheima.mapper.EmpMapper;
import com.itheima.pojo.Emp;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;import java.util.List;@SpringBootTest
class SpringbootMybatisCrudApplicationTests {@Autowiredprivate EmpMapper empMapper;@Testpublic void testList(){List<Emp> empList= empMapper.list("z",(short)1,LocalDate.of(2010,1,1),LocalDate.of(2020,1,1));System.out.println(empList);}
}④思考
mapper映射文件还有一个好处，修改sql语句不用重启项目
在方法上实现动态的条件查询就会使接口过于臃肿
如果操作语句多了，直接也在注解上面比较混乱
如果要做手动映射封装实体类的时候 xml方便，项目中会常用
用xml,因为查询的条件会变化，直接写在注解里面的话会使接口过于臃肿
这两个各自找各自对应的，原来是注解绑定，现在是通过路径和方法名绑定
多条件查询要写动态sql用映射文件比较合适，简单的可以直接注解方式
终于找到问题了，xml里的sql语句不能拼接，只能是一长条，运行才不报错
执行list()方法时，根据全限定类名找到对应的namespace ，再找到id为这个方法的SQL语句就可以执行了
⑤总结
