How can I use CriteriaBuilder to perform a join with more than one condition on it?

Viewed 38

I have two JPA entity classes (MyEntity and Attribute) and would like to know how to use CriteriaBuilder to perform an inner join with more than one condition on it.

I would like this:

SELECT e.id
FROM my_entity e
INNER JOIN my_attribute a
ON e.id = a.event_id
AND a.key = "special-key"   // I want to add this part.

Instead of just this:

SELECT e.id
FROM my_entity e
INNER JOIN my_attribute a
ON e.id = a.event_id

Which I can generate from the following CriteriaBuilder code:

var cb = entityManager.getCriteriaBuilder();
var cq = cb.createQuery(Event.class);

var entity = cq.from(MyEntity.class);
entity.join(MyEntity_.attributes);
cq.select(entity);

return entityManager.createQuery(cq).getResultList();

The classes in question are:

MyEntity class:

@Entity
public class MyEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @OneToMany
    @JoinColumn(name = "my_attribute_id")
    private List<MyAttribute> attributes;

MyAttribute class:

@Entity
public class MyAttribute {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String key;
    private String value;

    @ManyToOne(optional = false, fetch = FetchType.LAZY)
    private MyEntity entity;

How can I generate the first snippet with the extra AND a.key = "special-key" using CriteriaBuilder?

Note that with my example of an inner join I could use the code below but I would like to know how to specifically add the AND condition to the join.

var cb = entityManager.getCriteriaBuilder();
var cq = cb.createQuery(Event.class);

var entity = cq.from(MyEntity.class);
entity.join(MyEntity_.attributes);

// Add these three lines.
var join = event.join(MyEntity_.attributes);
var pred = cb.equal(join.get(MyAttribute_.key), "special-key");
cq.where(pred);

cq.select(entity);

return entityManager.createQuery(cq).getResultList();
0 Answers
Related