How to define custom findAll methods in JpaRepository?

Question

I have two api endpoints:

/api/posts - Fetches list of paginated posts
/api/countries/{country}/posts - Fetches list of paginated posts by country

So I have following entity:

@Data
@Entity
@Table(name = "posts")
@EntityListeners(AuditingEntityListener.class)
@NamedEntityGraph(
    name = "Post.Resource",
    attributeNodes = @NamedAttributeNode("country")
)
public class Post {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    private String title;
    private String body;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "country_id")
    private Country country;

    @Column(name = "published_at")
    private LocalDateTime publishedAt;

    @CreatedDate
    @Column(name = "created_at")
    private LocalDateTime createdAt;

    @LastModifiedDate
    @Column(name = "updated_at")
    private LocalDateTime updatedAt;

    public boolean isPublished() {
        return publishedAt != null;
    }

    public boolean isDraft() {
        return !isPublished();
    }
}

And I have defined following repository. Note that how I am defining entity graph for last two methods, and I need it that way, because I do not want to override findAll() method, because in the future I will need to load posts without any relations. Also another thing I want it to be predicate, so that in various different services I can reuse method, and not create method for every single query...

@Repository
public interface PostRepository extends JpaRepository<Post, Long>, 
    JpaSpecificationExecutor<Post>, 
    QuerydslPredicateExecutor<Post> 
{
    @EntityGraph("Post.Resource")
    Optional<Post> findPostResourceById(long id);

    @Query("SELECT post FROM Post post")
    @EntityGraph("Post.Resource")
    Page<Post> findAllPostResources(Pageable pageable);

    @Query("SELECT post FROM Post post")
    @EntityGraph("Post.Resource")
    Page<Post> findAllPostResources(Predicate predicate, Pageable pageable);
}

The problem is when I call findAllPostResources with predicate and pageable arguments:

public Page<Post> getAllPostsByCountryPaginated(long countryId, Pageable pageable) {
    return postRepository.findAllPostResources(QPost.post.country.id.eq(countryId), pageable);
}

It ignores predicate argument and execuites following query:

SELECT
    post0_.id AS id1_13_0_,
    country1_.id AS id1_3_1_,
    post0_.body AS body2_13_0_,
    post0_.country_id AS country_7_13_0_,
    post0_.created_at AS created_3_13_0_,
    post0_.published_at AS publishe4_13_0_,
    post0_.title AS title5_13_0_,
    post0_.updated_at AS updated_6_13_0_,
    country1_.alpha2_code AS alpha2_3_1_,
    country1_.alpha3_code AS alpha3_3_1_,
    country1_.created_at AS created_4_3_1_,
    country1_.name AS name5_3_1_,
    country1_.updated_at AS updated_6_3_1_
FROM
    posts post0_
LEFT OUTER JOIN countries country1_ ON
    post0_.country_id = country1_.id
LIMIT ?

As you can see there is no WHERE caluse in SQL (WHERE country_id = ?).

So, how to create findAll() method and define predicate, paging and also what entity graph to use inside JpaRepository? Or is this something that cannot be achieved this way and I will need to create custom repository implementation?

You either write a query and use pageable or pass a predicate and pageable without a query. Combining a query and predicate isn't going to work. — M. Deinum, Jun 29 '20 at 11:51
possible [duplicate](https://stackoverflow.com/questions/47368898/spring-data-rest-override-repository-findall-without-creating-search-findall) — Empty Brain, Jun 29 '20 at 11:54

score 1 · Accepted Answer · answered Jul 01 '20 at 09:44

1

I have found solution for my problem: How to have methods with Predicate, Pageable and EntityGraph all at once and the answer is to use following library:

https://github.com/Cosium/spring-data-jpa-entity-graph

answered Jul 01 '20 at 09:44

clzola

1,925
3
30
49

score 0 · Answer 2 · answered Jun 29 '20 at 11:54

0

but you have this on query :

LEFT OUTER JOIN countries country1_ ON
    post0_.country_id = country1_.id
LIMIT

is not it kind of same with "where country_id =". did you run this query on sample database and see what you get? Maybe it just works fine.

Also, i think, you can just say

findAllPostResourcesByCountryId(long countryId);

Sorry, i would love to comment instead of answer but i did not have enough reputation.

answered Jun 29 '20 at 11:54

fatma zehra güç

164
5

That would work, but then i would have methods for each query, while I want to have single method and let the service define search criteria... And I did run query on database and get posts from all countries, but I want only posts for specific country – clzola Jun 29 '20 at 12:11
you need to create custom repository implementation for this , sorry. – fatma zehra güç Jun 29 '20 at 13:27

How to define custom findAll methods in JpaRepository?

2 Answers2