In this article, you'll install and configure your database -- the back end of your Web application -- create a database schema, and learn about some simple tools for populating it with data. The database you'll be using is Apache Derby, a 100% pure Java™ relational database that was originally developed under the name Cloudscape ™. Eventually, the Cloudscape code was acquired by IBM®, which donated an open source version of it to the Apache project. The same project is also distributed by Sun Microsystems under the name JavaDB, which is not at all confusing.

I chose Derby not just because I like the fact that it has three names, but because it's lightweight and easy to configure. Unlike most relational databases, Derby can run within the same Java Virtual Machine (JVM) as your Java-side server code. (You can also run it in a separate JVM if you like.) That makes development and deployment easier, and Derby is fast enough to be a reasonable choice for a small- to mid-sized Web application.

Before you get started, a few notes: First, to follow along with this article, you should have a basic knowledge of relational databases, JDBC, and Structured Query Language (SQL). Second, this article presents a few things in the code for demonstration purposes that are not likely to be ideal in a production system. I try to point those elements out along the way, but I won't talk about performance tuning here.

Get Derby

Derby is available as part of the Apache DB project. As of this writing, the current release is version 10.1.3.1. If you're going to be working in the Eclipse integrated development environment (IDE), it's enough to grab the two plug-ins derby_core_plugin and derby_ui_plugin. If you aren't, you can grab whichever other distribution meets your needs. One distribution is only the library files, another is the library and documentation, one distribution is the library with debug information, and one distribution is just source code. Derby is based exclusively on Java technology and will run on any JVM version 1.3 or later. The code examples here assume that you're using Java 1.4.

Set up Derby without Eclipse

If you're not using Eclipse, extract the distribution you downloaded to anyplace convenient. When that's done, ensure that the files lib/derby.jar and lib/derbytools.jar are in your classpath variable. You can do this at the system level, in which case it might be helpful to set an environment variable DERBY_INSTALL to the directory in which Derby resides (include the Derby directory itself, as in /opt/bin/db-derby-10.1.3.1-bin). You can also do this within your IDE or launcher script. If you want to use Derby in a client/server mode as well as in an embedded mode, the files lib/derbyclient.jar and lib/derbynet.jar must also be in your classpath.

Set up Derby with Eclipse

If you're using Eclipse, setting up for development is a bit easier. To set up Derby in Eclipse, complete these steps:

1. Extract the two plug-in files. Each has a top directory named plugin.
2. Copy the contents of that directory into your Eclipse plug-in directory.
3. Open your project in Eclipse.
4. Click Project > Add Apache Derby Nature to open the world of Derby goodness. Doing so adds the four library files to your project classpath and gives you access to the ij command-line prompt.

Figure 1 shows the Derby menu after you've added the Derby Nature.

Even if you use Eclipse for development, you must have the appropriate JAR files available when you deploy your application. I'll cover this in more detail in a later article.

Design your schema

Before you start using your database, take a minute to figure out what the database should hold. I haven't discussed requirements for the Slicr application yet, so let's assume that you want the database to be able to hold basic customer and order information.

The trick to dealing with a database at this early stage in the product is to keep it simple and use as few database system-specific features as possible, even if that means initially doing additional processing in your Java code. A database is a large third-party dependency, and you must protect yourself from having your database decision drive the rest of your application. You want to minimize the points of contact between your program and the database so that if you change systems at some point, the change is actually feasible. The tension is that most things that you'll do to improve database performance tend to tie you to using a specific system, so try to put off that kind of optimization until the last possible moment in the project.

The start of your database design is straightforward. Customers place orders. Orders consist of one or more pizzas (for the moment, ignore that the restaurant may sell other food). A pizza consists of zero or more toppings, which might be on half of the pizza or on all of the pizza.

Create the Customer table

Right now, you only care about enough customer information to be able to deliver and confirm the orders, as Listing 1 shows.

                CREATE TABLE customers (
    id int generated always as identity constraint cust_pk primary key,
    first_name varchar(255),
    last_name varchar(255),
    phone varchar(15),
    address_1 varchar(200),
    address_2 varchar(200),
    city varchar(100),
    state varchar(2),
    zip varchar(10)
)

The CREATE statement has one slightly nonstandard bit of SQL syntax. You create an ID column that you want Derby to auto-increment for each new row. The clause to specify that behavior is:

id int generated always as identity

The other option for an Identity column would be:

generate by default as identity

The difference is that generate by default allows you to place your own value in the column, whereas generate always does not. You've also identified the ID column as the table's primary key.

You always want to have an ID in the database that's completely without connection to a real-world value. Someone on your team will eventually try to convince you that you can use something like a phone number as a key, because it will also uniquely identify a customer. Don't do it. The last thing you want to have to do is update your entire database because someone moved and changed phone numbers.

