openxava / documentation / Three-Level Composite Key Demo

After starting a project with a composite key database structure and a business need to load tables from external sources on an ongoing basis, considerable effort was spent trying to get composite keys working for an OpenXava project. This specific example was the most problematic: how to get multi-level parent/child structures working, since there are some problems in Hibernate support for this structure.

Scenario

The specific scenario demonstrated here seems to be the most difficult in Hibernate. Key requirements are as follows:

1. Three tables Level1, Level2, and Level3. Level2 is a child of Level1, Level3 is a child of Level2.
2. Keys are implemented in standard ERD fashion. For example, the primary key of Level3 is the combination of the Level1.id + Level2.id + Level3.id
3. While key values should be assigned in a next-available scenario, from a data perspective it would be nice if keys could be sequentially assigned within associated parent keys starting at 1, just like someone would do if they assigned them in a spreadsheet, but remaining unique within the specified parent keys. For example, many families can have a child with the same name, but within a family, no two children should have the same name.

Approach

• This is an OpenXava solution, and as such is based upon JPA but also dependent on Hibernate. Warning: Some Hibernate-specific components have been used, but only where necessary to get the solution working correctly.
• All database column names have been overridden in this example so that it is evident when we are referring to database field names versus Java field names.
• Likewise, this example uses Java field names which clearly describe the context where they are used. Some developers prefer standardized names for identifiers or keys (e.g. id, pk, etc). You are free to follow your own standard, the chosen standard was used so that all field references are distinct within the example for ease of learning.
• While @IdClass might not be your preference to use, this is what works with Hibernate at this time. If @EmbeddedId support for composite keys is improved in future releases, it may become viable to use here. Generally, @EmbeddedId is easier to read, and certainly a lot cleaner than some of the non-standard @IdClass structures we have in this example which have been coded to avoid some existing bugs/deficiencies in Hibernate.

Steps

Define your @Entity classes

Level1

@Entity
public class Level1 {
 
  @Id @Hidden
  @Column(length=5,name="LEVEL1_ID")
  @GenericGenerator(name="my_seq_gen",                                       // 1
    strategy="org.openxava.school.persist.SqlSequenceGenerator",
    parameters={
        @Parameter(name="id_query",value="select max(LEVEL1_ID) + 1 from LEVEL1")
    })
  @GeneratedValue(generator="my_seq_gen")
  private int level1Id;
 
  @Column(length=20,name="LEVEL1_NAME",unique=true,nullable=false)           // 2
  @Required
  private String level1Name;
 
  @OneToMany(mappedBy="level2Parent")                                        // 3
  private Collection<Level2> level1Children;
 
... getters and setters ...

1. This is a custom sequence generator that uses an SQL query to find the highest key in use now and return the next available key. At Level1, this is a static query, so it is included as a parameter here.
2. No duplicate names allowed.
3. This declares a convenient way to display the associated children. This is optional, you can include it or not based on your display preferences.

Level2

@Tab(properties="level2Parent.level1Name,level2Name",
    defaultOrder="${level2Parent.level1Name},${level2Name}")
 
@Entity
@IdClass(Level2Id.class)                                                             // 1
@Table(uniqueConstraints=@UniqueConstraint(columnNames={"LEVEL1_ID","LEVEL2_NAME"})) // 2
public class Level2 implements IDynamicSqlSequence {                                 // 3
 
  // Annotations for this field are duplicated in the ID class due to a Hibernate bug -- please keep in sync!!!
  @Id
  @ManyToOne(fetch=FetchType.EAGER)                                                  // 4
  @JoinColumn(name="LEVEL1_ID",referencedColumnName="LEVEL1_ID",nullable=false,unique=false,insertable=true,updatable=true)
  @DescriptionsList(descriptionProperties="level1Name")
  private Level1 level2Parent;
 
  // Annotations for this field are duplicated in the ID class due to a Hibernate bug -- please keep in sync!!!
  @Id @Hidden
  @GenericGenerator(name="sql_seq_gen",                                              // 5
    strategy="org.openxava.school.persist.SqlSequenceGenerator")
  @GeneratedValue(generator="sql_seq_gen")
  @Column(length=5,name="LEVEL2_ID")
  private int level2Id;
 
  @Column(length=20,name="LEVEL2_NAME",nullable=false)
  @Required
  private String level2Name;
 
  @OneToMany(mappedBy="level3Parent")                                                // 6
  private Collection<Level3> level2Children;
 
... getters and setters ...
 
