@OneToOne mapping for non key columns

Question

I am working on using Hibernate for an existing legacy schema (I cannot update it) and there is a curious case:

Table User:

1. db_id PK
2. user_id (Unique constraint)
3. ... other columns

Table Address

1. db_id PK
2. user_id (Unique constraint)
3. ... usual address columns

These have a one-one relationship. I am creating following @Entity annotated classes.

class UserEntity {
    @Id
    @GeneratedValue
    private UUID dbId;

    private String userId;

    @OneToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = "user_id", referencedColumnName = "user_id")
    private AddressEntity address;
}

class AddressEntity {
    @Id
    @GeneratedValue
    private UUID dbId;

    private String userId;

    @OneToOne
    private UserEntity user;
}

Hibernate thinks this is a duplicate column definition which is reasonable. However since in the Address table I do not have user_id as FK or PK I am not sure how to communicate this to Hibernate. I have tried to search for a similar but no success so any advise will be much appreciated.

Answer 1

UPDATED

Probably there are better ways to accomplish this, like using a @NaturalId, but I cannot get it work with Hibernate 5.2.12.Final.

However, @JoinFormula to the rescue:

@Entity
@Table(name = "T_USER")
public class UserEntity implements Serializable
{
    private static final long serialVersionUID = 1L;

    @Id
    @Type(type = "uuid-char")
    @Column(name = "DB_ID", length = 36)
    private UUID dbId;

    @Column(name = "USER_ID", nullable = false, unique = true)
    private String userId;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinFormula("(select x.DB_ID from T_ADDRESS x where x.USER_ID=USER_ID)")
    private AddressEntity address;

    @Override
    public String toString()
    {
        return String.format("%s: dbId=%s, userId=%s, address=%s",
            getClass().getSimpleName(),
            dbId,
            userId,
            address != null ? address.getDbId() : null);
    }
}

@Entity
@Table(name = "T_ADDRESS")
public class AddressEntity implements Serializable
{
    private static final long serialVersionUID = 1L;

    @Id
    @Type(type = "uuid-char")
    @Column(name = "DB_ID", length = 36, nullable = false, unique = true)
    private UUID dbId;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "USER_ID", referencedColumnName = "USER_ID")
    private UserEntity user;

    @Override
    public String toString()
    {
        return String.format("%s: dbId=%s, user=%s",
            getClass().getSimpleName(),
            dbId,
            user != null ? user.getDbId() : null);
    }
}

Tested it with MySQL8 using:

CREATE TABLE `t_user` (
  `DB_ID` varchar(36) NOT NULL,
  `USER_ID` varchar(255) NOT NULL,
  PRIMARY KEY (`DB_ID`),
  UNIQUE KEY `UK_kvueux8cmkdekeqhrs7pumkwi` (`USER_ID`)
) ENGINE=InnoDB

CREATE TABLE `t_address` (
  `DB_ID` varchar(36) NOT NULL,
  `USER_ID` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`DB_ID`),
  KEY `FK1s9gxk3we3yq11hjw5hp7ahp5` (`USER_ID`),
  CONSTRAINT `FK1s9gxk3we3yq11hjw5hp7ahp5` FOREIGN KEY (`USER_ID`) REFERENCES `t_user` (`user_id`)
) ENGINE=InnoDB

with this quick'n'dirty launcher:

public class Main
{
    public static void main(String[] args)
    {
        EntityManagerFactory emf = Persistence.createEntityManagerFactory("test_hibernate");
        try
        {
            EntityManager em = emf.createEntityManager();
            try
            {
                EntityTransaction transaction = em.getTransaction();
                transaction.begin();

                try
                {
                    UserEntity user = new UserEntity();
                    user.setDbId(UUID.randomUUID());
                    user.setUserId("user_" + System.nanoTime());

                    em.persist(user);


                    AddressEntity address = new AddressEntity();
                    address.setDbId(UUID.randomUUID());

                    address.setUser(user);
                    user.setAddress(address);

                    em.persist(address);

                    transaction.commit();

                    System.out.println("persisted user: " + user);
                    System.out.println("persisted address: " + address);
                }
                catch(Exception e)
                {
                    e.printStackTrace();
                    transaction.rollback();
                }
            }
            finally
            {
                em.close();
            }


            EntityManager em2 = emf.createEntityManager();
            try
            {
                List<UserEntity> userList = em2.createQuery("select x from UserEntity x", UserEntity.class).getResultList();
                userList.forEach(x -> System.out.println("loaded user: " + x));

                List<AddressEntity> addressList = em2.createQuery("select x from AddressEntity x", AddressEntity.class).getResultList();
                addressList.forEach(x -> System.out.println("loaded address: " + x));
            }
            finally
            {
                em2.close();
            }
        }
        finally
        {
            emf.close();
        }

        System.out.println("ok");
    }
}

that produced these results:

persisted user: UserEntity: dbId=a22db668-eda0-4de1-83ae-98a7cd8738bd, userId=user_789235935853200, address=c17c0c28-603e-4961-90b0-16232346e47b
persisted address: AddressEntity: dbId=c17c0c28-603e-4961-90b0-16232346e47b, user=a22db668-eda0-4de1-83ae-98a7cd8738bd

loaded user: UserEntity: dbId=a22db668-eda0-4de1-83ae-98a7cd8738bd, userId=user_789235935853200, address=c17c0c28-603e-4961-90b0-16232346e47b
loaded address: AddressEntity: dbId=c17c0c28-603e-4961-90b0-16232346e47b, user=a22db668-eda0-4de1-83ae-98a7cd8738bd

Answer 2

one-one relationship define like:

class UserEntity {
  ....

  @Column(length = 20)
  private String userId;

  @OneToOne(cascade = CascadeType.ALL)
  @JoinColumn(name = "userId", referencedColumnName = "userId", insertable = false, updatable = false)
  private AddressEntity address;

}

class AddressEntity {
  ....

  @Column(length = 20)
  private String userId;

  .....
}