openxava / documentation / Three-Level Composite Key Demo

Table of contents

Three-Level Composite Key Demo
Scenario
Approach
Steps
Define your @Entity classes
Level1
Level2
Level3
Define @IdClass classes.
Level2Id
Level3Id
Code for System-Assigned Keys
Source Files
Contributions Wanted
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


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: