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

      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文件名

 -->

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

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

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

   <!--

       id:resultMap的唯一标识

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

   -->

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

       <!--

           column:数据库中表的字段

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

        -->

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

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

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

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

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

   </resultMap>

   

   <!--

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

    -->

   <sql id="columns">

       PERSON_ID, NAME, GENDER, PERSON_ADDR, BIRTHDAY

   </sql>

   

   <!--

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

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

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

       parameterType:sql要接收的数据类型

       resultTypesql所返回的数据类型

    -->

    <!--

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

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

    -->

    <!--

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

     -->

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

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

    </select>

    

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

      select count(*) from person

    </select>

    

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

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

      select <includerefid="columns"/> from person

    </select>

    

    <!--

      可以使用map

      map.put("gender",1);

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

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

    -->

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

      <![CDATA[

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

      ]]>

    </select>

    

    <!--

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

    -->

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

      <![CDATA[

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

      ]]>

    </select>

    <!--

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

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

    -->

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

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

   </select>

   

   <!--库表变更 -->

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

       <!--

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

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

           resultType:主键返回的数据类型

           sql

               mysqlselect LAST_INSERT_ID()

               oracle select xxx.nextval from dual

                   

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

        -->

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

           select LAST_INSERT_ID()

       </selectKey>

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

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

   </insert>

   

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

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

       p.gender = #{gender},

       p.person_addr = #{personAddr},

       p.birthday = #{birthday}

       where p.person_id = #{personId}

   </update>

   

   <!--

       删除的sql不能使用别名

    -->

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

       delete from person where person_id = #{personId}

   </delete>

   

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

   <!--

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

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

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

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

       

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

    -->

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

       select * from person t

       <where>

           <if test="name != null">

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

           </if>

           <if test="gender != null">

               and t.gender = #{gender}

           </if>

           <if test="personAddr != null">

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

           </if>

           <if test="birthday != null">

               <![CDATA[

                   and t.birthday < #{birthday}

               ]]>

           </if>

       </where>

   </select>

   

   <!--

       动态修改

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

       

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

    -->

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

       update person t

       <set>

           <if test="name != null">

               t.name = #{name},

           </if>

           <if test="gender != null">

               t.gender = #{gender},

           </if>

           <if test="personAddr != null">

               t.person_addr = #{personAddr},

           </if>

           <if test="birthday != null">

               t.birthday = #{birthday}

           </if>

       </set>

       where t.person_id = #{personId}

   </update>

   

   <!--

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

       map.put("ids", list);

    -->

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

       select * from person t where t.person_id in

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

           #{personId}

       </foreach>

   </select>

       

   <!--

   map.put("pList", pList);

   

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

       values

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

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

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

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

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

    -->

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

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

           select LAST_INSERT_ID()

       </selectKey>

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

       values

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

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

       </foreach>

   </insert>

   

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

       delete from person where person_id in

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

           #{personId}

       </foreach>

   </delete>

   

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

   <!--一对多 -->

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

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

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

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

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

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

       <!--

       collection:一对多的关联映射

       property:一的端集合的属性名

       ofType:集合中的泛型

        -->

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

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

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

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

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

       </collection>

   </resultMap>

   

   <!--

       extends:resultMap的继承

    -->

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

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

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

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

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

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

       </collection>

   </resultMap>

   

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

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

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

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

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

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

          

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

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

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

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

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

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

          </collection>

       </collection>

   </resultMap>

   

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

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

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

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

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

       </collection>

   </resultMap>

   

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

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

   </select>

 

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

       select * from person p,

       orders o,

       order_detailod where

       p.PERSON_ID = o.PERSON_ID

       and o.ORDER_ID = od.ORDER_ID

       and p.PERSON_ID = #{personId}

   </select>

   

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

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

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

       person_rolepr,

       role r where

       p.PERSON_ID = pr.PERSON_ID

       and pr.ROLE_ID = r.ROLE_ID

       and p.PERSON_ID = #{personId}

   </select>

   

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

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

       <!--

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

           select:指定子sql的位置

        -->

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

       </collection>

   </resultMap>

   

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

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

   </select>

</mapper>


RoleMapper.xml


<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPEmapper 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>

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

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

     }

  }

}

 


评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

涂作权的博客

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值