Spring Jpa Specification Subquery without cross join

Question

I have a problem with subquery and JPA that took me a lot of time and I can't find a direct solution, so I used two different queries to solve it.

With Spring and JPA I have two entities similar to this:

public Class Entity{
    @Id
    private Long id;
    @OneToMany(mappedBy = "entity", fetch = FetchType.LAZY)
    private Set<SecondEntity> secondEntities;
}

public class SecondEntity{
    
    @Id
    private Long id;
    
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn
    private Entity entityId;
    
    @Column
    private Integer number;
}

And I want to use JPA and specification to run a query like this:

SELECT *
FROM Entity
WHERE id IN (SELECT id FROM Entity LEFT JOIN SecondEntity ON Entity.id = SecondEntity.entityId WHERE number = ?)

Please, don't focus on another way to solve it, but help me to understand if we can make Hibernate do exactly what I want.

The problem comes because initializing subroot like this:

Subquery<Entity> subquery = query.subquery(Entity.class);
Root<Entity> subroot = subquery.from(Entity.class);

The method from() causes Hibernate to add a crossJoin to the query and it appears like this:

SELECT *
FROM Entity
WHERE id IN (SELECT id FROM Entity LEFT OUTER JOIN SecondEntity ON Entity.id = SecondEntity.entityId CROSS JOIN Employee WHERE crossjoincondition AND number = ?)

Does anyone know how to initialize subquery with another method to avoid crossJoin? I know other implementations, like doing a different query and mixing the result, or writing an @Query dedicated can work better, but I just want to know how to manage this for future purposes?

The only solution I could find is one that recommends removing the @JoinColumn annotation and the mappedBy attribute, but I can't modify this mapping because I need it for another method.

Answer 1

There is an easy way of doing this,

You can use joins in specifications like below.

In my code, I have used INNER join, you can use your own type of join

public static Specification<Post> byTagNames(String[] tagNames) {
        return (root, query, criteriaBuilder) -> {
            if (tagNames == null || tagNames.length > 0) {
                return criteriaBuilder.conjunction();
            }
            Join<Post, Tag> postTagsTable = root.join("tags", JoinType.INNER);
            return postTagsTable.<String>get("name").in(tagNames);
        };
    }

I hope this helps!