Java databasing with Derby

Java's own open source database


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


Other stories you might like

  • Employers in denial over success of digital skills training, say exasperated staffers

    Large disparities in views from bosses vs workers on 'talent transformation initiatives,' says survey

    Digital transformation projects are being held back by a lack of skills, according to a new survey, which finds that while many employers believe they are doing well at training up existing staff to meet the requirements, their employees beg to differ.

    Skills shortages are nothing new, but the Talent Transformation Global Impact report from research firm Ipsos on behalf of online learning provider Udacity indicates that although digital transformation initiatives are stalling due to a lack of digital talent, enterprises are becoming increasingly out of touch with what their employees need to fill the skills gap.

    The report is the result of two surveys taking in over 2,000 managers and more than 4,000 employees across the US, UK, France, and Germany. It found that 59 per cent of employers state that not having enough skilled employees is having a major or moderate impact on their business.

    Continue reading
  • Saved by the Bill: What if... Microsoft had killed Windows 95?

    Now this looks like a job for me, 'cos we need a little, controversy... 'Cos it feels so NT, without me

    Former Microsoft veep Brad Silverberg has paid tribute to Bill Gates for saving Windows 95.

    Silverberg posted his comment in a Twitter exchange started by Fast co-founder Allison Barr Allen regarding somebody who'd changed your life. Silverberg responded "Bill Gates" and, in response to a question from Microsoft cybersecurity pro Ashanka Iddya, explained Gates's role in Windows 95's survival.

    Continue reading
  • UK government opens consultation on medic-style register for Brit infosec pros

    Are you competent? Ethical? Welcome to UKCSC's new list

    Frustrated at lack of activity from the "standard setting" UK Cyber Security Council, the government wants to pass new laws making it into the statutory regulator of the UK infosec trade.

    Government plans, quietly announced in a consultation document issued last week, include a formal register of infosec practitioners – meaning security specialists could be struck off or barred from working if they don't meet "competence and ethical requirements."

    The proposed setup sounds very similar to the General Medical Council and its register of doctors allowed to practice medicine in the UK.

    Continue reading
  • Microsoft's do-it-all IDE Visual Studio 2022 came out late last year. How good is it really?

    Top request from devs? A Linux version

    Review Visual Studio goes back a long way. Microsoft always had its own programming languages and tools, beginning with Microsoft Basic in 1975 and Microsoft C 1.0 in 1983.

    The Visual Studio idea came from two main sources. In the early days, Windows applications were coded and compiled using MS-DOS, and there was a MS-DOS IDE called Programmer's Workbench (PWB, first released 1989). The company also came up Visual Basic (VB, first released 1991), which unlike Microsoft C++ had a Windows IDE. Perhaps inspired by VB, Microsoft delivered Visual C++ 1.0 in 1993, replacing the little-used PWB. Visual Studio itself was introduced in 1997, though it was more of a bundle of different Windows development tools initially. The first Visual Studio to integrate C++ and Visual Basic (in .NET guise) development into the same IDE was Visual Studio .NET in 2002, 20 years ago, and this perhaps is the true ancestor of today's IDE.

    A big change in VS 2022, released November, is that it is the first version where the IDE itself runs as a 64-bit process. The advantage is that it has access to more than 4GB memory in the devenv process, this being the shell of the IDE, though of course it is still possible to compile 32-bit applications. The main benefit is for large solutions comprising hundreds of projects. Although a substantial change, it is transparent to developers and from what we can tell, has been a beneficial change.

    Continue reading
  • James Webb Space Telescope has arrived at its new home – an orbit almost a million miles from Earth

    Funnily enough, that's where we want to be right now, too

    The James Webb Space Telescope, the largest and most complex space observatory built by NASA, has reached its final destination: L2, the second Sun-Earth Lagrange point, an orbit located about a million miles away.

    Mission control sent instructions to fire the telescope's thrusters at 1400 EST (1900 UTC) on Monday. The small boost increased its speed by about 3.6 miles per hour to send it to L2, where it will orbit the Sun in line with Earth for the foreseeable future. It takes about 180 days to complete an L2 orbit, Amber Straughn, deputy project scientist for Webb Science Communications at NASA's Goddard Space Flight Center, said during a live briefing.

    "Webb, welcome home!" blurted NASA's Administrator Bill Nelson. "Congratulations to the team for all of their hard work ensuring Webb's safe arrival at L2 today. We're one step closer to uncovering the mysteries of the universe. And I can't wait to see Webb's first new views of the universe this summer."

    Continue reading

Biting the hand that feeds IT © 1998–2022