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-mapper.dtd">

<!--

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

 -->

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

   <!--

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

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

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

      parameterType:sql要接收的数据类型

      resultType：sql所返回的数据类型

    -->

    

    <!--

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

      
在java的model的实体类中的属性多个单词的命名规范是驼峰模式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:命名空间，用来唯一标识一个映射文件,命名规范就是当前的文件的包名+mapper的xml文件名

 -->

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

       resultType：sql所返回的数据类型

    -->

    <!--

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

      
在java的model的实体类中的属性多个单词的命名规范是驼峰模式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());

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

    
-->

    <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：生成主键的sql和insert执行的顺序，mysql是AFTER，
oracle是BEFORE

           resultType:
主键返回的数据类型

           sql：

               在mysql中select
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.sql的id，第二个参数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.sql的id，第二个参数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();

                  }

        }

        

        /**

         *
修改，将id是3的记录改成

         */

        @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.sql的id，第二个参数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.sql的id，第二个参数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.sql的id，第二个参数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.sql的id，第二个参数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.sql的id，第二个参数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();

     }

  }

}

 



时间： 2024-10-21 10:32:30

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

junit-关于Junit测试代码的编写

问题描述 关于Junit测试代码的编写 实在是搞不定了,苦逼程序女就各位大神帮助import java.util.Scanner; public class MoneyCalculate { public static void main(String[] args) { int max100 = 0;int max25 = 0;int max5 = 0;int max1 = 0; double money = getMoneyFromInput(); String str = String.v

VS2010 测试功能之旅：编码的UI测试（4）-通过编写测试代码的方式“.NET研究”建立UI测试（下）

回顾 最近比较忙,距离上次更新的时间较久,见谅. 在本章上部分,介绍了"添加用户"窗口的测试代码编写.想必大家也看到了,在UIMap.cs文件中实现自定义编码是一件很轻松的事情,接下来将介绍下个部分,查询用户窗体的测试代码的编写,以及他们测试的关联. 示例程序介绍 系统主窗口:(下载点我) 该系统拥有两个功能,"添加用户"和"查询用户": 点击添加用户后,进入添加用户子窗体:("添加用户"窗口的测试代码编写在上部分已经实现)

VS2010 测试功能之旅：编码的UI测试（4）-通“.NET研究”过编写测试代码的方式建立UI测试（上）

回顾 在之前的入门篇系列中,分别介绍了一个简单的示例, 操作动作的录制原理,通过修改UIMap.UItest文件控制操作动作代码的生成,对象的识别原理.接下来正式进入我们UI测试的进阶篇,在这一章,将讲述如何初步通过自己编写代码的方式来建立UI测试. 示例程序 一个系统的基本功能是增,删,改,查,其中增和改界面基本一样,删就几乎是一个按钮的事,所以我做了一个程序示例(下载点我),拥有增和查两个功能,之后的操作都将会在这个示例之上进行: 系统主窗口: 该系统拥有两个功能,"添加用户"和&

一起谈.NET技术，VS2010 测试功能之旅：编码的UI测试（4）-通过编写测试代码的方式建立UI测试（下）

回顾 最近比较忙,距离上次更新的时间较久,见谅. 在本章上部分,介绍了"添加用户"窗口的测试代码编写.想必大家也看到了,在UIMap.cs文件中实现自定义编码是一件很轻松的事情,接下来将介绍下个部分,查询用户窗体的测试代码的编写,以及他们测试的关联. 示例程序介绍 系统主窗口:(下载点我) 该系统拥有两个功能,"添加用户"和"查询用户": 点击添加用户后,进入添加用户子窗体:("添加用户"窗口的测试代码编写在上部分已经实现)

android使用ant自动打包(包括更改文件中的内容)

在ant打包过程中的一些学习所得以及用于这个项目的build文件的大体思路如下: 首先配置好整个系统所需的ant编译环境,在build.properties中配置好相关的参数.(build.properties文件主要包涵了一些在编译中需要用到工具的路径,以及一些需要配置的参数,如应用包名,项目名,以及一些需要传入的参数.写在build.properties中主要是为了方便配置文件的集中管理)在这次ant打包的需求中,需要修改一个java文件中的两个常量属性.根据这样的需求,首先需要拿到普通an

java生成csv文件 自己定义表头、用sql查询内容，把内容和表头放到表里 ， 最好有代码示例

问题描述 java生成csv文件 自己定义表头.用sql查询内容,把内容和表头放到表里 , 最好有代码示例 30C java生成csv文件 ,自己定义表头.用sql查询内容,把内容和表头放到表里 , 最好有代码示例 解决方案 csv文件最简单了,就是文本格式,逗号分割字段,换行分割记录.你自己sql循环,然后写文件,要表头的话,先输出一行作为表头就可以了. 解决方案二: 我要测试导出csv文件,response.getOutputStream()报空指针异常 解决方案三: 哪位大神有关于导出cs

Hibernate查询，返回new对象（注意这个新定义的类要有构造函数），使用sql带条件分页查询并且把结果显示到一个对象的集里面的解决方案

 IIndexDao package com.ucap.netcheck.dao; import com.ucap.netcheck.combination.beans.IndexCombinationBean;import com.ucap.netcheck.common.Page;import com.ucap.netcheck.common.dao.GenericDao;import com.ucap.netcheck.entity.Site; /**    * @Title: IIn

mybatis Example条件查询

Criterion是最基本,最底层的Where条件,用于字段级的筛选 Criteria Criteria包含一个Cretiron的集合,每一个Criteria对象内包含的Cretiron之间是由AND连接的,是逻辑与的关系. oredCriteria Example内有一个成员叫oredCriteria,是Criteria的集合,就想其名字所预示的一样,这个集合中的Criteria是由OR连接的,是逻辑或关系.oredCriteria就是ORed Criteria. 其他 Example类的di

MySQL数据类型与查询效率

在可以使用短数据列的时候就不要用长的.如果你有一个固定长度的CHAR数据列,那么就不要让它的长度超出实际需要.如果你在数据列中存储的最长的值有40个字符,就不要定义成CHAR(255),而应该定义成CHAR(40).如果你能够用MEDIUMINT代替BIGINT,那么你的数据表就小一些(磁盘I/O少一些),在计算过程中,值的处理速度也快一些.如果数据列被索引了,那么使用较a短的值带来的性能提高更加显著.不仅索引可以提高查询速度,而且短的索引值也比长的索引值处理起来要快一些. 如果你可以选择数据行