03_MyBatis基本查询,mapper文件的定义,测试代码的编写,resultMap配置返回值,sql片段配置,select标签标签中的内容介绍,配置使用二级缓存,使用别名的数据类型,条件查询ma

简介:  1 PersonTestMapper.xml中的内容如下: <?xmlversion="1.0"encoding="UTF-8"?> <!DOCTYPEmapper PUBLIC"-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapp


1 PersonTestMapper.xml中的内容如下:

<?xmlversion="1.0"encoding="UTF-8"?>

<!DOCTYPEmapper

PUBLIC"-//mybatis.org//DTD Mapper 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!--

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

 -->

<mappernamespace="com.rl.mapper.PersonTestMapper">

   <!--

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

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

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

      parameterType:sql要接收的数据类型

      resultTypesql所返回的数据类型

    -->

    

    <!--

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

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

     -->

    <selectid="selectPersonById"parameterType="java.lang.Integer"resultType="com.rl.model.Person">

       select * from person_test t where t.ID = #{id}

    </select>

</mapper>

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

<?xmlversion="1.0"encoding="UTF-8"?>

<!DOCTYPEmapper

PUBLIC"-//mybatis.org//DTD Mapper 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!--

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

 -->

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

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

   <cachetype="org.mybatis.caches.ehcache.EhcacheCache"/>

   <!--

       id:resultMap的唯一标识

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

   -->

   <resultMaptype="person"id="BaseResultMap">

       <!--

           column:数据库中表的字段

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

        -->

       <idcolumn="person_id"property="personId"/>

       <resultcolumn="name"property="name"/>

       <resultcolumn="gender"property="gender"/>

       <resultcolumn="person_addr"property="personAddr"/>

       <resultcolumn="birthday"property="birthday"/>

   </resultMap>

   

   <!--

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

    -->

   <sqlid="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要接收的数据类型

       resultTypesql所返回的数据类型

    -->

    <!--

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

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

     -->

     <!--

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

      -->

    <selectid="selectPersonById"parameterType="int"resultMap="BaseResultMap"useCache="true">

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

    </select>

    

    <selectid="selectPersonCount"resultType="int">

       select count(*) from person

    </select>

    

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

    <selectid="selectPersonAll"resultMap="BaseResultMap">

       select <includerefid="columns"/> from person

    </select>

    

    <!--

       可以使用map

       map.put("gender",1);

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

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

     -->

    <selectid="selectPersonByParams"parameterType="map"resultMap="BaseResultMap">

       <![CDATA[

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

       ]]>

    </select>

    

    <!--

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

     -->

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

     -->

   <selectid="selectPersonByLike"parameterType="qc"resultMap="BaseResultMap">

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

   </select>

   

   <!--库表变更 -->

   <insertid="insert"parameterType="person">

       <!--

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

           order:生成主键的sqlinsert执行的顺序,mysqlAFTER oracleBEFORE

           resultType: 主键返回的数据类型

           sql

               mysqlselect LAST_INSERT_ID()

               oracle select xxx.nextval from dual

                   

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

        -->

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

   

   <updateid="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不能使用别名

    -->

   <deleteid="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必须要写

    -->

   <selectid="selectPersonByCondition"parameterType="map"resultMap="BaseResultMap">

       select * from person t

       <where>

           <iftest="name != null">

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

           </if>

           <iftest="gender != null">

               and t.gender = #{gender}

           </if>

           <iftest="personAddr != null">

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

           </if>

           <iftest="birthday != null">

               <![CDATA[

                   and t.birthday < #{birthday}

               ]]>

           </if>

       </where>

   </select>

   

   <!--

       动态修改

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

       

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

    -->

   <updateid="dynamicUpdate"parameterType="person"flushCache="false">

       update person t

       <set>

           <iftest="name != null">

               t.name = #{name},

           </if>

           <iftest="gender != null">

               t.gender = #{gender},

           </if>

           <iftest="personAddr != null">

               t.person_addr = #{personAddr},

           </if>

           <iftest="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);

    -->

   <selectid="selectPersonByIn"parameterType="map"resultMap="BaseResultMap">

       select * from person t where t.person_id in

       <foreachcollection="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});

    -->

   <insertid="insertBatch"parameterType="map">

       <selectKeykeyProperty="personId"order="AFTER"resultType="int">

           select LAST_INSERT_ID()

       </selectKey>

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

       values

       <foreachcollection="pList"item="person"separator=",">

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

       </foreach>

   </insert>

   

   <deleteid="deleteBatch"parameterType="map">

       delete from person where person_id in

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

           #{personId}

       </foreach>

   </delete>

   

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

   <!--一对多 -->

   <resultMaptype="person"id="selectPersonAndOrderByPIdRM">

       <idcolumn="person_id"property="personId"/>

       <resultcolumn="name"property="name"/>

       <resultcolumn="gender"property="gender"/>

       <resultcolumn="person_addr"property="personAddr"/>

       <resultcolumn="birthday"property="birthday"/>

       <!--

       collection:一对多的关联映射

       property:一的端集合的属性名

       ofType:集合中的泛型

        -->

       <collectionproperty="ordersList"ofType="com.rl.model1.Orders">

           <idcolumn="ORDER_ID"property="orderId"jdbcType="INTEGER"/>

           <resultcolumn="PERSON_ID"property="personId"jdbcType="INTEGER"/>

           <resultcolumn="TOTAL_PRICE"property="totalPrice"jdbcType="REAL"/>

           <resultcolumn="ADDR"property="addr"jdbcType="VARCHAR"/>

       </collection>

   </resultMap>

   

   <!--

       extends:resultMap的继承

    -->

   <resultMaptype="person"id="selectPersonAndOrderByPIdRM1"extends="BaseResultMap">

       <collectionproperty="ordersList"ofType="com.rl.model1.Orders">

           <idcolumn="ORDER_ID"property="orderId"jdbcType="INTEGER"/>

           <resultcolumn="PERSON_ID"property="personId"jdbcType="INTEGER"/>

           <resultcolumn="TOTAL_PRICE"property="totalPrice"jdbcType="REAL"/>

           <resultcolumn="ADDR"property="addr"jdbcType="VARCHAR"/>

       </collection>

   </resultMap>

   

   <resultMaptype="person"id="selectPersonOrderAndDetailByPIdRM"extends="BaseResultMap">

       <collectionproperty="ordersList"ofType="com.rl.model1.Orders">

           <idcolumn="ORDER_ID"property="orderId"jdbcType="INTEGER"/>

           <resultcolumn="PERSON_ID"property="personId"jdbcType="INTEGER"/>

           <resultcolumn="TOTAL_PRICE"property="totalPrice"jdbcType="REAL"/>

           <resultcolumn="ADDR"property="addr"jdbcType="VARCHAR"/>

          

           <collectionproperty="detailList"ofType="com.rl.model1.OrderDetail">

           <idcolumn="DETAIL_ID"property="detailId"jdbcType="INTEGER"/>

               <resultcolumn="ORDER_ID"property="orderId"jdbcType="INTEGER"/>

               <resultcolumn="PRICE"property="price"jdbcType="REAL"/>

               <resultcolumn="QUANTITY"property="quantity"jdbcType="INTEGER"/>

               <resultcolumn="ITEM_NAME"property="itemName"jdbcType="VARCHAR"/>

           </collection>

       </collection>

   </resultMap>

   

   <resultMaptype="person"id="selectPersonAndRoleByPIdRM"extends="BaseResultMap">

       <collectionproperty="roleList"ofType="com.rl.model1.Role">

           <idcolumn="ROLE_ID"property="roleId"jdbcType="INTEGER"/>

           <resultcolumn="ROLE_NAME"property="roleName"jdbcType="VARCHAR"/>

           <resultcolumn="DESCRIPT"property="descript"jdbcType="VARCHAR"/>

       </collection>

   </resultMap>

   

   <selectid="selectPersonAndOrderByPId"parameterType="int"resultMap="selectPersonAndOrderByPIdRM1">

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

   </select>

 

   <selectid="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一端看 -->

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

   

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

   <resultMaptype="person"id="selectPersonByIdLazyRM"extends="BaseResultMap">

       <!--

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

           select:指定子sql的位置

        -->

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

       </collection>

   </resultMap>

   

   <selectid="selectPersonByIdLazy"parameterType="int"resultMap="selectPersonByIdLazyRM">

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

   </select>

