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

Making the connection

Climb aboard! This article begins your journey of writing Java™ applications that work with the Apache Derby database. Learn how to connect to an embedded Derby database by using a Derby embedded Java Database Connectivity (JDBC) driver. Plus find out about database metadata and how to properly handle SQL errors and warnings in your Java application that may be generated by Derby.

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

Robert J. Brunner photoRobert 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.



12 December 2006

Also available in Russian Japanese

Introduction to JDBC

Previously, the articles in this series have demonstrated a number of database concepts by using the ij tool to connect and interact with an Apache Derby database. Although it may not have been obvious at the time, you were using a Java application that used the JDBC application programming interface (API) to connect to and interact with an embedded Apache Derby database. In the next few articles, you'll learn how to re-create the basic functionality of the ij tool by writing your own Java application. This article focuses on establishing the database connection and dealing with potential database errors and warnings.

Before jumping into the Java code, you first may be curious about the nature of the JDBC API. JDBC is the official Java Database Connectivity API, and it has been around since the 1.1 release of the Java Development Kit. The JDBC API is contained within the java.sql package, and if you look carefully, you'll see that the API is predominantly interfaces. As a result, the real work of creating a database JDBC driver falls to the database vendors (or enterprising third parties) who must provide Java classes that implement these interfaces. Extensions to the JDBC API provide more advanced functionality and are available in the javax.sql package. The next few articles will cover most of the standard JDBC package; the extensions will have to wait until the basics are covered.

One last point about JDBC: The connection between your Java application and the database is controlled by a JDBC driver. Originally, there were four types of JDBC drivers, differentiated by their type number: 1, 2, 3, or 4. The types corresponded to different techniques in which the Java application communicated with the database. Today most drivers, including those you use to connect to a Derby database, are Type 4 drivers, which means they're written entirely in the Java language and directly translate the JDBC API into the vendor-specific database protocol. In the case of the Derby database, this process is simplified because Derby is written in the Java language.

Apache Derby and JDBC

Now that you're familiar with the basics of JDBC, you can begin to learn how to connect to an embedded Apache Derby database using the Java programming language. First, however, you must have a working installation of the Apache Derby database software, as discussed in the first article in this series. If you haven't already performed this crucial step, revisit the first article, and download and install the Derby software. After you have the Derby database software installed, you can use the sample code available with this article to connect to a Derby database, as shown in Listing 1.

Listing 1. Executing the sample code
rb$ mkdir derbyWork
rb$ cd derbyWork/
rb$ unzip ../derby9.zip 
Archive:  ../derby9.zip
  inflating: FirstConnect.java       
rb$ ls    
FirstConnect.java
rb$ javac FirstConnect.java 
rb$ java FirstConnect 

----------------------------------------------------
Database Name    = Apache Derby
Database Version = 10.1.2.1
Driver Name      = Apache Derby Embedded JDBC Driver
Driver Version   = 10.1.2.1
Database URL     = jdbc:derby:test
----------------------------------------------------
rb$ java FirstConnect
SQLWarning: State=01J01, Severity = 10000
Database 'test' not created, connection made to existing database instead.

----------------------------------------------------
Database Name    = Apache Derby
Database Version = 10.1.2.1
Driver Name      = Apache Derby Embedded JDBC Driver
Driver Version   = 10.1.2.1
Database URL     = jdbc:derby:test
----------------------------------------------------
rb$ ls
FirstConnect.class      derby.log
FirstConnect.java       test

What to do if it doesn't run

Although this first database connection example is as straightforward as possible, sometimes problems occur. Whenever you're working with the Java programming language, the most likely problem is that the necessary class files aren't in your CLASSPATH. For example, if your code compiles but gives the error message JDBC Driver org.apache.derby.jdbc.EmbeddedDriver not found in CLASSPATH when you try to run it, you need to add the derby.jar file to your CLASSPATH. This was discussed in the first article in this series. Other possible errors are forgetting to compile the Java source code by using the javac tool or being in the wrong directory.

In this first example, you create a clean workspace for developing and executing your database application code. First you create a new directory, into which you extract the code file supplied with this article. Before compiling and executing, you use the ls command to demonstrate that the directory includes only the Java source code explained in this article.

