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:


<span>@Entity</span>
<span>public</span> <span>class</span> <span>User</span> <span>{</span>
    <span>@Id</span>
    <span>@GeneratedValue</span><span>(</span><span>strategy</span> <span>=</span> <span>GenerationType</span><span>.</span><span>SEQUENCE</span><span>)</span>
    <span>private</span> <span>Long</span> <span>id</span><span>;</span>
    <span>@OneToMany</span><span>(</span><span>mappedBy</span> <span>=</span> <span>"user"</span><span>,</span> <span>cascade</span> <span>=</span> <span>CascadeType</span><span>.</span><span>ALL</span><span>)</span>
    <span>private</span> <span>List</span><span><</span><span>Item</span><span>></span> <span>items</span><span>;</span>
    <span>...</span> <span>getters</span><span>,</span> <span>setters</span><span>,</span> <span>equals</span><span>,</span> <span>and</span> <span>hashcode</span><span>.</span>
<span>}</span>
<span>@Entity</span>
<span>public</span> <span>class</span> <span>Item</span> <span>{</span>
    <span>@Id</span>
    <span>@GeneratedValue</span><span>(</span><span>strategy</span> <span>=</span> <span>GenerationType</span><span>.</span><span>SEQUENCE</span><span>)</span>
    <span>private</span> <span>Long</span> <span>id</span><span>;</span>
    <span>@ManyToOne</span>
    <span>@JoinColumn</span><span>(</span><span>name</span> <span>=</span> <span>"user"</span><span>)</span>
    <span>private</span> <span>User</span> <span>user</span><span>;</span>
    <span>private</span> <span>Boolean</span> <span>deleted</span><span>;</span>
    <span>...</span> <span>getters</span><span>,</span> <span>setters</span><span>,</span> <span>equals</span><span>,</span> <span>and</span> <span>hashcode</span><span>.</span>
<span>}</span>
<span>@Entity</span>
<span>public</span> <span>class</span> <span>User</span> <span>{</span>

    <span>@Id</span>
    <span>@GeneratedValue</span><span>(</span><span>strategy</span> <span>=</span> <span>GenerationType</span><span>.</span><span>SEQUENCE</span><span>)</span>
    <span>private</span> <span>Long</span> <span>id</span><span>;</span>

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

    <span>...</span> <span>getters</span><span>,</span> <span>setters</span><span>,</span> <span>equals</span><span>,</span> <span>and</span> <span>hashcode</span><span>.</span>
<span>}</span>

<span>@Entity</span>
<span>public</span> <span>class</span> <span>Item</span> <span>{</span>

    <span>@Id</span>
    <span>@GeneratedValue</span><span>(</span><span>strategy</span> <span>=</span> <span>GenerationType</span><span>.</span><span>SEQUENCE</span><span>)</span>
    <span>private</span> <span>Long</span> <span>id</span><span>;</span>

    <span>@ManyToOne</span>
    <span>@JoinColumn</span><span>(</span><span>name</span> <span>=</span> <span>"user"</span><span>)</span>
    <span>private</span> <span>User</span> <span>user</span><span>;</span>

    <span>private</span> <span>Boolean</span> <span>deleted</span><span>;</span>

    <span>...</span> <span>getters</span><span>,</span> <span>setters</span><span>,</span> <span>equals</span><span>,</span> <span>and</span> <span>hashcode</span><span>.</span>
<span>}</span>
@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:


<span>public</span> <span>interface</span> <span>UserRepository</span> <span>extends</span> <span>CrudRepository</span><span><</span><span>User</span><span>,</span> <span>Long</span><span>></span> <span>{</span>
    <span>@Query</span><span>(</span><span>"from User u left join u.items i where i.deleted = false or i.deleted is null"</span><span>)</span>
    <span>List</span><span><</span><span>User</span><span>></span> <span>findUserWithNonDeletedItems</span><span>();</span>
<span>}</span>
<span>public</span> <span>interface</span> <span>UserRepository</span> <span>extends</span> <span>CrudRepository</span><span><</span><span>User</span><span>,</span> <span>Long</span><span>></span> <span>{</span>

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

<span>}</span>
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:


