批量抓取和更新

当需要select、update、delete大量数据的时候,将操作一次执行将有效提高性能。可以想象成设置了一个缓冲区, 只有当缓存满了的时候才一次性执行,所以缓存越大,执行次数越少,速度越快。这个过程姑且叫作批处理。

想象一下批处理如何实现的,假设对于select、update、delete, 似乎都可以将同一个表的拼接where id=1 or id=2 ...来做。但insert呢?我就不知道了

对于hibernate,可以查看hibernate3的文档,关于批量处理有如下配置:
1)
hibernate.max_fetch_depth
为单向关联(一对一, 多对一)的外连接抓取(outer join fetch)树设置最大深度. 值为0意味着将关闭默认的外连接抓取. 取值 建议在0到3之间取值
hibernate.default_batch_fetch_size
为Hibernate关联的批量抓取设置默认数量. 取值 建议的取值为4, 8, 和16

2)
hibernate.jdbc.fetch_size
非零值,指定JDBC抓取数量的大小 (调用Statement.setFetchSize()).
hibernate.jdbc.batch_size
非零值,允许Hibernate使用JDBC2的批量更新.
取值 建议取5到30之间的值

第一组是对于集合映射情况,但只是fetch策略,当抓取多个对象时,它们的集合不需要一个一个抓取出来,而是通过...or...一次能够抓取几个集合,可以看出这也是一种对n+1问题的缓解,当然感觉不如直接join来得简单和快捷。
第二组配置包含了jdbc字样,因此是只需jdbc支持的,并非hibernate。应该受限于数据库的支持以及驱动支持。却不仅有fetch,还有batch,看来hibernate的batch update实际上是基于jdbc已实现的,并非原创啊(不看不知道,一看吓一跳)

下面具体看看第二组,jdbc的fetch和batch:
Statement.setFetchSize()的javadoc

/**
* Gives the JDBC driver a hint as to the number of rows that should
* be fetched from the database when more rows are needed. The number
* of rows specified affects only result sets created using this
* statement. If the value specified is zero, then the hint is ignored.
* The default value is zero.
*
* @param rows the number of rows to fetch
* @exception SQLException if a database access error occurs, or the
* condition 0 <= <code>rows</code> <= <code>this.getMaxRows()</code>
* is not satisfied.
* @since 1.2
* @see #getFetchSize
*/

大意应该是当从数据库返回大量数据时可以设置一个缓冲记录数,而这个设定只对于使用该statement并返回resultset时会生效。

Statement.executeBatch()的说明:

**
* Submits a batch of commands to the database for execution and
* if all commands execute successfully, returns an array of update counts.
* The <code>int</code> elements of the array that is returned are ordered
* to correspond to the commands in the batch, which are ordered
* according to the order in which they were added to the batch.
* The elements in the array returned by the method <code>executeBatch</code>
* may be one of the following:
* <OL>
* <LI>A number greater than or equal to zero -- indicates that the
* command was processed successfully and is an update count giving the
* number of rows in the database that were affected by the command's
* execution
* <LI>A value of <code>SUCCESS_NO_INFO</code> -- indicates that the command was
* processed successfully but that the number of rows affected is
* unknown
* <P>
* If one of the commands in a batch update fails to execute properly,
* this method throws a <code>BatchUpdateException</code>, and a JDBC
* driver may or may not continue to process the remaining commands in
* the batch. However, the driver's behavior must be consistent with a
* particular DBMS, either always continuing to process commands or never
* continuing to process commands. If the driver continues processing
* after a failure, the array returned by the method
* <code>BatchUpdateException.getUpdateCounts</code>
* will contain as many elements as there are commands in the batch, and
* at least one of the elements will be the following:
* <P>
* <LI>A value of <code>EXECUTE_FAILED</code> -- indicates that the command failed
* to execute successfully and occurs only if a driver continues to
* process commands after a command fails
* </OL>
* <P>
* A driver is not required to implement this method.
* The possible implementations and return values have been modified in
* the Java 2 SDK, Standard Edition, version 1.3 to
* accommodate the option of continuing to proccess commands in a batch
* update after a <code>BatchUpdateException</code> obejct has been thrown.
*
* @return an array of update counts containing one element for each
* command in the batch. The elements of the array are ordered according
* to the order in which commands were added to the batch.
* @exception SQLException if a database access error occurs or the
* driver does not support batch statements. Throws {@link BatchUpdateException}
* (a subclass of <code>SQLException</code>) if one of the commands sent to the
* database fails to execute properly or attempts to return a result set.
* @since 1.3
*/

通过Statement.addBatch(String sql)来添加sql,组成了batch。batch还是jdbc2.0产生的新事物。
既然addBatch是添加的语句,没有对insert做特别声明,看来应该也是支持的。
如何实现insert的操作简化的呢?[color=red]这个目前还是不晓得,以后再研究罢。[/color]
batch参考代码:

boolean isSupportBatchUpdates(Connection conn)
DatabaseMetaData dbm = con.getMetaData();
if(dbm.supportBatchUpdates())
return true;
//notice: catch SQLException,AbstractMethodError
return false;

int[] batchUpdate(String[] sql)
//make sure sql is not null!!!
int res = new int[sql.length];
if(isSupportBatchUpdates(conn)){
for(int i = 0;i<sql.length;i++)
stmt.addBatch(sql[i]);
res = stmt.executeBatch();
} else {
for(int i = 0 ;i<sql.length;i++){
if(!stmt.execute(sql[i])) //非select,DML/DDL
res[i] = stmt.getUpdateCount();
else throw new ....
}
}
return res;
阅读更多
个人分类: hibernate
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