DEV Community

loading...

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

golovpavel profile image Golov Pavel Updated on ・2 min read

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.

Discussion

pic
Editor guide