Chapter 6: Object/relational mapping

Object relational mapping allows you to declare in which tables and columns of your database the entity data will be stored.
Object/relational tools allow you to work with objects instead of tables and columns, and to generate automatically the SQL code to read and update the database. In this way you do not need direct access to the SQL database. Of course you have to define precisely how to map your classes to your tables, and this work is done using JPA mapping annotations.
The OpenXava entities are JPA entities, therefore the object/relational mapping in OpenXava is done by means of the Java Persistence API (JPA). This chapter shows the more basic mapping techniques and some special cases. If you want to learn more about JPA you can look at the documentation of Hibernate Annotations (the JPA implementation used by OpenXava by default), or whatever JPA manual you want.

Entity mapping

The @Table annotation specifies the primary table for the annotated entity. Additional tables may be specified using @SecondaryTable or @SecondaryTables annotation.
If no @Table annotation is specified for an entity class, the default values apply.
Example:

@Entity
@Table(name="CUST", schema="XAVATEST")
public class Customer {
 

Property mapping

The @Column annotation is used to specify a mapped column for a persistent property or field. If no @Column annotation is specified, the default values are applied.
A simple example:

@Column(name="DESC", length=512)
private String description;
 

An example annotating the getter:

@Column(name="DESC", nullable=false, length=512)
public String getDescription() { return description; }
 

Other examples:

@Column(name="DESC",
    columnDefinition="CLOB NOT NULL",
    table="EMP_DETAIL")
@Lob
private String description;
 
@Column(name="ORDER_COST", updatable=false, precision=12, scale=2)
private BigDecimal cost;
 

Reference mapping

The @JoinColumn annotation is used to specify a mapped column for a reference.
Example:

@ManyToOne
@JoinColumn(name="CUST_ID")
private Customer customer;

If you need to define a mapping for the composite foreign keys use @JoinColumns. This annotation groups @JoinColumn annotations for the same reference.
When the @JoinColumns annotation is used, both the name and the referencedColumnName elements must be specified in each such @JoinColumn annotation.
Example:

@ManyToOne
@JoinColumns({
    @JoinColumn(name="INV_YEAR", referencedColumnName="YEAR"),
    @JoinColumn(name="INV_NUMBER", referencedColumnName="NUMBER")
})
private Invoice invoice;

Collection mapping

When you use @OneToMany for a collection the mapping depends of the reference used in the other part of the association, that is, usually it's not needed to do anything. But if you are using @ManyToMany, maybe it's useful to declare the @JoinTable, as following:

@ManyToMany
@JoinTable(name="CUSTOMER_STATE",
    joinColumns=@JoinColumn(name="CUSTOMER"),
    inverseJoinColumns=@JoinColumn(name="STATE")
)
private Collection<State> states;
 

If @JoinTable is missing the default values apply.

Embedded reference mapping

An embedded reference contains data that in the relational model are stored in the same table as the main entity. For example, if you have an embeddable Address associated to a Customer, the address data is stored in the same data table as the customer data. How can you map this case with JPA?
Just using @AttributeOverrides annotations, in this way:

@Embedded
@AttributeOverrides({
    @AttributeOverride(name="street", column=@Column("ADDR_STREET")),
    @AttributeOverride(name="zip", column=@Column("ADDR_ZIP"))
    @AttributeOverride(name="city", column=@Column("ADDR_CITY")),
    @AttributeOverride(name="country", column=@Column("ADDR_COUNTRY"))
})
private Address address;

If you do not use @AttributeOverrides default values are assumed.

Type conversion

The type conversion between Java and the relational database is a work for the JPA implementation (OpenXava uses Hibernate by default). Usually, the default type conversion is good for most cases, but if you work with legate database perhaps you need to use the tips here.
Given that OpenXava uses the type conversion facility provided by Hibernate you can learn more on Hibernate documentation.

Property conversion

When the type of a Java property and the type of its corresponding column in DB do not match you need to write a Hibernate Type in order to do your custom type conversion.
For example, if you have a property of type String [], and you want to store its value concatenating it in a single table column of VARCHAR type. Then you must declare the conversion for your property in this way:

@Type(type="org.openxava.test.types.RegionsType")
private String [] regions;

The conversion logic in RegionsType is:

package org.openxava.test.types;
 
import java.io.*;
import java.sql.*;
 
import org.apache.commons.logging.*;
import org.hibernate.*;
import org.hibernate.usertype.*;
import org.openxava.util.*;
 
/**
 *
 * @author Javier Paniza
 */
 
public class RegionsType implements UserType {  // 1
 
    public int[] sqlTypes() {
        return new int[] { Types.VARCHAR };
    }
 
    public Class returnedClass() {
        return String[].class;
    }
 
    public boolean equals(Object obj1, Object obj2) throws HibernateException {
        return Is.equal(obj1, obj2);
    }
 
    public int hashCode(Object obj) throws HibernateException {
        return obj.hashCode();
    }
 
