Mybatis入门------（2）

最新推荐文章于 2024-06-14 15:02:13 发布

要努力点

最新推荐文章于 2024-06-14 15:02:13 发布

阅读量317

点赞数 5

分类专栏： Mybatis 文章标签： mybatis java java-ee junit idea

本文链接：https://blog.csdn.net/m0_74968164/article/details/139046622

版权

Mybatis 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

八、Mybatis中查询结果映射

1、使用resultMap结果集映射

 <!--
        1、专门定义一个结果映射，在这个结果映射当中指定数据库表的字段名和java实体类的属性名相对应
        2、type属性用来指定实体类的类名
        3、id属性：用来指定resultMap的唯一标识，这个id将来要在select标签中使用
    -->
    <resultMap id="carResultMap" type="com.powernode.mybatis.pojo.Car">
        <!--如果数据库表中有主键，建议设置一个id标签-->
        <id property="id" column="id"></id>
        <!--property后面填写pojo类的属性名-->
        <!--column后面填写数据库表的字段名-->


        <result property="carNum" column="car_num"/>
        <!--如果pojo类和数据库的属性名一样的话可以省略-->
        <!--<result property="brand" column="brand"/>-->
        <result property="guidePrice" column="guide_price"/>
        <result property="produceTime" column="produce_time"/>
        <result property="carType" column="car_type"/>
    </resultMap>

    <!--select标签的resultMap用来指定使用哪个结果映射。resultMap的值是resultMap的id-->
    <select id="selectAllByResultMap" resultMap="carResultMap">
        select * from t_car;
    </select>

2、驼峰命名结果集映射

前提：java中的pojo类属性，首字母小写，后面每个单词首字母大写，数据库中字段，全小写，单词之间用_链接_

在mybatis-config.xml文件中使用

<!--mybatis中的全局设置-->
    <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

九、Mybatis中的动态SQL

1、if标签

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">
    <select id="selectByMultiCondition" resultType="com.powernode.mybatis.pojo.Car">
        select * from t_car where 1=1
        <!--
            1.if中test属性必须有
            2.if中test属性值是false或者true
            3.true：if中的sql语句会拼接
              false：if中sql语句不会拼接
            4.test属性中可以使用的是：
               当使用了@Param注解，那么test中要出现是Param中指定的参数名
               例：@Param("brand"),test中只能使用brand
               当没有使用@Param注解，那么test出现的是：param1，param2，arg0，arg1····
               当使用了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.session.SqlSession;
import org.junit.Test;

import java.util.List;

public class CarMapperTest {
    @Test
    public void testSelectByMultiCondition(){
        SqlSession sqlSession = SqlSessionUtil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        //三个条件都不是空
        //List<Car> cars = mapper.selectByMultiCondition("劳斯莱斯",4.3,"新能源");

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

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

        //假设第一个条件不是空，后两个为空
        List<Car> cars = mapper.selectByMultiCondition("比亚迪",null,"");
        cars.forEach(car -> System.out.println(car));
        sqlSession.close();
    }
}

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 {
    /**
     * 多条件查询
     * @param brand 品牌
     * @param guidePrice 指导价
     * @param carType 车类型
     * @return
     */
    List<Car> selectByMultiCondition(@Param("brand") String brand,@Param("guidePrice") Double guidePrice, @Param("carType") String carType);
}

2、where标签

所有条件都为空时，where标签保证不会生成where子句
自动除去某些条件前面多余的and和or

Demo：

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">
    <select id="selectByMultiConditionWithWhere" resultType="com.powernode.mybatis.pojo.Car">
        select * from t_car
        <!--where标签是专门负责where子句动态生成的-->
        <where>
            <if test="brand != null and brand != ''">
                 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>
</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.session.SqlSession;
import org.junit.Test;

import java.util.List;

public class CarMapperTest {
     @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,"新能源");
        cars.forEach(car -> System.out.println(car));
        sqlSession.close();
    }
}

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 {
   /**
     * 使用where标签，让where子句更加的智能
     * @param brand 品牌
     * @param guidePrice 指导价
     * @param carType 车类型
     * @return
     */
    List<Car> selectByMultiConditionWithWhere(@Param("brand") String brand,@Param("guidePrice") Double guidePrice, @Param("carType") String carType);
}

3、trim标签

