Can we use @Query for a SQL operation that gives a result set different from the table structure in-CodePudding

I'm trying to use @Query in my springboot app, this is effective as long as the result is the same structure as db.

For Example it works fine for the followng example

Employee_Table

| empId | Name | Salary |
| --- | --- | --- |
| 1 | Bill | 10,000 |
| 2 | Sam | 5000 |

Entity:

@Entity
@Table(Name=Employee_Table)
public class employee() {

@Id
@Column(name="empId");
private String empId;

@Column(name="Name")
private String name;

@Column(name="Salary")
private int salary;
}

Service:

@Query(value = "select * from employee_table where salary>9000",nativeQuery=true)

Cause this returns the Employee object which has the same structure as the DB.

But when I try some operation which has something like I want the average of all salaries do I need to follow the same structure using @Query? Will the entity class for the operation be like this? What would be a better approach?

@Entity
public class employee() {

private int salary;
}

I've tried with the @Query Annotation but I keep getting the following Exception

nested exception is org.hibernate.exception.SQLGrammarException: could not execute query] with root cause java.sql.SQLException: Column not found: column_name

But when I run the same query on the DB itself I get the expected result

CodePudding user response：

Yes you can use @Query without whole entities. E.g Something like :

@Query(value = "select name, salary from employee where salary>9000")

which returns you an Object[].

Or you can define a new class

public class SomeEmployeeValues(){
    private String name;
    private int salary;
    public SomeEmployeeValues(String name,  int salary) {
     ...
    }
    //getter and setter
}

and then you can Query:

@Query(value = "select new SomeEmployeeValues(name, salary) from employee where salary>9000")

and you get back a List<SomeEmployeeValues>

So if you want the avarage of salary you can simply query:

@Query(value="select avg(s.salary) from employee s")

and you will get back an List<Object[]> which contains one element and you can access it like list.get(0)[0].

BTW: Take care of java naming conventions. Class names should start with upper case character