Create the Order table

For the Order table (see Listing 2), you just want to tie it to a customer and a date and allow for a discount. You can calculate the rest of the price in code.

                CREATE TABLE orders ( 
	id int generated always as identity constraint ord_pk primary key,
    customer_id int constraint cust_foreign_key references customers, 
    order_time timestamp,
    discount float
)

In addition to the id primary key, you've declared the customer_id column to be a foreign key referencing the Customer table. (If you don't include a foreign column in the declaration, Derby assumes that you're referencing the primary key of the other table.) This means that Derby will validate that any customer_id added to this table actually matches a customer in the system. Your database administrator will tell you that you should always do this. However, I think that there are legitimate cases in which you might not want the database to strictly validate all the time. For example, you might need to enter data before you know or can validate what the foreign value is. Alternately, you might want to delete the foreign row but keep your table's row. In this case, for example, you might want to delete a customer but keep the customer's orders around for data-gathering purposes. You can coax Derby into allowing that, but it may not be portable to other database systems.

Create the Toppings table

The last database design problem is the pizza and toppings. Well, not the toppings; that's pretty simple, as Listing 3 shows.

                
CREATE TABLE toppings(
    id int generated always as identity constraint top_pk primary key,
    name varchar(100), 
    price float
)

The question is, how do you manage the relationship between pizza and toppings? A pizza is an order, a size, and a set of toppings. Classic database normalization would say to create a Pizza table, and then a many-to-many table relating pizza IDs to topping IDs. Doing so has many nice properties, among them the fact that it allows an infinite number of toppings on a pizza. However, managing the database relationship between the tables can have a performance cost. If infinite toppings aren't needed, you can include several topping fields in the Pizza table (topping_1, topping_2, and so on). Conceptually, that's a bit simpler, but it would make it awkward to, say, mine your order data to count the most popular toppings. If you're feeling particularly adventurous, you could have a single topping field and populate it with a bitmap or concatenated string or the like. I really don't recommend that.

Create the Pizza table

After a bit of thought, I've decided to go with the fully normal form. You'd want to allow enough toppings on a pizza that putting them all in the same table would become rather ugly. So, use the code shown in Listing 4.

                
CREATE TABLE pizzas (
    id int generated always as identity constraint piz_pk primary key,
    order_id int constraint order_foreign_key references orders,
    size int 
)

CREATE TABLE pizza_topping_map (
    id int generated always as identity constraint ptmap_pk primary key,
    pizza_id int constraint pizza_fk references pizzas,
    topping_id int constraint topping_fk references toppings,
    placement int
)

Just to be clear, you'll have sizes 1, 2, 3, and 4 representing small, medium, large, and extra large, respectively. The topping placement will be -1, 0, or 1 for left half, whole pizza, and right half, respectively. And you do need a separate ID for each mapping so that you can, say, allow for extra pepperoni by having pepperoni appear as a topping twice in the same pizza.

Note: Did I mention that all those names you put after constraint must be unique across your database? They do. Derby is actually creating an index behind the scenes, and each index must have a unique name.

That should do it for your database schema. Now you can get it into the database.

Populate the database

You have a schema; now you must set it up and get some initial data into place. You're going to create a short stand-alone program that performs this setup. That's not the only choice, however. You could use the Derby ij command line to enter the SQL commands directly, or you could use a graphical SQL tool. The programmatic approach, however, gives you a nicely controlled way to see how to start Derby and how Derby differs from other JDBC databases. In practice, you would probably keep the SQL schema in its own SQL script as well.

You start with some fairly static data -- the list of pizza toppings that you included in the Slicr page in Part 1. Again, this approach works mostly because you're inserting static data. You'll set up a Toppings table in which each topping has a name and a base price. The code shown in Listing 5 sets up that data. For the moment, assume that all the toppings have the same price.

                
public class SlicrPopulatr {

    public static final String[] TOPPINGS = new String[] {
        "Anchovy", "Gardineria", "Garlic", 
        "Green Pepper", "Mushrooms", "Olives", 
        "Onions", "Pepperoni", "Pineapple", 
        "Sausage", "Spinach"
    }
    
    public void populateDatabase() throws Exception {
        Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
        Connection con = DriverManager.getConnection(
                "jdbc:derby:slicr;create=true");
        con.setAutoCommit(false);
        Statement s = con.createStatement();
        s.execute("DROP TABLE toppings");
        s.execute("CREATE TABLE toppings(" +
                "id int generated always as identity constraint top_pk primary key, " +
                "name varchar(100), " +
                "price float)");
        //        
        // All the other create table statements from above would go here...
        //
        for (int i = 0; i < TOPPINGS.length; i++) {
            s.execute("insert into toppings values (DEFAULT, '" +
                    TOPPINGS[i] + "', 1.25)");
        }
        con.commit();
        con.close();
        try {
            DriverManager.getConnection("jdbc:derby:;shutdown=true");
        } catch (SQLException ignore) {}
    }    
    
