前面写的关于 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>
以上