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 {
    @Autowired
    private EmpMapper empMapper;
   
    @Test
    public 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 mapper
        PUBLIC "-//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
    </select>
</mapper>

④结果

二、标签

（一）if where标签

①EmpMapper.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">
    <!--    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 mapper
        PUBLIC "-//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 emp
        where 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 {
    @Autowired
    private EmpMapper empMapper;

    @Test
    public 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 mapper
        PUBLIC "-//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>
</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 {
    @Autowired
    private EmpMapper empMapper;
   
    @Test
    public 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语句就可以执行了

⑤总结