    public static void main(String[] args) throws Exception {
        (new SlicrPopulatr()).populateDatabase();
    }

If you're familiar with JDBC, most of this code will be familiar. However, there are a couple of Derby-specific features that I should cover. You start by loading the driver class using the Class.forName idiom. Because you're going to be using the embedded version of Derby, the class name for the driver is org.apache.derby.jdbc.EmbeddedDriver. Next, you create the connection string. The Derby URL is of the form:

jdbc:derby:database name;[attr=value]

The database name is the name you want to use to refer to your database. It doesn't matter much what you pick as long as you're consistent when you open the database again in your server code.

After you create the connection, you're in standard JDBC land. You create a Statement to execute commands to drop and recreate the table, which allows you to reset the database from this program if it becomes corrupted. (Otherwise, Derby would throw an exception when it tried to create a table that already existed). After you create the table, you use one insert statement for each entry in your toppings array.

The SQL code in the insert statements has one feature you may not be expecting. I used the keyword DEFAULT as a placeholder for the Identity column. Derby expects that keyword in the slot of the Identity column if you don't specify the column list in your insert statement.

Before the program exists, you make a special call to get a connection with the URL "jdbc:derby:;shutdown=true" -- you don't need to specify the database. This call tells the Derby system to shut down and release any connections that might be active.

After running this little program, you'll see a directory in your application's top-level directory called derbyDb. This directory stores the binary files in which Derby stores its data. Don't change those files in any way.

Prepare data for GWT

With your database schema in place and static data loaded, now you must address how you're going to communicate that data to your client and vice versa. Eventually, you're going to have to serialize data across the client-server connection. For that serialization to work, your eventual data classes must be where GWT can see and deal with them, which means that the classes must be defined in your client package and compilable by the GWT Java-to-JavaScript compiler.

There are a few additional restrictions on a client class that's going to be serialized. For one, the class must implement the interface com.google.gwt.user.client.rpc.IsSerializable, which is a marker interface, defining no methods. Furthermore, all the data fields in the class must themselves be serializable. (As with ordinary Java serialization, you can exempt fields from being serialized by marking them as transient.)

What makes a serializable field? First, the field can be of a type that implements IsSerializable or has a superclass that does. Or the field can be one of the basic types, which includes Java primitives, all the primitive wrapper classes, Date, and String. An array or collection of serializable types is also serializable. However, if you're going to serialize a Collection or List, GWT prefers that you annotate it with a Javadoc comment specifying the actual type so that the compiler can optimize it. Listing 6 shows a sample for a field and for a method.

                
/**
 * @gwt.typeArgs <java.lang.Integer>
 */
private List aList; 

/**
 * @gwt.typeArgs <java.lang.Double>
 * @gwt.typeArgs argument <java.lang.String>
 */
public List doSomethingThatReturnsAList(List argument) {
    // Stuff goes here
}

Note: The argument in a method list has to be specified by name in the comment, while the return value does not.

Notice that anything having to do with java.sql and the JDBC is missing from that list of serializable objects. Whatever you do to get from your result set to your data object you must do in your server-side code.

At this point, you enter the world of Object-Relational Mapping (ORM), or transitioning data from a relational database structure to the object-oriented structure of your Java program. For a complex Java production system, you probably want to use a pre-existing, full-blown ORM system, such as Hibernate or Castor. Both of these systems automatically load your data from the database into Java objects of your choosing. However, they also require extensive configuration before you get started. In the interest of focusing on Derby and GWT here, I present a quick converter that serves during the start of development. Eventually, you can swap it for a more powerful tool.

A simple ORM converter

First, create bean classes for all your data tables. I use the Topping class as the example because it's simple and already has data. Use the ordinary bean-naming conventions for each column in the table, but convert underscores to mixed case (for example, topping_id becomes getToppingId). Listing 7 shows the Topping class.

                
package com.ibm.examples.client;

import com.google.gwt.user.client.rpc.IsSerializable;

public class Topping implements IsSerializable {

