[cfoley]

I'm trying to make a ResultSet programatically so I can pass it to a method I want to test. Unfortunately, the manually inserted rows are coming back in the reverse order. The hacky solution is to add the rows in the reverse order so they come back the right way round but that doesn't feel right.

Here is a sample of code that reproduces the problem with sample output.

Can anyone help fix it?

import java.sql.*;

import javax.sql.rowset.*;

import com.sun.rowset.*;


public class ResultSetProblem {

	public static void main(String[] args) throws SQLException {
		try (ResultSet r = makeResultSet();)/>/> {
			printResultSet(r);
		}
	}

	private static ResultSet makeResultSet() throws SQLException {
		CachedRowSet result = new CachedRowSetImpl();
		RowSetMetaDataImpl metaData = new RowSetMetaDataImpl();
		metaData.setColumnCount(1);
		result.setMetaData(metaData);

		result.moveToInsertRow();
		result.updateString(1, "This is row 1");
		result.insertRow();
		result.updateString(1, "This is row 2");
		result.insertRow();
		result.updateString(1, "This is row 3");
		result.insertRow();

		result.moveToCurrentRow();		
		return result;
	}

	private static void printResultSet(ResultSet results) throws SQLException {
		while (results.next()) {
			String rowData = results.getString(1);
			System.out.println(rowData);
		}
	}

}

This is row 3
This is row 2
This is row 1

[farrell2k]

Sorry, man. I have no idea why it is happening. It's adding the rows the exact opposite why the docs say it should be. If you figure it out, I'd like to know.

To make things a little easier, you can call

result.afterLast(); before you return the result set

then while(results.previous())

to loop through it backwards.

This post has been edited by farrell2k: 09 March 2013 - 01:25 PM

[andrewsw]

I was reading that the order of the rows for a CachedRowSet is implementation dependent, but you might need to move to the current row between each call and (again depending on the implementation) acceptChanges between each call:

     crs.insertRow();
     crs.moveToCurrentRow();
     crs.acceptChanges();

but I am not an expert in this area

[farrell2k]

Nah. He's inserting into an insertRow, a buffer. That won't work.

[cfoley]

Thanks for the suggestion farrel2k. That would work but I'm using it to test a function that takes a real ResultSet from a database query. If I have to use a dirty hack, I'd rather it was at creation time.

Andrewsw, I tried that. The acceptChanges() method throws an exception because there is no Connection object for it to push changes to. Toggling between current row and insert row didn't change the order but it was worth trying.

I've been looking for a method to move the insertRow cursor in the same way that the currentRow cursor can be moved. The search has been fruitless but I did see somewhere that the insert row is a "special position" for inserting.

That suggests maybe there is no fix. Is anyone able to recommend a different implementation of ResultSet. I'm going to go back and have another look at the API. Maybe a different implementation would insert them the other way around.

[farrell2k]

You could probably view the source of CachedRowSetImpl to see how it implements insertRow(), and maybe change it as needed in a subclass. It sure seems like a lot of work though. Good luck.

[andrewsw]

Database design principles state that the order of rows is not important and not to be relied upon />/>.

Is it not possible to sort (order) the resultset? I believe you would, again, need to subclass the ResultSet or create a List<T>; that is, copy the results into a List and then .sort()>

This post has been edited by andrewsw: 09 March 2013 - 02:58 PM

[cfoley]

Thanks farrell2k! Why didn't I think of looking at the source. There is a comment in there that goes like...

 5470           /*
 5471            * The new row is inserted into the RowSet
 5472            * immediately following the current row.
 5473            *
 5474            * If we are afterlast then the rows are
 5475            * inserted at the end.
 5476            */

A quick change to my sample fixes the order:

import java.sql.*;

import javax.sql.rowset.*;

import com.sun.rowset.*;


public class ResultSetProblem {

	public static void main(String[] args) throws SQLException {
		try (ResultSet r = makeResultSet();)/> {
			printResultSet(r);
		}
	}

	private static ResultSet makeResultSet() throws SQLException {
		CachedRowSet result = new CachedRowSetImpl();
		RowSetMetaDataImpl metaData = new RowSetMetaDataImpl();
		metaData.setColumnCount(1);
		result.setMetaData(metaData);

		result.afterLast();
		result.moveToInsertRow();
		result.updateString(1, "This is row 1");
		result.insertRow();
		result.moveToCurrentRow();
		
		result.afterLast();
		result.moveToInsertRow();
		result.updateString(1, "This is row 2");
		result.insertRow();
		result.moveToCurrentRow();
		
		result.afterLast();
		result.moveToInsertRow();
		result.updateString(1, "This is row 3");
		result.insertRow();
		result.moveToCurrentRow();
		
		result.beforeFirst();
		return result;
	}

	private static void printResultSet(ResultSet results) throws SQLException {
		while (results.next()) {
			String rowData = results.getString(1);
			System.out.println(rowData);
		}
	}

}

[farrell2k]

Nice catch, dude!