Simple JDBC Application

Overview

This example program is a minimal JDBC application written in Java. JDBC is the primary API for interacting with Apache Derby using Java. This program:

  1. Runs in either embedded mode (the default) or as a client in a client/server environment, depending on the arguments passed to the program
  2. Starts up the Derby engine, if running in embedded mode
  3. Connects to the Derby Network Server, if running in client mode
  4. Creates and connects to a database
  5. Creates a table
  6. Inserts data
  7. Updates data
  8. Selects data
  9. Drops a table
  10. Disconnects
  11. Shuts down the database, if running in embedded mode

This program is intended to run in Virtual Machines for the Java Platform supporting Java SE 6 or newer.

In embedded mode, the application starts up an instance of Derby within the current Java Virtual Machine (JVM) and shuts down the instance before it completes. No network access is involved. Only one JVM can access a database at a time.

In a client/server environment, the application demonstrates the use of the Derby network client 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.

Note that in this document, file paths and environment variables are referenced using UNIX notation unless noted otherwise. This means that if, for example, you are using a Windows platform, you must substitute file separators, path separators and environment variable references to the Windows-specific notation, for example:

Element Example, UNIX Example, Windows
File separator /home/path/file c:\path\file
(CLASS)PATH separator derby.jar:derbytools.jar derby.jar;derbytools.jar
Environment Variable reference $DERBY_HOME %DERBY_HOME%

Also note that this document refers to the JVM launch command as java, and that it is being assumed that the JVM installation's launcher is available via the system's PATH. Refer to the documentation of your JVM / runtime environment for details on how to run a Java program using that JVM.

Modifying the application code

If you decide to modify the demo application code, or use it as a model for your own JDBC application, be aware that any change may affect the behavior of the program and the results from running it. For example, the data verification code is very specific to the data this demo inserts into the database, and will not work without modification with other databases.

For more information about how to use Derby, refer to the included documentation and the Apache Derby web site.

If you are relatively new to databases or JDBC, the Oracle JDBC basics tutorial may be a good starting point.

What's Included?

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

After running the demo, you will see some new files and directories. These will be located in the directory that the system property derby.system.home points to, or the current directory (user.dir) if derby.system.home is not set for the embedded or Network Server JVM. If you are following the instructions on this page, you will find the new files in the directory demo/programs/simple/, relative to this Derby installation. New files are:

To remove the effects of running the demo program, simply delete the database directory (derbyDB) and derby.log. Note that if you are running the demo in a client/server environment you most likely need to shut down the Derby Network Server before being able to delete the database directory.

How to run this sample application in an embedded environment

The Derby embedded driver is a JDBC driver included in binary distributions of Apache Derby, in the directory $DERBY_HOME/lib/. The embedded driver should be used when you want to run Derby in the same JVM as your application, for example when no direct network access to the database system is needed.

Class name: org.apache.derby.jdbc.EmbeddedDriver
Library: derby.jar

 

  1. Open a command window.
  2. If you haven't set it already on a system-wide basis, set the DERBY_HOME environment variable to the location of this Derby installation. This is not strictly required to run the demo, but this environment variable will be used later on this page to refer to the required Derby resources, files, etc. Examples:

    UNIX (ksh/bash)

    export DERBY_HOME=/home/user/derby/db-derby-10.x.y.z-bin

    Windows:

    set DERBY_HOME=c:\programs\derby\db-derby-10.x.y.z-bin

  3. Change directories to the $DERBY_HOME/demo/programs/simple directory.
  4. In the command window, set the CLASSPATH to include the current directory (the location of SimpleApp.class) and Derby's embedded driver library (derby.jar). (You may skip this step and provide the classpath as an option to the JVM launch command instead, refer to your JVM's documentation for details).

    This may be done as follows:

    UNIX (ksh/bash):

    export CLASSPATH=.:${DERBY_HOME}/lib/derby.jar

    WINDOWS:

    set CLASSPATH=.;%DERBY_HOME%\lib\derby.jar

  5. (Optional) Run Derby's Sysinfo utility for testing the classpath for an embedded environment. You should 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 CLASS PATH:
   Derby embedded engine library (derby.jar)
   /home/user/derby/db-derby-10.x.y.z-bin/lib/derby.jar


   user-specified class (SimpleApp)
   /home/user/derby/db-derby-10.x.y.z-bin/demo/programs/simple


SUCCESS: All Derby related classes found in class path.

    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: 

    FOUND IN CLASS PATH:

   Derby embedded engine library (derby.jar)
   /home/user/derby/db-derby-10.x.y.z-bin/lib/derby.jar



NOT FOUND IN CLASS PATH:

   user-specified class (SimpleApp)
    (SimpleApp not found.)
  6. Once you have your environment set up correctly, execute the application from the same directory (demo/programs/simple):

    java SimpleApp

    The demo program's default framework is embedded, so there is no need to specify this explicitly.

    A successful run produces the following output:

    SimpleApp starting in embedded mode
