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

Start working with Derby today

Want to start using Apache Derby right now? This multi-part series featured in the "Developing with Apache Derby: Hitting the Trifecta" column will get you up and running with the Derby database in no time. This first installment introduces the basic concepts of relational databases with an emphasis on how these topics relate to the Derby database. You'll learn about the ACID test, demonstrated with a simple example, and the ij tool, which you can use to interactively connect to and query an Apache Derby database. Then the rest of this series will help you become a master at working with Apache Derby.

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

Robert J. BrunnerRobert 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. You can reach him at

28 March 2006

Also available in Russian Japanese

Introduction to relational database systems

You can begin developing database applications quickly, but you can spend a lifetime mastering them. Fortunately, Apache Derby is a lightweight database, and you won't need to devote your life to becoming proficient. You'll be able to start working with it after following the steps in this article.

But the goal of this article series isn't just to get you started; it's to help you master working with Apache Derby. First, you should get acquainted with the ACID test, which provides a succinct encapsulation of the basic tenets of a relational database.

The ACID test

Diamonds are obviously a valuable commodity -- so valuable that counterfeits are a serious concern. One simple and (at least, in the movies) popular test to determine whether a diamond is real is to run it across a piece of glass. Because diamonds are one of the hardest materials known, a real diamond easily cuts the glass surface; a fake, especially if it's made of glass itself, won't.

To a software developer, databases are equally valuable. If you use a database, you want to be sure it will safely store your data and let you easily retrieve the data later. You also want your database to allow multiple programs (or people) to work with the database without interfering with each other. To demonstrate, imagine you own a bank. The database for your bank must do the following, among other things:

  • Safely store the appropriate data
  • Quickly retrieve the appropriate data
  • Support multiple, concurrent user sessions

These tasks can be collectively referred to as the ACID test; ACID is an acronym for Atomicity, Consistency, Isolation, and Durability.

Atomicity means that operations with the database can be grouped together and treated as a single unit.

Consistency guarantees that either all the operations in this single unit (or transaction) are performed successfully, or none of them is performed. In other words, a database can't be in an unfinished state. To understand why these characteristics are important, think about a bank transaction during which money is transferred from a savings account into a checking account. If the transfer process fails after subtracting the money from your savings account and before it was added to your checking account, you would become poorer, and the bank would have an angry (ex)customer! Atomicity enables the two operations -- the subtraction from the savings account and the addition to the checking account -- to be treated as a single transaction. Consistency guarantees that both operations in the transaction either succeed or fail. That way, your money isn't lost.

Isolation means that independent sets of database transactions are performed in such a way that they don't conflict with each other. Continuing the bank analogy, consider two customers who transfer funds between accounts at the same time. The database must track both transfers separately; otherwise, the funds could go into the wrong accounts, and the bank might be left with two angry (ex)customers.

Durability guarantees that the database is safe against unexpected terminations. It may be a minor inconvenience if your television or computer won't work when the power goes out, but the same can't be said for a database. If the bank's computers lose power when transferring your funds, you won't be a happy customer if the transaction is lost. Durability guarantees that if the database terminates abnormally during a funds transfer, then when the database is brought back up, it will be able to recover the transaction and continue with normal operations.

Passing the ACID test is nontrivial, and many simple databases fall short. For critical e-business or Web-based applications, passing the ACID test is a must. This is one of the reasons so many companies and individuals utilize enterprise-level database systems, such as IBM DB2® Universal Database, Oracle 10g, or Microsoft® SQL Server. These databases are fully compliant with the ACID test. Compared to these database systems, the Apache Derby database may seem like the forgotten stepchild. However, Apache Derby is fully compliant with the ACID test, and you can use it with confidence to develop Web-based database applications. In addition, if you begin using Apache Derby, and your application (or business) grows, you can migrate your database application to an enterprise-class database system like DB2 with minimal difficulty.

Create a database with Apache Derby

Before you can use a database, you have to create one. As part of the Derby download and installation process, you acquire several tools. One of these, ij, is an interactive Java™ tool for communicating with the Apache Derby database server. The rest of this article shows you how to use the ij tool to perform simple database operations, like creating a database.

