Developing with Apache Derby -- Hitting the Trifecta: Java database development with Apache Derby, Part 3

Modifying data

Learn how to modify an Apache Derby database from a Java™ program. This article builds on the previous two articles in this series to demonstrate how to create and drop tables as well as how to insert, update, and delete data in an Apache Derby database from within your Java applications. This includes using the JDBC Statement and PreparedStatement classes, and using SQL batches for improved performance.

Robert J. Brunner, NCSA Research Scientist, Assistant Professor of Astronomy, University of Illinois, Urbana-Champaign

Robert J. BrunnerRobert 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.



20 February 2007

Also available in Russian Japanese

Introduction

The previous article in this "Java database development with Apache Derby" series showed you how to use the Java Statement object to execute an SQL SELECT query on an Apache Derby database. A query, by design, returns the set of rows that satisfy the query. As a result, you use the Statement object's executeQuery method to perform the query; this method returns the set of rows as a Java ResultSet object.

But many SQL statements, such as SQL Data Definition Language (DDL) commands, don't return a set of rows. Instead, they perform an action, such as creating a table or inserting, updating, or deleting rows. These operations return an integer value that encodes the result of the action, such as how many rows were inserted or deleted, or the possibility of an error. For SQL DDL operations, as shown in Listing 1, the return count is zero for a successful operation. For more information on using SQL DDL statements with the Apache Derby database, read the third article in this series.

Listing 1. Processing SQL DDL statements
...
public class BuildSchema {
...
    private static final String dropProductsSQL = "DROP TABLE bigdog.products" ;

    private static final String createProductsSQL = 
        "CREATE TABLE bigdog.products (" +
            "itemNumber INT NOT NULL," +
            "price DECIMAL(5, 2)," +
            "stockDate DATE," +
            "description VARCHAR(40))" ;

    private static final String productsQuerySQL = 
        "SELECT itemNumber, price, stockDate, description FROM bigdog.products" ;	
 
