MyBatis复习（5）

一、动态SQL

CarMapper.java

package com.powernode.mybatis.mapper;

import com.powernode.mybatis.pojo.Car;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface CarMapper {

    /**
     * 根据id批量删除 使用or关键字。
     * @param ids
     * @return
     */
    int deleteByIds2(@Param("ids") Long[] ids);

    /**
     * 批量插入，一次插入多条Car信息
     * @param cars
     * @return
     */
    int insertBatch(@Param("cars") List<Car> cars);

    /**
     * 批量删除。foreach标签
     * @param ids
     * @return
     */
    int deleteByIds(@Param("ids") Long[] ids);

    /**
     * 使用choose when otherwise标签。
     * @param brand
     * @param guidePrice
     * @param carType
     * @return
     */
    List<Car> selectByChoose(@Param("brand") String brand, @Param("guidePrice") Double guidePrice, @Param("carType") String carType);

    /**
     * 使用set标签
     * @param car
     * @return
     */
    int updateBySet(Car car);

    /**
     * 更新Car
     * @param car
     * @return
     */
    int updateById(Car car);

    /**
     * 使用trim标签
     * @param brand
     * @param guidePrice
     * @param carType
     * @return
     */
    List<Car> selectByMultiConditionWithTrim(@Param("brand") String brand, @Param("guidePrice") Double guidePrice, @Param("carType") String carType);

    /**
     * 使用where标签，让where子句更加的智能。
     * @param brand
     * @param guidePrice
     * @param carType
     * @return
     */
    List<Car> selectByMultiConditionWithWhere(@Param("brand") String brand, @Param("guidePrice") Double guidePrice, @Param("carType") String carType);

    /**
     * 多条件查询
     * @param brand 品牌
     * @param guidePrice 指导价
     * @param carType 汽车类型
     * @return
     */
    List<Car> selectByMultiCondition(@Param("brand") String brand, @Param("guidePrice") Double guidePrice, @Param("carType") String carType);

}

CarMapper.xml

