For most developers—especially of the Java variety—data binding has become a part of the common vocabulary, along with closures, singletons, and Ajax. And like those other terms, data binding is often defined incorrectly.

Specifically, when most programmers hear data binding, they actually think XML data binding. The insertion of that little word—XML—causes most programmers to miss out on a world of extra functionality and flexibility, especially if the Castor API is being used. That's because when it comes to Castor, XML data binding is just a part of the puzzle. Castor also offers the ability to bind Java data to SQL databases, in addition to binding to XML documents. That's where SQL data binding comes into play.

Defining SQL data binding

SQL data binding might be a new term, but it's a pretty simple concept. In fact, it's best viewed in light of what a more familiar term, XML data binding, means. XML data binding is simply the process of creating a mapping between the data in an XML document—usually stored in elements and attributes—and the properties of a Java object model. You can run a marshaller and an unmarshaller to move between the two. A marshaller takes data in a Java object model and stores that data in an XML document; an unmarshaller takes data from an XML document and loads it into the properties of a Java object model.

Based on that, then, it shouldn't come as any surprise that SQL data binding is the process of creating a mapping between the data in SQL databases—stored in schemas, tables, columns, and so forth—and Java objects. The same process of marshalling and unmarshalling applies, but the translation occurs between Java objects and SQL rather than Java objects and XML. In fact, if you replace XML with SQL and element data with table entry in most data binding articles, you've already got a handle on what SQL data binding is all about.

Back to top

The value of SQL data binding

When Java technology first came out, it was almost a toy language, largely because it had such a simple API, and the focus was on graphics (remember the AWT?). A hallmark of the maturation of Java technology was the addition of Java database connectivity (JDBC), allowing persistence to SQL databases. The only problem was (and is) that JDBC is fairly clunky to use. It's not that complex, but it introduces a significant amount of extra work to most programs.

With Castor and SQL data binding, you can avoid most of that complexity. Even better, you get to use an API that functions largely the same in both an XML and SQL context. And, as with data binding, your application has less plumbing details to worry about. Dealing with JDBC ResultSets and row counts is no longer a concern in your code; instead, a few simple marshalling and unmarshalling calls handle conversion between Java objects and your SQL database.

What's perhaps most interesting about SQL data binding is that it hasn't gotten more press and attention. This is especially true considering that a large contingent of programmers don't care for XML, either because it's too verbose, too unwieldy, or they prefer binary serialization. That same group, though, happily embraces SQL. In fact, you'll be hard-pressed to find a group of even semi-serious programmers who don't think that SQL is a legitimate data store and persistence technology. (See how many of your buddies think that relational databases are bloated, overhyped, or irrelevant.)

