File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes SQL Results To Collection Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "SQL Results To Collection" Watch "SQL Results To Collection" New topic

SQL Results To Collection

Mark Mero

Joined: Oct 14, 2004
Posts: 12
I am looking for the best way of storing a result of sql query results in a collection.

The sql results look similar to the following:

100 Tg Sample1
101 Th Sample5
102 Tk Sample8
103 Tf Sample7
104 Ts Sample2

Would a multidimensional array be best for storing the results so that they can be extracted element by element when required.
Paul Clapham

Joined: Oct 14, 2005
Posts: 19973

Not really. For one thing you don't know the number of rows until you have read them all, and for another thing it is not object-oriented.

Create a class that represents a row of your table. Each time you read a row of the table, create an object of that class and fill it with the column data. (This is called a DAO, Data Access Object). Then add that object to a collection; ArrayList would do nicely.
Reid M. Pinchback
Ranch Hand

Joined: Jan 25, 2002
Posts: 775
Another option you might want to consider is having a factory class or method somewhere that provides the java.util.Collection object, instead of making it obvious you are using ArrayList or whatever. Just use an Iterator to traverse the collection instead of using get(i) or size(), so you don't have an implicit assumptions about the nature of the Collection.

It seems most applications running against a daatabase sooner or later evolve into having more data to return than you want to actually have in memory at one time. If you hide your actual Collection (or List, or Map) implementation, you gain the opportunity to change the Iterator implemention too. That lets you play the same kinds of games as EJB CMP and CMR, where you can hide the result set dependency inside the Iterator.
[ February 03, 2006: Message edited by: Reid M. Pinchback ]

Reid - SCJP2 (April 2002)
I agree. Here's the link:
subject: SQL Results To Collection
It's not a secret anymore!