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

开发者社区> 涂作权> 正文

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

     }

  }

}

 



版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
MyBatis Generator 代码生成器 快速入门指南
要使用MyBatis Generator(MBG)快速启动并运行,请按照下列步骤操作:1、适当地创建并填写配置文件。至少必须指定:一个元素来指定如何连接到目标数据库一个元素来指定生成的Java模型对象的目标包和目标项目用于指定生成的SQL映射文件的目标包和目标项目的元素(可选)一个元素来指定生成的客户端接口和类的目标包和目标项目(如果不希望生成Java客户端代码,则可以省略元素)至少有一个数据库元素有关配置文件的示例,请参阅XML配置文件参考页面。
804 0
MyBatis Generator (MBG) 代码生成器简介
MyBatis Generator(MBG)是MyBatis MyBatis和iBATIS的代码生成器。它将生成所有版本的MyBatis的代码,以及版本2.2.0之后的iBATIS版本。它将内省数据库表(或许多表),并将生成可用于访问表的工件。
1330 0
Mybatis 级联查询 (一对多 )
后台系统中 涉及到添加试卷 问题 答案的一个模块的。我需要通过试卷 查询出所有的试题,以及试题的答案。这个主要要使用到Mybatis的级联查询。 通过试卷 查询出与该试卷相关的试题(一对多),查询出试题的答案及分数(一对多)。
969 0
备忘:spring jdbc事务代码 mybatis, nhibernate
http://files.cnblogs.com/files/mikelij/mymavenMar1.rar
782 0
mybatis的逆向工程自动生成代码
参考文献:https://blog.csdn.net/yerenyuan_pku/article/details/71909325 逆向工程: MyBatis需要程序员自己编写sql,如果表太多的话,会很麻烦。
1512 0
【WebAPI No.5】Core WebAPI中的自定义格式化
介绍 Web API为JSON和XML提供媒体类型格式化程序。框架默认将这些格式化程序插入管道中。客户端可以在HTTP请求的Accept标头中请求JSON或XML. 格式化数据这个东西,其实没有什么最好的数据,要看各种场景,最适合才是最好的,不是说json就比xml好,容易解析什么的等。
2073 0
SSM框架——使用 MyBatis Generator 生成代码,包括:Model、Dao、Mapping
基于 SSM 框架开发,Mybatis 属于半自动 ORM,如果每个数据表相关的 Model、Dao、Mapping 都要自己动手去写,是不是很麻烦呢?工作量最大的就是书写Mapping的映射文件,而且手动书写很容易出错。
1152 0
Spring Boot项目利用MyBatis Generator进行数据层代码自动生成
概 述 MyBatis Generator (简称 MBG) 是一个用于 MyBatis和 iBATIS的代码生成器。它可以为 MyBatis的所有版本以及 2.2.0之后的 iBATIS版本自动生成 ORM层代码,典型地包括我们日常需要手写的 POJO、mapper xml 以及 mapper 接口等。
1838 0
+关注
涂作权
java,架构,编程语言相关专家
1234
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载