Mybatis 批量操作

使用mybatis进行批量查询，更新，插入操作，主要针对oracle数据库（不同数据库都略有差别）

1.批量查询

在mybatis的mapper映射文件中的select写法，两种传递的参数不同，循环的时候  #{}写法不同，如下为对应的mapper和dao

mapper.xml

 

<select id="findByNo1" parameterType="java.util.Map"  resultType="com.lp.entity.Account">
      select * from account where account_id in 
      <foreach collection="acc1list" item="item" index="index" open="(" separator="," close=")">
              #{item}
      </foreach>
 </select>
      
   <select id="findByNo2" parameterType="java.util.Map"  resultType="com.lp.entity.Account">
      select * from account where account_id in 
      <foreach collection="acc2list" item="item" index="index" open="(" separator="," close=")">
              #{item.account_id}
     </foreach>
   </select>

 

 

　　dao:

//@Param("acc1list")中的acc1list与对应的mapper.xml中循环的foreach中的collection对应，写上此注解后resultType可以不写。

List<Account>  findByNo1(@Param("acc1list") List<Integer> lists);
List<Account>  findByNo2(@Param("acc2list") List<Account> lists);

 

 

 

2.批量插入

批量插入中使用foreach，各参数如下xml代码，与查询类似

3.批量更新

批量更新时，若连接oracle数据库，则要加上begin和;end;

否则会报无效字符的错误。

 

 

