I imagine the first question people will ask is --- Why would you want to use Apache Derby in JBuilder, when there is a perfectly good pure Java^TM database, JDataStore, that comes pre-installed and configured for development in every edition of JBuilder, from Enterprise right down to Foundation edition?

As most of you will have discovered by now, JDataStore is a very nice, easy to use, feature complete SQL database, which also happens to be pure Java^TM. But... it is NOT freely distributable. Since IBM has released the database formerly known as Cloudscape to the Apache Foundation, the newly renamed Apache Derby IS freely distributable under the Apache license.

For the purposes of this article, we will be using JBuilder 2006 on Windows Server 2003, and JBuilder 2005 on Mac OS X Tiger. To avoid information overload, we will also limit the usage of Derby to the “embedded” database version. Should this topic prove to be popular, a followup article on the Network Server version can be arranged. We will also assume that you are familiar with JBuilder, projects, libraries, and creating new classes and running them within JBuilder. If you are not familiar with these concepts, I suggest that you go and review the basic tutorials included as a part of JBuilder's Help and Samples before continuing.

Since the point of this article is how to use Derby with JBuilder, I will only give you the outline of installing Derby. A much more detailed discussion of this topic can be found in the Derby Getting Started manual.

• First, you'll need to go and download Derby. You can find at on the Apache website, where it has graduated from incubator status into a full-fledged project.
  
  http://db.apache.org/derby/derby_downloads.html
  
  Unless you have identified a particular bug to work around, or you really enjoy building things from scratch, I would always recommend starting with the binary distribution of the most recent official release. At the time of writing, that was 10.1.1.0 . The bin distribution is approximately 10.1 MB in size.


• Unzip the distribution into your directory of choice. From this point forward, we will refer to that directory as DERBY_INSTALL . You should set an environment variable called DERBY_INSTALL to point at your db-derby-10.1.1.0-bin directory.
  
• Derby is certified to work with most current JVM's, so I would not worry about installing any particular version just for this. I will be using the most current JDK 1.4.2 on boths Windows and Mac, for the sake of consistency. In order to confirm that your derby installation is working, you will need to make sure that you have a “java” command in your system path. In a command shell, type java -version to confirm that you are using the JVM that you expect.
  
• Now, open up a command shell; go to $DERBY_INSTALL/frameworks/embedded/bin ; and run the sysinfo script (.bat for Windows, and .ksh for Unix flavours) that you will find there. If it runs and tells you which version of Derby it found, then you have successfully installed Derby.
  
  On Windows Server 2003, it should look something like this:
  
  Hide image
  
  
  
  
  
  On Mac OS X Tiger it should look something like this:
  
  Hide image
  
  

Mac OS X Derby Install Issues:

1. Can't execute the script files
   
   Solution: use chmod +x on the script files


2. Bad line endings on the .ksh script files (^M).
   
   Solution: use darwinport's dos2unix to convert line endings
   or something like:
   tr -d '\r' <oldfile >newfile

JBuilder comes with a number of pre-configured JDBC drivers, as well as having built-in support for a number of commercial databases, like Oracle and DB2. But when it comes to adding support for new databases, this multistep process is a frequent cause of frustration. Just remember that you have to complete ALL the steps in order to make your new database fully functional from inside JBuilder.

    Step 1 – Making Derby accessible to your JBuilder projects.

    Step 2 – Making JBuilder aware of Derby

    Step 3 – Configuring Derby for use within Database Pilot

While you could just move on to using Derby in your own projects now, if might help to take it for a test drive first. JBuilder's HelloJDBC sample was created as a demonstration of the embedded version of JDataStore. Which makes it a perfect candidate to adapt for use with embedded version of Derby.

We have already seen one way of creating a new Derby database using Database Pilot. Now we will create one in code.

1. Open the HelloJDBC sample project, and add the Derby library as a required library
   
2. In the HelloJDBC project, you will find a class called "CreateDatastore". This class contains JDataStore specific code for creating a new database from scratch. As far as I know, there is no common JDBC way to create a new database, so we will need to use some Derby specific code. Start by creating a new class – CreateDerbyDatabase, like this:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;

public class CreateDerbyDatabase
{
    public static final String DERBY_EMBEDDED_DRIVER = "org.apache.derby.jdbc.EmbeddedDriver";
    public static final String DERBY_URL = "jdbc:derby:";
    public static void main(String[] args)
    {
        String derbyFile = "/Users/lori/Databases/TEMPORARY";
        String derbyOptions = ";create=true";
        String user = "sysdba";
        String passwd = "masterkey";

        try
        {
            Class.forName(DERBY_EMBEDDED_DRIVER);
        }
        catch (ClassNotFoundException ex)
        {
            Logger.global.log(Level.SEVERE, "Derby JDBC driver not found in the classpath");
            System.exit(1);
        }

        Connection con = null;
        try
        {
            con = DriverManager.getConnection(DERBY_URL + derbyFile + derbyOptions, user, passwd);
            Logger.global.log(Level.INFO, "Derby database successfully created");
        }
        catch (SQLException ex)
        {
            Logger.global.log(Level.WARNING, "Derby database could not be created" + ex);
        }
        finally
        {
            if ( con != null ) {
                try
                {
                    con.close();
                }
                catch (SQLException ex)
                {
                    // ignore this
                }
            }
        }
    }
}

3. Compile and run the CreateDerbyDatabase class. If you have completed everything successfully, you should see a message like this:

Sep 9, 2005 6:06:20 PM CreateDerbyDatabase main
INFO: Derby database successfully created

That was easy. Now, let's modify one of the existing classes, HelloJDBC, to use Derby. Since we have already created the database that we will be using, we will not be using the URLAttributes for this URL.

1. Replace the JDBC driver

//  String DRIVER = "com.borland.datastore.jdbc.DataStoreDriver";
    String DRIVER = "org.apache.derby.jdbc.EmbeddedDriver";

2. Replace the URL

//  String URL    = "jdbc:borland:dslocal:";
    String URL    = "jdbc:derby:";

3. Replace the file

//  String FILE   = "TEMPORARY.jds";
    String FILE   = "/Users/lori/Databases/TEMPORARY";

4. Well, that was it. Compile and run HelloJDBC. You should see:

COLOR,NUMBER,PRICE,
Red,1,7.99,
Blue,2,8.99,
Green,3,9.99,

Which is exactly what you would see if you ran the project with it's original JDataStore settings. So you can see that it is very easy to swtich from one JDBC technology to another, provided you are dealing with a database which has already been created. Ideally, the changes made to the code above could be refactored into a properties file, and then you would not even have to recompile in order to switch from one database to another.

Now that you see how easy it is, you should be ready to use Derby in one of your own JBuilder projects.

While I can make no special claim to being an Apache Derby expert, I've looked at Derby (aka Cloudscape), off and on for years, since it first appeared as part of the Sun J2EE reference implementation. It is nice to see that it has evolved into a viable open source project and I hope to see its' feature set continue to be expanded in the future. Meanwhile, we have an open source pure Java^TM database to play with in JBuilder. Enjoy!