Pollux

 

Introduction

The relational database is ubiquitous, but it isn't very object-oriented, and it doesn't always fit in well with modern object oriented languages like Java. Most databases read and write information in terms of low level primitives: strings, numbers, and dates. Tables might be compared to collections of objects, but the analogy breaks down fast since there isn't any inheritance, nested data types, or reference types.

Java provides a low level API called JDBC, to connect to a database. You could use JDBC and SQL throughout your code, but you're usually better off isolating all the SQL code into a narrow layer of data access code. This layer represents a mapping between the SQL data and native objects.

Pollux is tool for creating mappings between database tables and Java classes, and then creating Java code that implements the mapping. Pollux is written in Java and uses Swing, so it runs just fine on Mac OS X, Windows, and Linux. The generated code uses generic JDBC and it should work with most relational databases.

The database code generated by Pollux uses the Data Access Object (DAO) pattern. In this pattern we create an object that represents a database table. This object contains methods for retrieving objects from the table, as well as creating, updating, and deleting objects. The DAO strategy decouples your program logic from the persistant data store. It also enhances portability because you can switch data stores by just recoding your DAO. Sun uses this pattern in their original Java Pet Store example application. Martin Fowler calls this pattern a Table Data Gateway.

Why use Pollux?

Pollux may not be the best choice for every object-relational need, but it fills an important niche. Pollux could be used in any application, but it's especially helpful when you need access to the JDBC and SQL code. It's also great when you need a very lightweight solution that doesn't link in other large or expensive JAR files. Also, I've written Pollux with portability in mind, so it should work well if you want to address multiple JVMs, application servers, and databases.

Before we get rolling, it's important to point out that there are a lot of object-relational (OR) alternatives out there. There are commercial data mappings, such as Toplink and Cocobase. There are good free data mappings, such as Castor and Hibernate. Every J2EE application server supports Entity Beans in two varieties: Container Managed Persistance (CMP) and Bean Manged Persistance (BMP). There's also a standard called Java Data Objects (JDO) that will modify existing class files to make them persistant. Last, but not least, you could create your own data access layer from scratch using JDBC.

Given so many choices, it would be easy to shop around for a prepackaged solution, and never consider writing your own JDBC code.

However, reusing software often has a downside. Most OR packages are extremely general. This means they include lots of extra code to handle every possible problem, not just your problem. More code means more complexity, lower performance, and more bugs. All large programs have bugs. What will you do when your OR package does something you don't expect? Read the documentation (if there is any)? Examine the source code (if there is any)? Put a call in to tech support (if you've purchased support)?

There are plenty of instances where reusing someone else's code library is more trouble than it's worth, especially when you could easily generate the code yourself.

Writing your own data access layer isn't hard. In fact, it's quite mechanical. After you've done a data access object (DAO) once, it's very easy to knock out lot of them. Unfortunately, "mechanical" means "boring". It also means "error-prone", since you can easily make a mistake, or leave something out. This is where Pollux comes in. Pollux allows you to easily define the mapping between tables and classes. It then generates good quality code to implement the DAOs. Once you have the code, you can use it as-is, or modify it for your situation.

Pollux has many of the advantages of using a mapping library. It's easy to prototype and create the mappings. It's easy to understand. But it also gives programmers easy access to the low level code.

Pollux is a code generator. There are a lot of other code generators that create code at build time, based on some coded configuration. Pollux is different, in that you use it once to create some well-crafted data access code and then work from there. After the code is created, you the programmer take responsibility for it.

 

Creating Mappings

The Pollux application is stored in a single JAR file. To start things up, type the following at a command line:
   java -jar Pollux.jar

Creating a simple mapping

Suppose we have a SAVINGS_ACCOUNT table that holds the balances of peoples' bank accounts. We'd like to create a Java class called Account to represent each account, and also write a data access object called AccountDAO that will create, retrieve, update, and delete accounts.

Account class

Start up Pollux. You'll see a window with a menu bar, a mapping tree and a blank data area. From the Edit menu, select "Add Class". A new Class item will appear in the mapping tree. Click on this item and the Class dialog will appear in the data area. Fill in the Class. You won't be able to select a primary key yet. That will come later after we define the fields.

Account class dialog

From the Edit menu, select "Add field". A new field will appear under the Account class. Click on it and fill in the id field.

Account class dialog

Now repeat this for the other fields. You can now click on the "bank.Account" item again, and make sure the primary key is set to id. Finally, go to the File menu and select "Save as...". Save your mapping as "account.xml".

Now, let's generate some code. In the Generate menu, select "Generate Record Objects...". You'll be asked to select a directory. Pollux will then save the Account.java class to that directory. Next, from the Generate menu, select "Generate DAOs...". You now have two Java classes:

Account class and DAO

Here's an example of using a DAO to manipulate accounts:

