Java Hibernate JPA create an entity and Join two different tables referenced by the same column, wit-CodePudding

I have an issue that want to resolve using just annotations, and not two different query to obtain the data. I need to join two different tables with the same column id. I have Travels, and at certain time, the rows in Travel are erased, but now TravelHistory (a table to save data) have the row with just the data needed to save (and with the same id of Travel... make sense). At certain time, Travel and TravelHistory may coexist.

I want to have the two different classes mapped by same id (travel_id is the name of column), something like this (code is simplified):

@Entity
@Table(name = "travel_audit")
public class TravelAudit {
    @OneToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "travel_id")
    private Travel travel;
    
    @OneToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "travel_id")
    private TravelHistory travelHistory;

}

but Hibernate seems to not like that (having the same name = "travel_id"), so I have errors to create the Bean.

For this answer I put a little more effort, and all detail, so to have more references to the tables:

@Column(name = "travel_id")
private Long travelId;

@OneToOne(targetEntity = Travel.class, fetch = FetchType.LAZY)
@JoinColumn(name = "travel_id", table = "travel")
private Travel travel;

@OneToOne(targetEntity = TravelHistory.class, fetch = FetchType.LAZY)
@JoinColumn(name = "travel_id", table = "travel_history")
private TravelHistory travelHistory;

But it didn't work.

After that I want to make an Optional<TravelAudit> findByTravelId(Long travelId); and obtain Travel and/or TravelHistory

How can I do this? Unfortunately I think I'm going to have to retrieve the travel_id (it's a Long) and get the other tables separately.

The error is:

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaConfiguration.class]: Invocation of init method failed; nested exception is javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build Hibernate SessionFactory

CodePudding user response：

The mapping for your audit Entity might be:

@Entity
@Table(name = "travel_audit")
public class TravelAudit {
    @Id
    @Column(name="travel_id")
    private String travelId;

    @OneToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "travel_id", insertable=false, updatable=false)
    private Travel travel;
    
    @OneToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "travel_id", insertable=false, updatable=false)
    private TravelHistory travelHistory;
}

If you are using JPA/Hibernate to generate the DDL, you'll want to make sure there isn't a constraint generated for the travel_audit.travel_id -> travel table reference, as it will get broken any time your app tries to delete travel rows still referenced by a travel audit instance.

Having a basic mapping (travelId) will allow you to query using the value without having to join between the two tables and complicate your queries as the default inner join would break the logic you need.