如下mapper.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="cn.com.sinokorealife.base.dao.IBaseEmpDao"> 
    <sql id="baseSelect">
     <![CDATA[
         select           
                                               GUID, 
                                                                ISBN_CODE, 
                                                                NAME, 
                                                                RANK_NO, 
                                                                FOLK_NO, 
                                                                PARTY_NO, 
                                                                HIRE_DATE, 
                                                                LEAVE_DATE, 
                                                                MARRY_STATUS, 
                                                                ORGAN_GUID, 
                                                                DEPT_GUID, 
                                                                BIRTHDAY, 
                                                                SEX, 
                                                                CARD_TYPE, 
                                                                CARD_CODE, 
                                                                EMAIL, 
                                                                RESIDENTPLACE, 
                                                                ARCHIVESITE, 
                                                                DEGREE_NO, 
                                                                PROF_NO, 
                                                                PROF_NO_APP, 
                                                                PROFTITLE_NO, 
                                                                CONTACTPERSON, 
                                                                HOME_ADDRESS, 
                                                                POSTCODE, 
                                                                MOBILETEL, 
                                                                OFFICETEL, 
                                                                STATE, 
                                                                ATTENDANCE_NO, 
                                                                 DORDER,
                                                                 CHIEF_POST,
                                                                 ADMIN_POST,
                                                                 ADMIN_LEVEL,
                                                                 VENDOR_ID 
                                       from  BASE_EMP 
            where 1 = 1            
      ]]>
    </sql>
    
    <sql id="base_conditions">
      <if test="baseEmp.conditions != null">
              <![CDATA[
                  ${baseEmp.conditions}
            ]]>
      </if>
    </sql>

     <sql id="baseColumns">
       <![CDATA[
                                     GUID, 
                                                 ISBN_CODE, 
                                                 NAME, 
                                                 RANK_NO, 
                                                 FOLK_NO, 
                                                 PARTY_NO, 
                                                 HIRE_DATE, 
                                                 LEAVE_DATE, 
                                                 MARRY_STATUS, 
                                                 ORGAN_GUID, 
                                                 DEPT_GUID, 
                                                 BIRTHDAY, 
                                                 SEX, 
                                                 CARD_TYPE, 
                                                 CARD_CODE, 
                                                 EMAIL, 
                                                 RESIDENTPLACE, 
                                                 ARCHIVESITE, 
                                                 DEGREE_NO, 
                                                 PROF_NO, 
                                                 PROF_NO_APP, 
                                                 PROFTITLE_NO, 
                                                 CONTACTPERSON, 
                                                 HOME_ADDRESS, 
                                                 POSTCODE, 
                                                 MOBILETEL, 
                                                 OFFICETEL, 
                                                 STATE, 
                                                 ATTENDANCE_NO, 
                                                 DORDER,
                                                CHIEF_POST,
                                                 ADMIN_POST,
                                                  ADMIN_LEVEL,
                                                 VENDOR_ID     
                            ]]>
    </sql>
    <sql id="insertValues">
                                   #{baseEmp.guid,jdbcType=INTEGER},
                                               #{baseEmp.isbnCode,jdbcType=VARCHAR},
                                               #{baseEmp.name,jdbcType=VARCHAR},
                                               #{baseEmp.rankNo,jdbcType=VARCHAR},
                                               #{baseEmp.folkNo,jdbcType=VARCHAR},
                                               #{baseEmp.partyNo,jdbcType=VARCHAR},
                                               #{baseEmp.hireDate,jdbcType=TIMESTAMP},
                                               #{baseEmp.leaveDate,jdbcType=TIMESTAMP},
                                               #{baseEmp.marryStatus,jdbcType=VARCHAR},
                                               #{baseEmp.organGuid,jdbcType=INTEGER},
                                               #{baseEmp.deptGuid,jdbcType=INTEGER},
                                               #{baseEmp.birthday,jdbcType=TIMESTAMP},
                                               #{baseEmp.sex,jdbcType=VARCHAR},
                                               #{baseEmp.cardType,jdbcType=VARCHAR},
                                               #{baseEmp.cardCode,jdbcType=VARCHAR},
                                               #{baseEmp.email,jdbcType=VARCHAR},
                                               #{baseEmp.residentplace,jdbcType=VARCHAR},
                                               #{baseEmp.archivesite,jdbcType=VARCHAR},
                                               #{baseEmp.degreeNo,jdbcType=VARCHAR},
                                               #{baseEmp.profNo,jdbcType=VARCHAR},
                                               #{baseEmp.profNoApp,jdbcType=VARCHAR},
                                               #{baseEmp.proftitleNo,jdbcType=VARCHAR},
                                               #{baseEmp.contactperson,jdbcType=VARCHAR},
                                               #{baseEmp.homeAddress,jdbcType=VARCHAR},
                                               #{baseEmp.postcode,jdbcType=VARCHAR},
                                               #{baseEmp.mobiletel,jdbcType=VARCHAR},
                                               #{baseEmp.officetel,jdbcType=VARCHAR},
                                               #{baseEmp.state,jdbcType=VARCHAR},
                                               #{baseEmp.attendanceNo,jdbcType=VARCHAR},
                                               #{baseEmp.dorder,jdbcType=INTEGER},
                                               #{baseEmp.chiefPost,jdbcType=VARCHAR},
                                               #{baseEmp.adminPost,jdbcType=VARCHAR},
                                               #{baseEmp.adminLevel,jdbcType=VARCHAR},
                                               #{baseEmp.vendorId,jdbcType=INTEGER} 
                        </sql>
    
    <sql id="insertBatchValues">
                                  '${baseEmp.guid}',
                                              '${baseEmp.isbnCode}',
                                              '${baseEmp.name}',
                                              '${baseEmp.rankNo}',
                                              '${baseEmp.folkNo}',
                                              '${baseEmp.partyNo}',
                                              '${baseEmp.hireDate}',
                                              '${baseEmp.leaveDate}',
                                              '${baseEmp.marryStatus}',
                                              '${baseEmp.organGuid}',
                                              '${baseEmp.deptGuid}',
                                              '${baseEmp.birthday}',
                                              '${baseEmp.sex}',
                                              '${baseEmp.cardType}',
                                              '${baseEmp.cardCode}',
                                              '${baseEmp.email}',
                                              '${baseEmp.residentplace}',
                                              '${baseEmp.archivesite}',
                                              '${baseEmp.degreeNo}',
                                              '${baseEmp.profNo}',
                                              '${baseEmp.profNoApp}',
                                              '${baseEmp.proftitleNo}',
                                              '${baseEmp.contactperson}',
                                              '${baseEmp.homeAddress}',
                                              '${baseEmp.postcode}',
                                              '${baseEmp.mobiletel}',
                                              '${baseEmp.officetel}',
                                              '${baseEmp.state}',
                                              '${baseEmp.attendanceNo}',
                                              '${baseEmp.dorder}',
                                              '${baseEmp.chiefPost}',
                                              '${baseEmp.adminPost}',
                                              '${baseEmp.adminLevel}',
                                              '${baseEmp.vendorId}' 
                        </sql>
    
    
    <insert id="insertEntity">
      <selectKey resultType="java.lang.Long" order="BEFORE" keyProperty="baseEmp.guid">
             SELECT SEQ_BASE_EMP.NEXTVAL AS GUID FROM DUAL
      </selectKey>
      insert into BASE_EMP (<include refid="baseColumns"/>) values (<include refid="insertValues"/>)      
    </insert>
    
    
                
                    
                    
                    
    <insert id="insertBatchEntity">    
                insert into BASE_EMP ( <include refid="baseColumns" /> ) 
              (
                <foreach collection="baseEmpList" item="baseEmp" index="index" separator="union all">
                     select <include refid="insertBatchValues"/> from dual
                </foreach>
             )
     </insert>
    
