Skip to main content

skip to main content

developerWorks  >  Open source | Java technology | Information Management  >

Developing with Apache Derby — Hitting the Trifecta: Java database development with Derby, Part 5

Advanced prepared statements

developerWorks
Document options

Document options requiring JavaScript are not displayed

Sample code


Rate this page

Help us improve this content


Level: Intermediate

Robert J. Brunner (rb@ncsa.uiuc.edu), NCSA Research Scientist, Assistant Professor of Astronomy, University of Illinois, Urbana-Champaign

22 May 2007

Learn about the advanced features available when using prepared statements with your Apache Derby database application. First, use the ij tool to dynamically execute a PreparedStatement to enable rapid prototyping. Next, stream data into a PreparedStatement to efficiently insert large quantities of data into a particular column. Then use the ParameterMetaData object to obtain information, including vendor-specific implementation details, about the individual parameters in your PreparedStatement.

Advanced features of the PreparedStatement

In a previous article in this series, "Developing with Apache Derby -- Hitting the Trifecta: Java database development with Apache Derby, Part 3: Modifying data" (developerWorks, February 2007), you learned about the JDBC PreparedStatement object. By using a PreparedStatement, you can dynamically modify your SQL statement at run time. With this technique, you can insert large quantities of information by reusing the same SQL statement, or you can selectively query, delete, or update rows in a database by changing the value of a parameter.

The PreparedStatement interface has been modified with subsequent JDBC revisions, providing additional functionality, which this article explores in more detail. First, however, you learn how to work with a PreparedStatement from within the ij tool. Although you may not realize it, the ij program is a powerful tool that lets you dynamically try different SQL statements against an Apache Derby database without compiling or debugging Java code. This dynamic nature allows you to quickly connect to a database, try a series of SQL commands, and modify them until your desired action is performed — in a manner similar to using a scripting language like Python. You may prefer to develop industrial-grade applications by using the Java language, but the ability to quickly prototype and evaluate is an important skill that can often result in faster development times.

Fortunately, working with a PreparedStatement using the ij tool is straightforward, as shown in Listing 1. First, you need to create a working directory and extract the included source code (see the Download section). Next, you compile the Java source code presented later in this article, and then you can execute a build script to properly initialize a demo Apache Derby database.


Listing 1. Using the ij tool to work with prepared statements

                
rb$ mkdir derbyWork
rb$ cd derbyWork/
rb$ unzip ../derby13.zip 
Archive:  ../derby13.zip
  inflating: PreparedMetaData.java   
  inflating: PreparedStream.java     
  inflating: bio.txt                 
  inflating: derby.build.sql         
  inflating: p-rbrunner.jpg          
rb$ javac *.java
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> PREPARE pstmt AS 
    'INSERT INTO bigdog.products(itemNumber, price, stockDate, description) 
     VALUES(?, ?, ?, ?)' ;
ij> EXECUTE pstmt USING
    'VALUES (11, 88.99, ''2007-02-28'', ''Board rack'')' ;
1 row inserted/updated/deleted
ij> EXECUTE pstmt USING
    'VALUES (12, 199.99, ''2007-01-14'', ''Board bag, black'')' ;
1 row inserted/updated/deleted
ij> COMMIT ;
ij> REMOVE pstmt ;
ij> EXECUTE pstmt USING
    'VALUES (13, 1.99, ''2007-01-21'', ''Board keychain'')' ;
IJ ERROR: Unable to establish prepared statement PSTMT
ij> PREPARE pstmt AS
    'SELECT itemNumber, price, stockDate, description 
     FROM bigdog.products WHERE itemNumber > ?' ;
ij> EXECUTE pstmt USING 'VALUES (10)' ;
ITEMNUMBER |PRICE  |STOCKDATE |DESCRIPTION                             
-----------------------------------------------------------------------
11         |88.99  |2007-02-28|Board rack                              
12         |199.99 |2007-01-14|Board bag, black                        
2 rows selected
ij> REMOVE pstmt ;
ij> EXIT ;

After you've initialized a demo database, you can use the Apache Derby ij tool to connect to the test database and issue SQL commands. First, you should turn off autocommit mode so the results of each SQL statement aren't automatically committed to the database. This is generally useful when you use the ij tool to dynamically evaluate SQL commands, because it simplifies the undo process. In this case, you want to disable autocommit mode, because you reuse the same JDBC PreparedStatement more than once.

To create a new PreparedStatement within the ij tool, you use the PREPARE command as shown in Listing 1. Doing so defines a new PreparedStatement called pstmt that can be used to insert new data into the bigdog.products table. After this new PreparedStatement has been created, you can use it to insert new data into the products table by using the EXECUTE command. In this example, you first insert two new rows of data, and then use the COMMIT command to save the two new rows in the database.

