Develop an embedded Apache Derby application

Throughout this series of articles, you've been working with the Apache Derby database. The Derby database supports two modes of interaction: network mode and embedded mode. The more familiar mode of interaction is probably the network mode, where a database server is running on a machine, and database clients connect to the central server via a network connection. This setup describes most commercial database installations, which generally follow the transactional processing model.

The alternative model is the embedded mode, where an application consists not only of the software that implements the requisite business logic, but also of the database engine software. As a result, the application is entirely self contained, making for a simple installation and setup procedure. To emphasize this point, consider that throughout this series of articles, you've been working with the Apache Derby database exclusively in embedded mode.

You're now in a position to start developing your own embedded Derby database application. Formally, the process of developing an embedded database application can be broken down into three steps:

1. Map your database logic into Java classes.
2. Develop Java code that manages the database-specific functionality.
3. Implement your business logic within a Java application.

The rest of this section explores these three steps in more detail before moving on to a discussion of deploying an embedded Apache Derby application.

Object-Relational Mapping

The previous articles in this series have introduced database concepts by using the fictional Bigdog's Surf Shop business. This simple database manages the inventory of the surf shop, focusing primarily on a database table that contains the surf shop products. In this article, you develop an application that displays a detailed listing of a specific item in the surf shop inventory.

The first step you need to tackle is implementing a Java class that maps to a product, which is a row in the bigdog.products table. In doing so, you encounter a problem: A table schema is defined in SQL, whereas your business application is defined in the Java language. Mapping between an SQL relational data model and a Java object model can sometimes produce challenges, especially for complex database schemas (or Java object models) that involve numerous tables connected via primary-foreign keys. Formally, this process is known as Object-Relational Mapping (ORM).

For this demo application, however, the only complexities you face are mapping the SQL data types used in the schema of the bigdog.products table into the correct Java data types. In this example, the process is relatively straightforward, as shown in Listing 1, where you define the Product class.

                
public class Product {
    
    private int itemNumber = 0 ;
    private BigDecimal price = null ;
    private Date stockDate = null ;
    private String description = null ;
    
    public Product(int itemNumber, BigDecimal price, Date stockDate, String description){
        this.itemNumber = itemNumber ;
        this.price = price ;
        this.stockDate = stockDate ;
        this.description = description ;
    }

    public void printProduct() {
        String line = "------------------------------------" ;
        
        System.out.println("\nBigdog's Surf Shop Product Information") ;
        System.out.println(line + line);
        
        System.out.printf("Item Number      : %-11s\n", this.itemNumber) ;
        System.out.printf("Item Price       : $%-8.2f\n", this.price) ;
        System.out.printf("Item StockDate   : %-10s\n", this.stockDate) ;
        System.out.printf("Item Description : %-40s\n", this.description) ;
        
        System.out.println(line + line + "\n");
    }
...

The mapping presented in this listing should seem familiar, thanks to the lessons you learned when you were extracting data from the bigdog.products table in the tenth article in this series about executing a query. The class defines attributes that match the columns in the bigdog.products table with the correct Java data types. In practice, you can add full getter/setter methods for each attribute, which, although not done in this demo code, is often done automatically for you by most popular development environments.

The two main methods in the Product class are the constructor, which initializes the object as you might expect, and the printProduct method, which generates a nicely formatted printout for a specific product. This method is called by the demo application but is placed in this class to capitalize on the advantages of a tight coupling -- after all, who better to know how to print out an object than the defining class? In general, you should define a Java class for every table in your database that must be exposed to your business application. So, although doing so isn't necessary for this simple demo, you could also construct a Java class to map to the bigdog.vendors table.

Encapsulating database logic

After the database tables have been successfully mapped into Java classes, the next step is to connect the database tables with the appropriate Java class. One possibility is to place this connection code directly in the appropriate Java class—for example, the Product class presented in Listing 1. In most cases, however, this is a bad idea, because it introduces a tight connection between your database and your database application. As you've been repeatedly cautioned about in this series, a tight coupling is generally something to avoid.

A better approach is to implement an interface layer, where database-specific Java code can be encapsulated. In this approach, if your database details change, such as location, version, or even basic schema modifications, your application is insulated; the only changes you need to make are confined to the DAOs. Java classes in this intermediate layer are known as DAOs, and you generally have one DAO class for every Java class connected to a database table. For example, in Listing 2, the DAO for the previously defined Product class is presented in the ProductDAO class.

                
SELECT itemNumber, price, stockDate, description " + 
        "FROM bigdog.products WHERE itemNumber = ?" ;
...
    public Product getProduct(int targetItemNumber){

        Product product = null ;

        try{
            pstmt.clearParameters() ;
            pstmt.setInt(1, targetItemNumber) ;

            ResultSet rs = pstmt.executeQuery();

            if (rs.next()) {
                int itemNumber = rs.getInt("itemNumber") ;
                BigDecimal price = rs.getBigDecimal("price") ;
                Date stockDate = rs.getDate("stockDate") ;
                String description = rs.getString("description") ;

                product = new Product(itemNumber, price, stockDate, description) ;
            }
            
            rs.close() ;
            
        } catch(SQLException se) {
            printSQLException(se) ;
        }

        return product;
    }
...

The primary purpose of the ProductDAO class is to create a new Product object that consists of the data from a specific row in the bigdog.products table. The row is identified by the targetItemNumber value, which is passed into the getProduct method. Although it isn't shown in Listing 2, this class also determines the maximum item number (which the demo application needs) by issuing and parsing the result of the query SELECT MAX(itemNumber) FROM bigdog.products.

If you look at Listing 2, you can easily see the tight coupling between the underlying database schema and the DAO classes. In the getProduct method, the data is extracted via a PreparedStatement execution. By adding the extra layer, you minimize the impact of any database changes -- for minor changes, the only class that needs to be updated in this demo is ProductDAO.

You can also place database-management code in a separate class to encapsulate this type of functionality, as shown in Listing 3.

                
public class DBManager {
    private static Connection con = null ;    

