现在编写代码,总时刻要求自己,不只是实现功能,而且要质量更高。
最近使用ibatis框架,其灵活的sql编写,能够颗粒度的优化,蛮不错的。
另外使用批量insert时,找了好久,终于被我找到一种合适的批量添加操作。强调一下,本人使用的是oracle数据库。
传到DAO层的是一个List集合类
< insert id = "addCkbRedilutedInfo" parameterClass = "java.util.List" > insert all < iterate conjunction = " " > into ckb_dna_volume_info (dna_num,d_board_name,source_cryovial_id,date_processed,buffer_volume,buffer_reagent,create_user) values <![CDATA[ (#list[].gCryovialId:VARCHAR#, #list[].gBoxId:VARCHAR#, #list[].fCryovialId:VARCHAR#, #list[].operatdate:VARCHAR#, #list[].additionalBufferVolume:VARCHAR#, #list[].bufferReagent:VARCHAR#, #list[].operator:VARCHAR#) ]]> </ iterate > select * from dual </ insert >
另外,在网上有另外的两种批量insert的写法,我这边使用oracle+ibatis测试了,行不通,方法如下(提示错误信息:ORA-00936: missing expression):
< insert id = "addCkbRedilutedInfo" parameterClass = "java.util.List" > insert into ckb_dna_volume_info (dna_num,d_board_name,source_cryovial_id,date_processed,buffer_volume,buffer_reagent,create_user) values select dna_num,d_board_name,source_cryovial_id,date_processed,buffer_volume,buffer_reagent,create_user from ( < iterate conjunction = " union all " > select #list[].gCryovialId# as dna_num, #list[].gBoxId# as d_board_name, #list[].fCryovialId# as source_cryovial_id, #list[].operatdate# as date_processed, #list[].additionalBufferVolume# as buffer_volume, #list[].bufferReagent# as buffer_reagent, #list[].operator# as create_user from dual </ iterate > ) </ insert >
另外一种也不能够正常使用如下(提示错误信息:ORA-00933: SQL command not properly ended):
< insert id = "addCkbRedilutedInfo" parameterClass = "java.util.List" > insert into ckb_dna_volume_info (dna_num,d_board_name,source_cryovial_id,date_processed,buffer_volume,buffer_reagent,create_user) values < iterate conjunction = "," > <![CDATA[ (#list[].gCryovialId#, #list[].gBoxId#, #list[].fCryovialId#, #list[].operatdate#, #list[].additionalBufferVolume#, #list[].bufferReagent#, #list[].operator#) ]]> </ iterate > </ insert >
上述只是在sql里写批量,其实ibatis还可以在DAO层用java代码实现,写法还可以如下:
try { this .getSqlMapClient().startTransaction(); this .getSqlMapClient().startBatch(); for (CkbInfo info :infos){ this .getSqlMapClientTemplate().insert( "CKBDNAInfo.addCkbRedilutedInfo" , info);</span> } this .getSqlMapClient().executeBatch(); this .getSqlMapClient().commitTransaction(); } catch (SQLException e) { e.printStackTrace(); } finally { try { this .getSqlMapClient().endTransaction(); } catch (SQLException e) { e.printStackTrace(); } }
但是上述方法新开启了事务却导致了事务问题,所以还是用另外一种方式来处理,即基于回调方式的实现(当然,要注意你的数据库游标设置的最大值是多少,尽可能大些,不然会超出游标的最大设置值 ):
sqlMapClientTemplate.execute(new SqlMapClientCallback() { public Object doInSqlMapClient(SqlMapExecutor executor) throws SQLException { executor.startBatch(); for (CkbInfo info :infos) { executor.insert("CKBDNAInfo.addCkbRedilutedInfo" , info); } executor.executeBatch(); return null ; } });