JDBC - Resultset (SELECT|SQL query)

> Procedural Languages > Java > JDBC (Java Database Connectivity)

1 - About

The ResultSet interface encapsulates the results of an SQL query and implements a cursor API. Statements may also be batched, allowing an application to submit multiple updates to a data source as a single unit of execution.

You use a ResultSet object to display the data in the application pages.

By default, only one ResultSet object per Statement object can be open at the same time. Therefore, if the reading of one ResultSet object is interleaved with the reading of another, each must have been generated by different Statement objects.

A ResultSet object contains a table of data representing a database result set.

A cursor points to the current row of data in a ResultSet object.

Initially, it is positioned before the first row.

You use the next method of the ResultSet object to move the cursor to the next row in the result set. It returns false when there are no more rows in the ResultSet object.

The ResultSet interface provides accessor methods (getBoolean, getLong, getInt, and so on). Values can be retrieved by using either the index number of the column or the name of the column.

By default, only one ResultSet object per Statement object can be open at the same time.

Therefore, to read data from multiple ResultSet objects, you must use multiple Statement objects. A ResultSet object is automatically closed when the Statement object that generated it is closed, rerun, or used to retrieve the next result from a sequence of multiple results.

Advertising

3 - Properties

If you are working with a driver or database that does not offer scrollable and updatable ResultSet objects, you can use a RowSet object populated with the same data as a ResultSet object and thereby effectively make that ResultSet object scrollable and updatable.

3.1 - Scrollability

Scrollability refers to the ability to move backward as well as forward through a result set. You can also move to any particular position in the result set, through either relative positioning or absolute positioning.

ResultSet.TYPE_SCROLL_SENSITIVE creates a ResultSet object whose cursor can move both forward and backward relative to the current position and to an absolute position.

The default value is generally ResultSet.TYPE_SCROLL_INSENSITIVE.

3.2 - Sensitivity

When creating a scrollable or positionable result set, you must also specify sensitivity. This refers to the ability of a result set to detect and reveal changes made to the underlying database from outside the result set.

A sensitive result set can see changes made to the database while the result set is open, providing a dynamic view of the underlying data.

Changes made to the underlying column values of rows in the result set are visible.

3.3 - Updatability

Updatability refers to the ability to update data in a result set and then copy the changes to the database. This includes inserting new rows into the result set or deleting existing rows. A result set may be updatable or read-only.

The default value is generally for the concurrency property ResultSet.CONCUR_UPDATABLE

3.4 - Categorie

Scrollability and sensitivity are independent of updatability, and the three result set types and two concurrency types combine for the following six result set categories:

  • Forward-only/read-only
  • Forward-only/updatable
  • Scroll-sensitive/read-only
  • Scroll-sensitive/updatable
  • Scroll-insensitive/read-only
  • Scroll-insensitive/updatable

A forward-only updatable result set has no provision for positioning at a particular row within the ResultSet object. You can update rows only as you iterate through them using the next method.

Advertising

4 - Example

Connection con = DriverManager.getConnection
           ( "jdbc:myDriver:wombat", "myLogin","myPassword");
 
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM Table1");
while (rs.next()) {
	int x = rs.getInt("a");
	String s = rs.getString("b");
	float f = rs.getFloat("c");
	}