One idea in addition.
This pattern works in case you need to execute many SELECT queries too.
Say you need to execute three queries.
So you need to manage database connection manually or use three independant
database connections (even if they are pooled retreiving/releasing one is time consuming task).
If we will define executeActions method as:
==========================================================
/**
* Method retrieves database connection and then executes provided database
* actions. Actions execution results are placed into array and returned
* back.
*
* @param actions database actions array
* @return actions execution results as <code>Object[]</code>
* @throws SQLException
*/
public Object[] executeActions(DatabaseAction[] actions)
throws SQLException {
Connection conn = this.ds.getConnection();
int length = actions.length;
Object[] result = new Object[length];
try {
for (int i = 0; i < length; i++) {
result[i] = actions[i].execute(this, conn);
}
} finally {
DbUtils.close(conn);
}return result; } ========================================================== executeActions() will help us to avoid this situation.
Some other code from my QueryRunner version:
========================================================== /**
* Method prepares update database action
* @param sql sql statement to execute
* @return prepared update database action
*/
public DatabaseAction newUpdateAction(String sql) {
return new UpdateDatabaseAction(sql);
}
/**
* Method prepares update database action
* @param sql sql statement to execute
* @param parameter statement parameter
* @return prepared update database action
*/
public DatabaseAction newUpdateAction(String sql, Object parameter) {
return new UpdateDatabaseAction(sql, parameter);
}/**
* Method prepares update database action
* @param sql sql statement to execute
* @param parameters statement parameters
* @return prepared update database action
*/
public DatabaseAction newUpdateAction(String sql, Object[] parameters) {
return new UpdateDatabaseAction(sql, parameters);
} /**
* Method prepares query database action
* @param sql sql statement to execute
* @param rsh <code>ResultSetHandler</code> that will be used to process results
* @return prepared query database action
*/
public DatabaseAction newQueryAction(String sql, ResultSetHandler rsh) {
return new QueryDatabaseAction(sql, rsh);
}
/**
* Method prepares query database action
* @param sql sql statement to execute
* @param parameter statement parameter
* @param rsh <code>ResultSetHandler</code> that will be used to process results
* @return prepared query database action
*/
public DatabaseAction newQueryAction(String sql, Object parameter, ResultSetHandler rsh) {
return new QueryDatabaseAction(sql, parameter, rsh);
}
/**
* Method prepares query database action
* @param sql sql statement to execute
* @param parameters statement parameters
* @param rsh <code>ResultSetHandler</code> that will be used to process results
* @return prepared query database action
*/
public DatabaseAction newQueryAction(String sql, Object[] parameters, ResultSetHandler rsh) {
return new QueryDatabaseAction(sql, parameters, rsh);
}
==========================================================
And UpdateDatabaseAction, QueryDatabaseAction as examples:
========================================================== package com.mikkri.tarot.db;
import java.sql.Connection; import java.sql.SQLException;
import org.apache.commons.dbutils.QueryRunner;
/**
* Update database action.
*
* @author Mikhail Krivoshein <[EMAIL PROTECTED]>
* @since 0.1
*/
public class UpdateDatabaseAction implements DatabaseAction {
/** sql statement */
private final String sql;
/** parameters */
private final Object[] parameters;/**
* Class constructor.
* @param sql sql statement to execute
*/
public UpdateDatabaseAction(String sql) {
this.sql = sql;
this.parameters = null;
}
/**
* Class constructor.
* @param sql sql statement to execute
* @param parameter statement parameter
*/
public UpdateDatabaseAction(String sql, Object parameter) {
this.sql = sql;
this.parameters = new Object[] { parameter };
}
/**
* Class constructor.
* @param sql sql statement to execute
* @param parameters statement parameters
*/
public UpdateDatabaseAction(String sql, Object[] parameters) {
this.sql = sql;
this.parameters = parameters;
}
/**
* Update action code
*/
public Object execute(QueryRunner runner, Connection conn) throws SQLException {
return new Integer(runner.update(conn, sql, parameters));
}
}
========================================================== package com.mikkri.tarot.db;
import java.sql.Connection; import java.sql.SQLException;
import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.ResultSetHandler;
/**
* Query database action.
*
* @author Mikhail Krivoshein <[EMAIL PROTECTED]>
* @since 0.1
*/
public class QueryDatabaseAction implements DatabaseAction {
/** sql statement */
private final String sql;
/** parameters */
private final Object[] parameters;
/** <code>ResultSetHandler</code> that will be used to process results */
private final ResultSetHandler rsh;
/**
* Class constructor.
*
* @param sql sql statement to execute
* @param rsh <code>ResultSetHandler</code> that will be used to process results
*/
public QueryDatabaseAction(String sql, ResultSetHandler rsh) {
this.sql = sql;
this.parameters = null;
this.rsh = rsh;
}
/**
* Class constructor.
*
* @param sql sql statement to execute
* @param parameter statement parameter
* @param rsh <code>ResultSetHandler</code> that will be used to process results
*/
public QueryDatabaseAction(
String sql,
Object parameter,
ResultSetHandler rsh) {
this.sql = sql;
this.parameters = new Object[] { parameter };
this.rsh = rsh;
}
/**
* Class constructor.
*
* @param sql sql statement to execute
* @param parameters statement parameters
* @param rsh <code>ResultSetHandler</code> that will be used to process results
*/
public QueryDatabaseAction(
String sql,
Object[] parameters,
ResultSetHandler rsh) {
this.sql = sql;
this.parameters = parameters;
this.rsh = rsh;
}
/**
* Query action code
*/
public Object execute(QueryRunner runner, Connection conn)
throws SQLException {
return runner.query(conn, sql, parameters, rsh);
}
}
========================================================== package com.mikkri.tarot.db;
import java.sql.Connection; import java.sql.SQLException;
import org.apache.commons.dbutils.QueryRunner;
/**
* Batch update database action.
*
* @author Mikhail Krivoshein <[EMAIL PROTECTED]>
* @since 0.1
*/
public class BatchDatabaseAction implements DatabaseAction {
/** sql statement */
private final String sql;
/** parameters */
private final Object[][] parameters; /**
* Class constructor.
* @param sql sql statement to execute
* @param parameters statement parameters
*/
public BatchDatabaseAction(String sql, Object[][] parameters) {
this.sql = sql;
this.parameters = parameters;
}/**
* Batch update action code
* @see QueryRunner#batch(java.lang.String, java.lang.Object[][])
*/
public Object execute(QueryRunner runner, Connection conn) throws SQLException {
return runner.batch(conn, sql, parameters);
}
}
==========================================================
Best regards, Mikhail Krivoshein
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
