To begin with, I am a new comer to Java.

I am trying to puzzle out what will be a good/handy way to utilize DB from Java. I am using c3p0 for connection pooling. Hibernate or any other ORM isn't a choice this time around, we made the decision to stay with "plain SQL" for the time being.

Presently fundamental retrieval of information appears 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 lengthy for this type of fundamental operation. One other issue is the fact that the majority of the code would need to be repeated in lots of places (proclaiming Connection, PreparedStatement, ResultSet, closing them, catching exceptions). Though, this is exactly what I see in many good examples when searching.

In PHP I'd produce a wrapper class that will have method choose() that accepts 2 arguments (string)sqlQuery and (array)parameters and would return simple variety of data. Wrapper class would also provide couple of more specific techniques, 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 such as this practiced in Java? Or perhaps is there anything better / handier? Or must i use same style as with getUserID() example above? When I stated, ORM isn't a choice this time around.

Thanks ahead of time :)


edit: Presently DBConnection class is written. It will get connection from c3p0 connection pool in constructor. It's couple of public techniques for dealing with DB: select() for tabular data, selectValue() for single value, selectRow() and selectColumn() for single row or column, in addition to insert(), update(), delete() and ddl(). Techniques accept String query, Object[] params arguments, with params being optional. insert(), update() and delete() return Integer that is consequence of PreparedStatement.executeUpdate(). select techniques return spun sentences:

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

The final issue is with compiler alerts - "warning: [unchecked] unchecked cast". The reason being all techniques call single private method that returns Object and cast its lead to pointed out types. Like me a new comer to Java, I am also unsure basically have selected appropriate types for chooses. Apart from that, everything appears to operate not surprisingly.