Simple JDBC Application

• Overview
• What's Included?
• How to run this sample application in an embedded environment
• How to run this sample application in a server environment

Overview

This example program is a very minimal JDBC application. JDBC is the primary API for interacting with Apache Derby. This program:

• runs in either embedded mode (the default) or as a client in a server environment, depending on the arguments passed to the program.
• runs in J2ME or J2SE Java Virtual Machines.
• starts up the Derby engine, if running in embedded mode
• connects to the Derby Network Server, if running in client mode
• creates and connects to a database
• creates a table
• inserts data
• updates data
• selects data
• drops a table
• disconnects
• shuts down Derby, if running in embedded mode

In embedded mode, the application starts up an instance of Derby within the current Java Virtual Machine and shuts down the instance before it completes. For a J2ME Java Virtual Machine an appropriate DataSource is used, otherwise, the java.sql.Driver mechanism is used. No network access is involved. Only one application can access a database at a time.

In a server environment, the application demonstrates the use of the Derby network client or the IBM DB2 JDBC Universal Driver by connecting to the Network Server and running the demo. Note that the client drivers allow multiple instances of the application to run at the same time. However, the SQL operations performed by this demo will cause failures when multiple simultaneous instances of the application are run. Use of a client driver to connect to the Network Server in this application is intended only to demonstrate this type of connection. The SimpleApp demo is not suited for simultaneous executions because it creates and drops the table on which it operates.

What's Included?

Before running this demo, you should see the following files and directories in the /demo/programs/simple directory:

• example.html

  This file.

• SimpleApp.java

  Source code for the example program that starts up Derby, creates a database, performs some inserts and updates, and then shuts down Derby. Examine this file to see how the application behaves in the various environments.

• derby.properties

  Properties file for the Derby system.

• SimpleApp.class

  The compiled class file.

After running the demo, you will see some new files and directories:

• derbyDB (directory)

  The directory that makes up the derbyDB database. You must not modify anything in this directory, or you will corrupt the database. The directory was created when the application connected with Derby, using the attribute create=true in the database connection URL. The database name, derbyDB, was also set in the database connection URL.

• derbyDB\log (directory)

  The directory that holds the database transaction logs for the derbyDB database.

• derbyDB\seg0 (directory)

  The directory that holds the data for the derbyDB database.

• derbyDB\service.properties

  An internal file that holds boot-time configuration parameters for the derbyDB database; do not edit.

• derby.LOG

  The log file with Derby progress and error messages.

How to run this sample application in an embedded environment

1. Open a command window.
2. If you haven't set it already on a system-wide basis, set the DERBY_INSTALL environment variable to the location of the directory where you installed the Derby software in the current command window.
3. and change directories to the %DERBY_INSTALL%\demo\programs\simple directory.
4. In the command window, set the CLASSPATH as follows:

   WINDOWS: set CLASSPATH=.;%DERBY_INSTALL%\lib\derby.jar
   UNIX (ksh): export CLASSPATH=.:${DERBY_INSTALL}/lib/derby.jar

   Library or Directory	Path to Library or Directory
   main Derby library: derby.jar	%DERBY_INSTALL%\lib\derby.jar
   current directory SimpleApp.class	.\

   A note on setting the classpath for an embedded environment Derby provides a script to help you get started with setting the classpath in %DERBY_INSTALL%\frameworks\embedded\bin. This script is called setEmbeddedCP and comes in two flavors: one for Windows environment (this file ends with .bat) and one for UNIX environments (this file ends with .ksh). For users working in those environments, copying the commands in this file will help you get started setting the classpath.