public void doSomething(Connection con)
        throws SQLException
    {
        AccountDAO dao = new AccountDAO(con);
        
        // Create a new account
        Account a1 = new Account();
        a1.setFirstName("Mickey");
        a1.setLastName("Mouse");
        a1.setDateOpened(new Date());
        a1.setBalance(100.0);
        dao.create(a1);
        int id = a1.getId();
         
        // Modify the account
        Account a2 = dao.load(id);
        a2.setBalance(150.0);
        dao.store(a2);
        
        // Delete the account
        dao.remove(a2);
    }
}

Creating a one-to-one table mapping

Mapping one table to one class is simple, and really useful. Most of the mappings I see are nothing more. Container Managed Persistence entity beans only handle single tables. The whole Java Pet Store application only uses DAOs to single tables.

Handling table joins adds more power to your application, and also more headaches. Pollux manages a couple kinds of joined relationships. Consider the following example:

Employee/Department relationship

We have a table of employees and a table of departments. Each employee is in one department and a department can have multiple employees. Suppose we want to retrieve the department information whenever we get a new employee record.

  1. Start up Pollux and create the two classes. Add all the fields. Now go back to the class dialogs and make sure the primary key for the Employee class is set to id and the primary key for Department is set to deptId.
  2. Now select the dept field inside Employee. Change its type to "Object" and its subtype to "Department".
  3. Save the mapping. Generate the record objects and the DAOs. For fun, you can also generate the DDL, which can create the tables in your database. You'll see that a foreign key relationship is defined.
Setting the dept relationship

Now you've got two data classes and two DAO classes. If you use EmployeeDAO to retrieve an Employee record, you'll also get that employee's Department record. Here's an incomplete list of the things you can do:

The DAO's handling of the foreign key isn't too sophisticated. There's a few things you need to watch out for.

   public static final MARKETING_DEPT = 21;
    
    public void moveToMarketing(Connection con, int empId)
        throws SQLException
    {
        DepartmentDAO deptDao = new DepartmentDAO(con);
        Department marketing = deptDao.load(MARKETING_DEPT);
        
        EmployeeDAO empDao = new EmployeeDAO(con);
        Employee emp = empDao.load(empId);
        
        emp.setDept(marketing);
        empDao.store(emp);
    }
}

Creating a one-to-many table mapping

Pollux handles one other type of table join. It allows a record to contain a collection of objects in another table. For instance, suppose we're working with Order objects, each of which contains a list of OrderDetails:

Order class

Create this mapping just like the previous one:

  1. Start up Pollux and create the two classes. Add all the fields. Now go back to the class dialogs and make sure the primary key for the Order class is set to id and the primary key for OrderDetail is set to id.
  2. Now select the details field inside Order. Change its type to "List" and its subtype to "OrderDetail".
  3. Save the mapping. Generate the record objects and the DAOs. Take a look at the DDL again, to see how the foreign keys are set up.
Setting the detail relationship

Whenever you retrieve an Order object, you'll get a list of all the OrderDetail objects associated with it. Actually, the OrderDAO classes is using an OrderDetailDAO object internally to load all the details.

If you want to create, update, or delete OrderDetails, the easiest approach is to create your own OrderDetailDAO and manipulate the detail objects yourself. You can also use the updateByOrderId() method inside OrderDetailDAO which will update an entire list of details. This method takes the the order ID number and a list of details. After it returns, the specified order will have exactly this list of details.

public void addLineItem(Connection con, int orderId, int prodId)
        throws SQLException
    {
        OrderDAO orderDao = new OrderDAO(con);
        OrderDetailDAO detailDao = new OrderDetailDAO(con);
        
        // Retrieve an order
        Order order = orderDao.load(orderId);
        List detailList = order.getDetails();
        
        // Add a new detail to order
        OrderDetail detail = new OrderDetail();
        detail.setProductId(prodId);
        detail.setQuantity(1);
        detail.setPrice(1.00F);
        detailList.add(detail);
        
        // Save detail list
        detailDao.updateByOrderId(order.getId(), detailList);
    }
}

Use caution when using the updateByXXX methods, since they aren't too smart. The current implementation just deletes all the old detail objects and then creates new details based on the list. Future versions of Pollux may a more sophisticated algorithm that inserts, updates, and deletes only where necessary.

Many-to-many table mappings

Pollux doesn't directly support many-to-many mappings, but you can accomplish this kind of relationship by making two one-to-many mappings.

Book and Author classes

Consider a database that tracks authors and books. Each book may have multiple authors and each author may have written more than one book. To represent this in a relational database, we need to create an extra table with two foreign keys. You can build this mapping into Pollux. Both the Author and Book classes will contain a List attribute that contains a collection of BookAuthor objects.

Transactions

Pollux doesn't have any built-in functionality to do transactions. However, if your database supports transactions, you can use the commit/rollback functions built into the database connection. In fact, if you're using a database framework that uses JDBC's commit/rollback as part of its transaction definition, your Pollux DAOs should fit right in.

To use JDBC transactions, you'll need to turn off the auto-commit function on the connection. Here's an example of a transaction:

