![Close [x]](http://dw1.s81c.com/i/c.gif){kind=small}
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.
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-10.1.2.1-bin'
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-10.1.2.1-bin
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 /opt/Apache/db-derby-10.1.2.1-bin/lib/derby.jar: /opt/Apache/db-derby-10.1.2.1-bin/lib/derbytools.jar:. rb$ mkdir derbyWork rb$ cd derbyWork rb$ java org.apache.derby.tools.ij 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:
-
jdbcis the mechanism with which the ij tool communicates with the database server. -
derbyis the name of the JDBC driver class that the ij tool can use to communicate with the database server. -
testis the name of the database to create. -
create=trueis 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/ service.properties 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 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 org.apache.derby.tools.ij
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
connection
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
MAXIMUMDISPLAYWIDTH integerValue;
-- 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
GET [SCROLL INSENSITIVE] CURSOR name AS 'SQL-J query';
-- 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
LOCALIZEDDISPLAY [ ON | OFF ];
-- 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.
ij>
|
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.
Learn
- Read the first article in this series, "Developing with Apache Derby: Hitting the Trifecta: Introduction to Apache Derby" (developerWorks, February 2006), for instructions on how
to download and install
the Apache Derby software.
- Visit the official JDBC home page for more information on JDBC.
- Get more detailed information about how to use the Apache Derby database from many of the Apache Derby project's online manuals.
- Reference the Apache Derby project's tutorial that details how to
download and install Apache Derby.
- Check out the developerWorks Apache Derby project area for articles, tutorials, and other resources to help you get started with Derby today.
- 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.
Get products and technologies
- Download Apache Derby from the Apache Derby Project home page.
Discuss
- Get involved in the developerWorks community by participating in developerWorks blogs.
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. You can reach him at rb@ncsa.uiuc.edu.
