Feeds

Java databasing with Derby

Java's own open source database

Top 5 reasons to deploy VMware with Tegile

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. ®

Internet Security Threat Report 2014

More from The Register

next story
PEAK APPLE: iOS 8 is least popular Cupertino mobile OS in all of HUMAN HISTORY
'Nerd release' finally staggers past 50 per cent adoption
Microsoft to bake Skype into IE, without plugins
Redmond thinks the Object Real-Time Communications API for WebRTC is ready to roll
Microsoft promises Windows 10 will mean two-factor auth for all
Sneak peek at security features Redmond's baking into new OS
Mozilla: Spidermonkey ATE Apple's JavaScriptCore, THRASHED Google V8
Moz man claims the win on rivals' own benchmarks
FTDI yanks chip-bricking driver from Windows Update, vows to fight on
Next driver to battle fake chips with 'non-invasive' methods
DEATH by PowerPoint: Microsoft warns of 0-day attack hidden in slides
Might put out patch in update, might chuck it out sooner
Ubuntu 14.10 tries pulling a Steve Ballmer on cloudy offerings
Oi, Windows, centOS and openSUSE – behave, we're all friends here
Was ist das? Eine neue Suse Linux Enterprise? Ausgezeichnet!
Version 12 first major-number Suse release since 2009
prev story

Whitepapers

Choosing cloud Backup services
Demystify how you can address your data protection needs in your small- to medium-sized business and select the best online backup service to meet your needs.
A strategic approach to identity relationship management
ForgeRock commissioned Forrester to evaluate companies’ IAM practices and requirements when it comes to customer-facing scenarios versus employee-facing ones.
High Performance for All
While HPC is not new, it has traditionally been seen as a specialist area – is it now geared up to meet more mainstream requirements?
Top 5 reasons to deploy VMware with Tegile
Data demand and the rise of virtualization is challenging IT teams to deliver storage performance, scalability and capacity that can keep up, while maximizing efficiency.
The hidden costs of self-signed SSL certificates
Exploring the true TCO for self-signed SSL certificates, including a side-by-side comparison of a self-signed architecture versus working with a third-party SSL vendor.