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.

                
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

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.

                
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) ;
    }

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.

                
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.

                
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.

                
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

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.
  
  
• 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

• Advanced features of the PreparedStatement
• Streaming data with a PreparedStatement
• Prepared statement metadata
• Summary
• Download
• Resources
• About the author
• Comments

Local resources

• IBM Innovation Center - Austin, TX
• IBM Innovation Center - Chicago, IL
• IBM Innovation Center - Dallas, TX
• IBM Innovation Center - San Mateo, CA
• IBM Innovation Center - Waltham, MA
• Technical Briefings in North America

My developerWorks community

Dig deeper into Open source on developerWorks

Calling all developers

Download no charge Lotus Domino Designer

Special offers