一、MyBatis+Oracle带自增主键的批量添加
方法一:
<insert id="addBatch" parameterType="java.util.List">
BEGIN
<foreach collection="list" item="item" index="index" separator="">
INSERT INTO JOB_QUEUE( ID, JOB_ID, START_TIME, END_TIME, MODIFIED_USER, CREATED_TIME, UPDATED_TIME )
VALUES
(JOB_QUEUE_SEQ.NEXTVAL,#{item.jobId}, #{item.startTime}, #{item.endTime},
#{item.modifiedUser}, systimestamp, systimestamp );
</foreach>
COMMIT;
END;
</insert>
方法二:
<insert id="insertSalconfigAfter" parameterType="java.util.List">
<![CDATA[
INSERT INTO TB_DEPARTMENT (id,s1,s2,s3,s4,s5,s6)
]]>
select TB_MY_SEQUENCE.NEXTVAL,m.* from (
<foreach collection="list" item="item" index="index" separator="union all">
select
#{item.s1,jdbcType=VARCHAR},
#{item.s2,jdbcType=VARCHAR},
#{item.s3,jdbcType=VARCHAR},
#{item.s4,jdbcType=VARCHAR},
#{item.s5,jdbcType=VARCHAR},
#{item.s6,jdbcType=VARCHAR}
from dual
</foreach>
) m
</insert>
方法一存在批量添加大量数据时SQL插入语句执行不完,就没有提交事务,再次执行该方法,就会使第一次插入的数据全部回滚,导致第一次插入的数据存入不到数据库。
方法二没有加以验证,希望大家给予指正。
二、MyBatis+Oracle带自增主键的批量修改
最开始的时候,想着写一系列并列的更新语句就可以了
<update id="updateBatch" parameterType="java.util.List">
<foreach collection="list" item="item" index="index" separator=";"
open="" close="">
update REGION_CODE set
CODE=#{item.Code,jdbcType=VARCHAR},
NAME=#{item.Name,jdbcType=VARCHAR}
where ID = #{item.id,jdbcType=DECIMAL}
</foreach>
</update>
这样直接报错,因为Mybatis映射文件中的sql语句不允许 ; 符号。按照可行的case when处理方式,Mybatis映射文件书写方式如下:
<update id="updateBatch" parameterType="java.util.List">
update REGION_CODE set
CODE=
<foreach collection="list" item="item" index="index" separator=" " open="case ID" close="end">
when #{item.id,jdbcType=DECIMAL} then #{item.Code,jdbcType=VARCHAR}
</foreach>
,NAME=
<foreach collection="list" item="item" index="index" separator=" " open="case ID" close="end">
when #{item.id,jdbcType=DECIMAL} then #{item.Name,jdbcType=VARCHAR}
</foreach>
where ID in
<foreach collection="list" index="index" item="item" separator="," open="(" close=")">
#{item.id,jdbcType=DECIMAL}
</foreach>
</update>
至此,批量更新功能完成。
另外,注意一下oracle的SQL带in只能修改1000条一下,所以这个地方需要把大于1000条的批量修改进行分段截取拼装成in () or in ()的字符串最好。
- /**
- * 根据传入的List和参数,拼接in条件,防止in超过999条
- *
- * @param list
- * @param parameter
- * @return list.size()=n 'list1','list2',...,'list900') or parameter in ('list901','list902',...,'list1800') or parameter in ('list1801','list1802',...,'listn'
- * list.size()=0 ''
- */
- public static String getInParameter(List list, String parameter) {
- if (!list.isEmpty()) {
- List<String> setList = new ArrayList<String>(0);
- Set set = new HashSet();
- StringBuffer stringBuffer = new StringBuffer();
- for (int i = 1; i <= list.size(); i++) {
- set.add("'" + list.get(i - 1) + "'");
- if (i % 900 == 0) {//900为阈值
- setList.add(StringUtils.join(set.iterator(), ","));
- set.clear();
- }
- }
- if (!set.isEmpty()) {
- setList.add(StringUtils.join(set.iterator(), ","));
- }
- stringBuffer.append(setList.get(0));
- for (int j = 1; j < setList.size(); j++) {
- stringBuffer.append(") or " + parameter + " in (");
- stringBuffer.append(setList.get(j));
- }
- return stringBuffer.toString();
- } else {
- return "''";
- }
- }
1. 参数中直接加入%%
param.setUsername("%CD%");
param.setPassword("%11%");
<select id="selectPersons" resultType="person" parameterType="person">select id,sex,age,username,password from person where true <if test="username!=null"> AND username LIKE #{username}</if><if test="password!=null">AND password LIKE #{password}</if></select>
2. bind标签
<select id="selectPersons" resultType="person" parameterType="person"> <bind name="pattern" value="'%' + _parameter.username + '%'" /> select id,sex,age,username,password from person where username LIKE #{pattern}</select>
3. CONCAT
where username LIKE concat(concat('%',#{username}),'%')