Spring Data JPA native query result entity

Question

My native join query produces a new result that is a combination of database tables, so I created a dto for that resulting object (will be a list of records on a screen).

I believe I need to make it an entity, so JPA can recognize it, would that be the best way to do it?

Also, the entity needs an id, and I was hoping to let jpa generate it auto, but I'm getting "Invalid parameter: Unknown column name id. ERRORCODE=-4460, SQLSTATE=null"

My result set contains 4 of the same records instead of 4 different, and I think it has to do with my id field not set properly

Any help would be appreciated on the subject, thanks.

`public interface ErrorCodeRepo extends JpaRepository<Errors, ErrorsPK> {

@Query("SELECT e.transDate, e.category FROM Errors e")            
List<QueuedErrors> findQueuedErrors();

}`

DTO class: ` public class QueuedErrors {

private String transDate;
private String category;

public QueuedErrors(String transDate, String category) {
    this.transDate = transDate;
    this.category = category;
}

public String getTransDate() {
    return transDate;
}
public void setTransDate(String transDate) {
    this.transDate = transDate;
}
public String getCategory() {
    return category;
}
public void setCategory(String category) {
    this.category = category;
}

} `

Piotr Solarski Piotr Solarski · Accepted Answer · 2020-10-30T19:13:41

When you create navite query which contains results from multiple tables (after joins) you don't have to create new entities.

Better way to solve this problem is to projection with interface or class DTO. For example, if you want to combine results from Person and Address Entities, simply create interface:

public interface PersonView {
    String getFirstName();
    String getLastName();
    String getStreet();
}

You can see combined fileds from Person (firstName, lastName) and Address (street). You have to use it as query response, like this:

@Query(...)
List<PersonView> getPersonWithStreet(String state);

You can read more about it here:

https://www.baeldung.com/spring-data-jpa-projections https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#projections

Spring Data JPA native query result entity

1 Answers