Passing around Connection

Question

I have a myCon=getUnusedConnection() method that gets a connection from Connection pool. I also have a releaseConnection(myCon) method to release Connection to the pool after finishing using it.

When coding, I need to select data from the database many times. I also want to reuse my code. I want to have many methods for a single action.

Example:

public static List<String[]> getData(){
 Connection myCon=null;
 PreparedStatement preparedStmt=null;
 try{
 myCon=getUnusedConnection();
 String sql="select ........";
 preparedStmt=myCon.prepareStatement(sql);
 ResultSet results=preparedStmt.executeQuery();
 String str="";
 if(results.next()){
 str=results.getString(1);
 }
 if(!str.equals("")){
 List<String[]> list=getData2(myCon, preparedStmt, str);
 }
 return list;
 }
 catch (SQLException ex) {
 while (ex != null) {
 System.out.println ("SQL Exception: " +
 ex.getMessage ());
 ex = ex.getNextException ();
 }
 }catch (java.lang.Exception e) {
 System.out.println("***ERROR-->" + e.toString()); 
 }
 finally{
 releaseConnection (myCon);
 closeStatement (preparedStmt);
 }
 return null;
}
public static List<String[]> getData2(Connection myCon, PreparedStatement preparedStmt, String str){
 try{
 List<String[]> list=new ArrayList<String[]>();
 String sql="c.......";
 preparedStmt=myCon.prepareStatement(sql);
 ResultSet results=preparedStmt.executeQuery();
 while(results.next()){
 list.add(results.getString(1));
 }
 return list;
 }catch (SQLException ex) {
 while (ex != null) {
 System.out.println ("SQL Exception: " +
 ex.getMessage ());
 ex = ex.getNextException ();
 }
 }catch (java.lang.Exception e) {
 System.out.println("***ERROR-->" + e.toString()); 
 }
 finally {
 closeStatement(preparedStmt);
 releaseConnection(myCon); 
 }
 return null;
}

Do I need to include try - catch - finally in getData2, since I am passing myCon and prepareStatement around? I am not sure if this the right way to code.

Is the way I'm coding considered standard? If not, do you have something better in mind?

• Dave Jarvis: javaworld.com/article/2077817/java-se/…

Answer 1

There are a number of problems with your code. Let's go through the more important ones:

• you should close the PreparedStatement before you return the connection to the pool.... should be:

  finally{
   closeStatement (preparedStmt);
   releaseConnection (myCon);
  }
  

• you should be closing the ResultSet in addition to the Statement!

• but, this is all somewhat pointless because you only use your prepared statement once.... A prepared statement is useful for two reasons only:
  1. they separate issues in syntax from issues in data (you get syntax errors early) ... but, your SQL Query code will not have syntax errors... right?
  2. they improve performance IF THEY ARE REUSED, but you are not reusing them.
• If you are using Java 7 you should be using try-with-resources logic on your objects. This will remove the need for the finally block.

JDBC Connection pools are relatively complicated 'animals'. I strongly recommend you leverage the functionality that others have built. For example, Tomcat, WebSphere and I am sure other applications servers have good connection-pooling processes. Additionally, stand-alone applications can (re)use things like the Apache DBCP component (which also supports PreparedStatement connection pools)