Convert ResultSet into List/ArrayList/?

[AntiEarnie]

I am at a bit of a loss on this. How the heck do you write a generic class/function/whatever that will convert a ResultSet with an arbitratry number of Rows and Columns into some sort of container for data manipulation?

In VB I just use the GetRows method on the recordset and it generates a 2d array that I can manipulate to my hearts desire. I am fairly sure java has something similiar but darn if I can find it. ArrayLists seem like a close match but it appears that the colums have to be known at compile time (at least in every working example I have found thats the case). I suppose I could do something along the lines of a list of lists but I am unsure how to get to the items in the nested list.

 

[Diancecht]

To my understanding, the ResultSet is intended to not obtain all the data at a time but access them in a iterative basis.

Why can't you treat the data iterating through the ResultSet?

Cheers,
Dian

 

[AntiEarnie]

That would fit with what I have read on them. At least along the lines of requiring a constant connection to the DB while the ResultSet exists, as if it were buffering the results in the connection. Which is one reason I would like to dump the ResultSet into a data structure, to release the database connection quickly.

There are a couple different answers to the iterating thing. First off I expect to have to iterate through the ResultSet to populate what data structures I have seen in the tutorials. I am just unsure what type of data sturcture to try and use for this as I am trying to dump an arbitrary number of columns into it. Second answer is that in the side project I am doing is taking query results in a tab format and feeding them into a graphing program. This will require me to make 1 to X passes through the tabbed query results to populate each series. No first() method, no efficient way to create the all the series.

I can brute force a way to populate the series on my graph. I would greatly prefer to figure out a nice generic piece of code I could dump into my apps that will do this though. :\

 

[venur]

Hi,

Hope this will help you,

http://jakarta.apache.org/commons/beanutils/api/org/apache/commons/beanutils/RowSetDynaClass.html

Cheers
Venu

 

[AntiEarnie]

Hmm, that looks like it might do it venur. I'll have to experiment with it in my test code when it slows down some out here. Thanks for the info!

 

[AntiEarnie]

JOY!

That did the trick venur. Mildly annoying that a DynaBean needs the column name in order to get the values but building a ColumnLabel Vector off the ResultSet's MetaData did the trick.

If anyone is interested, the following dumps the results of a tabbed query of arbitrary rows and columns into jfreechart and generates a jpg.

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Vector;

import org.apache.commons.beanutils.DynaBean;
import org.apache.commons.beanutils.RowSetDynaClass;
import org.jfree.chart.ChartFactory;
import org.jfree.chart.ChartUtilities;
import org.jfree.chart.JFreeChart;
import org.jfree.chart.axis.DateAxis;
import org.jfree.chart.plot.XYPlot;
import org.jfree.data.time.Millisecond;
import org.jfree.data.time.TimeSeries;
import org.jfree.data.time.TimeSeriesCollection;