In the first article in this series, you learned how to download and install the Derby software. If you haven't read that article, you should do so now. In particular, follow the directions so that you have a working version of the Apache Derby software on your computer.

The end of the first article discusses how to properly set up your system to use the Derby database. To summarize, because Derby is a Java application, the CLASSPATH environment variable must be properly initialized. You can set the DERBY_INSTALL environment variable, and then add the relevant Java Archive (JAR) files to your CLASSPATH in your shell initialization file (such as the .bashrc file) by typing the following commands:

export DERBY_INSTALL='/opt/Apache/db-derby-'
export CLASSPATH=$DERBY_INSTALL/lib/derby.jar
export CLASSPATH=$CLASSPATH:$DERBY_INSTALL/lib/derbytools.jar:.

You can achieve a similar effect on a Microsoft Windows® system by creating a batch file with the following commands:

set DERBY_INSTALL=C:\Apache\db-derby-
set CLASSPATH=%DERBY_INSTALL%\lib\derby.jar
set CLASSPATH=%CLASSPATH%;%DERBY_INSTALL%\lib\derbytools.jar;.

Note that in these two examples, the initializations for the CLASSPATH environment variable (via either an export or a set shell command) are done in two steps. This is purely due to space limitations in the article. You can use one line or two in your own variable initializations.

Now you can run this batch file whenever you open a new command prompt. In either the Windows or UNIX® script file, be sure to properly specify the directory where you installed the Derby software.

After your environment is set up properly, you can run the ij tool to create a new directory, as shown in Listing 1.

Listing 1. Using the ij tool
rb$ echo $CLASSPATH
rb$ mkdir derbyWork
rb$ cd derbyWork
rb$ java
ij version 10.1
ij> connect 'jdbc:derby:test;create=true' ;
ij> exit ;
rb$ ls -CF
derby.log       test/

First, you display the CLASSPATH environment variable. Doing this occasionally is a good way to avoid errors later. In this case, the Derby JAR files are clearly present. The next step is to create and then change to a new directory in which you can work (in this example, the directory is called derbyWork, but you can use any name you like). This will make it easier to see what happens when you create a new database using the ij tool. When the preliminaries are out of the way, you're ready to fire up ij.

Because the ij tool is a Java application, you need to start up a Java Virtual Machine (JVM), and indicate the name of the appropriate main class you want to run -- in this case, the ij tool. If you get a Java exception, recheck your CLASSPATH to be sure the derbytools JAR file is present.

When the ij tool starts, it displays version information and provides a prompt, which is a greater-than symbol (>) by default. At this prompt, run the connect command, which connects to a database. The connect command requires an indicator string to find the database to which it should connect. In this example, this connect string is jdbc:derby:test;create=true. Formally, this string is known as a Java Database Connectivity (JDBC) URL. (JDBC is a Java technology that allows Java applications to communicate with a database.)

A full analysis of JDBC URLs is beyond the scope of this article (see the Resources section for more information). This example, however, is simple, and can be broken down as follows:

  • jdbc is the mechanism with which the ij tool communicates with the database server.
  • derby is the name of the JDBC driver class that the ij tool can use to communicate with the database server.
  • test is the name of the database to create.
  • create=true is a specific property that should be passed to the Derby JDBC driver. JDBC properties are listed after the rest of the URL and are separated by semicolons.

To be accessed properly, the JDBC URL must be passed into the connect command as a string; hence it's enclosed in single quotation mark (') characters. Finally, to tell the ij tool to process your command, you must add a semicolon (because ij is a Java tool) and press Return. After a brief delay, the ij tool provides you with a new prompt. That's it -- a new database has been created.

