[轉(zhuǎn)載]MyBatis基本查詢，mapper文件的定義

原文鏈接：http://blog.csdn.net/tototuzuo ... 01099


1 PersonTestMapper.xml中的內(nèi)容如下:
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis ... gt%3B
<!--
  namespace:命名空間，用來唯一標(biāo)識(shí)一個(gè)映射文件,命名規(guī)范就是當(dāng)前的文件的包加上文件名
 -->
<mapper namespace="com.rl.mapper.PersonTestMapper">
    <!--
       根據(jù)id來查詢一個(gè)Person的數(shù)據(jù)
       sql語句接收參數(shù)的一個(gè)語法#{}，如果接收的是一個(gè){}中的內(nèi)容任意select * from person_test t where t.ID = ?,使用預(yù)編譯方式生成sql
       id:sql語句的唯一的標(biāo)識(shí)不能重復(fù)
       parameterType:sql要接收的數(shù)據(jù)類型
       resultType：sql所返回的數(shù)據(jù)類型
     -->
     
     <!--
        實(shí)際項(xiàng)目中數(shù)據(jù)庫的表的字段一般由多個(gè)單詞來構(gòu)成 由下劃線來分隔多個(gè)單詞 person_addr
        在java的model的實(shí)體類中的屬性多個(gè)單詞的命名規(guī)范是駝峰模式personAddr
      -->
     <select id="selectPersonById" parameterType="java.lang.Integer" resultType="com.rl.model.Person">
        select * from person_test t where t.ID = #{id}
     </select>
</mapper>
2  PersonMapper.xml的配置內(nèi)容如下（resultMap配置返回值，sql片段配置，select標(biāo)簽標(biāo)簽中的內(nèi)容介紹，配置使用二級(jí)緩存，使用別名的數(shù)據(jù)類型，條件查詢map傳遞參數(shù)，模糊查詢，插入，更新，刪除，where條件查詢，動(dòng)態(tài)修改，in查詢foreach迭代，批量插入foreach,批量刪除，一對(duì)多查詢，extends:resultMap的繼承，多對(duì)多查詢，延遲加載）：
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis ... gt%3B
<!--
namespace:命名空間，用來唯一標(biāo)識(shí)一個(gè)映射文件,命名規(guī)范就是當(dāng)前的文件的包名+mapper的xml文件名
 -->
<mapper namespace="com.rl.mapper.PersonMapper">
    <!--當(dāng)前映射文件開啟二級(jí)緩存-->
    <cache type="org.mybatis.caches.ehcache.EhcacheCache"/>
    <!--
        id:resultMap的唯一標(biāo)識(shí)
        type:給哪個(gè)實(shí)體類做結(jié)果的映射
    -->
    <resultMap type="person" id="BaseResultMap">
        <!--
            column:數(shù)據(jù)庫中表的字段
            property：數(shù)據(jù)庫中表所有映射的實(shí)體類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片段,也可以接收參數(shù)，動(dòng)態(tài)sql，所有的sql可以使用
     -->
    <sql id="columns">
        PERSON_ID, NAME, GENDER, PERSON_ADDR, BIRTHDAY
    </sql>
   
    <!--
        根據(jù)id來查詢一個(gè)Person的數(shù)據(jù)
        sql語句接收參數(shù)的一個(gè)語法#{}，如果接收的是一個(gè){}中的內(nèi)容任意select * from person_test t where t.ID = ?,使用預(yù)編譯方式生成sql
        id:sql語句的唯一的標(biāo)識(shí)不能重復(fù)
        parameterType:sql要接收的數(shù)據(jù)類型
        resultType：sql所返回的數(shù)據(jù)類型
     -->
     <!--
        實(shí)際項(xiàng)目中數(shù)據(jù)庫的表的字段一般由多個(gè)單詞來構(gòu)成 由下劃線來分隔多個(gè)單詞 person_addr
        在java的model的實(shí)體類中的屬性多個(gè)單詞的命名規(guī)范是駝峰模式personAddr
      -->
      <!--
        useCache:控制當(dāng)前的這個(gè)sql是否使用二級(jí)緩存
       -->
     <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 <include refid="columns"/> from person
     </select>
     
     <!--
        可以使用map
        map.put("gender",1);
        map.put("birthday" new Date());
        #{}中的內(nèi)容使用Map的key來接收參數(shù)
      -->
     <select id="selectPersonByParams" parameterType="map" resultMap="BaseResultMap">
        <![CDATA[
            select * from person t where t.gender = #{gender} and t.birthday < #{birthday}
        ]]>
     </select>
     
     <!--
        使用查詢對(duì)象的get方法來接收參數(shù)（也就是屬性名）
      -->
     <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,一定要用查詢對(duì)象或者map
      -->
    <select id="selectPersonByLike" parameterType="qc" resultMap="BaseResultMap">
        select * from person t where t.name like '%${name}%'
    </select>
   
    <!-- 庫表變更 -->
    <insert id="insert" parameterType="person">
        <!--
            keyProperty:實(shí)體類中主鍵屬性，主鍵生成后把主鍵返回給這個(gè)屬性
            order：生成主鍵的sql和insert執(zhí)行的順序， mysql是AFTER， oracle是BEFORE
            resultType: 主鍵返回的數(shù)據(jù)類型
            sql：
                在mysql中select 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>
   
    <!-- =============================動(dòng)態(tài)sql================================== -->
    <!--
        map.put("name", "安");
        map.put("gender", "0");
        map.put("personAddr", "東京")
        map.put("birthday", new Date());
       
        <where>會(huì)自動(dòng)處理and， 第一個(gè)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>
   
    <!--
        動(dòng)態(tài)修改
        <set>標(biāo)簽可以去掉最后一個(gè)逗號(hào)
       
        flushCache：二級(jí)緩存的刷新的配置：默認(rèn)是true：會(huì)刷新，如果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>
   
    <!-- ===============================關(guān)聯(lián)查詢================== -->
    <!-- 一對(duì)多 -->
    <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:一對(duì)多的關(guān)聯(lián)映射
        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_detail od where
        p.PERSON_ID = o.PERSON_ID
        and o.ORDER_ID = od.ORDER_ID
        and p.PERSON_ID = #{personId}
    </select>
   
    <!-- 多對(duì)多從Person一端看 -->
    <select id="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>
   
    <!-- =========================延遲加載======================== -->
    <resultMap type="person" id="selectPersonByIdLazyRM" extends="BaseResultMap">
        <!--
            column：主sql的一列作為子sql的參數(shù)
            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>
