Class design for DB code

Discussion in 'Java' started by Durango2008, Nov 18, 2007.

  1. Durango2008

    Durango2008 Guest

    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.
    Durango2008, Nov 18, 2007
    #1
    1. Advertising

  2. 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);
    }
    }
    }
    Andrey Ryabov, Nov 18, 2007
    #2
    1. Advertising

  3. Durango2008

    David Harper Guest

    Durango2008 wrote:
    > 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
    David Harper, Nov 18, 2007
    #3
  4. 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, Nov 18, 2007
    #4

  5. > 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);
    }
    }
    Andrey Ryabov, Nov 18, 2007
    #5
  6. Durango2008

    Doug Morse Guest

    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


    On Sun, 18 Nov 2007 05:27:23 GMT, Durango2008 <0m> wrote:
    > 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.
    > ...
    Doug Morse, Nov 20, 2007
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. ZackS
    Replies:
    5
    Views:
    6,774
    Just an Illusion
    Jul 9, 2004
  2. SpamProof
    Replies:
    3
    Views:
    634
    SpamProof
    Dec 1, 2003
  3. E11
    Replies:
    1
    Views:
    4,686
    Thomas Weidenfeller
    Oct 12, 2005
  4. John_Woo

    class design vs. db design

    John_Woo, Dec 19, 2006, in forum: Java
    Replies:
    2
    Views:
    314
    John_Woo
    Dec 19, 2006
  5. Bartholomew Simpson

    class design/ design pattern question

    Bartholomew Simpson, Jun 12, 2007, in forum: C++
    Replies:
    2
    Views:
    427
    Daniel T.
    Jun 12, 2007
Loading...

Share This Page