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!"}

Answer 1

It is possible to provide custom implementations of methods in a Spring Data JPA repository, which enables complete control on queries and return types. The approach is as follows:

• Define an interface with the desired method signatures.
• Implement the interface to achieve the desired behavior.
• Have the Repository extend both JpaRepository and the custom interface.

Here is a working example that uses JpaRepository, assuming a user_table with two columns, user_id and user_name.

UserEntity class in model package:

@Entity
@Table(name = "user_table")
public class UserEntity {

    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    @Column(name = "user_id")
    private Long userId;

    @Column(name = "user_name")
    private String userName;

    protected UserEntity() {}

    public UserEntity(String userName) {
    this.userName = userName;

    // standard getters and setters
}

Define interface for the custom repository in the repository package:

public interface UserCustomRepository {
    List<String> findUserNames();
}

Provide implementation class for the custom interface in the repository package:

public class UserCustomRepositoryImpl implements UserCustomRepository {


    // Spring auto configures a DataSource and JdbcTemplate
    // based on the application.properties file. We can use
    // autowiring to get a reference to it.
    JdbcTemplate jdbcTemplate;

    @Autowired
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    // Now our custom implementation can use the JdbcTemplate
    // to perform JPQL queries and return basic datatypes.
    @Override
    public List<String> findUserNames() throws DataAccessException {
        String sql = "SELECT user_name FROM user_table";
        return jdbcTemplate.queryForList(sql, String.class);
    }
}

Finally, we just need to have the UserRepository extend both JpaRepository and the custom interface we just implemented.

public interface UserRepository extends JpaRepository<UserEntity, Long>, UserCustomRepository {}

Simple test class with junit 5 (assuming the database is initially empty):

@SpringBootTest
class UserRepositoryTest {

    private static final String JANE = "Jane";
    private static final String JOE = "Joe";

    @Autowired
    UserRepository repo;

    @Test
    void shouldFindUserNames() {
        UserEntity jane = new UserEntity(JANE);
        UserEntity joe = new UserEntity(JOE);

        repo.saveAndFlush(jane);
        repo.saveAndFlush(joe);

        List<UserEntity> users = repo.findAll();
        assertEquals(2, users.size());

        List<String> names = repo.findUserNames();
        assertEquals(2, names.size());
        assertTrue(names.contains(JANE));
        assertTrue(names.contains(JOE));
    }

}

Answer 2

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.

Answer 3

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

1. 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);
   }
   

2. 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
   }
   

Answer 4

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]

Answer 5

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.

Answer 6

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