Java代碼?
/*
*在項目中，遇到這樣的一個問題，當某一模塊需要插入，更新一批大的數據量時，
*此處大的含義是：更新到的數據記錄比較多
*用ibatis實現一條一條的插入，更新，如此循環下去。速度很慢
*分析：
* 這些操作有個共同點：
* PreparedStatement對象和已映射的語句完全一致（簡單點說：就是操作不斷重復）
* 由于事物是交給spring管理了，所以每做一次操作都會起一個事物，久而久之導致
* 性能問題。
*?
* batch批處理就適合那些查詢，插入，更新，刪除等大量重復的操作
*
* 存儲過程也比較適合這樣的場合，但要求很多?
* 批處理 是把 所有sql語句? 放入一塊內存中，然后一次性全部寫到服務器，這個節省了客* 服端到服務器的訪問時間
*/?
-- Create table?
create or replace table DEMO?
(?
? UUID????? NUMBER,?
? UUSER???? VARCHAR2(50),?
? UPASSWORD VARCHAR2(50)?
)?
?
--Insert a million records with the DEMO procedure?
create or replace procedure demo_p(total in integer) AS?
uuid number :=1;?
uuser varchar2(50) := 'user';?
upassword varchar2(50) :='password';?
begin?
loop???
? insert into demo(uuid,uuser,upassword) values(uuid,uuser||uuid,upassword||uuid);?
? uuid := uuid + 1;?
? exit when uuid >= total;?
end loop;?
end;?
?
---Clear table then call procedure to insert a million records??
truncate table demo;?
call demo_p(1000);?
select * from demo;?
?
--Java實現?
--1--ibatis普通實現插入一百條條數據--?
?
--2--ibatis批量插入一百萬條數據--?
?
--3--ibatis調用存儲過程插入一百萬條數據--?




1--構建環境 添加 classes12.jar ibatis-2.3.4.726.jar

2--寫好ibatis基本配置文件,公共資源文件

SqlMapConfig.properties
Java代碼?
# DB Config??
driver=oracle.jdbc.driver.OracleDriver?
url=jdbc:oracle:thin:@localhost:1521:orcl?
username=scott?
password=tiger?


SqlMapConfig.xml
Java代碼?
<?xml version="1.0" encoding="UTF-8"?>?
<!DOCTYPE sqlMapConfig?
PUBLIC "-//iBATIS.com//DTD SQL Map Config 2.0//EN"?
"http://www.ibatis.com/dtd/sql-map-config-2.dtd">?
<!-- Always ensure to use the correct XML header as above! -->?
<sqlMapConfig>?
??? <properties? resource = "com/oyp/sqlmap/SqlMapConfig.properties" />?
?????
??? <settings? cacheModelsEnabled="true" enhancementEnabled="true"?
??????? lazyLoadingEnabled="true" maxRequests="32" maxSessions="10"?
??????? maxTransactions="5" useStatementNamespaces="false" />?
?????
??? <transactionManager type = "JDBC">?
??????? <dataSource type? = "SIMPLE">?
??????????? <property name="JDBC.Driver" value="${driver}" />?
??????????? <property name="JDBC.ConnectionURL" value="${url}" />?
??????????? <property name="JDBC.Username" value="${username}" />?
??????????? <property name="JDBC.Password" value="${password}" />?
??????? </dataSource>?
??? </transactionManager>?
??? <sqlMap resource = "com/oyp/sqlmap/User.xml"/>?
?????
</sqlMapConfig>?



3--dto層,或者說是model層
User.java

Java代碼?
package com.oyp.model;?
?
import java.io.Serializable;?
/**
* @author oyp 2009-12-14
*/?
public class User implements Serializable {?
??? private static final long serialVersionUID = 2804032598967813289L;?
??? private int id;?
??? private String name;?
??? private String password;?
??? public String getPassword() {?
??????? return password;?
??? }?
??? public void setPassword(String password) {?
??????? this.password = password;?
??? }?
??? public int getId() {?
??????? return id;?
??? }?
??? public void setId(int id) {?
??????? this.id = id;?
??? }?
??? public String getName() {?
??????? return name;?
??? }?
??? public void setName(String name) {?
??????? this.name = name;?
??? }?
?????
}?