Demo：

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">
    <select id="selectByMultiConditionWithTrim" resultType="com.powernode.mybatis.pojo.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}"%" and
            </if>
            <if test="guidePrice != null and guidePrice != ''">
                 guide_price > #{guidePrice} and
            </if>
            <if test="carType != null and carType != ''">
                 car_type = #{carType}
            </if>
        </trim>

    </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.session.SqlSession;
import org.junit.Test;

import java.util.List;

public class CarMapperTest {
    @Test
    public void testSelectByMultiConditionWithTrim(){
        SqlSession sqlSession = SqlSessionUtil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        List<Car> cars = mapper.selectByMultiConditionWithTrim("",2.4,"");
        cars.forEach(car -> System.out.println(car));
        sqlSession.close();
    }
}

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 {
   /**
     * 使用trim标签
     * @param brand 品牌
     * @param guidePrice 指导价
     * @param carType 车类型
     * @return
     */
    List<Car> selectByMultiConditionWithTrim(@Param("brand") String brand,@Param("guidePrice") Double guidePrice, @Param("carType") String carType);
}

4、set标签

主要使用在update语句中，用来生成set关键字，同时去掉最后多余的“，”

比如只更新提交的不为空的字段，如果提交的数据是空或者“ ”，那么这个字段将不会更新

Demo：

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">
    <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>
</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.session.SqlSession;
import org.junit.Test;

import java.util.List;

public class CarMapperTest {
   @Test
    public void testUpdateBySet(){
        SqlSession sqlSession = SqlSessionUtil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        Car car = new Car(23L,null,"一汽大众",null,null,"燃油车");
        int count = mapper.updateBySet(car);
        System.out.println(count);
        sqlSession.commit();
        sqlSession.close();

    }
}

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 {
   /**
     * 使用set标签更新
     * @param car
     * @return
     */
    int updateBySet(Car car);
}

5、choose when otherwise标签

语法格式：

<choose>
	<when></when>
	<when></when>
	<when></when>
	<otherwise></otherwise>
</choose>

类似于
if(){

}else if(){

}else if(){

}else if(){

}else{
 
}

Demo：

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">
    <select id="selectByChoose" resultType="com.powernode.mybatis.pojo.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>
</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.session.SqlSession;
import org.junit.Test;

import java.util.List;

public class CarMapperTest {
  @Test
    public void testSelectByChoose(){
        SqlSession sqlSession = SqlSessionUtil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        List<Car> cars = mapper.selectByChoose("比亚迪宋",null,null);
        cars.forEach(car -> System.out.println(car));
        sqlSession.close();
    }
}

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 {
   /**
     * 使用choose when overwise标签查询
     * @param brand
     * @param guidePrice
     * @param carType
     * @return
     */
    List<Car> selectByChoose(@Param("brand") String brand,@Param("guidePrice") Double guidePrice,@Param("carType") String carType);
}

6、foreach标签

Demo（批量删除）：

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="deleteByIds">
        <!--
            foreach标签的属性：
                collection：指定数组或者集合
                item：代表数组或者集合中的元素
                separator：循环之间的分隔符
        -->
        delete from t_car where id in(
        <foreach collection="ids" item="id" separator=",">
            #{id}
        </foreach>
        )
    </delete>
</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.session.SqlSession;
import org.junit.Test;

import java.util.List;

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

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 {
   /**
     * 批量删除
     * @param ids
     * @return
     */
    int deleteByIds(@Param("ids") Long[] ids);
}

Demo（批量插入）：

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">
    <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>
</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.session.SqlSession;
import org.junit.Test;

import java.util.List;

public class CarMapperTest {
 @Test
    public void testInsertBatch(){
        SqlSession sqlSession = SqlSessionUtil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        Car car1 = new Car(null,"1200","萨切特",30.0,"2002-04-09","油车");
        Car car2 = new Car(null,"1300","凯迪拉克",20.0,"2003-03-23","新能源车");
        List<Car> cars = new ArrayList<>();
        cars.add(car1);
        cars.add(car2);
        int count = mapper.insertBatch(cars);
        System.out.println(count);
        sqlSession.commit();
        sqlSession.close();

    }
}

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 {
   /**
     *  批量插入，一次性插入多条记录
     * @param cars
     * @return
     */
    int insertBatch(@Param("cars") List<Car> cars);
}

7、sql标签和include标签

sql标签用来声明sql片段

include标签用来将声明的sql片段包含到某个sql语句当中

作用：代码复用，易维护

十、mybatis的高级映射及延迟加载

之前都是对一个表进行增删查改，而高级映射就是对几个表进行增删查改

1、多对一

①级联属性映射

Demo：

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">

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

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

