Archived: How to map a @ManyToOne association using a non-Primary Key column with JPA and Hibernate

 
https://vladmihalcea.com/how-to-map-a-manytoone-association-using-a-non-primary-key-column/

How to map a @ManyToOne association using a non-Primary Key column with JPA and Hibernate

Last modified: Jan 22, 2019

Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Hypersistence Optimizer is that tool!

Introduction

While answering questions on the Hibernate forum, I stumbled on the following question about using the @ManyToOne annotation when the Foreign Key column on the client side references a non-Primary Key column on the parent side.

In this article, you are going to see how to use the @JoinColumn annotation in order to accommodate non-Primary Key many-to-one associations.

Domain Model

Assuming we have the following tables in our database:

The isbn column on the publication and book tables are linked via a Foreign Key constraint which is the base of our @ManyToOne assocation:

Non Primary-Key @ManyToOne mapping

The Book represents the parent side of the association, and it’s mapped as follows:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

@Entity(name = "Book") @Table(name = "book") public class Book     implements Serializable {       @Id     @GeneratedValue     private Long id;       private String title;       private String author;       @NaturalId     private String isbn;       //Getters and setters omitted for brevity }

The isbn column is mapped as a @NaturalId since it can be used as a business key as well.

For more details about the @NaturalId annotation, check out this article.

The Publication represents the child of the association, so it’s going to be mapped like this:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

@Entity(name = "Publication") @Table(name = "publication") public class Publication {       @Id     @GeneratedValue     private Long id;       private String publisher;       @ManyToOne(fetch = FetchType.LAZY)     @JoinColumn(         name = "isbn",         referencedColumnName = "isbn"     )     private Book book;       @Column(         name = "price_in_cents",         nullable = false     )     private Integer priceCents;       private String currency;       //Getters and setters omitted for brevity }

By default, the @ManyToOne association assumes that the parent-side entity identifier is to be used to join with the client-side entity Foreign Key column.

However, when using a non-Primary Key association, the referencedColumnName should be used to instruct Hibernate which column should be used on the parent side to establish the many-to-one database relationship.

Testing time

Assuming we have the following entities in our database:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

Book book = new Book(); book.setTitle( "High-Performance Java Persistence" ); book.setAuthor( "Vlad Mihalcea" ); book.setIsbn( "978-9730228236" ); entityManager.persist(book);   Publication amazonUs = new Publication(); amazonUs.setPublisher( "amazon.com" ); amazonUs.setBook( book ); amazonUs.setPriceCents( 4599 ); amazonUs.setCurrency( "$" ); entityManager.persist( amazonUs );   Publication amazonUk = new Publication(); amazonUk.setPublisher( "amazon.co.uk" ); amazonUk.setBook( book ); amazonUk.setPriceCents( 3545 ); amazonUk.setCurrency( "&" ); entityManager.persist( amazonUk );

Upon fetching the Publication along with its associated Book, we can see that the @ManyToOne association works as expected:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

Publication publication = entityManager.createQuery(     "select p " +     "from Publication p " +     "join fetch p.book b " +     "where " +     "   b.isbn = :isbn and " +     "   p.currency = :currency", Publication.class) .setParameter( "isbn", "978-9730228236" ) .setParameter( "currency", "&" ) .getSingleResult();   assertEquals(     "amazon.co.uk",     publication.getPublisher() );   assertEquals(     "High-Performance Java Persistence",     publication.getBook().getTitle() );

When executing the JPQL query above, Hibernate generates the following SQL statement:

1 2 3 4 5 6 7 8 9 10 11 12

SELECT         p.id AS id1_1_0_, b.id AS id1_0_1_,         p.isbn AS isbn5_1_0_, p.currency AS currency2_1_0_,         p.price_in_cents AS price_in3_1_0_,         p.publisher AS publishe4_1_0_,         b.author AS author2_0_1_, b.isbn AS isbn3_0_1_,         b.title AS title4_0_1_ FROM    publication p INNER JOIN         book b ON p.isbn = b.isbn WHERE   b.isbn = '978-9730228236'         AND p.currency = '&'

As you can see, the referencedColumnName allows you to customize the JOIN ON clause so that the isbn column is used instead of the default entity identifier.