How do I leave my Hibernate (Spring Boot) dynamic query?

Question

Hello

I am trying to let my dynamic query down as follows:

@Query("SELECT T.NR_EVENT FROM TB_TIMELINE AS T WHERE T.NR_PENUMPER = :customerCode")
public List<TB_TIMELINE> getTimelineRecordsByClient(@Param("customerCode") String customerCode);

I’m getting the following error:

Hibernate: SELECT T.* FROM MRC_TIMELINE.TB_TIMELINE AS T WHERE T.NR_PENUMPER = ?

Someone knows how to help me?

Answer 1

You are returning a list of TB_TIMELINE, but when you do SELECT T.NR_EVENT ..., you are not selecting a TB_TIMELINE, you are selecting a NR_EVENT (which is an entity field). Thus, Spring Data cannot build the object to return it, precisely because the result of its query is a list of NR_EVENT, not a list of TB_TIMELINE.

I look at three possible solutions:

The first is to change your JPQL to:

SELECT T FROM TB_TIMELINE AS T WHERE T.NR_PENUMPER = :customerCode

This way, Spring Data is able to create its object (keep in mind that this will return all attributes of the database, discarding only child objects that have FetchType.LAZY, which is the standard for nested objects)

---

The second is to create a constructor in your class TB_TIMELINE who receives a NR_EVENT and use it to instantiate a new object in your query (since it is a JPQL, this is supported, see the example below)

TB_TIMELINE.java

public class TB_TIMELINE {
    
    private Integer NR_EVENT; // estou supondo que o tipo de NR_EVENT é Integer
    
    // desconheço os outros atributos
    
    // este é o construtor de que estou falando
    public TB_TIMELINE(Integer NR_EVENT) {
        this.NR_EVENT = NR_EVENT;
    }
    
    // mantenha o construtor default, pois é ele útil
    public TB_TIMELINE() {
    }
    
}

Your query would look like this (yes, new .... JPQL has this support)

SELECT new seu.pacote.TB_TIMELINE(T.NR_EVENT) FROM TB_TIMELINE AS T WHERE T.NR_PENUMPER = :customerCode

Note: do not forget to put the package in the creation of the instance, as in the example above.

Thus, would be returned a List of TB_TIMELINE with the attribute NR_EVENT completed and with all other null fields

---

The third is to change the return type of your method to a List field-type NR_EVENT, for example:

@Query("SELECT T.NR_EVENT FROM TB_TIMELINE AS T WHERE T.NR_PENUMPER = :customerCode")
public List<Integer> getTimelineRecordsByClient(@Param("customerCode") String customerCode);

In this case, a list of numbers will be returned.