SQL cursors

In the previous article in this series, you learned how to move through the rows of a Java ResultSet by using the next() method. This technique mirrors what happens when you execute an SQL query within the ij tool that is included with the Apache Derby database. In both cases, this functionality is implemented within the Derby database (or any other SQL-compliant database) by a cursor. A cursor is a database structure that allows you to iterate through the results of an SQL query.

You can interact directly with an SQL cursor in Derby either explicitly, by issuing SQL commands with the ij tool, or implicitly, by using the relevant methods in the JDBC application programming interface (API). This article discusses both methods, beginning with the explicit use of SQL cursors within the ij tool.

To begin, start the ij tool, and connect to a database, as shown in Listing 1. (Note: To save space, all the Java-based examples shown in this article are abbreviated.) The full source code for each example is provided in an archive file, which you can get from the Download section at the end of this article. There's also a Derby script file, derby.build.sql, that you can execute (see the fourth article in this series to create a working Derby database for use with these examples).

                rb$ java org.apache.derby.tools.ij < derby.build.sql
...
rb$ java org.apache.derby.tools.ij
ij version 10.2
ij> CONNECT 'jdbc:derby:test' ;
ij> AUTOCOMMIT OFF ;
ij> GET CURSOR productsCursor AS 
'SELECT * FROM bigdog.products FOR UPDATE OF price' ;
ij> NEXT productsCursor ;
ITEMNUMBER |PRICE  |STOCKDATE |DESCRIPTION                             
-----------------------------------------------------------------------
1          |19.95  |2006-03-31|Hooded sweatshirt                       
ij> UPDATE bigdog.products SET price = price * 1.10 WHERE CURRENT OF productsCursor ;
1 row inserted/updated/deleted
ij> CLOSE productsCursor ;
ij> SELECT * FROM bigdog.products WHERE itemNumber = 1 ;
ITEMNUMBER |PRICE  |STOCKDATE |DESCRIPTION                             
-----------------------------------------------------------------------
1          |21.94  |2006-03-31|Hooded sweatshirt                       

1 row selected
ij> ROLLBACK ;
ij> SELECT * FROM bigdog.products WHERE itemNumber = 1 ;
ITEMNUMBER |PRICE  |STOCKDATE |DESCRIPTION                             
-----------------------------------------------------------------------
1          |19.95  |2006-03-31|Hooded sweatshirt                       

1 row selected
ij> EXIT ; 

As the example in Listing 1 demonstrates, to work with an SQL cursor, you need to have a suitable database. This example begins by executing the Derby script provided in the sample code (available in the Download section). Next, the ij tool is started, and a connection to the appropriate database is made. The next step is to turn off the AUTOCOMMIT mode. By default, the AUTOCOMMIT mode is on, and any data-modification process causes a commit operation to be performed — which, by default, closes any open cursors. Thus, to perform positioned update or delete operations, you need to disable the AUTOCOMMIT mode. Note: If you're only going to iterate through a cursor, you can leave the AUTOCOMMIT mode enabled.

The next step is to create the named cursor, which you do by using the GET CURSOR command along with the necessary SQL query that selects the rows for the cursor. In Derby, the standard cursor is a forward-only cursor that starts with the first row, allowing you to step through the rows sequentially by using the NEXT command. When you've found the desired row within your cursor, you can issue the appropriate UPDATE operation.

In this example, the next step is to close the cursor and execute a SELECT query to display the modified results. Next, a ROLLBACK command is executed, which removes the changes. This is possible because you never executed a COMMIT command, and the AUTOCOMMIT mode was disabled. To demonstrate that the UPDATE operation was not committed to the database, the query is re-executed, showing the original results.

The second edition of the JDBC API introduced support for two new types of cursors: scroll-sensitive cursors and scroll-insensitive cursors. The two cursors both enable scrolling, which lets you move around within a cursor — forward and reverse, in both a relative and an absolute sense. These two types differ in whether they're sensitive to changes made to the underlying data while the cursor is open. A sensitive cursor provides a dynamic view of the underlying data, whereas an insensitive cursor generally isn't sensitive to any changes made to the database while the cursor is open.

As of version 10.2, Apache Derby only provides support for scroll-insensitive cursors. Such cursors can be manipulated in a number of interesting ways, as shown in Listing 2.

                rb$ java org.apache.derby.tools.ij