/**
 * Transfer an amount of money between two accounts.
 * If there is any error, roll back the transaction.
 */
public void transfer(String id1, String id2, float amount, DataSource ds)
throws Exception
{
    Connection con = null;
    try {
        
        con = ds.getConnection();
        con.setAutoCommit(false);
        AccountDAO dao = new AccountDAO(con);
        
        Account account1 = dao.load(id1);
        if (account1 == null)  
            throw new Exception("Account " + id1 + " not found");
        float balance1 = account1.getBalance();
        if (balance1 < amount)
            throw new Exception("Not enough money in " + id1);
        account1.setBalance(balance1 - amount);
        dao.store(account1);
                
        Account account2 = dao.load(id2);
        if (account2 == null)  
            throw new Exception("Account " + id2 + " not found");
        float balance2 = account2.getBalance();
        account2.setBalance(balance2 + amount);
        dao.store(account2);
                    
        con.commit();
                    
    } catch (Exception e)  {
        if (con != null)  con.rollback();
        throw e;
    } finally {
        if (con != null)  con.close();   
    }
}


 

Mapping Details

Editor Dialog Panels

The main Pollux window has two areas: a mapping tree on the left and a main dialog area on the right. Clicking on a tree item fills in the dialog area.

Mapping Dialog

Selecting the main mapping shows this dialog:

Mapping Dialog

You can use the description field to explain what this mapping is for. The description text isn't actually inserted in any of the code.

Class Dialog

Selecting any of the class items displays a class dialog. Notice that the tree shows the fully qualified class name, including the package.

Class Dialog

The class name and table fields are mandatory. You don't have to give a package name, although putting classes into the default package is considered to be a bad practice. The description text will be put into Javadoc comments in the generated code.

In Pollux, every class must have a primary key. If you don't specify a primary key, Pollux usually defaults to the first created field. Every class must have at least one field, or there wouldn't be much point to it.

Field Dialog

Here's the dialog for defining fields:

Field Dialog

The field name, SQL name, and type are mandatory. The subtype choice box is only enabled if your type is List or Object. The description text will be used to generate Javadoc comments.

Data Types

For simple fields, your choice for data types is governed by the pritive types available in Java. So, although your database may have six different types of floating point numbers, Pollux only keeps track of two: float and double. Here's a list of the basic field types and their corresponding SQL types:

Java Type SQL Type Description
String VARCHAR(255) Any textual data
Date TIMESTAMP A date and time. Java dates are measured down to the millisecond.
int INTEGER Number
long BIGINT Number
float FLOAT Floating point number
double DOUBLE PRECISION Floating point number
boolean BIT Usually stored and retrieved as a number. True is 1. False is 0.

This table describes how the DDL is generated. For the DAO code, your limitation is really the JDBC API and whatever automatic casting is built into your driver. The Pollux code may still work with table columns of exotic types. For instance, if you have a column of type DECIMAL(6,2), you may still be able to read and write it as a float.

Some databases don't really match up with ANSI standards. Oracle, for instance, has a DATE type that works the same as DATETIME. Oracle's LONG type holds long text like a CLOB. You can still get your Pollux code to work if you understand all the rules.

Generators

Record Objects

The record classes created by Pollux contain private class attributes for each of the record attributes. There will also be public "getters" and "setters". Pollux will create List and Object attributes to match the one-to-one and one-to-many relationships.

The record classes don't represent any read-only fields in the database. For instance, the primary key on each record probably shouldn't be manipulated, but the generated record classes will let you change the primary key value.

Pollux will also generate an equals() method for each record class.

Data Access Objects (DAOs)

Each DAO will have four basic methods for reading and manipulating a database table: create(), remove(), load(), and store(). It will also have a findAll() method that returns a list of all objects in the table. Also, there will be a constructor that takes a database connection as an argument.

If there are foreign keys into the table, there may also be some find and updateBy methods to accomodate the join relationships.

Notice that each DAO will also have a protected log() method. This method will be called every time an SQL command is executed. By default, this method doesn't do anything, but you can easily fix it to print out all the SQL used by your program. This can be a useful aid to debugging your program.

Data Definition Language (DDL)

The DDL generator creates very generic SQL-92 code to create all the tables for all the classes. It will also create primary and foreign key constraints. It doesn't create any indexes (except those that are automatically created to support the keys). It doesn't create views, generators, or stored procedures.

Struts ActionForms

Struts is a Model View Controller framework for web applications. It reads and writes the values in HTML forms using ActionForm objects.

Pollux generates ActionForm classes to display or input a base Record Class. For instance, if you're working with an Employee class, the EmployeeForm will contain getters and setters for each field in the Employee class. The EmployeeForm will also contain getEmployee() and setEmployee() methods that retrieve or display entire employee records.

The generated ActionForm classes will contain logic for validating the form and formatting values. By default, the validators require that all fields be filled in. You can change this by commenting out the validation logic. The formatting logic allows you to read and display dates properly, and to format numbers. Again, you may need to modify the code to format everything correctly.

 

References