</mapper>




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

  <resultMap id="BaseResultMap" type="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" />

  </resultMap>

  <sql id="Base_Column_List" >

    ROLE_ID, ROLE_NAME, DESCRIPT

  </sql>

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

    select

    <include refid="Base_Column_List" />

    from role

    where ROLE_ID = #{roleId,jdbcType=INTEGER}

  </select>

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

    delete from role

    where ROLE_ID = #{roleId,jdbcType=INTEGER}

  </delete>

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

    insert into role (ROLE_ID, ROLE_NAME, DESCRIPT

      )

    values (#{roleId,jdbcType=INTEGER}, #{roleName,jdbcType=VARCHAR}, #{descript,jdbcType=VARCHAR}

      )

  </insert>

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

    insert into role

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

      <if test="roleId != null" >

        ROLE_ID,

      </if>

      <if test="roleName != null" >

        ROLE_NAME,

      </if>

      <if test="descript != null" >

        DESCRIPT,

      </if>

    </trim>

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

      <if test="roleId != null" >

        #{roleId,jdbcType=INTEGER},

      </if>

      <if test="roleName != null" >

        #{roleName,jdbcType=VARCHAR},

      </if>

      <if test="descript != null" >

        #{descript,jdbcType=VARCHAR},

      </if>

    </trim>

  </insert>

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

    update role

    <set >

      <if test="roleName != null" >

        ROLE_NAME = #{roleName,jdbcType=VARCHAR},

      </if>

      <if test="descript != null" >

        DESCRIPT = #{descript,jdbcType=VARCHAR},

      </if>

    </set>

    where ROLE_ID = #{roleId,jdbcType=INTEGER}

  </update>

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

    update role

    set ROLE_NAME = #{roleName,jdbcType=VARCHAR},

      DESCRIPT = #{descript,jdbcType=VARCHAR}

    where ROLE_ID = #{roleId,jdbcType=INTEGER}

  </update>

 

  <resultMap type="com.rl.model1.Role" id="selectRoleAndPersonByRIdRM" extends="BaseResultMap">

    <collection property="personList" ofType="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"/>

    </collection>

  </resultMap>

 

  <select id="selectRoleAndPersonByRId" parameterType="int" resultMap="selectRoleAndPersonByRIdRM">

      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 r.ROLE_ID = #{roleId}

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



