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.

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

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:

package com.appsdeveloperblog.app.ws.io.entity;

import java.io.Serializable;
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;
 
@Entity
@Table(name = "users")
public class UserEntity implements Serializable {

    private static final long serialVersionUID = 4865903039190150223L;
    @Id
    @GeneratedValue
    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) {
        this.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) {
        this.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.