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.
}
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();
}
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);
}
}
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
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
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
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
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();
}
It requests users with only non deleted items.
Top comments (1)
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:
It's concise and I would love to learn about it. Could you give me some resources? Thanks 😁