StudentMapper.java

package com.powernode.mybatis.mapper;

import com.powernode.mybatis.pojo.Student;

public interface StudentMapper {
    /**
     * 根据id获取学生信息，同时获取学生关联的班级信息
     * @param id 学生id
     * @return 返回学生对象，但是学生对象中含有班级对象
     */
    Student selectById(Integer id);
}

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 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());
        sqlSession.close();
    }
}

②一条sql语句，采用association方式

Demo：

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">

     <resultMap id="studentResultMapAssociation" type="com.powernode.mybatis.pojo.Student">
        <id property="sid" column="sid"/>
        <result property="sname" column="sname"></result>
        <!--
            association： 意为关联，一个student对象关联一个class对象
                       property：提供要映射的pojo类的属性名，
                       javaType：用来指定要映射的java类型
        -->
        <association property="clazz" javaType="com.powernode.mybatis.pojo.Class">
            <id property="cid" column="cid"></id>
            <result property="cname" column="cname"></result>
        </association>
    </resultMap>

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

StudentMapper.java

package com.powernode.mybatis.mapper;

import com.powernode.mybatis.pojo.Student;

public interface StudentMapper {
    /**
     * 一条sql语句，association
     * @param id
     * @return
     */
    Student selectByIdAssociation(Integer id);
}

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 testSelectByIdAssociation(){
        SqlSession sqlSession = SqlSessionUtil.openSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        Student student = mapper.selectByIdAssociation(4);
        System.out.println(student);
        sqlSession.close();
    }
}

③两条sql语句，分布查询（优点：可以复用，支持懒加载）

Demo：

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">

     <!--
        两条sql语句，完成多对一的分布查询
    -->
    <!--根据学生的id查询的结果，结果中有班级的id-->
    <resultMap id="studentResultMapByStep" type="com.powernode.mybatis.pojo.Student">
        <id property="sid" column="sid"></id>
        <result property="sname" column="sname"></result>
        <association property="clazz" select="com.powernode.mybatis.mapper.ClassMapper.selectByIdStep2" column="cid">

        </association>
    </resultMap>
    <select id="selectByIdStep1" resultMap="studentResultMapByStep">
        select s.sid,s.sname,cid from t_student s
        where sid = #{sid}
    </select>
</mapper>

StudentMapper.java

package com.powernode.mybatis.mapper;

import com.powernode.mybatis.pojo.Student;

public interface StudentMapper {
    /**
     * 分步查询第一步：先根据学生的sid查询学生的信息
     * @param id
     * @return
     */
    Student selectByIdStep1(Integer id);
}

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);
        sqlSession.close();
    }
}

classMapper.java

package com.powernode.mybatis.mapper;

public interface ClassMapper {
    /**
     * 分布查询第二步，根据cid获取班级信息
     * @param cid
     * @return
     */
    Class selectByIdStep2(Integer cid);
}

ClassMapper.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.ClassMapper">
    <!--分步查询第二步：根据cid获取班级信息-->
    <select id="selectByIdStep2" resultType="com.powernode.mybatis.pojo.Class">
        select cid,cname from t_class where cid = #{cid}
    </select>
</mapper>

2、一对多

①使用collection集合方法

ClassMapper.java

package com.powernode.mybatis.mapper;

import com.powernode.mybatis.pojo.Class;

public interface ClassMapper {
    /**
     * 分布查询第二步，根据cid获取班级信息
     * @param cid
     * @return
     */
    Class selectByIdStep2(Integer cid);


    /**
     * 根据班级编号查询班级信息
     * @param cid
     * @return
     */
    Class selectByCollection(Integer cid);
}

ClassMapperTest.java

package com.powernode.mybatis.test;

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

public class ClassMapperTest {
    @Test
    public void testSelectByCollection(){
        SqlSession sqlSession = SqlSessionUtil.openSession();
        ClassMapper mapper = sqlSession.getMapper(ClassMapper.class);
        Class aClass = mapper.selectByCollection(1000);
        System.out.println(aClass);
        sqlSession.close();
    }
}

ClassMapper.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.ClassMapper">


    <resultMap id="classResultMap" type="com.powernode.mybatis.pojo.Class">
        <id property="cid" column="cid"></id>
        <result property="cname" column="cname"></result>
        <!--
            一对多这里是collection，意为集合的意思
            ofType用来指定集合中元素的类型
        -->
        <collection property="stus" ofType="com.powernode.mybatis.pojo.Student">
            <id property="sid" column="sid"></id>
            <result property="sname" column="sname"></result>
        </collection>
    </resultMap>
    <select id="selectByCollection" resultMap="classResultMap">
        select c.cid,c.cname,s.sid,s.sname from t_class c left join t_student s on c.cid = s.cid where c.cid = #{cid}
    </select>
