I have a Spring Boot project which has three entity classes like the following,
@Entity
@Table("item")
public class Item extends SomeParent {
@OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER, orphanRemoval = true, mappedBy = "item")
@JsonManagedReference("product-s")
Set<Product> products;
}
@Entity
@Table(name = "product")
public class Product extends SomeParent {
@ManyToOne
@JoinColumn(name = "item_id", nullable = false)
@JsonBackReference("product-s")
private Item item;
@ManyToOne
@JoinColumn(name = "fruit_id", nullable = false)
private Fruit fruit;
private String type;
}
@Entity
@Table(name = "fruit")
public class Fruit extends SomeParent {
private String name;
private Integer qty;
}
I am using JPA Specification to build a query to find items
by fruit.name
and product.type
. Following are my search queries,
1. fruit = mango, type = A and fruit = apple, type = B
2. fruit = mango, type = A or fruit = apple, type = B
By the 1st query it needs to search all the items
by fruit.name = mango and product.type = A
and fruit.name = apple and product.type = B
. Item must have both mango with A category and apple with B category.
By the 2nd query it needs to search all the items
by fruit.name = mango and product.type = A
or fruit.name = apple and product.type = B
. If any item has mango with A category or apple with B category then it should return the item
public Specification<Item> search() {
return (root, query, criteriaBuilder) -> {
SetJoin<Item, Product> productJoin = root.joinSet("products", JoinType.LEFT);
if (isAndQuery) {
criteriaBuilder.and(
criteriaBuilder.and(
criteriaBuilder.equal(productJoin.get("fruit").get("name"), "mango"),
criteriaBuilder.equal(productJoin.get("type"), "A")));
criteriaBuilder.and(
criteriaBuilder.and(
criteriaBuilder.equal(productJoin.get("fruit").get("name"), "apple"),
criteriaBuilder.equal(productJoin.get("type"), "B")));
} else {
criteriaBuilder.or(
criteriaBuilder.and(
criteriaBuilder.equal(productJoin.get("fruit").get("name"), "mango"),
criteriaBuilder.equal(productJoin.get("type"), "A")));
criteriaBuilder.or(
criteriaBuilder.and(
criteriaBuilder.equal(productJoin.get("fruit").get("name"), "apple"),
criteriaBuilder.equal(productJoin.get("type"), "B")));
}
return criteriaBuilder;
};
}
My issue is and query
is not working, so it is not giving me the results. So can anybody help me to resolve this issue? What is I am doing wrong here?
This is the JPA generated AND
query,
select * from item item
left outer join product product on item.id=product.item_id
cross join fruit fruit
where product.fruit_id=fruit.id
and ((fruit.name='mango')
and product.type='A'
and (fruit.name='apple')
and product.type='B')
order by item.id asc limit ?
This is the JPA generated OR
query,
select * from item item
left outer join product product on item.id=product.item_id
cross join fruit fruit
where product.fruit_id=fruit.id
and ((fruit.name='mango')
and product.type='A'
or (fruit.name='apple')
and product.type='B')
and 1=1
order by
item.id asc limit ?
CodePudding user response:
AND won't work because you are performing the filter check on the same product/fruit as the other checks. A product's fruit can't be both an apple and a mango. The solution is to manually create two separate, explicit joins
public Specification<Item> search() {
return (root, query, criteriaBuilder) -> {
Predicate returnPredicate;
SetJoin<Item, Product> productJoin = root.joinSet("products", JoinType.LEFT);
if (isAndQuery) {
SetJoin<Item, Product> productJoin2 = root.joinSet("products", JoinType.LEFT);
returnPredicate = criteriaBuilder.and(
criteriaBuilder.and(
criteriaBuilder.equal(productJoin.get("fruit").get("name"), "mango"),
criteriaBuilder.equal(productJoin.get("type"), "A"))),
criteriaBuilder.and(
criteriaBuilder.equal(productJoin2.get("fruit").get("name"), "apple"),
criteriaBuilder.equal(productJoin2.get("type"), "B")));
} else {
returnPredicate = criteriaBuilder.or(
criteriaBuilder.and(
criteriaBuilder.equal(productJoin.get("fruit").get("name"), "mango"),
criteriaBuilder.equal(productJoin.get("type"), "A"))),
criteriaBuilder.and(
criteriaBuilder.equal(productJoin.get("fruit").get("name"), "apple"),
criteriaBuilder.equal(productJoin.get("type"), "B")));
}
return returnPredicate;
};
}
This translates more into give me the Item that has both a product with an Apple and a separate product with a mango.