RT,业务需要为某个实体添加字段,如何在生成了Mybatis XML(包含了手写的部分联合查询)的情况下优雅的添加字段呢?
作者:方小葱
链接:https://www.zhihu.com/question/284511416/answer/438123378
来源:知乎
著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。
我非常讨厌Mybatis的其中一个原因就是因为这个~
hibernate这种生成SQL是"运行时"的;而Mybatis是"编译时"的,改动一个字段需要手动"修改"或者重新生成XML(这种需求在实际开发当中是很常见的,尤其是系统设计时间比较紧或者一些小项目,开发期间需要变动字段是很普遍的~)
因为Mybatis这种机制,我觉得目前没有很好的办法处理这个问题;
我们之前一般是将"手写的"和"生成的"分开管理~
修改字段后手写(自己管理的)自己改SQL,自动生成的重新生成一遍(可使用一些自动化工具,脚本,插件之类的);
个人觉得这种方式根本谈不上优雅(希望有人能真正给一个优雅的方式)~
总之我一直觉得Mybatis多数情况下不是一个很好地选择;
比如一些简单的小项目(比如个人博客这种)可以看看nutz这种框架;企业级管理软件之类的用hibernate(hibernate用代码直接数据库建模,然后导出DDL和文档可以省去很多工作,出来的模型约束和完整性都还不错,该加的约束和索引都能帮你加上,如果你自己设计数据库你还得考虑一遍这部分内容);银行/支付或者你认为hibernate有问题的项目(一般有问题,是因为你的开发人员hold不住,经验不够,而不是hibernate本身的问题)就用DButils,JDBCTemplate 或者自己封装一个工具;
如果你们公司很大,有专门的人帮你写SQL,检查SQL你再考虑Mybatis~
个人意见~
我介绍一下我司是如何做的。
思路如下:统一xml模板,使sql无论增加什么字段,最终的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.kanmars.sn.dao.TblDemoInfoMapper">
<resultMap id="BaseResultMap" type="cn.kanmars.sn.entity.TblDemoInfo">
<constructor>
<idArg column="demo_id" javaType="java.lang.Integer" jdbcType="DECIMAL" />
<arg column="demo_nm" javaType="java.lang.String" jdbcType="VARCHAR" />
<arg column="demo_money" javaType="java.math.BigDecimal" jdbcType="DECIMAL" />
<arg column="create_date" javaType="java.lang.String" jdbcType="CHAR" />
<arg column="create_time" javaType="java.lang.String" jdbcType="CHAR" />
<arg column="select_static" javaType="java.lang.String" jdbcType="CHAR" />
<arg column="select_dynamic" javaType="java.lang.String" jdbcType="CHAR" />
<arg column="radio_static" javaType="java.lang.String" jdbcType="CHAR" />
<arg column="radio_dynamic" javaType="java.lang.String" jdbcType="CHAR" />
<arg column="checkbox_static" javaType="java.lang.String" jdbcType="VARCHAR" />
<arg column="checkbox_dynamic" javaType="java.lang.String" jdbcType="VARCHAR" />
<arg column="net_address" javaType="java.lang.String" jdbcType="VARCHAR" />
<arg column="count_number" javaType="java.lang.Integer" jdbcType="DECIMAL" />
</constructor>
</resultMap>
<sql id="Base_Column_List">
demo_id,demo_nm,demo_money,create_date,create_time,select_static,select_dynamic,radio_static,radio_dynamic,checkbox_static,checkbox_dynamic,net_address,count_number
</sql>
<select id="select" parameterType="cn.kanmars.sn.entity.TblDemoInfo" resultMap="BaseResultMap">
select
<include refid="Base_Column_List" />
from tbl_demo_info
where 1=1
<if test="demoId != null and demoId != '' ">
and demo_id = #{demoId,jdbcType=DECIMAL}
</if>
<if test="demoNm != null and demoNm != '' ">
and demo_nm = #{demoNm,jdbcType=VARCHAR}
</if>
<if test="demoMoney != null and demoMoney != '' ">
and demo_money = #{demoMoney,jdbcType=DECIMAL}
</if>
<if test="createDate != null and createDate != '' ">
and create_date = #{createDate,jdbcType=CHAR}
</if>
<if test="createTime != null and createTime != '' ">
and create_time = #{createTime,jdbcType=CHAR}
</if>
<if test="selectStatic != null and selectStatic != '' ">
and select_static = #{selectStatic,jdbcType=CHAR}
</if>
<if test="selectDynamic != null and selectDynamic != '' ">
and select_dynamic = #{selectDynamic,jdbcType=CHAR}
</if>
<if test="radioStatic != null and radioStatic != '' ">
and radio_static = #{radioStatic,jdbcType=CHAR}
</if>
<if test="radioDynamic != null and radioDynamic != '' ">
and radio_dynamic = #{radioDynamic,jdbcType=CHAR}
</if>
<if test="checkboxStatic != null and checkboxStatic != '' ">
and checkbox_static = #{checkboxStatic,jdbcType=VARCHAR}
</if>
<if test="checkboxDynamic != null and checkboxDynamic != '' ">
and checkbox_dynamic = #{checkboxDynamic,jdbcType=VARCHAR}
</if>
<if test="netAddress != null and netAddress != '' ">
and net_address = #{netAddress,jdbcType=VARCHAR}
</if>
<if test="countNumber != null and countNumber != '' ">
and count_number = #{countNumber,jdbcType=DECIMAL}
</if>
</select>
<select id="selectList" parameterType="cn.kanmars.sn.entity.TblDemoInfo" resultMap="BaseResultMap">
select
<include refid="Base_Column_List" />
from tbl_demo_info
where 1=1
<if test="demoId != null and demoId != '' ">
and demo_id = #{demoId,jdbcType=DECIMAL}
</if>
<if test="demoNm != null and demoNm != '' ">
and demo_nm = #{demoNm,jdbcType=VARCHAR}
</if>
<if test="demoMoney != null and demoMoney != '' ">
and demo_money = #{demoMoney,jdbcType=DECIMAL}
</if>
<if test="createDate != null and createDate != '' ">
and create_date = #{createDate,jdbcType=CHAR}
</if>
<if test="createTime != null and createTime != '' ">
and create_time = #{createTime,jdbcType=CHAR}
</if>
<if test="selectStatic != null and selectStatic != '' ">
and select_static = #{selectStatic,jdbcType=CHAR}
</if>
<if test="selectDynamic != null and selectDynamic != '' ">
and select_dynamic = #{selectDynamic,jdbcType=CHAR}
</if>
<if test="radioStatic != null and radioStatic != '' ">
and radio_static = #{radioStatic,jdbcType=CHAR}
</if>
<if test="radioDynamic != null and radioDynamic != '' ">
and radio_dynamic = #{radioDynamic,jdbcType=CHAR}
</if>
<if test="checkboxStatic != null and checkboxStatic != '' ">
and checkbox_static = #{checkboxStatic,jdbcType=VARCHAR}
</if>
<if test="checkboxDynamic != null and checkboxDynamic != '' ">
and checkbox_dynamic = #{checkboxDynamic,jdbcType=VARCHAR}
</if>
<if test="netAddress != null and netAddress != '' ">
and net_address = #{netAddress,jdbcType=VARCHAR}
</if>
<if test="countNumber != null and countNumber != '' ">
and count_number = #{countNumber,jdbcType=DECIMAL}
</if>
</select>
<insert id="insert" parameterType="cn.kanmars.sn.entity.TblDemoInfo" >
insert into tbl_demo_info
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="demoId != null ">
demo_id,
</if>
<if test="demoNm != null ">
demo_nm,
</if>
<if test="demoMoney != null ">
demo_money,
</if>
<if test="createDate != null ">
create_date,
</if>
<if test="createTime != null ">
create_time,
</if>
<if test="selectStatic != null ">
select_static,
</if>
<if test="selectDynamic != null ">
select_dynamic,
</if>
<if test="radioStatic != null ">
radio_static,
</if>
<if test="radioDynamic != null ">
radio_dynamic,
</if>
<if test="checkboxStatic != null ">
checkbox_static,
</if>
<if test="checkboxDynamic != null ">
checkbox_dynamic,
</if>
<if test="netAddress != null ">
net_address,
</if>
<if test="countNumber != null ">
count_number,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="demoId != null ">
#{demoId,jdbcType=DECIMAL},
</if>
<if test="demoNm != null ">
#{demoNm,jdbcType=VARCHAR},
</if>
<if test="demoMoney != null ">
#{demoMoney,jdbcType=DECIMAL},
</if>
<if test="createDate != null ">
#{createDate,jdbcType=CHAR},
</if>
<if test="createTime != null ">
#{createTime,jdbcType=CHAR},
</if>
<if test="selectStatic != null ">
#{selectStatic,jdbcType=CHAR},
</if>
<if test="selectDynamic != null ">
#{selectDynamic,jdbcType=CHAR},
</if>
<if test="radioStatic != null ">
#{radioStatic,jdbcType=CHAR},
</if>
<if test="radioDynamic != null ">
#{radioDynamic,jdbcType=CHAR},
</if>
<if test="checkboxStatic != null ">
#{checkboxStatic,jdbcType=VARCHAR},
</if>
<if test="checkboxDynamic != null ">
#{checkboxDynamic,jdbcType=VARCHAR},
</if>
<if test="netAddress != null ">
#{netAddress,jdbcType=VARCHAR},
</if>
<if test="countNumber != null ">
#{countNumber,jdbcType=DECIMAL},
</if>
</trim>
</insert>
<update id="updateByPrimaryKey" parameterType="cn.kanmars.sn.entity.TblDemoInfo" >
update tbl_demo_info
<set>
<if test="demoNm != null">
demo_nm = #{demoNm,jdbcType=VARCHAR} ,
</if>
<if test="demoMoney != null">
demo_money = #{demoMoney,jdbcType=DECIMAL} ,
</if>
<if test="createDate != null">
create_date = #{createDate,jdbcType=CHAR} ,
</if>
<if test="createTime != null">
create_time = #{createTime,jdbcType=CHAR} ,
</if>
<if test="selectStatic != null">
select_static = #{selectStatic,jdbcType=CHAR} ,
</if>
<if test="selectDynamic != null">
select_dynamic = #{selectDynamic,jdbcType=CHAR} ,
</if>
<if test="radioStatic != null">
radio_static = #{radioStatic,jdbcType=CHAR} ,
</if>
<if test="radioDynamic != null">
radio_dynamic = #{radioDynamic,jdbcType=CHAR} ,
</if>
<if test="checkboxStatic != null">
checkbox_static = #{checkboxStatic,jdbcType=VARCHAR} ,
</if>
<if test="checkboxDynamic != null">
checkbox_dynamic = #{checkboxDynamic,jdbcType=VARCHAR} ,
</if>
<if test="netAddress != null">
net_address = #{netAddress,jdbcType=VARCHAR} ,
</if>
<if test="countNumber != null">
count_number = #{countNumber,jdbcType=DECIMAL} ,
</if>
</set>
where 1=1
and demo_id = #{demoId,jdbcType=DECIMAL}
</update>
<delete id="delete" parameterType="cn.kanmars.sn.entity.TblDemoInfo" >
delete from tbl_demo_info
where 1=1
<if test="demoId != null and demoId != '' ">
and demo_id = #{demoId,jdbcType=DECIMAL}
</if>
<if test="demoNm != null and demoNm != '' ">
and demo_nm = #{demoNm,jdbcType=VARCHAR}
</if>
<if test="demoMoney != null and demoMoney != '' ">
and demo_money = #{demoMoney,jdbcType=DECIMAL}
</if>
<if test="createDate != null and createDate != '' ">
and create_date = #{createDate,jdbcType=CHAR}
</if>
<if test="createTime != null and createTime != '' ">
and create_time = #{createTime,jdbcType=CHAR}
</if>
<if test="selectStatic != null and selectStatic != '' ">
and select_static = #{selectStatic,jdbcType=CHAR}
</if>
<if test="selectDynamic != null and selectDynamic != '' ">
and select_dynamic = #{selectDynamic,jdbcType=CHAR}
</if>
<if test="radioStatic != null and radioStatic != '' ">
and radio_static = #{radioStatic,jdbcType=CHAR}
</if>
<if test="radioDynamic != null and radioDynamic != '' ">
and radio_dynamic = #{radioDynamic,jdbcType=CHAR}
</if>
<if test="checkboxStatic != null and checkboxStatic != '' ">
and checkbox_static = #{checkboxStatic,jdbcType=VARCHAR}
</if>
<if test="checkboxDynamic != null and checkboxDynamic != '' ">
and checkbox_dynamic = #{checkboxDynamic,jdbcType=VARCHAR}
</if>
<if test="netAddress != null and netAddress != '' ">
and net_address = #{netAddress,jdbcType=VARCHAR}
</if>
<if test="countNumber != null and countNumber != '' ">
and count_number = #{countNumber,jdbcType=DECIMAL}
</if>
</delete>
<select id="queryOneMap" parameterType="java.util.HashMap" resultType="java.util.HashMap">
select
<include refid="Base_Column_List" />
from tbl_demo_info
where 1=1
<if test="demoId != null and demoId != '' ">
and demo_id = #{demoId,jdbcType=DECIMAL}
</if>
<if test="demoNm != null and demoNm != '' ">
and demo_nm = #{demoNm,jdbcType=VARCHAR}
</if>
<if test="demoMoney != null and demoMoney != '' ">
and demo_money = #{demoMoney,jdbcType=DECIMAL}
</if>
<if test="createDate != null and createDate != '' ">
and create_date = #{createDate,jdbcType=CHAR}
</if>
<if test="createTime != null and createTime != '' ">
and create_time = #{createTime,jdbcType=CHAR}
</if>
<if test="selectStatic != null and selectStatic != '' ">
and select_static = #{selectStatic,jdbcType=CHAR}
</if>
<if test="selectDynamic != null and selectDynamic != '' ">
and select_dynamic = #{selectDynamic,jdbcType=CHAR}
</if>
<if test="radioStatic != null and radioStatic != '' ">
and radio_static = #{radioStatic,jdbcType=CHAR}
</if>
<if test="radioDynamic != null and radioDynamic != '' ">
and radio_dynamic = #{radioDynamic,jdbcType=CHAR}
</if>
<if test="checkboxStatic != null and checkboxStatic != '' ">
and checkbox_static = #{checkboxStatic,jdbcType=VARCHAR}
</if>
<if test="checkboxDynamic != null and checkboxDynamic != '' ">
and checkbox_dynamic = #{checkboxDynamic,jdbcType=VARCHAR}
</if>
<if test="netAddress != null and netAddress != '' ">
and net_address = #{netAddress,jdbcType=VARCHAR}
</if>
<if test="countNumber != null and countNumber != '' ">
and count_number = #{countNumber,jdbcType=DECIMAL}
</if>
</select>
<select id="queryListMap" parameterType="java.util.HashMap" resultType="java.util.HashMap">
select
<include refid="Base_Column_List" />
from tbl_demo_info
where 1=1
<if test="demoId != null and demoId != '' ">
and demo_id = #{demoId,jdbcType=DECIMAL}
</if>
<if test="demoNm != null and demoNm != '' ">
and demo_nm = #{demoNm,jdbcType=VARCHAR}
</if>
<if test="demoMoney != null and demoMoney != '' ">
and demo_money = #{demoMoney,jdbcType=DECIMAL}
</if>
<if test="createDate != null and createDate != '' ">
and create_date = #{createDate,jdbcType=CHAR}
</if>
<if test="createTime != null and createTime != '' ">
and create_time = #{createTime,jdbcType=CHAR}
</if>
<if test="selectStatic != null and selectStatic != '' ">
and select_static = #{selectStatic,jdbcType=CHAR}
</if>
<if test="selectDynamic != null and selectDynamic != '' ">
and select_dynamic = #{selectDynamic,jdbcType=CHAR}
</if>
<if test="radioStatic != null and radioStatic != '' ">
and radio_static = #{radioStatic,jdbcType=CHAR}
</if>
<if test="radioDynamic != null and radioDynamic != '' ">
and radio_dynamic = #{radioDynamic,jdbcType=CHAR}
</if>
<if test="checkboxStatic != null and checkboxStatic != '' ">
and checkbox_static = #{checkboxStatic,jdbcType=VARCHAR}
</if>
<if test="checkboxDynamic != null and checkboxDynamic != '' ">
and checkbox_dynamic = #{checkboxDynamic,jdbcType=VARCHAR}
</if>
<if test="netAddress != null and netAddress != '' ">
and net_address = #{netAddress,jdbcType=VARCHAR}
</if>
<if test="countNumber != null and countNumber != '' ">
and count_number = #{countNumber,jdbcType=DECIMAL}
</if>
</select>
<update id="updateCAS" parameterType="java.util.HashMap" >
update tbl_demo_info
<set>
<if test="demoNm_new != null">
demo_nm = #{demoNm_new,jdbcType=VARCHAR} ,
</if>
<if test="demoMoney_new != null">
demo_money = #{demoMoney_new,jdbcType=DECIMAL} ,
</if>
<if test="createDate_new != null">
create_date = #{createDate_new,jdbcType=CHAR} ,
</if>
<if test="createTime_new != null">
create_time = #{createTime_new,jdbcType=CHAR} ,
</if>
<if test="selectStatic_new != null">
select_static = #{selectStatic_new,jdbcType=CHAR} ,
</if>
<if test="selectDynamic_new != null">
select_dynamic = #{selectDynamic_new,jdbcType=CHAR} ,
</if>
<if test="radioStatic_new != null">
radio_static = #{radioStatic_new,jdbcType=CHAR} ,
</if>
<if test="radioDynamic_new != null">
radio_dynamic = #{radioDynamic_new,jdbcType=CHAR} ,
</if>
<if test="checkboxStatic_new != null">
checkbox_static = #{checkboxStatic_new,jdbcType=VARCHAR} ,
</if>
<if test="checkboxDynamic_new != null">
checkbox_dynamic = #{checkboxDynamic_new,jdbcType=VARCHAR} ,
</if>
<if test="netAddress_new != null">
net_address = #{netAddress_new,jdbcType=VARCHAR} ,
</if>
<if test="countNumber_new != null">
count_number = #{countNumber_new,jdbcType=DECIMAL} ,
</if>
</set>
where 1=1
<if test="demoId != null ">
and demo_id = #{demoId,jdbcType=DECIMAL}
</if>
<if test="demoNm != null ">
and demo_nm = #{demoNm,jdbcType=VARCHAR}
</if>
<if test="demoMoney != null ">
and demo_money = #{demoMoney,jdbcType=DECIMAL}
</if>
<if test="createDate != null ">
and create_date = #{createDate,jdbcType=CHAR}
</if>
<if test="createTime != null ">
and create_time = #{createTime,jdbcType=CHAR}
</if>
<if test="selectStatic != null ">
and select_static = #{selectStatic,jdbcType=CHAR}
</if>
<if test="selectDynamic != null ">
and select_dynamic = #{selectDynamic,jdbcType=CHAR}
</if>
<if test="radioStatic != null ">
and radio_static = #{radioStatic,jdbcType=CHAR}
</if>
<if test="radioDynamic != null ">
and radio_dynamic = #{radioDynamic,jdbcType=CHAR}
</if>
<if test="checkboxStatic != null ">
and checkbox_static = #{checkboxStatic,jdbcType=VARCHAR}
</if>
<if test="checkboxDynamic != null ">
and checkbox_dynamic = #{checkboxDynamic,jdbcType=VARCHAR}
</if>
<if test="netAddress != null ">
and net_address = #{netAddress,jdbcType=VARCHAR}
</if>
<if test="countNumber != null ">
and count_number = #{countNumber,jdbcType=DECIMAL}
</if>
</update>
<select id="queryForPage" parameterType="java.util.HashMap" resultType="java.util.HashMap">
<if test="countFlag != null and countFlag == 'Y'.toString() ">
select count(1) as TOTALCOUNT from (
</if>
select
<include refid="Base_Column_List" />
from tbl_demo_info
where 1=1
<if test="demoId != null and demoId != '' ">
and demo_id = #{demoId,jdbcType=DECIMAL}
</if>
<if test="demoNm != null and demoNm != '' ">
and demo_nm like CONCAT(#{demoNm,jdbcType=VARCHAR},'%')
</if>
<if test="demoMoney != null and demoMoney != '' ">
and demo_money = #{demoMoney,jdbcType=DECIMAL}
</if>
<if test="createDate != null and createDate != '' ">
and create_date = #{createDate,jdbcType=CHAR}
</if>
<if test="createDate_start != null and createDate_start != '' ">
and create_date >= #{createDate_start,jdbcType=CHAR}
</if>
<if test="createDate_end != null and createDate_end != '' ">
and create_date <= #{createDate_end,jdbcType=CHAR}
</if>
<if test="createTime != null and createTime != '' ">
and create_time = #{createTime,jdbcType=CHAR}
</if>
<if test="selectStatic != null and selectStatic != '' ">
and select_static = #{selectStatic,jdbcType=CHAR}
</if>
<if test="selectDynamic != null and selectDynamic != '' ">
and select_dynamic = #{selectDynamic,jdbcType=CHAR}
</if>
<if test="radioStatic != null and radioStatic != '' ">
and radio_static = #{radioStatic,jdbcType=CHAR}
</if>
<if test="radioDynamic != null and radioDynamic != '' ">
and radio_dynamic = #{radioDynamic,jdbcType=CHAR}
</if>
<if test="checkboxStatic != null and checkboxStatic != '' ">
and checkbox_static = #{checkboxStatic,jdbcType=VARCHAR}
</if>
<if test="checkboxDynamic != null and checkboxDynamic != '' ">
and checkbox_dynamic = #{checkboxDynamic,jdbcType=VARCHAR}
</if>
<if test="netAddress != null and netAddress != '' ">
and net_address like CONCAT(#{netAddress,jdbcType=VARCHAR},'%')
</if>
<if test="countNumber != null and countNumber != '' ">
and count_number = #{countNumber,jdbcType=DECIMAL}
</if>
<if test="countFlag != null and countFlag == 'N'.toString() ">
limit #{limitStart,jdbcType=DECIMAL},#{limitSize,jdbcType=DECIMAL}
</if>
<if test="countFlag != null and countFlag == 'Y'.toString() ">
) as TMP_COUNT_TABLE
</if>
</select>
<!-- 如果是mysql数据库,需要在jdbcUrl中设置allowMultiQueries=true参数才可以使用 -->
<!-- 返回值为第一条更新语句的执行结果,并非所有批量更新的语句总和 -->
<!--
ORACLE的写法
<insert id="insertBatch" parameterType="java.util.List">
<foreach collection="list" item="item" index="index" open="begin" close="end;" separator=";">
insert into test (a,b,c) values (#{item.a},#{item.b},#{item.c})
</foreach>
</insert>
MYSQL的写法
<insert id="insertBatch" parameterType="java.util.List">
insert into test (a,b,c) values
<foreach collection="list" item="item" index="index" open="(" close=")" separator=",">
#{item.a},#{item.b},#{item.c}
</foreach>
</insert>
-->
<insert id="insertBatch" parameterType="java.util.List">
insert into tbl_demo_info (demo_id,demo_nm,demo_money,create_date,create_time,select_static,select_dynamic,radio_static,radio_dynamic,checkbox_static,checkbox_dynamic,net_address,count_number) values
<foreach collection="list" item="item" index="index" open="(" close=")" separator="),(">
#{item.demoId},#{item.demoNm},#{item.demoMoney},#{item.createDate},#{item.createTime},#{item.selectStatic},#{item.selectDynamic},#{item.radioStatic},#{item.radioDynamic},#{item.checkboxStatic},#{item.checkboxDynamic},#{item.netAddress},#{item.countNumber}
</foreach>
</insert>
<!-- 如果是mysql数据库,需要在jdbcUrl中设置allowMultiQueries=true参数才可以使用 -->
<!-- 返回值为第一条更新语句的执行结果,并非所有批量更新的语句总和 -->
<!--
ORACLE的写法
<update id="updateBatch" parameterType="java.util.List">
<foreach collection="list" item="item" index="index" open="begin" close="end;" separator=";">
update test
<set>
test=${item.test}+1
</set>
where id = ${item.id}
</foreach>
</update>
MYSQL的写法
<update id="updateBatch" parameterType="java.util.List">
<foreach collection="list" item="item" index="index" open="" close="" separator=";">
update test
<set>
test=${item.test}+1
</set>
where id = ${item.id}
</foreach>
</update>
-->
<update id="updateBatch" parameterType="java.util.List" >
<foreach collection="list" item="item" index="index" open="" close="" separator=";">
update tbl_demo_info
<set>
<if test="item.demoNm_new != null">
demo_nm = #{item.demoNm_new,jdbcType=VARCHAR} ,
</if>
<if test="item.demoMoney_new != null">
demo_money = #{item.demoMoney_new,jdbcType=DECIMAL} ,
</if>
<if test="item.createDate_new != null">
create_date = #{item.createDate_new,jdbcType=CHAR} ,
</if>
<if test="item.createTime_new != null">
create_time = #{item.createTime_new,jdbcType=CHAR} ,
</if>
<if test="item.selectStatic_new != null">
select_static = #{item.selectStatic_new,jdbcType=CHAR} ,
</if>
<if test="item.selectDynamic_new != null">
select_dynamic = #{item.selectDynamic_new,jdbcType=CHAR} ,
</if>
<if test="item.radioStatic_new != null">
radio_static = #{item.radioStatic_new,jdbcType=CHAR} ,
</if>
<if test="item.radioDynamic_new != null">
radio_dynamic = #{item.radioDynamic_new,jdbcType=CHAR} ,
</if>
<if test="item.checkboxStatic_new != null">
checkbox_static = #{item.checkboxStatic_new,jdbcType=VARCHAR} ,
</if>
<if test="item.checkboxDynamic_new != null">
checkbox_dynamic = #{item.checkboxDynamic_new,jdbcType=VARCHAR} ,
</if>
<if test="item.netAddress_new != null">
net_address = #{item.netAddress_new,jdbcType=VARCHAR} ,
</if>
<if test="item.countNumber_new != null">
count_number = #{item.countNumber_new,jdbcType=DECIMAL} ,
</if>
</set>
where 1=1
<if test="item.demoId != null ">
and demo_id = #{item.demoId,jdbcType=DECIMAL}
</if>
<if test="item.demoNm != null ">
and demo_nm = #{item.demoNm,jdbcType=VARCHAR}
</if>
<if test="item.demoMoney != null ">
and demo_money = #{item.demoMoney,jdbcType=DECIMAL}
</if>
<if test="item.createDate != null ">
and create_date = #{item.createDate,jdbcType=CHAR}
</if>
<if test="item.createTime != null ">
and create_time = #{item.createTime,jdbcType=CHAR}
</if>
<if test="item.selectStatic != null ">
and select_static = #{item.selectStatic,jdbcType=CHAR}
</if>
<if test="item.selectDynamic != null ">
and select_dynamic = #{item.selectDynamic,jdbcType=CHAR}
</if>
<if test="item.radioStatic != null ">
and radio_static = #{item.radioStatic,jdbcType=CHAR}
</if>
<if test="item.radioDynamic != null ">
and radio_dynamic = #{item.radioDynamic,jdbcType=CHAR}
</if>
<if test="item.checkboxStatic != null ">
and checkbox_static = #{item.checkboxStatic,jdbcType=VARCHAR}
</if>
<if test="item.checkboxDynamic != null ">
and checkbox_dynamic = #{item.checkboxDynamic,jdbcType=VARCHAR}
</if>
<if test="item.netAddress != null ">
and net_address = #{item.netAddress,jdbcType=VARCHAR}
</if>
<if test="item.countNumber != null ">
and count_number = #{item.countNumber,jdbcType=DECIMAL}
</if>
</foreach>
</update>
<select id="selectByPrimaryKey" resultMap="BaseResultMap">
select
<include refid="Base_Column_List" />
from tbl_demo_info
where 1=1
and demo_id = #{0}
</select>
</mapper>
按照以上模板,
增加字段,只需要在指定的几个位置修改即可:共计15个位置。且这几个位置都是可以通过正则表达式匹配出来的,可以“工具化”的在某个字段后新增字段。
再说一下我司的ORM层设计,mybatis有官方的mybatisgenerator,但是实际工作中不太好用,因此我们自己开发了一套mybatismapper生成工具,按照类似上面的xml的格式:
1、生成xml文件。
2、xml文件中固定有:select查询对象、selectList查询对象列表、insert插入对象、updateByPrimaryKey根据主键修改对象、delete删除对象、queryOneMap查询Map、queryListMap查询Map列表、updateCAS原子性更新、queryForPage分页查询、insertBatch批量插入、updateBatch批量更新、selectByPrimaryKey主键查询 共十二种标准SQL语句,覆盖了绝大多数场景。
3、xml中的和字段相关的行全部独立为一行,方便新增字段时全局替换,或者beyoundcompare对比,或者svnmerge,或者gitmerge
4、开发了工具包,针对xml文件,输入字段和自动类型,自动找到指定位置新增字段
5、开发了工具包,针对xml文件,实现了mysql->oracle->db2->sqlite->sqlserver五种数据库的相互转换
6、开发了工具包,针对整体的数据库层,设计了“页面元素描述语言”,根据DB中的comment,或者参数型的设计,全套自动生成:mapper.java,mapper.xml,entity,logic,controller,ftl,js,页面校验,页面排版,一整套都是自动生成的。
可以说,我司的后台开发人员,仅需安装powerdesign软件,然后用各种自动化工具,就能把整套系统完成到可上线状态。
而题主所说的“包含了手写的部分联合查询”,在这套方案里,不过是queryForPage分页查询的SQL语句不同罢了,我一般喜欢beyoundcompare一下,然后把旧的queryForPage放到新的xml上,提交SVN即可。
对于实体类,只能手动加。
xml可以用sql标签将字段写到一处。
看看是否可以添加到新表,写新的实体对象。不用在原表上添加。