Mapping ER-Model into JPA Annotations

Facebooktwittergoogle_plusredditpinterestlinkedinmail

Java Persistence API (JPA) coined with Java 5 Annotations made Object to Relational Mapping (ORM) a bliss. The problem is most of the detailed documentations available usually consider JPA from the POJOs perspective, but what if we are creating a JPA application that should persist to an existing database. An Entity Relationship diagram can easily be constructed for existing databases using a tool such as MySQL Workbench. From the ER Model we can work backwards to create the JPA annotations required to persist to the existing database. This article provides a mapping that can be used to create the JPA annotations for an existing database’s ER Model using Hibernate’s implementation of JPA.

I prefer to use the crow’s feet notation in the ER model to represent the cordiality of the relations and the participation conditions as explained in Table 1 below. An example of an ER model that uses this notation is shown in Figure 1 below.

Table 1 – Crow’s feet notation used in the ER- modelling

Symbol

Meaning

Open blob –

Zero or one participation

Closed blob –

Exactly one participation

Open blob and crow’s foot –

Zero or more participation

Closed blob and crow’s foot –

One or more participation

Figure 1 – Sample ER Model using crow’s feet annotation

The approach that can be used to map relations form the E-R model into JPA annotations is summarised in Table 2 below. This approach can be followed to map the entity classes into the existing data schema using JPA annotations. Good database design should resolve all the Many to Many relationships during the logical database design stage by introducing a third dependent relation, and hence there was no need to use @ManyToMany JPA mapping.

 I have also discovered that the Cascade annotation for foreign key updates on the parent table should not be included in the child entity as this will result in the deletion of the parent row whenever a child row is deleted. It was clear that this constraint should be implemented in the database schema and not included in the JPA annotation as it does not have the desired effect.

Table 2 – ER-Model and Relational Mode to JPA and Hibernate mappings

ER-Model

Relational Mode

JPA Annotation

Entity

Relation

@Entity

Identifier

Primary key

@Id

Alternate Key

@UniqueConstraint(columnNames = {“staff_id”, “holiday_year”})

Relationships:

One to One

Primary key + Foreign key mechanism – plus declaring the Foreign key as alternate key.

Mandatory participation condition:

Left side – can be achieved using a constraint.

Right side – not allowing null for Foreign key.

@OneToOne

Declaring alternate key:

@UniqueConstraint(columnNames = {“staff_id”, “holiday_year”})

Declaring a check constraint:

@org.hibernate.annotations.Check(

constraints = “(mileage is not null) or (amount is not null)”

)

Declaring not null:

@Column(nullable = false)

One to Many

Primary key + Foreign key mechanism.

Mandatory Participation condition:

Left side – can be achieved using a constraint.

Right side – not allowing null for Foreign key.

@OneToMany annotation created in the owning class

Declaring a check constraint:

@org.hibernate.annotations.Check(

constraints = “(mileage is not null) or (amount is not null)”

)

Declaring not null:

@Column(nullable = false)

Cascading:

Cascading works well when annotating the owning relationship

@OneToMany(cascade = {CascadeType.ALL})

Many to One

Primary key + Foreign key mechanism.

Mandatory Participation condition:

Left side – not allowing null for Foreign key.

Right side – can be achieved using a constraint.

@ManyToOne annotation created in the owned class

Declaring a check constraint:

@org.hibernate.annotations.Check(

constraints = “(mileage is not null) or (amount is not null)”

)

Declaring not null:

@Column(nullable = false)

Cascading:

Cascading annotation should not be used in the child class, as will result in the primary key table being updated. Should be included in the database schema

Many to Many

Should be resolved at the Logical database design, by resolving the M-N relationships into 3 relations

Mandatory Participation condition:

Implemented as above for One to Many relationships on the One side of the two owning relations

@ManyToMany

The expected name for the intermediate table is table1_table2 unless specified otherwise in the annotation.

  • René

    Thanks for this overview!