JDBC query execution: an overview

The JDBC API is designed in a hierarchical manner, where an object of one type contains objects of other types. For example, you make a database connection via a JDBC Connection; and to send an SQL query to a database, you create a JDBC Statement object from the appropriate Connection object. Although it may seem logical, this has an important effect: If a database connection is closed for some reason, any database objects that it contains are also closed. This hierarchy extends one more level, because the results of a query are accessed by using a ResultSet object, which is contained within the appropriate Statement object. This hierarchy is demonstrated graphically in Figure 1.

Although you won't do so in this article, if you re-execute a JDBC query, any underlying ResultSet object is reused. This means you need to first process a query result completely before reusing a JDBC Statement -- for example, by reissuing a database query -- or you will lose any results from the previous query.

To execute a query against an Apache Derby database, you need to first have a properly initialized database. If you've been following previous articles in this

, you probably already have a database that you can use for the rest of this article. If not, or if you want to start with a clean slate, you can use the derby.build.sql script file shown in Listing 1.

rb$ mkdir derbyWork
rb$ cd derbyWork/
rb$ unzip ../derby10.zip Archive:  ../derby10.zip
  inflating: derby.build.sql         
  inflating: FirstQuery.java         
  inflating: SecondQuery.java        
  inflating: ThirdQuery.java         
rb$ java org.apache.derby.tools.ij < derby.build.sql > derby.build.out 2> derby.build.err
rb$ javac *.java
rb$ ls
FirstQuery.class        ThirdQuery.class        derby.build.sql
FirstQuery.java         ThirdQuery.java         derby.log
SecondQuery.class       derby.build.err         test
SecondQuery.java        derby.build.out

As shown in

, you should first create a new working directory and then extract the source code file that is included with this article (see the

section at the end of this article). The next step is to create an Apache Derby database that is properly initialized, which is easily done by running an SQL script from the Apache Derby ij tool. Finally, compile the three included Java source code files. Although the results aren't explicitly shown (because some of them generate a lengthy output), you can execute each of these Java applications, for example, by entering java FirstQuery at the command prompt. The rest of this article presents the source code for each of these three examples.

Back to top

Executing a query

As you learned in the previous section, executing a database query involves three main concepts:

• A database connection
• A query statement
• The query results

To use these objects, you need to first import them into your application, as shown in Listing 2.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.Statement;
import java.sql.ResultSet;

public class FirstQuery {
    private static final String driver = "org.apache.derby.jdbc.EmbeddedDriver" ;
    private static final String url = "jdbc:derby:test" ;
    private static final String qry = 
        "SELECT itemNumber, price, stockDate, description FROM bigdog.products" ;	

The code snippet in

first explicitly imports all necessary Java classes and then defines several important constants you'll use in the rest of your first database query program. Most of this should look familiar; you saw much of it in the

. The new parts are the explicit inclusion of the import statements for the Statement and ResultSet JDBC interfaces, and the query string that you execute later in the program. This query explicitly lists the four columns in the bigdog.products table, which is a best practice (see the sidebar

). Doing so eliminates a number of potential bugs that might appear if the underlying database was modified unexpectedly.

The operations required to perform a database query from within a Java program are relatively straightforward, as shown in Listing 3, which presents the doQuery method for the FirstQuery.java program. Note that this method's signature includes the throws SQLException clause, which indicates that any SQL exceptions generated from within this method must be handled by the calling code. (You'll see how to properly accomplish this in

.)

static void doQuery(Connection con) throws SQLException {
        
    SQLWarning swarn = null ;
            
    Statement stmt = con.createStatement() ;
    ResultSet rs = stmt.executeQuery(qry) ;
        
    while (rs.next()) {
            
        System.out.println("Item Number: " + rs.getString("itemNumber")) ;
        System.out.println("Item Price:  " + rs.getString("price")) ;
        System.out.println("Stock Date:  " + rs.getString("stockDate")) ;
        System.out.println("Description: " + rs.getString("description") + '\n') ;
            
        swarn = rs.getWarnings() ;
            
        if(swarn != null){
            printSQLWarning(swarn) ;
        }
    }
    rs.close() ;
    stmt.close() ;
}

