Level: Introductory James Snell, Software Engineer, IBM
16 Dec 2004 The open source Derby database engine provides developers with a powerful new tool for lightweight database development. Derby databases are file system based and portable across different types of systems. Because of this, a common strategy is to create databases at development time and ship them with the binary distribution as opposed to having it created on the fly. In this article, author James Snell introduces a handful of Apache Ant tasks that make it easier to incorporate the building and distribution of Derby databases into an application’s build process.
Apache Ant is one of those rare tools that every developer must absolutely have in their development toolkit. Its flexibility and extensibility make it ideal for creating robust, flexible application build processes for nearly any type of application.
Apache Derby is a high quality, pure-Java™ embedded relational database engine that IBM® recently contributed to the open source community. Derby databases are file-system based, highly portable, and lightweight making them ideal for distribution.
For experimentation and development with Derby, Snell notes that it is helpful to integrate the creation and packaging of the Derby databases with the Ant-based compilation of application binaries by creating a handful of custom, simple Ant tasks.
Getting started
The base Ant package ships with a broad variety of basic tasks including one that allows SQL statements to be executed against Java Database Connectivity (JDBC) data sources. While this task is perfectly capable of working against the Derby database engine, there are a number of unique characteristics about Derby that need to be dealt with that the SQL task is simply not capable of handling.
Derby is an embedded database. Only one instance of Derby is allowed to be loaded per instance of the Java Virtual Machine (JVM). Only a single Derby instance is allowed to access any given database at a time. When the Derby instance connects to a database, a lock is created that helps to enforce these rules. The only way to remove that lock is to explicitly tear down Derby’s connection to the database. Simply closing the JDBC connection does not cut it. Shutting down the database in Derby is also important to ensure that data recovery checkpoints and various other database management functions are handled properly. The While the SQL Ant Task is fully capable of launching the Derby embedded database and executing SQL statements against it, it is not capable of handling the appropriate Derby shutdown tasks that are required to use Derby properly.
The Ant tasks introduced here give a simple answer by providing the means to explicitly control the load status of Derby and of individual database connections. To use these tasks, you need to get Ant version 1.6.2 from the Apache Ant Web site (http://ant.apache.org) and the most recent binary version of the Derby database engine (http://incubator.apache.org/derby). These tasks will also work with the release version Cloudscape 10.0 available from IBM (http://www.ibm.com/developerworks/db2/zones/cloudscape/).
The build process
The first critical step to integrating the creation of the Derby Ant tasks was to identify the basic ordering of the tasks involved. For Derby these steps are:
- Starting the embedded Derby database engine
- Opening a connection to the Derby Database you wish to work with, specifying whether or not to create the database as is appropriate (Derby can create a new database on demand simply by opening a connection to it as long as a special create=true parameter is specified in the database connection string.)
- Executing a series of statements that either create/modify/populate tables, create/modify views, etc.
- Close and shutdown the database connection.
- Shutdown the embedded database engine.
Out of this list, there are three distinct Ant tasks designed to complement the built in SQL task.
- StartDerbyTask.java : Starts the embedded database engine by using the ClassLoader to load it’s JDBC Driver into memory.
- StopDatabaseTask.java : Completely and properly shuts down Derby’s connection to the database.
- StopDerbyTask.java : Shuts down the embedded database engine. No further calls to Derby will be allowed.
There is also a fourth task that provides access to a very important utility that ship’s with the Derby engine:
- DBLookTask.java: provides access to the dblook command-line utility which allows DDL to be exported for a given database. This task must not be nested within a DatabaseConnectionTask and opens its own connection to the database.
In the download that accompanies this article, there is an example Ant build script that uses each of these tasks. Listing 1 shows that example script.
Listing 1. Example Derby Ant Build Script
<project name="demo" default="demo">
<!-- Define the Derby Ant Tasks -->
<taskdef
name="startDerby"
classname="com.ibm.developerworks.derby.ant.StartDerbyTask" />
<taskdef
name="stopDerby"
classname="com.ibm.developerworks.derby.ant.StopDerbyTask" />
<taskdef
name="stopDatabase"
classname="com.ibm.developerworks.derby.ant.StopDatabaseTask" />
<taskdef
name="ddlExport"
classname="com.ibm.developerworks.derby.ant.DBLookTask" />
<target name="demo">
<property name="DBURL" value="jdbc:derby:my/test;create=true" />
<property name="DRIVER" value="org.apache.derby.jdbc.EmbeddedDriver" />
<property name="USERID" value="" />
<property name="PASSWORD" value="" />
<property name="TEST" value="testing" />
<!-- Start the Derby database engine explicitly -->
<startDerby />
<!-- This is the default Ant sql task -->
<sql driver="${DRIVER}"
url="${DBURL}"
userid="${USERID}"
password="${PASSWORD}"
autocommit="yes"
onerror="continue"
caching="yes">
DROP TABLE TEST;
CREATE TABLE TEST (pkey int not null generated always as
identity (start with 1, increment by 1), firstfield char(10),
secondfield int);
INSERT INTO TEST (firstfield, secondfield) VALUES ('${TEST}', 10);
SELECT * FROM TEST;
</sql>
<!-- The SQL Command will leave the Derby database is a started state.
To protect the integrity of the database, you should explicitly
stop the database -->
<stopDatabase url="${DBURL}" />
<!-- Use the dblook tool to generate DDL for the tables -->
<ddlExport url="${DBURL}" dest="tables.sql" verbose="yes" />
<!-- Package the DB up for read-only distribution -->
<jar destfile="db.jar" >
<fileset dir=".">
<include name="my/**/*" />
</fileset>
</jar>
<!-- Shutdown Derby properly. This should be done every time,
but only when you're absolutely done using Derby -->
<stopDerby />
</target>
</project>
|
 |
StartDerbyTask.java
The start derby task is responsible for loading the embedded database engine into memory and should come before all other Derby tasks in the Ant script. The SQL task is capable of loading the engine on demand when it loads up the JDBC Driver for Derby, but it is always a good idea to follow a more declarative approach that gives you complete control over exactly when to launch the database. Listing 2 shows the code for the task.
Listing 2. StartDerbyTask.java
1 package com.ibm.developerworks.derby.ant;
2
3 import org.apache.tools.ant.BuildException;
4 import org.apache.tools.ant.Task;
5
6 public class StartDerbyTask
7 extends Task {
8
9 public void execute()
10 throws BuildException {
11 try {
12 Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
13 System.out.println("Derby has been started!");
14 } catch (Exception e) {
15 System.out.println(
16 "Derby could not start. This is most likely " +
17 "due to missing Derby JAR files. Please check your classpath" +
18 "and try again.");
19 throw new BuildException(e);
20 }
21 }
22 }
|
Line 12 is the critical piece that loads both the embedded engine and its JDBC driver into memory. This is the only way to load Derby and must only be done once per Java Virtual Machine (a fact that is only really a concern when using multiple classloaders within a single JVM instance).
For now, the start derby task has no unique parameters; however a more robust version may allow various Derby configuration properties to be set.
The SQL Ant task
Once Derby has been started, you can use the built in SQL Ant task to perform a variety of operations against the engine. The online Ant documentation provides (http://ant.apache.org/manual/CoreTasks/sql.html) a significant amount of detail on the use of this task so not much time will be spent on it here other than to point out a few details specific to working with Derby.
Listing 3. The SQL Ant Task
<sql driver="${DRIVER}"
url="${DBURL}"
userid="${USERID}"
password="${PASSWORD}"
autocommit="yes"
onerror="continue">
DROP TABLE TEST;
CREATE TABLE TEST (pkey int not null generated always as
identity (start with 1, increment by 1), firstfield char(10),
secondfield int);
INSERT INTO TEST (firstfield, secondfield) VALUES ('${TEST}', 10);
SELECT * FROM TEST;
</sql>
|
First, the driver attribute MUST equal "org.apache.derby.jdbc.EmbeddedDriver". Second, the url attribute MUST be in the basic form "jdbc:derby:databaseName" where databaseName is the name of the Derby database you are working with. Derby supports a collection of extended connection URL options such as the ability to indicate whether or not the database should be created automatically when the JDBC driver attempts to connect to it; whether or not to encrypt the database and various options relating to that encryption process; and so on. You will need to refer to the Derby documentation for full details about the connection string. For your simple example build process use the connection URL value "jdbc:derby:my/test;create=true" which will result in the creation of a new database in a directory called my/test located relative to the current path (e.g. if you are running your ant script from the C:\ root in Windows®, the database will be created a C:\my\test.
StopDatabaseTask.java
When the SQL task executes and Derby is asked to load a connection to the my/test database, a lock file will be created in the database directory that will keep other instances of Derby from being able to load the database. When the SQL task completes its work, the JDBC Connection to the Derby database will be closed but the lock file will not be removed because the Embedded Derby instance will keep an internal connection with the database around in case someone wants to open another connection and do more work with it. When you know you are completely finished working with the database or when you’re preparing the database for distribution as part of a build image, it is very important that you shut down Derby’s internal connection with the database properly. The StopDatabaseTask is designed for exactly that purpose.
Listing 4. StopDatabaseTask.java
1 package com.ibm.developerworks.derby.ant;
2
3 import java.sql.DriverManager;
4 import java.util.ArrayList;
5 import org.apache.tools.ant.BuildException;
6 import org.apache.tools.ant.Task;
7
8 public class StopDatabaseTask
9 extends Task {
10
11 private ArrayList subTasks;
12
13 private String url;
14
15 public String getUrl() {
16 return url;
17 }
18
19 public void setUrl(String url) {
20 this.url = url;
21 }
22
23 public void execute()
24 throws BuildException {
25 try {
26 DriverManager.getConnection(url + ";shutdown=true");
27 } catch (Exception e){
28 System.out.println("Database has been shutdown");
29 }
30 }
31
32 }
|
Line 26 is the important piece of this code. By appending "shutdown=true" to the connection URL of the database and using DriverManager.getConnection, instruct Derby to cleanly shutdown its internal connection with the database. Calling this method in this way always results in an Exception being thrown that indicates that the database was shutdown. The task catches that exception and reports the state back properly and cleanly to the user.
StopDerbyTask.java
When your Ant script is completely finished using Derby, it is a very good idea to shutdown the embedded database engine. For this, the StopDerbyTask has been provided.
Listing 5. StopDerbyTask.java
1 package com.ibm.developerworks.derby.ant;
2
3 import java.sql.DriverManager;
4 import org.apache.tools.ant.BuildException;
5 import org.apache.tools.ant.Task;
6
7 public class StopDerbyTask
8 extends Task {
9
10 public void execute()
11 throws BuildException {
12 try {
13 DriverManager.getConnection("jdbc:derby:;shutdown=true");
14 } catch (Exception e) {
15 System.out.println("Derby has been shutdown!");
16 }
17 }
18 }
|
As in StopDatabaseTask, shutdown Derby by passing in a special connection URL string to the JDBC DriverManager’s getConnection operation. To shut down Derby, however, this connection URL must be devoid of any reference to a database name. Calling this method will always result in an exception being thrown that indicates that Derby was successfully shutdown. Once this has been done, there is no way to reload the Derby engine within the same JVM instance so be careful how you use this.
DDL generation
DBlook is an important tool that ships with the Derby database engine that is used to generate Data Definition Language (DDL) scripts against a database schema. Typically this utility is accessed as a command-line tool. Below, Snell put together a simple task to make it available to your Ant scripts.
Listing 6. DBLookTask.java
1 package com.ibm.developerworks.derby.ant;
2
3 import java.util.ArrayList;
4 import java.util.StringTokenizer;
5 import java.util.Vector;
6 import org.apache.derby.tools.dblook;
7 import org.apache.tools.ant.BuildException;
8 import org.apache.tools.ant.Task;
9
10 public class DBLookTask
11 extends Task {
12
13 private String url; // -d
14 private String dest; // -o
15 private String schema; // -z
16 private Vector tables; // -t
17 private boolean append; // -append
18 private boolean verbose; // -verbose
19 private boolean noview; // -noview
20
21 public boolean isAppend() {
22 return append;
23 }
24 public void setAppend(boolean append) {
25 this.append = append;
26 }
27 public String getDest() {
28 return dest;
29 }
30 public void setDest(String dest) {
31 this.dest = dest;
32 }
33 public boolean isNoview() {
34 return noview;
35 }
36 public void setNoview(boolean noview) {
37 this.noview = noview;
38 }
39 public String getSchema() {
40 return schema;
41 }
42 public void setSchema(String schema) {
43 this.schema = schema;
44 }
45
46 public void setTables(String tables) {
47 if (this.tables == null)
48 this.tables = new Vector();
49 StringTokenizer st = new StringTokenizer(tables, " ");
50 while (st.hasMoreTokens()) {
51 this.tables.add(st.nextToken());
52 }
53 }
54
55 public String getTables() {
56 StringBuffer buf = new StringBuffer();
57 if (tables != null) {
58 for (int n = 0; n < tables.size(); n++) {
59 buf.append(tables.get(n) + " ");
60 }
61 }
62 return buf.toString();
63 }
64
65 public String getUrl() {
66 return url;
67 }
68 public void setUrl(String url) {
69 this.url = url;
70 }
71 public boolean isVerbose() {
72 return verbose;
73 }
74 public void setVerbose(boolean verbose) {
75 this.verbose = verbose;
76 }
77
78 public void execute()
79 throws BuildException {
80 ArrayList args = new ArrayList();
81 args.add("-d");
82 args.add(url);
83
84 if (dest != null) {
85 args.add("-o");
86 args.add(dest);
87 }
88
89 if (schema != null) {
90 args.add("-z");
91 args.add(schema);
92 }
93
94 if (tables != null) {
95 args.add("-t");
96 for (int n = 0; n < tables.size(); n++) {
97 args.add(tables.get(n));
98 }
99 }
100
101 if (append)
102 args.add("-append");
103
104 if (verbose)
105 args.add("-verbose");
106
107 if (noview)
108 args.add("-noview");
109
110 String[] argsarray = new String[args.size()];
111 argsarray = (String[])args.toArray(argsarray);
112 dblook.main(argsarray);
113 }
114 }
|
This task does absolutely nothing more than output the DDL for a database to a file. The task parameters specify the destination file, the names of the tables to include, whether or not to include Views in the DDL, whether or not to use Verbose mode when exporting, and the name of the specific Schema to export.
In the example build script, <dblook url="${DBURL}" dest="tables.sql" verbose="yes" /> is used to specify that DDL for the entire database identified by the Ant property ${DBURL} should be exported to a file called tables.sql. The export will include all schemas, all tables, all views, etc.
Packaging and distribution
Once your database has been created, and after the database has been shut down properly using the StopDatabaseTask, there are a number of different ways you can distribute it. If the client must have full read/write access to the database, the file structure must be distributed in tact and uncompressed on the client (you can zip it up for transport, but it needs to be unzipped in order for the client to use it). However, if the database must be accessed using read-only permissions, the file structure may be compressed and distributed to the client as a JAR file. This can be done in your Ant script using the standard Ant JAR task.
Listing 7. Using the JAR task to package a Derby database
<jar destfile="db.jar" >
<fileset dir=".">
<include name="my/**/*" />
</fileset>
</jar>
|
On the client side, a read-only connection to the database contained in the JAR may be obtained using the JDBC connection URL "jdbc:derby:jar:(db.jar)my/test".
Wrapping up
The Ant tasks presented here are rather simplistic and straightforward. As such, they are also somewhat incomplete. There are many more features of Derby that could be exploited, such as the creation of encrypted databases, support for various configuration files, etc that were not touched on. It is up to you to take and improve upon the tasks as you see fit in order to address your own development needs.
Download | Description | Name | Size | Download method |
|---|
| Source files for the Derby Ant Tasks | derbyant.zip | 10 KB | FTP |HTTP |
|---|
Resources
- The Derby Database engine is an open source embedded relational database engine that was contributed to The Apache Software Foundation by IBM. The latest version can be downloaded from http://incubator.apache.org/derby
- Apache Ant is a powerful XML-based tool for creating and managing build scripts. The latest version can be downloaded from http://ant.apache.org
- See my developerWorks weblog for more discussion about Emerging Technology topics including (but not limited to) Web services.
About the author | 
| | James Snell is a member of IBM's Emerging Technologies Toolkit and has spent the past few years focusing on emerging Web services technologies and standards. He maintains a weblog focused on emerging technologies at http://www.ibm.com/developerworks/blogs/dw_blog.jspa?blog=351. |
Rate this page
| 
