Java Magazine - May/June 2012

//java architect /

What’s New in Java DB in JDK 7

COMMUNITY

Improvements now make it even easier to start using databases and SQL in your apps.

KNU T HATLEN

Java DB is a relational database written purely in the Java programming language. It is based on and is compatible with the open source Apache Derby database.

Java DB has been available as part of the JDK since JDK 6, and it can be found in the db sub-directory of the JDK installation. Its presence in the JDK makes it easy for Java developers to start using databases and SQL in their applications.

Because Java DB adheres closely to the JDBC and SQL standards, it is upwardly compatible with many enterprise database systems. You can use Java DB as a starting point for developing data-rich applications, and then switch to an enterprise-caliber database later.

The version of Java DB in JDK 6 was based on Apache Derby 10. 2. The version in JDK 7 is based on Apache Derby 10. 8, which has many improvements and new features compared to the old version. This article surveys some of these improvements and is based on a presentation that Rick Hillegas, a member of the Java Class Libraries team at Oracle, gave at JavaOne 2011.

Better Integration with Java Code

Java DB has always allowed you to write user-defined routines in the Java language and use them in your SQL statements. In JDK 7, Java DB has two new features that enable you to take even more advantage of the Java platform inside the database: user-defined types and table functions. User-defined types. User-defined types allow you to define new datatypes that correspond to Java classes or interfaces and use those types just like any of the built-in datatypes, as columns in a table or as parameters in functions or procedures. The only

You can use Java DB as a starting point for developing data-rich applications, and then switch to an enterprise-caliber database later.

requirement is that the Java class must implement the java . io.Serializable interface.

The user-defined types can be defined both for your own application classes and for classes that exist in the standard class library of the Java runtime environment. Listing 1 shows an example of how to define and use a type that allows you to store java.util.List objects in a table. Table functions. A table function is a variant of user-defined functions. In contrast to an ordinary user-defined function, which returns a scalar value, a table function returns a tabular data set and can be used in the FROM clause of a SELECT statement. This allows you to query heterogeneous datasources using SQL.

Table functions are implemented as static Java methods that return instances of java.sql .ResultSet. Java DB provides an abstract class, org.apache.derby .vti.V TITemplate, with default implementations for most of the ResultSet methods, which makes it easier to write a custom ResultSet class.

Listing 2 shows an example of how to use VTITemplate to write your own table function. That table function reads a text file and returns a table with one row per line in the file, where each row has two columns: the line number and the text found on the line.

The following are the most important parts of the table function class:

; ; The public static readFile() method that creates a new instance

; ; The next() method that reads the next line of the file

; ; The getInt() and getString() methods that return the column values for the current row

To wire the table function into your database, you need to declare it using a CREATE FUNCTION statement. Listing 3 shows how to do that in ij, the Java DB interactive scripting tool, and it shows a sample query that uses the table function.