Best practices: working with DB in Java

Question

Hello!

First of all, I'm new to Java.

I'm trying to figure out what would be a good/handy way to work with DB from Java. I'm using c3p0 for connection pooling. Hibernate or other ORM is not an option this time, we decided to stick with "plain SQL" for now.

Currently basic retrieval of data looks like this:

private int getUserID(int sessionID, String userIP) {
 int result = 0;
 Connection conn = null;
 PreparedStatement st = null;
 ResultSet rs = null;
 try {
  // Application.cpds is an instance of c3p0's ComboPooledDataSource
  conn = Application.cpds.getConnection();
  st = conn.prepareStatement("SELECT user_id, user_ip, is_timed_out FROM g_user.user_session WHERE id = ?");
  st.setInt(1, sessionID);
  rs = st.executeQuery();
  if ( rs.next() ) {
   if ( !rs.getBoolean("is_timed_out") && userIP.equals(rs.getString("user_ip")) ) {
    result = rs.getInt("user_id");
   }
  }
 }
 catch (SQLException e) {
  e.printStackTrace();
 }
 finally {
  if ( rs != null ) {
   try { rs.close(); } catch (SQLException e) { e.printStackTrace(); }
  }
  if ( st != null ) {
   try { st.close(); } catch (SQLException e) { e.printStackTrace(); }
  }
  if ( conn != null ) {
   try { conn.close(); } catch (SQLException e) { e.printStackTrace(); }
  }
 }
 return result;
}

The code looks very long for such a basic operation. Another problem is that most of the code would have to be repeated in many places (declaring Connection, PreparedStatement, ResultSet, closing them, catching exceptions). Though, this is what I see in most examples when googling.

In PHP I would create a wrapper class that would have method select() that accepts 2 arguments (string)sqlQuery and (array)parameters and would return simple array of data. Wrapper class would also have few more specific methods, like:

• selectValue() for single value (e.g., select count(*) from user)
• selectRow() for single row (e.g., select name, surname from user where id = :user_id)
• selectColumn for single column (e.g., select distinct remote_address from user)

Is anything like this practiced in Java? Or is there anything better / handier? Or should I use same style as in getUserID() example above? As I said, ORM is not an option this time.

Thanks in advance :)

---

edit: Currently DBConnection class is written. It gets connection from c3p0 connection pool in constructor. It has few public methods for working with DB: select() for tabular data, selectValue() for single value, selectRow() and selectColumn() for single row or column, as well as insert(), update(), delete() and ddl(). Methods accept String query, Object[] params arguments, with params being optional. insert(), update() and delete() return Integer which is result of PreparedStatement.executeUpdate(). select methods return different results:

• ArrayCollection<HashMap<String, Object>> select()
• Object selectValue()
• HashMap<String, Object> selectRow()
• ArrayCollection<Object> selectColumn()

The last problem is with compiler warnings - "warning: [unchecked] unchecked cast". This is because all methods call single private method that returns Object and cast its result to mentioned types. As I am new to Java, I'm also not sure if I have chosen appropriate types for selects. Other than that, everything seems to work as expected.

Answer 1

" Hibernate or other ORM is not an option this time, we decided to stick with "plain SQL" for now." Out of curiosity, what was the reason to stick with plain SQL? Looking at the example and question you mentioned first obvious answer would be use ORM and don't bother - in most cases standard ORM feature list would be sufficient.

Obviously there are plenty of reasons not to use ORM's, so I'm interested in yours?

Answer 2

If the an ORM is now option, you could still use Spring's JDBC helper classes: http://static.springsource.org/spring/docs/2.0.8/reference/jdbc.html

Or you could simply write some helper methods on your own. Maybe a DBUtil.close(conn, st, rs); would be nice.

And by the way, you really should use a logging framework instead of "e.printStackTrace()"

EDIT: One more thing: I think it's kind of hard to add ORM alter, when you have all the SQL already written in plain JDBC. You can't refactor that stuff, you have to throw it away and do it again.

EDIT: You don't have to close the resultSet if you are closing the statement anyway. The Java ResultSet API reads:

A ResultSet object is automatically closed when the Statement object that generated it is closed, re-executed, or used to retrieve the next result from a sequence of multiple results.

Beside that, C3P0 does resource management as well and you might to look that up too.

Answer 3

I think the level o granularity is always a developer decision. I mean, the great thing of having so many exceptions and validations is that you can capture the specific error and act according to it, however if what you need doesn't require that level of robustness and as you are showing it is just to print out the stack trace, I think a wrapper method can be useful in your case.

Answer 4

To avoid the repetition of code and perhaps makes things simpler. What you could do is create a Database class.

In the class you could then create general purpose methods for access to the database. For eg.

if the class is called DBManager.java then inside create methods

private connect()
public boolean update()
public ResultSet query()

Reason for connect method is obvious, you use it get your connection. Since its private you call it in the constructor of DBManager.

You then use your update() method to allow you to perform SQL inserts,update,delete and the like, basically any SQL operation that doesn't return any data except for maybe a status of its success is done with the update method.

Your query method is used when you want to do a select query. You can thn return the resultset and then iterate through the results in the calling method/class

How you handle exceptions is up to you. It may be nicer on you to handle exceptions in the DBManager class that way you won't have to handle them in the various classes that you make a query from.

So instead of

public ResultSet query() Throws SQLException{

you would use a try catch inside the query method like you did in your examples above. The obvious advantage of handling it in the dbmanager class is that you won't have to worry about it in all the other classes that make use of your sql connection.

Hope that's helpful

in response to your comment:

Its up to you what you return, the ResultSet being return is only an idea but maybe it'd be best to return a collection of some sort instead of an array, maybe? depending on what you need. The resultset needn't be closed.

public ResultSet query(String strSql) {

        try {
            Statement tmpStatement = connection.createStatement();
            ResultSet resultSet = tmpStatement.executeQuery(strSql);
            return resultSet;
        } catch (java.sql.SQLException ex) {
           //handle exception here
            return null;
        }
    }

your update can then look like so

public boolean updateSql(String strSQL) {
         try {
            Statement tmpStatement = connection.createStatement();
            tmpStatement.executeUpdate(strSQL);
           return true;
        } catch (java.sql.SQLException ex) {
            //handle exception
            return false;
        }
}

erm, you can then use your query method like so

 ResultSet r = query(sql);

        try {

            while (r.next()) {
                someVar[i] = r.getString("columnName");           
            }
        } catch (SomeException ex) {
           //handle exception etc
        }

But then again as you said instead of returning a result set you could change the query method to copy your results to an array or collection and then return the collection and close the statement with

tmpStatement.close();

But when a Statement object is closed, its current ResultSet object, if one exists, is also closed.(from api docs)

Its good practice to free up database resources as soon as so copying your result to a collection object and then closing your statement is probably best. Again its up to you.