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.

@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);
}

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:

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 Boot.

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