Using result sets, statements & connections

[Adam Hardy]

Is it possible to write a bean or a function in JSP that will open a connection, obtain a result set from a statement, close the connection without trashing the result set and then return the result set for use elsewhere?
i.e. something like the visual basic equivalent of 'disconnected recordsets'? (yup, i've just converted from ASP & VB & SQL7 to JSP, Java & Oracle).
I'm currently doing this:

conDB = DriverManager.getConnection(strConnection, strUserName, strPassword);
staDB = conDB.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
objResultSet = staDB.executeQuery(strSQL);

thanks
Adam
[This message has been edited by Adam Hardy (edited October 09, 2001).]

[Yogen Vadnere]

Adam,
One solution is convert ur resultset to vector before closng the connection, and onwards use it instead of resultset.

[Adam Hardy]

a vector? is that like an array?
Could you show how in a quick code snippet? Thanks
Adam

[Yogen Vadnere]

here is the sample code for resultset to vector
try{
String sqlString = "SELECT user_name FROM user" ;
ResultSet myRst = con.executeQuery(sqlString) ;
Vector retuVector = new Vector() ;
while((imsRst!=null) && imsRst.next()){
retuVector.addElement(myRst.getString("user_name")) ;
return retuVector ;
}catch(SQLException ex){
return new Vector() ;
}

[Adam Hardy]

I see - I checked out the class in the java.sun docs. I can't see any way though to cater for multiple fields - do vectors or arrays allow more than one dimension, or do I have to use a vector to store each record's fields, and then put that in a vector?

[Yogen Vadnere]

I would suggest two ways
1. use array in vector

Vector returnVector = new Vector() ;
while(rst.next()){
String[] tempString = new String[3] ;
tempString[0] = rst.getString("name") ;
tempString[1] = rst.getString("address") ;
tempString[2] = rst.getString("city") ;
returnVector.addElement(tempString)
}
2. vector in vector

Vector returnVector = new Vector() ;
while(rst.next()){
Vector tempVector = new Vector();
tempVector.addElement(rst.getString("name")) ;
tempVector.addElement(rst.getString("address")) ;
tempVector.addElement(rst.getString("city")) ;
returnVector.addElement(tempVector)
}
Hope this will help u.

[Jamie Robertson]

If you don't need your resultset synchronized you can use ArrayList in the same manner as a Vector. The two are virtually the same , but an ArrayList is not synchronized. This means that there is less overhead(and better performance) when using ArrayLists.
What about using a CachedRowSet?? I believe that this may be what your looking for, anyways have a look at the documentation(I think it is fromt he javax.sql package using the jdk2EE).
Jamie

[Yogen Vadnere]

u can use array in vector or vector in vector
1. array in vector
Vector retuVector = new Vector() ;
while((imsRst!=null) && imsRst.next()){
String tempStrin[] = new String[3] ;
tempStrin[0] = myRst.getString("user_name") ;
tempStrin[1] = myRst.getString("user_city") ;
tempStrin[2] = myRst.getString("user_zip") ;
retuVector.addElement(tempStrin) ;
}

2. vector in vector
Vector retuVector = new Vector() ;
while((imsRst!=null) && imsRst.next()){
Vector myVector = new Vector() ;
myVector.addElement(myRst.getString("user_name")) ;
myVector.addElement(myRst.getString("user_city")) ;
myVector.addElement(myRst.getString("user_zip")) ;
retuVector.addElement(myVector) ;
}

Hope this will help u..

[Adam Hardy]

funnily enough i couldn't find any reference to CachedRowSet on the java.sun.com site - i had a look thro the javax.sql package and it looks v. useful though. so how do you create a cached rowset?

[Shiv Sidhaarth]

Hi,
You can create ur own class which is simulation of ResultSet. Place that vector of vector inside a class and create getString() which acts similarly to getString in ResultSet. Add as many methods u want.
- Sankar

[Jamie Robertson]

RowSet Tutorial: http://developer.java.sun.com/developer/Books/JDBCTutorial/chapter5.html

Jamie
[This message has been edited by Jamie Robertson (edited October 11, 2001).]

[Adam Hardy]

well for the sake of the archives here I'm just posting that I got the new disconnected resultsets working after downloading the earlyAccess rowset jar from sun at:
http://developer.java.sun.com/developer/earlyAccess/crs/
thanks for the help.