JPA CriteriaQuery join only entities which match the Criteria

Question

I have been working on a code to build a Criteria Query to do a conditional join. But its not quit correct. Please consider following example:

I have 2 Entities Owner and Car. Owner has OneToMany relation with Car.

On code Owner entity has List of Cars.(Using @OneToMany with FetchType.LAZY)

Owner.java:

@Entity
public class Owner {

    @Id
    @GeneratedValue(strategy=GenerationType.SEQUENCE)
    private int id;
    private String name;
    private String email;

    @OneToMany(mappedBy="owner",fetch=FetchType.LAZY)
    private List<Car>  cars;

    ...
}

Car.java

@Entity
public class Car{

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    private int id;
    private String manufacturer;
    private String regnumber;

    @ManyToOne
    @JoinColumn(name="ownerid")
    private Owner owner;
    ...
}

Now my requirement is simple I want to get all the Owners of TESLA cars. Following code works good for that:

CriteriaQuery<Owner> cq = cb.createQuery(Owner.class);
Root<Owner> rootowner = cq.from(Owner.class);
rootowner.fetch("cars");

cq.distinct(true);

Join<Owner, Car> carjoin= rootowner.join(Owner_.cars);
Expression<String> carmanExp = carjoin.get(Car_.manufacturer);
Predicate p = cb.like(carmanExp, "TESLA");
cq.where(p);

TypedQuery<Owner> tq = em.createQuery(cq);

Now my requirement is, if a Owner owns 2 cars, one is TESLA and another is VOLVO. I want Owner entity should contain only TESLA.

How do I do that?

You are selecting `Owner` objects, and if an `Owner` object has those 2 cars then, when the object is loaded, it will have those 2 cars. You will not be returned half an `Owner` object. Ever — , Nov 01 '17 at 13:31
But when you run a normal SQL JOIN Query `SELECT * FROM owner as o JOIN car as c on c.ownerid=o.id WHERE c.manufacturer='TESLA';` it returns only one tuple. IF we can do it in plain old SQL and not on JPA what advantage does JPA has on SQL or JDBC? — Kishor Prakash, Nov 01 '17 at 13:39
If `Owner` JOHN has 2 cars then the object returned as `Owner` JOHN will always have 2 cars because JOHN has 2 cars!; that's how O-O works. If you want to do flat SQL queries and manipulate them into an object with only part of that information then go for it, but JPA is designed for O-O, with an object having an identity and relations. — , Nov 01 '17 at 13:41
But If I run the same query using JPQL as `TypedQuery tq = em.createQuery("SELECT o FROM Owner o JOIN FETCH o.cars c WHERE c.manufacturer='TESLA'",Owner.class);` which will give me `Owner` object with ONLY ONE CAR. O-O works good here. All I need is an equivalent of this JPQL in `Criteria` API. I know it can be done because I have heard people have done it but they're unavailable for me ask right now. — Kishor Prakash, Nov 01 '17 at 14:27
Your 'normal' SQL query still returns multiple results for an owner - one row for each Tesla. But note, your SQL query is focused on the Car, not the owner. In your JPA example, you are getting the full Owner instance and ignoring the cars. You have to remember in JPA that the query criteria is only used to select the resulting object returned, not how they are built. Building objects is based on your mappings, not your queries, so the owner.getCars relationship is always required to reflect what is actually in the database according to the mapping. — Chris, Nov 01 '17 at 15:00

score 0 · Answer 1 · answered Nov 01 '17 at 14:56

You cannot do what you are asking in a simple JPA query on the fly, as that is a manipulation of the JPA Entity in such a way that it no longer represents what is in the database. Put another way, when JPA gives you back the Owner referencing just the Tesla, what do you want it to do with the now removed Volvo when it synchronizes with the database?

In your case, the easiest solution would be to just query for the cars you want instead, as they have a reference to their owners. You will get duplicates where an owner may have two or more teslas - you can work out this problem by creating a map of owners to cars with the results yourself.

JPA CriteriaQuery join only entities which match the Criteria

1 Answers1