Left Join in Spring Data JPA's Specification

Question

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 a cb.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.

Answer 1

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));

Answer 2

Don't know if it helps.

I had the same problem. The only way I could solve it was to use a subquery.

For instance this would resemble something like that :

JPASubQuery subquery = new JPASubQuery(); 
subquery = subquery .from( /* tableB */);
subquery .where(/* conditions */);

Then use i add the subquery to the predicate :

predicate.and(subquery.exists());

NB : In my case it helped as i am extensively using Specifications. In most cases, the performance impact didn't seem that great.

EDIT : I just realized that the former example worked only in my case as i'm using query-dsl.

In your case, have a look at JPA 2.0, Criteria API, Subqueries, In Expressions to create a subquery and join it to your predicate conditions.