4--sqlmap映射文件

Java代碼?
<?xml version="1.0" encoding="UTF-8"?>?
?
<!DOCTYPE sqlMap??
??? PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN"??
??? "http://www.ibatis.com/dtd/sql-map-2.dtd">?
?????
??? <sqlMap namespace = "user" >?
??????? <typeAlias alias = "user" type = "com.oyp.model.User"/>?
??????? <insert id = "insert_user" parameterClass = "user" >?
????????? INSERT INTO DEMO (UUID, UUSER, UPASSWORD) VALUES ( #id#,#name#, #password# )?
??????? </insert>?
?????????
??????? <update id = "clear_user" >?
??????????????? truncate table demo?
??????? </update>?
???????????
????????? <parameterMap id="oypmap" class="java.util.Map">?
??????????? <parameter property="total" javaType="int" jdbcType="INTEGER" mode="IN" />?
???????? </parameterMap>?
???????? <procedure id="procedureoyp" parameterMap="oypmap"? >?
??????????? { call demo_p(?) }?
???????? </procedure>??
??? </sqlMap>?


5--DAO層
Java代碼?
package com.oyp.dao;?
?
import java.sql.SQLException;?
?
import com.ibatis.sqlmap.client.SqlMapClient;?
import com.oyp.model.User;?
?
public class UserDAO {?
?????
??? public static SqlMapClient sqlMap = AppSqlConfig.getSqlMap();?
?????
??? public static void insertUser(User user) throws SQLException {?
??????? sqlMap.insert("insert_user",user);?
??? }?
??? public static void clearTable()? {?
??????? try {?
??????????? sqlMap.update("clear_user");?
??????? }catch (SQLException e) {?
??????????? e.printStackTrace();?
??????? }?
??? }?
}?


5--獲取封裝數據庫信息的Ibatis對象
Java代碼?
package com.oyp.dao;?
?
import java.io.IOException;?
import java.io.Reader;?
?
import com.ibatis.common.resources.Resources;?
import com.ibatis.sqlmap.client.SqlMapClient;?
import com.ibatis.sqlmap.client.SqlMapClientBuilder;?
?
public class AppSqlConfig {?
??? private static final SqlMapClient sqlMap;?
??? static {?
??????? try {?
????????? Reader reader = Resources.getResourceAsReader("com/oyp/sqlmap/SqlMapConfig.xml");?
????????? sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);?
????????? reader.close();??
??????? } catch (IOException e) {?
????????? // Fail fast.?
????????? throw new RuntimeException("Something bad happened while building the SqlMapClient instance." + e, e);?
??????? }?
????? }?
??? public static SqlMapClient getSqlMap() {?
??????? return sqlMap;?
??? }?
?
?
}?



大公告成,等著測試下
休息................