3配置sqlMapConfig.xml中的<mappers>配置:

<!--集中管理表的映射文件 -->

<mappers>

  <mapperresource="com/rl/mapper/PersonTestMapper.xml"/>

  <mapperresource="com/rl/mapper/PersonMapper.xml"/>

  <mapperresource="com/rl/mapper/RoleMapper.xml"/>

  <mapperresource="com/rl/mapper/OrdersMapper.xml"/>

  <mapperresource="com/rl/mapper/OrderDetailMapper.xml"/>

</mappers>

4测试代码如下:

MybatisTest.java

package com.rl.test;

 

import java.io.InputStream;

import java.util.Date;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

 

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

import com.rl.model1.QueryCondition;

 

/**

 * mybatis的简单查询

 */

public class MybatisTest {

        SqlSessionFactory sessionFactory;

        

        @Before

        public void setUp() throws Exception {

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

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

        }

 

        /**

         * 通过id查询数据

         */

        @Test

        public void selectPersonById() {

                  //创建session对象

                  SqlSession session = sessionFactory.openSession();

                  try {

                           //第一个参数:指定要执行的sql语法是namespace.sqlid,第二个参数sql要接收的参数

                           com.rl.model.Person person  = session.selectOne(

                                    "com.rl.mapper.PersonTestMapper.selectPersonById", 1);

                           System.out.println(person);

                  } finally{

                           session.close();

                  }

        }

        