When you're done using a PreparedStatement object, you need to inform that database that the internal database resources allocated for the PreparedStatement can be released. With the ij tool, you can accomplish this by using the REMOVE command to remove the PreparedStatement. If you try to reuse this PreparedStatement after it's been removed, an error is generated, because the internal resources are no longer available.

As shown in this example, you can also create a PreparedStatement to perform dynamic queries by using the ij tool. To do so, define a new PreparedStatement by writing the SELECT query as appropriate. To execute the new query, you pass in the appropriate values; in this case, you see the two new rows that you inserted previously.


Back to top


Streaming data with a PreparedStatement

JDBC 4.0 and Apache Derby
The JDBC 4.0 specification has been released, and it includes several improvements that directly relate to the use of a PreparedStatement. Select JDBC 4.0 features can be used with Apache Derby, but only when using the Java Platform, Standard Edition (Java SE) 6 virtual machine. In addition, the Apache Derby JDBC drivers that you can currently download must be recompiled using a Java SE 6 version compiler to take advantage of any new functionality that comes with the specification. By default, the Apache Derby JDBC driver that you currently download is compiled to support the JDBC 3.0 specification on a Java Development Kit (JDK) 1.4 or 1.5 virtual machine.

Previously, you used a PreparedStatement to insert data into an Apache Derby database from within both a Java program and the ij tool. But in some cases, you may need to insert a large quantity of data into a column — not just an integer value or a short, descriptive character string. For example, you may want to insert a picture for a product or a long product description. To simplify inserting bulk quantities of data into a database column, the JDBC specification supports three types of streams that you can use to directly stream data into a database column:

  • Binary streams, for uninterpreted bytes of data
  • ASCII streams, for ASCII character data
  • Unicode streams, for Unicode character data

To use a stream, you set up your PreparedStatement as normal; but rather than call a setXXX method to set the value of the appropriate parameter, you call the appropriate setXXXStream method, as shown in Listing 2.


Listing 2. Using a prepared statement to stream data into an Apache Derby database

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

    Statement stmt = con.createStatement() ;
    int counts = stmt.executeUpdate(createTableSQL) ;
    con.commit() ;   

    File tFile = new File("bio.txt") ;
    InputStream tIn = new java.io.FileInputStream(tFile);
       
    int tFileLength = (int) tFile.length() ;
       
    File bFile = new File("p-rbrunner.jpg") ;
    int bFileLength = (int) bFile.length() ;
    InputStream bIn = new java.io.FileInputStream(bFile);
       
    PreparedStatement pstmt = con.prepareStatement(insertInfoSQL) ;
    pstmt.setInt(1, 1) ;
    pstmt.setBinaryStream(2, bIn, bFileLength) ;
    pstmt.setAsciiStream(3, tIn, tFileLength) ;
      
    pstmt.execute();
    con.commit() ;
       
    showResults(infoQuerySQL) ;
       
    stmt = con.createStatement() ;
    counts = stmt.executeUpdate(dropTableSQL) ;
    con.commit() ;
       
} catch (SQLException se) {
    printSQLException(se) ;
    }

SQL data types for streaming data
Although you use the LONG VARCHAR and related data types in this article, a more seasoned SQL developer would likely choose to use the SQL BLOB and CLOB data types, which are supported by the JDBC specification and the Apache Derby database. A future article will discuss these data types and detail their use with Java streams.

You can use a stream with a PreparedStatement by creating the relevant Java stream and setting the appropriate INPUT parameter for the PreparedStatement, as shown in Listing 2. As the full example code demonstrates (available in its entirety from the Download section), you need a suitable table. In this case, you first create a new, temporary table called authors that has three columns:

  • An integer value for identification
  • A pic column to hold a picture
  • A bio column to hold a biography

The last two columns are declared as SQL types LONG VARCHAR FOR BIT DATA and LONG VARCHAR, respectively. By using these column types, you don't need to have the entire stream fit into memory, which is a requirement if you use a different SQL data type, such as CHAR, VARCHAR, or one of their variants.

The other important item to follow when using a stream is that you need to first open the stream as appropriate and determine its length. The length is required for JDBC 3.0 or earlier drivers (note that in JDBC 4.0, this restriction has been relaxed). In the previous example, the files are first opened, and a Java InputStream is attached to them. The file's length is recorded, and then you pass the stream and file length into the appropriate setXXXStream method.

After inserting this data by using two Java streams, you then query the table before deleting it. To view the resulting action, execute the PreparedStream.java file, as shown in Listing 3.


Listing 3. Streaming data with a prepared statement

                
rb$ java PreparedStream 
     
