How to Set Up a Composite Primary Key in jOOQ and Hibernate

Patrycja Dybka, Community Manager at Vertabelo

by
Patrycja Dybka
Community Manager at Vertabelo

Posted: September 1, 2016

In today’s article, we will take a look at composite primary key support in two top Java ORMs: jOOQ and Hibernate. We’ll look at a couple examples, learn how column configuration looks in Hibernate, and how support is organized in jOOQ.

The composite primary key is a key that consists of more than one column, and its column combination guarantees its uniqueness.

To handle the connection to the database, the application uses libraries known as object-relational mappers, or ORMs. Let’s see how jOOQ and Hibernate support this functionality.

ORMs and the Composite Primary Key

Below is a small part of a database designed as a prototype for an online store. The product table contains the name and price of a specific product. Take a close look, because this situation will be presented later in Hibernate and jOOQ.




The order_t table contains values that relate to users’ orders. Because the order_t table and the product table are connected via a many-to-many relationship, a junction table, order_item, was introduced.

Vertabelo order_item table

Notice that the foreign keys -- order_id from the order_t table and product_id from the product table -- are values that uniquely identify the order_item table. Therefore, both are marked as a primary key and together they make up the composite primary key of the order_item table.

Vertabelo order_item

To make the situation a little bit more complicated, let’s assume that each order item can be associated with one or more statuses. To meet this case, the order_item_status table was introduced. It relates to the order_item table. (This example is a little bit of a stretch, but stay with it and focus on the ORM support.)

Vertabelo order_item_status

Mapping the Composite Primary Key with Hibernate

Let’s start with Hibernate. For those who aren’t familiar with it, Hibernate is a Java object-relational mapper that follows the “Object-First” approach. This means that the appropriate database structures are generated based on the Java code. So, before we can create structures within the database, we need to write Java classes. Hibernate is an implementation of the JPA specification and follows the rules defined by that specification. Accordingly, there are several possibilities of defining a composite primary key through several syntaxes. What are they?

With an @Embeddable

For a single-column primary key, Hibernate provides the @Id annotation. For a table product, the entity looks as follows:

@Entity
public class Product {

@Id
private int id;

// ...
}

One of the ways of supporting the composite primary key is by using the Embeddable annotation.

This annotation specifies a class whose instances are stored as part of an owning entity and who share the identity of that entity. According to the JPA standard, the class that stands for the composite primary key must implement the Serializable interface. This class may be a static inner class.

In this instance, the @Embeddable class looks like this:

@Embeddable
static class OrderItemPK implements Serializable {
   private int orderId;
   private int productId;

//...
}

Then we use this class as a type of the field in the entity class. This field should be annotated by an @EmbeddedId annotation. It denotes a composite key that is an embeddable class:

@Entity
public class OrderItem {
	
@Embeddable
static class OrderItemPK implements Serializable {
   private int orderId;
   private int productId;

//...
}
   @EmbeddedId
   private OrderItemPk orderItemPk;

   @MapsId("OrderId")
   @ManyToOne(optional = false)
   @JoinColumn(name = "orderId", referencedColumnName = "orderId")
   private OrderT orderT;

   @MapsId("ProductId")
   @ManyToOne(optional = false)
   @JoinColumn(name = "productId", referencedColumnName = "productId")
   private Product product;

   private int amount;

// ...
}

In our example, the order_item table consists mainly of a composite primary key. The fields of this composite key constitute a foreign key to the product and order_t tables.

order_item foreign keys

We used the @MapsId annotator to accomplish this. It specifies the attribute (within the composite key) to which the attribute corresponds. The @ManyToOne annotation instantiates the many-to-one relationship, and @JoinColumn indicates the owner of the relationship.

I chose a PostgreSQL database as the target for the tables. The generated order_item table looks like this:

Postgres order_item table

Now, let’s consider the order_item_status table. Here the situation is a little bit different. The primary key is a surrogate identificator. The foreign key references a two-column primary key from the order_item table.

Vertabelo order_item_status table

In Hibernate, the id field is annotated by the @Id annotation and stands for the primary key in the table. Next, we have to present a foreign constraint. The two-column primary key stands in for this, so the foreign key should also consist of two columns. For that, we have to annotate the orderItem field of the OrderItem type (which stands for the OrderItem entity) with two annotations. The @ManyToOne annotation says that this is a many-to-one relationship with orderItemStatus, while @joinColumns joins the orderId and productId columns from the order_item table.

order_item status mapping foreign key

The code for this entity is presented below:

@Entity
public class OrderItemStatus {

   @Id
   private int id;

   @ManyToOne(optional = false)
   @JoinColumns({
           @JoinColumn(name = "orderId", referencedColumnName = "orderId"),
           @JoinColumn(name = "productId", referencedColumnName = "productId")
   })
   private OrderItem orderItem;

   private String status;
   private Date  changeTime;
}

The generated The generated order_item_status table in PostgreSQL looks as follows: table in PostgreSQL looks as follows:

Postgres order_item_status

With an @Idclass

Defining a composite primary key can be also done by using the Idclass annotation. This provides a slightly different mechanism for the same use case. This time, instead of the @Embeddable annotation, the composite primary key’s class is a simple Java class that implements the Serializable interface.

static class OrderItemPK implements Serializable {
   private int productId;
   private int orderId;
	
//...
 
}