The next step is to use the Java compiler to create the Java bytecode file that will be executed by the Java Virtual Machine (JVM) in the next step. If you successfully compile the source code, you can execute the bytecode within a JVM. This calls the main method for the FirstConnect class, which generates output similar to that shown; if you have a problem along the way, see the sidebar What to do if it doesn't run. This Java code first creates the test database and then creates a connection to it by using the Apache Derby embedded JDBC driver. To demonstrate how this code can properly handle SQL warnings and errors, you can re-execute the code, which first displays the warning information that indicates the database wasn't created (because it already exists) followed by the standard information about the database and JDBC driver that were displayed previously.

This example concludes by once again displaying the contents of the work directory, showing both the compiled Java class file and the new database files. This last point is important: When you're using Apache Derby as an embedded database, the default location for the database files is in the directory where your code resides. If this isn't what you want, you need to modify your JDBC URL to specify where the database files should be created. This topic is beyond the scope of this article; consult the Derby Developer's Guide, which is available via the link to the Derby online manuals in the Resources section of this article.


Connecting a Java application and a Derby database

The Java code that you learned how to execute in the previous section is straightforward and is covered in detail in the rest of this article. In a production environment, developing a Java database application can be difficult. Rather than getting bogged down in these details, which will be addressed in future articles, this article focuses on the most basic technique for establishing a connection between a Java application and an embedded Apache Derby database. This technique, as shown in Listing 2, requires the use of a JDBC driver to implement the connection protocols.

Listing 2. Using JDBC to connect to a Derby database
private static final String driver = "org.apache.derby.jdbc.EmbeddedDriver" ;
private static final String url = "jdbc:derby:test;create=true" ;
 
public static void main(String[] args) {
    Connection con = null ;
    DatabaseMetaData dbmd = null ;
        
    try {
        Class.forName(driver) ;
        con = DriverManager.getConnection(url);
       
       // Use the database connection somehow.
       
    } catch (SQLException se) {
        printSQLException(se) ;
    } catch(ClassNotFoundException e){
        System.out.println("JDBC Driver " + driver + " not found in CLASSPATH") ;
    }finally {
        if(con != null){
            try{
                con.close() ;
            } catch(SQLException se){
                printSQLException(se) ;
            }
        }
    }
}

What about a DataSource?

This article, for simplicity, focuses exclusively on connecting to an embedded Apache Derby database by using the Derby embedded JDBC driver. As a standard rule, the preferred JDBC solution for connecting to a database is to leverage a DataSource, which allows you to abstract away the database connection details, such as the database URL, driver class, user name, password, and database name. By abstracting out the specific connection information, you can easily change those parameters without having to alter your database application code. Future articles will explore the use of a DataSource with Derby; however, it's easier to understand the basics of JDBC by first learning about JDBC drivers and the DriverManager, and then learning about the DataSource.

This example code first defines two constants that contain the Java class name for the Apache Derby embedded JDBC driver and the JDBC database connection URL. This connection URL should look familiar -- it's exactly what you used in previous articles when issuing a connect command in the ij tool. The value assigned to the driver is the fully qualified name for the embedded driver class.

The rest of the code is contained in the main method; it uses the default class loader in the JVM to find and instantiate the driver class previously defined, which is done by using the Class.forname method. This method finds the class file for the Derby embedded driver, which must exist somewhere in your CLASSPATH, then loads it into the JVM. During this loading process, the class' static section is processed, which registers this driver with the JDBC DriverManager object.

The DriverManager acts as a factory object. Given a database URL, the DriverManager returns a database connection by using the appropriate JDBC driver. This step is performed in the next line of code, where you request a JDBC connection from the DriverManager using the previously defined URL.

You undoubtedly have noticed that the connection request is wrapped inside a try ... catch block, and that you call the close method on your connection inside a finally block. Both of these topics are addressed later in this article in the When something goes wrong section. Given space limitations, this article can't fully explore the different ways of establishing a database connection with JDBC. For instance, this example doesn't utilize any security information, such as user name or password. Future articles will explore alternative database connection techniques (see, for example, the sidebar What about a DataSource?). If you can't wait until then, be sure to look at the Derby Developer's Guide, which is linked to in the Resources section of this article.

Database metadata

If you've never encountered metadata, it may seem like a strange concept. But really it's simple: Metadata is data that describes data. In the context of a database, metadata describes that particular database, such as the name of the database, its version number, or the name of the JDBC driver making the connection. You access database metadata in JDBC by calling the appropriate method from the DatabaseMetaData object, as shown in Listing 3.

