JavaWeb Day09 Mybatis-基础操作02-XML映射文件动态SQL

目录

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语句就可以执行了

⑤总结