[FIXED] JPA Native Query. Cannot select specific columns

Issue

I'm working on a Spring Boot project using JPA to connect to my DB. I wan to make a native query to select some specific fields but it doesn't allow me to do. For example, I want to get only id, firstName, lastName and phoneNumber of a customer But it will throws me error like,

The column name current_access_token was not found in this ResultSet.

Here is my query code in the JPA repository,

@Query(value = "SELECT c.id, c.phone_number, c.firstname, c.lastname FROM tbl_customers c JOIN tbl_subscriptions s ON c.id = s.customer_id WHERE s.role = 'member' AND s.deleted_at IS NULL", nativeQuery = true) 
List<Customer> findMemberByRole(String role);

Here is my Cutomer.java

@Getter
@Setter
@Accessors(chain=true)
@NoArgsConstructor
@AllArgsConstructor
@ToString
@Entity
@Table(name = "tbl_customers")
public class Customer implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(unique = true)
    private Long id;
    @Column(nullable = false, name = "phone_number")
    private String phoneNumber;
    private String firstname;
    private String lastname;
    @Column(name = "current_access_token")
    private String currentAccessToken;
    @Column(name = "consent_accepted")
    private Boolean consentAccepted;
    ...
    ...
}

How can I avoid or ignore unwanted columns? Thanks a lot for helps.

Solution

If you really want to return only 4 columns from the customer table, then the signature you want to use here is List<Object[]>:

@Query(value = "SELECT c.id, c.phone_number, c.firstname, c.lastname FROM tbl_customers c JOIN tbl_subscriptions s ON c.id = s.customer_id WHERE s.role = 'member' AND s.deleted_at IS NULL", nativeQuery = true) 
List<Object[]> findMemberByRole(String role);

Then, when accessing your result set, you would use something like:

List<Object[]> resultSet = findMemberByRole("admin");
for (Object[] rs : resultSet) {
    Long id = (Long) rs[0];
    String phoneNumber = (String) rs[1];
    String firstName = (String) rs[2];
    String lastName = (String) rs[3];
}

Answered By - Tim Biegeleisen