 | Level: Intermediate Noel Rappin (noelrappin@gmail.com), Senior Software Engineer, Motorola, Inc.
23 Jan 2007 In this second article in the series on using the Google Web Toolkit (GWT) to build Asynchronous JavaScript + XML (Ajax) applications, learn how to build the Apache Derby database for your Web application, and use it to drive the GWT. Part 1 of this series introduced you to GWT and demonstrated how you can use it to create a rich-client front end for a Web application. This time, you'll go behind the scenes and set up the back end with your database and the code used to convert the data to a format that GWT can use. By the end of this article, you'll be ready for the front end and back end to talk to each other.
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.
| | Check out the Ajax Resource Center, your one-stop shop for information on the Ajax programming model, including articles and tutorials, discussion forums, blogs, wikis, events, and news. If it's happening, it's covered here. |
|
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:
- Extract the two plug-in files. Each has a top directory named plugin.
- Copy the contents of that directory into your Eclipse plug-in directory.
- Open your project in Eclipse.
- 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.
Figure 1. The Eclipse Derby menu
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.
Listing 1. The Customer table
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.
Listing 2. The Order table
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.
Listing 3. The Toppings table
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.
Listing 4. The Pizza table
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.
Listing 5. Set up the Toppings table in Derby
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.
Listing 6. A serializable field and 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.
Listing 7. 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.
Listing 8. Simple ORM tool
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.
|
What the ORM tool doesn't do
You could fill a book with all the useful ORM features missing from this tool. For
example, the tool doesn't:
- Avoid creating multiple versions of the same object.
- Let you write back to the database.
- Work very fast.
|
|
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.
Listing 9. Testing the ORM tool
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
Get products and technologies
Discuss
About the author | | | Noel Rappin, a Ph.D. from the Graphics, Visualization, and Usability Center at the Georgia Institute of Technology, is a senior software engineer at Motorola. He is also the coauthor of wxPython in Action and Jython Essentials. You can check out Noel's blog at 10printhello.blogspot.com. |
Rate this page
| |