5. Run Derby's Sysinfo utility for testing the classpath for an embedded environment. You will provide the arguments embedded to indicate an embedded environment and SimpleApp.class to test for the presence of the SimpleApp class.

   You run the utility like this:

   java org.apache.derby.tools.sysinfo -cp arguments

   So for the arguments you need here, run it like this (all on one line):

   java org.apache.derby.tools.sysinfo -cp embedded SimpleApp.class

   If your environment is set up correctly, the utility shows output indicating success. It looks like this:

   FOUND IN classpath:

   Derby embedded engine library (derby.jar)

   user-specified class (SimpleApp)

   SUCCESS: All Derby-Related classes for embedded environment found in classpath.

   If something is missing from your classpath environment, the utility indicates what is missing. For example, if you neglected to add the directory containing the SimpleApp class to your classpath, the utility would indicate as such:

   Testing for presence of Derby-related libraries for embedded environment.

   FOUND IN classpath:

   Derby embedded engine library (derby.jar)

   NOT FOUND IN classpath:

   user-specified class (SimpleApp)

   (SimpleApp not found.)

6. Once you have your environment set up correctly, execute the application in embedded mode from the same directory (/demo/programs/simple):

   java SimpleApp

SimpleApp starting in embedded mode.
Loaded the appropriate driver.
Connected to and created database derbyDB
Created table derbyDB
Inserted 1956 Webster
Inserted 1910 Union
Updated 1956 Webster to 180 Grand
Updated 180 Grand to 300 Lakeshore
Verified the rows
Dropped table derbyDB
Closed result set and statement
Committed transaction and closed connection
Database shut down normally
SimpleApp finished

How to run this sample application in a server environment

You will need to set up both the client process and the server process to run the demo application as a client connecting to the Network server.

First start the Network Server:

1. Open a command window for the server.
2. If you haven't set it already on a system-wide basis, set the DERBY_INSTALL environment variable to the location of the directory where you installed the Derby software in the current command window.
3. Change directories to the \demo\programs\simple directory.
4. In the command window for the server, set the CLASSPATH as follows:

   WINDOWS: set CLASSPATH=.;%DERBY_INSTALL%\lib\derby.jar;%DERBY_INSTALL%\lib\derbynet.jar
   UNIX (ksh): export CLASSPATH=.:${DERBY_INSTALL}/lib/derby.jar:${DERBY_INSTALL}/lib/derbynet.jar

   
   

   Classpath for the process starting the Network Server

   Library or Directory	Path to Library or Directory
   main Derby library: derby.jar	%DERBY_INSTALL%\lib\derby.jar
   Derby Network Server: derbynet.jar	%DERBY_INSTALL%\lib\derbynet.jar

   
   

   A note on setting the classpath for a server environment Derby provides a script to help you get started with setting the classpath in %DERBY_INSTALL%\frameworks\NetworkServer\bin. This script is called setNetworkServerCP and comes in two flavors: one for Windows environment (this file ends with .bat) and one for UNIX environments (this file ends with .ksh). For users working in those environments using the Derby Network Server, copying the commands in this file will help you get started setting the classpath on the server process.

5. Run Derby's Sysinfo utility for testing the classpath for a server environment. You will provide the arguments server to indicate a server environment and SimpleApp.class to test for the presence of the SimpleApp class.
   If your environment is set up correctly, the utility shows output indicating success. It looks like this:
   
   java org.apache.derby.tools.sysinfo -cp server SimpleApp.class
   
   FOUND IN CLASS PATH:
   
   

   Derby Network Server library (derbynet.jar)
   
   
   user-specified class (SimpleApp)

   
   
   SUCCESS: All Derby related classes found in class path.
   
6. Now start the Network Server:
   java org.apache.derby.drda.NetworkServerControl start
   

   A sucessful start produces the following output:

   Server is ready to accept connections on port 1527.

Next run the SimpleApp demo in Derby client mode:

1. Open a command window to run the application in client mode
2. If you haven't set it already on a system-wide basis, set the DERBY_INSTALL environment variable for the current command window to the location of the directory where you installed the Derby software
3. Change directories to the %DERBY_INSTALL%\demo\programs\simple directory.
4. In the command window for the client, set the CLASSPATH as follows:

   WINDOWS: set CLASSPATH=.;%DERBY_INSTALL%\lib\derbyclient.jar
   UNIX (ksh): export CLASSPATH=.:${DERBY_INSTALL}/lib/derbyclient.jar

   Classpath for the client process when using the Derby network client

   Derby network client: derbyclient.jar	%DERBY_INSTALL%\lib\derbyclient.jar
   current directory SimpleApp.class	.\

