JDBC interview Questions

How can you move the cursor in scrollable result sets?
One of the new features in the JDBC 2.0 API is the ability to move a result set’s cursor backward as well as forward. There are also methods that let you move the cursor to a particular row and check the position of the cursor.

Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet srs = stmt.executeQuery(”SELECT COF_NAME, PRICE FROM COFFEES”);

The first argument is one of three constants added to the ResultSet API to indicate the type of a ResultSet object: TYPE_FORWARD_ONLY, TYPE_SCROLL_INSENSITIVE , and TYPE_SCROLL_SENSITIVE. The second argument is one of two ResultSet constants for specifying whether a result set is read-only or updatable: CONCUR_READ_ONLY and CONCUR_UPDATABLE. The point to remember here is that if you specify a type, you must also specify whether it is read-only or updatable. Also, you must specify the type first, and because both parameters are of type int , the compiler will not complain if you switch the order. Specifying the constant TYPE_FORWARD_ONLY creates a nonscrollable result set, that is, one in which the cursor moves only forward. If you do not specify any constants for the type and updatability of a ResultSet object, you will automatically get one that is TYPE_FORWARD_ONLY and CONCUR_READ_ONLY.

What’s the difference between TYPE_SCROLL_INSENSITIVE , and TYPE_SCROLL_SENSITIVE?
You will get a scrollable ResultSet object if you specify one of these ResultSet constants.The difference between the two has to do with whether a result set reflects changes that are made to it while it is open and whether certain methods can be called to detect these changes. Generally speaking, a result set that is TYPE_SCROLL_INSENSITIVE does not reflect changes made while it is still open and one that is TYPE_SCROLL_SENSITIVE does. All three types of result sets will make changes visible if they are closed and then reopened:

1.       Statement stmt =

2.                con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

3.       ResultSet srs =

4.                stmt.executeQuery("SELECT COF_NAME, PRICE FROM COFFEES");

5.       srs.afterLast();

6.       while (srs.previous())

7.       {

8.                String name = srs.getString("COF_NAME");

9.                float price = srs.getFloat("PRICE");

10.            System.out.println(name + " " + price);

11.   }

How to Make Updates to Updatable Result Sets?
Another new feature in the JDBC 2.0 API is the ability to update rows in a result set using methods in the Java programming language rather than having to send an SQL command. But before you can take advantage of this capability, you need to create a ResultSet object that is updatable. In order to do this, you supply the ResultSet constant CONCUR_UPDATABLE to the createStatement method.

12.   Connection con =

13.            DriverManager.getConnection("jdbc:mySubprotocol:mySubName");

14.   Statement stmt =

15.            con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

16.   ResultSet uprs =

         stmt.executeQuery("SELECT COF_NAME, PRICE FROM COFFEES");

1

2

3

4

5

6

7

8