</mapper>

②使用分步查询的方式

Demo：

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="com.powernode.mybatis.pojo.Student">
        select sid,sname,cid from t_student where cid = #{cid}
    </select>
</mapper>

StudentMapper.java

package com.powernode.mybatis.mapper;

import com.powernode.mybatis.pojo.Student;

public interface StudentMapper {
    /**
     * 分步查询第二步：根据班级编号查询学生信息
     * @param cid
     * @return
     */
    List<Student> selectByCidStep2(Integer cid);
}

ClassMapperTest.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 testSelectByStep1(){
        SqlSession sqlSession = SqlSessionUtil.openSession();
        ClassMapper mapper = sqlSession.getMapper(ClassMapper.class);
        Class aClass = mapper.selectByStep1(1000);
        System.out.println(aClass);
        sqlSession.close();
    }
}

classMapper.java

package com.powernode.mybatis.mapper;

public interface ClassMapper {
    /**
     * 分步查询 第一步：根据班级编号获取班级信息
     * @param cid 班级编号
     * @return
     */
    Class selectByStep1(Integer cid);
}

ClassMapper.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.ClassMapper">
    <!--分步查询第一步：根据班级的cid获取班级信息-->
    <resultMap id="classResultMapStep" type="com.powernode.mybatis.pojo.Class">
        <id property="cid" column="cid"></id>
        <result property="cname" column="cname"></result>
        <collection property="stus" select="com.powernode.mybatis.mapper.StudentMapper.selectByCidStep2" column="cid"></collection>
    </resultMap>
    <select id="selectByStep1" resultMap="classResultMapStep">
        select cid,cname from t_class where cid = #{cid}
    </select>
</mapper>

十一、Mybatis中的缓存

mybatis中的缓存：将select语句的结果放到缓存中，下一次还是执行这条select语句的话，直接从缓存中取，不再查询数据库

1、类型

一级缓存：将查询的数据存储到Sqlsession中
二级缓存：将查询的数据存储到SqlSessionFactory中
集成第三方的缓存：EhCache等

注意：缓存只针对select语句

2、一级缓存

一级缓存是默认开启，不需要任何的配置

Demo：

CarMapper.java

package com.powernode.mybatis.mapper;

import com.powernode.mybatis.pojo.Car;

public interface CarMapper {
    /**
     * 根据id查询汽车信息
     * @param id
     * @return
     */
    Car selectById(Long id);
}

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.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

import java.io.IOException;

public class CarMapperTest {
    @Test
    public void testSelectById() throws IOException {
//        SqlSession sqlSession = SqlSessionUtil.openSession();
//        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
//        Car car1 = mapper.selectById(11l);
//        System.out.println(car1);
//
//        Car car2 = mapper.selectById(11l);
//        System.out.println(car2);
//        sqlSession.close();

        //测试一级缓存是否是将查询的数据存在SqlSession当中
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis-config.xml"));
        //第一个sqlSession对象
        SqlSession sqlSession1 = sqlSessionFactory.openSession();
        CarMapper mapper1 = sqlSession1.getMapper(CarMapper.class);
        Car car1 = mapper1.selectById(11l);
        System.out.println(car1);


        //第二个sqlSession对象
        SqlSession sqlSession2 = sqlSessionFactory.openSession();
        CarMapper mapper2 = sqlSession2.getMapper(CarMapper.class);
        Car car2 = mapper2.selectById(11l);
        System.out.println(car2);

        sqlSession1.close();
        sqlSession2.close();

    }
}

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">
    <select id="selectById" resultType="com.powernode.mybatis.pojo.Car">
        select * from t_car where id = #{id}
    </select>
</mapper>

①什么时候不走一级缓存？

SqlSession对象不是同一个，肯定不走缓存
查询条件不一样

②什么时候一级缓存失效？

在第一次DQL语句和第二次DQL语句之间，以下两件事都会让一级缓存清空
- 1、执行了sqlSession的clearCache方法，这个是手动清空
- 2、执行了insert或者update或者delete语句，不管是不是当前的表，都会清空一级缓存

2、二级缓存

二级缓存的范围是SqlSessionFactory

使用二级缓存必须具备以下的配置：