<update id="updateEntity">
        update BASE_EMP  set 
                                      GUID = #{baseEmp.guid,jdbcType=INTEGER},
                                                   ISBN_CODE = #{baseEmp.isbnCode,jdbcType=VARCHAR},
                                                   NAME = #{baseEmp.name,jdbcType=VARCHAR},
                                                   RANK_NO = #{baseEmp.rankNo,jdbcType=VARCHAR},
                                                   FOLK_NO = #{baseEmp.folkNo,jdbcType=VARCHAR},
                                                   PARTY_NO = #{baseEmp.partyNo,jdbcType=VARCHAR},
                                                   HIRE_DATE = #{baseEmp.hireDate,jdbcType=TIMESTAMP},
                                                   LEAVE_DATE = #{baseEmp.leaveDate,jdbcType=TIMESTAMP},
                                                   MARRY_STATUS = #{baseEmp.marryStatus,jdbcType=VARCHAR},
                                                   ORGAN_GUID = #{baseEmp.organGuid,jdbcType=INTEGER},
                                                   DEPT_GUID = #{baseEmp.deptGuid,jdbcType=INTEGER},
                                                   BIRTHDAY = #{baseEmp.birthday,jdbcType=TIMESTAMP},
                                                   SEX = #{baseEmp.sex,jdbcType=VARCHAR},
                                                   CARD_TYPE = #{baseEmp.cardType,jdbcType=VARCHAR},
                                                   CARD_CODE = #{baseEmp.cardCode,jdbcType=VARCHAR},
                                                   EMAIL = #{baseEmp.email,jdbcType=VARCHAR},
                                                   RESIDENTPLACE = #{baseEmp.residentplace,jdbcType=VARCHAR},
                                                   ARCHIVESITE = #{baseEmp.archivesite,jdbcType=VARCHAR},
                                                   DEGREE_NO = #{baseEmp.degreeNo,jdbcType=VARCHAR},
                                                   PROF_NO = #{baseEmp.profNo,jdbcType=VARCHAR},
                                                   PROF_NO_APP = #{baseEmp.profNoApp,jdbcType=VARCHAR},
                                                   PROFTITLE_NO = #{baseEmp.proftitleNo,jdbcType=VARCHAR},
                                                   CONTACTPERSON = #{baseEmp.contactperson,jdbcType=VARCHAR},
                                                   HOME_ADDRESS = #{baseEmp.homeAddress,jdbcType=VARCHAR},
                                                   POSTCODE = #{baseEmp.postcode,jdbcType=VARCHAR},
                                                   MOBILETEL = #{baseEmp.mobiletel,jdbcType=VARCHAR},
                                                   OFFICETEL = #{baseEmp.officetel,jdbcType=VARCHAR},
                                                   STATE = #{baseEmp.state,jdbcType=VARCHAR},
                                                   ATTENDANCE_NO = #{baseEmp.attendanceNo,jdbcType=VARCHAR},
                                                   CHIEF_POST = #{baseEmp.chiefPost,jdbcType=VARCHAR},
                                                   ADMIN_POST = #{baseEmp.adminPost,jdbcType=VARCHAR},
                                                   ADMIN_LEVEL = #{baseEmp.adminLevel,jdbcType=VARCHAR},
                                                  DORDER = #{baseEmp.dorder,jdbcType=INTEGER},
                                                  VENDOR_ID = #{baseEmp.vendorId,jdbcType=INTEGER}
                                               where GUID  = #{baseEmp.guid,jdbcType=INTEGER}
                    </update>
    <update id="updateBatchEntity"  >
     begin  
 
                <foreach collection="baseEmpList2" item="baseEmp" index="index"   separator=";" >
                           update BASE_EMP  <set> 
                                                   MOBILETEL = #{baseEmp.mobiletel,jdbcType=VARCHAR},
                                                   OFFICETEL = #{baseEmp.officetel,jdbcType=VARCHAR}
                                              </set>
                                               where NAME =#{baseEmp.name,jdbcType=VARCHAR} 
                 </foreach>
    ;end;
 
    </update>


     <delete id="deleteEntity">
         delete from BASE_EMP where  1 = 1
                     <if test="baseEmp.state != null">
                  and  STATE = #{baseEmp.state}
               </if>
                    <if test="baseEmp.proftitleNo != null">
                  and  PROFTITLE_NO = #{baseEmp.proftitleNo}
               </if>
                    <if test="baseEmp.guid != null">
                  and  GUID = #{baseEmp.guid}
               </if>
                    <if test="baseEmp.rankNo != null">
                  and  RANK_NO = #{baseEmp.rankNo}
               </if>
                    <if test="baseEmp.postcode != null">
                  and  POSTCODE = #{baseEmp.postcode}
               </if>
                    <if test="baseEmp.degreeNo != null">
                  and  DEGREE_NO = #{baseEmp.degreeNo}
               </if>
                    <if test="baseEmp.archivesite != null">
                  and  ARCHIVESITE = #{baseEmp.archivesite}
               </if>
                    <if test="baseEmp.cardCode != null">
                  and  CARD_CODE = #{baseEmp.cardCode}
               </if>
                    <if test="baseEmp.hireDate != null">
                  and  HIRE_DATE = #{baseEmp.hireDate}
               </if>
                    <if test="baseEmp.marryStatus != null">
                  and  MARRY_STATUS = #{baseEmp.marryStatus}
               </if>
                    <if test="baseEmp.sex != null">
                  and  SEX = #{baseEmp.sex}
               </if>
                    <if test="baseEmp.profNo != null">
                  and  PROF_NO = #{baseEmp.profNo}
               </if>
                    <if test="baseEmp.email != null">
                  and  EMAIL = #{baseEmp.email}
               </if>
                    <if test="baseEmp.officetel != null">
                  and  OFFICETEL = #{baseEmp.officetel}
               </if>
                    <if test="baseEmp.isbnCode != null">
                  and  ISBN_CODE = #{baseEmp.isbnCode}
               </if>
                    <if test="baseEmp.birthday != null">
                  and  BIRTHDAY = #{baseEmp.birthday}
               </if>
                    <if test="baseEmp.cardType != null">
                  and  CARD_TYPE = #{baseEmp.cardType}
               </if>
                    <if test="baseEmp.contactperson != null">
                  and  CONTACTPERSON = #{baseEmp.contactperson}
               </if>
                    <if test="baseEmp.name != null">
                  and  NAME = #{baseEmp.name}
               </if>
                    <if test="baseEmp.profNoApp != null">
                  and  PROF_NO_APP = #{baseEmp.profNoApp}
               </if>
                    <if test="baseEmp.partyNo != null">
                  and  PARTY_NO = #{baseEmp.partyNo}
               </if>
                    <if test="baseEmp.deptGuid != null">
                  and  DEPT_GUID = #{baseEmp.deptGuid}
               </if>
                    <if test="baseEmp.leaveDate != null">
                  and  LEAVE_DATE = #{baseEmp.leaveDate}
               </if>
                    <if test="baseEmp.attendanceNo != null">
                  and  ATTENDANCE_NO = #{baseEmp.attendanceNo}
               </if>
                    <if test="baseEmp.organGuid != null">
                  and  ORGAN_GUID = #{baseEmp.organGuid}
               </if>
                    <if test="baseEmp.residentplace != null">
                  and  RESIDENTPLACE = #{baseEmp.residentplace}
               </if>
                    <if test="baseEmp.homeAddress != null">
                  and  HOME_ADDRESS = #{baseEmp.homeAddress}
               </if>
                    <if test="baseEmp.dorder != null">
                  and  DORDER = #{baseEmp.dorder}
               </if>
                    <if test="baseEmp.folkNo != null">
                  and  FOLK_NO = #{baseEmp.folkNo}
               </if>
                    <if test="baseEmp.mobiletel != null">
                  and  MOBILETEL = #{baseEmp.mobiletel}
               </if>
                    <if test="baseEmp.chiefPost != null">
                    and  CHIEF_POST = #{baseEmp.chiefPost}
               </if>
                    <if test="baseEmp.adminPost != null">
                    and  ADMIN_POST = #{baseEmp.adminPost}
               </if>
                    <if test="baseEmp.adminLevel != null">
                    and  ADMIN_LEVEL = #{baseEmp.adminLevel}
               </if>
    </delete>

    
    <resultMap  id="baseEmp" type="cn.com.sinokorealife.base.entity.BaseEmp" autoMapping="true">
                    <id     property="guid"   column="GUID"/>
                
                                        <result     property="isbnCode"   column="ISBN_CODE"/>
                                <result     property="name"   column="NAME"/>
                                <result     property="rankNo"   column="RANK_NO"/>
                                <result     property="folkNo"   column="FOLK_NO"/>
                                <result     property="partyNo"   column="PARTY_NO"/>
                                <result     property="hireDate"   column="HIRE_DATE"/>
                                <result     property="leaveDate"   column="LEAVE_DATE"/>
                                <result     property="marryStatus"   column="MARRY_STATUS"/>
                                <result     property="organGuid"   column="ORGAN_GUID"/>
                                <result     property="deptGuid"   column="DEPT_GUID"/>
                                <result     property="birthday"   column="BIRTHDAY"/>
                                <result     property="sex"   column="SEX"/>
                                <result     property="cardType"   column="CARD_TYPE"/>
                                <result     property="cardCode"   column="CARD_CODE"/>
                                <result     property="email"   column="EMAIL"/>
                                <result     property="residentplace"   column="RESIDENTPLACE"/>
                                <result     property="archivesite"   column="ARCHIVESITE"/>
                                <result     property="degreeNo"   column="DEGREE_NO"/>
                                <result     property="profNo"   column="PROF_NO"/>
                                <result     property="profNoApp"   column="PROF_NO_APP"/>
                                <result     property="proftitleNo"   column="PROFTITLE_NO"/>
                                <result     property="contactperson"   column="CONTACTPERSON"/>
                                <result     property="homeAddress"   column="HOME_ADDRESS"/>
                                <result     property="postcode"   column="POSTCODE"/>
                                <result     property="mobiletel"   column="MOBILETEL"/>
                                <result     property="officetel"   column="OFFICETEL"/>
                                <result     property="state"   column="STATE"/>
                                <result     property="attendanceNo"   column="ATTENDANCE_NO"/>
                                <result     property="chiefPost"   column="CHIEFPOST"/>
                                <result     property="adminPost"   column="ADMINPOST"/>
                                <result     property="adminLevel"   column="ADMINLEVEL"/>
                                <result     property="dorder"   column="DORDER"/>
                                <result     property="vendorId"   column="VENDOR_ID"/>
     </resultMap>
