Select Specific Columns with JPA Native Query

In this short Spring Data JPA tutorial, you will learn how to create a JPA Native SQL Query to select specific columns only.

JPA Native SQL Query to Select Specific Columns

Below is an example of JPA Native Query that selects from a database table called Users only two columns:

  • first_name and,
  • last_name
@Query(value="select first_name, last_name from Users u where u.user_id =:userId", nativeQuery=true)
List<Object[]> getUserFullNameById(@Param("userId") String userId);

The above SQL query will return only one record. We can then create a JUnit test to test it.

public void testGetUserFullNameById()
    List<Object[]> records = userRepository.getUserFullNameById("1a2b3c");
    assertTrue(records.size() == 1);
    Object[] userDetails = records.get(0);
    String firstName = String.valueOf(userDetails[0]);
    String lastName = String.valueOf(userDetails[1]);

JPQL Native SQL Query to Select Specific Columns

If you use JPQL and are looking for an example on how to select specific columns only, then below is a short code snippet that should help you do that.

@Query("select user.firstName, user.lastName from UserEntity user where user.userId = :userId")
List<Object[]> getUserEntityFullNameById(@Param("userId") String userId);

For the above JPQL query to work, my UserEntity class should look like this:


import java.util.List;
import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;
@Table(name = "users")
public class UserEntity implements Serializable {

    private static final long serialVersionUID = 4865903039190150223L;
    private long id;

    @Column(nullable = false)
    private String userId;

    @Column(length = 50, nullable = false)
    private String firstName;

    @Column(length = 50, nullable = false)
    private String lastName;

    @Column(length = 100, nullable = false)
    private String email;

     * @return the id
    public long getId() {
        return id;

     * @param id the id to set
    public void setId(long id) { = id;

     * @return the userId
    public String getUserId() {
        return userId;

     * @param userId the userId to set
    public void setUserId(String userId) {
        this.userId = userId;

     * @return the firstName
    public String getFirstName() {
        return firstName;

     * @param firstName the firstName to set
    public void setFirstName(String firstName) {
        this.firstName = firstName;

     * @return the lastName
    public String getLastName() {
        return lastName;

     * @param lastName the lastName to set
    public void setLastName(String lastName) {
        this.lastName = lastName;

     * @return the email
    public String getEmail() {
        return email;

     * @param email the email to set
    public void setEmail(String email) { = email;

I hope this tutorial, was helpful to you. If you are interested in learning more about building RESTful Web Services with Spring Framework using Spring Boot, Spring MVC, Spring Data JPA and Spring Security please check my other tutorials on this page RESTful Web Services with Spring MVC.

Also, if you enjoy learning by watching short video lessons, check the below list of video courses that teach Spring Data JPA.

Leave a Reply

Your email address will not be published. Required fields are marked *

Free Video Lessons

Enter your email and stay on top of things,