jpa postgresql No Dialect mapping for JDBC type: 1111

Question

My Spring boot 2.7.5 app with PostgreSQL gives back an error when I try to run a nativeQuery:

    @Query(value = """select import, export from
               (select cast(max(sd.value) - min(sd.value) as float8)
                from solar_data sd
                where sd.device_address = :deviceAddress
                  and sd.tstamp between :from and now()) as import,
               (select cast(max(sd.value) - min(sd.value) as float8)
                from solar_data sd
                where sd.device_address = :deviceAddress
                  and sd.tstamp between :from and now ()) as export
     """, nativeQuery=true)
     fun getMainScreenData(@Param("deviceAddress") deviceAddress:Long, @Param("from") 
           from:LocalDateTime) : List<Impex>

It gives back two floats as import and export. My result interface is:

    interface Impex {
        fun getImport():Float
        fun getExport():Float
    }

Jpa error message is:

        org.hibernate.MappingException: No Dialect mapping for JDBC type: 1111
        org.hibernate.dialect.TypeNames.get(TypeNames.java:71)
        org.hibernate.dialect.TypeNames.get(TypeNames.java:103)
        org.hibernate.dialect.Dialect.getHibernateTypeName(Dialect.java:741)

I use dialect as

    spring:
       jpa:
          databasePlatform:  org.hibernate.dialect.PostgreSQL10Dialect

I tried casting as float and float8 in sql but the result is same. How can I execute this native query?

score 0 · Answer 1 · answered Dec 25 '22 at 17:23

Finally I found it. The problem was that, postgresql result contains bracket for every value like this:

(3764.81494140625),(2461.824951171875)

That's why Hibernate couldn't parse it. I have to change sql like this:

select ri.val as import,
       re.val as export
from
    (select max(sd.value) - min(sd.value) as val
     from solar_data sd
     where sd.device_address=:deviceAddress
           and sd.tstamp between :f and now ()
    ) ri ,
    (select max(sd.value) - min(sd.value) as val
     from solar_data sd
     where sd.device_address=:deviceAddress
           and sd.code_id= (select id from codes where code= '1-0:1.8.0')
           and sd.tstamp between :f and now ()
    ) re

jpa postgresql No Dialect mapping for JDBC type: 1111

1 Answers1