ij version 10.2
ij> CONNECT 'jdbc:derby:test' ;
ij> AUTOCOMMIT OFF ;
ij> GET SCROLL INSENSITIVE CURSOR productsCursor AS 
'SELECT * FROM bigdog.products FOR UPDATE OF price' ;
ij> ABSOLUTE 5 productsCursor ;
ITEMNUMBER |PRICE  |STOCKDATE |DESCRIPTION                             
-----------------------------------------------------------------------
5          |49.95  |2006-02-20|Female bathing suit, one piece, aqua    
ij> relative -1 productsCursor ;
ITEMNUMBER |PRICE  |STOCKDATE |DESCRIPTION                             
-----------------------------------------------------------------------
4          |29.95  |2006-02-10|Male bathing suit, blue                 
ij> BEFORE FIRST productsCursor ;
No current row
ij> NEXT productsCursor ;
ITEMNUMBER |PRICE  |STOCKDATE |DESCRIPTION                             
-----------------------------------------------------------------------
1          |19.95  |2006-03-31|Hooded sweatshirt                       
ij> UPDATE bigdog.products SET price = price * 1.10 WHERE CURRENT OF productsCursor ;
1 row inserted/updated/deleted
ij> NEXT productsCursor ; 
ITEMNUMBER |PRICE  |STOCKDATE |DESCRIPTION                             
-----------------------------------------------------------------------
2          |99.99  |2006-03-29|Beach umbrella                          
ij> PREVIOUS productsCursor ;
ITEMNUMBER |PRICE  |STOCKDATE |DESCRIPTION                             
-----------------------------------------------------------------------
ERROR XJ001: Java exception: ': java.lang.NullPointerException'.
ij> CLOSE productsCursor ;
ij> SELECT * FROM bigdog.products WHERE itemNumber = 1 ;
ITEMNUMBER |PRICE  |STOCKDATE |DESCRIPTION                             
-----------------------------------------------------------------------
1          |21.94  |2006-03-31|Hooded sweatshirt                       

1 row selected
ij> ROLLBACK ; 
ij> SELECT * FROM bigdog.products WHERE itemNumber = 1 ;
ITEMNUMBER |PRICE  |STOCKDATE |DESCRIPTION                             
-----------------------------------------------------------------------
1          |19.95  |2006-03-31|Hooded sweatshirt                       

1 row selected
ij> EXIT ;

This example differs from the first example exclusively in the use of a scroll-insensitive cursor. After making a database connection from within the ij tool, turning off the AUTOCOMMIT mode, and creating the scroll-insensitive cursor, you learn how to use several of the basic SQL cursor-manipulation commands, which are listed in Table 1. First, you use the ABSOLUTE command to move to the fifth row in the cursor; then, you use the RELATIVE command to move back one row (so the cursor is now at the fourth row). Next you use the BEFORE FIRST command to position the cursor before the first row so that you can call the NEXT command to access the first row in the cursor.

At this point, you execute an SQL UPDATE operation to modify the current row and move to the second row. Finally, you attempt to move back to the first row to display the updated contents. But you receive a NullPointerException, because the current row no longer exists — it was modified, and the changes aren't visible in the current cursor. To view the updated row, you must close the cursor and issue a query to display the new results. The ROLLBACK command removes the changes and exits to the operating system.

Back to top

Updateable ResultSets

Because the entire Apache Derby database suite is written in the Java language, it should come as no surprise that you can replicate the ij updateable cursor functionality in your own Java applications by creating an updateable ResultSet and calling the appropriate JDBC methods. The JDBC API includes many methods you can use to move around within a scrollable ResultSet (to move around in a forward-only ResultSet, you use the next() method). The most important methods match the ij commands listed in Table 1, including next(), previous(), absolute(), relative(), and beforeFirst().

The next few sections demonstrate how you can use the JDBC API to selectively update, delete, or insert new data into a ResultSet. To simplify the examples, they all use a unidirectional cursor; but you can easily modify them to use a scrollable cursor, which allows you to programmatically move forward and backward through your ResultSet.

Updating rows

As you may imagine, a common business need is to modify specific column values as you iterate through the rows in a ResultSet. For example, you might want to update the number of items in your inventory after you make a sale or update a product's price, depending on availability. Using an updateable ResultSet, this process is relatively straightforward, as shown in Listing 3.

                BigDecimal itemPrice ;
