5 Comments on “How to map a @ManyToOne association using a non-Primary Key column with JPA and Hibernate”

1. Hi, Vlad.

   I’m working with a legacy system that, for some reason, uses composite IDs for almost all its entities. I’m working with three entities, Payment, Account and User. It’s a bit of a mess.

   Payment has a composite ID (int p_x, int p_y, int p_z), with the first two components of its ID (p_x, p_y) also representing a foreign key to the Account (obviously then with a composite ID (a_x, a_y).

   Similarly, a User has a (u_x, u_y) composite primary key. Account models this as two fields:

   @Column(“cust_x”)
   int customerX;
   @Column(“cust_y”)
   int customerY;

   and the underlying DB table has an equivalent foreign key constraint. Payment has no “direct” JPA associations to either User or Account, but when a Payment is created it always takes an Account ID, copies it into its p_x and p_y, and tacks on the p_z value. The underlying table has a foreign key on (p_x, p_y) -> Account(a_x, a_y).

   I find myself in a situation where I want to query for a collection of Payments and their associated Users, but without also join fetching the Account.

   I thought to do this by adding the User directly to Payment, using the various fields on Account as a @JoinTable:

       @ManyToOne(fetch = FetchType.LAZY)
       @JoinTable(name = "accounts",
               joinColumns = {
                       @JoinColumn(name = "a_x", referencedColumnName = "p_x", insertable = false, updatable = false),
                       @JoinColumn(name = "a_y", referencedColumnName = "p_y", insertable = false, updatable = false)
               },
               inverseJoinColumns = {
                       @JoinColumn(name = "cust_x",table = "users", referencedColumnName = "u_x", insertable = false, updatable = false),
                       @JoinColumn(name = "cust_y", table = "users", referencedColumnName = "u_y", insertable = false, updatable = false),
               }
       )
       private User customer;
   

   But this is causing JPA to throw “secondaryTable JoinColumn cannot reference a non primary key”.

   p_x and p_y are both part of the primary key of Payment, similarly u_x and u_y User. That leaves only (a_x, a_y, cust_x, cust_y) on Account as suspects. (a_x, a_y) form the primary key for Account: (cust_x, cust_y) obviously do not, but as I said are part of a foreign key. But these must be the reason for the failure to model the relationship.

   I was hoping to find an answer in this article, but I don’t follow why my attempted solution doesn’t ‘match’ what’s happening here.

   Is what I’m attempting to do simply not possible? If I want the Payment and the User that paid it, am I simply forced to return it all as (select p from Payment join fetch p.account a join fetch a.user u)?

   Thanks in advance, big admirer.

   Reply

   • The @ManyToOne cannot be used with @JoinTable because @JoinTable is for @ManyToMany. But, in your case, you don’t really need a different mapping, you just need a different query.

     You said that you want the Payment and the User that paid it, and not the account.

     And the problem is that you used JOIN FETCH instead of JOIN. So, instead of:

     select p 
     from Payment 
     join fetch p.account a 
     join fetch a.user
     

     You need:

     select p, u 
     from Payment 
     join p.account a 
     join a.user u
     

     Here’s an example that uses this approach.

     Reply

     • Thanks for getting back to me, Vlad.

       If I wrap the result of (p, u) in a record as shown in that example, are p and u still managed?

       And in this instance, I’m not fetching a single (p, u), I need to batch-process a set of (p, u)s. So if I do

       select p, u
       from Payment p
         join p.account a
         join a.user u
         where p... (conditions on p for my batch job)
       

       and return a List<PaymentWithUser>, they’ll all be managed by Hibernate, right?

     • If I wrap the result of (p, u) in a record as shown in that example, are p and u still managed?

       That’s a very good question. Normally, we fetch DTO projections using the constructor expression, but in this case the entities are fetched and bound to the Record. So, both entities are managed even if fetched via the Record projection. Here’s a test case that validates this property.

       You should be able to select a List<PaymentWithUser> and the entities should be managed.

     • Inspired by this thread, I wrote this article about the proposed solution.

This site uses Akismet to reduce spam. Learn how your comment data is processed.