Providing a Complete Data Persistence Solution

Mapping your Java-based data to a relational database, such as SQL Server 2000, presents many problems difficult to resolve. For example, the SQL concept of a variable width, single-byte character array is the VARCHAR data type. The Java programming language does not contain an equivalent data type. The closest Java type is the String class.

Most database engines internally support their own data types and convert them to a SQL data type. A perfect example is the way Oracle maps its numeric types to SQL’s NUMERIC type. Fortunately, the JDBC specification allows you to call the ResultSet.getLong() method to retrieve numbers as Java Longs. The following table displays the JDBC Specification SQL to Java datatype mappings.

Table 1 JDBC Specification SQL to Java datatype mappings 

Object-to-Relational mapping is complex for a number of reasons:

• A mismatch exists between procedural Java and declarative SQL.
  
• Objects can have one-to-many and many-to-many associations with other objects. Unfortunately, relational schema normalization doesn’t allow a column to have multiple values.
  
• Relational schemas do not support inheritance.
  
• Object models do not support transaction semantics.

How do you solve these complexities? Fortunately, the JDBC APIs
come to the rescue by assisting in mapping data from the object
model to a relational database.

A Mapping Architectural Overview

Mapping data requires using a series of primary classes to map data. Let’s put together a Java Mapper series of interfaces that encapsulate common mapping behavior required to create, update, delete, and read from a relational database. The interfaces presented here conform to the Model, View, and Controller (MVC) design pattern so widely adopted in most distributed enterprise applications today. Note that the Java Mapper interface is independent from both the domain model and persistence technologies, as should be the case with all data mappings. The JDBCMapper interface represents an abstract implementation of the JDBC interface.

Examples

Let’s extract some methods from the JDBC Mapper Interfaces and see how they work.

Below are two examples demonstrating how you could implement a Mapper method.

