How to get selective column in spring boot JPA?

I am new to spring boot.

I have two entities Invite and User.

@Entity
public class Invite {

	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private int id;
	
	@Column(name="email")
	private String email_id;
	private String mobile;
	private String pincode;
	private String name;
	private String status;
	private Date created_on;
	private Date updated_on;
 
    //gettes and setters
}


@Entity
public class User {
	@Id
	@GeneratedValue(strategy= GenerationType.IDENTITY)
	private int id;
	
	@OneToOne
	private Invite invite;
	private String firstName;
	private String LastName;
	@JsonIgnore
	private String password;
	private Date created_on;
	private Date updated_on;

    // getters and setters

}

I want the following data

+---------+---------------------------------------------+
|  id     |   firstName    email_id         mobile      |
+-------------------+------------+----------------------+
|         |         |            |                      |
| 1       |Ram      | s@mail.com |      1111111111      |
|         |         |            |                      |
+---------+---------+------------+----------------------+

So I have created the below query in UserRepository:

@Repository
public interface UserRepository extends JpaRepository<User, Integer> {

	
	@Query("select u.id,u.firstName,i.email_id, i.mobile from User u inner join u.invite i")
	public List<User> getUserDetails();
}

So I am getting the below error -

Failed to convert from type [java.lang.Object[]] to type [@org.springframework.data.jpa.repository.Query com.example.jpa.JPA.entity.User] for value

I know it's because the return value is of type Object and I am trying to get it into user.

Could you please give me a way to get any data without creating extra DTO classes or interface?

Its great you already know what the issue is.

@Query("select u.id,u.firstName,i.email_id, i.mobile from User u inner join u.invite i")
List<User> getUserDetails();

You are selecting a few columns but asking Data-Jpa to return you back with the a List of User Objects.

So you need to understand first, what JPA specification says about using JPQL.

• select u from User u where u.id = :id -> this returns a List of User type Objects
  This query in native is : SELECT * FROM USER WHERE ID = "{whatever value you passed}";

• select u.username from User u where u.id = :id" -> this returns just the username, which lets say is a String type. So the return object type is of the String type in case you using TypedQuery or Object is returned in case you using Query . The corresponding SQL generated is SELECT u.USERNAME FROM USER u WHERE u.id = {id}

• select u.id, u.username from User u where u.id = :id -> this returns Object[]. This is when you need the DTO projection to map it to a proper Type of what you want.

With native JPA implementations you can use DTO Projection or Query Projection

I mostly use DTO Projection from JPA spec ad not use Query Projection, so I am giving an example of DTO projection, but above documents if gone through gives a good reference how to work with each abstraction/feature.

1. Define a class to represent the DTO and package I am taking into consideration: com.example. Package name is important since you need the full class name for reference in JPQL

package com.example;


class UserDTO {
 int id;
 String firstName,
 String email_id;
 String mobile;

 //All args constructor is needed and madatory
public User(int id, String firstName, String email_id, String mobile) {
 // assignments
}
  // getters and setters
}

In the Repository:

@Query("select new com.example.UserDTO(u.id,u.firstName,i.email_id, i.mobile) from User u inner join u.invite i")
List<UserDTO> getUserDetails();

The most important thing being : new fully-qualifed-classname(all columns you are fetching)

This is how you use DTO projection with JPQL and Data Jpa Repository.

Write a constructor in User class for your selective column and use in query. Make sure you are also using a constructor in Invite class for email_id and mobile and use it inside constructor in User.

@Query("select new com.example.jpa.JPA.entity.User(u.id,u.firstName,i.email_id, i.mobile) from User u inner join u.invite i")
public List<User> getUserDetails();

Use this constructor in User and Invite class

public User(int id, String firstName, String email_id, String mobile) {
 // assign id and firstName
 // call invite class constuctor 
}

public Invite(String email_id, String mobile) {
 // assign email_id and mobile
}

If you want to have selected fields in query result or customize query result, you can simply use jpa Projection. create an interface with related property method

try this ..this work for me :
spring.jpa.hibernate.naming-strategy=org.hibernate.cfg.DefaultNamingStrategy

this is to be added in application.properties