Java代码 /* *在项目中,遇到这样的一个问题,当某一模块需要插入,更新一批大的数据量时, *此处大的含义是:更新到的数据记录比较多 *用ibatis实现一条一条的插入,更新,如此循环下去。速度很慢 *分析: *这些操作有个共同点: *PreparedStatement对象和已映射的语
Java代码
- /*
- *在项目中,遇到这样的一个问题,当某一模块需要插入,更新一批大的数据量时,
- *此处大的含义是:更新到的数据记录比较多
- *用ibatis实现一条一条的插入,更新,如此循环下去。速度很慢
- *分析:
- * 这些操作有个共同点:
- * PreparedStatement 对象和已映射的语句完全一致(简单点说:就是操作不断重复)
- * 由于事物是交给 spring管理了,所以每做一次操作都会起一个事物,久而久之导致
- * 性能问题。
- *
- * batch批处理就适合那些查询,插入,更新,删除等大量重复的操作
- *
- * 存储过程也比较适合这样的场合,但要求很多
- */
- -- 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);
- }
- }