DEV Community

Golov Pavel
Golov Pavel

Posted on • Updated on

Make a query with sub condition for child list via Spring Data JPA

Introduction

Recently I faced this situation when I needed to query an Entity with non-deleted children via Spring Data JPA and Hibernate.

Let's look at the domain model of my test application:

@Entity
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    private Long id;

    @OneToMany(mappedBy = "user", cascade = CascadeType.ALL)
    private List<Item> items;

    ... getters, setters, equals, and hashcode.
}

@Entity
public class Item {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    private Long id;

    @ManyToOne
    @JoinColumn(name = "user")
    private User user;

    private Boolean deleted;

    ... getters, setters, equals, and hashcode.
}
Enter fullscreen mode Exit fullscreen mode

The question was: how can I query all the users with non-deleted items?

The first idea was to use JPQL and @Query annotation inside the Spring Data CrudRepository. I wrote the next query:

public interface UserRepository extends CrudRepository<User, Long> {

    @Query("from User u left join u.items i where i.deleted = false or i.deleted is null")
    List<User> findUserWithNonDeletedItems();

}
Enter fullscreen mode Exit fullscreen mode

Testing

The test code is:

@SpringBootApplication
public class DemoApplication implements CommandLineRunner {

    @Autowired
    private UserService userService;

    @Autowired
    private UserRepository userRepository;

    public static void main(String[] args) {
        SpringApplication.run(DemoApplication.class, args);
    }

    @Override
    public void run(String... args) throws Exception {
        userService.createUser();
        userService.makeQuery();
    }
}

@Service
public class UserService {

    private final UserRepository userRepository;

    public UserService(UserRepository userRepository) {
        this.userRepository = userRepository;
    }

    @Transactional
    public void makeQuery() {
        var result = userRepository.findUsersWithNonDeletedItems();
        assert result.get(0).getItems().size() == 1;
    }

    @Transactional
    public void createUser() {
        User userWithItems = new User();

        var items = List.of(
                new Item(userWithItems, false),
                new Item(userWithItems, true)
        );

        userWithItems.setItems(items);
        userRepository.save(userWithItems);

        User userWithoutItems = new User();
        userRepository.save(userWithoutItems);
    }
}
Enter fullscreen mode Exit fullscreen mode

When I ran this code, I received AssertionError, because findUsersWithNonDeletedItems() returned 2 items for userWithItems, including a deleted item. The reason for this appearance is the absence of the fetch keyword.

Let's explain the difference between left join and left join fetch queries.

Left join

If we make the next JPQL request:

from User u
left join u.items i
where i.deleted = false or i.deleted is null
Enter fullscreen mode Exit fullscreen mode

Hibernate is going to generate the following SQL statement:

SELECT u.*
FROM user u
LEFT OUTER JOIN item i ON i.user_id = u.id
WHERE i.deleted = false OR i.deleted is null
Enter fullscreen mode Exit fullscreen mode

It never requests items for every user. As result, it makes an additional query for receiving all user's items, which doesn't contain a deleted filter.

Left join fetch

If we make the next JPQL request:

from User u
left join fetch u.items i
where i.deleted = false or i.deleted is null
Enter fullscreen mode Exit fullscreen mode

Hibernate is going to generate the following SQL statement:

SELECT u.*, i.*
FROM user u
LEFT OUTER JOIN item i ON i.user_id = u.id
WHERE i.deleted = false OR i.deleted is null
Enter fullscreen mode Exit fullscreen mode

In this query hibernate loads users with their items and filters items by deleted column. As result, we receive users with deleted items.

Solution

Result query looks like this:

public interface UserRepository extends CrudRepository<User, Long> {

    @Query("from User u left join fetch u.items i where i.deleted = false or i.deleted is null")
    List<User> findUsersWithNonDeletedItems();

}
Enter fullscreen mode Exit fullscreen mode

It requests users with only non deleted items.

Top comments (1)

Collapse
 
rontran profile image
Ron Tran

Hi @golovpavel, thanks for your post. Where did you find the JPQL resource? It's mentioned in a lot of places on the internet, but everyone only uses the SQL syntax, not like yours:

FROM User u LEFT JOIN FETCH u.items
Enter fullscreen mode Exit fullscreen mode

It's concise and I would love to learn about it. Could you give me some resources? Thanks 😁