Left Join in Spring Data JPA's Specification
10,460
Specify join type:
town = '%' + StringUtils.lowerCase(town) + '%';
return cb.or(
cb.like(cb.lower(root.join("billingAddress", JoinType.LEFT).get("town")), town),
cb.like(cb.lower(root.join("shippingAddress", JoinType.LEFT).get("town")), town));
Related videos on Youtube
Comments
-
maxxyme almost 2 years
Assume I'm having the following class: (simplified to the extreme)
@Entity @Table(name = "USER") public class User { @OneToOne(mappedBy = "user", cascade = CascadeType.ALL) private BillingAddress billingAddress; @OneToOne(mappedBy = "user", cascade = CascadeType.ALL) private ShippingAddress shippingAddress; // This one CAN be null }
and both
*Address
inherit from this abstract: (again, it's extra-simplified)public abstract class Address { @OneToOne(optional = false, fetch = FetchType.LAZY) @JoinColumn(name = "USER_ID") private User user; @NotEmpty @Size(max = 32) @Column(name = "ADDR_TOWN") private String town; }
I tried the JPA Specifications, as explained by Spring's blog post:
/** * User specifications. * * @see <a href="https://spring.io/blog/2011/04/26/advanced-spring-data-jpa-specifications-and-querydsl">Advanced Spring Data JPA - Specifications and Querydsl</a> */ public class UserSpecifications { public static Specification<User> likeTown(String town) { return new Specification<User>() { @Override public Predicate toPredicate(Root<User> root, CriteriaQuery<?> query, CriteriaBuilder cb) { return cb.like(cb.lower(root.get("billingAddress").get("town")), '%' + StringUtils.lowerCase(town) + '%'); } }; }
Using this "specification" as follow:
List<User> users = userRepository.findAll(UserSpecifications.likeTown(myTown));
But now, I also want to search the town for the shippingAddress, which might not exist. I tried combining both
cb.like
in acb.or
but it turned out the resulting SQL query had an INNER JOIN for the shippingAddress, which is incorrect because, as said above, it might be null, so I'd like a LEFT JOIN.How to do that?
Thanks.