joinjpqlcriteriaquery

Adding a join to a JPA CriteriaQuery

I have a CriteriaQuery on my FlowStep object.

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<FlowStep> cbQuery = cb.createQuery(FlowStep.class);
Root<FlowStep> flowStep = cbQuery.from(FlowStep.class);


List<Predicate> predicates = new ArrayList<>();

Then I add many predicates depending on what I want to filter.

// add all the conditions to query
cbQuery.select(flowStep).where(predicates.toArray(new Predicate[0]));

I even add sorting stuff.

cbQuery.orderBy(orders);

Query query = em.createQuery(cbQuery);

List<FlowStep> resultList = query.getResultList();

But I need to add a join like this:

select * from flowstep fs join flowinstance fi on fi.id = fs.flowinstanceid and fi.domainid = 'Test'

so I only want to return the flow steps matching the criteria AND which are in the Test domain, and domain information is in table flowinstance. How do you add join to a CriteriaQuery?

I saw something like

Join<FlowStep, UUID> flowStepFlowInstanceJoin = flowStep.join(FlowInstance_.id);

but then I need to add the condition on the domain being equal to a value.

Is it possible to use a join like the above on a JPQL criteria query?

The initial answer was to add this before the Predicate list:

Join<FlowStep, FlowInstance> flowStepFlowInstanceJoin = flowStep.join("id", JoinType.LEFT);
flowStepFlowInstanceJoin.on(cb.equal(flowStepFlowInstanceJoin.get("domainid"), domain));

FlowStep has a flowinstanceid column and field, and flowinstance has an id field. This compiles but does not work. I get an error "Cannot join to attribute of basic type". So there needs to be a one-to-many relationship between FlowStep and FlowInstance? A FlowInstance has many flow steps so maybe

@Column(name = flowinstanceid)
private UUID flowInstanceId;

in FlowStep class needs to be changed to a JoinColumn? And add a OneToMany or ManyToOne relationship to make the above JOIN possible?

Solution

  • The solution was suggested by CriteriaBuilder join two tables with a custom condition and JPA many-to-one relation - need to save only Id.

    In class FlowStep, we need to add the FlowInstance object and a ManyToOne annotation:

    @JoinColumn(name = FLOW_INSTANCE_ID, insertable = false, updatable = false)
@ManyToOne(targetEntity = FlowInstance.class, fetch = FetchType.EAGER)
@JsonIgnore
private FlowInstance flowInstance;

    Note the class already had a field for the flow instance id of type UUID, but a field of class FlowInstance is necessary, it seems, for the ManyToOne relationship.

    Then when building the JPA query:

    Join<FlowStep, FlowInstance> flowStepFlowInstanceJoin = flowStep.join("flowInstance", JoinType.INNER); 
flowStepFlowInstanceJoin.on(cb.equal(flowStepFlowInstanceJoin.get("domainId"), domain));

    This goes below the

    Root<FlowStep> flowStep = cbQuery.from(FlowStep.class);

    line. This makes it work. It's very important that the join is of type INNER so that it only returns the steps having the wanted domain. LEFT would return steps with a non-wanted domain, and RIGHT would return as many null rows as steps having the wanted domain.