    static int processStatement(String sql) throws SQLException {
		
        Statement stmt = con.createStatement() ;
        int count = stmt.executeUpdate(sql) ;

        stmt.close() ;
		
        return(count) ;
    }
...
    public static void main(String[] args) {

        try {
            Class.forName(driver) ;
            con = DriverManager.getConnection(url);
...
            processStatement(dropProductsSQL) ;
            processStatement(createProductsSQL) ;

            doProductsQuery(productsQuerySQL) ;

        } catch (SQLException se) {
            printSQLException(se) ;
        }
...

Like the rest of the Java code presented in this article, this example builds on the ThirdQuery example from the previous article. As a result, you see only partial code listings here. (The full code is available in the compressed file available in the Download section.) In this example, you first define several Java String objects that contain the SQL code for dropping and creating the bigdog.products table you've been using throughout this series. You then define a new method, processStatement, which processes any appropriate SQL statement it's given by using the Statement object's executeUpdate method.

This method can be used only for SQL operations that don't return data, such as SQL DDL or SQL INSERT, UPDATE, or DELETE operations. This method sends the SQL to the Apache Derby database, where it's processed, and an integer value is returned. In the case of SQL DDL operations, the return count is zero, so you can ignore it in the main method of this introductory example. In practice, you should verify the value to prevent error conditions when you try to work with data in more complex schemas.

To run the Java programs presented in this article, you need a clean working environment. You can either follow along with the commands displayed in Listing 2, which guide you through the process, or reuse an existing test database you may have from a previous article.

Listing 2. Modifying database schema from Java
rb$ mkdir derbyWork
rb$ cd derbyWork
rb$ unzip ../derby11.zip 
Archive:  ../derby11.zip
  inflating: BuildSchema.java        
  inflating: derby.build.sql         
  inflating: FirstInsert.java        
  inflating: FirstUpdate.java        
  inflating: SecondInsert.java       
  inflating: ThirdInsert.java        
rb$ java org.apache.derby.tools.ij < derby.build.sql             
ij version 10.2
...
ij> 
rb$ javac *.java
rb$ java BuildSchema 

ITEMNUMBER |PRICE   |STOCKDATE |DESCRIPTION                             
------------------------------------------------------------------------

0 rows selected

Upgrade your Apache Derby database

You may notice that the Apache database version displayed in Listing 2 is now 10.2. When a new version of a database is released, database administrators must be careful. Any upgrades should be done with caution, following a well-defined migration path with sufficient regression testing to verify the veracity of your critical business applications. For the purposes of this series of articles, however, you can throw caution to the wind. This new version has a number of additional features and several bug fixes that make the upgrade an easy decision.

These steps are fairly simple:

  1. Create a clean working directory, then expand the example code into this new directory.
  2. Use the Apache Derby ij tool to execute the included Apache Derby script file.
  3. Compile all the Java code included with this article, and then execute the BuildSchema Java program.

As you can see in the example output, the BuildSchema class first drops and then re-creates the bigdog.products table, providing a fresh table that is ready for you to insert new data into.

If you encounter an error in either using the ij tool or compiling or executing any of the Java classes, the most likely culprit is the Java CLASSPATH environmental variable. Be sure this variable contains the requisite Apache Derby JAR files, which you can do by displaying the value of this variable using the echo $CLASSPATH command; the command should produce output similar to the following (note that your Apache Derby installation may change these values slightly):

/opt/Apache/db-derby-10.2.1.6-bin/lib/derby.jar:/
/opt/Apache/db-derby-10.2.1.6-bin/lib/derbytools.jar:.

Data modification statements

The previous example modified the bigdog schema by using SQL DDL statements like CREATE and DROP. You can use a similar process to insert new rows using an SQL INSERT statement, as shown in Listing 3.

Listing 3. Processing SQL INSERT statements
...
public class FirstInsert {
...
    private static final String insertProductsSQL = 
        "INSERT INTO bigdog.products(itemNumber, price, stockDate, description) VALUES" ;

    private static final String[] productsData = 
        {"(1, 19.95, '2006-03-31', 'Hooded sweatshirt')",
         "(2, 99.99, '2006-03-29', 'Beach umbrella')",
         "(3, 0.99, '2006-02-28', '')",
         "(4, 29.95, '2006-02-10', 'Male bathing suit, blue')",
         "(5, 49.95, '2006-02-20', 'Female bathing suit, one piece, aqua')",
         "(6, 9.95, '2006-01-15', 'Child sand toy set')",
         "(7, 24.95, '2005-12-20', 'White beach towel')",
         "(8, 32.95, '2005-12-22', 'Blue-stripe beach towel')",
         "(9, 12.95, '2006-03-12', 'Flip-flop')",
         "(10, 34.95, '2006-01-24', 'Open-toed sandal')"} ;

    private static final String productsQuerySQL = 
        "SELECT itemNumber, price, stockDate, description FROM bigdog.products" ;	
...
    public static void main(String[] args) {
...
            int numRows = 0 ;

            for(String product: productsData){
                numRows += processStatement(insertProductsSQL + product) ;
            }

            System.out.println("\n" + numRows + 
                " rows inserted into bigdog.products table.") ;

            doProductsQuery(productsQuerySQL) ;
...

Explicit queries: A best practice

In the SQL INSERT statement in Listing 3, the column names are explicitly listed (itemNumber, price, and so on). Although you can leave them out, doing so isn't recommended: It opens your code to unexpected and potentially silent errors if someone else modifies the schema unexpectedly by, for example, renaming, adding, or deleting columns. By explicitly listing the columns' names and their order, you minimize your risk from this class of errors.

In this FirstInsert Java program, you replace the SQL DDL statements with an SQL INSERT statement, which is modified to contain the appropriate product data by adding the two Java String objects prior to calling the processStatement method. In this case, each row of data is inserted separately, and you accumulate the row count returned by the processStatement method to determine how many total rows were inserted into the database.

This operation inserts the same 10 rows into the bigdog.products table that you added in earlier articles in this series, but in this case, it does so one row at a time. You could, instead, write one large string that attempts to insert all the data at once — in this case, all 10 rows. However, doing so is a bad idea for two reasons:

  • By inserting one row at a time, you gain a finer level of control over the data in your database. If one row fails to be inserted, it's much easier to track down the problem when it's the only row being manipulated.
  • Using a single large Java String to insert a large number of rows becomes unwieldy and results in code that's harder to maintain. Note that adding multiple Java String objects together in this fashion is not a recommended practice; instead, you should use a StringBuffer. For the demonstration purposes of this article, however, you can follow this simpler approach.

To run this Java code, execute the FirstInsert Java program. Doing so populates your bigdog.products table with 10 new rows, which are displayed as shown in Listing 4.

Listing 4. Using Java code to insert data
rb$ java FirstInsert 

10 rows inserted into bigdog.products table.

ITEMNUMBER |PRICE   |STOCKDATE |DESCRIPTION                             
------------------------------------------------------------------------
1          |19.95   |2006-03-31|Hooded sweatshirt                       
2          |99.99   |2006-03-29|Beach umbrella                          
3          |0.99    |2006-02-28|                                        
4          |29.95   |2006-02-10|Male bathing suit, blue                 
5          |49.95   |2006-02-20|Female bathing suit, one piece, aqua    
6          |9.95    |2006-01-15|Child sand toy set                      
7          |24.95   |2005-12-20|White beach towel                       
8          |32.95   |2005-12-22|Blue-stripe beach towel                 
9          |12.95   |2006-03-12|Flip-flop                               
10         |34.95   |2006-01-24|Open-toed sandal                        

10 rows selected

Prepared statements

In the previous section, you inserted 10 rows of data into an Apache Derby database by creating a Java String that contained the appropriate SQL INSERT statement. This approach, although functional, isn't optimal, because it required that you create a new static INSERT statement every time you want to call the Java Statement object's executeUpdate method.

A more-efficient approach sends the basic INSERT statement to the database and then passes the relevant data for each new row separately as required. This way you can let the database prepare the SQL statement in the same manner in which the Java compiler handles a Java function. Following this analogy, you can then pass new parameters into this prepared SQL statement for processing. Because this approach can lead to a significant performance boost, the JDBC specification provides the PreparedStatement class to enable you to execute an SQL operation multiple times with different input parameters.

Dynamic SQL INSERT statements

Given its different capabilities, using a PreparedStatement is different than using a Statement. First, you need to modify the basic SQL INSERT statement by using question mark characters (?) to indicate where an input parameter will be supplied. For example, VALUES(?, ?, ?, ?) indicates that four input parameters will be supplied to complete the VALUES clause of your SQL INSERT statement. You pass this modified String as input to the Connection object's prepareStatement method, which allows the Apache Derby database to precompile the SQL for faster processing.

Second, you must supply values for each of the input parameters. You do this by calling a setXXX method for each input parameter. Two important points about this class of methods:

  • XXX is replaced by the data type for the parameter you're sending to the database; for example, setInt indicates you're sending an integer, and setDate indicates you're sending a Date object.
  • These methods take two parameters: the ordinal number of the input parameter and the actual value to be used. By including an ordinal value, which indicates the input parameter that's being set, you don't have to set the input parameters in a particular order.

Although using a PreparedStatement may sound confusing, it's really simple, as shown in Listing 5.

Listing 5. Using prepared statements for SQL INSERT operations
...
public class SecondInsert {
...
    private static final String insertProductsSQL = 
        "INSERT INTO bigdog.products(itemNumber, price, stockDate, description) " + 
            "VALUES(?, ?, ?, ?)" ;

    private static final int[] itemNumbers = {1, 2, 3, 4, 5, 6, 7, 8, 9, 10} ;

    private static final BigDecimal[] prices = 
        {new BigDecimal(19.95), new BigDecimal(99.99), new BigDecimal(0.99), 
         new BigDecimal(29.95), new BigDecimal(49.95), new BigDecimal(9.95), 
         new BigDecimal(24.95), new BigDecimal(32.95), 
         new BigDecimal(12.95), new BigDecimal(34.95)} ;

    private static final Date[] dates = 
        {Date.valueOf("2006-03-31"), Date.valueOf("2006-03-29"), 
         Date.valueOf("2006-02-28"), Date.valueOf("2006-02-10"), 
         Date.valueOf("2006-02-20"), Date.valueOf("2006-01-15"),
         Date.valueOf("2005-12-20"), Date.valueOf("2005-12-22"), 
         Date.valueOf("2006-03-12"), Date.valueOf("2006-01-24")} ;

    private static final String[] descriptions = 
        {"Hooded sweatshirt", "Beach umbrella", "", "Male bathing suit, blue", 
         "Female bathing suit, one piece, aqua", "Child sand toy set", 
         "White beach towel", "Blue-stripe beach towel", "Flip-flop", 
         "Open-toed sandal"} ;

    private static final String productsQuerySQL = 
        "SELECT itemNumber, price, stockDate, description FROM bigdog.products" ;	
...
    static void insertData(String sql) throws SQLException {

        int numRows = 0 ;

        PreparedStatement stmt = con.prepareStatement(sql) ;

        for(int itemNumber: itemNumbers){
            stmt.setInt(1, itemNumbers[itemNumber - 1]) ;
            stmt.setBigDecimal(2, prices[itemNumber - 1]) ;
            stmt.setDate(3, dates[itemNumber - 1]) ;
            stmt.setString(4, descriptions[itemNumber - 1]) ;

            numRows += stmt.executeUpdate() ;
        }

        System.out.println("\n" + numRows + 
            " rows inserted into bigdog.products table.") ;

        stmt.close() ;
    }

    public static void main(String[] args) {
...
            insertData(insertProductsSQL) ;
            doProductsQuery(productsQuerySQL) ;
...

This example code starts by defining Java arrays that contain the data to be inserted. Although this is a useful demonstration tactic, in a production environment you will most likely read this data in from a file or retrieve it as the result of a calculation or from a user's input. The other modifications basically relate to changing the code from using a Statement object to using a PreparedStatement object. This includes:

  • Creating the PreparedStatement object.
  • Setting the relevant input parameters, which include a Java int, BigDecimal, Date, and String.
  • Executing the INSERT statement by using the executeUpdate method.

To see this concept in action, first clear out the existing bigdog.products table, which you can easily do by running the BuildSchema program, and then run the SecondInsert program, as shown in Listing 6 (note that the output has been compressed in this code listing to save space).

Listing 6. Executing Java prepared INSERT statements
rb$ java BuildSchema

ITEMNUMBER |PRICE   |STOCKDATE |DESCRIPTION                             
------------------------------------------------------------------------

0 rows selected
rb$ java SecondInsert

10 rows inserted into bigdog.products table.

ITEMNUMBER |PRICE   |STOCKDATE |DESCRIPTION                             
------------------------------------------------------------------------
1          |19.94   |2006-03-31|Hooded sweatshirt                       
...     
10         |34.95   |2006-01-24|Open-toed sandal                        

10 rows selected

Dynamic updates and selects

As was the case with the Statement object, the PreparedStatement object can be used for other SQL operations in addition to SQL INSERT operations. For example, you can selectively UPDATE, DELETE, and even SELECT data from an Apache Derby database by using an appropriately set PreparedStatement object. In Listing 7, you use a PreparedStatement to update rows in the bigdog.products table, and then you select these rows by using a different PreparedStatement.

Listing 7. Using prepared statements for SQL UPDATE and DELETE operations
...
public class FirstUpdate {
...
    private static final String updateProductsSQL = 
        "UPDATE bigdog.products SET price = price * 1.25, " + "" +
            "stockDate = CURRENT_DATE WHERE price > ?" ; 

    private static final String productsQuerySQL = 
        "SELECT itemNumber, price, stockDate, description " + 
            "FROM bigdog.products WHERE price > ?" ;	
...
    static void doProductsQuery(String sql) throws SQLException {
...
        PreparedStatement stmt = con.prepareStatement(sql) ;
        BigDecimal threshold = new BigDecimal(40.00) ;

        stmt.setBigDecimal(1, threshold) ;		

        ResultSet rs = stmt.executeQuery() ;
...
    }

    static void updateData(String sql) throws SQLException {

        PreparedStatement stmt = con.prepareStatement(sql) ;
        BigDecimal threshold = new BigDecimal(40.00) ;

        stmt.setBigDecimal(1, threshold) ;		

        int numRows = stmt.executeUpdate() ;

        System.out.println("\n" + numRows + " rows updated in bigdog.products table.") ;

        stmt.close() ;
    }

    public static void main(String[] args) {
...
            doProductsQuery(productsQuerySQL) ;						
            updateData(updateProductsSQL) ;
            doProductsQuery(productsQuerySQL) ;
            ...

In the FirstUpdate class, you first define the two SQL statements that will be passed in to create the two PreparedStatement objects. First is the SQL UPDATE statement, which updates the price of any row that already has a price greater than some threshold value. Second is the SQL SELECT statement, which selects any row that has a price greater than some threshold value. You define two new methods to separately perform these two operations, and then you perform the query, modify the data, and redo the same query to verify the results of the SQL UPDATE operation.

Listing 8. Executing Java prepared UPDATE and SELECT statements
rb$ java FirstUpdate

ITEMNUMBER |PRICE   |STOCKDATE |DESCRIPTION                             
------------------------------------------------------------------------
2          |99.98   |2006-03-29|Beach umbrella                          
5          |49.95   |2006-02-20|Female bathing suit, one piece, aqua    

2 rows selected

2 rows updated in bigdog.products table.

ITEMNUMBER |PRICE   |STOCKDATE |DESCRIPTION                             
------------------------------------------------------------------------
2          |124.97  |2006-11-03|Beach umbrella                          
5          |62.43   |2006-11-03|Female bathing suit, one piece, aqua    

2 rows selected

Batch operations

Using a PreparedStatement improves the flexibility and performance characteristics of your Java code, but it still isn't optimal. This is due to the fact that each SQL INSERT (or other SQL operation) is performed in a separate transaction. As you learned in the second article in this series, a transaction is a logical unit of work that Apache Derby uses to guarantee the consistency of your database. Either all operations in an individual transaction are successfully completed or the effects of all the operations are undone or, more formally, rolled back to the previous database state.

By design, any time you call the executeUpdate method (or the executeQuery method), the operation is treated as a single transaction. This results in a performance degradation due to the overhead of setting up and finalizing multiple transactions. A better approach is to send a batch of SQL statements to the database and execute them all together. The JDBC specification supports a batch capability to facilitate this performance boost, as demonstrated in Listing 9.

Listing 9. Using batch SQL INSERT statements
...
public class ThirdInsert {
...
 private static final String insertProductsSQL = 
   "INSERT INTO bigdog.products(itemNumber, price, stockDate, description) " + 
       "VALUES(?, ?, ?, ?)" ;
...
 static void batchInsertData(String sql) throws SQLException {

   PreparedStatement stmt = con.prepareStatement(sql) ;

   for(int itemNumber: itemNumbers){
       stmt.setInt(1, itemNumbers[itemNumber - 1]) ;
       stmt.setBigDecimal(2, prices[itemNumber - 1]) ;
       stmt.setDate(3, dates[itemNumber - 1]) ;
       stmt.setString(4, descriptions[itemNumber - 1]) ;
       stmt.addBatch() ;
   }

   int numRows = 0 ;	
   int[] counts = stmt.executeBatch() ;

   for(int count: counts){
       numRows += count ;
   }
        
   System.out.println("\n" + numRows + 
       " rows inserted into bigdog.products table.") ;

   stmt.close() ;
 }

 public static void main(String[] args) {
...
       con.setAutoCommit(false) ;
            
       batchInsertData(insertProductsSQL) ;
       doProductsQuery(productsQuerySQL) ;
            
       con.commit() ;
            
   }catch(BatchUpdateException bue) {
       try{
          con.rollback() ;
        		
          System.err.println("Batch Update Exception: Transaction Rolled Back") ;
          printSQLException((SQLException)bue) ;
       }catch(SQLException se){
          printSQLException(se) ;
        	}
...

The main change in ThirdInsert from the previous two SQL INSERT example codes is the new batchInsertData method, which uses the addBatch method to add each fully defined PreparedStatement to a batch of statements. You execute all these statements together within a single transaction by calling the executeBatch method. For a large number of SQL statements, this approach is significantly faster, because the Apache Derby database needs to set up only one transaction for every batch of statements.

The executeBatch method returns an array of integers, where each element in the array corresponds to the number of rows that were inserted, updated, or deleted by the corresponding statement in the batch. In this example, you have 10 statements in your batch, so the array contains 10 integers; you iterate over them to get the total number of rows that were modified in the batch. This point is important: It clearly indicates that you can include only SQL data-modification commands in your SQL batch, such as CREATE, DROP, INSERT, UPDATE, and DELETE operations. If you attempt to include in a batch an SQL command like a SELECT query, which returns anything other than a single update count, the executeBatch method will throw an exception.

Calling the batchInsertData method also requires several modifications to your main method:

  1. Disable autocommit mode for the current database connection before setting up the batch. Doing this prevents the SQL operations in your batch from being automatically applied to the database, which may not be what you intended if something goes wrong.
  2. Add an explicit commit operation after the batch has been successfully processed.
  3. Add an exception handler for a BatchUpdateException, which will be thrown if the database runs into a problem while processing your batch.

In this simple example, you roll back the current transaction, which undoes all database modifications that may have occurred within the batch. In a production environment, you can call the getUpdateCount method on a BatchUpdateException; this method returns an array whose values indicate whether every SQL operation in your batch succeeded or failed, thereby allowing you to more easily diagnose and fix any problems.

To test the batch-insert example, first clean out the bigdog.products table, and then execute the ThirdInsert program, as shown in Listing 10.

Listing 10. Executing Java batch-insert statements
rb$ java BuildSchema

ITEMNUMBER |PRICE   |STOCKDATE |DESCRIPTION                             
------------------------------------------------------------------------

0 rows selected
rb$ java ThirdInsert

10 rows inserted into bigdog.products table.

ITEMNUMBER |PRICE   |STOCKDATE |DESCRIPTION                             
------------------------------------------------------------------------
1          |19.94   |2006-03-31|Hooded sweatshirt                       
...
10         |34.95   |2006-01-24|Open-toed sandal                        

10 rows selected

Summary

In this article, you learned how to use a Java program to modify the contents of an Apache Derby database. This included using the Statement object to execute SQL DDL and SQL INSERT operations. Then you learned how to use a PreparedStatement to execute SQL data-modification commands that are dynamically constructed within your Apache Derby database at run time. Finally, you saw how to construct and execute a batch of SQL commands to improve the performance of Java applications that perform a large number of SQL data-modification commands. Future articles will build on these basic skills and demonstrate how to execute more complex database operations from within your Java application.


Download

DescriptionNameSize
Derby SQL script and Java code for this articlederby11.zip8KB

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Open source on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Open source, Java technology, Information Management
ArticleID=196791
ArticleTitle=Developing with Apache Derby -- Hitting the Trifecta: Java database development with Apache Derby, Part 3
publish-date=02202007