Select Specific Columns with JPA Native Query

This tutorial will teach you how to create a JPA Native SQL Query to only select information from specific columns.

You can find many more Spring Data JPA-related tutorials on this site. Some of the most popular tutorials are:

JPA Native SQL Query to Select Specific Columns

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

  • 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]);

The above SQL query will select information only if there is an exact match. To select information based on a specific pattern(starts with or ends with), read tutorial called “How to use LIKE % expression in JPA SQL Query“.

JPQL Native SQL Query to Select Specific Columns

If you use JPQL and are looking for an example of selecting specific columns only, 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 Boot.

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 *