mybatis 总结

前面写的关于 mybatis 的，进行总结的文章

include

主要针对 mybatis 中 sql 语句的复用，可以减少代码量，例如下：

	<sql id="insertIntoHoldSql">
		INSERT INTO tb_day_hold (
			…… 
		)VALUES (
			……
		)
	</sql>

	<insert id="addOrUpdateOne_addAmount" parameterType="……Domain">
		<include refid="insertIntoHoldSql"></include>
		<![CDATA[
		ON DUPLICATE KEY UPDATE
		amount = amount + VALUES(amount)
		;
		]]>
	</insert>

组合之后，addOrUpdateOne_addAmount 的 sql 语句，就是 insert into …… ON DUPLICATE KEY UPDATE ……两者拼组之后的 SQL 语句

choose

没见着 mybatis 中，有 if else 的使用，而 choose when 可以替代这个功能，如下：

		<choose>
			<when test="…… ">
				……
			</when>
			<when test="…… ">
				……
			</when>
			<otherwise>
				……
			</otherwise>
		</choose>

if

if 多用于查询条件的拼组，如下：

	    WHERE ……
		    <if test="……">  
		        ……
		    </if>   
		<if test="…… ">  
		        ……
		    </if>   

foreach

foreach 一般循环 mybatis 中传递过来的 list ，进行逐条操作，如下：

		<foreach collection="list" index="index" item="item">
			……
				#{item.firm_account  } ,
			……
			;
		</foreach>

实例

以下是我们项目，真实使用的代码的实例，已经过测试，如下：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.hundsun.cloudtrade.match.dao.IDayHoldDao">

	<sql id="insertIntoHoldSql">
		INSERT INTO tb_day_hold (
			firm_account    ,
			seat_no         ,
			stock_account   ,
			exchange_type   ,
			stock_name      ,
			stock_code      ,
			amount          ,
			market_value    ,
			position_str    
		)VALUES (
			#{firm_account  } ,
			#{seat_no       } ,
			#{stock_account } ,
			#{exchange_type } ,
			#{stock_name    } ,
			#{stock_code    } ,
		<choose>
			<when test="occur_amount != null and occur_amount != '' ">
				#{occur_amount        } ,  
			</when>
			<when test="amount != null and amount != '' ">
				#{amount        } ,  
			</when>
			<otherwise>
				0,
			</otherwise>
		</choose>
			#{market_value  } ,
			#{position_str  } 
		)
	</sql>

	<insert id="addOrUpdateOne_addAmount" parameterType="com.hundsun.cloudtrade.match.domain.DayHoldDomain">
		<include refid="insertIntoHoldSql"></include>
		<![CDATA[
		ON DUPLICATE KEY UPDATE
		amount = amount + VALUES(amount)
		;
		]]>
	</insert>

	<insert id="addOrUpdateOne_amount" parameterType="com.hundsun.cloudtrade.match.domain.DayHoldDomain">
		<include refid="insertIntoHoldSql"></include>
		<![CDATA[
		ON DUPLICATE KEY UPDATE
		amount = VALUES(amount) , seat_no = VALUES(seat_no)
		;
		]]>
	</insert>
	
	<insert id="batchInsert" parameterType="java.util.List">
		<foreach collection="list" index="index" item="item">
			INSERT INTO tb_day_hold (
				firm_account    ,
				seat_no         ,
				stock_account   ,
				exchange_type   ,
				stock_name      ,
				stock_code      ,
				amount          ,
				market_value    ,
				position_str    
			)VALUES (
				#{item.firm_account  } ,
				#{item.seat_no       } ,
				#{item.stock_account } ,
				#{item.exchange_type } ,
				#{item.stock_name    } ,
				#{item.stock_code    } ,
				#{item.amount        } ,  
				#{item.market_value  } ,
				#{item.position_str  } 
			)
			ON DUPLICATE KEY UPDATE
			amount = VALUES(amount) , seat_no = VALUES(seat_no)
			;
		</foreach>
	</insert>
	
	<select id="qryMultiRecords"
		parameterType="com.hundsun.cloudtrade.match.dto.req.QryHoldReq"
		resultType="com.hundsun.cloudtrade.match.domain.DayHoldDomain">
		SELECT 
		    		firm_account    ,
					seat_no         ,
					stock_account   ,
					exchange_type   ,
					stock_name      ,
					stock_code      ,
					amount          ,
					market_value    ,
					position_str    
		<choose>
			<!-- 查询历史持仓表 -->
		    <when test="hold_date != null and hold_date != '' ">
		    		,hold_date FROM tb_history_hold
		    </when>
		    <!-- 查询当日持仓 -->
		    <otherwise>
		    		,'' hold_date FROM tb_day_hold
		    </otherwise>
	    </choose>
	    WHERE firm_account = #{firm_account}
		    <if test="seat_no != null and seat_no != '' ">  
		        AND seat_no = #{seat_no}  
		    </if>   
			<if test="exchange_type != null and exchange_type != '' ">  
		        AND exchange_type = #{exchange_type}  
		    </if>   
			<if test="stock_account != null and stock_account != '' ">  
		        AND stock_account = #{stock_account}  
		    </if>   
			<if test="position_str != null and position_str != '' ">  
		        AND position_str = #{position_str}  
		    </if>   
			    ORDER BY stock_code DESC
			<if test="req_number != null and req_number != '' ">  
		        LIMIT 0,#{req_number}
		    </if>
		    ;
	</select>
	
	<delete id="delMultiRecords" parameterType="com.hundsun.cloudtrade.match.dto.req.DelHoldsReq">
		<![CDATA[
			DELETE FROM tb_history_hold WHERE firm_account = #{firm_account};
			DELETE FROM tb_day_hold WHERE firm_account = #{firm_account};
		]]>
	</delete>
	
</mapper>

以上