To see what happened, exit from the ij tool by using the exit command (don't forget the semicolon), and look in the directory where you ran the ij tool (either with the ls command on a UNIX system or with the dir command on a Windows system). You'll have a new file, derby.log, along with a new directory, called test, which matches the name you gave your database in the JDBC URL.

If you're curious, feel free to explore the test directory that was created for your database, as shown in Listing 2. (The names and contents of your directories may differ slightly from those shown here.)

Listing 2. The contents of the test directory
rb$ ls -CF test/
log/                    seg0/           
rb$ ls -CF test/log/
log.ctrl        log1.dat        logmirror.ctrl
rb$ ls -CF test/seg0/ 
c10.dat         c191.dat        c221.dat        c2c1.dat       c90.dat
c101.dat        c1a1.dat        c230.dat        c2d0.dat       ca1.dat
c111.dat        c1b1.dat        c241.dat        c2e1.dat       cb1.dat
c121.dat        c1c0.dat        c251.dat        c2f0.dat       cc0.dat
c130.dat        c1d1.dat        c260.dat        c31.dat        cd1.dat
c141.dat        c1e0.dat        c271.dat        c41.dat        ce1.dat
c150.dat        c1f1.dat        c281.dat        c51.dat        cf0.dat
c161.dat        c20.dat         c290.dat        c60.dat
c171.dat        c200.dat        c2a1.dat        c71.dat
c180.dat        c211.dat        c2b1.dat        c81.dat

As you explore these directories, you may be amazed at all the action that occurred when you created the new directory. But remember, Apache Derby is an ACID-compliant database, and, as a result, a lot goes on behind the scenes. Inside the database main directory (in this case, test) are a log directory, a seg0 directory, and a properties file. The log directory holds the database-specific log files that let Derby record the operations that occur during a set of database operations (a transaction). If the Derby database server is terminated for some reason during the operation, it can pick up where it left off and recover the database to a normal state.

In this example, the seg0 directory holds the data files that you'll use for the test database. It may seem odd that all those files were generated when your database was just created and is, presumably, empty. The reason for the files is simple: When a database stores data, it doesn't just create one new file and dump the data into this file. For performance reasons (and to satisfy the ACID test), the database spreads data into a number of files. Data is written to these files in special structures known as pages. The content of a data page is both the data and information about the data on the page (sometimes called metadata). By generating all these files (and pages within the files) when the database is first created, the database can begin to store data as soon as necessary.

The ij tool

The ij tool is powerful. You can use it (and you will in future articles) to perform a wide range of operations, including creating a database (as you've already seen) and creating new items inside a database. The ij tool includes a help command that lists and describes some common commands you may want to use, as shown in Listing 3. Note that the listing has been reformatted to fit within space limitations, so your version may appear slightly different.

Listing 3. Getting help for the ij tool
rb$ java
ij version 10.1
ij> help ;
 Supported commands include:
  PROTOCOL 'JDBC protocol' [ AS ident ];
                               -- sets a default or named protocol
  DRIVER 'class for driver';   -- loads the named class
  CONNECT 'url for database' [ PROTOCOL namedProtocol ] 
                             [ AS connectionName ];
                               -- connects to database URL
                               -- and may assign identifier
  SET CONNECTION connectionName; -- switches to the specified 
  SHOW CONNECTIONS;            -- lists all connections
  AUTOCOMMIT [ ON | OFF ];     -- sets autocommit mode for the 
                               -- connection
  DISCONNECT [ CURRENT | connectionName | ALL ];
                               -- drop current, named, or all 
                               -- connections; the default is CURRENT
  COMMIT;                      -- commits the current transaction
  ROLLBACK;                    -- rolls back the current transaction
  PREPARE name AS 'SQL-J text'; -- prepares the SQL-J text
  EXECUTE { name | 'SQL-J text' } [ USING { name | 'SQL-J text' } ] ;
                               -- executes the statement with 
                               -- parameter values from the USING 
                               -- result set row
  REMOVE name;                 -- removes the named previously 
                               -- prepared statement
  RUN 'filename';              -- run commands from the named file
  ELAPSEDTIME [ ON | OFF ];    -- sets elapsed time mode for ij
                               -- sets the maximum display width for
                               -- each column to integerValue
  ASYNC name 'SQL-J text';     -- run the command in another thread
  WAIT FOR name;               -- wait for result of ASYNC'd command
                               -- gets a cursor (JDBC result set) 
                               -- on the query
                               -- SCROLL cursors are only available 
                               -- in JDBC 2.0 and higher. 
                               -- (Cursor scroll type is ignored in 
                               -- JDBC 1.X.) 
  NEXT name;                   -- gets the next row from the 
                               -- named cursor
  FIRST name;                  -- gets the first row from the 
                               -- named scroll cursor
  LAST name;                   -- gets the last row from the 
                               -- named scroll cursor
  PREVIOUS name;               -- gets the previous row from the 
                               -- named scroll cursor
  ABSOLUTE integer name;       -- positions the named scroll cursor
                               -- at the absolute row number
                               -- (A negative number denotes 
                               -- position from the last row.) 
  RELATIVE integer name;       -- positions the named scroll cursor 
                               -- relative to the current row
                               -- (integer is number of rows)
  AFTER LAST name;             -- positions the named scroll cursor 
                               -- after the last row
  BEFORE FIRST name;           -- positions the named scroll cursor 
                               -- before the first row
  GETCURRENTROWNUMBER name;    -- returns the row number for the 
                               -- current position of the named 
                               -- scroll cursor (0 is returned when 
                               -- the cursor isn't positioned 
                               -- on a row.) 
  CLOSE name;                  -- closes the named cursor
                               -- controls locale sensitive data 
                               -- representation
  EXIT;                        -- exits ij
  HELP;                        -- shows this message
 Any unrecognized commands are treated as potential SQL-J commands 
 and executed directly.

Most of the commands shown in Listing 3 probably seem alien, but that's OK -- you're just getting started in your race to learn about Apache Derby.

You can also make more than one connection to a database with the ij tool, as shown in Listing 4.

Listing 4. Making connections with the ij tool
ij> connect 'jdbc:derby:test;create=true' ;
WARNING 01J01: Database 'test' not created, 
connection made to existing database instead.
ij> connect 'jdbc:derby:test' ;
ij(CONNECTION1)> show connections ;
CONNECTION0 -   jdbc:derby:test
CONNECTION1* -  jdbc:derby:test
* = current connection
ij(CONNECTION1)> disconnect ;
ij> show connections ;
CONNECTION0 -   jdbc:derby:test
No current connection
ij> set connection CONNECTION0 ;
ij> show connections ;
CONNECTION0* -  jdbc:derby:test
* = current connection
ij> exit ;

This example first tries to connect to the test database using the original JDBC URL. You get a warning, however, because the test database already exists. The code issues a new connect command, changing the JDBC URL so that the ;create=true JDBC property is absent. This time no warning is issued, but the prompt changes to include a (CONNECTION1) string. This may seem unusual because nothing like this happened when you first created the test database.

This result demonstrates that, despite the warning, your first attempt to create a connection to the test database succeeded. To show that you now have two connections to the same database, issue a show connections ; command, which displays two connections, their associated URLs, and which connection is current (it's CONNECTION1).

You don't need two connections to the same database right now, so you can use the disconnect ; command to close the current connection. Issuing another show connections ; command displays the only currently open connection, but you also see that you don't have a current connection. Because you need an active or current connection to send commands to a specific database, you should change the current connection appropriately. This is easily accomplished by using the set connections command with the name of the target connection as the final parameter. Now, when you issue another show connections ; command, you see the list of current connections along with the name of the current connection. Finally, issue an exit ; command to disconnect all current connections and terminate the ij tool.


This article introduced relational databases, focusing on the ACID test. The ACID test lets developers gauge the utility of a database system. If you require a Java-based, lightweight database that supports the full capabilities required to pass the ACID test, you should adopt the Apache Derby database. This article also introduced the ij tool, which comes with the Derby database. The ij tool can be used to connect to and issue commands to a database. This article used the ij tool to create a new database and then explored the directories and files that were generated when Derby created the new database.

Check back in for the next installment of this series in which you'll get a comprehensive overview of database development using schemas.



Get products and technologies

  • Download Apache Derby from the Apache Derby Project home page.



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

Zone=Open source, Information Management
ArticleTitle=Developing with Apache Derby -- Hitting the Trifecta: Database development with Apache Derby, Part 1