  @Override
  public String nextKeyQuery() {
    String myResult = new String("select max(LEVEL2_ID) + 1 from LEVEL2 where LEVEL1_ID = ");
    myResult += level2Parent.getLevel1Id();                                          // 7
    return myResult;
  }
 

1. The ID class will be demonstrated below
2. Value of the name field must be unique within the specified Level1 parent
3. The generated key in this case depends on the parent, so a callback has been implemented. IDynamicSqlSequence interface defines the contract for this callback
4. This defines the relationship to the parent
5. SqlSequenceGenerator implements the logic for the sequence generator
6. A convenient way to display the associated children
7. Returns the SQL query used by the sequence generator, including the substitution of the current parentage ID values

Level3

@Tab(properties="level3Parent.level2Parent.level1Name,level3Parent.level2Name,level3Name")
 
@Entity
@IdClass(Level3Id.class)
@Table(uniqueConstraints=@UniqueConstraint(columnNames={"LEVEL1_ID","LEVEL2_ID","LEVEL3_NAME"})) // 1
public class Level3 implements IDynamicSqlSequence {
 
  // Annotations for this field are duplicated in the ID class due to a Hibernate bug -- please keep in sync!!!
  @Id
  @ManyToOne(fetch=FetchType.EAGER)
  @JoinColumns({                                                                                 // 2
    @JoinColumn(name="LEVEL2_ID",referencedColumnName="LEVEL2_ID",nullable=false,unique=false,insertable=false,updatable=false),
    @JoinColumn(name="LEVEL1_ID",referencedColumnName="LEVEL1_ID",nullable=false,unique=false,insertable=false,updatable=false)
  })
  @DescriptionsList(descriptionProperties="level2Parent.level1Name,level2Name")
  private Level2 level3Parent;                                                                   // 3
 
  // Annotations for this field are duplicated in the ID class due to a Hibernate bug -- please keep in sync!!!
  @Id @Hidden
  @GenericGenerator(name="sql_seq_gen",strategy="org.openxava.school.persist.SqlSequenceGenerator")
  @GeneratedValue(generator="sql_seq_gen")
  @Column(length=5,name="LEVEL3_ID")
  private int level3Id;
 
  @Column(length=20,name="LEVEL3_NAME",nullable=false)
  @Required
  private String level3Name;
 
... getters and setters ...
 
  @Override
  public String nextKeyQuery() {
    String myResult = String.format("select max(LEVEL3_ID) + 1 from LEVEL3 where LEVEL1_ID = %1$s and LEVEL2_ID = %2$s",
        level3Parent.getLevel2Parent().getLevel1Id(),                                            // 4
        level3Parent.getLevel2Id());
    return myResult;
  }

1. Names must be unique within the specified Level2 parent
2. Lists the parent fields included in the composite key
3. Level3's parent is a Level2 item
4. Returns the SQL query with the values of the parent identifiers parsed into the where condition

Define @IdClass classes.

Typically, the key classes do not normally have many annotations. However, in order to work around the Hibernate shortcomings, it has been found that if the annotations are duplicated here, it works.

Level2Id

public class Level2Id implements Serializable, IDynamicSqlSequence { // 1
  private static final long serialVersionUID = 1L;
 
  // These field annotations are duplicated from the entity class due to a Hibernate bug. Please keep in sync!!!
  // If the Hibernate bug gets fixed, these should all be removed.
  @Id
  @ManyToOne(fetch=FetchType.EAGER)
  @JoinColumn(name="LEVEL1_ID",referencedColumnName="LEVEL1_ID",nullable=false,unique=false,insertable=true,updatable=true)
  @DescriptionsList(descriptionProperties="level1Name")
  private Level1 level2Parent;
 
  // These field annotations are duplicated from the entity class due to a Hibernate bug. Please keep in sync!!!
  // If the Hibernate bug gets fixed, these should all be removed.
  @Id
  @GenericGenerator(name="sql_seq_gen",strategy="org.openxava.school.persist.SqlSequenceGenerator")
  @GeneratedValue(generator="sql_seq_gen")
  @Column(length=5,name="LEVEL2_ID")
  private int level2Id;
 
... getters and setters ...
... implement required methods for IDynamicSqlSequence interface (copy from Level2) ...
... hashCode() and equals() ...

1. In support of the custom ID generator in the annotations, IDynamicSqlSequence interface is also implemented here.

Level3Id

public class Level3Id implements Serializable,IDynamicSqlSequence {
  private static final long serialVersionUID = 4L;
 
  // These field annotations are duplicated from the entity class due to a Hibernate bug. Please keep in sync!!!
  // If the Hibernate bug gets fixed, these should all be removed.
  @Id
  @ManyToOne(fetch=FetchType.EAGER)
  @JoinColumns({
    @JoinColumn(name="LEVEL2_ID",referencedColumnName="LEVEL2_ID",nullable=false,unique=false,insertable=false,updatable=false),
    @JoinColumn(name="LEVEL1_ID",referencedColumnName="LEVEL1_ID",nullable=false,unique=false,insertable=false,updatable=false)
  })
  @DescriptionsList(descriptionProperties="level2Parent.level1Name,level2Name")
  private Level2 level3Parent;
 
