Join partial composite key in JPA Hibernate

Question

I'm using JPA to define a datamodel for a database with composite keys. I cannot change the database.

I have got a Car entity with a composite key (carType and carId). A second (optional) PorscheInfo entity contains additional information for Porsche cars. Unfortunately, the corresponding "porsche_info" table does not contain a column containing the carType information as its entries exclusively refer to CarType = 'Porsche'.

The SQL for this operation is straightforward:

SELECT *
FROM cars
LEFT JOIN porsche_info
ON cars.CarId = porsche_info.CarId
    AND cars.CarType = 'Porsche'

How can I translate this into a proper JPA setup?

So far, I have got the following entity classes:

@Embeddable
public class CarKey {
    private String carType;

    private String carId;
}

@Entity(name = "cars")
public class Car {
    @EmbeddedId
    private CarKey key;

    // car information

    @OneToOne
    @JoinColumn(name = "CarId", referencedColumnName = "CarId")
    private PorscheInfo porscheInfo;
    // or
    @OneToMany
    @JoinColumn(name = "CarId", referencedColumnName = "CarId")
    private Set<PorscheInfo> porscheInfo;
}

@Embeddable
public class PorscheInfoKey {
    private String carId;
}

@Entity (name = "porsche_info")
public class PorscheInfo {
    @EmbeddedId
    private PorscheInfoKey key;

    // porsche info
}

A @OneToOne solution would be prefered, but a @OneToMany solution (due to different key classes) is viable, too.

The CriteriaQuery looks like:

CriteriaBuilder cb = entityManager.getCriteriaBuilder();

CriteriaQuery<Car> carQuery = cb.createQuery(Car.class).distinct(true);
Root<Car> carRoot = carQuery.from(Car.class);

carQuery.select(carRoot);
carRoot.fetch("porscheInfo", JoinType.LEFT);

• In the @OneToOne approach, I get: org.hibernate.MappingException: Repeated column in mapping for entity: Car column: CarId (should be mapped with insert="false" update="false")
• In the @OneToMany approach, I get: org.hibernate.AnnotationException: referencedColumnNames(CarId) of PorscheInfo.porscheInfo referencing Car not mapped to a single property

How should a proper JPA setup look like? I don't know how to tell Hibernate which columns have to be joined. I thought of .multiselect() or Join<Car, PorscheInfo> ... .on() approaches, but did not get it to work.

edit: I got the @OneToOne approach to work with a caveat:

@OneToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "CarId", referencedColumnName = "CarId", insertable = false, updatable = false)
private PorscheInfo porscheInfo;

The strange behaviour is: All Porsche cars have their porscheInfo property properly initialized due to the carRoot.fetch("porscheInfo", JoinType.LEFT);. However, all Non-Porsche cars throw a LazyInitializationException when I try to access their porscheInfo property. Eager fetching is not desired since it results in an additional unnecessary query for every car (1+N queries).

Jealous

Yes, carId is only unique per carType. That's why carType is part of CarKey. The carIdis unique for a specific carType, e.g. "Porsche". – Jealous

Both key classes include more properties than shown in the example code above. I omitted them to foreground my question. The database is designed in the way that there is a common "cars" table and some tables with addition information for specific CarTypes, e.g., "porsche_info", "audi_info", "bmw_info". I know that the plain SQL query is simple, so I already included it in my question. I just need advice how to transfer it to JPA/CriteriaBuilder. Thank you! – Jealous

I'm facing exactly the same problem. Do you have any solution? – Cyprian