Feeds

Java databasing with Derby

Java's own open source database

Combat fraud and increase customer satisfaction

It sometimes feels as if open source databases are a dime a dozen. There are the big names like MySQL, PostgreSQL, and Ingres. There are plenty of other lesser known but no less powerful open source databases: Firebird, SQLite, HSQLDB, Apache Derby, IBM Cloudscape, and Java DB.

Actually, I cheated there at the end, those last three are the same database sporting different branding. Apache Derby – which is the name we'll stick with throughout the rest of this article – used to belong to Informix, who had bought a company called Cloudscape, who had developed a SQL-compliant Java relational database. Informix was in turn swallowed up by IBM, who eventually open sourced Cloudscape by contributing the code to Apache Software Foundation. Cloudscape then became Apache Derby; though when Sun Microsystems decided to add it to the Java 6.0 SDK, they christened it the Java DB.

Now that we've got all that clear, what actually is Apache Derby and why should you be interested? Derby is a relational database management system written completely in Java. It offers a high level of SQL standards-compliance, native access using JDBC, works both as an embedded database or in client/server mode and has a relatively small footprint (around 2MB).

But it's not just compact, it's powerful too, and supports transactions, referential integrity, stored procedures (written in Java), and in client/server mode supports bindings to ODBC, PHP, Perl, and Python. Being Java also makes it multi-platform, and it uses any certified Java Virtual Machine so that its availability is maximised across platforms.

This tutorial will concentrate mainly on using Derby as an embedded database for a Java application. In other words Derby will be used as a persistent data store, and it is the application which will manage the database. This is in contrast to client/server mode, where the database is loaded onto a server and waits for client applications to connect to it.

Installation

Installation is remarkably painless. It's a simple case of downloading the zip files (fromhere) and unzipping to an appropriate directory. We can easily check that installation has been successful by using the sysinfo tool in the bin directory (sysinfo.bat for Windows). To do this requires the setting up of an environment variable called DERBY_HOME to point to the bin directory, and for this to be added to the PATH. For example under Windows:

set DERBY_HOME=C:\Apache\db-derby-10.2.1.6-bin
set PATH=%DERBY_HOME%\bin;%PATH%

With these environment variables in place running sysinfo at the command prompt will spool out a listing of information about the Java environment and the installation of Derby that it has found.

Command-line Access

Sticking to the command-line for the moment, we can use the ij tool to interact with Derby. This provides a command-line from which we can connect to a database instance and issue SQL commands to create tables, enter data, and submit queries.

The first thing we'll do is create a new database, called AssetDB, which we'll use later with some Java code. It will be a simple DB with just two tables to hold user names and a basic asset register. Assuming that we want the database to be in the C:\DerbyDB directory we create it by loading the ij command-line (type ij from a command-prompt), and then at the ij> prompt entering:

connect 'jdbc:derby:/DerbyDB/AssetDB;create=true';

This creates the directory tree C:\DerbyDB\AssetDB. To populate the database we can either enter the CREATE TABLE statements direct from the ij> prompt, or we can run them from a text file. We'll do the latter and enter the following commands into a file called create_sql.txt in the DerbyDB directory:

CREATE TABLE USERS ( 
FIRST_NAME VARCHAR(30) NOT NULL,
LAST_NAME VARCHAR(30) NOT NULL,
EMP_NO INTEGER NOT NULL CONSTRAINT EMP_NO_PK PRIMARY KEY
);

CREATE TABLE PC (
TYPE VARCHAR(10) NOT NULL,
SERIAL VARCHAR(50),
OS VARCHAR(20),
EMP_NO INTEGER,
CODE_NO INTEGER NOT NULL CONSTRAINT CODE_NO_PK PRIMARY KEY
);

To run these from ij> enter the following command: run '/ DerbyDB/create_sql.txt';

The SQL commands will be executed in turn and ij will report that '0 rows have been inserted/updated/deleted'. Running describe USERS; will produce a listing of the columns and column meta-data for the table.

We can also use ij to enter some data. Again we'll use a text file to contain the following INSERT queries:

INSERT INTO USERS VALUES('Bill','Gates',1);
INSERT INTO USERS VALUES('Joe','Bloggs',2);
INSERT INTO USERS VALUES('Peter','Kropotkin',3);
INSERT INTO PC VALUES('Desktop','01010','Linux',1,1);
INSERT INTO PC VALUES('Laptop','101010','BSD',2,2);
INSERT INTO PC VALUES('Desktop','101010','XP',3,12);