全局性地开启或者关闭所有映射配置文件中已配置的任何缓存，默认就是true，所以不用配置
在需要使用二级缓存的SqlMapper.xml文件中添加配置：
使用二级缓存的实体类对象必须是可序列化的，也就是说必须实现java.io.Serializable接口
SqlSession对象关闭或者提交之后，一级缓存中的数据才会被写入到二级缓存中，此时二级缓存才可以使用

Demo：

CarMapper.java

package com.powernode.mybatis.mapper;

import com.powernode.mybatis.pojo.Car;

public interface CarMapper {
    /**
     * 测试二级缓存
     * @param id
     * @return
     */
    Car selectById2(Long id);
}

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.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

import java.io.IOException;

public class CarMapperTest {
    @Test
    public void testSelectById2() throws IOException {

        //这里只有一个SqlSessionFactory对象，二级缓存对应的就是SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis-config.xml"));
        SqlSession sqlSession1 = sqlSessionFactory.openSession();
        SqlSession sqlSession2 = sqlSessionFactory.openSession();

        CarMapper mapper1 = sqlSession1.getMapper(CarMapper.class);
        CarMapper mapper2 = sqlSession2.getMapper(CarMapper.class);

        //这行代码结束之后，实际上数据是缓存到一级缓存当中了
        Car car1 = mapper1.selectById2(10l);
        System.out.println(car1);

        //这里不关闭SqlSession1对象的话，二级缓存中还是没有数据的

        //这行代码执行完毕，实际上数据会缓存到一级缓存中
        Car car2 = mapper2.selectById2(10l);
        System.out.println(car2);

        //程序执行到这里的时候，会将sqlSession1这个一级缓存中的数据写入到二级缓存中
        sqlSession1.close();
        //程序执行到这里的时候，会将sqlSession2这个一级缓存中的数据写入到二级缓存中
        sqlSession2.close();
    }
}

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">
    <cache/>
    
    <select id="selectById2" resultType="com.powernode.mybatis.pojo.Car">
        select * from t_car where id = #{id}
    </select>
</mapper>

Car.java

public class Car implements Serializable {
    //数据库中的字段和属性相同

    //建议使用包装类，防止null
    private Long id;

    private String carNum;
    private String brand;
    private Double guidePrice;
    private String produceTime;
    private String carType;

    public Car(Long id, String carNum, String brand, Double guidePrice, String produceTime, String carType) {
        this.id = id;
        this.carNum = carNum;
        this.brand = brand;
        this.guidePrice = guidePrice;
        this.produceTime = produceTime;
        this.carType = carType;
    }
    public Car(){}

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getCarNum() {
        return carNum;
    }

    public void setCarNum(String carNum) {
        this.carNum = carNum;
    }

    public String getBrand() {
        return brand;
    }

    public void setBrand(String brand) {
        this.brand = brand;
    }

    public Double getGuidePrice() {
        return guidePrice;
    }

    public void setGuidePrice(Double guidePrice) {
        this.guidePrice = guidePrice;
    }

    public String getProduceTime() {
        return produceTime;
    }

    public void setProduceTime(String produceTime) {
        this.produceTime = produceTime;
    }

    public String getCarType() {
        return carType;
    }

    public void setCarType(String carType) {
        this.carType = carType;
    }

    @Override
    public String toString() {
        return "Car{" +
                "id=" + id +
                ", carNum='" + carNum + '\'' +
                ", brand='" + brand + '\'' +
                ", guidePrice=" + guidePrice +
                ", produceTime='" + produceTime + '\'' +
                ", carType='" + carType + '\'' +
                '}';
    }
}

①二级缓存失效

只要两次查询之间出现了增删查改，二级缓存就会失效（一级缓存也会失效）

十二、Mybatis的逆向工程

使用这个插件，需要给这个插件配置哪些信息？

pojo类、包名已经生成位置
SqlMapper.xml文件名以及生成的位置
Mapper接口名以及生成的位置
连接数据库的信息
指定哪些表参加逆向工程

①在pom.xml文件添加逆向工程插件

