ResultSet customization

The modern JDBC API allows you to greatly customize objects.StatementAndresult set. For example, usingresult setyou can change rows in the database.

When creating a statement object, we can pass a bunch of our wishes into it. These wishes can be divided into three groups:

  • Base connection type
  • Concurrent access control
  • Persistence and transactions

These parameters can be passed when creating an objectStatementorPreparedStatement. Example:

Statement statement = connection.createStatement(
    ResultSet.TYPE_FORWARD_ONLY,
    ResultSet.CONCUR_READ_ONLY,
    ResultSet.CLOSE_CURSORS_OVER_COMMIT );

PreparedStatement statement = connection.prepareStatement(sql,
    ResultSet.TYPE_FORWARD_ONLY,
    ResultSet.CONCUR_READ_ONLY,
    ResultSet.CLOSE_CURSORS_OVER_COMMIT);

We will not study these things in depth, but I want you to know that this is possible if you come across something similar in someone else's code.

ResultSet types

ResultSet can be of a specific type. The type defines some of the characteristics and capabilities of the ResultSet.

Java university

Not all types are supported by all databases and JDBC drivers. You will have to check your database and JDBC driver to see if it supports the type you want to use. The DatabaseMetaData.supportsResultSetType(int type) method returns true or false depending on whether the given type is supported or not.

At the time of writing, there are three types of ResultSet:

  • ResultSet.TYPE_FORWARD_ONLY
  • ResultSet.TYPE_SCROLL_INSENSITIVE
  • ResultSet.TYPE_SCROLL_SENSITIVE

The default type is TYPE_FORWARD_ONLY.

TYPE_FORWARD_ONLY means that the ResultSet can only be moved forward. That is, you can only move from row 1, row 2, row 3, etc. In a ResultSet, you cannot move backward: you cannot read data from the 9th row after reading the tenth.

TYPE_SCROLL_INSENSITIVE means that the ResultSet can be moved (scrolled) forward or backward. You can also move to a position relative to the current position, or move to an absolute position.

A ResultSet of this type is insensitive to changes to the underlying data source while the ResultSet is open. That is, if an entry in the ResultSet is changed in the database by another thread or process, it will not be reflected in the already openedresult setof this type.

TYPE_SCROLL_SENSITIVE means that the ResultSet can be moved (scrolled) forward or backward. You can also move to a position relative to the current position, or move to an absolute position.

A ResultSet of this type is sensitive to changes in the underlying data source while the ResultSet is open. That is, if an entry in the ResultSet is changed in the database by another thread or process, it will be reflected in the already openresult setof this type.

Concurrency

The concurrency of a ResultSet determines whether the ResultSet can be updated or only read.

Some databases and JDBC drivers support updating a ResultSet, but not all. The DatabaseMetaData.supportsResultSetConcurrency(int concurrency) method returns true or false depending on whether the given concurrency mode is supported or not.

ResultSet can have one of two levels of concurrency:

  • ResultSet.CONCUR_READ_ONLY
  • ResultSet.CONCUR_UPDATABLE

CONCUR_READ_ONLY means the ResultSet can only be read.

CONCUR_UPDATABLE means the ResultSet can be read and modified.

An example of changing data in the database

With these parameters, you can control the generated Statement and its ResultSet.

For example, you can create an updatable ResultSet and use it to change the database. When creating a Statement, it is important to observe the following conditions:

  • only one table is specified
  • does not contain join or group by clauses
  • query columns must contain a primary key

When the above conditions are met, the updated ResultSet can be used to update a table in the database. When creating a Statement object, you need to specify the following parameters:

Statement st = createStatement(Result.TYPE_SCROLL_INSENSITIVE, Result.CONCUR_UPDATABLE)

The result of executing such a statement is an updatable result set. The update method is to move the ResultSet cursor to the row you want to update and then call the updateXXX() method .

The updateXXX method works similarly to the getXXX() method . The updateXXX() method has two parameters. The first is the number of the column being updated, which can be a column name or a serial number. The second is the data to be updated and this data type must be the same as XXX.

To actually update the row in the database, you need to call the updateRow() method before the ResultSet cursor leaves the changed row, otherwise the changes will not get into the database.

You can also add new rows to the table:

First you need to move the cursor to an empty line. To do this, call the moveToInsertRow() method .

Then you need to fill this row with data using the updateXXX() method .

Then you need to call the inserterRow() method to add the row to the base.

And finally, you need to return the cursor back by calling the moveToCurrentRow() method .

Important! Not all DBMSs support these options for the extended Statement. In case of problems, see the official documentation of a particular DBMS.