How to update a resultset programmatically ?

Create a scrollable and updatable ResultSet object

Statement stmt = connection.createStatement(

       ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

ResultSet rs = stmt.executeQuery(

       "SELECT COLUMN_1, COLUMN_2 FROM TABLE_NAME");

Move the cursor to the specific position and use related method to update data and then, call updateRow() method

rs.last();

rs.updateFloat("COLUMN_2", 25.55);   // update last row's data

rs.updateRow();  // Don't miss this method, otherwise data will be lost

What is RowSet ? What are types of RowSet ?

A RowSet is an object that encapsulates a set of rows from either JDBC resultsets or tabular data sources like a file or spreadsheet.

RowSets support component-based development models like JavaBeans, with a standard set of properties and an event notification mechanism.

Types of RowSet

Connected

A connected RowSet object connects to the database once and remains connected until the application terminates.

Disconnected

A disconnected RowSet object connects to the database, executes a query to retrieve the data from the database and then closes the connection.

A program may change the data in a disconnected RowSet while it is disconnected.

Modified data can be updated in the database after a disconnected RowSet re-establishes the connection with the database.

Scenario (JDBC 2.0) : How to insert and delete a row programmatically ?

To insert / delete row, make sure the resultset is updatable.

1. Move the cursor to the specific position

rs.moveToCurrentRow();

2. Set value for each column

rs.moveToInsertRow();  // to set up for insert

rs.updateString("col1" "strvalue");

rs.updateInt("col2", 5);

...

3. Call inserRow() method to finish the row insert process

rs.insertRow();

4. To delete a row, move to the specific position and call deleteRow() method

rs.absolute(5);

rs.deleteRow();   // delete row 5

5. To see the changes call refreshRow()

rs.refreshRow();

Scenario : How can I retrieve only the first n rows, second n rows of a database using a particular WHERE clause ?

SCENARIO

How can I retrieve only the first n rows, second n rows of a database using a particular WHERE clause ?

For example

If a SELECT typically returns a 1000 rows, how do first retrieve the 100 rows, then go back and retrieve the next 100 rows and so on.

Use the Statement.setFetchSize method to indicate the size of each database fetch.

Note that this method is only available in the Java 2 platform.

What are new features in JDBC 2.0 ?

Scrollable result sets

Scroll forward and backward in a result set or move to a specific row

[TYPE_SCROLL_SENSITIVE, previous(), last(), absolute(), relative(), etc.]

Batch Updates functionality

Send multiple SQL statements to the database as a unit, or batch [addBatch(), executeBatch()]

New data types

Use the new SQL3 datatypes as column values like Blob, Clob, Array, Struct, Ref. 

Custom  mapping of user-defined types (UTDs)

New data manipulation methods

Make updates to database tables using methods in the Java programming language instead of using SQL commands.

(updateRow(), insertRow(), deleteRow(), etc.)

Java applications can now use the ResultSet.updateXXX methods.

Miscellaneous features

• Performance hints
• Use of character streams
• Full precision for java.math.BigDecimal values
• Additional security
• Support for time zones in date, time, and timestamp values.