In the doQuery method, you first create a new JDBC Statement object by using the Connection object's createStatement method. You use the Statement object's executeQuery method to send the query string to the Apache Derby database where it's executed. You access the query results in your Java program by using the ResultSet implementation provided by the Apache Derby embedded JDBC driver package.

The ResultSet interface provides a number of techniques for accessing the data returned from your query. In the doQuery method, you use the iteration technique, whereby you use the next method to loop through each row of the data that results from the query being executed. Initially, this iterator is positioned before the first row, such that after it's first called, the iterator points to the first row. Each time through the while loop, the next row in the resulting data set is accessed until the last row is reached. After the last row has been processed, the iterator becomes null, because you've moved beyond the last row; this causes the loop to terminate.

Within the loop, you access the four columns for each row by using the ResultSet object's getString method; this method retrieves the column value as a Java String object so you can print the row values directly. The getString method can access columns in two ways: by using the column's ordinal number in the query or by using the column's name. For example, with the previous query, getString(1) is equivalent to getString("itemNumber"). As usual, being explicit is a best practice and is generally preferred because it reduces the chances of subtle bugs appearing in your application. In addition, columns are accessed starting with 1, not 0, which can be another source for problems -- this is further impetus for being explicit.

Although it's unlikely that any warnings will be generated, the doQuery method explicitly checks for new warnings on the ResultSet object after you access each new row. This is necessary, because any previous warnings on a ResultSet are cleared each time a new row is accessed. You don't do so in this method, but you also could check for SQL warnings on the Statement object in the same manner.

To use the doQuery method, you need to establish a database connection and call the method from within a try ... catch block, as shown in Listing 4.