BigDecimal multiplier = new BigDecimal("0.90") ;

Date itemDate ;
Date updateDate = Date.valueOf("2006-1-01") ;

String sql = "SELECT itemNumber, price, stockDate, description FROM bigdog.products" ;

Statement stmt = con.createStatement(
    ResultSet.TYPE_FORWARD_ONLY, 
    ResultSet.CONCUR_UPDATABLE) ;

ResultSet uprs = stmt.executeQuery(sql) ;

while(uprs.next()){
    itemDate = uprs.getDate("stockDate") ;
    if (itemDate.before(updateDate)){
        itemPrice = uprs.getBigDecimal("price") ;
        itemPrice = itemPrice.multiply(multiplier) ;
        uprs.updateBigDecimal("price", itemPrice) ;
        uprs.updateRow() ;
    }
}

uprs.close() ;
stmt.close() ;

This example code reduces the prices of all old items in the bigdog.products table by 90 percent, which you might do in a real business environment to move merchandise that isn't selling. To accomplish this process in your database, you need to first define the appropriate variables to determine both the testing condition and the update process, which requires both Date and BigDecimal values.

The next step is to create the updateable ResultSet, which is the equivalent of using the GET CURSOR command within the ij tool. When writing Java code to create the updateable ResultSet, the only change you need to make is to pass two parameters into the createStatement() method. The first parameter indicates whether the cursor should be unidirectional (TYPE_FORWARD_ONLY) or bidirectional (TYPE_SCROLL_INSENSITIVE). The second parameter indicates whether the cursor is read only (CONCUR_READ_ONLY) or updateable (CONCUR_UPDATABLE). The default, if no parameters are supplied to the createStatement() method, is a unidirectional, read-only ResultSet. Thus you need to supply parameters to the createStatement() method only when writing Java code that works with the Derby database when you need either a scrollable cursor or an updateable ResultSet.

After you have an updateable ResultSet, you iterate through the rows until you find a row you want to modify. In this example, it's any row whose stockDate is before January 1, 2006. Given a row, you call an appropriate updateXXX() method, where XXX is replaced by the appropriate Java data type — for example, BigDecimal. These update methods take two parameters: the column number or name that should be modified and the new value for the column. In this example, you update the price column and call the updateRow() method to indicate that the row updates are complete. At this point, the row updates aren't permanently written to the database; that doesn't happen until the current transaction is committed. However, the updates are generally visible within the current transaction.

Deleting rows

You can remove a row from an updateable ResultSet by calling the deleteRow() method, as shown in Listing 4.

                Date itemDate ;
Date updateDate = Date.valueOf("2006-1-01") ;

String sql = "SELECT itemNumber, price, stockDate, description FROM bigdog.products" ;

Statement stmt = con.createStatement(
    ResultSet.TYPE_FORWARD_ONLY, 
    ResultSet.CONCUR_UPDATABLE) ;

ResultSet uprs = stmt.executeQuery(sql) ;
        
while(uprs.next()){
    itemDate = uprs.getDate("stockDate") ;
    if (itemDate.before(updateDate)){
        uprs.deleteRow() ;
    }
}

uprs.close() ;
stmt.close() ;

In this example, you learn how to remove old items from a product inventory by selectively removing rows from your database. Here, you specifically remove any rows in the bigdog.products table that have a value in the stockDate column that is before January 1, 2006. To do this, you create the updateable ResultSet, iterate the cursor through the rows, and call the deleteRow() method as appropriate. Although the changes are made immediately within the realm of your current transaction, the row isn't removed from the database until the current transaction is committed.

Inserting rows

Of all the SQL data-modification operations, inserting data is the most complex, because new storage must be obtained for the new data, and the new data must be properly encoded. The JDBC standard provides support for inserting data into an updateable ResultSet by following this exact recipe, as shown in Listing 5. The key concept is the introduction of a new, special row in the ResultSet, called the insert row. This row is used to hold the new data while the row is being prepared for insertion into the database.

                int itemNumber = 11 ;
BigDecimal price = new BigDecimal(99.99) ;
Date stockDate = Date.valueOf("2006-12-07") ;
String description = "Board carrying bag" ;

String sql = 
    "SELECT itemNumber, price, stockDate, description FROM bigdog.products" ; 

