Implementing OR condition on jpa criteria join

26 views Asked by At

I am creating a spring boot application and I want to implement an or condition in a JPA Criteria query such that the resulting query consists of:

INNER JOIN CUSTOMER c ON t1.CUSTOMER_ID = c.CUSTOMER_ID OR t1.DEST_ID = c.CUSTOMER_ID

(t1 is another table SALES_ORDER)

Without using the JPA criteria API results in:

inner join customer customermo3_ on salesorder0_.customer_id=customermo3_.customer_id and salesorder0_.dest_id=customermo3_.customer_id

What I want is an OR condition.

Note that my field in the SalesOrderModel class is:

@OneToOne(fetch = FetchType.LAZY)
@Fetch(FetchMode.JOIN)
@JoinColumn(name = "CUSTOMER_ID", referencedColumnName = "CUSTOMER_ID", insertable = false, updatable = false)
@JoinColumn(name = "DEST_ID", referencedColumnName = "CUSTOMER_ID", insertable = false, updatable = false)
private CustomerModel customerModel;

My JPA Criteria code:

Join<SalesOrderModel, CustomerModel> customerJoin = salesOrderRoot.join("customerModel", JoinType.INNER);
customerJoin.on(
        builder.or(
                builder.equal(salesOrderRoot.get("customerId"), customerJoin.get("customerId")),
                builder.equal(salesOrderRoot.get("destId"), customerJoin.get("customerId"))
        )
);

However the query ends up being

inner join customer customermo3_ on salesorder0_.customer_id=customermo3_.customer_id and salesorder0_.dest_id=customermo3_.customer_id and (salesorder0_.customer_id=customermo3_.customer_id or salesorder0_.dest_id=customermo3_.customer_id)
0

There are 0 answers