        /**

         * 使用resultMap来查询

         */

        @Test

        public void selectPersonById1() {

                  //创建session对象

                  SqlSession session = sessionFactory.openSession();

                  try {

                           //第一个参数:指定要执行的sql语法是namespace.sqlid,第二个参数sql要接收的参数

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

                           System.out.println(person);

                  } finally{

                           session.close();

                  }

        }

        

        /**

         * 查询表的记录数

         */

        @Test

        public void selectPersonCount() {

                  //创建session对象

                  SqlSession session = sessionFactory.openSession();

                  try {

                           Integer count = session.selectOne("com.rl.mapper.PersonMapper.selectPersonCount");

                           System.out.println(count);

                  } finally{

                           session.close();

                  }                

        }

        

        /**

         * 查询所有Person

         */

        @Test

        public void selectPersonAll() {

                  //创建session对象

                  SqlSession session = sessionFactory.openSession();

                  try {

                           //查询集合的时候需要使用selectList

                           List<Person> pList = session.selectList("com.rl.mapper.PersonMapper.selectPersonAll");

                           for(Person p : pList){

                                    System.out.println(p);

                           }

                  } finally{

                           session.close();

                  }

        }

        

        /**

         * 使用多个参数用Map方式来查询

         */

        @Test

        public void selectPersonByParams() {

                  //创建session对象

                  SqlSession session = sessionFactory.openSession();

                  try {

                           Map<String, Object> map = new HashMap<String, Object>();

                           map.put("gender",0);

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

                           //查询集合的时候需要使用selectList

                           List<Person> pList = session.selectList("com.rl.mapper.PersonMapper.selectPersonByParams",map);

                           for(Person p : pList){

                                    System.out.println(p);

                           }

                  } finally{

                           session.close();

                  }

        }

        

        /**

         * 使用多个参数查询数据,使用查询对象的方式

         */

        @Test

        public void selectPersonByParams1() {

                  //创建session对象

                  SqlSession session = sessionFactory.openSession();

                  try {

                           QueryCondition qc = new QueryCondition();

                           qc.setGender("0");

                           qc.setBirthday(new Date());

                           //查询集合的时候需要使用selectList

                           List<Person> pList = session.selectList("com.rl.mapper.PersonMapper.selectPersonByParams1",qc);

                           for(Person p : pList){

                                    System.out.println(p);

                           }

                  } finally{

                           session.close();

                  }

        }

        

        /**

         * 模糊查询

         */

        @Test

        public void selectPersonByLike() {

                  //创建session对象

                  SqlSession session = sessionFactory.openSession();

                  try {

                           QueryCondition qc = new QueryCondition();

                           qc.setName("");

                           //查询集合的时候需要使用selectList

                           List<Person> pList = session.selectList("com.rl.mapper.PersonMapper.selectPersonByLike",qc);

                           for(Person p : pList){

                                    System.out.println(p);

                           }

                  } finally{

                           session.close();

                  }

        }

}

MybatisTest1.java的内容如下:

package com.rl.test;

 

import java.io.InputStream;

import java.util.Date;

 

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

 

/**

 * mybatis的数据库表的变更

 */

public class MybatisTest1 {

        SqlSessionFactory sessionFactory;

        

        @Before

        public void setUp() throws Exception {

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

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

        }

 

