Batch Update

[Adkins Kendall <Ke...@HCAhealthcare.com>]

Has any consideration been given to adding batch update capability?  For
instance, the following method could be added to the QueryRunner class:

/**
 * Execute a batch of SQL INSERT, UPDATE, or DELETE queries.
 * 
 * @param conn The connection to use to run the query.
 * @param sql The SQL to execute.
 * @param params An array of query replacement parameters.
 * @return The number of rows updated per statement.
 * @throws SQLException
 */
public int[] batchUpdate(Connection conn, String sql, Object[][] params)
	throws SQLException {
	PreparedStatement stmt = null;
	int[] rows = null;
	try {
		stmt = this.prepareStatement(conn, sql);
		
		for(int i = 0; i < params.length; i++) {
			this.fillStatement(stmt, params[i]);
			stmt.addBatch();
		}
		rows = stmt.executeBatch();
	} catch (SQLException e) {
		this.rethrow(e, sql, params);
	} finally {
		DbUtils.close(stmt);
	}
	return rows;
}


---------------------------------------------------------------------
To unsubscribe, e-mail: commons-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-dev-help@jakarta.apache.org

Re: Batch Update

[David Graham <gr...@yahoo.com>]

I haven't used batch updates so I'm assuming that the addBatch() call adds
a set of replacement parameters for one sql statement.  The same sql is
executed many times with different sets of values?

I'm fine with adding this to query runner but executeBatch() is marked as
@since 1.3 so we would need to bump DbUtils' minimum Java level.  I would
shorten the method name to batch() so we would have query(), update() and
batch() and of course some test cases are needed.

Please create a bugzilla enhancement ticket and attach patches to it.

David

--- Adkins Kendall <Ke...@HCAhealthcare.com> wrote:
> Has any consideration been given to adding batch update capability?  For
> instance, the following method could be added to the QueryRunner class:
> 
> /**
>  * Execute a batch of SQL INSERT, UPDATE, or DELETE queries.
>  * 
>  * @param conn The connection to use to run the query.
>  * @param sql The SQL to execute.
>  * @param params An array of query replacement parameters.
>  * @return The number of rows updated per statement.
>  * @throws SQLException
>  */
> public int[] batchUpdate(Connection conn, String sql, Object[][] params)
> 	throws SQLException {
> 	PreparedStatement stmt = null;
> 	int[] rows = null;
> 	try {
> 		stmt = this.prepareStatement(conn, sql);
> 		
> 		for(int i = 0; i < params.length; i++) {
> 			this.fillStatement(stmt, params[i]);
> 			stmt.addBatch();
> 		}
> 		rows = stmt.executeBatch();
> 	} catch (SQLException e) {
> 		this.rethrow(e, sql, params);
> 	} finally {
> 		DbUtils.close(stmt);
> 	}
> 	return rows;
> }
> 
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: commons-dev-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: commons-dev-help@jakarta.apache.org
> 


__________________________________
Do you Yahoo!?
Yahoo! Search - Find what you�re looking for faster
http://search.yahoo.com

---------------------------------------------------------------------
To unsubscribe, e-mail: commons-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-dev-help@jakarta.apache.org