<?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="com.powernode.mybatis.mapper.CarMapper">

    <delete id="deleteByIds2">
        delete from t_car where
        <foreach collection="ids" item="id" separator="or">
            id=#{id}
        </foreach>
    </delete>

    <insert id="insertBatch">
        insert into t_car values
        <foreach collection="cars" item="car" separator=",">
            (null,#{car.carNum},#{car.brand},#{car.guidePrice},#{car.produceTime},#{car.carType})
        </foreach>
    </insert>

    <!--
        foreach标签的属性：
            collection：指定数组或者集合
            item：代表数组或集合中的元素
            separator：循环之间的分隔符
            open: foreach循环拼接的所有sql语句的最前面以什么开始。
            close: foreach循环拼接的所有sql语句的最后面以什么结束。

        collection="ids" 第一次写这个的时候报错了，错误信息是：[array, arg0]
        什么意思？
            map.put("array", 数组);
            map.put("arg0", 数组);
    -->
    <delete id="deleteByIds">
        <!--
        delete from t_car where id in(
        <foreach collection="ids" item="aaaaaaa" separator=",">
            #{aaaaaaa}
        </foreach>
        )
        -->
        delete from t_car where id in
        <foreach collection="ids" item="id" separator="," open="(" close=")">
            #{id}
        </foreach>
    </delete>

    <select id="selectByChoose" resultType="Car">
        select * from t_car
        <where>
            <choose>
                <when test="brand != null and brand != ''">
                    brand like "%"#{brand}"%"
                </when>
                <when test="guidePrice != null and guidePrice != ''">
                    guide_price > #{guidePrice}
                </when>
                <otherwise>
                    car_type = #{carType}
                </otherwise>
            </choose>
        </where>
    </select>

    <update id="updateBySet">
        update t_car
        <set>
            <if test="carNum != null and carNum != ''">car_num = #{carNum},</if>
            <if test="brand != null and brand != ''">brand = #{brand},</if>
            <if test="guidePrice != null and guidePrice != ''">guide_price = #{guidePrice},</if>
            <if test="produceTime != null and produceTime != ''">produce_time = #{produceTime},</if>
            <if test="carType != null and carType != ''">car_type = #{carType},</if>
        </set>
        where
            id = #{id}
    </update>

    <update id="updateById">
        update t_car set
            car_num = #{carNum},
            brand = #{brand},
            guide_price = #{guidePrice},
            produce_time = #{produceTime},
            car_type = #{carType}
        where
            id = #{id}
    </update>

    <select id="selectByMultiConditionWithTrim" resultType="Car">
        select * from t_car
        <!--
            prefix：加前缀
            suffix：加后缀
            prefixOverrides：删除前缀
            suffixOverrides：删除后缀
        -->
        <!--prefix="where" 是在trim标签所有内容的前面添加 where-->
        <!--suffixOverrides="and|or" 把trim标签中内容的后缀and或or去掉-->
        <trim prefix="where" suffixOverrides="and|or">
            <if test="brand != null and brand != ''">
                brand like "%"#{brand}"%" or
            </if>
            <if test="guidePrice != null and guidePrice != ''">
                guide_price > #{guidePrice} and
            </if>
            <if test="carType != null and carType != ''">
                car_type = #{carType}
            </if>
        </trim>

    </select>

    <select id="selectByMultiConditionWithWhere" resultType="Car">
        select * from t_car
        <!--where标签是专门负责where子句动态生成的。-->
        <where>
            <if test="brand != null and brand != ''">
                and brand like "%"#{brand}"%"
            </if>
            <if test="guidePrice != null and guidePrice != ''">
                and guide_price > #{guidePrice}
            </if>
            <if test="carType != null and carType != ''">
                and car_type = #{carType}
            </if>
        </where>
    </select>

    <select id="selectByMultiCondition" resultType="Car">
        select * from t_car where 1 = 1
        <!--
            1. if标签中test属性是必须的。
            2. if标签中test属性的值是false或者true。
            3. 如果test是true，则if标签中的sql语句就会拼接。反之，则不会拼接。
            4. test属性中可以使用的是：
                当使用了@Param注解，那么test中要出现的是@Param注解指定的参数名。@Param("brand")，那么这里只能使用brand
                当没有使用@Param注解，那么test中要出现的是：param1 param2 param3 arg0 arg1 arg2....
                当使用了POJO，那么test中出现的是POJO类的属性名。
            5. 在mybatis的动态SQL当中，不能使用&&，只能使用and。
        -->
        <if test="brand != null and brand != ''">
            and brand like "%"#{brand}"%"
        </if>
        <if test="guidePrice != null and guidePrice != ''">
            and guide_price > #{guidePrice}
        </if>
        <if test="carType != null and carType != ''">
            and car_type = #{carType}
        </if>
    </select>

</mapper>

CarMapperTest.java

package com.powernode.mybatis.test;

import com.powernode.mybatis.mapper.CarMapper;
import com.powernode.mybatis.pojo.Car;
import com.powernode.mybatis.utils.SqlSessionUtil;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.ArrayList;
import java.util.List;

public class CarMapperTest {

    @Test
    public void testDeleteByIds2(){
        SqlSession sqlSession = SqlSessionUtil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        Long[] ids = {161L,162L,163L};
        int count = mapper.deleteByIds2(ids);
        System.out.println(count);
        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void testInsertBatch(){
        SqlSession sqlSession = SqlSessionUtil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        Car car1 = new Car(null,"1200", "帕萨特1", 30.0, "2020-11-11", "燃油车");
        Car car2 = new Car(null,"1201", "帕萨特2", 30.0, "2020-11-11", "燃油车");
        Car car3 = new Car(null,"1202", "帕萨特3", 30.0, "2020-11-11", "燃油车");
        List<Car> cars = new ArrayList<>();
        cars.add(car1);
        cars.add(car2);
        cars.add(car3);
        mapper.insertBatch(cars);
        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void testDeleteByIds(){
        SqlSession sqlSession = SqlSessionUtil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        Long[] ids = {158L,159L,160L};
        int count = mapper.deleteByIds(ids);
        System.out.println(count);
        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void testSelectByChoose(){
        SqlSession sqlSession = SqlSessionUtil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        // 三个条件都不为空
        //List<Car> cars = mapper.selectByChoose("丰田霸道",1.0,"新能源");
        // 第一个条件是空
        //List<Car> cars = mapper.selectByChoose(null,1.0,"新能源");
        // 前两个条件都是空
        //List<Car> cars = mapper.selectByChoose(null,null,"新能源");
        // 全部都是空
        List<Car> cars = mapper.selectByChoose(null,null,null);
        cars.forEach(car -> System.out.println(car));
        sqlSession.close();
    }

    @Test
    public void testUpdateBySet(){
        SqlSession sqlSession = SqlSessionUtil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        Car car = new Car(158L, null,"丰田霸道",null,null,null);
        mapper.updateBySet(car);
        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void testUpdateById(){
        SqlSession sqlSession = SqlSessionUtil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        Car car = new Car(158L, null,"丰田霸道",null,null,"燃油车");
        mapper.updateById(car);
        sqlSession.commit();
        sqlSession.close();
    }
    @Test
    public void testSelectByMultiConditionWithTrim(){
        SqlSession sqlSession = SqlSessionUtil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        List<Car> cars = mapper.selectByMultiConditionWithTrim("比亚迪", null, "");
        cars.forEach(car -> System.out.println(car));
        sqlSession.close();
    }

    @Test
    public void testSelectByMultiConditionWithWhere(){
        SqlSession sqlSession = SqlSessionUtil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        // 三个条件都不是空
        //List<Car> cars = mapper.selectByMultiConditionWithWhere("比亚迪", 2.0, "新能源");
        // 三个条件都是空
        //List<Car> cars = mapper.selectByMultiConditionWithWhere("", null, "");
        // 如果第一个条件是空
        //List<Car> cars = mapper.selectByMultiConditionWithWhere("", 2.0, "新能源");
        // 后面两个条件是空
        List<Car> cars = mapper.selectByMultiConditionWithWhere("比亚迪", null, "");
        cars.forEach(car -> System.out.println(car));
        sqlSession.close();
    }

    @Test
    public void testSelectByMultiCondition(){
        SqlSession sqlSession = SqlSessionUtil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);

        // 假设三个条件都不是空
        //List<Car> cars = mapper.selectByMultiCondition("比亚迪", 2.0, "新能源");

        // 假设三个条件都是空
        //List<Car> cars = mapper.selectByMultiCondition("", null, "");

        // 假设后两个条件不为空，第一个条件为空
        //List<Car> cars = mapper.selectByMultiCondition("", 2.0, "新能源");

        // 假设第一个条件不是空，后两个条件是空
        List<Car> cars = mapper.selectByMultiCondition("比亚迪", null, "");

        cars.forEach(car -> System.out.println(car));
        sqlSession.close();
    }
}

二、MyBatis的⾼级映射及延迟加载

准备数据库

 创建pojo：Student、Clazz

student.java

package com.powernode.mybatis.pojo;

import com.powernode.mybatis.mapper.ClazzMapper;

/**
 * 学生信息
 */
public class Student { // Student是多的一方
    private Integer sid;
    private String sname;
    private Clazz clazz; // Clazz是一的一方。
    @Override
    public String toString() {
        return "Student{" +
                "sid=" + sid +
                ", sname='" + sname + '\'' +
                ", clazz=" + clazz +
                '}';
    }

    public Clazz getClazz() {
        return clazz;
    }

    public void setClazz(Clazz clazz) {
        this.clazz = clazz;
    }

    public Integer getSid() {
        return sid;
    }

    public void setSid(Integer sid) {
        this.sid = sid;
    }

    public String getSname() {
        return sname;
    }

    public void setSname(String sname) {
        this.sname = sname;
    }

    public Student(Integer sid, String sname) {
        this.sid = sid;
        this.sname = sname;
    }

    public Student() {
    }
}

clazz.java

package com.powernode.mybatis.pojo;

import java.util.List;

/**
 * 班级信息
 */
public class Clazz {
    private Integer cid;
    private String cname;

    private List<Student> stus;

    @Override
    public String toString() {
        return "Clazz{" +
                "cid=" + cid +
                ", cname='" + cname + '\'' +
                ", stus=" + stus +
                '}';
    }

    public List<Student> getStus() {
        return stus;
    }

    public void setStus(List<Student> stus) {
        this.stus = stus;
    }

    public Integer getCid() {
        return cid;
    }

    public void setCid(Integer cid) {
        this.cid = cid;
    }

    public String getCname() {
        return cname;
    }

    public void setCname(String cname) {
        this.cname = cname;
    }

    public Clazz() {
    }

    public Clazz(Integer cid, String cname) {
        this.cid = cid;
        this.cname = cname;
    }
}

StudentMapper.xml

<?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="com.powernode.mybatis.mapper.StudentMapper">

    <select id="selectByCidStep2" resultType="Student">
        select * from t_stu where cid = #{cid}
    </select>

    <!--
        分步查询的优点：
            第一：复用性增强。可以重复利用。（大步拆成N多个小碎步。每一个小碎步更加可以重复利用。）
            第二：采用这种分步查询，可以充分利用他们的延迟加载/懒加载机制。
        什么是延迟加载（懒加载），有什么用？
            延迟加载的核心原理是：用的时候再执行查询语句。不用的时候不查询。
            作用：提高性能。尽可能的不查，或者说尽可能的少查。来提高效率。
        在mybatis当中怎么开启延迟加载呢？
            association标签中添加fetchType="lazy"
            注意：默认情况下是没有开启延迟加载的。需要设置：fetchType="lazy"
            这种在association标签中配置fetchType="lazy"，是局部的设置，只对当前的association关联的sql语句起作用。

        在实际的开发中，大部分都是需要使用延迟加载的，所以建议开启全部的延迟加载机制：
            在mybatis核心配置文件中添加全局配置：lazyLoadingEnabled=true

        实际开发中的模式：
            把全局的延迟加载打开。
            如果某一步不需要使用延迟加载，请设置：fetchType="eager"
    -->
    <!--两条SQL语句，完成多对一的分步查询。-->
    <!--这里是第一步：根据学生的id查询学生的所有信息。这些信息当中含有班级id（cid）-->
    <resultMap id="studentResultMapByStep" type="Student">
        <id property="sid" column="sid"/>
        <result property="sname" column="sname"/>
        <association property="clazz"
                     select="com.powernode.mybatis.mapper.ClazzMapper.selectByIdStep2"
                     column="cid"
                     fetchType="eager"/>
    </resultMap>

    <select id="selectByIdStep1" resultMap="studentResultMapByStep">
        select sid,sname,cid from t_stu where sid = #{sid}
    </select>

    <!--一条SQL语句，association。-->
    <resultMap id="studentResultMapAssociation" type="Student">
        <id property="sid" column="sid"/>
        <result property="sname" column="sname"/>
        <!--
            association：翻译为关联。一个Student对象关联一个Clazz对象
                property：提供要映射的POJO类的属性名。
                javaType：用来指定要映射的java类型。
        -->
        <association property="clazz" javaType="Clazz">
            <id property="cid" column="cid"/>
            <result property="cname" column="cname"/>
        </association>
    </resultMap>

    <select id="selectByIdAssociation" resultMap="studentResultMapAssociation">
        select
            s.sid,s.sname,c.cid,c.cname
        from
            t_stu s left join t_clazz c on s.cid = c.cid
        where
            s.sid = #{sid}
    </select>

    <!--多对一映射的第一种方式：一条SQL语句，级联属性映射。-->
    <resultMap id="studentResultMap" type="Student">
        <id property="sid" column="sid"/>
        <result property="sname" column="sname"/>
        <result property="clazz.cid" column="cid"/>
        <result property="clazz.cname" column="cname"/>
    </resultMap>

    <select id="selectById" resultMap="studentResultMap">
        select
            s.sid,s.sname,c.cid,c.cname
        from
            t_stu s left join t_clazz c on s.cid = c.cid
        where
            s.sid = #{sid}
    </select>

</mapper>

ClazzMapper.xml

<?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="com.powernode.mybatis.mapper.ClazzMapper">

    <!--分步查询第一步：根据班级的cid获取班级信息。-->
    <resultMap id="clazzResultMapStep" type="Clazz">
        <id property="cid" column="cid"/>
        <result property="cname" column="cname"/>
        <collection property="stus"
                    select="com.powernode.mybatis.mapper.StudentMapper.selectByCidStep2"
                    column="cid" fetchType="eager" />
    </resultMap>

    <select id="selectByStep1" resultMap="clazzResultMapStep">
        select cid,cname from t_clazz where cid = #{cid}
    </select>

    <resultMap id="clazzResultMap" type="Clazz">
        <id property="cid" column="cid"/>
        <result property="cname" column="cname"/>
        <!--一对多，这里是collection。collection是集合的意思。-->
        <!--ofType 属性用来指定集合当中的元素类型。-->
        <collection property="stus" ofType="Student">
            <id property="sid" column="sid"/>
            <result property="sname" column="sname"/>
        </collection>
    </resultMap>

    <select id="selectByCollection" resultMap="clazzResultMap">
        select c.cid,c.cname,s.sid,s.sname from t_clazz c left join t_stu s on c.cid = s.cid where c.cid = #{cid}
    </select>


    <!--分步查询第二步：根据cid获取班级信息。-->
    <select id="selectByIdStep2" resultType="Clazz">
        select cid,cname from t_clazz where cid = #{cid}
    </select>

</mapper>

StudentMapperTest.java

package com.powernode.mybatis.test;

import com.powernode.mybatis.mapper.StudentMapper;
import com.powernode.mybatis.pojo.Student;
import com.powernode.mybatis.utils.SqlSessionUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

public class StudentMapperTest {

    @Test
    public void testSelectByIdStep1(){
        SqlSession sqlSession = SqlSessionUtil.openSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        Student student = mapper.selectByIdStep1(5);
        //System.out.println(student);

        // 只需要看学生的名字
        System.out.println(student.getSname());

        // 程序执行到这里了，我想看看班级的名字
        //System.out.println(student.getClazz().getCname());

        sqlSession.close();
    }

    @Test
    public void testSelectByIdAssociation(){
        SqlSession sqlSession = SqlSessionUtil.openSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        Student student = mapper.selectByIdAssociation(4);
        System.out.println(student);
        sqlSession.close();
    }

    @Test
    public void testSelectById(){
        SqlSession sqlSession = SqlSessionUtil.openSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        Student student = mapper.selectById(1);
        /*System.out.println(student.getSid());
        System.out.println(student.getSname());
        System.out.println(student.getClazz().getCid());
        System.out.println(student.getClazz().getCname());*/

        System.out.println(student);
        sqlSession.close();
    }
}

ClazzMapperTest.java

package com.powernode.mybatis.test;

import com.powernode.mybatis.mapper.ClazzMapper;
import com.powernode.mybatis.pojo.Clazz;
import com.powernode.mybatis.utils.SqlSessionUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

public class ClazzMapperTest {

    @Test
    public void testSelectByStep1(){
        SqlSession sqlSession = SqlSessionUtil.openSession();
        ClazzMapper mapper = sqlSession.getMapper(ClazzMapper.class);
        Clazz clazz = mapper.selectByStep1(1000);
        //System.out.println(clazz);

        // 只访问班级名字。
        System.out.println(clazz.getCname());

        // 只有用到的时候才会去执行第二步SQL
        //System.out.println(clazz.getStus());

        sqlSession.close();
    }

    @Test
    public void testSelectByCollection(){
        SqlSession sqlSession = SqlSessionUtil.openSession();
        ClazzMapper mapper = sqlSession.getMapper(ClazzMapper.class);
        Clazz clazz = mapper.selectByCollection(1000);
        System.out.println(clazz);
        sqlSession.close();
    }
}