wood burning stoves 2.0*
The moose likes JSP and the fly likes Putting a ResultSet into an Array Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » JSP
Bookmark "Putting a ResultSet into an Array" Watch "Putting a ResultSet into an Array" New topic
Author

Putting a ResultSet into an Array

George Larry
Ranch Hand

Joined: Nov 07, 2001
Posts: 52
Hi...
I have a query that returns 1 field from my db. Like last name...
query = "select distinct name from table";
rs = stmt.executeQuery( query );
Now... I want to create an array of all the names... How can I do that?
I thought I could do this...
String[] listNames = rs.getArray( "name" );
But that's not working...
Thank you for your help,
-GL
Zac Roberts
Ranch Hand

Joined: Jan 29, 2002
Posts: 82
Maybe you need to iterate through your ResultSet, something like this...


Zac Roberts<br />SCJP2
Hartmut Ludwig
Ranch Hand

Joined: Aug 31, 2002
Posts: 51
Originally posted by George Larry:
I thought I could do this...
String[] listNames = rs.getArray( "name" );
But that's not working...

It would work only for SQL3 Datatypes, if the Database column type is SQL ARRAY, which means each field contains an actual array.
But in your case each field contains a String.
To convert your resultset into an array you have to step through the resultset row by row to fetch the values and write them to the array.
So your code should look somehow like this:
query = "select distinct name from table";
rs = stmt.executeQuery(query);
int i = 0;
while (rs.next()) {
listNames[i] = rs.getString(1);
i++
}
make shure the array listNames is large enough. If you are not shure how many names the result contains you can use a "count distinct name from table" first to find it out and size the array correctly, or you could collect the data into a vector and copy the vector into the array.
BTW: This question should actually be posted in the JDBC-forum.
sl
Hartmut
George Larry
Ranch Hand

Joined: Nov 07, 2001
Posts: 52
What if I'm not sure on the length? It could be anywhere from 0 to 100...
I tried this:

I'm getting an error:
Variable listDept may not have been initialized (both when I'm trying to set it and when displaying it).
How do I initialize it... I thought I did. Also- Do I have to set the size when I initialize it? If I do that- and set it to 100, for example, and the query returns 4 names... will I get 96 ( null + "<br>" )'s?
Thanks,
-GL
Thomas Paul
mister krabs
Ranch Hand

Joined: May 05, 2000
Posts: 13974
You didn't initialize listNames. All you did was identify it as a pointer to an array of Strings. You didn't create an array of Strings though.
You need to do something like this before you can add anything to it:
String[] listnames = new String[100];
If you don't know the right size to use, you can do what one of the other posters suggested.


Associate Instructor - Hofstra University
Amazon Top 750 reviewer - Blog - Unresolved References - Book Review Blog
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

You might want to use a Collection of some sort ( probably arraylist if not familiar with them ).

Jamie
[ September 13, 2002: Message edited by: Jamie Robertson ]
Romin Irani
Greenhorn

Joined: Aug 03, 2002
Posts: 3
1. Use a Vector to hold your resultset
2. Use the method toArray() on the vector. The method returns a Object[].
-Romin
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

Originally posted by Romin Irani:
1. Use a Vector to hold your resultset
2. Use the method toArray() on the vector. The method returns a Object[].
-Romin
Vector's are not really recommended anymore. The Vector class has been retrofitted into the Collections Framework hierarchy to implement the List interface. However, if you are using the new framework, you should use ArrayList, instead. If you need a synchronized list, then synchronize the arraylist ( see
here for more details on synchronizing Lists/Collections )
Jamie
Ali Hassaan
Ranch Hand

Joined: May 16, 2001
Posts: 103
You cant use getArray method with JDBC Type 1 Driver. You have to use any driver which implements javax.sql.Array Interface like Oracle 8i JDBC Driver.
Thomas Paul
mister krabs
Ranch Hand

Joined: May 05, 2000
Posts: 13974
Originally posted by Jamie Robertson:
If you need a synchronized list, then synchronize the arraylist ( see
here for more details on synchronizing Lists/Collections )
Jamie is now officially my buddy!
Sean MacLean
author
Ranch Hand

Joined: Nov 07, 2000
Posts: 621
Jamie's provided the most common technique of using an ArrayList. This would be the most robust implementation.
 
jQuery in Action, 2nd edition
 
subject: Putting a ResultSet into an Array