ID        |PIC       |BIO       
------------------------------------
1         |Robert J. |[B@1eb50b 
     
1 rows selected

When you run this example, the Java code creates a temporary authors table, opens two files (bio.txt and p-rbrunner.jpg, included with the article's source code), inserts one row into the authors table by reading data from these two files, displays this row (which is abbreviated due to the large quantity of data in the last two columns), and then drops this table to reclaim the space in the database. By using streams with a PreparedStatement, you can quickly insert large quantities of complex data into your Apache Derby database.



Back to top


Prepared statement metadata

In previous articles, you learned about JDBC metadata support for both the database connection and the query results. With the JDBC 3.0 specification, the PreparedStatement object was also given metadata support, which is accessible via the ParameterMetaData object. This object exposes a number of different methods that let a Java program determine various features of the PreparedStatement, as shown in Listing 4.


Listing 4. Accessing the metadata for a prepared statement

                
try {
    Class.forName(driver) ;
    con = DriverManager.getConnection(url);
    
    PreparedStatement pstmt = con.prepareStatement(insertInfoSQL) ;
    pstmt.setInt(1, 1) ;
    pstmt.setBinaryStream(2, bIn, bFileLength) ;
    pstmt.setAsciiStream(3, tIn, tFileLength) ;
    
    ParameterMetaData pmd = pstmt.getParameterMetaData();
    int count = pmd.getParameterCount();
    System.out.println("\nThis PreparedStatement contains " + count + " parameters.\n");
    System.out.printf("%-10s|%27s|%24s|\n", "Argument","Type", "Class") ;
    String line = "--------------------------------" ;
    System.out.println(line + line);
    
    for (int i=1; i <= count; i++){
        System.out.printf("%-10d", i) ;
        System.out.printf("%28s", pmd.getParameterTypeName(i));
        System.out.printf("%25s\n", pmd.getParameterClassName(i));
    }
    System.out.println(line + line + "\n");
} catch (SQLException se) {
    printSQLException(se) ;
}

In this example, you first define the PreparedStatement as you did earlier in Listing 2. In this case, however, you don't execute the PreparedStatement, but use it to access the appropriate metadata by calling the getParameterMetaData() method on your new PreparedStatement. The ParameterMetaData object supports nine different methods that you can use to determine the number of parameters used by a PreparedStatement, as well as details about each specific parameter, in which case you must pass the parameter number into the appropriate method. In this example, you use the getParameterTypeName() and getParameterClassName() methods to retrieve the SQL data types and Java classes used to handle the parameter.

To work with metadata for a JDBC PreparedStatement, you can execute the PreparedMetaData.java file, as shown in Listing 5.


Listing 5. Viewing prepared statement metadata

                
rb$ java PreparedMetaData

This PreparedStatement contains 3 parameters.

Argument  |                       Type|                   Class|
----------------------------------------------------------------
1                              INTEGER        java.lang.Integer
2            LONG VARCHAR FOR BIT DATA                   byte[]
3                         LONG VARCHAR         java.lang.String
----------------------------------------------------------------

The Apache Derby JDBC driver maps the parameters for the PreparedStatement from their Java data types to the appropriate SQL data types. In this case, the binary data is handled as an array of bytes, whereas the character data is handled as a String. You could extend this example to provide additional details of the different parameters by using some of the other methods exposed by the ParameterMetaData objects, including:

  • isNullable() to determine if a parameter can hold NULL values.
  • isSigned() to determine if a parameter can hold signed numerical values.
  • getPrecision() to determine the number of decimal digits a parameter can hold.
  • getScale() to determine the number of decimal digits after the decimal point that a parameter will support.
  • getParameterType() to determine the SQL type for a parameter.
  • getParameterMode() to determine the mode for a given parameter.

By using ParameterMetaData correctly, you can ensure that your application correctly operates across different versions of the Apache Derby Database and also check how your application will scale to a more powerful, enterprise-class database like the IBM DB2 Universal Database.



Back to top


Summary

In this article, you learned how to more effectively use PreparedStatement objects to support more advanced queries. This included using the ij tool to prepare, execute, and remove a PreparedStatement, which can be useful when you're prototyping different approaches. Next, you were introduced to the concept of streaming data into a JDBC application, in this case a PreparedStatement parameter, to more efficiently insert data into an Apache Derby database. Finally, you learned about the ParameterMetaData object and how it can be used to obtain metadata about different PreparedStatement parameters. The next step is to bundle everything you've learned into a single Java application that includes an embedded Apache Derby database.


Back to top


Download

DescriptionNameSizeDownload method
Derby SQL script and Java code for this articlederby13.zip4KBHTTP
Information about download methods


Resources

Learn

Get products and technologies

Discuss


About the author

Robert J. Brunner

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.




Rate this page


Please take a moment to complete this form to help us better serve you.



 


 


Not
useful		Extremely
useful
 


Share this....

digg Digg this story del.icio.us del.icio.us Slashdot Slashdot it!



Back to top


Cloudscape, DB2, DB2 Universal Database, IBM, and the IBM logo are registered trademarks of IBM in the United States, other countries or both. Java and all Java-based trademarks are trademarks of Sun Microsystems, Inc. in the United States, other countries, or both. Other company, product, or service names may be trademarks or service marks of others.