<!--配置mybatis逆向工程的插件-->
    <build>
        <plugins>
            <!--mybatis-generator的Maven插件-->
            <plugin>
                <groupId>org.mybatis.generator</groupId>
                <artifactId>mybatis-generator-maven-plugin</artifactId>
                <version>1.4.1</version>
                <!--允许覆盖-->
                <configuration>
                    <!--<verbose>true</verbose>-->
                    <overwrite>true</overwrite>
                </configuration>
                <!-- 插件需要的依赖环境 -->
                <dependencies>
                    <!--
                    这个是 在插件 org.mybatis.generator 需要配置这信息，
                    所以需要再次这个依赖数据库驱动放在org.mybatis.generator的 plugin中依赖
                    与项目中引入的区别不重复
                    -->
                    <dependency>
                        <groupId>mysql</groupId>
                        <artifactId>mysql-connector-java</artifactId>
                        <version>8.0.30</version>
                    </dependency>
                </dependencies>
            </plugin>
        </plugins>
    </build>

②配置generatorConfig.xml

注意：

文件名必须叫做generatorConfig.xml
该文件必须放在类的根路径下

十三、Mybatis分页-PageHelper插件

①引入依赖

<!--mybatis的插件pagehelper-->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper</artifactId>
            <version>5.3.1</version>
        </dependency>

②在mybatis-conflg.xml文件中配置插件

<!--mybatis分页的拦截器-->
   <plugins>
       <plugin interceptor="com.github.pagehelper.PageInterceptor"></plugin>
   </plugins>

十四、Mybatis的注解式开发

注意：简单的sql语句，比如单表的crud可以使用注解开发，但是像多表的联合使用，最好还是使用xml配置文件开发

①insert注解

CarMapper.java

package com.powernode.mybatis.mapper;

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

public interface CarMapper {

    @Insert("insert into t_car values(null,#{carNum},#{brand},#{guidePrice},#{produceTime},#{carType})")
    int insert(Car car);

    @Delete("delete from t_car where id = #{id}")
    int deleteById(Long id);
}

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.session.SqlSession;
import org.junit.Test;

public class CarMapperTest {
    @Test
    public void testInsert(){
        SqlSession sqlSession = SqlSessionUtil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        Car car = new Car(null,"4545","小米SU7",25.0,"2023-12-12","新能源");
        int count = mapper.insert(car);
        System.out.println(count);
        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void testDeleteById(){
        SqlSession sqlSession = SqlSessionUtil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        int i = mapper.deleteById(27l);
        System.out.println(i);
        sqlSession.commit();
        sqlSession.close();

    }
}

②delete注解

CarMapper.java

package com.powernode.mybatis.mapper;

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

public interface CarMapper {


    @Delete("delete from t_car where id = #{id}")
    int deleteById(Long id);
}

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.session.SqlSession;
import org.junit.Test;

public class CarMapperTest {
  

    @Test
    public void testDeleteById(){
        SqlSession sqlSession = SqlSessionUtil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        int i = mapper.deleteById(27l);
        System.out.println(i);
        sqlSession.commit();
        sqlSession.close();

    }
}

③update注解

CarMapper.java

package com.powernode.mybatis.mapper;

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

public interface CarMapper {

    @Update("update t_car set car_num=#{carNum},brand=#{brand},guide_price=#{guidePrice},produce_time=#{produceTime},car_type=#{carType} where id = #{id}")
    int update(Car car);
}

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.session.SqlSession;
import org.junit.Test;

public class CarMapperTest {
   @Test
    public void testUpdate(){
        SqlSession sqlSession = SqlSessionUtil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        Car car = new Car(20l,"6666","特斯拉",30.0,"2020-09-08","新能源");
        int count = mapper.update(car);
        System.out.println(count);
        sqlSession.commit();
        sqlSession.close();
    }
}

④select注解

CarMapper.java

package com.powernode.mybatis.mapper;

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

public interface CarMapper {

     @Select("select * from t_car where id = #{id};")
    Car selectById(Long id);
}

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.session.SqlSession;
import org.junit.Test;

public class CarMapperTest {
   @Test
    public void testSelectById(){
        SqlSession sqlSession = SqlSessionUtil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        Car car = mapper.selectById(21l);
        System.out.println(car);

        sqlSession.close();
    }
}

要努力点

关注

5
点赞
踩
5

收藏

觉得还不错? 一键收藏
打赏
0
评论
Mybatis入门------（2）

2、驼峰命名结果集映射前提：java中的pojo类属性，首字母小写，后面每个单词首字母大写，数据库中字段，全小写，单词之间用_链接_在mybatis-config.xml文件中使用九、Mybatis中的动态SQL1、if标签CarMapper.xmlCarMapperTest.javaCarMapper.java2、where标签所有条件都为空时，where标签保证不会生成where子句自动除去某些条件前面多余的and和orDemo：CarMapper.xmlCarMapperT
复制链接

扫一扫