    private static final String driver = "org.apache.derby.jdbc.EmbeddedDriver" ;
    private static final String url = "jdbc:derby:" ;
    private static final String dbName = "SurfShop" ;
...
    private ProductDAO pdao = null ;

    public DBManager(){
        if(!dbExists()){
            try {
                Class.forName(driver) ;
                con = DriverManager.getConnection(url + dbName + ";create=true");

                processStatement(createProductsSQL) ;

                con.setAutoCommit(false) ;
                batchInsertData(insertProductsSQL) ;
                con.commit() ;

            }catch(BatchUpdateException bue) {
...
            }
        }
        pdao = new ProductDAO(con) ;
    }

    public void close() {
        try {
            con = DriverManager.getConnection(url + ";shutdown=true") ;
        }catch(SQLException se) {
            ; // Do Nothing. System has shut down.
        }
        con = null ;
    }

    public ProductDAO getProductDAO() {
        return pdao ;
    }

    private Boolean dbExists() {
        Boolean exists = false ;
        try{
            Class.forName(driver) ;
            con = DriverManager.getConnection(url + dbName);
            exists = true ;
        } catch(Exception e){
            ; // Do nothing, as DB does not (yet) exist
        }
        return(exists) ;
    }
...

The DBManager class presented in Listing 3 is the largest class in this application. This is partly due to the simplicity of the demo, but it's also a reflection of the complexity of interacting successfully with a database from within a Java application. This class is responsible for managing the database connection, which includes establishing the initial connection and creating a ProductDAO instance for the demo application to access.

Because this is an embedded application, this code is also responsible for creating and initializing the embedded database itself. In this class, you accomplish this by using the dbExists method, which attempts to establish a database connection. If the database doesn't exist, an exception is thrown, and the DBManager constructor creates the database itself. Then it creates the bigdog.products table, and finally it inserts 10 new rows in a single batch-insert operation. By following this logic, if this application is executed a second time, the dbExists method successfully connects (assuming the locally stored database isn't removed somehow), and the database-creation process is skipped. To simplify the different database-connection operations, the JDBC URL is encoded as jdbc:derby, and different suffixes are appended based on the required operations. For example, url + dbName + ";create=true" is used to create the database, and url + dbName is used to establish a database connection.

One last important point about the DBManager class is the presence of the close method. This method explicitly shuts down the Apache Derby embedded database by attempting to establish a database connection with the database property shutdown=true appended to the base JDBC URL. By shutting down the database in this manner, you guarantee that all transactions are closed properly. This allows for faster database restarts, because the database will already be in a consistent state.

Develop an embedded application

The last formal step in the process is the development of the Java application. To keep things simple, this demo application:

• Accepts a numerical input from the user at the command line
• Retrieves the information for the product that has the input item number
• Displays the product for the user to view

Given the previously defined classes, the demo application code is relatively short and straightforward to implement, as shown in Listing 4.

                
import java.io.BufferedReader ;
import java.io.InputStreamReader ;

public class DemoApp {
    private DBManager dbm = null ;
    private ProductDAO pdao = null ;
    private int maxItemNumber = 0 ;