Listing 3. Working with database metadata
DatabaseMetaData dbmd = null ;
dbmd = con.getMetaData() ;

System.out.println("\n----------------------------------------------------") ;
System.out.println("Database Name    = " + dbmd.getDatabaseProductName()) ;
System.out.println("Database Version = " + dbmd.getDatabaseProductVersion()) ;
System.out.println("Driver Name      = " + dbmd.getDriverName()) ;
System.out.println("Driver Version   = " + dbmd.getDriverVersion()) ;
System.out.println("Database URL     = " + dbmd.getURL()) ;
System.out.println("----------------------------------------------------") ;

To access the appropriate metadata, you first create a new DatabaseMetaData object from the current JDBC Connection, as shown in the previous example. You can then call one of the many metadata functions provided by the Apache Derby JDBC driver (for the full listing, see the JDBC specification, which you can access from the Resources section of this article). In this example, you extract the name and product version number for the database you're accessing, the JDBC driver name and version number you're using to access the database, and the full JDBC URL that identifies the database to which you've established a connection. Database metadata is most useful if you're developing a database application that must interact with a number of different databases or JDBC drivers. In this case, you can use the metadata to determine the capabilities of a particular database and JDBC driver at run time.


When something goes wrong

As you've worked with the Apache Derby database, either by following along with the previous articles in this series or on your own, you've undoubtedly experienced both database warnings and database errors. Given the importance of the information you're storing in your database, proper handling of both database errors and warnings can be vital to your business. Fortunately, doing so, as demonstrated in the rest of this section, is simple. The first step is to learn how to view the information provided to your application about the error encountered by the database, as shown in Listing 4.

Listing 4. Exception-handling code
private static void printSQLException(SQLException se) {
    while(se != null) {
        System.out.print("SQLException: State:   " + se.getSQLState());
        System.out.println("Severity: " + se.getErrorCode());
        System.out.println(se.getMessage());            
        
        se = se.getNextException();
    }
 }

private static void printSQLWarning(SQLWarning sw) {
    while(sw != null) {
        System.out.print("SQLWarning: State=" + sw.getSQLState()) ;
        System.out.println(", Severity = " + sw.getErrorCode()) ;
        System.out.println(sw.getMessage()); 
        
        sw = sw.getNextWarning();
    }
    }

As shown, handling SQL exceptions and SQL warnings is similar. The SQLWarning class inherits from the SQLException class; however, a SQL warning is less severe than an SQL exception, so it's better to handle them separately. These two functions are both declared private static, which enables you to call them from within your main method but not from other classes. In production code, you would change this as dictated by your program requirements.

In both of these functions, you loop over the warnings or exceptions. This may seem odd in a normal error-handling sense, but when it comes to database programming, expect the unexpected. The reason for the chaining is simple: Events are often coupled within a database. For example, if you're inserting multiple rows into a database, and they all fail due to a column datatype or name mismatch, you may have multiple errors. To properly account for all of them, you need to be able to tie exceptions together so that the calling code is notified of all the problems the database encountered. To make things easier, Apache Derby always places the most important exception first in any exception chain.

Inside the loop, you print out the error or warning information. The SQL state is a five-character string that follows the X/OPEN Common Application Environment (CAE) specification, Data Management: SQL, Version 2 SQL specification, or SQL99 standard conventions and allows a program to recover from specific database error conditions. The first two characters of the SQL state are a class value, and the last three characters form a subclass value. An SQL state with a value of 00000 indicates success, whereas a class value of 01 indicates warning conditions, such as data truncation. The SQL code is a database-specific value.

Handling SQL exceptions is straightforward. You use the standard Java try ... catch mechanism to wrap JDBC method invocations, as shown in Listing 5.

Listing 5. Catching SQL exceptions
try{
    // Execute a JDBC operation
} catch (SQLException se) {
    printSQLException(se) ;
}

As this sample code demonstrates, you handle any SQL exceptions by passing the SQLException object to the previously defined printSQLException method, which handles all the error reporting. The only major difficulty in dealing with SQL exceptions is the proper reclamation of database resources, such as the database connection. Because these resources, like the connection or any database cursors, can be managed outside the JVM running your database application code, your application must close them explicitly. The Connection object's close method can throw an SQLException, so you place this method in a finally block, which ensures that the Java application attempts to close the database connection even if an error occurs.

