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

Providing a secure and efficient Helpdesk

More from The Register

next story
Google+ goes TITSUP. But WHO knew? How long? Anyone ... Hello ...
Wobbly Gmail, Contacts, Calendar on the other hand ...
Preview redux: Microsoft ships new Windows 10 build with 7,000 changes
Latest bleeding-edge bits borrow Action Center from Windows Phone
Microsoft promises Windows 10 will mean two-factor auth for all
Sneak peek at security features Redmond's baking into new OS
UNIX greybeards threaten Debian fork over systemd plan
'Veteran Unix Admins' fear desktop emphasis is betraying open source
Google opens Inbox – email for people too stupid to use email
Print this article out and give it to someone techy if you get stuck
DEATH by PowerPoint: Microsoft warns of 0-day attack hidden in slides
Might put out patch in update, might chuck it out sooner
Redmond top man Satya Nadella: 'Microsoft LOVES Linux'
Open-source 'love' fairly runneth over at cloud event
prev story

Whitepapers

Cloud and hybrid-cloud data protection for VMware
Learn how quick and easy it is to configure backups and perform restores for VMware environments.
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?
Three 1TB solid state scorchers up for grabs
Big SSDs can be expensive but think big and think free because you could be the lucky winner of one of three 1TB Samsung SSD 840 EVO drives that we’re giving away worth over £300 apiece.
Security for virtualized datacentres
Legacy security solutions are inefficient due to the architectural differences between physical and virtual environments.