5. Run Derby's Sysinfo utility for testing the classpath for a client environment. You will provide the arguments client to indicate a client environment and SimpleApp.class to test for the presence of the SimpleApp class. Note that the following assumes you have not downloaded the IBM DB2 JDBC Universal Driver (see the next section for details) so the db2jcc.jar is listed as NOT FOUND - this is not a problem.
   If your environment is set up correctly, the utility shows output indicating success. It looks like this:
   
   java org.apache.derby.tools.sysinfo -cp client SimpleApp.class
   FOUND IN CLASS PATH:
   
   

   Derby Client libraries (derbyclient.jar)
   
   
   user-specified class (SimpleApp)

   
   NOT FOUND IN CLASS PATH:
   
   

   Derby Client libraries (db2jcc.jar)
   (com.ibm.db2.jcc.DB2Driver not found.)

   
   
6. Now start the SimpleApp in Derby client mode:
   
   java SimpleApp derbyclient
   
   A successful run produces the following output:

   SimpleApp starting in derbyclient mode.
   Loaded the appropriate driver.
   Connected to and created database derbyDB
   Created table derbyDB
   Inserted 1956 Webster
   Inserted 1910 Union
   Updated 1956 Webster to 180 Grand
   Updated 180 Grand to 300 Lakeshore
   Verified the rows
   Dropped table derbyDB
   Closed result set and statement
   Committed transaction and closed connection
   SimpleApp finished

Running the SimpleApp demo using the IBM DB2 JDBC Universal Driver:

In version 10.0 of Derby, the IBM DB2 JDBC Universal Driver was the only client driver that could communicate with the Derby Network Server. This driver is not licensed under the Apache License, Version 2.0 used by Apache Derby, but can be downloaded from the IBM developerWorks website (see: IBM DB2 JDBC Universal Driver, for Apache Derby Network Server for more information). Beginning with version 10.1, the Derby network client driver is bundled with the Derby distribution and this is the recommended client driver for most applications. The DB2 Universal Driver is still supported and can be utilized with the SimpleApp demo as follows (these instructions assume you have unzipped the Universal Driver into the lib directory of DERBY_INSTALL):

1. Open a command window to run the application in client mode using the DB2 Universal Driver
2. If you haven't set it already on a system-wide basis, set the DERBY_INSTALL environment variable for the current command window to the location of the directory where you installed the Derby software. It is assumed that you installed the IBM DB2 JDBC Universal Driver jar in the lib subdirectory at this location.
3. Change directories to the %DERBY_INSTALL%\demo\programs\simple directory.
4. Set the Network Server classpath as you would if using the Derby network client.
5. In the command window for the client, set the classpath as follows:

   WINDOWS: set CLASSPATH=.;%DERBY_INSTALL%\lib\db2jcc.jar;%DERBY_INSTALL%\lib\db2jcc_license_c.jar
   UNIX (ksh): export CLASSPATH=.:${DERBY_INSTALL}/lib/db2jcc.jar:${DERBY_INSTALL}/lib/db2jcc_license_c.jar

   Classpath for the client process when using the IBM DB2 JCC Client Driver

   IBM DB2 JCC Client Driver jar files: db2jcc.jar and db2jcc_license_c.jar	%DERBY_INSTALL%\lib\db2jcc_license_c.jar %DERBY_INSTALL%\lib\db2jcc.jar
   current directory SimpleApp.class	.\

6. Now start the SimpleApp demo in Derby client mode:
   
   java SimpleApp jccjdbcclient

A successful run produces the following output:

SimpleApp starting in jccjdbc mode.
Loaded the appropriate driver.
Connected to and created database derbyDB
Created table derbyDB
Inserted 1956 Webster
Inserted 1910 Union
Updated 1956 Webster to 180 Grand
Updated 180 Grand to 300 Lakeshore
Verified the rows
Dropped table derbyDB
Closed result set and statement
Committed transaction and closed connection
SimpleApp finished

Apache Derby Version 10.1