![]() |
|||
![]() ![]() |
![]() |
![]()
|
![]() |
CursorsCursors 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:
Figure 6-1 illustrates the cursor mechanism.
Operation TheoryJDBC 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 ResultSets 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 ExamplesThe 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(); } } } SummaryThis chapter discussed some essential techniques for fine-tuning database queries and updates from JDBC:
The next chapter discusses more advanced techniques such as callable statements, dynamic SQL, and the metadata interfaces of JDBC.
|
![]() |
|