public void update (DomainObject domainObject)
throws NoSuchObjectException {
  Connection cn = null;
  try { 
      cn = getConnection();
      updateImpl(cn,domainObject);
  }catch (Exception e) { 
      System.out.println(“Exception “ + e + “caught in update()”);
  throw new NoSuchObjectException(“Wrapped
Exception“ + e + “caught in update()”);
  }finally { close(cn);
    }
}
protected abstract void updateImpl(Connection cn, DomainObject 
                                   anObject;
    throws SQLException, MappingException;

We have presented two methods, public void update(), and protected abstract void updateImpl(). The first method implements the JDBC-specific interface by managing connection-management and JDBC exception handling. The second method calls the abstract method updateImpl, a representation of the domain model object-related behavior. The domain object-specific mapper must implement the abstract Impl methods in order to create, update, delete, and read instances of the object in the relational database. You must call the abstract activate method in order for it to be implemented by domain object-specific subclasses. The RegistrantMapper doActivate() and passivate() methods execute the mappings to and from the results, reading and/or updating the relational database.

Here are examples of both of the doActivate and passivate methods.

protected void doActivate(DomainObject <insert
variable here>,ResultSet rs)
  throws SQLException, MappingException {
  Registrant r = (Registrant)domainObject;
  r.setName(rs.getString(2));
  r.setAddress(new address (r));
  r.getAddress().setStreet (rs.getString(3));
  r.getAddress().setCity(rs.getString(4));
…

}
protected void passivate (DomainObject <insert
variable here>, PreparedStatement ps) 
throws SqlException, Mapping Exception { 
Registrant r = (Registrant) domainObject; 
ps.setString(1, r.getId()); 
ps.setString(2.r.getName()); 
if (r.getAddress() != null){
    ps.setString(3, r.getAddress() 
.setStreet());
ps.setString(4, r.getAddress().getCity());
    }
}

Also notice how the RegistrantMapper interface provides the necessary SQL statements to execute the abstract Impl() methods such as read, create, update, and delete. For example, the createImpl method creates a database row and maps the registrant department attributes to the columns in the expression that match the primary key of the table.

The other mapper interfaces, including MapperRegistry, DomainObject, and UnitOfWork, are interfaces provided by Martin Fowler in his Patterns of Enterprise Application Architecture, published by Addison-Wesley. The MapperRegistry interface provides access to specific mappers being used for each unique object residing in the domain model. The UnitOfWork interface maintains a list of objects affected by a business transaction and coordinates the writing of changes.

Figure 1 JDBC interfaces that facilitate mapping from Java to a relational database.

Mapping Tool Types

Mapping tools fall into two distinct categories: (1) developmental tools and (2) plug-and-play tools. JNBridge LLC provides a developmental tool called JNBridge Pro. It provides bi-directional connectivity to and from Java and .NET. Another tool, called JSQLMapper from JNetDirect, offers plug-and-play connectivity to SQL Server 2000 from Java and vice versa. JSQLMapper is the tool we will now focus on.

Currently, three approaches exist for calling Java code from .NET and vice versa: (1) socket communication, (2) XML and SOAP, and (3) bridging technologies. SOAP is an XML-oriented approach that supports schemas such as XSD and Relax NG schemas. Schemas validate XML document instances by providing document structure, enforcing business model rules, and facilitating the creation of user-defined data types when needed. Additionally, JSQLMapper achieves bi-directional mappings through support for Remote Method Invocation calls.

JNetDirect provides a well-written tutorial that leads users through the steps of mapping data to a relational database such as SQL Server 2000. Let’s walk through the tutorial. Be sure to start the JSQLMapper GUI in tutorial mode by issuing the following command:

JSQLMapper_Tutorial

JSQLMapper’s wizard allows you to create a new mapping or load a pre-existing mapping. The second wizard page connects to a database provided especially for the tutorial. The drop-down list provides a list of drivers from which you choose the driver appropriate for your object-to-relational database mapping:

           • JNetDirect             • DB2
           • PostgreSQL             • MySQL (Connector/J) 
           • Oracle                 • Custom 
           • Microsoft SQL Server   • Tutorial

Once you have made your choice, the connection to the tutorial database is established. After providing a user ID and password (sa), a list of tables residing within the database is displayed so you can select and add tables via a user-friendly GUI. Typically, because the tables exist in hierarchical format, the first table added is referred to as the Root table. Subsequently, all tables added after the root table are considered child tables of the root, thereby setting up a parent/child relationship. This makes it easy to set up your primary key/foreign relationships.

The tutorial lists four tables for your convenience: Customer, Invoice, Item, Product.

JSQLMapper requires each database row to contain a globally unique ID (GUID). The wizard allows you to select columns that comprise the table’s primary key. The wizard screen is divided in two panes. The left pane displays all fields residing in a table, whereas the right pane represents key fields selected by the user.

The next wizard allows you to select a key generator. For example, when JSQLMapper inserts new rows into the table, it utilizes the key generator to generate a key value for the new row. JSQLMapper provides a list of key generators that represent the type of identity key associated with a specific relational database. For example, DB2’s identity key generator differs from Microsoft’s identity key generator.

Once the key is generated, you need to define how the table is related to its parent. You accomplish this by identifying the primary key/foreign key relationship. In the event JSQLMapper is unable to determine the key relationship, you can do it manually. It is also possible to define a compound key as mandated by dataset rules.

Once you finish adding tables and establishing relational key mappings, JSQLMapper allows you to test the mapping without writing any code. If everything is properly defined, a new wizard window allows you to restrict your test dataset using a standard SQL WHERE clause. Enter where id = 0 to restrict your dataset to the first table, in this case Invoice. Finally, select the "read" button to have your dataset printed in XML format. This verifies that JNetDirect provides support for the Relax NG Schema as a core component of their technology.

Writing XML, Reading from Java

JSQLMapper also provides the capability for writing XML to a database from the GUI as well as reading data from a Java program. A single class called JSQLMapper reads, writes, and deletes database data. Before you use JSQLMapper, you must import the following libraries and place them in your classpath: 

• sqlmapper.jar from the "lib" directory located in the install directory. 
  
• castor-0.9.3.21.jar
  
• hsqldb.jar
  
• xercesImpl.jar
  
• xmlParserAPIs.jar

The following example demonstrates how to read from a file:

package com.jnetdirect.sqlmapper.tutorial;
import java.io.*;
import java.sql.*;

//import the JSQLMapper classes
import com.jnetdirect.sqlmapper.*;

public class ReadTutorial
{
  public ReadTutorial()
    {
    }
  public static void main(String[] args) Throws
Exception
  {
    class.forName(“org.hsqldb.jdbcDriver”);
    Connection cn = DriverManager.getConnection
      (“jdbc:jsqldb:hsql://localhost”, “sa”, ““);
  //create an instance of the JSQLMapper class
    JSQLMapper sqlmapper = new
      JSQLMapper(new FileInputStream(“invoice. map”); 
    sqlmapper.read(cn, new FileOutputStream (“invoices.xml”)); 
    cn.close();
    }
}

Hopefully, this article will provide some stimulus for reaching out and leveraging JNetDirect’s JSQLMapper’s technology. JSQLMapper is fully J2EE compliant and is an invaluable third party business solution for bridging between Java’s J2EE specifications and .NET’s. exciting new technology. Happy computing!

References

• Patterns of Enterprise Application Architecture, Martin Fowler, 2003, Pearson Education
• Design Patterns, Gamma, 1995, Addison Wesley Longman
• The Unified Modeling Language User Guide, Booch, Rumbough, Jacobson, 1999, Addison Wesley
• .NET & J2EE Interoperability, Dwight Peltzer, 2004, McGraw-Hill/Osborne
• Database Programming with JDBC and Java, Reese, 2000, O’Reilly
• JNetDirect JSQLMapper API and Documentation, 2004, Doug Crane/Burke Cox
• Java 2 Enterprise Edition (J2EE) Specification 1.4
• JDBC Specification 3.0

(This article appeared, in a slightly altered form, in the May 2004 issue of Plug in).