Finally, we can run a SELECT query: SELECT * FROM USERS;

To quit ij simply enter: exit;

And Java?

So far we've used a command-line tool to create a simple database and enter a few rows of data; big deal. Now we'll show how simple it is to embed that database in a Java application. The first step is to make sure that the derby.jar file is on your classpath or is included with your project in Eclipse, NetBeans or other IDE.

Setting up the database just uses standard JDBC functionality. Register the JDBC driver, provide a URL for the connection, and attempt the connection:

String driver = "org.apache.derby.jdbc.EmbeddedDriver";
String dbName="/DerbyDB/AssetDB";
String connectionURL = "jdbc:derby:" + dbName; 
Connection conn = null;

try{
    Class.forName(driver); 
} catch(java.lang.ClassNotFoundException e) {
    e.printStackTrace();
}

try {
    conn = DriverManager.getConnection(connectionURL); 

    //body of code to go here

}  catch (Throwable e)  {   
    e.printStackTrace();
} finally {
    conn.close();
}

Once the connection is made it's possible to create a statement object and to use it to access the database. Here we want to add another record to the database:

Statement st=conn.createStatement()
int m=st.executeUpdate("INSERT INTO USERS VALUES('Adam','Smith',4)");
System.out.println("Updated " + m + " rows"); 

Executing the above should result in the message “Updated 1 rows” appearing in console output.

SELECT queries are no more difficult:

ResultSet rs=st.executeQuery("SELECT * FROM USERS");
while (rs.next()){
    String first=rs.getString("FIRST_NAME");
    String last=rs.getString("LAST_NAME");
    System.out.println("Name: " + first + " " + last);
}
rs.close();

Joins are straightforward too:

String qry="SELECT USERS.FIRST_NAME, USERS.LAST_NAME,"
    + " PC.OS FROM USERS, PC WHERE USERS.EMP_NO=PC.EMP_NO"
    + " AND USERS.EMP_NO=1";
rs=st.executeQuery(qry);
while (rs.next()){
    String first=rs.getString(1);
    String last=rs.getString(2);
    String os=rs.getString(3);
    System.out.println(first + " " + last + " uses " + os);
}
rs.close();

This prints out the message: Bill Gates uses Linux

Conclusion

If you add to its simplicity of setup, ease of use, and small footprint, the fact that Derby supports referential integrity and ACID-compliant transactions, you have a pretty powerful database tool for your Java applications. ®

High performance access to file storage

More from The Register

next story
This time it's 'Personal': new Office 365 sub covers just two devices
Redmond also brings Office into Google's back yard
Batten down the hatches, Ubuntu 14.04 LTS due in TWO DAYS
Admins dab straining server brows in advance of Trusty Tahr's long-term support landing
Microsoft lobs pre-release Windows Phone 8.1 at devs who dare
App makers can load it before anyone else, but if they do they're stuck with it
Half of Twitter's 'active users' are SILENT STALKERS
Nearly 50% have NEVER tweeted a word
Oh no, Joe: WinPhone users already griping over 8.1 mega-update
Hang on. Which bit of Developer Preview don't you understand?
Internet-of-stuff startup dumps NoSQL for ... SQL?
NoSQL taste great at first but lacks proper nutrients, says startup cloud whiz
Windows 8.1, which you probably haven't upgraded to yet, ALREADY OBSOLETE
Pre-Update versions of new Windows version will no longer support patches
Ditch the sync, paddle in the Streem: Upstart offers syncless sharing
Upload, delete and carry on sharing afterwards?
Microsoft TIER SMEAR changes app prices whether devs ask or not
Some go up, some go down, Redmond goes silent
prev story

Whitepapers

Designing a defence for mobile apps
In this whitepaper learn the various considerations for defending mobile applications; from the mobile application architecture itself to the myriad testing technologies needed to properly assess mobile applications risk.
3 Big data security analytics techniques
Applying these Big Data security analytics techniques can help you make your business safer by detecting attacks early, before significant damage is done.
Five 3D headsets to be won!
We were so impressed by the Durovis Dive headset we’ve asked the company to give some away to Reg readers.
The benefits of software based PBX
Why you should break free from your proprietary PBX and how to leverage your existing server hardware.
Securing web applications made simple and scalable
In this whitepaper learn how automated security testing can provide a simple and scalable way to protect your web applications.