mybatis中特殊SQL的几种配置方法

1.in条件for循环

<select id="getMaxBookingSeqList" resultMap="java.util.Map" parameterType="java.util.List" >
	select booking_no, max(booking_general_seq) max_seq from interface_booking_t where booking_no in
      <foreach item="item" index="index" collection="list" open="(" separator="," close=")">
	 	#{item}
	  </foreach>	
 	group by booking_no
  </select>

2.insert并且返回主键

<insert id="insertAndGetId" parameterType="com.phy.mlisp.management.domain.Document">
		<selectKey resultType="long" order="BEFORE" keyProperty="id">
			SELECT MLISP_DOCUMENT_SEQ.NEXTVAL as ID from DUAL
		</selectKey>
		insert into MLISP_DOCUMENT_T (ID, NAME, ALIAS,
		FILE_SIZE,CONTENT,STATUS,CREATE_BY,CREATE_DATE,LAST_UPDATE_BY,
		LAST_UPDATE_DATE)
		values (#{id},#{name,jdbcType=VARCHAR}, #{alias,jdbcType=VARCHAR},#{fileSize,jdbcType=INTEGER},
		#{content,jdbcType=BLOB},
		#{status,jdbcType=INTEGER},#{createBy,jdbcType=VARCHAR},SYSDATE,
		#{lastUpdateBy,jdbcType=VARCHAR},SYSDATE)
	</insert>

3.批量insert

<insert id="batchInsert" parameterType="java.util.List">
  insert into INTERFACE_BOOKING_CONTA_T (ID, BOOKING_ID, BOOKING_CONTAINER_SEQ, 
      CONTA_TYPE, CONTA_SIZE, CONTA_QUANTITY, 
      CONTA_SIZE_TYPE_ISO)
              select  interface_booking_conta_seq.NEXTVAL,A.*  from(
              <foreach collection="list" item="item" index="index"  separator="UNION ALL" >
                  (select #{item.bookingId,jdbcType=DECIMAL}, #{item.bookingContainerSeq,jdbcType=DECIMAL}, 
			      #{item.contaType,jdbcType=VARCHAR}, #{item.contaSize,jdbcType=VARCHAR}, #{item.contaQuantity,jdbcType=DECIMAL}, 
			      #{item.contaSizeTypeIso,jdbcType=VARCHAR} from dual)
             </foreach>) A
     </insert> 

4.批量更新

<update id="batchUpdate" parameterType="java.util.List">
      <foreach collection="list" item="item" index="index" open="begin" close=";end;" separator=";">
      	UPDATE WCP_DISPATCH_CONTA_T
		<set>
		    <if test="item.dispatchId != null">
                      DISPATCH_ID = #{item.dispatchId,jdbcType=DECIMAL},
                    </if>
		</set>
		WHERE DISPATCH_ID = #{item.dispatchId,jdbcType=DECIMAL}
      </foreach>
    </update>

5.返回Map<String,Object>或List<Map<String,Object>>

<select id="getPaymentSOList" resultType="java.util.HashMap" parameterType="java.util.Map">
   select * from dual
        WHERE 1=1
          <if test="firstDay!=null" >
             AND ws.create_date >= #{firstDay,jdbcType=TIMESTAMP}   
         </if>
</select>

Dao接口

List<Map<String,Object>> getPaymentSOList(Map<String,Object> map);
Map<String,Object> getPaymentSO(Map<String,Object> map);
int batchInsert(List<T> wbList);
void batchUpdate(List<T> recordList);
long insertAndGetId(T record);
List<T> getMaxBookingSeqList(ArrayList<String> arrayList);