public class TimeSeries_jdbc_dynamic {
		public static void main(String[] args) {
		    try {
				Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
				Connection con = DriverManager.getConnection("jdbc:odbc:MY_DB_DSN", "USER", "PASSWORD");
				Statement stmt = con.createStatement();
				String sql = Some_Query_That_Gives_Tabbed_Results;
				ResultSet rs = stmt.executeQuery(sql);

				ResultSetMetaData rsmd = rs.getMetaData();
				int numberOfColumns = rsmd.getColumnCount();
				String tempString = null;
				Vector headers  = new Vector(15) ;
				for (int i=1 ;i <= numberOfColumns; i++){
					tempString = new String ((String) rsmd.getColumnLabel(i));
					headers.add(tempString);
				}

				TimeSeriesCollection dataset = new TimeSeriesCollection();

				RowSetDynaClass rsdc = new RowSetDynaClass(rs);
				
				List rowset = rsdc.getRows();
				
				for (int i=1;i < numberOfColumns; i++)
				{
					TimeSeries newSeries = new TimeSeries((String) headers.get(i), Millisecond.class);
					
					Iterator rowsetIterator = rowset.iterator();					
					while (rowsetIterator.hasNext()){
						String columnName = (String) headers.get(i);

						DynaBean row = (DynaBean) rowsetIterator.next();
						Number tempValue = (Number) row.get(columnName.toLowerCase());
						java.util.Date time = new Date((String) row.get("timestamp"));
						newSeries.add(new Millisecond(time), tempValue );	
					}					

		    	dataset.addSeries(newSeries);
				} // for (int i=3;i <= numberOfColumns; i++)
		    	JFreeChart chart = ChartFactory.createTimeSeriesChart(
		        		"Analog Chart Test", // Title
		        		"Timestamp", // X-Axis
		        		"Value", // Y-Axis
		        		dataset, // Dataset
		        		true, //show legend
		        		true, //use tooltips
		        		false); //configure chart to generate urls
		        	
		        	XYPlot plot = chart.getXYPlot();
		        	DateAxis axis = (DateAxis) plot.getDomainAxis();
		        	axis.setDateFormatOverride(new SimpleDateFormat("hh:mm:ss"));
		        	
		        	try {
		        		ChartUtilities.saveChartAsJPEG(new File("TimeSeriesChart_JDBC_dynamic.jpg"), chart, 500, 300);
		        	}catch (IOException e){
		        		System.err.println("problem occurred creating chart");
		        		}			
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (ClassNotFoundException e) {
					e.printStackTrace();
				}
		  }
	}

 

[AntiEarnie]

Hmm, assuming ResultSet is in the form:

timestamp header1 ... headerX
date number number number
... ... ... ...
date number number number

 

[sedj]

I have to ask ... why on earth would you bother using that 'RowSetDynaClass' bumf in that code ? You really should just use the ResultSet directly.
I could understand why you might want to extract the ResultSet data into some wrapper if you were perhaps interacting with a user, that may take several minutes to perform some action before you could close the db connection and resources, but in that example above, there really is NO point in it.

BTW, you also would be better off using saveChartAsPNG instead of saveChartAsJPEG because JPEGs only really make sense for graphics with a lot of anti-aliasing or data such as photos (Joint Photographic Expert Group == JPEG). PNGs are typically a lot smaller, and more suitable for charts and simple lines/block colours.

--------------------------------------------------
Free Java/J2EE Database Connection Pooling Software

http://www.primrose.org.uk

 

[AntiEarnie]

Actually there is a point to it. If I knew how many columns this would catch then this code could be simply hardcoded for them. I am having to loop through an unknown number of columns in order to build each series (for various reasons I can not just do a first()). If there is a clean way to use a ResultSet in one pass for an unknown number of columns I would love to see it, god knows it would be more straightforward, probably.

As a side note I was looking for a generic mechanism to create a data structure that I could perform logic upon, or further process. In this case it seemed interesting to feed it to jfreechart.

Truth be told I forgot about png's. It is rare that I dump anything to an image file so it didn't occure to me. Heck this phase is just a stepping stone to streaming the chart into an applet at any rate, but thats out of the scope of this thread.

 

[sedj]

OK, so as I see it, you need to loop each column in the dataset, while looping the data fully.

Just so I understand how you are manipulating your data ...
So if your data looked like :

1aaaa 1bbbb 1cccc
2aaaa 2bbbb 2cccc
3aaaa 3bbbb 3cccc

You would in effect be doing :

first column pass :
1aaaa
2aaaa
3aaaa

second column pass :
1bbbb
2bbbb
3bbbb

third column pass :
1ccccc
2ccccc
3ccccc

So you need to loop your ResultSet for each column. Why not use first() to scroll the the ResultSet back to the beginning ? Or does your driver or db not support srollable resultsets ? If it cannot, then I could see why you would need that DynaStuff.

--------------------------------------------------
Free Java/J2EE Database Connection Pooling Software

http://www.primrose.org.uk

 

[AntiEarnie]

That is pretty much it on the contents of the ResultSet. Only difference is that there is a common TIMESTAMP field reused for each series.

The driver appears to support first() but the query's that are being passed throw odd errors when I try and use TYPE_SCROLL_INSENSITIVE. From the way the errors read it is a result of the aggregation going on in the DB to create the tabbed format.

 

[sedj]

The Sun JdbcOdbcDriver is not very good, and even Sun says it really should not be used for any production level stuff. Maybe a better driver (or a real database ) would support scrollable resultsets better ?

--------------------------------------------------
Free Java/J2EE Database Connection Pooling Software

http://www.primrose.org.uk

 

[Diancecht]

And if you need all those data, why not using the metadata of the ResultSet to gather all data in just one pass?

Cheers,
Dian

 

[AntiEarnie]

Hmm, thats a suprise. Even the MS default drivers are fairly decent. As for DB's Oracle is about as real as it gets.

 

[AntiEarnie]

Dian -
I did not see anything in ResultSetMetaData that was useful ofther then the number of columns and column lables. Am I missing something? From what I have been able to piece together I can not crete an arbitrary number of series objects to populate in one pass. Although, I suppose I could use a bigger hammer approach and use a massive number of IF statements since there are only 16 or so different colors to graph
with, i.e.

if numberOfColumns == 1
newSeries1 =
if numberOfColumns == 2
newSeries2 =
...
if numberOfColumns == 15
newSeries15 =

*shudder*

I do not think I would willingly admit to making code like that though :\

 

[sedj]

*shudder*

Indeed !

I think if your driver supports first(), then use that and not the DynaBla stuff ... but if it doesn't then your solution using the DynaBla stuff is probably the neatest

--------------------------------------------------
Free Java/J2EE Database Connection Pooling Software

http://www.primrose.org.uk

 

[Diancecht]

You can define a HashMap of ArrayLists, the key will be the column name and the contents the data.

But maybe I still can't see your problem because to my understanding you're just getting the database to your front end.

Cheers,
Dian

 

[wiser]

Just a thought:
Couldn't you create an array of TimeSeries objects before iterating through the data? That way you could use the standard ResultSet functionality to step through the data a row at a time building up your TimeSeries simultaneously, and add them all to the TimeSeriesCollection at the end.

I imagine it would be something along the lines of:

...
	TimeSeries[] newSeries = new TimeSeries[numberOfColumns];
	for (int i=1; i < numberOfColumns; i++)
	{
		newSeries[i] = new TimeSeries((String) headers.get(i), Millisecond.class)
	}

	while(rs.next())
	{
		for (int i=1; i < numberOfColumns; i++)
		{
			String columnName = (String) headers.get(i);
			Number tempValue = (Number) rs.getObject(columnName.toLowerCase());
			// or
			// Number tempValue = new Double(rs.getDouble(columnName.toLowerCase());

			java.util.Date time = new Date(rs.getTimestamp("timestamp").getTime());
			newSeries[i].add(new Millisecond(time), tempValue );    
		}
	}

	for (int i=1; i < numberOfColumns; i++)
	{
		dataset.addSeries(newSeries[i]);
	}
...

OK, perhaps this could be tidied up a bit but hopefully you get what I was on about.

As for your database driver, I've used the one that comes with Oracle 9i client for a while without any trouble - So if you have access to the Oracle client software it might be worth checking that out.