Statement stmt = con.createStatement(
    ResultSet.TYPE_FORWARD_ONLY, 
    ResultSet.CONCUR_UPDATABLE) ;

ResultSet uprs = stmt.executeQuery(sql) ;

uprs.moveToInsertRow();
uprs.updateInt("itemNumber", itemNumber);
uprs.updateBigDecimal("price", price) ;
uprs.updateDate("stockDate", stockDate) ;
uprs.updateString("description", description) ;
uprs.insertRow();

uprs.close() ;
stmt.close() ;

In this example, you first create the basic data that you want to add to the database. In practice, you might get this data from a user via a Web form or a supplier as part of a purchase order or invoice. Next, you create your updateable ResultSet. Now you're ready to insert your new row, which requires moving the cursor to the special insert row by calling the moveToInsertRow() method. After you've positioned the cursor at the insert row, you update the row's columns to hold your new data values and call the insertRow() method to indicate that the new values should be saved.

If you issue a new query within this transaction, your new row is displayed, because your transaction holds the lock for the new row. Other users won't see the new row until after your transaction is committed. Because you're working with AUTOCOMMIT disabled, your new row isn't permanently saved in the bigdog.products table. One other important point is that you can continue to work with an updateable ResultSet after inserting new rows. To do so, you tell the underlying database cursor to move back to the current row in the ResultSet by calling the moveToCurrentRow() method, which moves the cursor back to the row in the ResultSet where you were before moving to the insert row. In this example, that's before the first row, so a call to the next() method retrieves the first row in the ResultSet.

Back to top

Positioned updates

The flexibility of the JDBC API also permits you to create a cursor that you can use programmatically to perform positioned updates and deletes by issuing the appropriate SQL commands. To utilize this functionality, you need to refer to the database cursor by name, as shown in Listing 6.

                con.setAutoCommit(false) ;

Statement stmt = con.createStatement() ;

stmt.setCursorName("PRODUCTSCURSOR") ;

String usql = "SELECT itemNumber, price, stockDate, description " + "" +
    "FROM bigdog.products FOR UPDATE of price, stockDate" ;

ResultSet uprs = stmt.executeQuery(usql) ;

String updateProductsSQL = 
    "UPDATE bigdog.products SET price = ?, stockDate = CURRENT_DATE " +
    "WHERE CURRENT OF " + uprs.getCursorName() ;

PreparedStatement pstmt = con.prepareStatement(updateProductsSQL) ;

This example looks rather different than the earlier Java examples, because you're embedding SQL commands that perform most of the work. First, you disable AUTOCOMMIT so that changes aren't automatically applied to the database. This is important, because you'll be performing multiple queries (one for each SQL command that's required) within a specific transaction; with AUTOCOMMIT enabled (the default action), each query would be performed within a single transaction.

The next step is to execute the appropriate SQL query to construct your updateable cursor, which requires using the FOR UPDATE clause as described earlier. Next, you construct an SQL command to update the target row by using an SQL UPDATE statement, with a WHERE CURRENT OF clause. This clause is concluded by explicitly retrieving the name of the cursor by calling the getCursorName() method. You can either set this name explicitly by calling the setCursorName() method, as shown in this example, or use the database-defined name; in either approach, the getCursorName() method retrieves the correct name when called on the correct ResultSet.

You may have noticed that Listing 6 never executes the PreparedStatement that you constructed for the SQL UPDATE command. The full listing, which is provided in the source code, shows you how to iterate through the read-only ResultSet and update the appropriate rows by first setting the value in the PreparedStatement and then executing the SQL UPDATE command.

Back to top

Summary

You learned about several different concepts that you can use to dynamically modify data in an Apache Derby database. You first learned how to explicitly work with SQL cursors by using the ij tool to create both a unidirectional and a scrollable SQL cursor, and then to move around within the cursor and selectively perform data-modification operations. Next, you learned how to achieve similar functionality from within a Java program by creating an updateable ResultSet. Finally, you learned how to mix the two approaches to perform positioned updates and deletes.

At this point in this series, you have reached an important stage: You can issue queries, process the results, and dynamically update an Apache Derby database from within a Java program. Your next step is to learn several advanced Java query concepts, after which you can learn how to create and deploy a Java application that includes an embedded Apache Derby database. Stay tuned!

