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();

     }

  }

}

 


目录
相关文章
|
4月前
|
SQL 自然语言处理 数据库
【Azure Developer】分享两段Python代码处理表格(CSV格式)数据 : 根据每列的内容生成SQL语句
本文介绍了使用Python Pandas处理数据收集任务中格式不统一的问题。针对两种情况:服务名对应多人拥有状态(1/0表示),以及服务名与人名重复列的情况,分别采用双层for循环和字典数据结构实现数据转换,最终生成Name对应的Services列表(逗号分隔)。此方法高效解决大量数据的人工处理难题,减少错误并提升效率。文中附带代码示例及执行结果截图,便于理解和实践。
122 4
|
21天前
|
Java 数据库连接 数据库
Spring boot 使用mybatis generator 自动生成代码插件
本文介绍了在Spring Boot项目中使用MyBatis Generator插件自动生成代码的详细步骤。首先创建一个新的Spring Boot项目,接着引入MyBatis Generator插件并配置`pom.xml`文件。然后删除默认的`application.properties`文件,创建`application.yml`进行相关配置,如设置Mapper路径和实体类包名。重点在于配置`generatorConfig.xml`文件,包括数据库驱动、连接信息、生成模型、映射文件及DAO的包名和位置。最后通过IDE配置运行插件生成代码,并在主类添加`@MapperScan`注解完成整合
Spring boot 使用mybatis generator 自动生成代码插件
|
2月前
|
SQL 数据挖掘 关系型数据库
【SQL 周周练】一千条数据需要做一天,怎么用 SQL 处理电表数据(如何动态构造自然月)
题目来自于某位发帖人在某 Excel 论坛的求助,他需要将电表缴费数据按照缴费区间拆开后再按月份汇总。当时用手工处理数据,自称一千条数据就需要处理一天。我将这个问题转化为 SQL 题目。
114 12
|
2月前
|
SQL 数据采集 资源调度
【SQL 周周练】爬取短视频发现数据缺失,如何用 SQL 填充
爬虫爬取抖音和快手的短视频数据时,如果遇到数据缺失的情况,如何使用 SQL 语句完成数据的补全。
82 5
|
5月前
|
SQL 关系型数据库 MySQL
基于SQL Server / MySQL进行百万条数据过滤优化方案
对百万级别数据进行高效过滤查询,需要综合使用索引、查询优化、表分区、统计信息和视图等技术手段。通过合理的数据库设计和查询优化,可以显著提升查询性能,确保系统的高效稳定运行。
178 9
|
4月前
|
SQL 容灾 关系型数据库
阿里云DTS踩坑经验分享系列|DTS打通SQL Server数据通道能力介绍
SQL Server 以其卓越的易用性和丰富的软件生态系统,在数据库行业中占据了显著的市场份额。作为一款商业数据库,外部厂商在通过解析原生日志实现增量数据捕获上面临很大的挑战,DTS 在 SQL Sever 数据通道上深研多年,提供了多种模式以实现 SQL Server 增量数据捕获。用户可以通过 DTS 数据传输服务,一键打破自建 SQL Server、RDS SQL Server、Azure、AWS等他云 SQL Server 数据孤岛,实现 SQL Server 数据源的流动。
238 0
阿里云DTS踩坑经验分享系列|DTS打通SQL Server数据通道能力介绍
|
9月前
|
Java 数据库连接 Maven
mybatis使用一:springboot整合mybatis、mybatis generator,使用逆向工程生成java代码。
这篇文章介绍了如何在Spring Boot项目中整合MyBatis和MyBatis Generator,使用逆向工程来自动生成Java代码,包括实体类、Mapper文件和Example文件,以提高开发效率。
326 2
mybatis使用一:springboot整合mybatis、mybatis generator,使用逆向工程生成java代码。
|
8月前
|
SQL 存储 缓存
SQL Server 数据太多如何优化
11种优化方案供你参考,优化 SQL Server 数据库性能得从多个方面着手,包括硬件配置、数据库结构、查询优化、索引管理、分区分表、并行处理等。通过合理的索引、查询优化、数据分区等技术,可以在数据量增大时保持较好的性能。同时,定期进行数据库维护和清理,保证数据库高效运行。
190 4
|
9月前
|
搜索推荐 Java 数据库连接
Java|在 IDEA 里自动生成 MyBatis 模板代码
基于 MyBatis 开发的项目,新增数据库表以后,总是需要编写对应的 Entity、Mapper 和 Service 等等 Class 的代码,这些都是重复的工作,我们可以想一些办法来自动生成这些代码。
141 6
|
9月前
|
SQL 移动开发 Oracle
SQL语句实现查询连续六天数据的方法与技巧
在数据库查询中,有时需要筛选出符合特定时间连续性条件的数据记录