• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Using result sets, statements & connections

 
Adam Hardy
Ranch Hand
Posts: 567
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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).]
 
Yogen Vadnere
Ranch Hand
Posts: 58
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Adam,
One solution is convert ur resultset to vector before closng the connection, and onwards use it instead of resultset.
 
Adam Hardy
Ranch Hand
Posts: 567
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
a vector? is that like an array?
Could you show how in a quick code snippet? Thanks
Adam
 
Yogen Vadnere
Ranch Hand
Posts: 58
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 567
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 58
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 58
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 567
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 119
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 567
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic