Brought to you by EarthWeb
ITKnowledge Logo Login Graphic Click to go to the Oscars.
Click to go to the Oscars.
ITKnowledge
Search this book:
 
Search the site:
 
EXPERT SEARCH ----- nav

EarthWeb Direct

EarthWeb Direct

EarthWeb sites: other sites

Previous Table of Contents Next


Cursors

Cursors are used to access a set of rows returned by a SQL SELECT statement. They are associated with query statements and have a symbolic name that may be used to access individual rows of data. Associated with cursors are cursor result sets and cursor positions. Note that some DBMSs do not support cursors.

Thus, a cursor:

  Is associated with a SELECT statement
  Has a name
  Has a position
  May affect ResultSets by positioned update/delete statements using the cursor name

Figure 6-1 illustrates the cursor mechanism.


Figure 6-1:  Cursor components.

Operation Theory

JDBC supports simple cursors that can be used in positioned update or positioned delete statements. They remain valid until the ResultSets or their parent Statements are closed.

Statement

void ResultSet.setCursorName(String name);
String ResultSet.getCursorName();
boolean DatabaseMetaData.supportsPositionedDelete();
boolean DatabaseMetaData.supportsPositionedUpdate();

void ResultSet.setCursorName(String name);

This method is used to give a statement a specific cursor name. Cursor names should be unique. A cursor name is automatically provided by default, which is often sufficient.

String ResultSet.getCursorName();

This method returns the current ResultSet’s cursor name. This cursor name may be used later for positioned updates and positioned deletes.

boolean DatabaseMetaData.supportsPositionedDelete();

This DatabaseMetaData method returns true when the database supports positioned deletes.

boolean DatabaseMetaData.supportsPositionedUpdate();

This method returns true when the database supports positioned updates.

Practical Examples

The following examples are somewhat simplified. They illustrate the cursor mechanism for positioned updates and positioned deletes. In a real-world application, the SELECT statement would have a WHERE clause to limit the cursor scope. The condition that is tested before doing the positioned delete or positioned update is usually more elaborate.

// cursors: positioned delete
import java.sql.*;
class SimpleExample
{
   public static void main(String args[])
   {
          String url = “jdbc:odbc:mysource”;
          try
          {
   Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
          Connection myConnection =
                   DriverManager.getConnection(url,
                           “javauser”, “hotjava”);
          Statement firstStmt =
                   myConnection.createStatement();
          Statement secondStmt =
                   myConnection.createStatement();
          ResultSet rs = firstStmt.executeQuery(
          “SELECT * FROM employees FOR UPDATE”);
          String csr = rs.getCursorName();
          int temp;
          // we scan the resultset, row by row
          while (rs.next())
          {
                           temp = rs.getInt(“salary”);
                           // activate positioned delete
                           if (temp >= 100000)
                           {
                                       secondStmt.executeUpdate(
                   “DELETE employees WHERE CURRENT OF “ + csr);
                           }
                   }
                   rs.close();
                   firstStmt.close();
                   secondStmt.close();
                   myConnection.close();
          }
                   catch(java.lang.Exception ex)
          {
                   ex.printStackTrace();
          }
   }
}

The same remarks are valid for the positioned update example shown here:

// cursors: positioned update
import java.sql.*;
class SimpleExample
{
   public static void main(String args[])
   {
          String url = “jdbc:odbc:mysource”;
          try
          {
          Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
                   Connection myConnection =
                           DriverManager.getConnection(url,
                                   “javauser”, “hotjava”);
                   Statement firstStmt =
                           myConnection.createStatement();
                   Statement secondStmt =
                           myConnection.createStatement();
                   ResultSet rs = firstStmt.executeQuery(
                   “SELECT * FROM employees FOR UPDATE”);
                   String csr = rs.getCursorName();
                   int temp;
                   // we scan the resultset, row by row
                   while (rs.next())
                   {
                                   temp = rs.getInt(“salary”);
                                   // activate positioned update
                                   if (temp >= 100000)
                                   {
                                         secondStmt.executeUpdate(
                   “UPDATE employees SET salary=salary*1.1
                                               WHERE CURRENT OF “ + csr);
                                   }
                   }
                   rs.close();
                   firstStmt.close();
                   secondStmt.close();
                   myConnection.close();
          }
          catch(java.lang.Exception ex)
          {
                   ex.printStackTrace();
          }
   }
}

Summary

This chapter discussed some essential techniques for fine-tuning database queries and updates from JDBC:

  Dealing with data type conversion
  The SQL escape syntax
  Database transactions
  Handling SQL cursors

The next chapter discusses more advanced techniques such as callable statements, dynamic SQL, and the metadata interfaces of JDBC.


Previous Table of Contents Next
HomeAbout UsSearchSubscribeAdvertising InfoContact UsFAQs
Use of this site is subject to certain Terms & Conditions.
Copyright (c) 1996-1999 EarthWeb Inc. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Read EarthWeb's privacy statement.