Spring Boot 2.7.5+PostgreSQL执行JPA原生查询遇JDBC类型1111映射错误

基于Spring Boot 2.7.5搭配PostgreSQL开发的应用，执行原生JPA查询时抛出No Dialect mapping for JDBC type: 1111错误，相关细节如下：

相关代码 #

原生JPA查询 #

@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>

结果映射接口 #

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

错误栈信息 #

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)

当前JPA方言配置 #

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

用户已尝试将SQL结果转为float和float8，问题仍未解决。

1. 修正SQL语法错误 #

原SQL的写法会被PostgreSQL解析为笛卡尔积查询，返回的结果结构不符合预期，导致Hibernate无法识别类型。正确写法是将两个子查询作为同一行的两个列：

select 
    (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

2. 对齐结果类型与接口定义 #

PostgreSQL的float8对应Java的Double类型，而原接口使用Float会导致类型映射不兼容，修改接口：

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

3. 可选：优化查询逻辑（提升性能） #

如果两个子查询逻辑完全一致，可合并为单次查询减少数据库访问：

select 
    cast(max(sd.value) - min(sd.value) as float8) as import,
    cast(max(sd.value) - min(sd.value) as float8) as export
from solar_data sd
where sd.device_address = :deviceAddress
  and sd.tstamp between :from and now()

4. 可选：匹配PostgreSQL版本升级方言 #

若使用的PostgreSQL版本高于10，可对应升级方言（如PostgreSQL 12+用org.hibernate.dialect.PostgreSQL12Dialect），避免潜在版本兼容问题。

内容的提问来源于stack exchange，提问作者zamek 42