public static void main(String[] args) {
    Connection con = null ;

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

        SQLWarning swarn = con.getWarnings() ;

        if(swarn != null){
            printSQLWarning(swarn) ;
        }

        doQuery(con) ;

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

As you can see, by encapsulating the query-execution code into a separate method, only a single modification is required to the main method of the database connection code presented in the

. After the database connection is established and a check for possible SQL warnings is made, you call the doQuery method, which handles all the query-processing details. In the rest of this article, you modify the logic in the doQuery method to learn more about executing select queries from within a Java program to a Derby database.

Back to top

Extracting typed data

In the previous section, you saw how to issue a query and extract the data as character strings, which is perfectly acceptable if you're merely printing the data. But most of the time, you want to apply business logic to the data, which requires that the data be extracted to an appropriate data type. For example, you may want to pull out price as a numerical currency data type and stockDate as a date data type.

Fortunately, the JDBC API provides methods to extract data as an appropriate data type. Formally, the rules for type matching are lengthy, but because the Apache Derby database is written in the Java language, the type matching is much more straightforward. The full syntax guidelines are available in the Apache Derby reference manual, which is listed in the

section of this article. Generally, however, the type matching is what you expect, as shown in Listing 5, where the doQuery method for the SecondQuery.java program is presented.

static void doQuery(Connection con) throws SQLException {
    int itemNumber = -1 ;
    BigDecimal price = null ;
    Date stockDate = null ;
    String description = null ;
    
    BigDecimal threshold = new BigDecimal(40.00) ;
    
    SQLWarning swarn = null ;
    Statement stmt = con.createStatement() ;
    ResultSet rs = stmt.executeQuery(qry) ;
    
    while (rs.next()) {
        
        itemNumber = rs.getInt("itemNumber") ;
        price = rs.getBigDecimal("price") ;
        stockDate = rs.getDate("stockDate") ;
        description = rs.getString("description") ;
        
        swarn = rs.getWarnings() ;
        
        if(swarn != null){
            printSQLWarning(swarn) ;
        }else{
            if((itemNumber < 6)&&(price.compareTo(threshold) > 0)){
                System.out.println("Item Number: " + itemNumber) ;
                System.out.println("Item Price:  " + price) ;
                System.out.println("Stock Date:  " + stockDate) ;
                System.out.println("Description: " + description + '\n') ;
            }
        }
    }
    rs.close() ;
    stmt.close() ;
}

This example code shows the modified doQuery method, which now extracts the query results to the appropriate data types. The price column is extracted as a java.math.BigDecimal data type, and the stockDate column is extracted as a java.sql.Date data type. Although not shown in this code listing (but part of the full source code listing in the file SecondQuery.java), you need to include the appropriate import statements so that you can use these two classes in your program.

In this new implementation of the doQuery method, you iterate through the rows in your query result set, but now each column is extracted to the relevant data type. The results are printed out as before, but this time you print only those rows that have an itemNumber column value less than 6 and whose price is less than some threshold (US$40.00 in this example). A ResultSet can be a complex object with a number of important methods. As the next section demonstrates, a ResultSet has its own metadata, which you can access by using the ResultSetMetaData object.

Back to top

Working with query metadata

The previous two Java applications generated lengthy listings for the query results, even when only several rows were selected. Earlier in this series of articles, you used the Apache Derby ij tool to perform database operations (you also used it at the start of this article to execute the included SQL script). The ij tool produces nicely formatted query output, which you can also do by using the ResultSetMetaData object and a formatted print statement, as shown in Listing 6, which presents the doQuery method for the ThirdQuery.java program.

static void doQuery(Connection con) throws SQLException {
    int itemNumber = -1 ;
    BigDecimal price = null ;
    Date stockDate = null ;
    String description = null ;

    int numRows = 0 ;
    String line = "------------------------------------" ;
    BigDecimal threshold = new BigDecimal(40.00) ;
 
    SQLWarning swarn = null ;
    Statement stmt = con.createStatement() ;
    ResultSet rs = stmt.executeQuery(qry) ;
    ResultSetMetaData rsmd = rs.getMetaData() ;
        
    System.out.printf("%-11s|", rsmd.getColumnName(1)) ;
    System.out.printf("%-8s|", rsmd.getColumnName(2)) ;
    System.out.printf("%-10s|", rsmd.getColumnName(3)) ;
    System.out.printf("%-40s\n", rsmd.getColumnName(4)) ;
    
    System.out.println(line + line);

    while (rs.next()) {
        
        itemNumber = rs.getInt("itemNumber") ;
        price = rs.getBigDecimal("price") ;
        stockDate = rs.getDate("stockDate") ;
        description = rs.getString("description") ;
        
        swarn = rs.getWarnings() ;
        
        if(swarn != null){
            printSQLWarning(swarn) ;
        }else{
            numRows ++ ;
            System.out.printf("%-11s|", itemNumber) ;
            System.out.printf("%-8s|", price) ;
            System.out.printf("%-10s|", stockDate) ;
            System.out.printf("%-40s\n", description) ;
        }
    }
    
    System.out.println("\n" + numRows + " rows selected") ;
    
    rs.close() ;
    stmt.close() ;
}

This example extracts and works with the metadata for a given ResultSet object by using the ResultSetMetaData object. Although it isn't shown in this code listing, you need to import the ResultSetMetaData interface, which is shown in the included ThirdQuery.java source code file. This metadata contains a wealth of information, which can allow you to write your Java application in a more database-generic manner. This generality usually isn't necessary for a Java application that leverages the Apache Derby database, because a tight coupling often exists between your Java application and an embedded Apache Derby database, which is itself a Java application. If you need to process somewhat arbitrary SQL commands, however, as is done by the Derby ij tool, having access to this metadata can be critical.

The other major innovation in this example is the use of formatted printing. In case you haven't seen this before, a formatted print statement applies a format string to subsequent data to create specially formatted output. In this case, you limit the length of each column that is displayed and insert vertical bars (|) to separate columns in an attempt to mimic the output of the ij tool. For example, "%-11s|" means to print a character string that is limited to 11 characters, followed by a vertical bar. The minus sign means to left-justify the string.

Summary

In this article, you learned how to issue a database query and process the resulting set of data by using three Java source code examples. In the first example, you connected to the database, issued a query, and printed the results to the screen. In the second example, you extracted the results as the appropriate Java data type and then filtered the resulting data before printing the results to the screen. Finally, you used metadata about the query results to print nicely formatted query results in a manner similar to the Apache Derby ij tool. In the next article in this series, you'll learn how to build on the data-extraction techniques presented in this article to modify data in an 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.
  
  
• 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 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.

Rate this article

Comments

Back to top

Table of contents

• JDBC query execution: an overview
• Executing a query
• Extracting typed data
• Working with query 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

Dig deeper into Open source on developerWorks

Try IBM PureSystems. No charge.

Experience today!

---

Get started with the cloud-based trial and pattern development kit.

Special offers