Using a Bean for Retrieving Results Sets - Best Approach

[Reggie McDougal]

I want to use a Bean for my DB Connections and results set by just passing in the SQL and returing a Result Set.

But I'm stunped here How can I close the connection?

I want to used this or a similar approach so I don't have to repeat the same code multiple times. If some one could give me a way I can achive this?

  //Method in DB Bean
public ResultSet getResultSet(String sql) throws NamingException, SQLException {
Connection conn = null;
InitialContext ic = new InitialContext();
DataSource ds = (DataSource)ic.lookup("shop");
conn = ds.getConnection();
PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
//rs.close();
//conn.close();
return rs;

}

// Instantiating method

  
public ArrayList getDataTypeCode(String productTypeFeatureID) throws NamingException, SQLException {
String sql = "SELECT * FROM product";
DB_Bean db = new DB_Bean();
ResultSet rs = db.getResultSet(sql);
ArrayList productTypeCode = new ArrayList();
int i=0;
while (rs.next()){
 ArrayList row = new ArrayList();
 row.add(0,"0"); // deprecated
 row.add(1,rs.getString("product_type_feature_id"));
 row.add(2,rs.getString("available_value"));
 row.add(3,rs.getString("data_type_code"));
 productTypeCode.add(i,row);
 i++;
}
 

[ February 15, 2005: Message edited by: Reggie McDougal ]

[ February 15, 2005: Message edited by: Reggie McDougal ]
[ February 15, 2005: Message edited by: Reggie McDougal ]

[Jeanne Boyarsky]

Reggie,
You can't close the connection because it has a smaller scope than the result set. A good solution is to put that method in a superclass. You can provide hooks (methods to be implemented in the subclass) for setting the parameters and processing the resultset. Your idea is sound to separate out the db code.

[Reggie McDougal]

Setting the DB_Bean as a super class to product solved the problem OK.

I have a couple of servlets that save form data and they cant extend DB_Bean.

What about setting up an interface to impliment the DB connection and returning the resultSet.

Is there more elegant solution than just sub classing to DB_Bean?
[ February 15, 2005: Message edited by: Reggie McDougal ]

[Adeel Ansari]

do some reading on DAO, Data Access Object.

[Jeanne Boyarsky]

Reggie,
It's best if a servlet isn't going data access directly. You can use the DAO that Adeel suggested or at least a separate class.

I'm not sure how an interface to implement the DB connection would help you. Wouldn't you still have the problem with closing the connection?