        /**

         * 数据插入主键返回

         * public void saveOrder(Orders order, List<OrderDetail> detailList){

                           orderDao.save(order);

                           for(OrderDetail detail : detailList){

                                    detail.setOrderId(order.getOrderId());

                                    detailDao.save(detail)

                           }

                  }

         */

        @Test

        public void insert(){

                  SqlSession session = sessionFactory.openSession();

                  Person p = new Person();

                  //p.setPersonId(3);

                  p.setName("武松");

                  p.setGender("0");

                  p.setPersonAddr("阳谷县");

                  p.setBirthday(new Date());

                  try {

                           session.insert("com.rl.mapper.PersonMapper.insert", p);

                           //库表的变更都需要提交

                           session.commit();

                  } catch (Exception e) {

                           e.printStackTrace();

                           session.rollback();

                  }finally{

                           session.close();

                  }

        }

        

        /**

         * 修改,将id3的记录改成

         */

        @Test

        public void update(){

                  SqlSession session = sessionFactory.openSession();

                  Person p = new Person();

                  p.setPersonId(3);

                  p.setName("陆虞候");

                  p.setGender("0");

                  p.setPersonAddr("阳谷县");

                  p.setBirthday(new Date());

                  try {

                           session.update("com.rl.mapper.PersonMapper.update", p);

                           //库表的变更都需要提交

                           session.commit();

                  } catch (Exception e) {

                           e.printStackTrace();

                           session.rollback();

                  }finally{

                           session.close();

                  }

        }

        

        /**

         * 删除

         */

        @Test

        public void delete(){

                  SqlSession session = sessionFactory.openSession();

                  try {

                           session.delete("com.rl.mapper.PersonMapper.delete", 4);

                           //库表的变更都需要提交

                           session.commit();

                  } catch (Exception e) {

                           e.printStackTrace();

                           session.rollback();

                  }finally{

                           session.close();

                  }

        }

}

MybatisTest2.java

package com.rl.test;

 

import java.io.InputStream;

import java.util.ArrayList;

import java.util.Date;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

 

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

 

 

/**

 * mybatis的动态sql

 */

public class MybatisTest2 {

 

        SqlSessionFactory sessionFactory;

        

        @Before

        public void setUp() throws Exception {

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

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

        }

 

        @Test

        public void selectPersonByCondition() {

                  //创建session对象

                  SqlSession session = sessionFactory.openSession();

                  try {

                           Map<String, Object> map = new HashMap<String, Object>();

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

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

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

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

                           //第一个参数:指定要执行的sql语法是namespace.sqlid,第二个参数sql要接收的参数

                           List<Person> pList  = session.selectList("com.rl.mapper.PersonMapper.selectPersonByCondition", map);

                           for(Person p : pList){

                                    System.out.println(p);

                           }

                  } finally{

                           session.close();

                  }

                  

        }

        

        @Test

        public void dynamicUpdate(){

                  SqlSession session = sessionFactory.openSession();

                  Person p = new Person();

                  p.setPersonId(3);

                  p.setName("陆虞候");

                  p.setGender("0");

                  //p.setPersonAddr("阳谷县");

                  //p.setBirthday(new Date());

                  try {

                           session.update("com.rl.mapper.PersonMapper.dynamicUpdate", p);

                           //库表的变更都需要提交

                           session.commit();

                  } catch (Exception e) {

                           e.printStackTrace();

                           session.rollback();

                  }finally{

                           session.close();

                  }

        }

        

        /**

         * foreach的用法

         */

        @Test

        public void selectPersonByIn() {

                  //创建session对象

                  SqlSession session = sessionFactory.openSession();

                  try {

                           Map<String, Object> map = new HashMap<String, Object>();

                           /*List list = new ArrayList();

                           list.add(1);

                           list.add(2);

                           list.add(3);*/

                           String [] list = {"1","2","3"};

                           map.put("ids", list);

                           List<Person> pList  = session.selectList("com.rl.mapper.PersonMapper.selectPersonByIn", map);

                           for(Person p : pList){

                                    System.out.println(p);

                           }

                  } finally{

                           session.close();

                  }

                  

        }

        