Given that, SQL data binding is arguably an even more useful tool than its XML cousin. You always need to persist (or store) data in an enterprise application, and writing data access and retrieval code is a pain. SQL data binding takes a familiar API (assuming you've read the previous articles in this series) and applies it all to SQL data stores. You can write to a table (or multiple tables) with a few commands, and pull data in the same way.

Mapping still applies, too

Particularly important to SQL data binding is the ability to map from your Java objects to a SQL data schema without being bound by Java or SQL names. Even more so than XML, the structure of a relational database is typically radically different from the structure of your Java objects. Tables are generally collections of data, while an object usually represents a single piece (possibly a row) of data. Relationships between objects have to be traced to other objects, just as relationships between tables must be traced to other tables. But there's no one-to-many join table in your Java object model, and certainly no many-to-many joins.

When you start to get into even intermediate complexity in your relational database, you'll design things differently than you would with an object model. A lot of the work in mapping from SQL to Java objects and back is defining the mapping between objects and tables. Although mappings can be quite advanced, I'll use a few simple mappings in this article to give you a basic understanding of how mapping in the SQL data binding world works.

Back to top

This is just JDO, right?

Yes. Well, no. Sort of. It gets a little confusing here.

Sun has a specification referred to as Java Data Objects (JDO). Java Specification Request (JSR) 12 for JDO 1.0 and JSR 243 for JDO 2.0 define a very specific approach to SQL data binding (although it's never explicitly called "SQL data binding"). If you read up on JDO and then reread the introductory paragraphs to this article, you'll think you're seeing double. However, Castor's version of JDO (yes, Castor calls it that, too, which makes things even more confusing) is not the same as Sun's, or even related to Sun's, except that both have the same basic goal.

That can make for a lot of confusion, so it should be said again: if you use Castor's JDO, you're not using the Sun-standard API. But wait, it's not as bad, or as clear-cut, as that makes it seem. The folks at Castor are actually trying to get many of the features they've worked into their own API into Sun's data binding and JDO APIs. So while Castor currently isn't a standard API, many of the features—and the API itself—might make their way into the standardized JDO API.

Setting up an example environment

In the case of XML data binding, your two endpoints are a Java object model and an XML document (really, an XML schema that constrains documents). In the SQL data binding world, you still have a Java object model, but the other endpoint is a SQL schema: one or more tables with columns. Look at a relatively simple set of endpoints for this article's examples.

I've used a Java object model to represent books and authors in this series, so I'll reuse that same data model in this article. Listing 1 shows the code for the Book class.

package ibm.xml.castor;

import java.util.LinkedList;
import java.util.List;

public class Book {

  /** The book's ISBN */
  private String isbn;
  /** The book's title */
  private String title;
  /** The authors' names */
  private List<Author> authors;

  public Book() { }

  public Book(String isbn, String title, List<Author> authors) {
    this.isbn = isbn;
    this.title = title;
    this.authors = authors;
  }

  public Book(String isbn, String title, Author author) {
    this.isbn = isbn;
    this.title = title;
    this.authors = new LinkedList<Author>();
    authors.add(author);
  }

  public void setIsbn(String isbn) {
    this.isbn = isbn;
  }

  public String getIsbn() {
    return isbn;
  }

  public void setTitle(String title) {
    this.title = title;
  }

  public String getTitle() {
    return title;
  }

  public void setAuthors(List<Author> authors) {
    this.authors = authors;
  }

  public List<Author> getAuthors() {
    return authors;
  }

  public void addAuthor(Author author) {
    authors.add(author);
  }
}

Listing 2 is the code for the Author class.

package ibm.xml.castor;

public class Author {

  private String firstName, lastName;

  public Author() { }

  public Author(String firstName, String lastName) {
    this.firstName = firstName;
    this.lastName = lastName;
  }

  public void setFirstName(String firstName) {
    this.firstName = firstName;
  }

  public void setLastName(String lastName) {
    this.lastName = lastName;
  }

  public String getFirstName() {
    return firstName;
  }
  
  public String getLastName() {
    return lastName;
  }

}

In earlier articles, you took several instances of these classes and persisted them out to XML documents. In the first two articles, you focused on converting directly from the object model to XML documents, using the same property names in both. In the third article, the focus was on converting from Java objects to XML (and back again), but with the ability to change naming conventions from one format to the other.

In this case, though, you want to take all the data in these classes and store that data in a SQL database. Instead of elements and attributes, the data belongs in columns, rows, and tables.

Creating a sample SQL database schema

In some ways, a SQL database schema often maps more closely to a Java object model, especially if the model isn't too complex (as is the case here). Let's consider two tables: dw_books and dw_authors.

The dw_authors table is actually easiest to create first, because the dw_books table refers to it. It should have three columns: an ID for each author, and their first and last names. If you're following along, you can create this table in MySQL with the code in Listing 3.

CREATE TABLE 'dw_authors' (
'id' INT NOT NULL ,
'first_name' VARCHAR( 50 ) NOT NULL ,
'last_name' VARCHAR( 50 ) NOT NULL ,
PRIMARY KEY ( 'id' ) ,
INDEX ( 'first_name' , 'last_name' )
);

Note that I've used the dw_ prefix on all tables in this article to distinguish them from other tables you might run across, particularly because authors and books are common table names. While this is potentially a bit awkward, it ensures you won't have naming conflicts with other databases on your system if you're following along (and hopefully you are).

Next is the dw_books table. It's also pretty simple, and the data definition language (DDL), which is the language you use to create structures in SQL, is in Listing 4.

CREATE TABLE 'dw_books' (
'isbn' VARCHAR( 13 ) NOT NULL ,
'title' VARCHAR( 200 ) NOT NULL ,
PRIMARY KEY ( 'isbn' ) ,
INDEX ( 'title' )
);

Finally, you have to connect these two tables somehow. Here, an issue already presents itself. A book can have multiple authors, which means that there's a many-to-many relationship between the dw_books table and the dw_authors table. In other words, one book can have multiple authors, and one author can write many books. So you need a table to connect a book to an author, and you must allow those connections to be many-to-many.

In the SQL world, this is pretty common fare: you need a join table, where each row has a book ISBN and an author ID. Listing 5 shows what that table might look like.

CREATE TABLE 'dw_books_authors' (
'book_isbn' VARCHAR( 13 ) NOT NULL ,
'author_id' INT NOT NULL ,
PRIMARY KEY ( 'book_isbn' , 'author_id' )
);

Understanding referential integrity

Referential integrity is a fancy SQL term that refers to keeping all the data in your database clean, free from incorrect or unused data, and accurate in terms of joins across tables. In this context, it means that if you delete a book, then all records in the dw_books_authors table with that book's ISBN will get deleted. Once the book no longer exists, then no records with that book's ISBN should exist, either. The same is true for authors; when an author is deleted, then any records with that author's ID should be deleted as well.

To accomplish this, you use foreign keys. So in the dw_books_authors table, book_isbn has isbn in the dw_books table as a foreign key; the same relationship holds between author_id in dw_books_authors and id in dw_authors. That ensures referential integrity between the tables.

However, most databases handle referential integrity differently, and MySQL doesn't completely support it in many versions of the software. So to make things simpler for this article, no foreign keys are used. If you'd like to create foreign keys, which is a great idea, check the vendor documentation for your database or ask your friendly neighborhood database administrator (DBA).

Back to top

Getting set up for SQL data binding

Even if you've used Castor for XML data binding, you'll probably need to make a few changes (or at least additions) to your programming environment to get SQL data binding up and running.

Adding Castor's JDO files to your classpath

The first thing you need is Castor's JDO classes on your classpath. If you followed the installation steps back in this series' first article (see Resources for links to that article), you'll have all the Castor Java ARchive (JAR) files. However, you might not have added the specific JDO JAR file to your classpath. In your Castor directory, find and add castor-1.1.2.1-jdo.jar to your classpath (your version might be more recent, but you get the idea).

This is in addition to the other Castor JARs that you probably already include, such as castor-1.1.2.1.jar, castor-1.1.2.1-xml.jar, and the Xerces and Commons Logging JAR files. If you use an application server or IDE to run your code, make sure those classpaths are changed as well.

Adding a JDBC driver to your environment

Since you'll connect to a SQL database, you need a JDBC driver for your vendor. JDBC drivers for IBM DB2® and Informix®, Oracle, MySQL, PostgreSQL, and so on are all freely available. If you've already got some applications that have database access, this might be taken care of for you. If you're new to JDBC, check out Resources for links to JDBC drivers from DataDirect that work well on DB2 and Informix, as well as several other database servers. If you're not using DB2 or Informix, find the JDBC driver for your database vendor with a quick Google search, download a JAR, and add that to your classpath as well.

For this article, I use the MySQL database in most examples. If you go with MySQL, you'll need mysql-connector-java-3.0.17-ga-bin.jar, freely available from the MySQL Web site (see Resources for more information).

Back to top

Mapping a class to a table

Look at the simplest SQL data binding task: mapping an object that doesn't refer to any other objects to a single table. This is the most fundamental operation you'll perform in SQL data binding, and all the more advanced features turn out to be variations on this theme.

Figuring out a mapping

First map the Author class to the single dw_authors table. You want a mapping like that shown in Table 1.

This is simple enough, but there's an obvious problem: the dw_authors table also has an id column. In fact, it's the primary key of the dw_authors table, so it must exist. As a result, you need to make an addition to the Author class, as shown in Listing 6.

package ibm.xml.castor;

public class Author {

  private String firstName, lastName;
  private int id;

  public Author() { }

  public Author(int id, String firstName, String lastName) {
    this.id = id;
    this.firstName = firstName;
    this.lastName = lastName;
  }

  public void setId(int id) {
    this.id = id;
  }

  public int getId() {
    return id;
  }

  public void setFirstName(String firstName) {
    this.firstName = firstName;
  }

  public void setLastName(String lastName) {
    this.lastName = lastName;
  }

  public String getFirstName() {
    return firstName;
  }
  
  public String getLastName() {
    return lastName;
  }

}

Now you can update the table that shows the mapping between an Author instance and a row in the dw_authors table, as shown in Table 2.

Creating a mapping for Castor

Now you need to tell Castor which Java properties map to which SQL columns. You use a mapping file for this, similar to the mapping files you used for XML in the third article of this series. Listing 7 shows the mapping you use to convert Author instances to rows in dw_authors.

<mapping>
  <class name="ibm.xml.castor.Author" identity="id">
    <map-to table="dw_authors" />
    <field name="id" type="int">
      <sql name="id" type="integer" />
    </field>
    <field name="firstName" type="string">
      <sql name="first_name" type="varchar" />
    </field>
    <field name="lastName" type="string">
      <sql name="last_name" type="varchar" />
    </field>
  </class>
</mapping>

There's not much to this; it looks similar to the mapping files you used with Java objects and XML. Save this file as sql-mapping.xml, and now you can tell Castor how to map the properties in your Java objects into your database tables.

The identity attribute

Compared to what you've seen in Java and XML mapping, the only thing new in the mapping file is the use of an identity attribute, which indicates the primary key field for the dw_authors table. You probably haven't used the identity attribute before, as Castor often infers it or doesn't need it to function. In SQL data binding, though, this attribute lets Castor know the field that uniquely defines an object; in this case, that's the id field of an author.

Configuring Castor for SQL access

In the XML data binding world, you'd be ready to write code to marshal from XML to Java objects and vice versa. However, in the SQL paradigm, you've got another piece of software to deal with: the database. You need to tell Castor where your database is located, how to log in, and what mapping file to use. You accomplish this through a configuration file, usually named something like jdo-conf.xml. Listing 8 provides a configuration file you can use for MySQL.

<?xml version="1.0" ?>
<!DOCTYPE jdo-conf PUBLIC "-//EXOLAB/Castor JDO Configuration DTD Version 1.0//EN"
                          "http://castor.org/jdo-conf.dtd">
<jdo-conf>
  <database name="YOUR-DATABASE-NAME" engine="mysql">
    <driver class-name="com.mysql.jdbc.Driver"
            url="jdbc:mysql://YOUR-DATABASE-SERVER-HOSTNAME/YOUR-DATABASE-NAME">
      <param name="user" value="YOUR-DATABASE-USERNAME"/>
      <param name="password" value="YOUR-DATABASE-PASSWORD"/>
    </driver>
    <mapping href="sql-mapping.xml"/>
  </database>
  <transaction-demarcation mode="local"/>
</jdo-conf>

Adding, looking up, and removing an entry

You've done a lot of setup, but you're finally ready to interact with the database. Using these configuration files, you need to load the configuration, open up a database, and interact with it. Listing 9 does all of that, creating a new Author instance, storing it in the database, looking up that instance, and then removing it.

import ibm.xml.castor.Author;

import org.exolab.castor.jdo.Database;
import org.exolab.castor.jdo.JDOManager;

public class SQLTester {
  public static void main(String args[]) {
    try {
      JDOManager.loadConfiguration("jdo-conf.xml");
      JDOManager jdoManager = JDOManager.createInstance("bmclaugh");

      Database database = jdoManager.getDatabase();
      database.begin();
      Author author = new Author(1001, "Joseph", "Conrad");
      database.create(author);

      Author lookup = (Author)database.load(Author.class,
                                    new Integer(1001));
      System.out.println("Located author is named " +
        author.getFirstName() + " " + author.getLastName());
      database.remove(lookup);
      database.commit();
      database.close();
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
}

Let's walk through this step by step.

First, you need to do some initial work to load your connection information and connect to the database. This is basically the same for every application where you use SQL data binding, so you' largely need to just memorize these commands (or bookmark this article):

JDOManager.loadConfiguration("jdo-conf.xml");
JDOManager jdoManager = JDOManager.createInstance("bmclaugh");
Database database = jdoManager.getDatabase();
database.begin();

The goal here is to get an instance of the Database object, which allows you to create, look up, and delete entries. Once you have that instance, you can create a new instance of Author and store it in the database:

Author author = new Author(1001, "Joseph", "Conrad");
database.create(author);

When you call create(), Castor uses your mapping file (sql-mapping.xml) to figure out how to store the object you pass to that method as SQL data. Your data is pushed into the database, and you're ready to use that data.

Next, you can look up objects by their identity (here's where that identity attribute comes into play):

Author lookup = (Author)database.load(Author.class,
                                    new Integer(1001));
System.out.println("Located author is named " +
  author.getFirstName() + " " + author.getLastName());

You use the load() method and pass it the class type you want to load, as well as the identity value for the entry you want. load() returns a new instance that you can use like any other Java object, again using the mappings defined in sql-mapping.xml.

This example deletes the entry, leaving the database in the same state as it was before the program ran:

database.remove(lookup);

Last, but not least, you need to commit these changes and close the database:

database.commit();
database.close();

Back to top

Adding relational data

As you can see, working with a single class-to-table mapping isn't too difficult. However, things get more interesting when you need to work with a more realistic situation, such as a Java object model with Books related to Author instances.

Defining a basic SQL mapping for a book

As with Author, you need to define a mapping in sql-mapping.xml to take the properties of the Book class and persisting them to a relational database. Start with the additions in Listing 10; these handle the basic properties of a book.

<mapping>
  <class name="ibm.xml.castor.Book" identity="isbn">
    <map-to table="dw_books"/>
    <field name="isbn" type="string">
      <sql name="isbn" type="varchar" />
    </field>
    <field name="title" type="string">
      <sql name="title" type="varchar" />
    </field>
  </class>

  <class name="ibm.xml.castor.Author" identity="id">
    <map-to table="dw_authors" />
    <field name="id" type="int">
      <sql name="id" type="integer" />
    </field>
    <field name="firstName" type="string">
      <sql name="first_name" type="varchar" />
    </field>
    <field name="lastName" type="string">
      <sql name="last_name" type="varchar" />
    </field>
  </class>
</mapping>

None of this is surprising; it just takes the Book class and identifies the table to map to (dw_books) and how to map that object's properties (isbn and title).

Defining a many-to-many relationship

Now comes the interesting part: telling Castor how books are related to authors. Remember, the relationship works like this:

• Each book has a unique ISBN.
• Each author has a unique ID.
• When a book is written by an author, an entry in dw_books_authors has the book's ISBN and the author's ID.
• A book can have multiple authors (dw_books_authors has multiple entries with the same book ISBN but a different author ID).
• An author can write multiple books (dw_books_authors has multiple entries with the same author ID but different book ISBNs).

First, examine this relationship from the Book class side of the equation. In an instance of Book, you want to populate the authors property with the authors for that book. The converse is true, as well. If a Book instance has data in its authors property, you need to store those authors through the ID of each author. You must map that property—authors—to entries in dw_books_authors.

To make this work, you start with another Castor field element, as shown in Listing 11.

<field name="authors" type="ibm.xml.castor.Author"
       collection="arraylist">
</field>

So far, nothing new here; you map the authors property, and the type is a class this time, instead of string or int. You use collection="arraylist" to tell Castor the property is a collection and not single-valued.

Now, though, you need to indicate the field to store the author's ID in, as well as what table to store that field in, because the author's ID never ends up in the dw_books table. Listing 12 shows how to indicate the name of the many-to-many table—in this case, dw_books_authors—as well as the key for the book's ISBN in that same table.

<field name="authors" type="ibm.xml.castor.Author"
       collection="arraylist">
  <sql name="author_id"
       many-table="dw_books_authors"
       many-key="book_isbn" />
</field>

Trace this logic through carefully. First, remember that this element is all about values in the authors property of Book instances. The actual field being mapped is an instance of Author, and you want to take the identifying part of that object—the id—and map it to a column called author_id. However, that's not in the dw_books table, so you use many-table to indicate that the table this goes in is dw_books_authors. Finally, the many-key attribute indicates to store this Book instance's ISBN in your many-to-many table, as well.

Listing 13 shows sql-mapping.xml with this integrated into the rest of the file.

<mapping>
  <class name="ibm.xml.castor.Book" identity="isbn">
    <map-to table="dw_books"/>
    <field name="isbn" type="string">
      <sql name="isbn" type="varchar" />
    </field>
    <field name="title" type="string">
      <sql name="title" type="varchar" />
    </field>
    <field name="authors" type="ibm.xml.castor.Author"
           collection="arraylist">
      <sql name="author_id"
           many-table="dw_books_authors"
           many-key="book_isbn" />
    </field>
  </class>

  <class name="ibm.xml.castor.Author" identity="id">
    <map-to table="dw_authors" />
    <field name="id" type="int">
      <sql name="id" type="integer" />
    </field>
    <field name="firstName" type="string">
      <sql name="first_name" type="varchar" />
    </field>
    <field name="lastName" type="string">
      <sql name="last_name" type="varchar" />
    </field>
  </class>
</mapping>

Testing the mapping

Listing 14 shows how to revise the SQLTester program so it creates a new Author, then a Book related to that author.

import java.util.Iterator;

import ibm.xml.castor.Author;
import ibm.xml.castor.Book;

import org.exolab.castor.jdo.Database;
import org.exolab.castor.jdo.JDOManager;

public class SQLTester {
  public static void main(String args[]) {
    try {
      JDOManager.loadConfiguration("jdo-conf.xml");
      JDOManager jdoManager = JDOManager.createInstance("bmclaugh");

      Database database = jdoManager.getDatabase();
      database.begin();
      Author author = new Author(1001, "Joseph", "Conrad");
      Book book = new Book("1892295490", "Heart of Darkness", author);
      database.create(author);
      database.create(book);
      database.commit();

      database.begin();
      Book lookup = (Book)database.load(Book.class, "1892295490");
      System.out.println("Located book is named " + lookup.getTitle());
      System.out.println("Authors:");
      for (Iterator i = lookup.getAuthors().iterator(); i.hasNext(); ) {
        Author bookAuthor = (Author)i.next();
        System.out.println("  " + bookAuthor.getFirstName() + " " +
                                  bookAuthor.getLastName());
      }
      database.commit();
      database.close();
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
}

This looks pretty straightforward. You connect to the database, create a new author and a new book, and then add them both into the database (closing with a commit()):

      Database database = jdoManager.getDatabase();
      database.begin();
      Author author = new Author(1001, "Joseph", "Conrad");
      Book book = new Book("1892295490", "Heart of Darkness", author);
      database.create(author);
      database.create(book);
      database.commit();

Then, everything else is about checking the database to see if the data was entered successfully.

To better simulate real data persistence, the data is committed, and then a new transaction is started (with the second database.begin(), after the book and author data is committed). This ensures that Castor's local version of the data isn't used, but that the database is actually queried in the second half of the program.

Verifying the data

Take a peek at the data in your tables to make sure it matches your expectations. You should have something like the data in Table 3 in your dw_books table.

Table 4 shows the dw_authors table after insertion.

Finally, Table 5 shows the join between a book and its author, which in a lot of ways comprises the most important data you've seen yet. If authors and books can't be related, then none of this is very useful.

Deleting the data

Listing 15 is a short utility program that deletes the data that SQLTester inserts. As you play with SQL data binding and add books and authors (which you should absolutely do), you can update this class to remove those entries. Keeping the classes separate gives you a chance to examine the database between insertion and deletion, which is an important part of learning and debugging any problems that might occur.

import ibm.xml.castor.Author;
import ibm.xml.castor.Book;

import org.exolab.castor.jdo.Database;
import org.exolab.castor.jdo.JDOManager;

public class SQLClean {
  public static void main(String args[]) {
    try {
      JDOManager.loadConfiguration("jdo-conf.xml");
      JDOManager jdoManager = JDOManager.createInstance("bmclaugh");
      Database database = jdoManager.getDatabase();
      database.begin();
      try {
        Book book = (Book)database.load(Book.class, "1892295490");
        database.remove(book);
      } catch (org.exolab.castor.jdo.ObjectNotFoundException ignored) { }
      try {
        Author author = (Author)database.load(Author.class, 1001);
        database.remove(author);
      } catch (org.exolab.castor.jdo.ObjectNotFoundException ignored) { }
      database.commit();
      database.close();
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
}

The program has nested exception handling to deal with situations in which it looks up nonexistent data to delete, making the program a bit more robust.

Back to top

Castor doesn't persist your relationships by default

One interesting point in looking at SQLTester (in Listing 14) is that both the instance of Authorand the instance of Book are persisted to the database. In fact, if you take either of these out, you'll get a book or author, but not the other, and you won't get any data in dw_books_authors.

For Java programmers, this rightfully seems a bit odd. If a Book instance has an Author, aren't the two tied? Can a book be persisted without its author? Well, it can in Castor, which is the default behavior. However, if you want Castor to follow through on any relationships like this, you can issue the following command:

database.setAutoStore(true);

You have to do this before any transactions are created, so Listing 16 handles this task with a slightly modified version of Listing 14.

import java.util.Iterator;

import ibm.xml.castor.Author;
import ibm.xml.castor.Book;

import org.exolab.castor.jdo.Database;
import org.exolab.castor.jdo.JDOManager;

public class SQLTester {
  public static void main(String args[]) {
    try {
      JDOManager.loadConfiguration("jdo-conf.xml");
      JDOManager jdoManager = JDOManager.createInstance("bmclaugh");

      Database database = jdoManager.getDatabase();
      database.setAutoStore(true);
      database.begin();
      Author author = new Author(1001, "Joseph", "Conrad");
      Book book = new Book("1892295490", "Heart of Darkness", author);
      // We can persist just the book, and Castor will handle the author, as well
      // database.create(author);
      database.create(book);
      database.commit();

      database.begin();
      Book lookup = (Book)database.load(Book.class, "1892295490");
      System.out.println("Located book is named " + lookup.getTitle());
      System.out.println("Authors:");
      for (Iterator i = lookup.getAuthors().iterator(); i.hasNext(); ) {
        Author bookAuthor = (Author)i.next();
        System.out.println("  " + bookAuthor.getFirstName() + " " +
                                  bookAuthor.getLastName());
      }
      database.commit();
      database.close();
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
}

Try this out (be sure to run SQLClean first if you still have data in your database), and you'll get the same results as before—except without the additional database.create() call. While this is a small improvement, consider adding hundreds (or thousands!) of books with all those authors. Saving that extra work really matters.

Back to top

Conclusion

While XML data binding has stabilized and is largely being added to rather than changed, JDO—both Sun's variety and the Castor version—is still a volatile API. However, that doesn't mean you should avoid it, because SQL data binding and JDO (from any vendor) can make your programming life a lot easier. Use this article as a jumping-off point, rather than a definitive approach, to learning the technologies.

Play around with SQL data binding and see if it helps your application (it almost certainly will). Then, check out both Sun's and Castor's versions of JDO and see how you like them. Finally, choose a variety to work with. Even though Castor's version is nonstandard, it's more flexible, and with its work to get involved with Sun, the official standard might incorporate lots of Castor features in the future.

Most importantly, get better at programming. Learn how SQL data binding can help you out, save you effort, and ultimately improve the quality of your work and the applications you develop. Experiment with using SQL and XML data binding in the same application to understand how the same sort of mapping paradigm is used for both. You'll benefit from it, and you'll probably get rid of JDBC almost completely; nobody would argue that's not a good thing.

Back to top

Downloads

Information about download methods

Resources

Learn

• Data binding with Castor, Part 1: Install and set up Castor (Brett McLaughlin, developerWorks, November 2007): In the initial article of this series, take the first steps to run Castor on your own machine with downloading, installation, setup, configuration, class path issues, and more all covered.
  
  
• Data binding with Castor, Part 2: Marshall and unmarshall XML (Brett McLaughlin, developerWorks, December 2007): Convert your Java classes to XML and transform that XML back into Java code. Also, learn how Castor works and design your classes to function well with the API.
  
  
• Data binding with Castor, Part 3: Map between schemas (Brett McLaughlin, developerWorks, January 2008): Convert data in an unwieldy or inconvenient XML document to your custom Java objects using Castor. You'll no longer be constrained by the names of elements in your XML document, or by the member variable names in your Java classes.
  
  
• The Castor Web site: Visit the online hub for all things Castor.
  
  
• Castor classes: Read the JavaDoc.
  
  
• Introduction to Castor JDO: Get a great introduction to this object-relational mapping and databinding framework.
  
  
• Sun's Java Data Objects page: Visit the central place to find out about the official version of JDO.
  
  
• JSR 12: Read this specification that defines JDO 1.0, the original Java Data Objects.
  
  
• JSR 243: Dig into this specification on JDO 2.0 and the efforts to make JDO easier to use, standardize JDO's database support, and broaden the scope of JDO.
  
  
• Get started with Castor JDO (Bruce Snyder, developerWorks, August 2002): Read this older article on developerWorks. Some of the language specifics of this article are out of date, but the concepts are still right on.
  
  
• Practical data binding (Brett McLaughlin, developerWorks, May 2004): Start with the introductory article of a multi-part series for more on Sun's data binding API, JAXB.
  
  
• IBM XML certification: Find out how you can become an IBM-Certified Developer in XML and related technologies.
  
  
• XML technical library: See the developerWorks XML Zone for a wide range of technical articles and tips, tutorials, standards, and IBM Redbooks.
  
  
• developerWorks XML zone: Learn all about XML.
  
  
• developerWorks technical events and webcasts: Stay current with technology in these sessions.
  
  
• The technology bookstore: Browse for books on these and other technical topics.
  
  
• Podcasts: Tune in and catch up with IBM technical experts.
  
  

Get products and technologies

• JDBC driver for MySQL: Download and learn more about this native Java JDBC driver from MySQL.com.
  
  
• Java and XML, Third Edition (Brett McLaughlin and Justin Edelson, O'Reilly Media, Inc.): Learn about XML from start to finish, including extensive information on data binding and mapping.
  
  
• Java and XML Data Binding (Brett McLaughlin, O'Reilly Media, Inc.): In this older book, find details on Castor and the concepts involved in data binding.
  
  
• Castor Professional Services: Hunting for paid support or help with Castor? Look into Castor's professional services.
  
  
• DB2 Express-C: Download this no-charge version of DB2 that's great for trying out JDO. The IBM DB2 database is a SQL database that works great with Castor JDO, as well as Sun's standard JDO.
  
  
• IBM Informix: Check out a product line with industrial-strength database server products.
  
  
• JDBC drivers for DB2 and Informix: Try this single convenient, easily-downloadable package from DataDirect.
  
  
• IBM trial software: Build your next development project with trial software available for download directly from developerWorks.
  
  

Discuss

• Participate in the discussion forum.
  
  
• XML zone discussion forums: Participate in any of several XML-related discussions.
  
  
• developerWorks XML zone: Share your thoughts: After you read this article, post your comments and thoughts in this forum. The XML zone editors moderate the forum and welcome your input.
  
  
• developerWorks blogs: Check out these blogs and get involved in the developerWorks community.
  
  

About the author

Brett McLaughlin is a bestselling and award-winning non-fiction author. His books on computer programming, home theater, and analysis and design have sold in excess of 100,000 copies. He has been writing, editing, and producing technical books for nearly a decade, and is as comfortable in front of a word processor as he is behind a guitar, chasing his two sons around the house, or laughing at reruns of Arrested Development with his wife. His last book, Head First Object Oriented Analysis and Design, won the 2007 Jolt Technical Book award. His classic Java and XML remains one of the definitive works on using XML technologies in the Java language.

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Defining SQL data binding
• The value of SQL data binding
• This is just JDO, right?
• Getting set up for SQL data binding
• Mapping a class to a table
• Adding relational data
• Castor doesn't persist your relationships by default
• Conclusion
• Downloads
• Resources
• About the author
• Comments

Next steps from IBM

Spring, JRuby, and Ajax development is easier with WebSphere Application Server - a smart Java 5 and J2EE Web services-based application server.

---

• Try: The no-charge WebSphere Application Server Community Edition is a pre-integrated, lightweight Java 5 application server built on Apache Tomcat and other best-of-breed open source software such as OpenEJB, Apache Axis, and Apache Derby.
• Article: The article leverage the Spring Framework and the WebSphere Application Server to improve your J2EE project productivity.
• Tutorial: See how the free WebSphere Application Server and XML can improve the efficiency of your JRuby on Rails and Ajax development.
• Buy: WebSphere Application Server - Express

My developerWorks community

Dig deeper into XML on developerWorks

Calling all developers

Download no charge Lotus Domino Designer

Special offers