The entity class (OrderItem, in this example) that contains the composite primary key should be annotated by the Idclass annotation. This points to the OrderItemPK.class. Each field in the composite primary key must be also annotated by Idclass. Note that the names and types for the identifier properties of the OrderItem entity are the same as those in the static inner class OrderItemPK. Fields from the composite key (that also constitutes a foreign key) are mapped the same way as the preceding example.

@Entity
@IdClass(OrderItemPK.class)
public class OrderItem {

   @Id
   private int productId;

   @Id
   private int orderId;

   @MapsId("productId")
   @ManyToOne
   @JoinColumn(name = "productId", referencedColumnName = "productId")
   private Product product;

   @MapsId("OrderId")
   @ManyToOne
   @JoinColumn(name = "orderId", referencedColumnName = "orderId")
   private OrderT orderT;
  
   private int amount;

//...
}

The order_item_status table is the same as in the @Embeddable annotation example.

How Composite Keys Are Handled by jOOQ

jOOQ is a Java ORM, but it follows a different approach than Hibernate; it generates the Java code from the database schema. Because the code is based on the schema, we don’t have to know more than a little SQL for this task to work.

For example, after creating the database model in Vertabelo we can click the SQL button, which will generate the DDL schema for us. We can use this to create a database schema, either by using psql or by launching a Gradle task that will execute the script.

So there is really no need to dig much more into jOOQ. It will generate ready-to-use sufficient classes by itself.

generate jOOQ classes

Since we already have the database schema, let’s generate some jOOQ code. For that purpose, we’ll need to configure the jOOQ codegen plugin in build.gradle by specifying the target database’s connection details, the generation details, and so on.

The relevant part of build.gradle for this particular configuration looks like this:

jooq {
  sample(sourceSets.main) {
     jdbc {
        driver = 'org.postgresql.Driver'
        url = 'jdbc:postgresql://localhost:5432/orderdemo'
        user = 'orderdemo'
        password = 'orderdemo'
        schema = 'public'
     }
     generator {
        name = 'org.jooq.util.DefaultGenerator'
        strategy {
           name = 'org.jooq.util.DefaultGeneratorStrategy'
           // ...
        }
        database {
           name = 'org.jooq.util.postgres.PostgresDatabase'
           inputSchema = 'public'
           // ...
        }
        generate {
           relations = true
           deprecated = false
           records = true
           immutablePojos = true
           fluentSetters = true
           daos = true
           // ...
        }
        target {
           packageName = 'com.example.database'
           directory = 'src/main/java'
        }
     }
  }
}

After running the build task, you can now take a look at what was generated. The sufficient classes are presented below.

jOOQ  classes

These classes were generated in the specified build.gradle destination package com.example.database. The tables package consists of these packages:

  • Pojos, which has classes that match tables in the database.
  • Daos, which holds the DAOs for all tables.
  • Records, which contains classes that hold the records for a particular type.

Apart from the tables package, we additionally have Keys.java, Public.java, and Tables.java. The most interesting in this instance is Keys.java, which contains UNIQUE, PRIMARY and FOREIGN key definitions.

jOOQ doesn’t provide a separate class for primary columns. Instead, it stores the columns for the composite key in a Record subtype group.

Let’s take a look at the relevant parts of this code. To start, look at the generated Keys.java. For the order_item table, the primary key definition looks like:

public static final org.jooq.UniqueKey<com.example.database.tables.records.OrderItemRecord> ORDER_ITEM_PKEY = createUniqueKey(com.example.database.tables.OrderItem.ORDER_ITEM, com.example.database.tables.OrderItem.ORDER_ITEM.ORDER_ID, com.example.database.tables.OrderItem.ORDER_ITEM.PRODUCT_ID);

This primary key for the order_item table uses a foreign key to reference the order_item_status table. This results in a composite foreign key; the jOOQ definition is presented below:

public static final org.jooq.ForeignKey<com.example.database.tables.records.OrderItemStatusRecord, com.example.database.tables.records.OrderItemRecord> ORDER_ITEM_STATUS__FK_OVK5KA7V30B5LQKOY8PRMM865 = createForeignKey(com.example.database.Keys.ORDER_ITEM_PKEY, com.example.database.tables.OrderItemStatus.ORDER_ITEM_STATUS, com.example.database.tables.OrderItemStatus.ORDER_ITEM_STATUS.ORDER_ID, com.example.database.tables.OrderItemStatus.ORDER_ITEM_STATUS.PRODUCT_ID);

Now, let’s move to the created DAOs. (JOOQ also supports composite keys in DAO generation.)

The DAO with the composite primary key for the order_item table is below:

public class OrderItemDao extends org.jooq.impl.DAOImpl<com.example.database.tables.records.OrderItemRecord, com.example.database.tables.pojos.OrderItem, org.jooq.Record2<java.lang.Integer, java.lang.Integer>> {

…

@Override
protected org.jooq.Record2<java.lang.Integer, java.lang.Integer> getId(com.example.database.tables.pojos.OrderItem object) {
  return compositeKeyRecord(object.getOrderId(), object.getProductId());
}

...

}

In the end, you have a reasonable amount of choice for introducing a composite key.

If you’re wondering which option supports ORMs, you can securely leave it to jOOQ or Hibernate. Configuring Hibernate is not difficult, and jOOQ generates code automatically from the database schema.

 
 
 

Try our online database modeler. No registration. No commitments.

 
 
Tags
 
Subscribe to our newsletter

If you find this article useful, join our weekly newsletter to be notified about the latest posts.

 
 
 
New SQL Course! The only interactive course for SQL window functions on the Internet. View course Discover our other courses: