Spring Data JPA: get first record using @Query annotation on query method

Question

My query method returns list of entities:

@Query("select u from ProfileDMO p inner join p.userPrincipal u where p.id=:profileId")
    List<UserPrincipalDMO> findUserPrincipalByProfileId(@Param("profileId") long profileId);

And I need only first result. Currently, I am using List.get(int index) to get first element.

Does anyone know how should I update my query method to return only first result?

@AndriySlobodyanyk, thanks for response. That solution works for me. — I. Domshchikov, Dec 13 '17 at 15:26

Jens Schauder · Accepted Answer · 2019-04-09T07:17:38.847

Updated answer:

If you can't use a derived query where Spring Data JPA derives the query from the method name you can use a Pageable parameter to limit the results and a second method with a default implementation to hide the parameter and unwrap the result:

@Query("select u from ProfileDMO p inner join p.userPrincipal u where p.id=:profileId")
List<UserPrincipalDMO> internalFindUserPrincipalByProfileId(@Param("profileId") long profileId, Pageable page);

default UserPrincipalDMO findUserPrincipalByProfileId(long profileId){
    return internalFindUserPrincipalByProfileId(
        profileId, 
        PageRequest.of(0,1)
    ).get(0);
};

Of course, that leaves the internal implementation in your API, if you don't like that you always can fall back to a custom implementation.

Original answer:

You should be able to use query derivation, i.e. remove the @Query annotation and change your method to:

UserPrincipalDMO findFirstByUserPrincipalProfileId(@Param("profileId") long profileId);

If you don't like the name you can use a default method with your preferred name and delegate to the one I proposed.

If you don't want the method name at all in your interface you can provide a custom implementation.

thanks for your response. I couldn't use here query generation from method name approach, as `UserPrincipalDMO` entity doesn't have relation with `ProfileDMO` entity. — I. Domshchikov, Dec 13 '17 at 14:25
oh, I see the relation is the other way round. That leaves you with a custom implementation. — Jens Schauder, Dec 13 '17 at 14:36
I updated my answer with a solution that is easier to implement. — Jens Schauder, Jun 15 '18 at 08:44

Spring Data JPA: get first record using @Query annotation on query method

1 Answers1