    private Integer id;
    private String name;
    private Double price;

    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public Double getPrice() {
        return price;
    }
    public void setPrice(Double price) {
        this.price = price;
    }
}

The simple ORM tool is next, as shown in Listing 8.

                
package com.ibm.examples.server;

import java.beans.PropertyDescriptor;
import java.lang.reflect.Method;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class ObjectFactory {

    public static String convertPropertyName(String name) {
        String lowerName = name.toLowerCase();
        String[] pieces = lowerName.split("_");
        if (pieces.length == 1) {
            return lowerName;
        }
        StringBuffer result = new StringBuffer(pieces[0]);
        for (int i = 1; i < pieces.length; i++) {
            result.append(Character.toUpperCase(pieces[i].charAt(0)));
            result.append(pieces[i].substring(1));
        }
        return result.toString();
    }

    public static List convertToObjects(ResultSet rs, Class cl) {
        List result = new ArrayList();
        try {
            int colCount = rs.getMetaData().getColumnCount();
            while (rs.next()) {
                Object item = cl.newInstance();
                for (int i = 1; i <= colCount; i += 1 ) {
                    String colName = rs.getMetaData().getColumnName(i);
                    String propertyName = convertPropertyName(colName);
                    Object value = rs.getObject(i);
                    PropertyDescriptor pd = new PropertyDescriptor(propertyName, cl);
                    Method mt = pd.getWriteMethod();
                    mt.invoke(item, new Object[] {value});
                }
                result.add(item);
            } 
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
        return result;
    }
}

The convertToObjects() method simply loops through the result set, infers the property getter using JavaBean reflection, and sets all the values. The convertPropertyName() method switches between the SQL underscored naming convention and the Java mixed-case convention.

The code has more going for it than you might think. You can run with it right away on any database tool without further configuration. You don't need to keep a mapping file in sync with your database during early development, when your schema might change. And it won't be hard to swap a more powerful tool in when the time comes.

Listing 9 shows this tool in action, reading back all the Topping instances you created earlier.

                
public class ToppingTestr {

    public static final String DRIVER = "org.apache.derby.jdbc.EmbeddedDriver";

    public static final String PROTOCOL = "jdbc:derby:slicr;";

    public static void main(String[] args) throws Exception {
        try {
            Class.forName(DRIVER).newInstance();
            Connection con = DriverManager.getConnection(PROTOCOL);
            Statement s = con.createStatement();
            ResultSet rs = s.executeQuery("SELECT * FROM toppings");
            List result = ObjectFactory.convertToObjects(rs, Topping.class);
            for (Iterator itr = result.iterator(); itr.hasNext();) {
                Topping t = (Topping) itr.next();
                System.out.println("Topping " + t.getId() + ": " +
                        t.getName() + " is $" + t.getPrice());
            }
        } finally {
            try {
                DriverManager.getConnection("jdbc:derby:;shutdown=true");
            } catch (SQLException ignore) {}
        }
    }

}

This test program creates a Derby connection to the Slicr database. (You're no longer asking the protocol string to create the database if needed.) You perform a simple SQL query, and then pass the results to your factory. Then you're free to loop over the resulting list and quit the database.

Tune in next time

Your database is now installed and configured. You created a database schema and discovered some simple tools for putting data into it. After two articles in this series, your Slicr project now has simple but functional front and back ends. The next step is communication. In the third article in this series, you'll learn about the framework that GWT uses to make Remote Procedure Calls (RPCs) easy to code and manage.

Resources

Learn

• Visit the Google Groups forum for GWT.
  
  
• Check out the official GWT blog.
  
  
• Get resources for Derby.
  
  
• Learn more about the Eclipse Foundation and its many projects.
  
  
• Visit the developerWorks Open source zone for extensive how-to information, tools, and project updates to help you develop with open source technologies and use them with IBM's products.
  
  
• Browse all the Apache articles and free Apache tutorials available in the developerWorks Open source zone.
  
  
• Browse for books on these and other technical topics at the Safari bookstore.
  
  
• Check out the developerWorks Apache Derby project area for articles, tutorials, and other resources to help you get started with Derby today.
  
  
• Visit the Apache Derby Project Web site.
  
  
• Stay current with developerWorks technical events and webcasts.
  
  

Get products and technologies

• Download the Google Web Toolkit.
  
  
• Download the open source, freely available, and extensible Eclipse IDE.
  
  
• Innovate your next open source development project with IBM trial software, available for download or on DVD.
  
  
• Download Apache Derby.
  
  

Discuss

• Get involved in the developerWorks community by participating in developerWorks blogs.
  
  

About the author

Rate this article

Comments

Back to top

Table of contents

• Get Derby
• Design your schema
• Populate the database
• Prepare data for GWT
• Tune in next time
• Resources
• About the author
• Comments

Next steps from IBM

Create rich Internet applications using WebSphere Portal Express Web 2.0 technologies.

---

• Try: WebSphere Portal Express
• Article: Get started using Ajax and WebSphere Portal Express
• Tutorial: Quickly build a an Ajax-enabled WebSphere Portal Express Web application
• Demo: Architecture, design, and construction using the IBM Rational Software Delivery Platform: AJAX
• Buy: WebSphere Portal Express

Dig deeper into Java on developerWorks

IBM SmartCloud trial. No charge.

Unleash the power of hybrid cloud computing today!

---

Public and private solutions in one trial.

Special offers