Select one column using Spring Data JPA

Question

Does anyone have any idea how to get a single column using Spring Data JPA? I created a repository like below in my Spring Boot project, but always get the {"cause":null,"message":"PersistentEntity must not be null!"} error when accessing the Restful URL.

@RepositoryRestResource(collectionResourceRel = "users", path = "users")
public interface UsersRepository extends CrudRepository<Users, Integer> {

    @Query("SELECT u.userName  FROM Users u")
    public List<String> getUserName();
}

Then if I access the Restful URL like ../users/search/getUserName, I get the error: {"cause":null,"message":"PersistentEntity must not be null!"}

This works for me with JpaRepository. – kervin Jul 07 '16 at 22:43 — kervin, Jul 07 '16 at 22:43

James Gawron · Answer 1 · 2021-09-22T01:35:45.390

10

Create a Projection interface

public interface UserNameOnly {
    String getUserName();
}

Then in your repository interface return that type instead of the user type

public interface UserRepository<User> extends JpaRepository<User,String> {
    List<UsernameOnly> findNamesByUserNameNotNull();
}

The get method in the projection interface must match a get method of the defined type on the JPA repository, in this case User. The "findBySomePropertyOnTheObjectThatIsNotNull" allows you to get a List of the entities (as opposed to an Iterable) based on some criteria, which for a findAll can simply be if the unique identifier (or any other NonNull field) is not null.

edited Sep 22 '21 at 01:35

answered Jun 02 '20 at 17:05

James Gawron

781
8
26

@Alax would you consider possibly accepting my answer? – James Gawron Oct 26 '20 at 19:45
1

It's not the solution tbh, projection selects all the fields anyways. – Boldbayar Jun 12 '21 at 13:13
I think James forgot to add `@Query` in the `UserRepository` interface. I have edited the @James code. Hope this will return only UserName. – Muhammad Tariq Sep 16 '21 at 03:13
No I didnt. The Query annontation is not necessary as Spring Data JPA can parse the method name to determine what query to run. – James Gawron Sep 22 '21 at 01:39
1

@Boldbayar, your statement is not correct. For closed projections, where the method names on the interface match getters on the entity ... the Hibernate sql logs confirm that only the fields present in the interface are queried for. Open projections, where SPEL expressions are used to calculate field values, cannot be optimized, and in that case you would be correct. – James Gawron Mar 13 '22 at 18:41

Ethiraj · Answer 2 · 2020-06-02T15:21:09.293

Concept is : In your entity class create a constructor with only required instant variables. And use that constructor in the repository method shown below.

Lets say you have a interface Repository like below

Repository implementation:

public interface UserRepository<User> extends JpaRepository<User,String>
{
    @Query(value = "select new com.org.User(usr.userId) from User usr where usr.name(:name)")
    List<User> findUserIdAlone(@Param("name") String user);
}

In Controller

@RestController
public class UserController 
{
    @Autowired
    private UserRepository<User> userRepository; 

    @Res
    public ResponseEntity<User> getUser(@PathVariable("usrname") String userName)
    {
        User resultUser = usrRepository.findUserIdAlone(userName);
        return ResponseEntity.ok(resultUser);
    }
}

public class User 
{

    private String userId,userName;

    public User(String userId) 
    {
        this.userId=userId;
    }
    // setter and getters goes here
}

Is that an extra bracket on the @Query(value = "select new com.org.User(usr.userId) from User usr where usr.name(:name))") section? — Azy Sır, Sep 10 '19 at 09:31

score 2 · Answer 3 · answered Aug 16 '17 at 14:51

2

This Works for me.

public interface UserDataRepository extends JpaRepository<UserData, Long> {

    @Query(value = "SELECT emp_name FROM user_data", nativeQuery = true)
    public List<Object[]> findEmp_name();
}


System.out.println("data"+  userDataRepository.findEmp_name());

The above line gave me this result :

data[abhijeet, abhijeet1, abhijeet2, abhijeet3, abhijeet4, abhijeet5]

answered Aug 16 '17 at 14:51

Abhijeet Behare

597
1
7
21

This doesn't seem to work with a Custom Object. If you see where you have Object[] I have a custom object and it has not worked. – Azy Sır Sep 10 '19 at 09:37

score 1 · Answer 4 · answered Feb 03 '17 at 00:36

1

If you want to only return a single column you should look at Projections and Excerpts which will allow you to filter specific columns and other things that are usefule.

answered Feb 03 '17 at 00:36

Hatem Jaber

2,264
2
20
38

Murder · Answer 5 · 2015-03-07T10:14:06.043

0

If you need list all of the users, try select userName from Users, if you need one user use "where" look at spring data JPA http://docs.spring.io/spring-data/jpa/docs/current/reference/html/ , try change CrudRepository to JpaRepository

edited Mar 07 '15 at 10:14

answered Mar 07 '15 at 09:57

Murder

51
1
9

Thanks a lot for your answer. Actually, it still does not work. I got the same error. If to list all columns, it works, but not if just part of them. – Alax Mar 15 '15 at 08:49

Select one column using Spring Data JPA

5 Answers5

Linked