- 新增.ibatis的新增有专门的标签insert.新增的时候可以简化下代码量,values前面跟后面都可以用include的方式写,这样代码看起来更简洁明了。
<sql id="sql_columns"> <![CDATA[BAZ001, AAZ001,BKE137]]> </sql> <sql id="sql_values"> <![CDATA[#BAZ001#, #AAZ001#,#BKE137#]]> </sql> <insert id="insertInfo" parameterClass="实体类"> INSERT INTO tables (<include refid="sql_columns"/>) VALUES (<include refid="sql_values"/>) </insert>
- 批量新增,批量的时候传入的参数是List类型,然后iterate循环遍历的添加
<insert id="insertInfo" parameterClass="java.util.List"> INSERT INTO ka06 (BAZ001, AAZ001, AAZ164, AKA120, AKA121, AKA174, AKA122, AKA020, AKA021, AAE030, AAE031, AAE100, BAE011, BAE036, BAE034, AAE011, AAE036,AAB034,BAB301,AAA027) <iterate conjunction="union all"> <![CDATA[ SELECT #list[].BAZ001#, #list[].AAZ001#, #list[].AAZ164#, #list[].AKA120#, #list[].AKA121#, #list[].AKA174#, #list[].AKA122#, #list[].AKA020#, #list[].AKA021#, to_date(#list[].AAE030#,'yyyy-mm-dd'), to_date(#list[].AAE031#,'yyyy-mm-dd'), #list[].AAE100#, #list[].BAE011#, sysdate,#list[].BAE034#,#list[].AAE011#, sysdate,#list[].AAB034#,#list[].BAB301#,#list[].AAA027# FROM DUAL ]]> </iterate> </insert>
- 删除
<delete id="kf01.deleteInfo" parameterClass="java.lang.String"> <![CDATA[DELETE FROM KF01 WHERE BAZ001 = #BAZ001#]]> </delete>
- 批量删除
<delete id="medicare.miinterface.deletekf01Info" parameterClass="java.util.List"> DELETE FROM KF01 WHERE BAZ001 IN <iterate property="" open="(" close=")" conjunction=","> #list[].BAZ001# </iterate> </delete>
- 修改
<update id="kf01.updateInfo" parameterClass="returnKF01DTO"> UPDATE kf01 <dynamic prepend=" set "> <isNotEmpty prepend="," property="BKE301"> BKE301 = #BKE301# </isNotEmpty> </dynamic> WHERE BAZ001 = #BAZ001# </update>
- 批量修改
package com.paic.mhis.smcs.yits.common.util; import java.lang.reflect.Field; import java.lang.reflect.InvocationTargetException; import java.util.List; import com.paic.mhis.smcs.yits.common.exception.BusinessException; /** * * TODO(动态生成更新sql语句) * @author EX-LIAOKUNLIN001 * @createDate 2015-8-12 */ public class GenerateSqlMapUtils { /** * 需要转换成TO_DATE的字符串 */ private final static String DATE_TYPE = "AKC266,AKC221,AKC194,BAE036,AAE036,AAE030,AAE031,AAE127"; /** * 在实体类中需要过滤的字段 */ private final static String FILTER_FIELD = "BKZ001,serialVersionUID,flag,state,dateNum,kf01Flag,PERSIONS,AKB021,BKC042,AKB082,AKB069,BKB007," + "BKB060,BKB009,AKB100,AKB101,BKB020,BKB012,ZYCS,TOTALMON,JRTCFY,TCBX,ZF,GRFD,MZQFX,GRXJ,DATASTATE,BKA121,AKC050,AKC185,FHYBFY,MZTC,"+ "YJMZQFX,EJMZQFX,SJMZQFX,ISEDITTAG,BKA200"; /** * TODO(动态生成批量更新语句) * @author EX-LIAOKUNLIN001 * @param list * @param isNotNull 是否null也保存 * @param 数据库的主键 * @return String 返回sqlMap * @createDate 2015-8-11 */ @SuppressWarnings("rawtypes") public static String generateSqlMap(List list, String key, String keyExt, boolean isNotNull) throws BusinessException { StringBuilder sql = new StringBuilder(); Object columnValue = null; for (Object obj : list) { String entity = obj.getClass().getSimpleName().replace("DTO", ""); sql.append("UPDATE "+ entity +" SET "); try { // 获得KC22中的所有字段 Field[] fields = obj.getClass().getDeclaredFields(); for (Field field : fields) { // 获得字段名称 String columnName = field.getName(); if (FILTER_FIELD.indexOf(columnName) < 0) { columnValue = obj.getClass().getMethod("get" + columnName, new Class[] {}).invoke(obj, new Object[] {}); columnValue = columnValue == null ? "" : columnValue; if (isNull(columnValue, field.getType().getName(), isNotNull)) { if (DATE_TYPE.indexOf(columnName) >= 0) { sql.append(columnName + " = TO_DATE('"+ columnValue +"','yyyy-MM-dd HH24:mi:ss'),"); } else { sql.append(columnName + " = '" + columnValue + "',"); } } } } Object pk_key = obj.getClass().getMethod("get" + key, new Class[] {}).invoke(obj, new Object[] {}); sql = new StringBuilder(sql.substring(0, sql.lastIndexOf(","))); String keyValue = ""; Object pkExt_key = null; if (null != keyExt && !"".equals(keyExt)) { pkExt_key = obj.getClass().getMethod("get" + keyExt, new Class[] {}).invoke(obj, new Object[] {}); keyValue += " AND " + keyExt + " = '" + pkExt_key + "'"; } if ("KC22".equals(entity)) { Object bkz001 = obj.getClass().getMethod("getBKZ001", new Class[] {}).invoke(obj, new Object[] {}); sql.append(" WHERE BKZ001 = '"+ bkz001 +"' AND " + key + " = '" + pk_key + "'"+ keyValue +";\n"); } else { sql.append(" WHERE " + key + " = '" + pk_key + "'"+ keyValue +";\n"); } } catch (IllegalArgumentException e) { throw new BusinessException("反射方法错误:" + e.getMessage()); } catch (SecurityException e) { throw new BusinessException("反射方法错误:" + e.getMessage()); } catch (IllegalAccessException e) { throw new BusinessException("反射方法错误:" + e.getMessage()); } catch (InvocationTargetException e) { throw new BusinessException("反射方法错误:" + e.getMessage()); } catch (NoSuchMethodException e) { throw new BusinessException("反射方法错误:在类中没有找到该字段。" + e.getMessage()); } } return sql.toString(); } private static boolean isNull(Object param, Object fieldTypeName, boolean isNotNull) { if (!isNotNull) { if (param == null || "".equals(param)) { return false; } } return true; } }
GenerateSqlMapUtils.generateSqlMap(List,主键,null,false);
String updatSql = GenerateSqlMapUtils.generateSqlMap(ka06Dto_update, "BAZ001", null, false);ka06Dao.updateKA60(updatSql);
<!-- 批量更新操作 -->
<statement id="ka06.batchUpdateKA06" parameterClass="java.lang.String">
BEGIN
$updatSql$ END;
</statement> - 查询
第一种:简单的查询 <resultMap id="resultMap_kA06" class="ka06"> <result property="BAZ001" column="BAZ001" /> <result property="AAZ164" column="AAZ164" nullValue="0" /> <result property="AKA120" column="AKA120" /> <result property="AKA121" column="AKA121" /> <result property="AKA174" column="AKA174" /> <result property="AKA122" column="AKA122" /> </resultMap> <select id="ka06.findKa06ByMap" parameterClass="java.util.Map" resultMap="resultMap_kA06"> SELECT * FROM KA06 WHERE 1=1 <isNotEmpty prepend="and" property="BAE034"> BAE034=#BAE034# </isNotEmpty> <isNotEmpty prepend="and" property="AKA120"> AKA120=#AKA120# </isNotEmpty> </select> 第二种:把字段用sql标签抽出来,代码看起来会干净很多。 <sql id="sql_columns_kF01"> <![CDATA[ BAZ001, BKE301, BKE302, BKE303, BKE304, BKE305, BKE306, BKE307, BKE308, BKE309, BKE310, BKE311, BKE312, BKE313, TO_CHAR(AAE127,'yyyy-MM-dd HH24:mi:ss') AAE127, AAE100, AAE012, TO_CHAR(AAE034,'yyyy-MM-dd HH24:mi:ss') AAE034, AKC175, AAE189, BZE011, TO_CHAR(BZE036,'yyyy-MM-dd HH24:mi:ss') BZE036, BAE034, AAE011, TO_CHAR(AAE036,'yyyy-MM-dd HH24:mi:ss') AAE036, AAB034, AAA027, BAB301]]> </sql> <select id="kf01.findByKey" parameterClass="java.lang.String" resultMap="resultMap_kF01"> SELECT <include refid="sql_columns_kF01" /> FROM kf01 WHERE BAZ001 = #BAZ001# </select>
- 总结:批量的增删改效率更高一些。
转载于:https://www.cnblogs.com/hawell/p/5445147.html