生活随笔
收集整理的這篇文章主要介紹了
ibatis增删改、批量增删改以及查询
小編覺得挺不錯的,現(xiàn)在分享給大家,幫大家做個參考.
新增.ibatis的新增有專門的標(biāo)簽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="實(shí)體類">INSERT INTO tables (<include refid="sql_columns"/>) VALUES (<include refid="sql_values"/>)
</insert> ?
批量新增，批量的時候傳入的參數(shù)是List類型，然后iterate循環(huán)遍歷的添加 <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（動態(tài)生成更新sql語句）* @author EX-LIAOKUNLIN001* @createDate 2015-8-12*/
public class GenerateSqlMapUtils {/*** 需要轉(zhuǎn)換成TO_DATE的字符串*/private final static String DATE_TYPE = "AKC266,AKC221,AKC194,BAE036,AAE036,AAE030,AAE031,AAE127";/*** 在實(shí)體類中需要過濾的字段*/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(動態(tài)生成批量更新語句)* @author EX-LIAOKUNLIN001* @param list* @param isNotNull 是否null也保存* @param 數(shù)據(jù)庫的主鍵* @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;}} View Code 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 KA06WHERE 1=1<isNotEmpty prepend="and" property="BAE034">BAE034=#BAE034#</isNotEmpty><isNotEmpty prepend="and" property="AKA120">AKA120=#AKA120#</isNotEmpty>
</select>
第二種:把字段用sql標(biāo)簽抽出來，代碼看起來會干凈很多。
<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> ?
總結(jié):批量的增刪改效率更高一些。
轉(zhuǎn)載于:https://www.cnblogs.com/hawell/p/5445147.html
總結(jié)
以上是生活随笔為你收集整理的ibatis增删改、批量增删改以及查询的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。
