Extract only certain fields using CrudRepository?

Question

I'm using CrudRepository to fetch Persons from database.

Is it possible to only fetch certain fields from the db, instead of the full entity, using a CrudRepository mapping method?

Example: I only want to extract all lastname column fields:

interface PersonRepository extends CrudRepository<Person, Long> {
    //of course this is invalid
    List<String> findAllLastname();
}

@Entity
public class Person {
    @Id
    private Long id;


    private String firstname, lastname;
}

findAllLastname() is of course not valid, but can I achieve this?

Does this answer help? [Spring JPA selecting specific columns](https://stackoverflow.com/questions/22007341/spring-jpa-selecting-specific-columns) — banncee, Jul 18 '18 at 14:57

Mamtha Soni K · Answer 1 · 2018-07-18T15:00:52.590

2

Giving a Query annotation with the specific SQL query should work

@Query("select p.lastName from Person  p")
List<Person> getAllLastName();

edited Jul 18 '18 at 15:00

answered Jul 18 '18 at 14:58

Mamtha Soni K

895
6
9

Ok I thought it would be possible without writing sql explicit. – membersound Jul 18 '18 at 14:59
AFAIK, that is not possible. JPA returns the entire object because it is probably as efficient as returning a subset of columns in most cases. – banncee Jul 18 '18 at 15:00
@banncee : you are right. It returns the whole object – Mamtha Soni K Jul 18 '18 at 15:04

score 2 · Accepted Answer · answered Jul 18 '18 at 15:43

2

You can fetch specific field just doing this:

@Query("select p.lastName from Person p")
List<String> getAllLastName();

But sometimes this may not work (for example for enum type). The best way here, IMO, is using projection as returned type, for example:

public interface PersonProjection {
    String getLastName();
}

@Query("select p.lastName as lastName from Person p")
List<PersonProjection> getAllPersonProjections();

In this case if you need to get, in the future, not only the lastName but also firstName then you simple update your PersonProjection and the query method:

public interface PersonProjection {
    String getFirstName();
    String getLastName();
}

@Query("select p.firstName as firstName, p.lastName as lastName from Person p")
List<PersonProjection> getAllPersonProjections();

Note that you should use aliases of the entity fields (p.firstName as firstName) that correspond to names of getters in the projection.

answered Jul 18 '18 at 15:43

Cepr0

28,144
8
75
101

This looks primising. Does that work without the `@Query` too, means the query is autogenerated from spring by method name? – membersound Jul 18 '18 at 15:44
1

Projections should work without `@Query`, but getting single field - doesn't. – Cepr0 Jul 18 '18 at 15:47
@membersound without `@Query` you should correctly name your query method, like `findAllBy()` – Cepr0 Jul 19 '18 at 07:48
I just discovered one does only require an *interface* with the getters accordingly. No `@Projection` from `spring-data-rest` is required at all. That's very neat! – membersound Jul 19 '18 at 07:49

Extract only certain fields using CrudRepository?

2 Answers2