開始測試
Java代碼?
/**
* 描述：一般處理
* @author oyp 2009-12-15
*/?
public class Main {?
??? public static int number =Util.number;?
??? public static void main (String[] args) throws SQLException {?
??????? //清空表?
??????? UserDAO.clearTable();?
??????? //一般處理?
??????? List<User> list = new ArrayList<User>();?
??????? for (int i = 0 ; i < number; i++) {?
??????????? User tempuser = new User();?
??????????? tempuser.setId(i);?
??????????? tempuser.setName("user"+i);?
??????????? tempuser.setPassword("password"+i);?
??????????? list.add(tempuser);?
??????? }?
??????? //開始?
??????? UserDAO.sqlMap.startTransaction();?
??????? long begin = System.currentTimeMillis();?
??????? for (int i = 0 ; i < number ; i++) {?
??????????? try {?
???????????????? UserDAO.insertUser(list.get(i));?
??????????? } catch (SQLException e) {?
??????????????? e.printStackTrace();?
??????????? }?
??????? }?
??????? UserDAO.sqlMap.commitTransaction();?
??????? UserDAO.sqlMap.endTransaction();?
??????? System.out.println(System.currentTimeMillis()-begin);?
??? }?
}?
?
?
/**
* 描述：batch處理
* @author oyp 2009-12-15
*/?
public class MainBatch {?
??? public static int number =Util.number;?
??? public static void main (String[] args) throws SQLException {?
??????? //清空表?
??????? UserDAO.clearTable();?
??????? //批量插入的對象?
??????? List<User> list = new ArrayList<User>();?
??????? for (int i = 0 ; i < number; i++) {?
??????????? User tempuser = new User();?
??????????? tempuser.setId(i);?
??????????? tempuser.setName("user"+i);?
??????????? tempuser.setPassword("password"+i);?
??????????? list.add(tempuser);?
??????? }?
??????? //批量處理開始?
??????? long begin = System.currentTimeMillis();?
??????? UserDAO.sqlMap.startTransaction();?
??????? for (int i = 0 ; i < number ; i ++) {?
??????????? if (i % 10000 == 0) {?
??????????????? UserDAO.sqlMap.startBatch();?
??????????? }?
??????????? UserDAO.insertUser(list.get(i));?
??????????? if ((i+1) % 10000 ==0) {??
??????????????? UserDAO.sqlMap.executeBatch();?
??????????? }?
??????? }?
??????? UserDAO.sqlMap.executeBatch();?
??????? UserDAO.sqlMap.commitTransaction();?
??????? UserDAO.sqlMap.endTransaction();?
??????? System.out.println(System.currentTimeMillis()-begin);?
??? }?
}?
?
/*
*描述：調用存儲過程
*@author oyp 2009-12-15
*/?
?
?
public class MainProcedure {?
??? public static int number =Util.number;?
??? public static void main (String[] args) throws SQLException {?
??????? //清空表?
??????? UserDAO.clearTable();?
??????? HashMap map = new HashMap();?
??????? map.put("total", number);?
??????? //調用存儲過程?
??????? long begin = System.currentTimeMillis();?
??????? UserDAO.sqlMap.startTransaction();?
??????? UserDAO.sqlMap.insert("procedureoyp",map);?
??????? UserDAO.sqlMap.commitTransaction();?
??????? UserDAO.sqlMap.endTransaction();?
??????? System.out.println(System.currentTimeMillis()-begin);?
??? }?
?????
}?





Java代碼?
1-- Util.number = 1000 ;?
?? 普通處理:593ms?
?? 批量處理:94ms?
?? 存儲過程處理:218ms?
?
2--Util.number = 10000;?
?? 普通處理:2734ms?
?? 批量處理:328ms?
?? 存儲過程處理:1172ms?
?
3--Util.number = 100000;?
?? 普通處理: 32641 ms? 休息....?
?? 批量處理: 1937ms?? 休息....?
?? 存儲過程處理:8453 ms?
?
4--Util.number = 300000;?
?? 批量處理: 3937 ms?? 休息....?
?
5--Util.number = 1000000;?
??? 批量處理: 電腦被我折騰著不行了?
??? Exception in thread "main" java.lang.OutOfMemoryError: Java heap????? space?
??? at java.util.Arrays.copyOf(Arrays.java:2760)?
??? at java.util.Arrays.copyOf(Arrays.java:2734)?
??? at java.util.ArrayList.ensureCapacity(ArrayList.java:167)?
??? at java.util.ArrayList.add(ArrayList.java:351)?
??? at MainBatch.main(MainBatch.java:24)?
? (注:因為要創建插入數據庫的對象所以內存耗盡, 公司配的電腦太差!)?
?
?? 存儲過程: 119906 ms??

?

這些純屬菜鳥級的試驗,真正的項目當中要考慮到各方面的原因,要比這復雜多了,所以哪里需要改善,或者有什么好的建議,盡管砸吧!~~(事務,數據庫)
???
ps: 這種情況,存儲過程的效率怎么比不上批處理? ??

ibatis 學習筆記 批量處理 存儲過程