Getting Started using Apache Derby in JBuilder - by Lori Olson

By: Lori Olson

Abstract: Using the Apache Derby database involves a lot of setup and configuration. In this article, you will be guided, step-by-step, through the process of installing, configuring, and finally using Derby with one of the JBuilder JDBC sample applications.

Table of Contents

Introduction

Installing Derby

Configuring Derby for use in 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

Adapting the HelloJDBC sample for use with Derby

Closing Thoughts

    Introduction

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

    Installing Derby

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.

    https://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

    Configuring Derby for use in JBuilder

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.

  • Invoke the “Configure Libraries” dialog from the JBuilder menus, by selecting Tools | Configure | Libraries.

    Hide image
    Click to see full-sized image

  • Select “New...” to invoke the New Library Wizard
  • Type in a name for your Derby library
  • Select a location for the library to be stored
  • Add $DERBY_INSTALL\lib\derby.jar to the “Library paths”

    Non-English users will probably want to add a language-appropriate locale jar (located in the $DERBY_INSTALL\lib directory as well) to the Library paths as well.



    Hide image


  • Select “Ok” in the New Library Wizard, and “Ok” in the Configure Libraries dialog.

    Note!
    Now that you have a library for your Derby driver... you have to actually USE it. Make sure that this library is specified as a “Required Library” in your projects which will be using Derby.

    Step 2 – Making JBuilder aware of Derby

Just because you have created a library to use in your Derby projects, doesn't mean that JBuilder can use it. Now you have to get it into JBuilder's classpath, so that JBuilder and other tools (Database Pilot, etc) can use it.

  • For Developer and Enterprise edition users
    • Invoke the Enterprise Setup dialog by selecting Enterprise | Enterprise Setup from the JBuilder menus.
    • Select “Database Drivers” from the left pane.

      Hide image


    • Choose “Add...” to invoke the “Select Libraries” dialog

      Hide image


    • Select your newly created Derby library from the list of libraries, and choose “Ok” in the Select Libraries dialog, and choose “Ok” in the Enterprise Setup dialog.
    • Restart JBuilder when told to do so.
  • For Foundation edition users. The “Enterprise Setup” menu is not accessible to Foundation users, so you must create the necessary configuration file by hand. These are the steps.
    • Open up a new file in JBuilder (through the menu - File | Open File). This file should be called derby.config , and it should reside in the <JBuilder>/lib/ext directory
    • Make sure that you know exactly where the $DERBY_INSTALL directory is, and insert the “ addpath ” directive for the derby.jar file to your derby.config file. For example:
      addpath /Users/lori/Applications/db-derby-10.1.1.0-bin/lib/derby.jar
      or
      addpath C:/apps/db-derby-10.1.1.0-bin/lib/derby.jar

      Non-English users should include an additional addpath directive in this file for the language specific locale jar of their choice


    • Save and close the derby.config file.
    • Restart JBuilder

At this point, JBuilder itself will be aware of Derby, and will be able to use Derby. For example, if you use data-aware controls, like DBSwing, they will be able to function properly with the JBuilder design view.

    Step 3 – Configuring Derby for use within Database Pilot

Some people would stop at the previous step, but I happen to think that Database Pilot is an essential development tool for database work. Behind it's deceptively simple UI is a surprisingly powerful tool. So the third and last step will be to ensure that Database Pilot is properly configured to use Derby.
  • Start up Database Pilot. You can use the shortcut that was installed to run it directly, or you can invoke it from within JBuilder, from the Tools | Database Pilot menu.

    Hide image


  • Choose View | Options from the Database Pilot menu to invoke the Options dialog, and select the Drivers tab.

    Hide image


  • Select the Add... button, and enter the Derby JDBC driver “ org.apache.derby.jdbc.EmbeddedDriver ” and press OK
  • With the new driver selected, enter a Sample URL - “ derby: databaseName ; URLAttributes ”, and press OK to dismiss the Options dialog.

    Note : do not include the usual “jdbc:” at the front of the URL, as it will be added automatically by Database Pilot.



    Hide image


  • Now, we can test the driver by creating a new database connection in Database Pilot. Choose File | New to invoke the New URL dialog.
  • Select your new driver: org.apache.derby.jdbc.EmbeddedDriver and customize the URL entry. In this case, we will enter the filename of the database, and then make use of one of the URLAttributes, and specify “ create=true ”, so that a new, unpopulated database is created for us. Press OK to finish creating the new database connection.

    For example:
    jdbc:derby:/Users/lori/Databases/test;create=true
    or
    jdbc:derby:c:/databases/test;create=true

    Hide image

  • Now, try to open your new database connection. When prompted, you can enter a username and password, or you can leave them blank. After a brief pause as the database is created, you should see something like this:

    Hide image

Congratulations! You have now completed all the steps necessary to make full use of Apache Derby in JBuilder.

Note: There is a documented Derby bug on Mac OS X for creating databases as described above (create=true). I did not experience this problem, but YMMV.

    Adapting the HelloJDBC sample for use with Derby

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:
  3. 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
                    }
                }
            }
        }
    }
    
  4. Compile and run the CreateDerbyDatabase class. If you have completed everything successfully, you should see a message like this:
  5. 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
  2. //  String DRIVER = "com.borland.datastore.jdbc.DataStoreDriver";
        String DRIVER = "org.apache.derby.jdbc.EmbeddedDriver";
  3. Replace the URL
  4. //  String URL    = "jdbc:borland:dslocal:";
        String URL    = "jdbc:derby:";
  5. Replace the file
  6. //  String FILE   = "TEMPORARY.jds";
        String FILE   = "/Users/lori/Databases/TEMPORARY";
  7. Well, that was it. Compile and run HelloJDBC. You should see:
  8. 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.

    Closing Thoughts

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 JavaTM database to play with in JBuilder. Enjoy!


Server Response from: ETNASC01