<span>@SpringBootApplication</span>
<span>public</span> <span>class</span> <span>DemoApplication</span> <span>implements</span> <span>CommandLineRunner</span> <span>{</span>
    <span>@Autowired</span>
    <span>private</span> <span>UserService</span> <span>userService</span><span>;</span>
    <span>@Autowired</span>
    <span>private</span> <span>UserRepository</span> <span>userRepository</span><span>;</span>
    <span>public</span> <span>static</span> <span>void</span> <span>main</span><span>(</span><span>String</span><span>[]</span> <span>args</span><span>)</span> <span>{</span>
        <span>SpringApplication</span><span>.</span><span>run</span><span>(</span><span>DemoApplication</span><span>.</span><span>class</span><span>,</span> <span>args</span><span>);</span>
    <span>}</span>
    <span>@Override</span>
    <span>public</span> <span>void</span> <span>run</span><span>(</span><span>String</span><span>...</span> <span>args</span><span>)</span> <span>throws</span> <span>Exception</span> <span>{</span>
        <span>userService</span><span>.</span><span>createUser</span><span>();</span>
        <span>userService</span><span>.</span><span>makeQuery</span><span>();</span>
    <span>}</span>
<span>}</span>
<span>@Service</span>
<span>public</span> <span>class</span> <span>UserService</span> <span>{</span>
    <span>private</span> <span>final</span> <span>UserRepository</span> <span>userRepository</span><span>;</span>
    <span>public</span> <span>UserService</span><span>(</span><span>UserRepository</span> <span>userRepository</span><span>)</span> <span>{</span>
        <span>this</span><span>.</span><span>userRepository</span> <span>=</span> <span>userRepository</span><span>;</span>
    <span>}</span>
    <span>@Transactional</span>
    <span>public</span> <span>void</span> <span>makeQuery</span><span>()</span> <span>{</span>
        <span>var</span> <span>result</span> <span>=</span> <span>userRepository</span><span>.</span><span>findUsersWithNonDeletedItems</span><span>();</span>
        <span>assert</span> <span>result</span><span>.</span><span>get</span><span>(</span><span>0</span><span>).</span><span>getItems</span><span>().</span><span>size</span><span>()</span> <span>==</span> <span>1</span><span>;</span>
    <span>}</span>
    <span>@Transactional</span>
    <span>public</span> <span>void</span> <span>createUser</span><span>()</span> <span>{</span>
        <span>User</span> <span>userWithItems</span> <span>=</span> <span>new</span> <span>User</span><span>();</span>
        <span>var</span> <span>items</span> <span>=</span> <span>List</span><span>.</span><span>of</span><span>(</span>
                <span>new</span> <span>Item</span><span>(</span><span>userWithItems</span><span>,</span> <span>false</span><span>),</span>
                <span>new</span> <span>Item</span><span>(</span><span>userWithItems</span><span>,</span> <span>true</span><span>)</span>
        <span>);</span>
        <span>userWithItems</span><span>.</span><span>setItems</span><span>(</span><span>items</span><span>);</span>
        <span>userRepository</span><span>.</span><span>save</span><span>(</span><span>userWithItems</span><span>);</span>
        <span>User</span> <span>userWithoutItems</span> <span>=</span> <span>new</span> <span>User</span><span>();</span>
        <span>userRepository</span><span>.</span><span>save</span><span>(</span><span>userWithoutItems</span><span>);</span>
    <span>}</span>
<span>}</span>
<span>@SpringBootApplication</span>
<span>public</span> <span>class</span> <span>DemoApplication</span> <span>implements</span> <span>CommandLineRunner</span> <span>{</span>

    <span>@Autowired</span>
    <span>private</span> <span>UserService</span> <span>userService</span><span>;</span>

    <span>@Autowired</span>
    <span>private</span> <span>UserRepository</span> <span>userRepository</span><span>;</span>

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

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

<span>@Service</span>
<span>public</span> <span>class</span> <span>UserService</span> <span>{</span>

    <span>private</span> <span>final</span> <span>UserRepository</span> <span>userRepository</span><span>;</span>

    <span>public</span> <span>UserService</span><span>(</span><span>UserRepository</span> <span>userRepository</span><span>)</span> <span>{</span>
        <span>this</span><span>.</span><span>userRepository</span> <span>=</span> <span>userRepository</span><span>;</span>
    <span>}</span>

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

    <span>@Transactional</span>
    <span>public</span> <span>void</span> <span>createUser</span><span>()</span> <span>{</span>
        <span>User</span> <span>userWithItems</span> <span>=</span> <span>new</span> <span>User</span><span>();</span>

        <span>var</span> <span>items</span> <span>=</span> <span>List</span><span>.</span><span>of</span><span>(</span>
                <span>new</span> <span>Item</span><span>(</span><span>userWithItems</span><span>,</span> <span>false</span><span>),</span>
                <span>new</span> <span>Item</span><span>(</span><span>userWithItems</span><span>,</span> <span>true</span><span>)</span>
        <span>);</span>

        <span>userWithItems</span><span>.</span><span>setItems</span><span>(</span><span>items</span><span>);</span>
        <span>userRepository</span><span>.</span><span>save</span><span>(</span><span>userWithItems</span><span>);</span>

        <span>User</span> <span>userWithoutItems</span> <span>=</span> <span>new</span> <span>User</span><span>();</span>
        <span>userRepository</span><span>.</span><span>save</span><span>(</span><span>userWithoutItems</span><span>);</span>
    <span>}</span>
<span>}</span>
@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:


<span>from</span> <span>User</span> <span>u</span>
<span>left</span> <span>join</span> <span>u</span><span>.</span><span>items</span> <span>i</span>
<span>where</span> <span>i</span><span>.</span><span>deleted</span> <span>=</span> <span>false</span> <span>or</span> <span>i</span><span>.</span><span>deleted</span> <span>is</span> <span>null</span>
<span>from</span> <span>User</span> <span>u</span>
<span>left</span> <span>join</span> <span>u</span><span>.</span><span>items</span> <span>i</span>
<span>where</span> <span>i</span><span>.</span><span>deleted</span> <span>=</span> <span>false</span> <span>or</span> <span>i</span><span>.</span><span>deleted</span> <span>is</span> <span>null</span>
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:


<span>SELECT</span> <span>u</span><span>.</span><span>*</span>
<span>FROM</span> <span>user</span> <span>u</span>
<span>LEFT</span> <span>OUTER</span> <span>JOIN</span> <span>item</span> <span>i</span> <span>ON</span> <span>i</span><span>.</span><span>user_id</span> <span>=</span> <span>u</span><span>.</span><span>id</span>
<span>WHERE</span> <span>i</span><span>.</span><span>deleted</span> <span>=</span> <span>false</span> <span>OR</span> <span>i</span><span>.</span><span>deleted</span> <span>is</span> <span>null</span>
<span>SELECT</span> <span>u</span><span>.</span><span>*</span>
<span>FROM</span> <span>user</span> <span>u</span>
<span>LEFT</span> <span>OUTER</span> <span>JOIN</span> <span>item</span> <span>i</span> <span>ON</span> <span>i</span><span>.</span><span>user_id</span> <span>=</span> <span>u</span><span>.</span><span>id</span>
<span>WHERE</span> <span>i</span><span>.</span><span>deleted</span> <span>=</span> <span>false</span> <span>OR</span> <span>i</span><span>.</span><span>deleted</span> <span>is</span> <span>null</span>
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:


<span>from</span> <span>User</span> <span>u</span>
<span>left</span> <span>join</span> <span>fetch</span> <span>u</span><span>.</span><span>items</span> <span>i</span>
<span>where</span> <span>i</span><span>.</span><span>deleted</span> <span>=</span> <span>false</span> <span>or</span> <span>i</span><span>.</span><span>deleted</span> <span>is</span> <span>null</span>
<span>from</span> <span>User</span> <span>u</span>
<span>left</span> <span>join</span> <span>fetch</span> <span>u</span><span>.</span><span>items</span> <span>i</span>
<span>where</span> <span>i</span><span>.</span><span>deleted</span> <span>=</span> <span>false</span> <span>or</span> <span>i</span><span>.</span><span>deleted</span> <span>is</span> <span>null</span>
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:


<span>SELECT</span> <span>u</span><span>.</span><span>*</span><span>,</span> <span>i</span><span>.</span><span>*</span>
<span>FROM</span> <span>user</span> <span>u</span>
<span>LEFT</span> <span>OUTER</span> <span>JOIN</span> <span>item</span> <span>i</span> <span>ON</span> <span>i</span><span>.</span><span>user_id</span> <span>=</span> <span>u</span><span>.</span><span>id</span>
<span>WHERE</span> <span>i</span><span>.</span><span>deleted</span> <span>=</span> <span>false</span> <span>OR</span> <span>i</span><span>.</span><span>deleted</span> <span>is</span> <span>null</span>
<span>SELECT</span> <span>u</span><span>.</span><span>*</span><span>,</span> <span>i</span><span>.</span><span>*</span>
<span>FROM</span> <span>user</span> <span>u</span>
<span>LEFT</span> <span>OUTER</span> <span>JOIN</span> <span>item</span> <span>i</span> <span>ON</span> <span>i</span><span>.</span><span>user_id</span> <span>=</span> <span>u</span><span>.</span><span>id</span>
<span>WHERE</span> <span>i</span><span>.</span><span>deleted</span> <span>=</span> <span>false</span> <span>OR</span> <span>i</span><span>.</span><span>deleted</span> <span>is</span> <span>null</span>
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:


<span>public</span> <span>interface</span> <span>UserRepository</span> <span>extends</span> <span>CrudRepository</span><span><</span><span>User</span><span>,</span> <span>Long</span><span>></span> <span>{</span>
    <span>@Query</span><span>(</span><span>"from User u left join fetch u.items i where i.deleted = false or i.deleted is null"</span><span>)</span>
    <span>List</span><span><</span><span>User</span><span>></span> <span>findUsersWithNonDeletedItems</span><span>();</span>
<span>}</span>
<span>public</span> <span>interface</span> <span>UserRepository</span> <span>extends</span> <span>CrudRepository</span><span><</span><span>User</span><span>,</span> <span>Long</span><span>></span> <span>{</span>

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

<span>}</span>
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.

原文链接：Make a query with sub condition for child list via Spring Data JPA

展开阅读全文

文章版权声明 1、本网站名称：拾光赋
2、本站永久网址：https://www.blogs.ink
3、本网站的文章部分内容可能来源于网络，仅供大家学习与参考，如有侵权，请联系站长QQ：805375623进行删除处理。
4、本站一切资源不代表本站立场，并不代表本站赞同其观点和对其真实性负责。
5、本站一律禁止以任何方式发布或转载任何违法的相关信息，访客发现请向站长举报
6、本站资源大多存储在云盘，如发现链接失效，请联系我们我们会第一时间更新。

THE END