批量抓取和更新

最新推荐文章于 2021-02-12 20:50:42 发布

iteye_10416

最新推荐文章于 2021-02-12 20:50:42 发布

阅读量139

点赞数

分类专栏： hibernate 文章标签： Hibernate SQL JDBC Access

hibernate 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

当需要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;