MyBatis延迟加载



1  PersonMapper.xml的配置内容如下（resultMap配置返回值，sql片段配置，select标签标签中的内容介绍，配置使用二级缓存，使用别名的数据类型，条件查询map传递参数，模糊查询，插入，更新，删除，where条件查询，动态修改，in查询foreach迭代，批量插入foreach,批量删除，一对多查询，extends:resultMap的继承，多对多查询，延迟加载）：

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

<!--

namespace:命名空间，用来唯一标识一个映射文件,命名规范就是当前的文件的包名+mapper的xml文件名

 -->

<mapper namespace="com.rl.mapper.PersonMapper">

    <!--当前映射文件开启二级缓存-->

    <cache type="org.mybatis.caches.ehcache.EhcacheCache"/>

    <!--

        id:resultMap的唯一标识

        type:给哪个实体类做结果的映射

    -->

    <resultMap type="person" id="BaseResultMap">

        <!--

            column:数据库中表的字段

            property：数据库中表所有映射的实体类javaBean中的属性名

         -->

        <id column="person_id" property="personId"/>

        <result column="name" property="name"/>

        <result column="gender" property="gender"/>

        <result column="person_addr" property="personAddr"/>

        <result column="birthday" property="birthday"/>

    </resultMap>

   

    <!--

        公用的sql片段,也可以接收参数，动态sql，所有的sql可以使用

     -->

    <sql id="columns">

        PERSON_ID, NAME, GENDER, PERSON_ADDR, BIRTHDAY

    </sql>

   

    <!--

        根据id来查询一个Person的数据

        sql语句接收参数的一个语法#{}，如果接收的是一个{}中的内容任意select * from person_test t where t.ID = ?,使用预编译方式生成sql

        id:sql语句的唯一的标识不能重复

        parameterType:sql要接收的数据类型

        resultType：sql所返回的数据类型

     -->

     <!--

        实际项目中数据库的表的字段一般由多个单词来构成 由下划线来分隔多个单词 person_addr

        在java的model的实体类中的属性多个单词的命名规范是驼峰模式personAddr

      -->

      <!--

        useCache:控制当前的这个sql是否使用二级缓存

       -->

     <select id="selectPersonById" parameterType="int" resultMap="BaseResultMap" useCache="true">

        select * from person t where t.person_id = #{id}

     </select>

     

     <select id="selectPersonCount" resultType="int">

        select count(*) from person

     </select>

     

     <!-- 这里引用了上面的sql片段 -->

     <select id="selectPersonAll" resultMap="BaseResultMap">

        select <include refid="columns"/> from person

     </select>

     

     <!--

        可以使用map

        map.put("gender",1);

        map.put("birthday" new Date());

        #{}中的内容使用Map的key来接收参数

      -->

     <select id="selectPersonByParams" parameterType="map" resultMap="BaseResultMap">

        <![CDATA[

            select * from person t where t.gender = #{gender} and t.birthday < #{birthday}

        ]]>

     </select>

     

     <!--

        使用查询对象的get方法来接收参数（也就是属性名）

      -->

     <select id="selectPersonByParams1" parameterType="qc" resultMap="BaseResultMap">

        <![CDATA[

            select * from person t where t.gender = #{gender} and t.birthday < #{birthday}

        ]]>

     </select>

     <!--

        模糊查询使用${} select * from person t where t.name like '%安%'

        parameterType：不能直接使用String,一定要用查询对象或者map

      -->

    <select id="selectPersonByLike" parameterType="qc" resultMap="BaseResultMap">

        select * from person t where t.name like '%${name}%'

    </select>

   

    <!-- 库表变更 -->

    <insert id="insert" parameterType="person">

        <!--

            keyProperty:实体类中主键属性，主键生成后把主键返回给这个属性

            order：生成主键的sql和insert执行的顺序， mysql是AFTER， oracle是BEFORE

            resultType: 主键返回的数据类型

            sql：

                在mysql中select LAST_INSERT_ID()

                在oracle中 select xxx.nextval from dual

                   

            selectKey做了两件事：1.主键自增 2.主键返回

         -->

        <selectKey keyProperty="personId" order="AFTER" resultType="int">

            select LAST_INSERT_ID()

        </selectKey>

        insert into person (person_id, name, gender, person_addr, birthday)

        values(#{personId}, #{name}, #{gender}, #{personAddr}, #{birthday})

    </insert>

   

    <update id="update" parameterType="person">

        update person p set p.name = #{name},

        p.gender = #{gender},

        p.person_addr = #{personAddr},

        p.birthday = #{birthday}

        where p.person_id = #{personId}

    </update>

   

    <!--

        删除的sql不能使用别名

     -->

    <delete id="delete" parameterType="int">

        delete from person where person_id = #{personId}

    </delete>

   

    <!-- =============================动态sql================================== -->

    <!--

        map.put("name", "安");

        map.put("gender", "0");

        map.put("personAddr", "东京")

        map.put("birthday", new Date());

       

        <where>会自动处理and， 第一个and可以不写，其他的and必须要写

     -->

    <select id="selectPersonByCondition" parameterType="map" resultMap="BaseResultMap">

        select * from person t

        <where>

            <if test="name != null">

                 t.name like '%${name}%'

            </if>

            <if test="gender != null">

                and t.gender = #{gender}

            </if>

            <if test="personAddr != null">

                and t.person_addr like '%${personAddr}%'

            </if>

            <if test="birthday != null">

                <![CDATA[

                    and t.birthday < #{birthday}

                ]]>

            </if>

        </where>

    </select>

   

    <!--

        动态修改

        <set>标签可以去掉最后一个逗号

       

        flushCache：二级缓存的刷新的配置：默认是true：会刷新，如果false就不刷新缓存

     -->

    <update id="dynamicUpdate" parameterType="person" flushCache="false">

        update person t

        <set>

            <if test="name != null">

                t.name = #{name},

            </if>

            <if test="gender != null">

                t.gender = #{gender},

            </if>

            <if test="personAddr != null">

                t.person_addr = #{personAddr},

            </if>

            <if test="birthday != null">

                t.birthday = #{birthday}

            </if>

        </set>

        where t.person_id = #{personId}

    </update>

   

    <!--

        select * from person t where t.person_id in (1,2,3)

        map.put("ids", list);

     -->

    <select id="selectPersonByIn" parameterType="map" resultMap="BaseResultMap">

        select * from person t where t.person_id in

        <foreach collection="ids" item="personId" open="(" close=")" separator="," index="index">

            #{personId}

        </foreach>

    </select>

       

    <!--

    map.put("pList", pList);

   

    insert into person (person_id, name, gender, person_addr, birthday)

        values

        (#{personId}, #{name}, #{gender}, #{personAddr}, #{birthday}),

        (#{personId}, #{name}, #{gender}, #{personAddr}, #{birthday}),

        (#{personId}, #{name}, #{gender}, #{personAddr}, #{birthday}),

        (#{personId}, #{name}, #{gender}, #{personAddr}, #{birthday}),

        (#{personId}, #{name}, #{gender}, #{personAddr}, #{birthday});

     -->

    <insert id="insertBatch" parameterType="map">

        <selectKey keyProperty="personId" order="AFTER" resultType="int">

            select LAST_INSERT_ID()

        </selectKey>

        insert into person (person_id, name, gender, person_addr, birthday)

        values

        <foreach collection="pList" item="person" separator=",">

            (#{person.personId}, #{person.name}, #{person.gender}, #{person.personAddr}, #{person.birthday})

        </foreach>

    </insert>

   

    <delete id="deleteBatch" parameterType="map">

        delete from person where person_id in

        <foreach collection="ids" item="personId" open="(" close=")" separator="," index="index">

            #{personId}

        </foreach>

    </delete>

   

    <!-- ===============================关联查询================== -->

    <!-- 一对多 -->

    <resultMap type="person" id="selectPersonAndOrderByPIdRM">

        <id column="person_id" property="personId"/>

        <result column="name" property="name"/>

        <result column="gender" property="gender"/>

        <result column="person_addr" property="personAddr"/>

        <result column="birthday" property="birthday"/>

        <!--

        collection:一对多的关联映射

        property：一的端集合的属性名

        ofType：集合中的泛型

         -->

        <collection property="ordersList" ofType="com.rl.model1.Orders">

            <id column="ORDER_ID" property="orderId" jdbcType="INTEGER" />

            <result column="PERSON_ID" property="personId" jdbcType="INTEGER" />

            <result column="TOTAL_PRICE" property="totalPrice" jdbcType="REAL" />

            <result column="ADDR" property="addr" jdbcType="VARCHAR" />

        </collection>

    </resultMap>

   

    <!--

        extends:resultMap的继承

     -->

    <resultMap type="person" id="selectPersonAndOrderByPIdRM1" extends="BaseResultMap">

        <collection property="ordersList" ofType="com.rl.model1.Orders">

            <id column="ORDER_ID" property="orderId" jdbcType="INTEGER" />

            <result column="PERSON_ID" property="personId" jdbcType="INTEGER" />

            <result column="TOTAL_PRICE" property="totalPrice" jdbcType="REAL" />

            <result column="ADDR" property="addr" jdbcType="VARCHAR" />

        </collection>

    </resultMap>

   

    <resultMap type="person" id="selectPersonOrderAndDetailByPIdRM" extends="BaseResultMap">

        <collection property="ordersList" ofType="com.rl.model1.Orders">

            <id column="ORDER_ID" property="orderId" jdbcType="INTEGER" />

            <result column="PERSON_ID" property="personId" jdbcType="INTEGER" />

            <result column="TOTAL_PRICE" property="totalPrice" jdbcType="REAL" />

            <result column="ADDR" property="addr" jdbcType="VARCHAR" />

           

            <collection property="detailList" ofType="com.rl.model1.OrderDetail">

            <id column="DETAIL_ID" property="detailId" jdbcType="INTEGER" />

                <result column="ORDER_ID" property="orderId" jdbcType="INTEGER" />

                <result column="PRICE" property="price" jdbcType="REAL" />

                <result column="QUANTITY" property="quantity" jdbcType="INTEGER" />

                <result column="ITEM_NAME" property="itemName" jdbcType="VARCHAR" />

            </collection>

        </collection>

    </resultMap>

   

    <resultMap type="person" id="selectPersonAndRoleByPIdRM" extends="BaseResultMap">

        <collection property="roleList" ofType="com.rl.model1.Role">

            <id column="ROLE_ID" property="roleId" jdbcType="INTEGER" />

            <result column="ROLE_NAME" property="roleName" jdbcType="VARCHAR" />

            <result column="DESCRIPT" property="descript" jdbcType="VARCHAR" />

        </collection>

    </resultMap>

   

    <select id="selectPersonAndOrderByPId" parameterType="int" resultMap="selectPersonAndOrderByPIdRM1">

        select * from person p, orders o where p.PERSON_ID = o.PERSON_ID and p.PERSON_ID = #{personId}

    </select>

 

    <select id="selectPersonOrderAndDetailByPId" parameterType="int" resultMap="selectPersonOrderAndDetailByPIdRM">

        select * from person p,

        orders o,

        order_detail od where

        p.PERSON_ID = o.PERSON_ID

        and o.ORDER_ID = od.ORDER_ID

        and p.PERSON_ID = #{personId}

    </select>

   

    <!-- 多对多从Person一端看 -->

    <select id="selectPersonAndRoleByPId" parameterType="int" resultMap="selectPersonAndRoleByPIdRM">

        SELECT p.*, r.* from person p,

        person_role pr,

        role r where

        p.PERSON_ID = pr.PERSON_ID

        and pr.ROLE_ID = r.ROLE_ID

        and p.PERSON_ID = #{personId}

    </select>

   

    <!-- =========================延迟加载======================== -->

    <resultMap type="person" id="selectPersonByIdLazyRM" extends="BaseResultMap">

        <!--

            column：主sql的一列作为子sql的参数

            select：指定子sql的位置

         -->

        <collection property="ordersList" column="person_id" select="com.rl.mapper.OrdersMapper.selectOrderByPersonId">

        </collection>

    </resultMap>

   

    <select id="selectPersonByIdLazy" parameterType="int" resultMap="selectPersonByIdLazyRM">

        select * from person t where t.person_id = #{personId}

    </select>

</mapper>

OrdersMapper.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.rl.mapper.OrdersMapper" >

  <resultMap id="BaseResultMap" type="com.rl.model1.Orders" >

    <id column="ORDER_ID" property="orderId" jdbcType="INTEGER" />

    <result column="PERSON_ID" property="personId" jdbcType="INTEGER" />

    <result column="TOTAL_PRICE" property="totalPrice" jdbcType="REAL" />

    <result column="ADDR" property="addr" jdbcType="VARCHAR" />

  </resultMap>

  <sql id="Base_Column_List" >

    ORDER_ID, PERSON_ID, TOTAL_PRICE, ADDR

  </sql>

  <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" >

    select

    <include refid="Base_Column_List" />

    from orders

    where ORDER_ID = #{orderId,jdbcType=INTEGER}

  </select>

  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" >

    delete from orders

    where ORDER_ID = #{orderId,jdbcType=INTEGER}

  </delete>

  <insert id="insert" parameterType="com.rl.model1.Orders" >

    insert into orders (ORDER_ID, PERSON_ID, TOTAL_PRICE,

      ADDR)

    values (#{orderId,jdbcType=INTEGER}, #{personId,jdbcType=INTEGER}, #{totalPrice,jdbcType=REAL},

      #{addr,jdbcType=VARCHAR})

  </insert>

  <insert id="insertSelective" parameterType="com.rl.model1.Orders" >

    insert into orders

    <trim prefix="(" suffix=")" suffixOverrides="," >

      <if test="orderId != null" >

        ORDER_ID,

      </if>

      <if test="personId != null" >

        PERSON_ID,

      </if>

      <if test="totalPrice != null" >

        TOTAL_PRICE,

      </if>

      <if test="addr != null" >

        ADDR,

      </if>

    </trim>

    <trim prefix="values (" suffix=")" suffixOverrides="," >

      <if test="orderId != null" >

        #{orderId,jdbcType=INTEGER},

      </if>

      <if test="personId != null" >

        #{personId,jdbcType=INTEGER},

      </if>

      <if test="totalPrice != null" >

        #{totalPrice,jdbcType=REAL},

      </if>

      <if test="addr != null" >

        #{addr,jdbcType=VARCHAR},

      </if>

    </trim>

  </insert>

  <update id="updateByPrimaryKeySelective" parameterType="com.rl.model1.Orders" >

    update orders

    <set >

      <if test="personId != null" >

        PERSON_ID = #{personId,jdbcType=INTEGER},

      </if>

      <if test="totalPrice != null" >

        TOTAL_PRICE = #{totalPrice,jdbcType=REAL},

      </if>

      <if test="addr != null" >

        ADDR = #{addr,jdbcType=VARCHAR},

      </if>

    </set>

    where ORDER_ID = #{orderId,jdbcType=INTEGER}

  </update>

  <update id="updateByPrimaryKey" parameterType="com.rl.model1.Orders" >

    update orders

    set PERSON_ID = #{personId,jdbcType=INTEGER},

      TOTAL_PRICE = #{totalPrice,jdbcType=REAL},

      ADDR = #{addr,jdbcType=VARCHAR}

    where ORDER_ID = #{orderId,jdbcType=INTEGER}

  </update>

 

 

  <resultMap type="com.rl.model1.Orders" id="selectPersonByOrderIdRM" extends="BaseResultMap">

    <!--

        association:多对一的关联映射

        property：多的一端所属的一的一端类的属性名

        javaType：一的一端的数据类型

     -->

    <association property="person" javaType="person">

        <id column="person_id" property="personId"/>

        <result column="name" property="name"/>

        <result column="gender" property="gender"/>

        <result column="person_addr" property="personAddr"/>

        <result column="birthday" property="birthday"/>

    </association>

  </resultMap>

 

  <resultMap type="com.rl.model1.Orders" id="selectPersonAndDetailByOrderIdRM" extends="selectPersonByOrderIdRM">

    <collection property="detailList" ofType="com.rl.model1.OrderDetail">

        <id column="DETAIL_ID" property="detailId" jdbcType="INTEGER" />

        <result column="ORDER_ID" property="orderId" jdbcType="INTEGER" />

        <result column="PRICE" property="price" jdbcType="REAL" />

        <result column="QUANTITY" property="quantity" jdbcType="INTEGER" />

        <result column="ITEM_NAME" property="itemName" jdbcType="VARCHAR" />

    </collection>

  </resultMap>

 

  <!--

    多对一和一对多的混合查询的resultMap association要放在Collection的前面

   -->

  <resultMap type="com.rl.model1.Orders" id="selectPersonAndDetailByOrderIdRM1" extends="BaseResultMap">

    <association property="person" javaType="person">

        <id column="person_id" property="personId"/>

        <result column="name" property="name"/>

        <result column="gender" property="gender"/>

        <result column="person_addr" property="personAddr"/>

        <result column="birthday" property="birthday"/>

    </association>

    <collection property="detailList" ofType="com.rl.model1.OrderDetail">

        <id column="DETAIL_ID" property="detailId" jdbcType="INTEGER" />

        <result column="ORDER_ID" property="orderId" jdbcType="INTEGER" />

        <result column="PRICE" property="price" jdbcType="REAL" />

        <result column="QUANTITY" property="quantity" jdbcType="INTEGER" />

        <result column="ITEM_NAME" property="itemName" jdbcType="VARCHAR" />

    </collection>

  </resultMap>

 

  <select id="selectPersonByOrderId" parameterType="int" resultMap="selectPersonByOrderIdRM">

    select * from orders o, person p where o.PERSON_ID = p.PERSON_ID and o.ORDER_ID = #{orderId}

  </select>

 

  <select id="selectPersonAndDetailByOrderId" parameterType="int" resultMap="selectPersonAndDetailByOrderIdRM">

    select * from orders o, person p, order_detail od

    where o.PERSON_ID = p.PERSON_ID

    and o.ORDER_ID = od.ORDER_ID

    and o.ORDER_ID = #{orderId}

  </select>

 

  <!--

    延迟加载的子sql，传递过来的参数是person_id

   -->

  <select id="selectOrderByPersonId" parameterType="int" resultMap="BaseResultMap">

    select * from orders t where t.person_id = #{personid}

  </select>

 

 

  <resultMap type="com.rl.model1.Orders" id="selectOrderByIdLazyRM" extends="BaseResultMap">

    <association property="person" column="person_id" select="com.rl.mapper.PersonMapper.selectPersonById"></association>

  </resultMap>

 

  <resultMap type="com.rl.model1.Orders" id="selectOrderByIdLazy1RM" extends="BaseResultMap">

    <association property="person" column="person_id" select="com.rl.mapper.PersonMapper.selectPersonById"></association>

    <collection property="detailList" column="order_id" select="com.rl.mapper.OrderDetailMapper.selectDetailByOrderId"></collection>

  </resultMap>

 

    <select id="selectOrderByIdLazy" parameterType="int" resultMap="selectOrderByIdLazyRM">

        select * from orders t where t.order_id = #{orderId}

    </select> 

   

    <!--

        查询订单的所属人和订单明细，延迟加载

     -->

    <select id="selectOrderByIdLazy1" parameterType="int" resultMap="selectOrderByIdLazy1RM">

        select * from orders t where t.order_id = #{orderId}

    </select> 

</mapper>

2 配置sqlMapConfig.xml中的<settings>配置：

<configuration>

   <!-- 开启延迟加载 -->

   <settings>

      <!-- 全局的延迟加载的开关必须要开启 -->

      <setting name="lazyLoadingEnabled" value="true"/>

      <!-- 积极加载设置成false -->

      <setting name="aggressiveLazyLoading" value="false"/>

      <!-- 开启二级缓存, 缓存中只要是需要配置的针对的都是二级缓存  -->

      <setting name="cacheEnabled" value="true"/>

   </settings>

3 测试代码如下：

MybatisTest4.java

package com.rl.test;

 

import java.io.InputStream;

 

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.Before;

import org.junit.Test;

 

import com.rl.model1.Orders;

import com.rl.model1.Person;

 

/**

 * mybatis的延迟加载

 */

public class MybatisTest4 {

 

         SqlSessionFactory sessionFactory;

        

         @Before

         public void setUp() throws Exception {

                   InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml");

                   sessionFactory = new SqlSessionFactoryBuilder().build(in);

         }

 

         /**

          * 一对多关联查询

          */

         @Test

         public void selectPersonByIdLazy() {

                   //创建session对象

                   SqlSession session = sessionFactory.openSession();

                   try {

                            Person person  = session.selectOne("com.rl.mapper.PersonMapper.selectPersonByIdLazy", 1);

                            System.out.println(person.getOrdersList());

                  } finally{

                            session.close();

                   }

         }

        

         /**

          * 多对一的延迟加载

          */

         @Test

         public void selectOrderByIdLazy() {

                   //创建session对象

                   SqlSession session = sessionFactory.openSession();

                   try {

                            Orders order  = session.selectOne("com.rl.mapper.OrdersMapper.selectOrderByIdLazy", 1);

                            System.out.println(order.getPerson());

                   } finally{

                            session.close();

                   }

         }

        

         /**

          * 一对多和多对一的混合使用，延迟加载

          */

         @Test

         public void selectOrderByIdLazy1() {

                   //创建session对象

                   SqlSession session = sessionFactory.openSession();

                   try {

                            Orders order  = session.selectOne("com.rl.mapper.OrdersMapper.selectOrderByIdLazy1", 1);

                            System.out.println(order.getPerson());

                            System.out.println(order.getDetailList());

                   } finally{

                            session.close();

                   }

         }

}

相关资源：预加载防止sql注入