Class design for DB code

[Durango2008]

Hi this is really not a Java specific question but more in regards to OO
design in general. I have an application which has access to multiple
tables in a database and a multitude of different SQL statements for
manipulating the many tables and retrieving data from them. The part I am
struggling with is a good class design for the Java code which deals with
the DB.
I would like to achieve the following:
1. Separating DB code from the main application code.
2. Reducing code redundancy
3. Managing SQL statements in a good way.

For the most part I can deal with items 1 and 2, however the managing of SQL
statements is the part that gets me.
There will be a huge number of different SQL statements needed for this
project.
However to create a means of dynamically creating SQL statements can become
a bit over-complicated.
I also don't like to see a class filled with hard-coded SQL strings waiting
to be called, but it looks like it's the most practical means.

I am quite new to dealing with DB and although having used it before it was
rather sparce use of it.

If anyone knows my situation and can give me some advice I'd appreciate it.
If you need me to further clarify or give more info please let me know.

Thank you,
Peyman M.

 

[Andrey Ryabov]

Consider aproach like the following:


package ru.factor.toolkit.jdbc;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
* Contains convenient methos for working with JDBC. <u>This class it
not thread safe</u>
* because it keeps created connection in instance variable during
call of any method.
* It made for performance resasons. Parameters to stored procedures
and functions passing as
* <code>java.lang.Object[][]</code>, where each row represent one
paramter.
* Each paramter is array of three objects (<code>java.lang.Object[]</
code>):
* <ul><li>parameter's value <code>java.lang.Object</code>.
* <li>type of paramter {@link ParamType} (<code>ParamType.IN</code>
by default).
* <li>type of value of parameter {@link JDBCType}.
* </ul>
* The order is not relevant. Which of elements is required depends on
usage.
* The main rule is that {@link JDBCType}
* can be omitted when it can be deterimined by class of value.
Therefore if value is <code>null</code> or
* parameter is <code>OUT</code> you must specify {@link JDBCType}
explicitly.
* <p/>
* For example:
* <pre>
* new Object[][] {{null, JDBCType.INT}, {"string_value"}}
* <p/>
* Equals to:
* <p/>
* new Object[][] {{null, ParamType.IN, JDBCType.INT},
{"string_value". ParamType.IN, JDBCType.STRING}}
* <p/>
* And can be passed to function with signatue:
* <p/>
* function someFunction(i number, s varchar) return someType;
* </pre>
*
* @author Andrey Ryabov
*/
public class JDBCTemplates {
private static final Log log =
LogFactory.getLog(JDBCTemplates.class);
private static final int MAX_SQL_LENGTH = 100;
private DataSource dataSource;
private Connection connection;
private ConnectionListener listener;
private int counter;

private static final ConnectionListener NULL_LISTENER = new
ConnectionListener() {
public void afterCreate(Connection connect) {
// do nothing
}

public void beforeClose(Connection connect) {
// do nothing
}
};

/**
* Intitializes by <code>DataSource</code>
*
* @param dataSource
*/
public JDBCTemplates(DataSource dataSource) {
this(dataSource, NULL_LISTENER);
}

/**
* Intitializes by <code>DataSource</code>
*
* @param dataSource
* @param listener
*/
public JDBCTemplates(DataSource dataSource, ConnectionListener
listener) {
this.dataSource = dataSource;
this.listener = listener;
}

public static JDBCTemplates createNonThreadSafe(DataSource
dataSource) {
return new JDBCTemplates(dataSource);
}

public static JDBCTemplates createThreadSafe(final DataSource
dataSource) {
return new JDBCTemplates(dataSource) {
public <T, E extends Exception> T
execute(ConnectionHandler<T, E> handler) throws E {
Connection connection = null;
try {
connection = dataSource.getConnection();
return handler.onConnection(connection);
} catch (SQLException ex) {
log.error("Error due to access to database", ex);
throw new DataAccessException("Error due to access to
database: ", ex);
} finally {
closeNoEx(connection);
}
}
};
}


/**
* Prepares connection for working.
* Gets connection from DataSource, pass it to {@link
ConnectionHandler#onConnection(java.sql.Connection)}
* and close connection.
*
* @param handler Handler of <code>connection</code>
* @return result of processing if present
*/
public <T, E extends Exception> T execute(ConnectionHandler<T, E>
handler) throws E {
try {
if (counter++ == 0) {
connection = dataSource.getConnection();
listener.afterCreate(connection);
}
return handler.onConnection(connection);
} catch (SQLException ex) {
log.error("Error due to access to database", ex);
throw new DataAccessException("Error due to access to
database: ", ex);
} finally {
if (--counter == 0) {
try {
listener.beforeClose(connection);
} catch (SQLException e) {
log.error("Error in ConnectionListener", e);
}
closeNoEx(connection);
connection = null;
}
}
}

/**
* Manage <code>PreparedStatement</code> creation and handling.
Gets <code>PreparedStatement</code> via
* {@link
PreparedStatementCreator#createPreparedStatement(java.sql.Connection)},
* pass it to {@link
PreparedStatementHandler#onPreparedStatement(java.sql.PreparedStatement)},
close it.
*
* @param creator PreparedStatement abstract factory
* @param handler PreparedStatement handler.
* @return result of processing if present
*/
public <T, E extends Exception> T execute(final
PreparedStatementCreator creator,
final
PreparedStatementHandler<T, E> handler) throws E {
return execute(new ConnectionHandler<T, E>() {
public T onConnection(Connection connection) throws E,
SQLException {
PreparedStatement statement =
creator.createPreparedStatement(connection);
try {
return handler.onPreparedStatement(statement);
} finally {
closeNoEx(statement);
}
}
});
}

/**
* Creates initialized prepared statement. Creates
<code>PreparedStatement</code>, initialize it by parameters
* and pass it to {@link
PreparedStatementHandler#onPreparedStatement(java.sql.PreparedStatement)},
close it.
*
* @param sql SQL query string. It can be select, update,
delete.
* @param params array with statement parameters.
* @param handler PreparedStatement handler.
* @return result of processing if present.
*/
public <T, E extends Exception> T execute(final String sql, final
Object[][] params,
final
PreparedStatementHandler<T, E> handler) throws E {
return execute(new PreparedStatementCreator() {
public PreparedStatement
createPreparedStatement(Connection connection) throws SQLException {
PreparedStatement statement =
connection.prepareStatement(sql);
fillPreparedStatement(statement, params, 1);
return statement;
}
}, handler);
}

/**
* Creates uninitialized prepared statement.
*/
public <T, E extends Exception> T execute(final String sql,
final PreparedStatementHandler<T, E> handler) throws E {
return execute(new PreparedStatementCreator() {
public PreparedStatement
createPreparedStatement(Connection connection) throws SQLException {
PreparedStatement statement =
connection.prepareStatement(sql);
return statement;
}
}, handler);
}


/**
* Manage <code>ResultSet</code> creation and handling. Gets
<code>PreparedStatement</code> via
* {@link
PreparedStatementCreator#createPreparedStatement(java.sql.Connection)},
* invokes <code>PreparedStatement.executeQuery()</code> and pass
result set to
* {@link ResultSetHandler#onResultSet(java.sql.ResultSet)}, close
it.
*
* @param creator <code>PreparedStatement</code> abstract factory.
* @param handler <code>ResultSet</code> handler.
* @return result of processing if present.
*/
public <T, E extends Exception> T execute(PreparedStatementCreator
creator,
final
ResultSetHandler<T, E> handler)
throws E {
return execute(creator, new PreparedStatementHandler<T, E>() {
public T onPreparedStatement(PreparedStatement
preparedStatement) throws E, SQLException {
ResultSet resultSet =
preparedStatement.executeQuery();
try {
return handler.onResultSet(resultSet);
} finally {
closeNoEx(resultSet);
}
}
});
}

/**
* Manage <code>CallableStatement</code> creation and handling.
Creates <code>CallableStatement</code> by
* {@link
CallableStatementCreator#createCallableStatement(java.sql.Connection)},
* pass it to {@link
CallableStatementHandler#onCallableStatement(java.sql.CallableStatement)},
close it.
*
* @param creator <code>CallableStatement</code> abstract factory.
* @param handler <code>CallableStatement</code> handler
* @return result of processing if present.
*/
public <T, E extends Exception> T execute(final
CallableStatementCreator creator,
final
CallableStatementHandler<T, E> handler) throws E {
return execute(new ConnectionHandler<T, E>() {
public T onConnection(Connection connection) throws E,
SQLException {
CallableStatement statement =
creator.createCallableStatement(connection);
try {
return handler.onCallableStatement(statement);
} finally {
closeNoEx(statement);
}
}
});
}

/**
* Performs select query. Executes select statement and pass
<code>ResultSet</code> to
* {@link ResultSetHandler#onResultSet(java.sql.ResultSet)}
*
* @param querySQL select query.
* @param params query parametes.
* @param handler <code>ResulSet</code> handler.
* @return result of processing if present.
*/
public <T, E extends Exception> T executeSelect(final String
querySQL, final Object[][] params,
final
ResultSetHandler<T, E> handler) throws E {
return execute(new PreparedStatementCreator() {
public PreparedStatement
createPreparedStatement(Connection connection) throws SQLException {
PreparedStatement statement =
connection.prepareStatement(querySQL);
fillPreparedStatement(statement, params, 1);
return statement;
}
}, handler);
}

/**
* Performs select query. Executes select statement and pass
<code>ResultSet</code> to
* {@link ResultSetHandler#onResultSet(java.sql.ResultSet)}
*/
public <T, E extends Exception> T executeSelect(final String
querySQL, final int rsType,
final int rsConcurrency, final int
rsHoldability,
final Object[][] params,
final ResultSetHandler<T, E> handler) throws E {
return execute(new PreparedStatementCreator() {
public PreparedStatement
createPreparedStatement(Connection connection) throws SQLException {
PreparedStatement statement =
connection.prepareStatement(querySQL, rsType,
rsConcurrency, rsHoldability);
fillPreparedStatement(statement, params, 1);
return statement;
}
}, handler);
}

/**
* Shortcut for {@link #executeSelect(String, Object[][],
ResultSetHandler<T,E>)}
*/
public <T, E extends Exception> T executeSelect(String querySQL,
ResultSetHandler<T, E> handler,
Object... params)
throws E {
return executeSelect(querySQL, convertParams(params),
handler);
}

/**
* Shortcut for {@link #executeSelect(String, int, int, int,
Object[][], ResultSetHandler)}
*/
public <T, E extends Exception> T executeSelect(String querySQL,
int rsType, int rsConcurrency,
int rsHoldability, ResultSetHandler<T, E> handler, Object...
params) throws E {
return executeSelect(querySQL, rsType, rsConcurrency,
rsHoldability,
convertParams(params), handler);
}


/**
* Returns first column of queries result as List. You should use
appropriate
* {@link JDBCTypeConverter}
* to convert from JDBC type to type you needed.
*
* @param querySQL select query.
* @param params parameters of query.
* @return result of processing if present.
*/
public <T> List<T> selectForList(String querySQL, final
JDBCTypeConverter converter, Object[][] params) {
return executeSelect(querySQL, params, new
ResultSetHandler<List<T>, DataAccessException>() {
@SuppressWarnings("unchecked")
public List<T> onResultSet(ResultSet resultSet) throws
SQLException {
List<T> result = new ArrayList<T>();
while (resultSet.next()) {
result.add((T)
converter.convertFromJDBC(resultSet.getObject(1)));
}
return result;
}
});
}

/**
* Shortcut for {@link #selectForList(String, JDBCTypeConverter,
Object[][])}
*/
public <T> List<T> selectForList(String querySQL, Object...
params) {
return selectForList(querySQL,
JDBCTypeConverters.NO_CONVERSION, convertParams(params));
}

/**
* Returns first column of first row of result as Object. You
should use appropriate
* {@link JDBCTypeConverter}
* to convert from JDBC type to type you needed.
*
* @param querySQL select query.
* @param params query parameters.
* @return result of processing if present.
*/
public <T> T selectForObject(final String querySQL, final
JDBCTypeConverter converter, Object[][] params) {
return executeSelect(querySQL, params, new ResultSetHandler<T,
DataAccessException>() {
@SuppressWarnings("unchecked")
public T onResultSet(ResultSet resultSet) throws
SQLException {
if (!resultSet.next()) {
throw new NoDataFoundException("One row required
for query: " + querySQL);
}
return (T)
converter.convertFromJDBC(resultSet.getObject(1));
}
});
}

/**
* Shortcut for {@link #selectForObject(String, JDBCTypeConverter,
Object[][])}
*/
@SuppressWarnings("unchecked")
public <T> T selectForObject(String querySQL, Object... params) {
return (T) selectForObject(querySQL,
JDBCTypeConverters.NO_CONVERSION, convertParams(params));
}

/**
* Performs update query and returns number of rows updated.
*
* @param updateSQL update query.
* @param params query parameters.
* @return number of rows updated.
*/
public int executeUpdate(final String updateSQL, Object[][]
params) {
return execute(updateSQL, params, new
PreparedStatementHandler<Integer, DataAccessException>() {
public Integer onPreparedStatement(PreparedStatement
preparedStatement) throws SQLException {
return new Integer(preparedStatement.executeUpdate());
}
});
}

/**
* Shortcut for {@link #executeUpdate(String, Object[][])}
*/
public int executeUpdate(String updateSQL, Object... params) {
return executeUpdate(updateSQL, convertParams(params));
}

/**
* Performs insert query and returns object generated identity
object.
*
* @param insertSQL update query.
* @param params query parameters.
* @return generated identity object.
*/
public Object executeInsert(final String insertSQL, final Object[]
[] params) {
return execute(new PreparedStatementCreator() {
public PreparedStatement
createPreparedStatement(Connection connection) throws SQLException {
PreparedStatement statement =
connection.prepareStatement(insertSQL,
Statement.RETURN_GENERATED_KEYS);
fillPreparedStatement(statement, params, 1);
return statement;
}},
new PreparedStatementHandler<Object, DataAccessException>() {
public Object onPreparedStatement(PreparedStatement
preparedStatement) throws SQLException {
preparedStatement.executeUpdate();
ResultSet rSet =
preparedStatement.getGeneratedKeys();
try {
return rSet.next() ? rSet.getObject(1) : null;
} finally {
JDBCTemplates.closeNoEx(rSet);
}
}});
}

/**
* Shortcut for {@link #executeInsert(String, Object[][])}
*/
public Object executeInsert(String insertSQL, Object... params) {
return executeInsert(insertSQL, convertParams(params));
}

/**
* Executes SQL statement.
*
* @param statementSQL sql statemenet
* @return same value as {@link
java.sql.Statement#execute(String)}
* @see {@link java.sql.Statement#execute(String)}
*/
public boolean executeStatement(final String statementSQL, final
Object... params) {
return execute(new ConnectionHandler<Boolean,
DataAccessException>() {
public Boolean onConnection(Connection connection) throws
SQLException {
PreparedStatement statement =
connection.prepareStatement(statementSQL);
fillPreparedStatement(statement,
convertParams(params), 1);
return statement.execute();
}
});
}

/**
* Performs stored function call. Call stored function, gets
return value convert it by
* {@link JDBCTypeConverter}
* from {@link JDBCType}.
*
* @param functionSQL query.
* @param returnType type of expected result.
* @param params params of function.
* @return function's return value.
*/
public <T> T functionCall(final String functionSQL, final JDBCType
returnType, final Object[][] params) {
return execute(new CallableStatementCreator() {
public CallableStatement
createCallableStatement(Connection connect) throws SQLException {
CallableStatement call =
connect.prepareCall(functionSQL);
returnType.registerReturnParameter(call, 1);
fillPreparedStatement(call, params, 2);
return call;
}
}, new CallableStatementHandler<T, DataAccessException>() {
@SuppressWarnings("unchecked")
public T onCallableStatement(CallableStatement call)
throws SQLException {
call.execute();
return (T) returnType.getReturnParameter(call, 1);
}
});
}

/**
* Performs call of stored procedure. If parameter is marked as
<code>OUT</code> ou <code>IN_OUT</code>
* it's value will be set in result of stored procedure. If {@link
ParamType}
* is <code>OUT<code> or <code>IN_OUT</code> and its {@link
JDBCType} can't be
* determined, the type must be specified explicitly.
*
* @param procedureSQL query string.
* @param params parameters of procedure.
* @return same array as passed to method.
*/
public Object[][] procedureCall(final String procedureSQL, final
Object[][] params) {
return execute(new CallableStatementCreator() {
public CallableStatement
createCallableStatement(Connection connect) throws SQLException {
CallableStatement call =
connect.prepareCall(procedureSQL);
fillPreparedStatement(call, params, 1);
registerOutputParams(call, params);
return call;
}
}, new CallableStatementHandler<Object[][],
DataAccessException>() {
public Object[][] onCallableStatement(CallableStatement
call) throws SQLException {
call.execute();
fillOutputParams(call, params);
return params;
}
});
}

/**
* Deletes white spaces and abbreviate query. Used in proposes of
logging.
*/
public static String formatSQL(String originalSQL) {
return formatSQL(originalSQL, MAX_SQL_LENGTH);
}

/**
* Deletes white spaces and abbreviate query. Used in proposes of
logging.
*
* @param originalSQL original query text.
* @param maxSqlLength maximal length of formated query.
*/
public static String formatSQL(String originalSQL, int
maxSqlLength) {
String[] clauses = StringUtils.split(originalSQL);
StringBuffer result = new StringBuffer();
for (int i = 0; i < clauses.length; i++) {
if (i != 0) {
result.append(" ");
}
result.append(clauses);
}
return StringUtils.abbreviate(result.toString(),
maxSqlLength);
}

public static void fillOutputParams(CallableStatement call,
Object[][] params) throws SQLException {
for (int i = 0; i < params.length; i++) {
ParamType paramType = extractParamType(params);
if (paramType != ParamType.IN) {
JDBCType jdbcType = extractJDBCType(params);
params = new Object[]
{jdbcType.getReturnParameter(call, i + 1), jdbcType, paramType};
}
}
}

public static void fillPreparedStatement(PreparedStatement call,
final Object[][] params, int startWith)
throws SQLException {
for (int i = 0; i < params.length; i++) {
ParamType paramType = extractParamType(params);
if (paramType != ParamType.OUT) {
extractJDBCType(params).setParameter(call,
startWith + i, extractValue(params));
}
}
}

private void registerOutputParams(CallableStatement call, final
Object[][] params) throws SQLException {
for (int i = 0; i < params.length; i++) {
ParamType paramType = extractParamType(params);
if (paramType != ParamType.IN) {

extractJDBCType(params).registerReturnParameter(call, i + 1);
}
}
}

private static JDBCType extractJDBCType(Object[] param) {
for (Object p : param) {
if (p instanceof JDBCType) {
return (JDBCType) p;
}
}
Object paramValue = extractValue(param);
if (paramValue != null) {
return JDBCType.defineType(paramValue);
}
return JDBCType.NULL;
}

private static Object extractValue(Object[] param) {
for (Object p : param) {
if (!(p instanceof JDBCType) && !(p instanceof ParamType))
{
return p;
}
}
log.error("Value in param wasn't found: " + param);
throw new IllegalArgumentException("There is no value in
param: " + param);
}

private static ParamType extractParamType(Object[] param) {
for (Object p : param) {
if (p instanceof ParamType) {
return (ParamType) p;
}
if (p instanceof ResultSetHandler) {
return ParamType.OUT;
}
}
return ParamType.IN;
}

public static Object[][] convertParams(Object[] params) {
Object[][] result = new Object[params.length][];
for (int i = 0; i < params.length; i++) {
result = new Object[]{params};
}
return result;
}

public static void closeNoEx(Connection connection) {
try {
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
log.error("Error closing connection", e);
}
}

public static void closeNoEx(PreparedStatement statement) {
try {
if (statement != null) {
statement.close();
}
} catch (SQLException e) {
log.error("Error closing prepared statement", e);
}
}

public static void closeNoEx(ResultSet resultSet) {
try {
if (resultSet != null) {
resultSet.close();
}
} catch (SQLException e) {
log.error("Error closing result set", e);
}
}
}

 

[David Harper]

Hi this is really not a Java specific question but more in regards to OO
design in general. I have an application which has access to multiple
tables in a database and a multitude of different SQL statements for
manipulating the many tables and retrieving data from them. The part I am
struggling with is a good class design for the Java code which deals with
the DB.
I would like to achieve the following:
1. Separating DB code from the main application code.
2. Reducing code redundancy
3. Managing SQL statements in a good way.

For the most part I can deal with items 1 and 2, however the managing of SQL
statements is the part that gets me.
There will be a huge number of different SQL statements needed for this
project.
However to create a means of dynamically creating SQL statements can become
a bit over-complicated.

Prepared statements are one technique to avoid creating large numbers of
almost-identical SQL statements. You can use prepared statements if you
expect to issue many queries such as

SELECT name,address FROM CUSTOMER WHERE customer_id = 123456

but for different values of customer_id. Instead of creating a new SQL
qstatement for each customer_id, you use a pattern like this:

// Initialisation code, probably in a class constructor
String query =
"SELECT name,address FROM CUSTOMER WHERE customer_id =?";
PreparedStatement pstmt = conn.prepareStatement(query);

...

// Set parameter #1 in prepared statement
int customer_id = 123456;
pstmt.setInt(1, customer_id);

// Execute the query using the specified parameter
ResultSet rs = pstmt.executeQuery();

// Now use ResultSet as usual

I also don't like to see a class filled with hard-coded SQL strings waiting
to be called, but it looks like it's the most practical means.

The java.util.ResourceBundle class is one solution to this problem. You
put your SQL strings in a text file, separate from your source code,
with entries such as

sqlCustomerByID=SELECT name,address FROM CUSTOMER WHERE customer_id =?

and retrieve them with code like this:

ResourceBundle sqlResources =
ResourceBundle.getBundle("SQLQueries.txt");

String query = sqlResources.getString("sqlCustomerByID");

David Harper
Cambridge, England

 

[Andrey Ryabov]

The previous class is a part of the framework I've developed ages ago
being inspired by the way Spring improved working with jdbc.
It manages resources such as connections, resultsets, statements and
provide you with the ability to make most common queries easily for
example:

if you want to perform update your code might look like that:

BigInteger objectID = new BigInteger("123455");
jdbc.executeUpdate("update nc_objects set name = ? where object_id
= ?", "NewName", objectID);


selecting list of integers:

List childIDs = jdbc.selectForList("select object_id from
nc_objects where parent_id = ?",
JDBCTypeConverters.NUMBER_CONVERTER, new Object[][]
{{parentID}});


working with a result set:
Integer orderNumber = new Integer("...");
Integer result = jdbc.executeSelect("select * from nc_objects
where parent_id = ? and order_number = ?",
parentId, orderNumber, new
ResultSetHandler<Integer>(){
public Integer onResultSet(ResultSet resultSet) throws
SQLException {
// work with result set here ...
while (resultSet.next()) {//...
}
return 1;
}
});

 

[Andrey Ryabov]

The java.util.ResourceBundle class is one solution to this problem. You
put your SQL strings in a text file, separate from your source code,

I would recomend use more specific class to store SQL then
ResourceBundle
The following class allows:
1) to keep sql queries in well formed sql files (Tools like SQL
Developer will understend it).
2) modify queries without restarting application in debug mode just
edit .sql file.
3) build queries by template stored in .sql file.
4)...


Example of .sql file
-- Query: update_message_status
UPDATE MESSAGES SET STATUS = ?, STATUS_VERSION = ?, STATUS_TIME = ?
WHERE (MESSAGE_ID = ?) AND ((STATUS_VERSION + 1) = ?);


-- Query: find_obsolete_messages
SELECT * FROM MESSAGES WHERE
TS_DIFF_MSEC(CURRENT_TIMESTAMP, STATUS_TIME) > ?
AND STATUS <> {0} -- MessageStatus.STAT_DRAFT
AND STATUS <> {1}; -- MessageStatus.STAT_DELETED

-- Query: delete_obsolete_messages
DELETE FROM MESSAGES WHERE
TS_DIFF_MSEC(CURRENT_TIMESTAMP, STATUS_TIME) > ?
AND STATUS = {0}; -- MessageStatus.STAT_DELETED


Usage example within EJB container:

/**
* Bean implementation class for Enterprise Bean: MessageService
*/
public class MessageServiceBean extends SessionSupportBase {
private static final Log log =
LogFactory.getLog(MessageServiceBean.class);
private static final QueriesStorage queries =
QueriesStorage.newInstance(MessageServiceBean.class);


......

public Long changeStatus(final String messageId, final Long
newStatus) {
checkSecurity(messageId, "changeStatus");
try {
Message message = messagePersistence.findById(messageId);
Long oldStatus = message.getStatus();
if (log.isDebugEnabled()) {
log.debug("Changing message status from: " + oldStatus +
", to: " + newStatus);
}
final Date statusTime = new Date();
final Long version = message.getStatusVersion().longValue() + 1;
message.setStatus(newStatus);
message.setStatusVersion(version);
message.setStatusTime(statusTime);



jdbc.execute(new ConnectionHandler<Void,
DataAccessException>() {
public Void onConnection(Connection connection) throws
SQLException {
////////////////////////////// HERE !!!!

jdbc.executeUpdate(queries.get("update_status_history"), messageId);
int updated =
jdbc.executeUpdate(queries.get("update_message_status"), newStatus,
version, new
Timestamp(statusTime.getTime()), messageId, version);
if (updated != 0) {
throw new OptimisticLockException("Can't change
status: " + messageId);
}
return null;
}
});
return oldStatus;
} catch (RecordNotFoundException e) {
throw new IllegalArgumentException("Failed to change
message status msgId: " + messageId, e);
}
}
}



The class itself:

package ru.factor.toolkit.queries;

import java.io.*;
import java.text.MessageFormat;
import java.util.LinkedHashMap;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

public class QueriesStorage {
public static final Log log =
LogFactory.getLog(QueriesStorage.class);

private Pattern pattern = Pattern.compile("\\p{Blank}*-+\\p{Blank}*"
+
"Query:\\p{Blank}*(\\w+)\\s+([^;]+);");
private Map<String, String> queries = new LinkedHashMap<String,
String>();

public QueriesStorage() {
}

public QueriesStorage(Class resource) throws IOException {
this(resource.getName().replace('.', '/') + ".sql");
}

public QueriesStorage(String resource) throws IOException {
InputStream input = Thread.currentThread().getContextClassLoader()
.getResourceAsStream(resource);
if (input == null) {
throw new IOException("Resource: '" + resource + "' has not been
found");
}
try {
init(input);
if (log.isDebugEnabled()) {
StringBuilder message = new StringBuilder("\nQueriesStorage
loaded: ")
.append(resource).append("\n\t");
for (String query : queries.keySet()) {
message.append(query).append(", ");
}
log.debug(message.toString());
}
} finally {
input.close();
}
}

public static final QueriesStorage newInstance(Class clazz) {
try {
return new QueriesStorage(clazz);
} catch (IOException e) {
log.error("Error while instantiating queries for class: " + clazz);
throw new RuntimeException(e);
}
}

public void init(InputStream input) throws IOException {
InputStreamReader reader = new InputStreamReader(input, "UTF-8");
char[] buffer = new char[1024 * 4];
StringBuilder queries = new StringBuilder();
while (reader.read(buffer) != -1) {
queries.append(buffer);
}
this.queries.clear();
init(queries);
}

private void init(CharSequence queries) {
Matcher matcher = pattern.matcher(queries);
while (matcher.find()) {
this.queries.put(matcher.group(1), matcher.group(2));
}
}

public String get(String name) {
String sql = queries.get(name);
if (sql == null) {
throw new IllegalArgumentException("Query not found: " + name);
}
return sql;
}

public String get(String name, Object... params) {
String sql = queries.get(name);
if (sql == null) {
throw new IllegalArgumentException("Query not found: " + name);
}
return MessageFormat.format(sql, params);
}
}

 

[Doug Morse]

hi all,

wouldn't an object-relational mapping approach, such as hibernate, toplink,
etc., provide for an even greater degree of transparency, a lot less code, and
practically no SQL (because the SQL is auto-generated)? in addition, to
minimize db management code even further, i would think using aspects (e.g.,
aspectj) to treat persistence as a cross-cutting concern would further
reduce the amount of code to be written, simplify that which does have to be
written, and centralize all the persistence code so that it's easier to manage
and guaranteed to be applied consistently.

just my $0.02. i'd be curious to hear arguments for or against this.

cheers,
doug