单批次限制1000条数据。
Xml实现代码:
1.批量插入:
<insert id="savePpmxNEW" parameterClass="Java.util.List">
<![CDATA[
INSERT ALL
]]>
<iterate conjunction=" ">
into FPMX(ID,FPLXDM,FPDM,FPHM)
values(#csList[].ID#,#csList[].FPLXDM#,#csList[].FPDM#,#list[].FPHM)
</iterate>
<![CDATA[
select * from dual
]]>
</insert>
这种写法是Oracle的版本。主要是对应了Oracle批量插入数据库的操作,其中<iterate>为循环迭代,对传来的List数据进行迭代,有人说csList与Dao传来的LIst数据名称一致,但我发现,任意写好像也可以,不过大家也可以注意下这一点,避免出错。
2.批量更新:
<update id="updatePpmx" parameterClass="java.util.List">
begin
<iterate conjunction=";">
update PJ_ZZSP_FPMX set
FPLXDM=#list[].FPLXDM#,
FPDM=#list[].FPDM#,
FPHM=#list[].FPHM#
where ID=#list[].ID#
</iterate>
;end;
</update>
这种同样也是Oracle版本,这种写法也就不多解释了,update语句之间用“;”隔开,首尾加上begin和end。
3.批量删除
<!-- 批量删除一 -->
<delete id="delList" parameterClass="java.util.List">
delete from files
<iterate prepend="where" conjunction="OR">
fileId = #fileIds[]#
</iterate>
</delete>
<!-- 批量删除二 -->
<delete id="delList" parameterClass="java.util.List">
delete from files where fileId in
<iterate open="(" close=")" conjunction=",">
#fileIds[]#
</iterate>
</delete>
---------------------
java实现代码:
public class BaseDao extends SqlMapClientDaoSupport {
@Resource(name = "sqlMapClient")
private SqlMapClient sqlMapClient;
@PostConstruct
public void initSqlMapClient() {
super.setSqlMapClient(sqlMapClient);
}
}
BaseDao一般的写法如上,在继承basedao后,我们使用this.getSqlMapClientTemplate()方法调用现成的数据库操作方法(增删改查),但是这里面却没有批量操作方法
我们需要利用execute和SqlMapClientCallback
修改basedao为:
public class BaseDao<T extends Entity> extends SqlMapClientDaoSupport
Ibatis批量添加修改删除方法
public void batchUpdate( final String statementName, final List list) {
try {
if (list != null ) {
this .getSqlMapClientTemplate().execute( new SqlMapClientCallback() {
public Object doInSqlMapClient(SqlMapExecutor executor) throws SQLException {
executor.startBatch();
for ( int i = 0, n = list.size(); i < n; i++) {
executor.update(statementName, list.get(i));
}
executor.executeBatch();
return null ;
}
});
}
} catch (Exception e) {
if ( log .isDebugEnabled()) {
e.printStackTrace();
log .debug( "batchUpdate error: id [" + statementName + "], parameterObject [" + list + "]. Cause: " + e.getMessage());
}
}
}
public void batchInsert( final String statementName, final List list) {
try {
if (list != null ) {
this .getSqlMapClientTemplate().execute( new SqlMapClientCallback() {
public Object doInSqlMapClient(SqlMapExecutor executor) throws SQLException {
executor.startBatch();
for ( int i = 0, n = list.size(); i < n; i++) {
executor.insert(statementName, list.get(i));
}
executor.executeBatch();
return null ;
}
});
}
} catch (Exception e) {
if ( log .isDebugEnabled()) {
e.printStackTrace();
log .debug( "batchInsert error: id [" + statementName + "], parameterObject [" + list + "]. Cause: " + e.getMessage());
}
}
}
public void batchDelete( final String statementName, final List list) {
try {
if (list != null ) {
this .getSqlMapClientTemplate().execute( new SqlMapClientCallback() {
public Object doInSqlMapClient(SqlMapExecutor executor) throws SQLException {
executor.startBatch();
for ( int i = 0, n = list.size(); i < n; i++) {
executor.delete(statementName, list.get(i));
}
executor.executeBatch();
return null ;
}
});
}
} catch (Exception e) {
if ( log .isDebugEnabled()) {
e.printStackTrace();
log .debug( "batchDelete error: id [" + statementName + "], parameterObject [" + list + "]. Cause: " + e.getMessage());
}
}
}