Back to top

Download

Information about download methods

Resources

Learn

• Check out some of other relevant articles in this series:
  • The first article in this series, "Developing with Apache Derby -- Hitting the Trifecta: Introduction to Apache Derby" (developerWorks, February 2006), introduces the Apache Derby database and provides the foundation for many topics in this series.
  • The second article in this series, "Developing with Apache Derby -- Hitting the Trifecta: Database development with Apache Derby, Part 1" (developerWorks, March 2006), introduces the ij tool and demonstrates how to use it to connect to an Apache Derby database.
  • The fourth article in this series, "Developing with Apache Derby -- Hitting the Trifecta: Database development with Apache Derby, Part 3" (developerWorks, May 2006), introduces the concept of executing SQL scripts with Apache Derby and demonstrates how to insert data into tables in a Derby database using the SQL INSERT statement.
  • The fifth article in this series, "Developing with Apache Derby -- Hitting the Trifecta: Database development with Apache Derby, Part 4" (developerWorks, June 2006), introduces the concept of an SQL query and demonstrates how to extract data from a Derby database using the SQL SELECT statement.
  • The ninth article in this series, "Developing with Apache Derby -- Hitting the Trifecta: Java Database development with Apache Derby, Part 1" (developerWorks, December 2006), discusses how to establish a connection to a Derby database from a Java program.
  • The tenth article in this series, "Developing with Apache Derby -- Hitting the Trifecta: Java Database development with Apache Derby, Part 2" (developerWorks, January 2007), discusses how to execute queries on a Derby database from a Java program.
  • The eleventh article in this series, "Developing with Apache Derby -- Hitting the Trifecta: Java Database development with Apache Derby, Part 3" (developerWorks, February 2007), discusses how to use the results of a query on a Derby database from a Java program.
  
  
• Access a number of online Apache Derby project manuals for more detailed information on how to use the Derby database.
  
  
• Learn how to download and install Apache Derby in this Derby Project tutorial.
  
  
• Refer to the Apache Derby developer's reference manual for a wealth of useful information, including the type matching that indicates the allowed conversions to go between SQL data types and their corresponding Java data types.
  
  
• Learn how to properly use the JDBC API on the official Web site for JDBC.
  
  
• Check out the developerWorks Apache Derby project area for articles, tutorials, and other resources to help you get started with Derby today.
  
  
• Learn more about the IBM® Cloudscape™ database, which is built using the Apache Derby code base.
  
  
• Stay current with developerWorks technical events and webcasts.
  
  
• Browse all the Apache articles and free Apache tutorials available in the developerWorks Open source zone.
  
  
• Browse for books on these and other technical topics at the Safari bookstore.
  
  
• Visit the developerWorks Open source zone for extensive how-to information, tools, and project updates to help you develop with open source technologies and use them with IBM's products.
  
  
• Get an RSS feed for this series. (Find out more about RSS.)
  
  

Get products and technologies

• Download Apache Derby.
  
  
• Innovate your next open source development project with IBM trial software, available for download or on DVD.
  
  

Discuss

• Get involved in the developerWorks community by participating in developerWorks blogs.
  
  

About the author

Robert J. Brunner is a Research Scientist at the National Center for Supercomputing Applications and an Assistant Professor of Astronomy at the University of Illinois, Urbana-Champaign. He has published several books and a number of articles and tutorials on a range of topics.

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• SQL cursors
• Updateable ResultSets
• Positioned updates
• Summary
• Download
• Resources
• About the author
• Comments

Next steps from IBM

Spring, JRuby, and Ajax development is easier with WebSphere Application Server - a smart Java 5 and J2EE Web services-based application server.

---

• Try: The no-charge WebSphere Application Server Community Edition is a pre-integrated, lightweight Java 5 application server built on Apache Tomcat and other best-of-breed open source software such as OpenEJB, Apache Axis, and Apache Derby.
• Article: The article leverage the Spring Framework and the WebSphere Application Server to improve your J2EE project productivity.
• Tutorial: See how the free WebSphere Application Server and XML can improve the efficiency of your JRuby on Rails and Ajax development.
• Buy: WebSphere Application Server - Express

My developerWorks community

Dig deeper into Open source on developerWorks

Rate a product. Write a review.

Write a review for Rational products today!

Special offers