    public Object nullSafeGet(ResultSet resultSet, String[] names, Object owner)  // 2
        throws HibernateException, SQLException
    {
        Object o = resultSet.getObject(names[0]);
           if (o == null) return new String[0];
           String dbValue = (String) o;
           String [] javaValue = new String [dbValue.length()];
           for (int i = 0; i < javaValue.length; i++) {
               javaValue[i] = String.valueOf(dbValue.charAt(i));
           }
           return javaValue;
    }
 
    public void nullSafeSet(PreparedStatement ps, Object value, int index)  // 3
        throws HibernateException, SQLException
    {
        if (value == null) {
            ps.setString(index, "");
            return;
        }
        String [] javaValue = (String []) value;
        StringBuffer dbValue = new StringBuffer();
        for (int i = 0; i < javaValue.length; i++) {
            dbValue.append(javaValue[i]);
        }
        ps.setString(index, dbValue.toString());
    }
 
    public Object deepCopy(Object obj) throws HibernateException {
        return obj == null?null:((String []) obj).clone();
    }
 
    public boolean isMutable() {
        return true;
    }
 
    public Serializable disassemble(Object obj) throws HibernateException {
        return (Serializable) obj;
    }
 
    public Object assemble(Serializable cached, Object owner) throws HibernateException {
        return cached;
    }
 
    public Object replace(Object original, Object target, Object owner) throws HibernateException {
        return original;
    }
 
}
 

The type converter has to implement org.hibernate.usertype.UserType (1). The main methods are nullSafeGet (2) for read from database and to convert to Java, and nullSafeSet (3) for writing the Java value into database.
OpenXava has generic Hibernate type converters in the org.openxava.types package ready to use. One of them is EnumLetterType, that allows to map properties of enum type. For example, if you have a property like this:

private Distance distance;
public enum Distance { LOCAL, NATIONAL, INTERNATIONAL };
 

In this Java property 'LOCAL' is 1, 'NATIONAL' is 2 and 'INTERNATIONAL' is 3 when the property is stored in database. But what happens, if in the database a single letter ('L', 'N' or 'I') is stored? In this case you can use EnumLetterType in this way:

@Type(type="org.openxava.types.EnumLetterType",
    parameters={
        @Parameter(name="letters", value="LNI"),
        @Parameter(name="enumType", value="org.openxava.test.model.Delivery$Distance")
    }
)
private Distance distance;
public enum Distance { LOCAL, NATIONAL, INTERNATIONAL };

As you put 'LNI' as a value to letters, the type converter matches the 'L' to 1, the 'N' to 2 and the 'I' to 3. You also see how type converters are configurable using its properties and this makes the converters more reusable.

Multiple column conversion

With CompositeUserType you can map several table columns to a single Java property. This is useful if you have properties of custom class that have itself several attributes to store. Also it is used when you have to deal with legate database schemes.
A typical example is the generic converter Date3Type, that allows to store in the database 3 columns and in Java a single property of type java.util.Date.

@Type(type="org.openxava.types.Date3Type")
@Columns(columns = {
    @Column(name="YEARDELIVERY"),
    @Column(name="MONTHDELIVERY"),
    @Column(name="DAYDELIVERY")
})
private java.util.Date deliveryDate;
 

DAYDELIVERY, MONTHDELIVERY and YEARDELIVERY are 3 columns in database that store the delivery date. And here Date3Type:

package org.openxava.types;
 
import java.io.*;
import java.sql.*;
 
import org.hibernate.*;
import org.hibernate.engine.*;
import org.hibernate.type.*;
import org.hibernate.usertype.*;
import org.openxava.util.*;
 
/**
 * In java a <tt>java.util.Date</tt> and in database 3 columns of
 * integer type. <p>
 *
 * @author Javier Paniza
 */
 
public class Date3Type implements CompositeUserType {  // 1
 
    public String[] getPropertyNames() {
        return new String[] { "year", "month", "day" };
    }
 
    public Type[] getPropertyTypes() {
        return new Type[] { Hibernate.INTEGER, Hibernate.INTEGER, Hibernate.INTEGER };
    }
 
    public Object getPropertyValue(Object component, int property) throws HibernateException {  // 2
        java.util.Date date = (java.util.Date) component;
        switch (property) {
            case 0:
                return Dates.getYear(date);
            case 1:
                return Dates.getMonth(date);
            case 2:
                return Dates.getYear(date);
        }
        throw new HibernateException(XavaResources.getString("date3_type_only_3_properties"));
    }
 
    public void setPropertyValue(Object component, int property, Object value)
        throws HibernateException    // 3
    {
        java.util.Date date = (java.util.Date) component;
        int intValue = value == null?0:((Number) value).intValue();
        switch (property) {
            case 0:
                Dates.setYear(date, intValue);
            case 1:
                Dates.setMonth(date, intValue);
            case 2:
                Dates.setYear(date, intValue);
        }
        throw new HibernateException(XavaResources.getString("date3_type_only_3_properties"));
    }
 
    public Class returnedClass() {
        return java.util.Date.class;
    }
 
    public boolean equals(Object x, Object y) throws HibernateException {
        if (x==y) return true;
        if (x==null || y==null) return false;
        return !Dates.isDifferentDay((java.util.Date) x, (java.util.Date) y);
    }
 