Connected to and created database derbyDB
Created table location
Inserted 1956 Webster
Inserted 1910 Union
Updated 1956 Webster to 180 Grand
Updated 180 Grand to 300 Lakeshore
Verified the rows
Dropped table location
Committed the transaction
Derby shut down normally
SimpleApp finished

    If any error messages appear, and you are unable to resolve the error(s), ask for help on the derby-user mailing list.

How to run this sample application in a client/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. The Network Server needs to be running before you can connect using a client driver. This demo includes support for the Derby client driver.

You must start the Network Server before trying to run the demo application in one of the client modes.

Starting 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_HOME environment variable to the location of this Derby installation. This is not strictly required to run the demo, but this environment variable will be used later on this page to refer to the required Derby resources, files, etc.

    Examples:

    UNIX (ksh/bash):

    export DERBY_HOME=/home/user/derby/db-derby-10.x.y.z-bin

    Windows:

    set DERBY_HOME=c:\programs\derby\db-derby-10.x.y.z-bin

  3. Change directories to the $DERBY_HOME/demo/programs/simple directory.
  4. In the command window for the server, start the Network Server by running the executable jar file derbyrun.jar.

    Examples:

    UNIX (ksh/bash):

    java -jar $DERBY_HOME/lib/derbyrun.jar server start

    Windows:

    java -jar %DERBY_HOME%\lib\derbyrun.jar server start

    When the server starts, the server console output will resemble something like this: 

    Security manager installed using the Basic server security policy.
Apache Derby Network Server - <version> - (<svnrevision>) started and ready to accept connections on port 1527 at <timestamp>

    (When you are done with the demo, you may shut down the network server for example by passing the arguments server shutdown to derbyrun.jar in a new command window.)

    A note on running the Network Server

    You may start the server in a number of other ways, including:

    • Invoking the Network Server's main class from the command line.

      java -cp $DERBY_HOME/lib/derbynet.jar org.apache.derby.drda.NetworkServerControl start

    • Accessing the NetworkServerControl API from a Java program.
    • Running a so-called "embedded server" by setting the property derby.drda.startNetworkServer=true before loading the embedded driver (with derbynet.jar in the classpath).
    • Running the script $DERBY_HOME/bin/startNetworkServer (%DERBY_HOME%\bin\startNetworkServer.bat on Windows).

    Note that unless you set the system property derby.system.home, Derby's default database location is the working directory of the Network Server JVM.

Using the Derby client driver (derbyclient)

The Derby client driver is a JDBC driver included in binary distributions of Apache Derby, in the directory $DERBY_HOME/lib/. It is recommended that you use this driver to connect to the Derby Network Server, as this client driver is developed and maintained by the Apache Derby development community.

Class name:org.apache.derby.jdbc.ClientDriver
Library:derbyclient.jar

 

  1. Open a command window
  2. If you haven't set it already on a system-wide basis, set the DERBY_HOME environment variable to the location of this Derby installation. This is not strictly required to run the demo, but this environment variable will be used later on this page to refer to the required Derby resources, files, etc. Examples:

    UNIX (ksh/bash):

    export DERBY_HOME=/home/user/derby/db-derby-10.x.y.z-bin

    Windows:

    set DERBY_HOME=c:\programs\derby\db-derby-10.x.y.z-bin

  3. Change directories to the $DERBY_HOME/demo/programs/simple directory.
  4. In the client command window, set the CLASSPATH to include the current directory (the location of SimpleApp.class) and Derby's client driver library (derbyclient.jar). (You may skip this step and provide the classpath as an option to the JVM launch command instead, refer to your JVM's documentation for details).

    This may be done as follows:

    UNIX (ksh/bash):

    export CLASSPATH=.:${DERBY_HOME}/lib/derbyclient.jar

    WINDOWS:

    set CLASSPATH=.;%DERBY_HOME%\lib\derbyclient.jar

  5. (Optional) Run Derby's Sysinfo utility for testing the classpath in your environment. You should provide the arguments client (to indicate a client 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 client SimpleApp.class

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

    FOUND IN CLASS PATH:

   Derby Client libraries (derbyclient.jar)
   /home/user/derby/db-derby-10.x.y.z-bin/lib/derbyclient.jar


   user-specified class (SimpleApp)
   /home/user/derby/db-derby-10.x.y.z-bin/demo/programs/simple


SUCCESS: All Derby related classes found in class path.
  6. Start the Derby Network Server in a different command window, as described here
  7. Start SimpleApp in Derby client mode:

    java SimpleApp derbyclient

    The derbyclient argument tells the program to use the Derby client driver instead of its default driver (the embedded driver).

    A successful run produces the following output:

    SimpleApp starting in derbyclient mode
Connected to and created database derbyDB
Created table location
Inserted 1956 Webster
Inserted 1910 Union
Updated 1956 Webster to 180 Grand
Updated 180 Grand to 300 Lakeshore
Verified the rows
Dropped table location
Committed the transaction
SimpleApp finished

    If any error messages appear, and you are unable to resolve the error(s), ask for help on the derby-user mailing list.

Last updated for Apache Derby Version 10.11