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)