  // These field annotations are duplicated from the entity class due to a Hibernate bug. Please keep in sync!!!
  // If the Hibernate bug gets fixed, these should all be removed.
  @Id @Hidden
  @GenericGenerator(name="sql_seq_gen",strategy="org.openxava.school.persist.SqlSequenceGenerator")
  @GeneratedValue(generator="sql_seq_gen")
  @Column(length=5,name="LEVEL3_ID")
  private int level3Id;
 
... getters and setters ...
... implement required methods for IDynamicSqlSequence interface ...
... hashCode() and equals() ...

Code for System-Assigned Keys

This section includes a system-assigned key solution (required so that any numeric PK fields can be hidden making the user interface clean). While it is not critical to the solution that you use this specific approach, if your legacy database uses numeric PK values (as many do) it is likely that some method will be necessary for a clean user interface (since the user is not likely to know the next available key value).
Note: This solution has not been tested for thread-safety (in the case of concurrent user requests to insert rows to the same table). User beware!!!

/**
 * Custom class to generate the next available sequence number appropriate for the context using SQL.
 *
 * Classes which use this generator must implement IDynamicSqlSequence interface which requires the following method(s);
 * a method which returns an SQL query to calculate the next sequence.
 *
 * @author Roy Hellinga
 *
 */
public class SqlSequenceGenerator implements IdentifierGenerator, Configurable {
  public static final String PROPERTY_KEY_QUERY = "id_query";
  private static Log log = LogFactory.getLog(SqlSequenceGenerator.class);
  private Properties props = null;
 
  /* (non-Javadoc)
   * @see org.hibernate.id.IdentifierGenerator#generate(org.hibernate.engine.spi.SessionImplementor, java.lang.Object)
   */
  @Override
  // Until OpenXava 6.0.2 public Serializable generate(SessionImplementor session, Object object) throws HibernateException {
  public Serializable generate(SharedSessionContractImplementor session, Object object) throws HibernateException { // Since OpenXava 6.0.2 that uses Hibernate 5.3
 Integer nextValue = new Integer(1);
    IDynamicSqlSequence myClass = null;
    ResultSet rs = null;
 
    String qs = null;
    if(props.containsKey(PROPERTY_KEY_QUERY)) {
      qs = props.getProperty(PROPERTY_KEY_QUERY);
      log.info("RH:Property-based query for next key=" + qs);
    }
    else
    {
      try {
        myClass = (IDynamicSqlSequence)object;
      }
      catch(Exception e) {
        log.error("Problem casting the Entity to the interface. Ensure interface IDynamicSqlSequence is implemented.",e);
        throw new HibernateException("Problem casting the Entity to the required interface",e);
      }
      qs = myClass.nextKeyQuery();
      if(qs == null) {
        log.error("RH:nextKeyQuery() returned null.");
        throw new HibernateException("nextKeyQuery() callback returned null");
      }
      else {
        log.info("RH:Callback query for next key=" + qs);
      }
    }
 
    try{
      rs = session.connection().createStatement().executeQuery(qs);
      if(rs.next()){
        int newId = rs.getInt(1);
        log.info("RH:Generated next key="+newId);
        nextValue = new Integer(newId);
      }
    }
    catch (SQLException e){
      log.error("Unexpected SQL Exception while calculating key value", e);
      throw new HibernateException(e);
    }
    finally {
      if (rs != null) {
        try {
          rs.close();
        }
        catch (Throwable t) {
          log.error("Unexpected exception closing result set", t);
          throw new HibernateException(t);
        }
      }
    }
    if(nextValue.intValue() < 1) {
      log.warn("RH:This must be the first row in this table -- setting initial value to 1");
      nextValue = new Integer(1);
    }
    return nextValue;
 
  }
 
  @Override
  public void configure(Type arg0, Properties arg1, Dialect arg2) throws MappingException {
    props = arg1;
  }
 
}
 

Source Files

Complete source for the above solution is here: src.zip
If you have a functioning MySchool project, you should be able to unzip the above file into your src folder of that project, build your project, update your schema, and deploy to your web app server.

Contributions Wanted

This example could be improved with the following contributions:

• Any advancements in subsequent Hibernate releases (current example was developed using Hibernate 4.3.9.Final)
• Possibly eliminate some of the duplication between the entity and the ID classes. It is likely that the duplicated annotations and code would only be executed in one place, but which annotations and code could be suppressed is to be determined.
• Thread-safety for the provided sequence generator (probably using a table lock of some sort).