一、insert单条实体动态xml
<!-- insert数据SQL-->
<insert id="cartype-insert"parameterClass="cartype">
insert into cartype(CarTypeId,CarTypeName,parking_id,Status,Update_time,State,SynID )
values
(
#CarTypeId#,#CarTypeName#,#parking_id#,#Status#,#Update_time#,#State#,#SynID#
);
</insert>
二、insert多条List
<!-- insertList<cartype>-->
<insert id="cartype-insert_list" parameterClass="ArrayList">
insert into user(name,age)
values
<iterate conjunction=",">
( #list[].name#,
#list[].age#
)
</iterate>
</insert>
三、批量插入(数据库里面没有要插入的数据则执行插入,如果存在要插入的数据则执行更新)
表需要创建唯一索引;
ON DUPLICATE KEY UPDATE,更新时变更数据
<insert id="abatorgenerated_insertByBatch" parameterClass="java.util.List" >
<!--
WARNING - This element is automatically generated by Abator for iBATIS, do not modify.
This element was generated on Fri May 18 15:34:06 CST 2018.
-->
<![CDATA[
insert into t_cpl_info (cpl_uuid, material_name, clip, remark1, remark2, valid, create_time,
create_user, update_time, update_user) values
]]>
<iterate conjunction ="," >
<![CDATA[
(
#list[].cplUuid#,
#list[].materialName#,
#list[].clip#,
#list[].remark1#,
#list[].remark2#,
#list[].valid#,
#list[].createTime#,
#list[].createUser#,
#list[].updateTime#,
#list[].updateUser#
)
]]>
</iterate>
ON DUPLICATE KEY UPDATE
clip = values(clip),update_time = values(create_time)
</insert>
java端实现
public void insertByBatch(List<CplInfo> list) {
//每批次插入数量
int batchCount = 1000;
//游标
int index = 0;
//批次
int batchNum = 1;
for(;;){
if(index+batchCount>=list.size()){
//一次可全部插入
getSqlMapClientTemplate().insert("ext_cpl_info.abatorgenerated_insertByBatch"
, list.subList(index,list.size()));
break;
}else {
//需要多次插入
List<CplInfo> cplInfo = list.subList(index, index + batchCount);
getSqlMapClientTemplate().insert("ext_cpl_info.abatorgenerated_insertByBatch"
, cplInfo);
index = index+batchCount;
}
batchNum++;
}
System.out.println("总共插入"+list.size()+"条,插入了"+batchNum+"批");
}
四、批量更新(其一)
xml端写一个update
<update id="abatorgenerated_updateByMaterialName" parameterClass="com.wd.aoc.launch.common.model.CplInfo" >
update t_cpl_info
set
clip = #clip:VARCHAR#,
update_time = #updateTime:DATE#
where material_name = #materialName:VARCHAR#
</update>
java端使用getSqlMapClient()
public void updateByBatch(List<CplInfo> list) throws Exception{
getSqlMapClient().startBatch();//开始批量操作
//遍历list
for (CplInfo cplInfo : list){
getSqlMapClient().update("ext_cpl_info.abatorgenerated_updateByMaterialName", cplInfo);//statement表示你调用的删除方法,在xml文件中定义的。
}
getSqlMapClient().executeBatch();//结束批量操作
System.out.println("总共更新"+list.size()+"条");
}