CODEWITHSUNDEEP
springspring-dataspring-data-jpajpqlnamed-query
M. Justin
M. Justin

Reputation: 21377

Can I limit query results of a Spring Data query method that uses a JPQL named query?

I have a named JPQL query which I would like to use in a Spring Data repository interface. Furthermore, I would to have it return just the first result, or null if there are no matching results.

From what I have been able to determine, there is no way to limit the number of results within a JPQL query. I cannot just declare the return type as the entity class, as a IncorrectResultSizeDataAccessException will be thrown if more than one result is returned by the query.

For regular (non-JPQL) query methods, using the keywords first or top (as detailed in the Spring Data JPA docs). Named queries can be included in a repository interface by specifying a method name matching the name of the named query (again, as detailed in the Spring Data JPA docs). However, I do not see any way to combine these two: to return just the first result of a named query in a repository interface.

Here is a concrete example:

public interface ExampleRepository extends JpaRepository<Example, Integer> {
    // Using "first" or "top" can return just the first value
    Example findFirstByValueNotOrderByValueDesc(String value);

    // Using the "Example.findMatchingComplicatedCriteria" named query to return all matching values
    List<Example> findMatchingComplicatedCriteria(@Param("value") String value);

    // TODO: How do I make something like this work?
    // Example firstFindMatchingComplicatedCriteria(@Param("value") String value);
}

@Entity
@NamedQuery(name = "Example.findMatchingComplicatedCriteria",
        query = "SELECT e from Example e WHERE e.value <> :value ORDER BY e.value DESC")
public class Example {
    @Id public Integer id;
    public String value;
}

I am aware that I could use an EntityManager directly, but I would like to do this all directly from the repository interface, if possible. The other workaround would be to have the repository return the list, and have my application code pull out the first element; again, something I'm wondering if I can avoid. I am also aware that I could use a native query to achieve this functionality.

Ultimately, my question is thus: can I have a Spring Data repository interface method use a named query and return a single result? Barring that, is there an issue open for this? I didn't find anything in the Spring Data issue tracker, but my search fu might be failing me.

Upvotes: 0

Views: 5789

Answers (1)

Ralph
Ralph

Reputation: 120861

Use @Query(named="")

@Query(named="Example.findMatchingComplicatedCriteria")
List<Example> findFirstMatchingComplicatedCriteria(@Param("value") String value);

The other way is is to use a Pageable: use page 0, and use the page size for your limit

 List<Example> findMatchingComplicatedCriteria(@Param("value") String value, Pageable pageable);

 Pageable pageable = new PageRequest(0, 10);

Upvotes: 1

Related Questions