</mapper>

 

对应的dao方法。（无删除的方法）如下

package cn.com.sinokorealife.base.dao;

import java.util.List;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.session.RowBounds;
import org.springframework.stereotype.Repository;
import cn.com.sinokorealife.base.entity.BaseEmp;
import cn.com.sinokorealife.common.page.Pagination;
import cn.com.sinokorealife.common.exception.BusinessException;


/**
 *
 *  @author : 自动生成
 *  @date   : 2014年05月10日 09:29:27
 *  @desc   : 员工表数据库层
 */

@Repository("baseEmpDao")
public interface IBaseEmpDao{
    /**
     * 插入员工表
     * @param  baseEmp
     * @throws Exception
     */
     void insertEntity(@Param("baseEmp")BaseEmp baseEmp) throws BusinessException;
     
     /**
      * 批量插入员工表
      * @param  baseEmpList
      * @throws Exception
      */
     void insertBatchEntity(@Param("baseEmpList")List<BaseEmp> baseEmpList) throws BusinessException;
     

     /**
      * 更新 员工表
      * @param baseEmp
      * @return
      * @throws BusinessException
      */
    void updateEntity(@Param("baseEmp")BaseEmp baseEmp) throws BusinessException;
    
    /**
      * 批量更新 员工表
      * @param baseEmp
      * @return
      * @throws BusinessException
      */
    void updateBatchEntity(@Param("baseEmpList2")List<BaseEmp> baseEmpList) throws BusinessException;
}