ONJava.com    
 Published on ONJava.com (http://www.onjava.com/)
 See this if you're having trouble printing code examples


Lightweight R/O Mapping

by Norbert Ehreke
12/07/2005

An unwritten consensus in the IT industry is that data shared between object-oriented software and relational databases is best exchanged with object/relational (O/R) mapping frameworks where the entity relationship (ER) model follows the object-oriented model. This article proposes a reversed, lightweight approach supported by a small framework called Amber. This approach uses Java annotations to manage the CRUD cycle (Create Read Update Delete) of JavaBeans. Transaction management is put back into the database, and XML mapping descriptors are replaced by annotations. This article is for intermediate Java developers who are interested in efficient transactions with databases without XML descriptors.

Motivation

Common O/R mapping frameworks are very powerful; however, their design and setup introduces several problems that are rarely discussed. We will address these shortcomings, listed below, demonstrated with a small framework called Amber.

  1. OO-driven data modeling leads to poor entity relationship models.
  2. XML descriptors make maintenance difficult.
  3. Transaction management in the O/R tier is difficult.
  4. The learning curve of existing frameworks is relatively steep.

For exchanging data between models that are described with entity relationships compared to object-oriented models, it necessary to overcome the so-called impedance mismatch. With most O/R mapping tools, the object model rules over the relational model. In essence, this means that the Java persistence layer is responsible for generating the entity relationship model from an existing object model. The idea is compelling, because the promise is that once the business model is designed, the development team no longer needs to worry about the persistence anymore.

Related Reading

Java 5.0 Tiger: A Developer's Notebook
By David Flanagan, Brett McLaughlin

For regular O/R tools, the ER model is a result, a product, at best a container. This clashes with system setups where the business process is actually designed as an ER model. In that case, then, a tuning of the ER model is difficult or even impossible because the O/R framework might reconstruct the ER model at any time. Also, when the business process changes and when the adaptations in the O/R domain are automatically reconstructed in the ER domain, the ER model becomes convoluted and sometimes the performance drops to critical levels.

Another problem exists. The classes that are to be persisted need to be configured with external XML specification (mapping) files. At first glance this seems to be not so bad. But when we're dealing with living systems, this becomes a pain in the neck very quickly. Whenever a change occurs, there is more than just one place to look in order to fix the problem, namely the source code and the mapping files.

Finally, existing O/R frameworks are designed to handle transactions. Following the philosophy of those frameworks, this is absolutely necessary because the storage container (i.e., the relational database) is just that: a stupid container. Having to deal with transaction management, however, is simply not desirable. This is something that belongs in the database.

Introducing Amber

Amber approaches the problem of data exchange from the opposite angle. It assumes that the ER model is the reference for resulting OO structures. It also assumes that the database access is handled mainly via stored procedures, which provide a unified point of access to the database and which are also perfectly set up to handle transactions. Put into a provocative statement: the middle tier is implemented as a set of stored procedures. This means an expert on ER modelling, the DBA, is responsible for design and optimization, including stored procedures, which results in much better structures, and faster and more secure access, compared to automatically created ones. Therefore, a number of rather difficult problems that normally need to be addressed fall away.

Of course, this means that an entire playing field, which is normally located in the Java domain, is taken away. Make no mistake. That is a huge gain for Java developers, not a loss.

Mapping

Central to Amber is the idea that no matter how a query is submitted to a database, the resulting tabular data is, in essence, a list of Java objects. Rather, that is the way it should be treated from the perspective of the Java developer. The only problem is to map the columns to the properties of an object. Conversely, when writing to a database, the properties of a Java object need to be mapped to the parameters in the call.

Amber maps the rows of a result set to a JavaBean and uses the same mechanism to map the bean, rather the contents of the bean, back to the parameters of an update, insert, or delete call to the database. For more information about JavaBeans and their definition, please check the Resources section.

This is done using a new Java language feature called annotations, available since J2SE 5.0.

Annotations, also called "metadata" under JSR 175, are supplementary code parts that can provide more information about the aim of a method, class, or field. The motivation for metadata stems mainly from the Javadoc API, which is used for inline documentation. So, without interfering with the actual code, annotations are used to describe the context of code, how it can or should be used. If you like to know more about annotations and what can be done with them, see Tiger: A Developer's Notebook, or, for a more playful example, my article " Annotations to the Rescue."

Step by Step

Let's tackle a little persistence problem. To read a list of Jedi objects from the database, we assume that the result set that is delivered looks like the table below. Please note that we are not going to rely on tables in the following discussion, even though the examples are actually based on tables. In general, we're just expecting tabular data that might have been put together using several SQL joins and spanning more than just one table or view. (Also, apologies to all Star Wars fans for any inconsistencies.)


jedi_id  name             force_rating  age  alive 
-------- ---------------- ------------- ---- ----- 
1000     Obi Wan Kenobi   9.40          30   0
1001     Luke Skywalker   7.20          19   1
1002     Yoda             10.00         912  1

Let's define a simple class called Jedi.


public class Jedi {

   private Integer _id;
   private String _name;
   private Double _forceRating;
   private Integer _age;
   private Boolean _alive;

   @ColumnAssociation(name="jedi_id")
   public void setId( Integer id ) {
      _id = id;
   }
   @ColumnAssociation(name="name")
   public void setName( String name ) {
      _name = name;
   }
   @ColumnAssociation(name="force_rating")
   public void setForceRating( Double fr ) {
      _forceRating = fr;
   }
   @ColumnAssociation(name="age")
   public void setAge( Integer age ) {
      _age = age;
   }
   @ColumnAssociation(name="alive")
   public void setAlive( Boolean alive ) {
      _alive = alive;
   }

   @ParameterAssociation(name="@jedi_id", 
   index=0, isAutoIdentity=true)
   public Integer getId() {
      return _id;
   }
   @ParameterAssociation(name="@name", index=1)
   public String getName() {
      return _name;
   }
   @ParameterAssociation(name="@force_rating", 
   index=2)
   public Double getForceRating() {
      return _forceRating;
   }
   @ParameterAssociation(name="@age", index=3)
   public Integer getAge() {
      return _age;
   }
   @ParameterAssociation(name="@alive", index=4)
   public Boolean getAlive() {
      return _alive;
   }
}

What happened here? You see two kinds of annotations above the getter and setter methods of that class.

The annotation @ColumnAssociation is used to connect the setter methods of the JavaBean to a column from the result set, so that the tabular data from the database can be written to the bean properties by Amber. The annotation @ColumnAssociation applies to the setter methods only, because Amber uses these annotations to find and call those methods with the corresponding values after reading the data from the database.

Accordingly, the getter methods are equipped with @ParameterAssociation annotations to connect the properties of the JavaBean to the parameters in an update, delete, or insert call. This annotation applies to the getter methods only, because Amber uses the getter methods to retrieve the values to fill in the parameters. Because of JDBC, it is necessary to index the parameters. This can be redundant, depending on the underlying database and whether stored procedures are used or not, but for completeness and to follow the JDBC API they need to be provided. The annotation property isAutoIdentity will be discussed when we get to writing data into the database.

It is necessary to provide a no-argument constructor in order for objects of this class to be constructed automatically (via reflection). In the class above, there is a no-argument constructor available, since we did not provide any other, but we need to be careful when we add additional constructors to the class, because then we would need to provide one explicitly for Amber.

The result is a JavaBean that shows exactly from where in the database its content is taken and to where it is written. There is no need for an external specification file. Note that we could actually set up any class in this fashion, not just JavaBeans.

You may wonder: why use annotations? Why not use an implicit association via the property names, since we are dealing with JavaBeans anyway? This is done in order to retain one degree of freedom in our design. In other words, we Java guys do not want to be dependent on how the ER model designers name their table columns. If you are used to working with tables, you might not agree with this, but when you are using stored procedures, you have to deal with joined tables and views that sometimes have to use distinguished names.

Amber's Connectors and JDBC

Before we go ahead with reading and writing, we need to establish a connection to the database. Amber uses a Connector to access the database. In short, this is the combination of a database driver and a corresponding connection URL. A ConnectorFactory is used to manage the available connections in an application. To initialize a simple connection to an SQL server using a native type-4 driver, one would use the following code. We assume the server name to be localhost, the database name to be jedi, the login to be use, and finally, the password to be theforce. For brevity, I will omit all exception-handling in the code below.


String driverClassName = 
   "com.microsoft.jdbc.sqlserver.SQLServerDriver";
String url = 
   "jdbc:microsoft:sqlserver://" + 
   "localhost;databasename=jedi;" + 
   "user=use;pwd=theforce";

Amber's Connector is associated with a String, alias under which it remains accessible from the ConnectorFactory. Here, we're going to use the alias starwars.


ConnectorFactory.getInstance().add( 
   "starwars", driverClassName, url 
);
Since a Connector is a lightweight wrapper around a JDBC connection, we can, in general, do anything with it that we might originally be doing with such a connection.

Reading

Wrapped around such a Connector is a BeanReader object, which takes a Connector and a Class that tells the reader what type of bean should be read from the database. The problem of reading a list of Jedi objects is then reduced to the following lines.

Connector connector = 
   ConnectorFactory.createConnector( "starwars" );
BeanReader reader = 
   new BeanReader( Jedi.class, connector );
Collection<Jedi> jediList = 
   reader.executeCreateBeanList( 
      "select * from jedi" 
   );

This code employs a new Java language feature called generics that is available as of J2SE 5.0. In the line that declares the Collection, the syntax says that the Collection called jediList consists uniformly of objects that are of the type Jedi. The compiler will issue a warning here, since the reader only knows at runtime what kind of class it is going to create. Because of type erasure in the generics implementation of J2SE 5.0, it is not possible to safely cast the result. And, sadly, it is also not possible to write the BeanReader class as BeanReader<Jedi> for the same reason. In short, using Java reflection and generics don't mix.

What about compound data structures? Well, there are several possible approaches to this. Say, we have a 1:n relationship between the Jedi above and a Fighter (i.e., each Jedi owns a number of fighter spacecraft). The Fighter class is described in the database with the following data.


fighter_id  jedi_id  name            firepower_rating   turbo_laser_equipped 
----------- -------- --------------- ------------------ -------------------- 
1           1001     X-Wing          2.50               0
2           1001     B-Wing          .00                0
3           1002     Star Destroyer  23.50              1

In other words, Luke owns two Fighters (an X- and a B-Wing) and Yoda owns a Star Destroyer, while Obi Wan is dead--sort of, anyway.

Again, this relationship between the data can be modeled in the OO domain in several ways. We'll just pick one that seems most obvious and skip the others. So we'll want the Jedi class to have a member that is a collection of Fighter objects. Here is the class Fighter set up for use with Amber.


public class Fighter {

   private Integer _id;
   private Integer _jediId;
   private String _name;
   private Double _firepowerRating;
   private Boolean _turboLaserEquipped;

   @ColumnAssociation(name="fighter_id")
   public void setId( Integer id ) {
      _id = id;
   }
   @ColumnAssociation(name="jedi_id")
   public void setJediId( Integer jediId ) {
      _jediId = jediId;
   }
   @ColumnAssociation(name="name")
   public void setName( String name ) {
      _name = name;
   }
   @ColumnAssociation(name="firepower_rating")
   public void setFirepowerRating( Double firepowerRating ) {
      _firepowerRating = firepowerRating;
   }
   @ColumnAssociation(name="turbo_laser_equipped")
   public void setTurboLaserEquipped( 
      Boolean turboLaserEquipped ) {
      _turboLaserEquipped = turboLaserEquipped;
   }
   @ParameterAssociation(name="@fighter_id",
      index=0,isAutoIdentity=true)
   public Integer getId() {
      return _id;
   }
   @ParameterAssociation(name="@jedi_id",index=1)
   public Integer getJediId() {
      return _jediId;
   }
   @ParameterAssociation(name="@name",index=2)
   public String getName() {
      return _name;
   }
   @ParameterAssociation(name="@firepower_rating",
      index=3)
   public Double getFirepowerRating() {
      return _firepowerRating;
   }
   @ParameterAssociation(name="@turbo_laser_equipped",
      index=4)
   public Boolean getTurboLaserEquipped() {
      return _turboLaserEquipped;
   }
}

Here is the enhanced Jedi class. It just has an additional member of the type List<Fighter>. Again, this is J2SE 5.0 code that states that the list contains only objects of the type Fighter. Added code appears bold.


public class Jedi {

   private Integer _id;
   private String _name;
   private Double _forceRating;
   private Integer _age;
   private Boolean _alive;
   
   private ArrayList<Fighter> _fighterList = 
      new ArrayList<Fighter>();
   
   @ColumnAssociation(name="jedi_id")
   public void setId( Integer id ) {
      _id = id;
   }
   @ColumnAssociation(name="name")
   public void setName( String name ) {
      _name = name;
   }
   @ColumnAssociation(name="force_rating")
   public void setForceRating( Double forceRating ) {
      _forceRating = forceRating;
   }
   @ColumnAssociation(name="age")
   public void setAge( Integer age ) {
      _age = age;
   }
   @ColumnAssociation(name="alive")
   public void setAlive( Boolean alive ) {
      _alive = alive;
   }

   @ParameterAssociation(name="@jedi_id", 
      index=0, isAutoIdentity=true)
   public Integer getId() {
      return _id;
   }
   @ParameterAssociation(name="@name", index=1)
   public String getName() {
      return _name;
   }
   @ParameterAssociation(name="@force_rating", 
      index=2)
   public Double getForceRating() {
      return _forceRating;
   }
   @ParameterAssociation(name="@age", index=3)
   public Integer getAge() {
      return _age;
   }
   @ParameterAssociation(name="@alive", index=4)
   public Boolean getAlive() {
      return _alive;
   }
   public ArrayList<Fighter> getFighterList() {
      return _fighterList;
   }
   public void setFighterList( ArrayList<Fighter> fighterList ) {
      _fighterList = fighterList;
   }
}

The code that reads the available Jedis from the database now looks like this:


Connector connector = 
   ConnectorFactory.getInstance().createConnector( "starwars" );
BeanReader jediReader = 
   new BeanReader( Jedi.class, connector );
BeanReader fighterReader = 
   new BeanReader( Fighter.class, connector );
Collection<Jedi> jediList = 
   reader.executeCreateBeanList( "select * from jedi" );
for( Jedi jedi : jediList ) {
   String query = 
      "select * from fighter where jedi_id = " + jedi.getId();
   Collection<Fighter> fighters = 
      fighterReader.executeCreateBeanList( query );
   jedi.setFighterList( 
      new ArrayList<Fighter>( fighters ) );
}

Et voila, there you have all of the Jedi with a collection of their respective fighters. Note that we have not coded the reading of the Fighter collection into the Jedi class. This would mean tight coupling between the Jedi and the Fighter. You could say that the code above is the assembler part in a dependency injection pattern. Big words, I know. It just means this: Do things that depend on each other separately, and then put them together in separate code. If you want to read a very good summary on this topic see Martin Fowler's "Inversion of Control Containers and the Dependency Injection pattern."

Writing

Now, on to writing. The writing of an altered Jedi to the database is done with the following lines of code.


Connector connector = 
   ConnectorFactory.getInstance().createConnector( "starwars" );
BeanWriter writer = 
   new BeanWriter( Jedi.class, connector );
writer.executeStringUpdate( 
   sampleBean, "UpdateJedi" );

Here, the database access is made via a generated SQL query string. For the Jedi with the id of 1000 (which is Obi Wan), this last line of code produces the following execution string and sends it to the database (assuming we've changed the property alive to true and forceRating to 6.0).


UpdateJedi 
   @name='Obi Wan Kenobi', @jedi_id=1000, 
   @alive=1, @force_rating=6.0, @age=30

If we want to create a new Jedi, we would simply construct a new Jedi and write it with the following code.


Jedi newJedi = new Jedi();
newJedi.setName( "Mace Windu");
newJedi.setAge( 40 );
newJedi.setAlive( false );
newJedi.setForceRating( 9.7 );
Connector connector = 
    ConnectorFactory.getInstance().createConnector( "starwars" );
BeanWriter writer = 
    new BeanWriter( Jedi.class, connector );
writer.executeStringInsert( 
    newJedi, "InsertJedi" );

You'll notice that we are using a different method and a different stored procedure to write the data. The resulting string looks like this.


InsertJedi 
   @name='Mace Windu', @alive=0, 
   @force_rating=9.7, @age=40

What happened here? We'll assumed that the property jediId is provided by the database when a new entry is made. Actually, we've specified this when we used the @ParameterAssociation with the property isAutoIdentity=true in the Jedi class definition above. Since the database will provide the bean with a primary key, the parameter @jedi_id is omitted in the call string.

There is a catch here. Since jediId is provided by the database, this data must be returned from the stored procedure InsertJedi. At the moment, the method executeStringInsert returns a JDBC ResultSet, which can be used to return either the ID or the entire database row that was just inserted. This information has to be handled manually, but Amber will soon provide helper functionality to easily inject the new ID into the new object.

Dealing with strings being used for reading and writing is questionable when the focus lies more on type safety compared to transparency. Because of the conversion of the parameters to strings, type information is lost. However, there is one big advantage with this technique: any query string can be logged and given to database administrators for analysis in case anything goes wrong, or simply when there is a question on what exactly the application called or queried the database with. This sort of transparency makes debugging easier.

If the list of fighters of the Jedi has been altered, this has to be written manually into the database as well. Depending on what happened to the Fighter list, the crude way might be to delete all of the fighters of the Jedi first, and then write the new list back into the database. Assuming we have the object jedi at hand and a list of new Fighter objects in our collection of fighters, the following takes care of writing the new list into the database. Further, we assume that a new Fighter object is written into the database via the stored procedure InsertFighter.


Connector connector = 
   ConnectorFactory.createConnector( "starwars" );
BeanWriter writer = 
   new BeanWriter( Fighter.class, connector );
connector.execute( 
   "delete from fighters where jedi_id = " + jedi.getId() );
for( Fighter fighter : fighters ) {
   fighter.setJediId( jedi.getId() );
   connector.executeStringInsert( 
      fighter, "InsertFighter" )
}

This code produces a set of execution strings like this, where the name in each item is the value of the fighter item in the collection fighters:


InsertFighter @jediId=..., @name="...";

You may have noticed that this approach is not wrapped in a transaction. As mentioned above, there is no exception handling implemented, so if the delete command fails, an SQLException is raised and the subsequent for loop is not executed. But what about the other case, when the following InsertFighter call is going wrong? In a situation where it is necessary to provide a transaction, it is best to code this within the stored procedure that you are calling. If I wanted to handle this within a transaction, what I'd do is code an update stored procedure that takes all parameters from the Fighter object plus the Jedi ID and handles "new" fighters within. This topic might be worth discussing in another article.

Limitations and Drawbacks

As with any tool or technique, the approach we've discussed has limitations.

Conclusion

This article has demonstrated a reversed R/O mapping approach compared to conventional O/R mapping. The complexity of the mapping task, the so-called impedance mismatch between object-oriented and relational systems, has been reduced by defining the relational data model as the reference model for the object domain and by the utilization of stored procedures especially for task of writing objects into a RDBMS (Relational Database Management System). The mapping is implemented by annotations, a language feature of Java 1.5. This approach is supported and demonstrated by a framework called Amber.

Amber is a small framework, easy to learn and easy to use. There are just a couple of classes to be handled and they are very close to JDBC. The interfacing between database and JavaBean classes is accomplished via annotations. There is no need for XML descriptors anymore. Since XML is not easily readable by humans, this is a plus. That also means the mapping description between database and application lies in one single location, the bean class. Amber also provides a constraint checking mechanism to allow content validation, which is not discussed here to keep this article short.

Amber does one thing and it does it well: map database columns and query parameters to JavaBean properties. No more, no less. Amber is no silver bullet, and it does not solve the problems that are tackled by large, industry O/R frameworks.

Amber has proven its worth in a business environment. At Impetus, we've developed a sales force solution for one of Germany's largest mail order companies, in Java using MS SQL Server, that handles all database interaction with Amber. We've had no change in the API since its inception this spring (with the coming of J2SE 5.0) and there were no significant problems in using it.

Resources

Norbert Ehreke is a consultant and software developer currently affiliated as a senior development lead with Impetus Unternehmensberatung GmbH in Frankfurt, Germany.


Return to ONJava.com.

Copyright © 2009 O'Reilly Media, Inc.