Using the MyEclipse Derby Database Server

Introduction

I was recently working on an example web application that uses both Struts 2 and Spring. One of the Spring features I like best is Spring JDBC. To show off what Spring JDBC can do, my example application needed to interact with a database. However, since this was just an example application that I wanted to share with other Java developers I wanted the application to run completely within our primary IDE (MyEclipse), even when not connected to the internet. So the database server needed to reside on the developer's computer.

Previously for Java database application development, I'd used either MySql or SQL Server 2005 Express. But using those database servers would require the other developers to either already have them installed or install them. Also, I'd still have the challenge of getting them the actual database to use with the example application.

I'd heard about the Derby database server and knew that it came with MyEclipse. Since the other developers I wanted to share the application with also have MyEclipse (and therefore the Derby database server) I figured using Derby would be an easy way to provide them the example application and the database. The other developers could then run the application complete from within MyEclipse and not need to be connected to the internet or install another database server.

Using The Derby Database Server In MyEclipse

My first step then was to create the database needed by my Java Struts 2/Spring example application. But I quickly realized that creating a new database wasn't going to be easy. I read the MyEclipse Derby Database Server Tutorial. But the tutorial was maddeningly vague on how to actually create a new Derby database. I didn't want to use the default Derby database that came with MyEclipse because copying that database to another developer's computer might overwrite some data he/she had put in their own MyEclipse Derby database. I wanted to create a completely new and separate database that I could give to other developers.

After much trial and error and searching the web, I finally figured out how to create a new Derby database in MyEclipse. Here are the steps I followed:

Do the steps in the MyEclipse Derby Database Server Tutorial under the heading Connecting to Derby with the Database Explorer. You should see something like the following:

To create a new Derby database do the following:

1. Download the Derby library of jar files, one of which is the DerbyClient.jar and has the JDBC driver classes, from http://db.apache.org/derby/derby_downloads.html
   1. Note you just need the lib distribution
2. Unzip the download into a folder on your computer
3. Back in MyEclipse in the MyEclipse Database Explorer right click on MyEclipse Derby and choose New
4. In Driver Template - select Derby
5. In Driver Name - type in the name you want for this database connection (eg "Phillips Derby")
6. In Connection URL you need: jdbc:derby://localhost:1527/phillipsDB;create=true (only you can replace phillipsDB with your own database name)
   1. The create=true will force MyEclipse's Derby server to create the database if it doesn't already exist
7. For User name type in the name of the database schema that you want to connect to by default. So if the database schema that you want to connect to by default is named contacts, type contacts for User name. This part of the process caused me no end of trouble shooting until I figured it out.
8. For password you can use whatever you want. I used "password"
9. Click on the Add Jars button and navigate to where you unzipped the Derby library of jar files and find the one named DerbyClient.jar.
10. When you're done you should have something close to the below:

Just click on the Finish button and you should then have a new database connection in your list in the MyEclipse Database Explorer.

Before opening the new connection and creating the database, you need to start up MyEclipse's Derby database server Click on the down arrow for the server icon and select MyEclipse Derby start. In the console window you should see something like the following message:

Apache Derby Network Server - 10.2.2.0 - (485682) started and ready to accept connections on port 1527 at 2009-03-01 03:34:33.515 GMT

After starting the Derby database server, you can right click on your new database connection and select Open Connection. Click on the plus sign next to your connection and the plus sign next to the Connected to... and you should see a list of schemas like this:

Notice that our contacts schema wasn't created. The only way I found to create a new schema in the database was to use a create table command and specify the schema I want to create. So right click on the Connected to ... line and select New SQL Editor. In the editor type a table creation statement with a schema specified. This schema should be the default one you want to use for your Java application. For example:

create table contacts.temptbl (id int);

Click the green arrow to execute the statement. Then right click on the Connected to ... line and select Reconnect and Refresh and you should see the new schema. Click on the plus sign next to the new schema and the plus sign next to TABLE and you'll see the temptbl you created. You can right click on the temptbl and choose Drop Table as you won't need that table in this schema.

If you right click on the TABLE line under your new schema you'll see the New Table option which will launch a wizard to assist you in creating a table and its columns. You can also put create table statements into the SQL Editor (as we did above). If you copy the below SQL code into the SQL Editor and execute the statements (click the green arrow button), you should create three new tables in the CONTACTS schema.

When the above statements are done being executed you should have three new tables under the CONTACTS schema. For example:

A Java Application That Uses The Derby Database

Now you can connect to your new database (eg phillipsDB) and schema (eg CONTACTS) by using the normal JDBC classes and methods. You'll need to use the same DerbyClient.jar in your application for the Derby driver classes. You can download a MyEclipse archived Java project that you can use to connect to the Derby database and schema created above.

There are just a couple of key points you should remember when creating the connection in Java. For example in this excerpt from the DerbyTest application:

Note the connectionURL value of jdbc:derby://localhost:1527/PHILLIPSDB;user=CONTACTS. PHILLIPSDB is the name of the new database we created and CONTACTS is the default schema we want to use in that database. You need to specify the user=CONTACTS in the connectionURL string so that the schema you want to use by default (in thise case CONTACTS) is identified. This was another area not very well explained in the MyEclipse Derby documentation. Also notice because we are not using any security, we don't need to specify the password value.

Provide The Derby Database To Others

Lastly, I had to figure out how to provide the Derby database to other developers. MyEclipse creates the Derby databases by default in <user-home>\.myeclipse\derby folder (note that is dot myeclipse [.myelipse]). On my Windows XP computer my <user-home> is C:\Documents and Settings\brucephillips. In the derby folder is a folder for the new database I created above, phillipsDB. I just needed to zip up this folder and distribute the zipped file along with my example project.

Other developers with MyEclipse's Derby database server can unzip the file into their <user-home>\.myeclipse\derby folder and when they start up the Derby database server the phillipsDB database will be available for use with the Java application. Note for the other developer to manipulate the phillipsDB database in MyEclipse's Database Explorer he/she will have to create a connection to the phillipsDB Derby database in Database Explorer using the same process described above for creating a new database (only without the create=true in the connection URL).

Stopping the MyEclipse Derby Database Server

Be sure to stop the MyEclipse Derby database server by selecting MyEclipse Derby under the Servers view and clicking on the red stop button. You'll see a shutdown message like the following in the console

Apache Derby Network Server - 10.2.2.0 - (485682) shutdown at 2009-03-01 03:32:58.078 GMT

Summary

After finally figuring out how to create a new Derby database and connect to its default schema in my Java application, I can see that Derby will be useful in developing prototype applications that I want to share with other developers who have MyEclipse (or Eclipse) and the Derby database server (there is a Derby plugin for Eclipse, see http://db.apache.org/derby/integrate/index.html#eclipse). It seems to be an easy way to share applications and databases in a development environment.

My next step is to learn how to embed the Derby database directly into my Java application (done see below). Per the Derby getting started manual:

Embedded Refers to Derby being started by a simple single-user Java application. With this option Derby runs in the same Java virtual machine (JVM) as the application. Derby can be almost invisible to the end user because it is started and stopped by the application and often requires no administration. The Derby documentation often refers to this as the embedded configuration or embedded mode.

References

1. MyEclipse Derby Database Server Tutorial
2. Apache Derby, http://db.apache.org/derby/index.html
3. Apache Derby Documentation, http://db.apache.org/derby/manuals/index.html (note I think the version of Derby that comes with MyEclipse 7 is Derby 10.2.)
4. Test Derby MyEclipse Archived Java Application
5. phillipsDB Derby Database (unzip to your Derby home folder)