0

I am using Vaadin framework and following MVC design pattern to develop a web application project. While implementing connection pooling feature for my project I encountered the following problem. I am getting the ResultSet in one class(data class) and I am using that ResultSet in another class(Business Logic). I have to close the connection object after using that ResultSet in the business logic class. What may be the efficient way to achieve this without passing the connection object to the business logic class? Please Explain.Thank You.

user3256546
  • 73
  • 2
  • 5
  • 2
    Hmm, personally I would store the data in DTOs and shut the connection immediately. – Scary Wombat Jan 31 '14 at 08:54
  • 1
    Like @user2310289 says, you should really get the data from the `ResultSet`, however, if this isn't an option you could look at use a `javax.sql.rowset.CachedRowSet` (http://docs.oracle.com/javase/6/docs/api/javax/sql/rowset/CachedRowSet.html). – Nick Holt Jan 31 '14 at 09:00
  • Yes,I used CachedRowSet... it was working fine...Thank You so much...:) – user3256546 Jan 31 '14 at 09:39

1 Answers1

1

I would recommend that you write a Dao which returns a List of Business Objects and NOT the resultsets. The connection must be closed in the Dao itself. Below is an example

public class PersonDao {
    private DataSource ds; //add a setter and inject the JDBC resource
    public List<Person> getPersons() {
        List<Person> personList = new ArrayList();
        Connection con;
        PreparedStatement pstmt;
        try {
            con = ds.getConnection(username, password);
            pstmt = con.prepareStatement("SELECT * FROM PERSON");
            ResultSet rs = pstmt.executeQuery(query); 
            //Fetch the resultset, iterate over it and populate the list
            while (rs.next()) {
                Person p = new Person();
                p.setName(rs.getString("name");
                personList.add(p);
            }
        } catch (Exception ex {
            // ... code to handle exceptions
       } finally {
            if (con != null) con.close();
       }

      return personList;
}

If you can use Java 7, you can also use try with resource which would automatically handle the closing of connections for you. If you are not in a position to change the Dao interface, then it is a good idea to write a layer between the Dao and the business layer.

senseiwu
  • 5,001
  • 5
  • 26
  • 47
  • I used CachedRowSet as suggested by Nick Holt which does not needs DAOs it was working fine...you may also know this it will be useful for you... – user3256546 Jan 31 '14 at 09:41
  • its your choice, but i would try to avoid it. Returning CachedRowSet is also not an elegant way to design a method. Someone who reads the code will not know what exactly the CachedRowSet is holding unless they look what your query is. If you return a simple Dto, like EmployeeDto, then the signature is clear – senseiwu Jan 31 '14 at 10:23