3 配置sqlMapConfig.xml中的<mappers>配置：
<!-- 集中管理表的映射文件 -->
<mappers>
   <mapper resource="com/rl/mapper/PersonTestMapper.xml" />
   <mapper resource="com/rl/mapper/PersonMapper.xml" />
   <mapper resource="com/rl/mapper/RoleMapper.xml" />
   <mapper resource="com/rl/mapper/OrdersMapper.xml" />
   <mapper resource="com/rl/mapper/OrderDetailMapper.xml" />
</mappers>
4 測(cè)試代碼如下：
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的簡(jiǎn)單查詢
 */
public class MybatisTest {
         SqlSessionFactory sessionFactory;
        
         @Before
         public void setUp() throws Exception {
                   InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml");
                   sessionFactory = new SqlSessionFactoryBuilder().build(in);
         }
 
         /**
          * 通過id查詢數(shù)據(jù)
          */
         @Test
         public void selectPersonById() {
                   //創(chuàng)建session對(duì)象
                   SqlSession session = sessionFactory.openSession();
                   try {
                            //第一個(gè)參數(shù)：指定要執(zhí)行的sql語法是namespace.sql的id，第二個(gè)參數(shù)sql要接收的參數(shù)
                            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() {
                   //創(chuàng)建session對(duì)象
                   SqlSession session = sessionFactory.openSession();
                   try {
                            //第一個(gè)參數(shù)：指定要執(zhí)行的sql語法是namespace.sql的id，第二個(gè)參數(shù)sql要接收的參數(shù)
                            Person person  = session.selectOne("com.rl.mapper.PersonMapper.selectPersonById", 1);
                            System.out.println(person);
                   } finally{
                            session.close();
                   }
         }
        
         /**
          * 查詢表的記錄數(shù)
          */
         @Test
         public void selectPersonCount() {
                   //創(chuàng)建session對(duì)象
                   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() {
                   //創(chuàng)建session對(duì)象
                   SqlSession session = sessionFactory.openSession();
                   try {
                            //查詢集合的時(shí)候需要使用selectList
                            List<Person> pList = session.selectList("com.rl.mapper.PersonMapper.selectPersonAll");
                            for(Person p : pList){
                                     System.out.println(p);
                            }
                   } finally{
                            session.close();
                   }
         }
        
         /**
          * 使用多個(gè)參數(shù)用Map方式來查詢
          */
         @Test
         public void selectPersonByParams() {
                   //創(chuàng)建session對(duì)象
                   SqlSession session = sessionFactory.openSession();
                   try {
                            Map<String, Object> map = new HashMap<String, Object>();
                            map.put("gender",0);
                           map.put("birthday", new Date());
                            //查詢集合的時(shí)候需要使用selectList
                            List<Person> pList = session.selectList("com.rl.mapper.PersonMapper.selectPersonByParams",map);
                            for(Person p : pList){
                                     System.out.println(p);
                            }
                   } finally{
                            session.close();
                   }
         }
        
         /**
          * 使用多個(gè)參數(shù)查詢數(shù)據(jù)，使用查詢對(duì)象的方式
          */
         @Test
         public void selectPersonByParams1() {
                   //創(chuàng)建session對(duì)象
                   SqlSession session = sessionFactory.openSession();
                   try {
                            QueryCondition qc = new QueryCondition();
                            qc.setGender("0");
                            qc.setBirthday(new Date());
                            //查詢集合的時(shí)候需要使用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() {
                   //創(chuàng)建session對(duì)象
                   SqlSession session = sessionFactory.openSession();
                   try {
                            QueryCondition qc = new QueryCondition();
                            qc.setName("武");
                            //查詢集合的時(shí)候需要使用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的內(nèi)容如下：
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的數(shù)據(jù)庫表的變更
 */
public class MybatisTest1 {
         SqlSessionFactory sessionFactory;
        
         @Before
         public void setUp() throws Exception {
                   InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml");
                   sessionFactory = new SqlSessionFactoryBuilder().build(in);
         }
 
         /**
          * 數(shù)據(jù)插入主鍵返回
          * 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的動(dòng)態(tài)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() {
                   //創(chuàng)建session對(duì)象
                   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());
                            //第一個(gè)參數(shù)：指定要執(zhí)行的sql語法是namespace.sql的id，第二個(gè)參數(shù)sql要接收的參數(shù)
                            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() {
                   //創(chuàng)建session對(duì)象
                   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(i0 == 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(i0 == 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的內(nèi)容如下：
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的關(guān)聯(lián)查詢
 */
public class MybatisTest3 {
 
   SqlSessionFactory sessionFactory;
  
   @Before
   public void setUp() throws Exception {
      InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml");
      sessionFactory = new SqlSessionFactoryBuilder().build(in);
   }
 
   /**
    * 一對(duì)多關(guān)聯(lián)查詢
    */
   @Test
   public void selectPersonAndOrderByPId() {
      //創(chuàng)建session對(duì)象
      SqlSession session = sessionFactory.openSession();
      try {
         //第一個(gè)參數(shù)：指定要執(zhí)行的sql語法是namespace.sql的id，第二個(gè)參數(shù)sql要接收的參數(shù)
         Person person  = session.selectOne("com.rl.mapper.PersonMapper.selectPersonAndOrderByPId", 1);
         System.out.println(person);
      } finally{
         session.close();
      }
   }
  
   /**
    * 查詢Person下的所有訂單和訂單下的明細(xì)
    */
   @Test
   public void selectPersonOrderAndDetailByPId() {
      //創(chuàng)建session對(duì)象
      SqlSession session = sessionFactory.openSession();
      try {
         //第一個(gè)參數(shù)：指定要執(zhí)行的sql語法是namespace.sql的id，第二個(gè)參數(shù)sql要接收的參數(shù)
         Person person  = session.selectOne("com.rl.mapper.PersonMapper.selectPersonOrderAndDetailByPId", 1);
         System.out.println(person);
      } finally{
         session.close();
      }
   }
  
   /**
    * 多對(duì)多查詢從Person端出發(fā)
    */
   @Test
   public void selectPersonAndRoleByPId() {
      //創(chuàng)建session對(duì)象
      SqlSession session = sessionFactory.openSession();
      try {
         //第一個(gè)參數(shù)：指定要執(zhí)行的sql語法是namespace.sql的id，第二個(gè)參數(shù)sql要接收的參數(shù)
         Person person  = session.selectOne("com.rl.mapper.PersonMapper.selectPersonAndRoleByPId", 1);
         System.out.println(person);
      } finally{
         session.close();
      }
   }
  
   /**
    * 多對(duì)多查詢從角色端來看
    */
   @Test
   public void selectRoleAndPersonByRId() {
      //創(chuàng)建session對(duì)象
      SqlSession session = sessionFactory.openSession();
      try {
         //第一個(gè)參數(shù)：指定要執(zhí)行的sql語法是namespace.sql的id，第二個(gè)參數(shù)sql要接收的參數(shù)
         Role role  = session.selectOne("com.rl.mapper.RoleMapper.selectRoleAndPersonByRId", 1);
         System.out.println(role);
      } finally{
         session.close();
      }
   }
  
   /**
    * 多對(duì)一的關(guān)聯(lián)查詢
*這里的mapper配置文件在后續(xù)的博文中定義。
    */
   @Test
   public void selectPersonByOrderId() {
      //創(chuàng)建session對(duì)象
      SqlSession session = sessionFactory.openSession();
      try {
         Orders order  = session.selectOne("com.rl.mapper.OrdersMapper.selectPersonByOrderId", 1);
         System.out.println(order);
      } finally{
         session.close();
      }
   }
  
   /**
    * 多對(duì)一和一對(duì)多混合查詢
    * 這里的mapper配置文件在后續(xù)的博文中定義。
    */
   @Test
   public void selectPersonAndDetailByOrderId() {
      //創(chuàng)建session對(duì)象
      SqlSession session = sessionFactory.openSession();
      try {
         Orders order  = session.selectOne("com.rl.mapper.OrdersMapper.selectPersonAndDetailByOrderId", 1);
         System.out.println(order);
      } finally{
         session.close();
      }
   }
}
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容