Returning a Resultset...........best practice question

["Pitre, Russell" <RP...@shawmut.com>]

Running:  Tomcat 4.1.24,  JSDK 1.4.0_03

 

I'm writing a class to return a resultSet to use in a JSP page or at
least i think thats what i want to do. Whats the best practice to return
that resultset.....As the class is written below, when i try to iterate
through the resultset in the jsp page i get a NullPointerException which
i understand because i closed the connection before returning....If i
take out all the code in the finally section i can iterate through the
data no problem....but thats not a good idea to leave connections
open.......instead of returning a resultset should i return some other
type such as an Array with all the data. How should i return the data?
How can I alter my code for best practice.....



======================================================================
package foo;

import javax.naming.*;
import javax.sql.*;
import java.sql.*;

public class DBTest{

String foo = "Not Connected";
String bar = "Not";

public ResultSet getUser(){

Connection conn = null;
Statement stmt = null; //Or preparedStatement if needed
ResultSet rs = null;

try{
Context ctx = new InitialContext();
if(ctx == null)
throw new Exception("Boom - No Context");

DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/Show");
if(ds != null){
conn = ds.getConnection();

if(conn != null){
foo = "Got Connection " + conn.toString();
stmt = conn.createStatement();
rs = stmt.executeQuery("SELECT * FROM user");

if(rs.next()){
foo=rs.getString("login");
bar=rs.getString("password");
}

}
}


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

// Always make sure result sets and statements are closed,
// and the connection is returned to the pool.
if(rs != null){
try{
rs.close();
}catch(SQLException e){;}
rs=null;
}
if(stmt != null){
try{stmt.close();}catch(SQLException e){;}
stmt=null;
}
if(conn != null){
try{conn.close();}catch(SQLException e){;}
conn=null;
}

}
return rs; 
}

public String getFoo(){return foo;}
public String getBar(){return bar;}