        /**

         * 批量插入

         */

        @Test

        public void insertBatch(){

                  SqlSession session = sessionFactory.openSession();

                  Map<String,Object> map = new HashMap<String,Object>();

                  List<Person> pList= new ArrayList<Person>();

                  try {

                           for(int i = 0; i < 1000009; i++){

                                    Person p = new Person();

                                    p.setName("武松"+i);

                                    p.setGender("0");

                                    p.setPersonAddr("阳谷县");

                                    p.setBirthday(new Date());

                                    pList.add(p);

                                    if(i%100 == 0){

                                              map.put("pList", pList);

                                              session.insert("com.rl.mapper.PersonMapper.insertBatch", map);

                                              pList.clear();

                                    }

                           }

                           map.put("pList", pList);

                           session.insert("com.rl.mapper.PersonMapper.insertBatch", map);

                           //库表的变更都需要提交

                           session.commit();

                  } catch (Exception e) {

                           e.printStackTrace();

                           session.rollback();

                  }finally{

                           session.close();

                  }

        }

        

        @Test

        public void deleteBatch(){

                  SqlSession session = sessionFactory.openSession();

                  Map<String,Object> map = new HashMap<String,Object>();

                  List<Integer> ids= new ArrayList<Integer>();

                  try {

                           for(int i = 106; i < 1000115; i++){

                                    ids.add(i);

                                    if(i%100 == 0){

                                              map.put("ids", ids);

                                              session.delete("com.rl.mapper.PersonMapper.deleteBatch", map);

                                              ids.clear();

                                    }

                           }

                           map.put("ids", ids);

                           session.insert("com.rl.mapper.PersonMapper.deleteBatch", map);

                           //库表的变更都需要提交

                           session.commit();

                  } catch (Exception e) {

                           e.printStackTrace();

                           session.rollback();

                  }finally{

                           session.close();

                  }

        }

}

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

import com.rl.model1.Role;

 

/**

 *mybatis的关联查询

 */

publicclass MybatisTest3 {

 

  SqlSessionFactory sessionFactory;

  

  @Before

  publicvoid setUp()throws Exception {

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

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

  }

 

  /**

   * 一对多关联查询

   */

  @Test

  publicvoid selectPersonAndOrderByPId() {

     //创建session对象

     SqlSession session = sessionFactory.openSession();

     try {

        //第一个参数:指定要执行的sql语法是namespace.sqlid,第二个参数sql要接收的参数

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

        System.out.println(person);

     } finally{

        session.close();

     }

  }

  

  /**

   * 查询Person下的所有订单和订单下的明细

   */

  @Test

  publicvoid selectPersonOrderAndDetailByPId() {

     //创建session对象

     SqlSession session = sessionFactory.openSession();

     try {

        //第一个参数:指定要执行的sql语法是namespace.sqlid,第二个参数sql要接收的参数

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

        System.out.println(person);

     } finally{

        session.close();

     }

  }

  

  /**

   * 多对多查询从Person端出发

   */

  @Test

  publicvoid selectPersonAndRoleByPId() {

     //创建session对象

     SqlSession session = sessionFactory.openSession();

     try {

        //第一个参数:指定要执行的sql语法是namespace.sqlid,第二个参数sql要接收的参数

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

        System.out.println(person);

     } finally{

        session.close();

     }

  }

  

  /**

   * 多对多查询从角色端来看

   */

  @Test

  publicvoid selectRoleAndPersonByRId() {

     //创建session对象

     SqlSession session = sessionFactory.openSession();

     try {

        //第一个参数:指定要执行的sql语法是namespace.sqlid,第二个参数sql要接收的参数

        Role role  = session.selectOne("com.rl.mapper.RoleMapper.selectRoleAndPersonByRId", 1);

        System.out.println(role);

     } finally{

        session.close();

     }

  }

  

  /**

   * 多对一的关联查询

*这里的mapper配置文件在后续的博文中定义。

   */

  @Test

  publicvoid selectPersonByOrderId() {

     //创建session对象

     SqlSession session = sessionFactory.openSession();

     try {

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

        System.out.println(order);

     } finally{

        session.close();

     }

  }

  

  /**

   * 多对一和一对多混合查询

    *这里的mapper配置文件在后续的博文中定义。

   */

  @Test

  publicvoid selectPersonAndDetailByOrderId() {

     //创建session对象

     SqlSession session = sessionFactory.openSession();

     try {

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

        System.out.println(order);

     } finally{

        session.close();

     }

  }

}

 