    public DemoApp() {
        this.dbm = new DBManager() ;
        this.pdao = dbm.getProductDAO() ;
        this.maxItemNumber = pdao.getMaxItemNumber() ;
    }

    public void showProduct() {

        int itemNumber = 0 ;
        Product p = null ;

        BufferedReader cin = new BufferedReader(new InputStreamReader(System.in)) ;

        while(true) {
            try {
                System.out.print("Enter object item number (0 to Exit): ") ;
                itemNumber = Integer.parseInt(cin.readLine()) ;
            }catch(Exception ex){
                ; // Do nothing. In this simple demo you ignore any input errors.
            }    
            
            if(itemNumber == 0)
                break ;
            else if ((itemNumber < 0) || (itemNumber > this.maxItemNumber))
                System.out.println ("Invalid product item number, please try again.") ;
            else{
                p = pdao.getProduct(itemNumber) ;
                p.printProduct() ;
            }
        }
        dbm.close() ;
    }

    public static void main(String[] args) {
        DemoApp da = new DemoApp() ;
        da.showProduct() ;
    }
}

The application logic shown in Listing 4 is contained within two methods in this class: the constructor and the showProduct method. The constructor creates a DBManager object to establish a database connection, as discussed earlier, and then acquires an instance of the ProductDAO class. This instance is used within the showProduct method to retrieve the necessary data from the database. The constructor also retrieves the maximum item number from the database, which simplifies error management.

The showProduct method reads an integer from the console's standard input stream. If the integer value is an allowed value from the bigdog.products table, a Product object is created by using the appropriate data from the bigdog.products table, and the value is displayed by using the printProduct method. This process continues until the user enters a zero to terminate the loop.

Back to top

Deploy an embedded Apache Derby application

After you've successfully implemented and tested your embedded Apache Derby database application, the next step is to package all the necessary files together so you can deploy the application as necessary. The rest of this section steps you through this process for the demo application you just constructed. The first step (if you haven't already done so) is to decompress the sample code file available in the Download section, as shown in Listing 5.

                
rb$ mkdir derbyWork
rb$ cd derbyWork/
rb$ unzip ../derby14.zip 
Archive:  ../derby14.zip
  inflating: DBManager.java          
  inflating: DemoApp.java            
  inflating: Product.java            
  inflating: ProductDAO.java         
  inflating: manifest.txt  
rb$ more manifest.txt 
Main-Class: DemoApp
Class-Path: lib/derby.jar

rb$

As you can see, the first step is to create a new work directory and then decompress the sample code file into this new directory. The compressed sample code file includes five files: four Java source code files for the demo application, and a manifest.txt file, whose contents, as shown in Listing 5, consist of two lines that contain a name-value pair. You use this manifest file to build an executable JAR file for the demo application. The first name-value pair specifies the name of the class that is the primary application driver, which is DemoApp in this case. The second name-value pair specifies where to find the Apache Derby database Java classes, which in embedded mode are contained entirely in the derby.jar file.

The next step is to bundle the necessary files together into the .jar file, as shown in Listing 6.

                
rb$ mkdir lib
rb$ cp $DERBY_INSTALL/lib/derby.jar lib/
rb$ javac *.java
rb$ jar cvmf manifest.txt demoapp.jar *.class
added manifest
adding: DBManager.class(in = 5087) (out= 2792)(deflated 45%)
adding: DemoApp.class(in = 1460) (out= 881)(deflated 39%)
adding: Product.class(in = 1684) (out= 919)(deflated 45%)
adding: ProductDAO.class(in = 2625) (out= 1369)(deflated 47%)
rb$ ls
DBManager.class         Product.class           demoapp.jar
DBManager.java          Product.java            lib
DemoApp.class           ProductDAO.class        manifest.txt
DemoApp.java            ProductDAO.java
rb$ rm -rf *.java *.class manifest.txt 
rb$ ls
demoapp.jar     lib
rb$ zip demoapp.zip demoapp.jar lib/derby.jar 
  adding: demoapp.jar (deflated 6%)
  adding: lib/derby.jar (deflated 8%)
rb$ rm -rf demoapp.jar lib 
rb$ ls
demoapp.zip