    public int hashCode(Object x) throws HibernateException {
        return x.hashCode();
    }
 
    public Object nullSafeGet(ResultSet rs, String[] names, SessionImplementor session, Object owner)
        throws HibernateException, SQLException  // 4
    {
        Number year = (Number) Hibernate.INTEGER.nullSafeGet( rs, names[0] );
        Number month = (Number) Hibernate.INTEGER.nullSafeGet( rs, names[1] );
        Number day = (Number) Hibernate.INTEGER.nullSafeGet( rs, names[2] );
 
        int iyear = year == null?0:year.intValue();
        int imonth = month == null?0:month.intValue();
        int iday = day == null?0:day.intValue();
 
        return Dates.create(iday, imonth, iyear);
    }
 
    public void nullSafeSet(PreparedStatement st, Object value, int index, SessionImplementor session)
        throws HibernateException, SQLException  // 5
    {
        java.util.Date d = (java.util.Date) value;
        Hibernate.INTEGER.nullSafeSet(st, Dates.getYear(d), index);
        Hibernate.INTEGER.nullSafeSet(st, Dates.getMonth(d), index + 1);
        Hibernate.INTEGER.nullSafeSet(st, Dates.getDay(d), index + 2);
    }
 
    public Object deepCopy(Object value) throws HibernateException {
        java.util.Date d = (java.util.Date) value;
        if (value == null) return null;
        return (java.util.Date) d.clone();
    }
 
    public boolean isMutable() {
        return true;
    }
 
    public Serializable disassemble(Object value, SessionImplementor session)
        throws HibernateException
    {
        return (Serializable) deepCopy(value);
    }
 
    public Object assemble(Serializable cached, SessionImplementor session, Object owner)
        throws HibernateException
   {
        return deepCopy(cached);
    }
 
    public Object replace(Object original, Object target, SessionImplementor session, Object owner)
        throws HibernateException
    {
        return deepCopy(original);
    }
 
}
 

As you see the type converter implements CompositeUserType (1). The key methods are getPropertyValue (2) and setPropertyValue (3) to get and to set values in the properties of the object of the composite type, and nullSafeGet (4) and nullSafeSet (5) for reading and storing this object from and to database.

Reference conversion

Reference conversion is not supported directly by Hibernate. But in some very rare circumstances maybe you need to do conversion in the reference. In this section we explain how to do it.
For example, you may have a reference to driver licence using two columns, DRIVINGLICENCE_LEVEL and DRIVINGLICENCE_TYPE, and the DRIVINGLICENCE_TYPE column does not admit null, but it's possible that the object can have no reference to driving lincence in which case the column DRIVINGLICENCE_TYPE hold an empty string. This is not a normal case if you design the database using foreign keys, but if the database was designed by a RPG programmer, for example, this was done in this way, because RPG programmer are not used to cope with nulls.
That is, you need a conversion for DRIVINGLICENCE_TYPE, for transform null to empty string. This can be achieve with a code like this:

// We apply conversion (null into an empty String) to DRIVINGLICENCE_TYPE column
// In order to do it, we create drivingLicence_level and drivingLicence_type
// We make JoinColumns not insertable nor updatable, we modify the get/setDrivingLincence methods
// and we create a drivingLicenceConversion() method.
@ManyToOne(fetch=FetchType.LAZY)
@JoinColumns({  // 1
    @JoinColumn(name="DRIVINGLICENCE_LEVEL", referencedColumnName="LEVEL",
         insertable=false, updatable=false),
    @JoinColumn(name="DRIVINGLICENCE_TYPE", referencedColumnName="TYPE",
         insertable=false, updatable=false)
})
private DrivingLicence drivingLicence;
private Integer drivingLicence_level;  // 2
private String drivingLicence_type;    // 2
 
public DrivingLicence getDrivingLicence() {  // 3
    // In this way because the column for type of driving lincence does not admit null
    try {
        if (drivingLicence != null) drivingLicence.toString(); // to force load
        return drivingLicence;
    }
    catch (EntityNotFoundException ex) {
        return null;
    }
}
 
public void setDrivingLicence(DrivingLicence licence) {  // 4
    // In this way because the column for type of driving lincence does not admit null
    this.drivingLicence = licence;
    this.drivingLicence_level = licence==null?null:licence.getLevel();
    this.drivingLicence_type = licence==null?null:licence.getType();
}
 
@PrePersist @PreUpdate
private void drivingLicenceConversion() {  // 5
    if (this.drivingLicence_type == null) this.drivingLicence_type = "";
}
 

First, you have to use @JoinColumns with insertable=false and updatable=false on all @JoinColumn (1), in this way the reference is readed from database, but it is not write. Also define plain properties for storing the foreign key of the reference (2).
Now you must write a getter, getDrivingLicence() (3), for returning null when the reference is not found; and a setter, setDrivingLicence() (4), for assigning the key of the reference to the correspoding plain properties.
Finally, you have to write a callback method, drivingLincenceConversion() (5), to do the conversion work. This method will be automatically executed on create and update.
This example shows how it's possible to wrap legate databases simply using a little of programming and some basic resources from JPA.