目录
相关文章
|
1月前
|
缓存 Java 数据库连接
Mybatis缓存相关面试题有多卷
使用 MyBatis 缓存机制需要注意以下几点: 对于频繁更新和变动的数据,不适合使用缓存。 对于数据的一致性要求比较高的场景,不适合使用缓存。 如果配置了二级缓存,需要确保缓存的数据不会影响到其他业务模块的数据。 在使用缓存时,需要注意缓存的命中率和缓存的过期策略,避免缓存过期导致查询性能下降。
52 0
|
1月前
|
SQL XML Java
Mybatis中选择语句的使用:<choose>标签、分区排序 Row_num() over ()函数的使用呢
Mybatis中选择语句的使用:<choose>标签、分区排序 Row_num() over ()函数的使用呢
21 0
|
11天前
|
SQL 缓存 Java
MYBATIS缓存
MYBATIS缓存
|
1月前
|
SQL 存储 安全
第七章 SQL错误信息 - SQL错误代码 -400 到 -500
第七章 SQL错误信息 - SQL错误代码 -400 到 -500
33 1
|
1月前
|
SQL 数据库连接 索引
第四章 SQL错误信息 - SQL错误代码 -1 到 -99
第四章 SQL错误信息 - SQL错误代码 -1 到 -99
30 0
|
3天前
|
存储 Java 数据库连接
mybatis精讲(三)--标签及TypeHandler使用
mybatis精讲(三)--标签及TypeHandler使用
13 2
|
6天前
|
缓存 NoSQL Java
在 SSM 架构(Spring + SpringMVC + MyBatis)中,可以通过 Spring 的注解式缓存来实现 Redis 缓存功能
【6月更文挑战第18天】在SSM(Spring+SpringMVC+MyBatis)中集成Redis缓存,涉及以下步骤:添加Spring Boot的`spring-boot-starter-data-redis`依赖;配置Redis连接池(如JedisPoolConfig)和连接工厂;在Service层使用`@Cacheable`注解标记缓存方法,指定缓存名和键生成策略;最后,在主配置类启用缓存注解。通过这些步骤,可以利用Spring的注解实现Redis缓存。
25 2
|
14天前
|
SQL 机器学习/深度学习 分布式计算
MaxCompute产品使用合集之sql代码中支持插入jinja语法语句吗
MaxCompute作为一款全面的大数据处理平台,广泛应用于各类大数据分析、数据挖掘、BI及机器学习场景。掌握其核心功能、熟练操作流程、遵循最佳实践,可以帮助用户高效、安全地管理和利用海量数据。以下是一个关于MaxCompute产品使用的合集,涵盖了其核心功能、应用场景、操作流程以及最佳实践等内容。
|
7天前
|
SQL 关系型数据库 数据库
实时计算 Flink版操作报错合集之在本地执行代码没有问题,但是在线执行sql命令就会报错,该怎么办
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
|
1月前
|
SQL 存储 数据库
第六章 SQL错误信息 - SQL错误代码 -200 到 -399
第六章 SQL错误信息 - SQL错误代码 -200 到 -399
14 1