Separate JDBC Connection and ResultSet code

Question

I want to separate my database connection code and my result set code. The only way that I can think of to do this is not ideal, because it would be creating 2 connection pools. Code snippet:

    public void connectivity() throws SQLException{

    try{
     Class.forName(driver);
     Connection c = DriverManager.getConnection(url, user, pass);
     Statement st = c.createStatement();
    }
   catch(ClassNotFoundException e){
    e.printStackTrace();
    }
   finally{
      try{
      c.close();

    }
     catch(Exception e){
     e.printStackTrace();
    }

   }
}

public Statement getStatement() throws SQLException{
         Class.forName(driver);
         Connection c = DriverManager.getConnection(url, user, pass);
         Statement st = c.createStatement();

         return st;

}

And then in another class I have:

Connectivity connect = new Connectivity();
Statement st = connect.getStatement();
ResultSet r = st.executeQuery(sql);

I do this because I need access to Statement in order to make the ResultSet work. How can I abstract the Connectivity code and the result set code to have them in 2 different modules, without having to create 2 connection pools?

Thanks in advance.

Answer 1

If you want to separate out the code you might want to just make the connection a member variable and just create one connection and then either create statements or actually make another method that would have a prepared statement (another data member) and then just return a result set. There are probably a bunch of ways of doing this. Here is an example to get you on your way:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class Connectivity {
    Connection connection;
    String url, user, pass, driver;

    public Connectivity(String url, String user, String pass, String driver) {
        super();
        this.url = url;
        this.user = user;
        this.pass = pass;
        this.driver = driver;
        try{
             Class.forName(driver);
             connection = DriverManager.getConnection(url, user, pass);

            }catch(ClassNotFoundException e){
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            }
    }

    public Statement getStatement() throws SQLException{
             return connection.createStatement();

    }

    public void close() throws SQLException{
        connection.close();
    }

    @Override
    protected void finalize() throws Throwable {
        super.finalize();
        connection.close();
    }
}

By the way there might be better ways of trying to do what you want to do. You should listen to EJP and look into the Data Access Object Pattern.