CODEWITHSUNDEEP
javaspringhibernatejpanativequery
Olek
Olek

Reputation: 359

Native queries with @Query annotation in Spring

I have a problem with native queries in Spring. I use annotation @Query with parametr nativeQuery=true and SQL query in param value.

I have the fallowing method in file RezerwacjaDao.java:

@Query(value = "select d.DATA, pt.POKOJTYP_ID, pt.POKOJTYP_NAZWA, 10 \n" +
        "from LISTADAT(:data_od, :data_do) d \n" +
        "left join POKOJTYP pt on 1=1 \n" +
        "order by d.DATA, pt.POKOJTYP_NAZWA",
        nativeQuery = true)
List<DostepnoscTypyListDTO> getDostepnoscNaTypy(
        @Param("data_od") Date dataOd,
        @Param("data_do") Date dataDo);

In file RezerwacjaController.java I have:

@GetMapping("/getDostepnoscNaTypy")
public @ResponseBody
List<DostepnoscTypyListDTO> getDostepnoscNaTypy(Date dataOd, Date dataDo) {               
   return rezMgr.getDostepnoscNaTypy(dataOd, dataDo);
}

And in RezerwacjaManager.java:

public List<DostepnoscTypyListDTO> getDostepnoscNaTypy(Date dataOd, Date dataDo) {     
    return rezerwacjaDao.getDostepnoscNaTypy(dataOd, dataDo);
}

Problem is when method rezerwacjaDao.getDostepnoscNaTypy(dataOd, dataDo) is called and executing is this query. I get an error error:

"Failed to convert from type [java.lang.Object[]] to type [ekoncept.dto.DostepnoscTypyListDTO] for value '{2018-05-01 00:00:00.0, 4, Apartament, 10}'; nested exception is org.springframework.core.convert.ConverterNotFoundException: No converter found capable of converting from type [java.sql.Timestamp] to type [ekoncept.dto.DostepnoscTypyListDTO]",

DostepnoscTypyListDTO.java:

public class DostepnoscTypyListDTO {

   @Getter @Setter private Integer pokojtypId;
   @Getter @Setter private String pokojtypNazwa;
   @Getter @Setter private String naDzien;
   @Getter @Setter private Integer ileDostepnych;

    public DostepnoscTypyListDTO(
            String naDzien, Integer pokojtypId, String pokojtypNazwa,
            Integer ileDostepnych) {

        this.pokojtypId = pokojtypId;
        this.pokojtypNazwa = pokojtypNazwa;
        this.naDzien = naDzien;
        this.ileDostepnych = ileDostepnych;
    }

    public DostepnoscTypyListDTO(
            Timestamp naDzien, //Date naDzien,
            Integer pokojtypId, String pokojtypNazwa,
            Integer ileDostepnych)  {

        SimpleDateFormat df= new SimpleDateFormat("yyyy-MM-dd");
        this.naDzien = df.format(naDzien);  //naDzien;

        this.pokojtypId = pokojtypId;
        this.pokojtypNazwa = pokojtypNazwa;
        this.ileDostepnych = ileDostepnych;
    }
}

Upvotes: 0

Views: 1723

Answers (3)

Olek
Olek

Reputation: 359

I changed code basing on this example:

https://github.com/roberthunt/spring-data-native-query-projection/tree/master/src/main/java/uk/co/rbrt

I created a projection interface DostepnoscTypyListProjection.java:

public interface DostepnoscTypyListProjection {

    Date getNaDzien();
    Integer getPokojtypId();
    String getPokojtypNazwa();
    Integer getIleDostepnych();
}

and changed RezerwacjaDao.java:

@Query(value = 
    "cast(d.DATA as Date) as naDzien, pt.POKOJTYP_ID as pokojtypId, pt.POKOJTYP_NAZWA as pokojtypNazwa, 10 as ileDostepnych \n" +
    "from LISTADAT(:data_od, :data_do) d \n" +
    "left join POKOJTYP pt on 1=1 \n" +
    "order by d.DATA, pt.POKOJTYP_NAZWA",
    nativeQuery = true)
List<DostepnoscTypyListProjection> getDostepnoscNaTypy(
        @Param("data_od") Date dataOd,
        @Param("data_do") Date dataDo);

In other files I changed DostepnoscTypyListDTO to DostepnoscTypyListProjection and everything works fine.

Upvotes: 1

Ajit Soman
Ajit Soman

Reputation: 4074

You need to change return type of your query to List<Object[]>

@Query(value = "select d.DATA, pt.POKOJTYP_ID, pt.POKOJTYP_NAZWA, 10 \n" +
        "from LISTADAT(:data_od, :data_do) d \n" +
        "left join POKOJTYP pt on 1=1 \n" +
        "order by d.DATA, pt.POKOJTYP_NAZWA",
        nativeQuery = true)
List<Object[]> getDostepnoscNaTypy(
        @Param("data_od") Date dataOd,
        @Param("data_do") Date dataDo);

Ans cast the response of your method getDostepnoscNaTypy(...) to List<DostepnoscTypyListDTO>

Upvotes: 1

Alex Roig
Alex Roig

Reputation: 1574

You need to specify how to map the response that the DB returns into the object that you want.

Native queries by default return Object[] and it needs to be mapped to your class. Check this other post: Return custom object from Spring Data with Native Query

Upvotes: 2

Related Questions