One Class to Two Tables

From time to time, you'll run into a scenario where your application defines a single class, but the database maintains the correspoinding information in two separate tables. Well, with Hibernate, that's not a problem. All you have to do to map one class to two database tables is to define the first table mapping as you normally would, and then specify the name of the second table your class uses with the very intelligently named @SecondaryTable annotation. From there, when you get to a field that is supposed to be stored in the second table, well, you just mention the second table's name in the @Column annotation. It's all very simple and straight forward.

This tutorial will look at how we map one Java class to two database tables using the @SecondaryTable and @Column JPA annotations. We will create a single class named FooBar, and map it to two database tables, named foo and bar.

FooBar Class to Foo and Bar Tables

To demonstrate the idea of a single class mapping to two different database tables, we're going to create a single class called FooBar. The FooBar class will define an id field of type int, and two String properties named fooName and barCode. As you could imagine, the two String fields in this FooBar class will map to two different database tables, with one table being named foo, and the other table being named bar.

As far as the property mappings go, we'll throw the fooName field in the foo table, and we'll map the barcode field to a column in the bar table. But, how do we do this with Hibernate and JPA annotations?

Well, first, we need to figure out which table will be the main, primary table. For our purposes, the foo table will be the primary mapping table, and the bar table will be the secondary table. As such, we decorate the class declaration of the FooBar class as so:

@Entity
@Table(name="bar")
@SecondaryTable(name="foo")
public class FooBar {
  int id;
  String fooName;
  String barCode;       
}

So, by default, the FooBar class is mapped to a table named bar, as per the @Table(name="bar") annotation. As such, any fields not explicitly mapped to the secondary table foo will be automatically mapped to the bar table.

The UnAnnotated, Un-mained FooBar Class

Here's the FooBar class before we add any annotations, or for that matter, a main method. The key question you should be asking yourself is ?what will this code look like when we map the fields in this single class across two database tables?'

package com.examscam.mappings;
import javax.persistence.*;
import org.hibernate.Session;
import com.examscam.HibernateUtil;

public class FooBar {
  
  int id;
  String fooName; /* maps to foo table */
  String barCode; /* maps to bar table */

  public int getId() {return id;}

  public void setId(int id) {this.id = id;}
  
  public String getBarCode() {
    return barCode;
  }
  public void setBarCode(String barCode) {
    this.barCode = barCode;
  }
  
  public String getFooName() {
    return fooName;
  }
  public void setFooName(String fooName) {
    this.fooName = fooName;
  }
}

Primay Keys and Secondary Tables

After mapping the FooBar class to its default and secondary database tables, standard @Id and @GeneratedValue annotations must be used to decorate the getId() method.

@Entity
@Table(name="bar")
@SecondaryTable(name="foo")
public class FooBar {
  int id;
  String fooName;
  String barCode;   
  @Id
  @GeneratedValue
  public int getId() { return id;}
   
}

Now, the @Id annotation appears to be fairly innocuous, but there is a very significant and easily overlooked detail with regards to how the primary key is mapped behind the scenes. In this case, a primary key named id will appear in both the foo table and the bar table. After all, both tables need a unique identifier for all record entries, and since a single instance branches across two tables, records in those two tables must share the same, unique yet common, identifier. Without a common, shared primary key, recreating the state of a single instance persisted across two database tables would be impossible.

Just looking at the create statements for the two tables, you can see that both have a defined primary key, although only the default table, bar, defines the auto_increment property.

CREATE TABLE  'examscam'.'bar' (
  'id' int(11) NOT NULL auto_increment, 
  'barCode' varchar(255) default NULL,
  PRIMARY KEY  ('id') );

CREATE TABLE  'examscam'.'foo' (
  'fooName' varchar(255) default NULL,
  'id' int(11) NOT NULL, 
  PRIMARY KEY  ('id'),  
  KEY 'FK18CC6E9F1224B' ('id') );

Mapping Columns to Secondary Tables

With the @Table(name="bar") annotation pointing at the bar table, any column not explicitly mapped to another table will, by default, be persisted to the bar table. Of course, that's fine for the barCode field, but we need to ensure that the fooName field is persisted to the secondary, foo table. This is easily accomplished by adding a (table="foo") attribute to the @Column annotation decorating the getFooName() method.

