ibatis 学习笔记(一) 批量处理存储过程

最新推荐文章于 2021-10-31 14:55:11 发布

原创最新推荐文章于 2021-10-31 14:55:11 发布 · 217 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#iBATIS #Java #JDBC #SQL #Oracle

java 专栏收录该内容

49 篇文章

订阅专栏

本文对比了在不同数据量下使用ibatis进行普通处理、批量处理及存储过程的性能表现，展示了批量处理和存储过程如何提高大规模数据插入的速度。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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调用存储过程插入一百万条数据--

/*
*在项目中，遇到这样的一个问题，当某一模块需要插入，更新一批大的数据量时，
*此处大的含义是：更新到的数据记录比较多
*用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

# 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">
<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>

<?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;
}
}

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>

<?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();
}
}
}

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;
}
}

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);
}
}

/**
 * 描述：一般处理
 * @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)
(注:因为要创建插入数据库的对象所以内存耗尽, :twisted: 公司配的电脑太差!)
存储过程: 119906 ms

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)
  (注:因为要创建插入数据库的对象所以内存耗尽, :twisted: 公司配的电脑太差!)

   存储过程: 119906 ms

这些纯属菜鸟级的试验,真正的项目当中要考虑到各方面的原因,要比这复杂多了,所以哪里需要改善,或者有什么好的建议,尽管砸吧!~~(事务,数据库)

ps: 这种情况,存储过程的效率怎么比不上批处理 ??