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 Getting data from database into a collection Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Getting data from database into a collection" Watch "Getting data from database into a collection" New topic
Author

Getting data from database into a collection

Iain Emsley
Ranch Hand

Joined: Oct 11, 2007
Posts: 60
I've been trying to change some code from Hibernate into JDBC (as my cide queries a separate db in a project) and have come more than a little unstuck and would be graateful for some advice (and any explanations on why I've gone wrong so I can learn rather than guess). The original Hibernate code is:

The code that I have so far is:

I don't think the code is in fact collecting the data into the collection at all (which is what I need to do - I've been trying to set up something like return new TreeSet<BwGroups>.add) and I'm fairly sure its down to mismatched data types but would appreciate some guidance. The code queries a database to find which group an individual belongs to so that a resource can be assigned to them.
[ June 19, 2008: Message edited by: Iain Emsley ]
Bill Cruise
Ranch Hand

Joined: Jun 01, 2007
Posts: 148
The first problem that I see is that you're closing your PreparedStatement and ResultSet too early. You should do that after the loop, preferably in the finally block.

Beyond that, I don't know what a BwGroup is, but I'm pretty sure you don't want to cast your ResultSet to that type. You'd normally use a ResultSet getXXX method where XXX is a Java type name to get the fields from each database record returned by the query. Check out the ResultSet API for more details.
Iain Emsley
Ranch Hand

Joined: Oct 11, 2007
Posts: 60
Thanks for this. I've moved the openings and closures to a more appropriate area. BwGroup is a collection of names to which names are linked in the database so what I'm trying to do is to query the db where the BwGroup name is linked to val and return that in a tree set. I'm wondering if I ought to do something like put the result set into a string and interate through it and add each result into the TreeSet.

I'll take a further look at the link but wonder if I need to get the ResultSet into a string then add it to the TreeSet which I need to return.
[ June 19, 2008: Message edited by: Iain Emsley ]
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 40041
    
  28
Sounds more like a question for the JDBC forum: I shall move it there.
Bill Cruise
Ranch Hand

Joined: Jun 01, 2007
Posts: 148
Each time through the loop the ResultSet is going to contain one piece of data for each column in your listname table. Typically, you'd take the pieces of data you're interested in, make an object out of them, then add that object to your collection.

Here's a contrived example:



Note that as I get items from my ResultSet, I have to know what order they're in. In your case, since you did select *, you have to know the order of the columns in the database. If you're only interested in a few columns you should rewrite the SQL to only return those few.
Iain Emsley
Ranch Hand

Joined: Oct 11, 2007
Posts: 60
Thanks for the explanation, Bill. I think my code is gradually getting there along with a better understanding of how to use ResultSet.
Iain Emsley
Ranch Hand

Joined: Oct 11, 2007
Posts: 60
This is something that I always fallover on: how would I return the group from the while loop?
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18987
    
    8

You wouldn't return it from the while loop. Where did you get that idea from? You would return it from the method that loop is contained in, using the "return" statement.
Bill Cruise
Ranch Hand

Joined: Jun 01, 2007
Posts: 148
Paul's right. Each time through the loop you're adding a new object to your collection. Once that loop is done you return the entire collection.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18987
    
    8

Originally posted by Bill Cruise:
Each time through the loop you're adding a new object to your collection. Once that loop is done you return the entire collection.
Which is exactly what the code in your original code does. So perhaps I didn't understand the question about returning from the while-loop?
Iain Emsley
Ranch Hand

Joined: Oct 11, 2007
Posts: 60
No, it's not you misunderstanding; its me not being entirely crystal. What I need to do is the return the collection to another section of the programme to complete the task (associating resources with an individual) in other sections of code. When I run the compiler, I get the error that the symbol for user cannot be found (I seem to be stuck in a loop of them at the moment with this).
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18987
    
    8

I still don't understand. Your original post had it correct:

1. Create an empty collection before the loop starts.

2. For each time through the loop, create an object and add it to the collection.

3. After the loop ends, return the collection.

Now you have code that doesn't do that. What happened?
Iain Emsley
Ranch Hand

Joined: Oct 11, 2007
Posts: 60
I don't think I had set up the code correctly in the original in that I hadn't declared the set which I think I've now done:

If I run the compiler without the final return statement, it complains that it needs one. However if do put one in, I either get an illegal start of argument or incompatible types error.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18987
    
    8

Now we don't have a JDBC question at all, we have a Java beginners question. You're just trying to write basic code that will compile. Try this: forget about JDBC for now. Write a method that creates an empty collection and returns it. (That's steps #1 and #3.) If you can't do that, post your best try. All of it, please, not just part of it as in the last post.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Getting data from database into a collection