On the other hand, you must explicitly check for any SQL warnings, because they aren't propagated via the standard exception-handling mechanisms. To do so, call the appropriate getWarnings method on the relevant JDBC object, as shown in Listing 6.

Listing 6. Checking for SQL warnings
SQLWarning swarn = con.getWarnings() ;

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

As discussed earlier, SQL warnings are handled by the printSQLWarning method. Before calling the method, you first check that there is at least one SQL warning; if so, you pass the first SQLWarning object to the appropriate method. As you'll see in future articles, a number of JDBC objects can generate SQL warnings, so encapsulating error- and warning-handling code can simplify your task of developing and maintaining database application code.


Putting it all together

Up to this point, this article has discussed a number of components that are required to establish a database connection by using the JDBC API with Apache Derby. Together, these pieces provide most of the necessary functionality, as you can see in the complete connection example provided in Listing 7.

Listing 7. The complete connection example code
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.DatabaseMetaData;

public class FirstConnect {
    private static final String driver = "org.apache.derby.jdbc.EmbeddedDriver" ;
    private static final String url = "jdbc:derby:test;create=true" ;
    
    static void printSQLException(SQLException se) {
        while(se != null) {

            System.out.print("SQLException: State:   " + se.getSQLState());
            System.out.println("Severity: " + se.getErrorCode());
            System.out.println(se.getMessage());            
            
            se = se.getNextException();
        }
    }
        
    static void printSQLWarning(SQLWarning sw) {
        while(sw != null) {

            System.out.print("SQLWarning: State=" + sw.getSQLState()) ;
            System.out.println(", Severity = " + sw.getErrorCode()) ;
            System.out.println(sw.getMessage()); 
            
            sw = sw.getNextWarning();
        }
    }

    public static void main(String[] args) {
        Connection con = null ;
        DatabaseMetaData dbmd = null ;
        
        try {
            Class.forName(driver) ;
            con = DriverManager.getConnection(url);

            SQLWarning swarn = con. getWarnings() ;
            
            if(swarn != null){
                printSQLWarning(swarn) ;
            }
            
            dbmd = con.getMetaData() ;
            
            System.out.println("\n----------------------------------------------------") ;
            System.out.println("Database Name    = " + dbmd.getDatabaseProductName()) ;
            System.out.println("Database Version = " + dbmd.getDatabaseProductVersion()) ;
            System.out.println("Driver Name      = " + dbmd.getDriverName()) ;
            System.out.println("Driver Version   = " + dbmd.getDriverVersion()) ;
            System.out.println("Database URL     = " + dbmd.getURL()) ;
            System.out.println("----------------------------------------------------") ;
            
        } catch (SQLException se) {
            printSQLException(se) ;
        } catch(ClassNotFoundException e){
            System.out.println("JDBC Driver " + driver + " not found in CLASSPATH") ;
        }
        finally {
            if(con != null){
                try{
                    con.close() ;
                } catch(SQLException se){
                    printSQLException(se) ;
                }
            }
        }
    }
}

This class is all you need to establish a database connection via JDBC to an embedded Apache Derby database, and it's exactly what is provided in the sample code that you compiled and executed at the beginning of this article. The main new code is the inclusion of the five import statements at the top of the program code. Although you could use import java.sql.*, listing each import statement separately makes you explicitly conscious of the JDBC objects you're using in your application.

This application includes the error-handling functions discussed in Listing 4. All the database operations are encased within a single try ... catch block, including the database metadata operations shown in Listing 3. Many database applications avoid this tight coupling between the database application code and the database to simplify the management and maintenance of the application and database. Given the embedded capabilities of the Apache Derby database, however, the line between application and database is blurred, and this separation doesn't need to be as rigidly enforced.

Summary

In this article, you learned how to write a Java application that connected to an embedded Apache Derby database. This process used a Type 4 JDBC driver from the Apache Derby project to connect to a database, extract metadata from the database, and then close the database connection. You also learned how to handle SQL warnings and SQL exceptions in your Java application that may be generated by Derby. Future articles will build on the techniques presented in this article to issue queries to a Derby database and process the results from within a Java application.


Download

DescriptionNameSize
Derby SQL script for this articlederby9.zip1KB

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, Information Management
ArticleID=183204
ArticleTitle=Developing with Apache Derby -- Hitting the Trifecta: Java database development with Apache Derby, Part 1
publish-date=12122006