Lets Innovate: Interview Questions: JDBC : Volume 3

Please click on any advertisement on right side if you like this blog.

Q. Can ResultSets be passed between methods of a class? Are there any special usage

Yes. There is no reason that a ResultSet can't be used as a method parameter just like any other object reference. You must ensure that access to the ResultSet is synchronized. This should not be a problem is the ResultSet is a method variable passed as a method parameter - the ResultSet will have method scope and multi-thread access would not be an issue.

As an example, say you have several methods that obtain a ResultSet from the same table(s) and same columns, but use different queries. If you want these ResultSets to be processed the same way, you would have another method for that. This could look something like:

public List getStudentsByLastName(String lastName) {
ResultSet rs = ... (JDBC code to retrieve students by last name);
return processResultSet(rs);
}

public List getStudentsByFirstName(String firstName) {
ResultSet rs = ... (JDBC code to retrieve students by first name);
return processResultSet(rs);
}

private List processResultSet(ResultSet rs) {
List l = ... (code that iterates through ResultSet to build a List of Student objects);
return l;
}

Since the ResultSet always has method scope - sychronization is never an issue.

1. There is only one ResultSet. Dont assume that the ResultSet is at the start (or in any good state...) just because you received it as a parameter. Previous operations involving the ResultSet will have had the side-effect of changing its state.
2. You will need to be careful about the order in which you close the ResultSet and CallableStatement/PreparedStatement/etc

From my own experience using the Oracle JDBC drivers and CallableStatements the following statements are true:

* If you close the CallableStatement the ResultSet retrieved from that CallableStatement immediately goes out-of-scope.
* If you close the ResultSet without reading it fully, you must close the CallableStatement or risk leaking a cursor on the database server.
* If you close the CallableStatement without reading it's associated ResultSet fully, you risk leaking a cursor on the database server.

No doubt, these observations are valid only for Oracle drivers. Perhaps only for some versions of Oracle drivers.

The recommended sequence seems to be:
* Open the statement
* Retrieve the ResultSet from the statement
* Read what you need from the ResultSet
* Close the ResultSet
* Close the Statement

Q. What is Metadata and why should I use it? Metadata ('data about data') is information about one of two things:
1. Database information (java.sql.DatabaseMetaData), or
2. Information about a specific ResultSet (java.sql.ResultSetMetaData).

Use DatabaseMetaData to find information about your database, such as its capabilities and structure. Use ResultSetMetaData to find information about the results of an SQL query, such as size and types of columns.

Please click on any advertisement on right side if you like this blog.

Q. How to Retrieve Warnings ?

ans:-- SQLWarning objects are a subclass of SQLException that deal with database access warnings. Warnings do not stop the execution of an application, as exceptions do; they simply alert the user that something did not happen as planned. For example, a warning might let you know that a privilege you attempted to revoke was not revoked. Or a warning might tell you that an error occurred during a requested disconnection.A warning can be reported on a Connection object, a Statement object (including PreparedStatement and CallableStatement objects), or a ResultSet object. Each of these classes has a getWarnings method, which you must invoke in order to see the first warning reported on the calling object. If getWarnings returns a warning, you can call the SQLWarning method getNextWarning on it to get any additional warnings. Executing a statement automatically clears the warnings from a previous statement, so they do not build up. This means, however, that if you want to retrieve warnings reported on a statement, you must do so before you execute another statement.The following code fragment illustrates how to get complete information about any warnings reported on the Statement object stmt and also on the ResultSet object rs

Statement stmt = con.createStatement();                                                                                               ResultSet rs = stmt.executeQuery("select COF_NAME from COFFEES");
while (rs.next())
{
String coffeeName = rs.getString("COF_NAME");

            System.out.println("Coffees available at the Coffee Break: ");
            System.out.println("    " + coffeeName);
            SQLWarning warning = stmt.getWarnings();
            if (warning != null) {
                        System.out.println("\n---Warning---\n");
                        while (warning != null) {
                                    System.out.println("Message: "
                                                   + warning.getMessage());
                                    System.out.println("SQLState: "
                                                 + warning.getSQLState());
                                    System.out.print("Vendor error code: ");
                                    System.out.println(warning.getErrorCode());
                                    System.out.println("");
                                    warning = warning.getNextWarning();
                        }
            }
            SQLWarning warn = rs.getWarnings();
            if (warn != null) {
                        System.out.println("\n---Warning---\n");
                        while (warn != null) {
                                    System.out.println("Message: "
                                                   + warn.getMessage());
                                    System.out.println("SQLState: "
                                                   + warn.getSQLState());
                                    System.out.print("Vendor error code: ");
                                    System.out.println(warn.getErrorCode());
                                    System.out.println("");
                                    warn = warn.getNextWarning();
                        }
            }
        }

Warnings are actually rather uncommon. Of those that are reported, by far the most common warning is a DataTruncation warning, a subclass of SQLWarning. All DataTruncation objects have an SQLState of 01004, indicating that there was a problem with reading or writing data. DataTruncation methods let you find out in which column or parameter data was truncated, whether the truncation was on a read or write operation, how many bytes should have been transferred, and how many bytes were actually transferred