The first step in the process is to create the lib directory and then copy the derby.jar file into this directory, as mandated by the CLASS-PATH: lib/derby.jar name-value pair shown in the manifest file. If you change the value to something else, change the location of the derby.jar file appropriately, or your deployed demo application will fail (because it won't be able to find the required Apache Derby class files).

Next, you need to compile the included source code for the demo application and then create the JAR file. You create the JAR file by calling the jar executable with the cvmf flags, which specify to create an archive, be verbose about all steps taken, use the specified manifest file, and name the resulting JAR file the indicated name. When you call the executable in this manner, you must specify the manifest file first, followed by the name you want to call the JAR file. Finally, you must explicitly list any files or directories that should be included in the new JAR file.

After you've created the JAR file containing your application code, you can safely remove those files (of course, you should have a backup copy located somewhere else) and then create a new compressed file that contains your application JAR file and the required support classes (in this example, just the derby.jar file). Finally, to demonstrate the self-contained nature of your new embedded Apache Derby database application, you can remove all other files. One final point about packaging your embedded application is that in this example, you aren't distributing any Apache Derby software. As a result, you don't need to include any Apache Derby license information. If you ever distribute Apache Derby database software -- for example, by sending this example to someone else on another computer -- you need to follow the Apache Derby database license requirements discussed in the Distributing Apache Derby software sidebar.

Now that you've created a new compressed file that contains your entire embedded Apache Derby application, the only thing left to do is run the demo application, as shown in Listing 7.

                
rb$ unzip demoapp.zip 
Archive:  demoapp.zip
  inflating: demoapp.jar             
   creating: lib/
rb$ rm demoapp.zip 
rb$ CLASSPATH=  
rb$ export CLASSPATH
rb$ echo $CLASSPATH

rb$ java -jar demoapp.jar 
Enter object item number (0 to Exit): -1
Invalid product item number, please try again.
Enter object item number (0 to Exit): 11
Invalid product item number, please try again.
Enter object item number (0 to Exit): 1

Bigdog's Surf Shop Product Information
------------------------------------------------------------------------
Item Number      : 1          
Item Price       : $19.94   
Item StockDate   : 2006-03-31
Item Description : Hooded sweatshirt                       
------------------------------------------------------------------------

Enter object item number (0 to Exit): 10

Bigdog's Surf Shop Product Information
------------------------------------------------------------------------
Item Number      : 10         
Item Price       : $34.95   
Item StockDate   : 2006-01-24
Item Description : Open-toed sandal                        
------------------------------------------------------------------------

Enter object item number (0 to Exit): 0
rb$ ls
SurfShop        demoapp.jar     derby.log       lib

To run the demo application, you expand the compressed file and execute the demoapp.jar file. For good measure, this example also removes the compressed application file and clears the value of your shell's CLASSPATH environment variable. As a result, you know that this demo application will work on a computer on which the full Apache Derby database software is not installed.

When run, this demo application creates the Surf Shop database, populates the bigdog.products table with 10 rows, and then continues to retrieve product information until the user enters a zero to terminate the program. While simple, this demo application demonstrates the main requirements for an embedded Apache Derby application. You can easily take this example and expand it to allow a user to update, insert, or delete rows from the bigdog.products table. Or you can use it as the basis for your own embedded Apache Derby database application.

Back to top

Summary

In this article, the last one in this series, you combined many of the lessons presented in earlier articles to develop and deploy a complete embedded Apache Derby database application. You learned how to map Derby database tables into a Java class, how to construct DAOs, and how to put them all together successfully. You also learned how to combine your Derby application with the Apache Derby database Java code into a self-contained, deployable Java application. With the knowledge you've gained from this series of articles, you're empowered to build your own embedded Apache Derby database applications—so get to work!

Back to top

Download

Information about download methods

Resources

Learn

• Check out some of the other 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.
  
  
• Get a full listing of Object-Relational Mapping tools that work with the Apache Derby database at the Apache Derby Wiki under the Persistence/Object Relational Mapping heading.
  
  
• Find the version 2.0 Apache license.
  
  
• Innovate your next open source development project with IBM trial software, available for download or on DVD.
  
  

Discuss

• Participate in the discussion forum.
  
  
• 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

• Develop an embedded Apache Derby application
• Deploy an embedded Apache Derby application
• Summary
• Download
• Resources
• About the author
• Comments

Dig deeper into Open source on developerWorks

IBM SmartCloud trial. No charge.

Unleash the power of hybrid cloud computing today!

---

Public and private solutions in one trial.

Special offers