Copying Java ResultSet

Question

I have a java.sql.ResultSet object that I need to update. However the result set is not updatable. Unfortunately this is a constraint on the particular framework I'm using.

What I'm trying to achieve here is taking data from a database, then manipulating a small amount of the data and finally the data is being written to a CSV file.

At this stage I think my best option is to create a new result set object and copy the contents of the original result set into the new one, manipulating the data as I do so.

However, I've hunted high and low on Google and don't seem to be able to determine how to do this or whether it's even possible at all.

I'm new to everything Java so any assistance would be gratefully received.

Answer 1

The normal practice would be to map the ResultSet to a List<Entity> where Entity is your own class which contains information about the data represented by a single database row. E.g. User, Person, Address, Product, Order, etcetera, depending on what the table actually contains.

List<Entity> entities = new ArrayList<Entity>();
// ...
while (resultSet.next()) {
    Entity entity = new Entity();
    entity.setId(resultSet.getLong("id"));
    entity.setName(resultSet.getString("name"));
    entity.setValue(resultSet.getInt("value"));
    // ...
    entities.add(entity);
}
// ...
return entities;

Then, you can access, traverse and modify it the usual Java way. Finally, when persisting it back in the DB, use a PreparedStatement to update them in batches in a single go.

String sql = "UPDATE entity SET name = ?, value = ? WHERE id = ?";
// ...
statement = connection.prepareStatement(sql);
for (Entity entity : entities) {
    statement.setString(1, entity.getName());
    statement.setInt(2, entity.getValue());
    statement.setLong(3, entity.getId());
    // ...
    statement.addBatch();
}
statement.executeBatch();
// ...

Note that some DB's have a limit on the batch size. Oracle's JDBC driver has a limit on around 1000 items. You may want to call executeBatch() every 1000 items then. It should be simple using a counter inside the loop.

See also:

• Collections tutorial
• PreparedStatement tutorial

Answer 2

Thanks for the responses. In the end I found CachedRowSet which is exactly what I needed. With this I was able to disconnect the ResultSet object and update it.

What's more, because CachedRowSet implements the ResultSet interface I was still able to pass it to my file generation method which requires an object that implements ResultSet.