Copy Java ResultSet

Question

I have a java.sql.ResultSet object that I need to update. However, the result set is not updated. Unfortunately, this is a limitation for the specific structure that I am using.

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

At this point, I believe that my best option is to create a new result set object and copy the contents of the original result set to a new one, manipulating the data as I do it.

However, I hunted high and low on Google and it seems that I can’t determine how to do this or even even possible.

I am new to Java, so any help would be greatly appreciated.

Answer 1

Thanks for answers. I ended up finding CachedRowSet , which is exactly what I need. With this, I was able to disable the ResultSet object and update it.

What else, because CachedRowSet implements the ResultSet interface, I could still pass it to the file generation method, which requires an object that implements the ResultSet.

Answer 2

It would be common practice to map ResultSet to List<Entity> , where Entity is your own class that contains information about the data represented by a single row of the database. For example. User , Person , Address , Product , Order , etc., 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, move and modify it in the usual way of Java. Finally, when saving it to the database, use PreparedStatement to update them in packages at a time.

 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(); // ... 

Please note that some databases have a limit on the lot size. The Oracle JDBC driver has a limit of about 1000 elements. You can call executeBatch() every 1000 points. It should be simple using a counter inside the loop.

See also:

• Collection Book
• PreparedStatement Tutorial