Spring Boot JPA how to select specific column from table with the entity class?

Question

In am using spring boot JPA for my database operations. In my entity class, I mapped every columns with my table. In my table I have many columns, but I need to select some of them in my query result set. I does not need to do select * from table_name which brings the performance issue to my application.

My entity class:

@Entity
@Table(name = "user_table")
public class UserInformation {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "user_id")
    private int userId;

    @Column(name = "user_name")
    private String userName;

    @Column(name = "user_type")
    private String userType;

    @Column(name = "user_age")
    private int userAge;

    @Column(name = "is_male")
    private boolean isMale;
}

After this I generated all getters and setters for above class.

My userDAO class

public interface UserInformationRepo extends 
         JpaRepository<UserInformation, String>{

    @Query(value="select user_name from user_table where user_age > 
               :userAge", nativeQuery=true)
    public UserInformation getInfo(int userAge);\

    @Query(value="select user_name,userType, user_id  from user_table where 
          user_age > :userAge", nativeQuery=true)
    public UserInformation getInfo(int userAge);
}

When I run this spring boot app with my above class it shows error as

user_id was not included in the ResultSet

This error because I did't select the user_id from my query.

In my userDAO class I have a multiple functions, each functions need different set of columns, but the return type of all methods should be UserInformation class.

My excepted result is, the columns which I does not select from query should have null value in my result UserInformation object.

I also searched many resources for this, but I cannot find any relevant answer.

Any Help. Thanks in advance

How can you fetch 3 columns and expect the whole object to be created? — Andronicus, Sep 12 '19 at 16:11
Paste the complete log to better understand the exact error. — padmanabhanm, Sep 18 '19 at 15:19

padmanabhanm · Answer 1 · 2019-09-13T05:57:42.827

0

One way could be create a constructor of the UserInformation with userId and userName.

package com.demo.model;

public UserInformation( String userName, String userType, String userId){
    this.userName = userName;
    this.userType = userType;
    this.userId = userId;
}

then

 @Query(value="select new com.demo.model.UserInformation
               (user_name,userType, user_id)  
                from user_table where 
                user_age > :userAge", nativeQuery=true)

edited Sep 13 '19 at 05:57

answered Sep 12 '19 at 16:19

padmanabhanm

315
3
15

Thanks for your answer. Whether the empty constructor will these fields are enough or I have to generate getters and setters inside the constructor.. – Prakash Sep 13 '19 at 05:10
I did'nt understand new valid.java.package.path.to.UserInformation from above sql query. Can you please explain by one example – Prakash Sep 13 '19 at 05:31
Thanks for your answer. Still I got the same error. It is showing user_age was not found in the result set.. – Prakash Sep 18 '19 at 11:15
This is not working for me. It shows error as Synax error near '.' at line of query statement. – Prakash Oct 08 '19 at 10:56
And also you are passing two parameters to UserInformation constructor, but those datatypes are different like user_name must varchar in sql where user_name in the constructor function must be a String. This throws a error for me "ERROR: function Sample(character varying, character varying, timestamp without time zone, character varying, character varying) does not exist" and also it shows "Hint: No function matches the given name and argument types. You might need to add explicit type casts." – Prakash Oct 08 '19 at 10:58

score 0 · Answer 2 · answered Sep 12 '19 at 16:27

0

The user_id is the primary key in your UserInformation object. It would be best to include all columns in the UserInformation object in the Query:

public interface UserInformationRepo extends 
         JpaRepository<UserInformation, String>{

    @Query(value="select * from user_table where user_age > 
               :userAge", nativeQuery=true)
    public UserInformation getInfo(int userAge);
    @Query(value="select *  from user_table where 
          user_age > :userAge", nativeQuery=true)
    public UserInformation getInfo(int userAge);

        }

If your user_table has a great deal of information, you can list only the columns you care about, but be sure to include the @Id (primary key).

answered Sep 12 '19 at 16:27

Kieveli

10,944
6
56
81

Thanks for your answer. Ok I can include the primary key in my select statement , but I should not select other columns until it necessary. – Prakash Sep 12 '19 at 17:26
Try to avoid premature optimization. A half-formed UserInformation object could be a nightmare to debug in a few months - especially if the query used to retrieve the list of users becomes detached from how that User object is used later. – Kieveli Sep 12 '19 at 17:30
Is this necessary to include primary key in my select statement, rest of my columns can I remove if it is not needed. – Prakash Sep 12 '19 at 17:31
I would guess that yes - the error you're seeing is due to missing the primary key in your query. – Kieveli Sep 12 '19 at 17:32
Even though I included the primary key it is showing as error as other columns was not included in the result set. – Prakash Sep 13 '19 at 05:07
Make a new object. No rules against that. If it works with select *, then this question is answered, but you could ask a new question about using some fields some of the time. – Kieveli Sep 13 '19 at 18:57

score 0 · Answer 3 · answered Sep 12 '19 at 19:19

The easiest solution if you create a projection for your entity (containing only the necessary properties):

public interface UserInformationNameProjection {
  String getUserName();
}

Then you can define the repository method this way (You don't even need the id!):

@Query(value="select user_name from user_table where user_age > 
           :userAge", nativeQuery=true)
public UserInformationNameProjection getInfo(int userAge);

Let Spring Data JPA do the rest of the job. :)

Spring Boot JPA how to select specific column from table with the entity class?

3 Answers3

Linked