File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Using result sets, statements & connections Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Using result sets, statements & connections" Watch "Using result sets, statements & connections" New topic
Author

Using result sets, statements & connections

Adam Hardy
Ranch Hand

Joined: Oct 09, 2001
Posts: 565
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:

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


I have seen things you people would not believe, attack ships on fire off the shoulder of Orion, c-beams sparkling in the dark near the Tennhauser Gate. All these moments will be lost in time, like tears in the rain.
Yogen Vadnere
Ranch Hand

Joined: Sep 20, 2001
Posts: 58
Adam,
One solution is convert ur resultset to vector before closng the connection, and onwards use it instead of resultset.

Yogen Vadnere
Adam Hardy
Ranch Hand

Joined: Oct 09, 2001
Posts: 565
a vector? is that like an array?
Could you show how in a quick code snippet? Thanks
Adam
Yogen Vadnere
Ranch Hand

Joined: Sep 20, 2001
Posts: 58
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
Ranch Hand

Joined: Oct 09, 2001
Posts: 565
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
Ranch Hand

Joined: Sep 20, 2001
Posts: 58
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
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

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
Ranch Hand

Joined: Sep 20, 2001
Posts: 58
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
Ranch Hand

Joined: Oct 09, 2001
Posts: 565
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
Ranch Hand

Joined: Aug 06, 2001
Posts: 119
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
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

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
Ranch Hand

Joined: Oct 09, 2001
Posts: 565
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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Using result sets, statements & connections
 
Similar Threads
redirection problem
requestDispatcher
show preselected values
how to know when server automatically invalidates session
Reporting Application + JSP