MyBatis SQL动态装配
MyBatis的方便在于可以配置动态SQL,通过过滤器进行动态装配。在刚开始使用中,遇到不少问题,其中update语句也需要动态装配,核心在于DAO层要与.xml文件中的语句和变量名要匹配。例如:
DAO层如下配置:
public void updateByJid(@Param("objectResult")RBTaskScoreDO rbTaskScore, @Param("filterRules")List<FilterRule> filterRules);
xml中如下:
<update id="updateByJid"> update RB_TASK_SCORE <set> <if test="objectResult.userId != null"> USER_ID = #{objectResult.userId,jdbcType=VARCHAR}, </if> <if test="objectResult.rid != null"> RID = #{objectResult.rid,jdbcType=NUMERIC}, </if> <if test="objectResult.score != null"> SCORE = #{objectResult.score,jdbcType=NUMERIC}, </if> <if test="objectResult.adddate != null"> ADDDATE = #{objectResult.adddate,jdbcType=TIMESTAMP}, </if> <if test="objectResult.checkState != null"> CHECK_STATE = #{objectResult.checkState,jdbcType=NUMERIC}, </if> <if test="objectResult.gid != null"> GID = #{objectResult.gid,jdbcType=NUMERIC}, </if> <if test="objectResult.isCheck != null"> IS_CHECK = #{objectResult.isCheck,jdbcType=NUMERIC}, </if> <if test="objectResult.roadLength != null"> ROAD_LENGTH = #{objectResult.roadLength,jdbcType=NUMERIC}, </if> <if test="objectResult.checkIsBound != null"> CHECK_IS_BOUND = #{objectResult.checkIsBound,jdbcType=NUMERIC}, </if> <if test="objectResult.matchLenPct != null"> MATCH_LEN_PCT = #{objectResult.matchLenPct,jdbcType=NUMERIC}, </if> <if test="objectResult.matchPntNum != null"> MATCH_PNT_NUM = #{objectResult.matchPntNum,jdbcType=NUMERIC}, </if> <if test="objectResult.isBoss != null"> IS_BOSS = #{objectResult.isBoss,jdbcType=NUMERIC}, </if> <if test="objectResult.isBossDate != null"> IS_BOSS_DATE = #{objectResult.isBossDate,jdbcType=TIMESTAMP}, </if> <if test="objectResult.md5 != null"> MD5 = #{objectResult.md5,jdbcType=VARCHAR}, </if> <if test="objectResult.checkDate != null"> CHECK_DATE = #{objectResult.checkDate,jdbcType=TIMESTAMP}, </if> <if test="objectResult.usertype != null"> USERTYPE = #{objectResult.usertype,jdbcType=NUMERIC}, </if> <if test="objectResult.srcdate != null"> SRCDATE = #{objectResult.srcdate,jdbcType=TIMESTAMP}, </if> <if test="objectResult.noBoundInfo != null"> NO_BOUND_INFO = #{objectResult.noBoundInfo,jdbcType=VARCHAR}, </if> <if test="objectResult.matchPicNum != null"> MATCH_PIC_NUM = #{objectResult.matchPicNum,jdbcType=NUMERIC}, </if> <if test="objectResult.pidStart != null"> PID_START = #{objectResult.pidStart,jdbcType=NUMERIC}, </if> <if test="objectResult.pidEnd != null"> PID_END = #{objectResult.pidEnd,jdbcType=NUMERIC}, </if> <if test="objectResult.taskDir != null"> TASK_DIR = #{objectResult.taskDir,jdbcType=NUMERIC}, </if> <if test="objectResult.taskCategory != null"> TASK_CATEGORY = #{objectResult.taskCategory,jdbcType=NUMERIC}, </if> <if test="objectResult.payState != null"> PAY_STATE = #{objectResult.payState,jdbcType=NUMERIC}, </if> <if test="objectResult.payMd5 != null"> PAY_MD5 = #{objectResult.payMd5,jdbcType=VARCHAR}, </if> <if test="objectResult.appType != null"> APP_TYPE = #{objectResult.appType,jdbcType=NUMERIC}, </if> <if test="objectResult.autonaviId != null"> AUTONAVI_ID = #{objectResult.autonaviId,jdbcType=VARCHAR}, </if> <if test="objectResult.tid != null"> TID = #{objectResult.tid,jdbcType=NUMERIC}, </if> <if test="objectResult.coefficient != null"> COEFFICIENT = #{objectResult.coefficient,jdbcType=NUMERIC}, </if> <if test="objectResult.payType != null"> PAY_TYPE = #{objectResult.payType,jdbcType=NUMERIC}, </if> </set> <include refid="Common.parseFilterRules"/> </update>
objectResult是数据库映射到java的实体对象,在XML中配置如下:
<resultMap type="com.autonavi.collect.ccs.dao.dbo.task.RBTaskScoreDO" id="objectResult"> <result column="SID" property="sid"/> <result column="USER_ID" property="userId"/> <result column="JID" property="jid"/> <result column="RID" property="rid"/> <result column="SCORE" property="score"/> <result column="ADDDATE" property="adddate"/> <result column="CHECK_STATE" property="checkState"/> <result column="GID" property="gid"/> <result column="IS_CHECK" property="isCheck"/> <result column="ROAD_LENGTH" property="roadLength"/> <result column="CHECK_IS_BOUND" property="checkIsBound"/> <result column="MATCH_LEN_PCT" property="matchLenPct"/> <result column="MATCH_PNT_NUM" property="matchPntNum"/> <result column="IS_BOSS" property="isBoss"/> <result column="IS_BOSS_DATE" property="isBossDate"/> <result column="MD5" property="md5"/> <result column="CHECK_DATE" property="checkDate"/> <result column="USERTYPE" property="usertype"/> <result column="SRCDATE" property="srcdate"/> <result column="TASK_TYPE" property="taskType"/> <result column="NO_BOUND_INFO" property="noBoundInfo"/> <result column="MATCH_PIC_NUM" property="matchPicNum"/> <result column="TASK_CLASSES" property="taskClasses"/> <result column="PID_START" property="pidStart"/> <result column="PID_END" property="pidEnd"/> <result column="TASK_DIR" property="taskDir"/> <result column="TASK_CATEGORY" property="taskCategory"/> <result column="PAY_STATE" property="payState"/> <result column="PAY_MD5" property="payMd5"/> <result column="APP_TYPE" property="appType"/> <result column="AUTONAVI_ID" property="autonaviId"/> <result column="TID" property="tid"/> <result column="COEFFICIENT" property="coefficient"/> <result column="PAY_TYPE" property="payType"/> </resultMap>
这样在配置好了之后,在java中调用update的时候,传入实体类和过滤器就OK。
RBTaskScoreDO entity = new RBTaskScoreDO(); entity.setRid(Long.parseLong(rid)); entity.setJid(Long.parseLong(jid)); entity.setCheckIsBound(Long.parseLong(checkIsBound)); entity.setNoBoundInfo(noBoundInfo); entity.setCheckState(Long.parseLong(checkState)); List<FilterRule> filterRuleList = new ArrayList<>(); filterRuleList.add(new FilterRule("jid", "=", jid)); filterRuleList.add(new FilterRule("task_type", "=", taskType)); filterRuleList.add(new FilterRule("task_classes", "=", taskClasses)); filterRuleList.add(new FilterRule("is_check", "=", 1)); rbtaskscoreService.updateByJid(entity, filterRuleList);
作者:cjingzm
出处:http://www.cnblogs.com/cjingzm/
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。