@Column(table="foo")
public String getFooName() {
  return fooName;
} 

Testing FooBar with a main Method

With all of the annotations in place, we're free to code a simple main method that creates a new FooBar object, and subsequently persists the pertinent fields to the appropriate database tables. Here's how the runnable main method looks:

public static void main(String args[]) {
/* HibernateUtil needs FooBar.class 
   in AnnotationConfiguration*/

  HibernateUtil.recreateDatabase();
  FooBar fb = new FooBar();
  fb.setBarCode("90210");
  fb.setFooName("ManChu");
  Session session=HibernateUtil.beginTransaction();
  session.save(fb);
  HibernateUtil.commitTransaction();
}

The Complete and Annotated FooBar Class

package com.examscam.mappings;
import javax.persistence.*;import org.hibernate.Session;
import com.examscam.HibernateUtil;

@Entity
@Table(name="bar")
@SecondaryTable(name="foo")
public class FooBar {
  int id;
  String fooName;
  String barCode;

  @Id
  @GeneratedValue
  public int getId() {return id;}
  public void setId(int id) {this.id = id;}

  @Column(table="foo")
  public String getFooName() {return fooName;}
  public void setFooName(String fooName) {
    this.fooName = fooName;
  }

  /* no need for mapping-goes to default bar table */
  public String getBarCode() {return barCode;}
  public void setBarCode(String barCode) {
    this.barCode = barCode;
  }

  public static void main(String args[]) {
/*HibernateUtil needs FooBar.class in AnnotationConfiguration*/
    HibernateUtil.recreateDatabase();
    FooBar fb = new FooBar();
    fb.setBarCode("90210");
    fb.setFooName("ManChu");
     Session session = HibernateUtil.beginTransaction();
    session.save(fb);
    HibernateUtil.commitTransaction();
  }
}

Adding the Annotated FooBar.class

As with any newly JPA annotated persistence class, you must ensure that it is added to the AnnotationConfiguration object. The custom class we have coded to maintain the configuration of our environment is the HibernateUtil class. The code below contains the getInitializedConfiguration() method of the HibernateUtil class with the FooBar.class being added to the AnnotationConfiguration object.

public class HibernateUtil {
 public static Configuration getInitializedConfiguration(){
    AnnotationConfiguration config = 
                      new AnnotationConfiguration();
    /* add all of your JPA annotated classes here!!! */
    config.addAnnotatedClass(User.class);
    config.addAnnotatedClass(Snafu.class);
    config.addAnnotatedClass(FooBar.class);
    config.configure();
    return config;
  }
}

foo and bar Table Creation

Furthermore, you must make sure the bar and foo tables exist in the examscam database. You can have Hibernate create these tables for you by using the SchemaExport class in conjunction with the updated Configuration object that can be generated by the HibernateUtil's getInitializedConfiguration method. Of course, if you're more into running ddl scripts, here's a little bit of SQL that will create the required tables through a scripting tool:

CREATE TABLE  'examscam'.'bar' (
  'id' int(11) NOT NULL auto_increment, 
  'barCode' varchar(255) default NULL, 
  PRIMARY KEY  ('id'));

CREATE TABLE  'examscam'.'foo' (
  'fooName' varchar(255) default NULL,
  'id' int(11) NOT NULL, PRIMARY KEY  ('id'),  
  KEY 'FK18CC6E9F1224B' ('id'));

Running the FooBar's main Method

After running the FooBar's main method, you'll find that the following two SQL queries have been executed by Hibernate against the database:

Hibernate: 
insert into bar (barCode) values (?)

Hibernate: 
insert into foo (fooName, id) values (?,?)

Of course, the FooBar class' main method set the barCode property to 90210 and the fooName property to Manchu, so inspecting the database after the main method has run shows the 90210 and Manchu properties saved to the appropriate database tables, with both records sharing a common id.

  FooBar fb = new FooBar();
  fb.setBarCode("90210");
  fb.setFooName("ManChu");
  session.save(fb);