I've been designing some source code because we had in our project much code being repeated when making SQL queries.
So I did this code below which seems to work fine trying something similar to Command pattern. It receives just a SQL query in a String and some parameters (if needed) to set on the statement. So you can use this code as an anonymous class an execute the query only defining what to do with the output of the query.
My problem is that I wanted to design this to make mandatory to define and write the method getResult in the anonymous child class, but I can't think any way of doing it without making an abstract method and class.
If QueryCommand becomes abstract I should make another class to be able to instantiate, which can't be abstract as well. Is there any other way to make compulsory the overriding in child class? I'm looking for the smartest simplest way to achieve it.
Didn't know how to search for a similar pattern or solution.
Thanks in advance.
Source code:
import java.sql.Connection;
import java.sql.SQLException;
public interface IQueryCommand<T> {
T executeQuery(Connection conn, String query, Object... args) throws SQLException;
}
import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.MessageFormat;
import org.apache.log4j.Logger;
public class QueryCommand<T> implements IQueryCommand<T> {
private Logger LOGGER = Logger.getLogger(this.getClass());
/** The constant ERROR_CLOSING_RESULT_SET */
protected static final String ERROR_CLOSING_RESULT_SET = "Error when closing ResultSet";
/** The Constant ERROR_CLOSING_PREPARED_STATEMENT. */
protected static final String ERROR_CLOSING_PREPARED_STATEMENT = "Error when closing PreparedStatement";
// FIXME: I want this method to be mandatory to be defined in the anonymous child class
protected T getResult(ResultSet rs) throws SQLException {
return null;
};
public T executeQuery(Connection conn, String sqlQuery, Object... args) throws SQLException {
if (LOGGER.isDebugEnabled()) {
LOGGER.debug(sqlQuery);
}
PreparedStatement ps = null;
ps = conn.prepareStatement(sqlQuery);
return executeQuery(conn, ps, args);
}
public T executeQuery(Connection conn, PreparedStatement ps, Object... args) throws SQLException {
ResultSet rs = null;
try {
if(args != null && args.length > 0) {
for(int i=0; i< args.length; i++) {
setArg(ps, i+1, args[i]);
}
}
rs = ps.executeQuery();
T result = getResult(rs); // Method defined in child class
return result;
} catch (SQLException e) {
throw e;
} finally {
if(rs != null) {
try {
rs.close();
} catch (final SQLException e) {
LOGGER.error(ERROR_CLOSING_RESULT_SET, e);
}
}
if (ps != null) {
try {
ps.close();
} catch (final Exception e) {
if(ps instanceof CallableStatement) {
LOGGER.error("Error when closing CallableStatement", e);
} else if(ps instanceof PreparedStatement) {
LOGGER.error(ERROR_CLOSING_PREPARED_STATEMENT, e);
}
}
}
}
}
/**
* Sets a value on the PreparedStatemente with a method dependending on dataType
*
* @param ps the preparedStatement
* @param idx the index on which the value is set
* @param value the value to set
* @throws SQLException if an error is detected
*/
private void setArg(PreparedStatement ps, int idx, Object value) throws SQLException {
// Implementation not relevant...
}
}
Example of how to use this:
sqlQuery = " SELECT X FROM Y WHERE countryId = ? and languageId = ?";
return new QueryCommand<String>() {
// This method should be REQUIRED when compiling
@Override
protected String getResult(ResultSet rs) throws SQLException {
String result = "";
while (rs.next()) {
result = rs.getString("DESCRIPTION");
}
return result;
};
}.executeQuery(getDB2Connection(), sqlQuery.toString(), new Object[] { countryIdParameter, languageIdParameter});
Aucun commentaire:
Enregistrer un commentaire