PostgreSQL 14使用json_agg执行JPA原生查询遇JDBC类型1111映射错误

问题核心是Hibernate默认未将PostgreSQL的JSON类型（JDBC类型1111）映射到JsonNode，以下是几种可行解决方案：

方案1：扩展PostgreSQL方言，注册JSON类型映射 #

自定义方言补充JDBC类型1111到JsonNode的映射：

import org.hibernate.dialect.PostgreSQL14Dialect;
import com.fasterxml.jackson.databind.JsonNode;

public class CustomPostgreSQLDialect extends PostgreSQL14Dialect {
    public CustomPostgreSQLDialect() {
        super();
        registerHibernateType(1111, JsonNode.class.getName());
    }
}

在配置文件中指定自定义方言：

spring.jpa.properties.hibernate.dialect=com.yourpackage.CustomPostgreSQLDialect

方案2：使用自定义AttributeConverter转换JSON字符串到JsonNode #

创建转换器自动处理JSON字符串与JsonNode的互转，同时修改查询将json_agg结果转为文本：

import jakarta.persistence.AttributeConverter;
import jakarta.persistence.Converter;
import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import java.io.IOException;

@Converter(autoApply = true)
public class JsonNodeConverter implements AttributeConverter<JsonNode, String> {
    private final ObjectMapper objectMapper = new ObjectMapper();

    @Override
    public String convertToDatabaseColumn(JsonNode jsonNode) {
        try {
            return objectMapper.writeValueAsString(jsonNode);
        } catch (IOException e) {
            throw new RuntimeException("JsonNode转字符串失败", e);
        }
    }

    @Override
    public JsonNode convertToEntityAttribute(String s) {
        try {
            return objectMapper.readTree(s);
        } catch (IOException e) {
            throw new RuntimeException("字符串转JsonNode失败", e);
        }
    }
}

修改原生查询，将json_agg结果转为text：

SELECT id, name, json_agg(channels)::text AS channels FROM ... GROUP BY id, name

方案3：通过@SqlResultSetMapping显式映射类型 #

定义结果集映射，明确指定channels字段的类型：

import jakarta.persistence.Entity;
import jakarta.persistence.Id;
import jakarta.persistence.SqlResultSetMapping;
import jakarta.persistence.ColumnResult;
import jakarta.persistence.ConstructorResult;
import com.fasterxml.jackson.databind.JsonNode;

@Entity
@SqlResultSetMapping(
    name = "NotificationListDtoMapping",
    classes = @ConstructorResult(
        targetClass = NotificationListDto.class,
        columns = {
            @ColumnResult(name = "id", type = Long.class),
            @ColumnResult(name = "name", type = String.class),
            @ColumnResult(name = "channels", type = JsonNode.class)
        }
    )
)
public class DummyMappingEntity {
    @Id
    private Long id;
}

执行查询时指定该映射：

Query query = entityManager.createNativeQuery(
    "SELECT id, name, json_agg(channels) AS channels FROM ... GROUP BY id, name",
    "NotificationListDtoMapping"
);
List<NotificationListDto> result = query.getResultList();

方案4：使用Hibernate Types库简化配置 #

引入Hibernate Types库直接支持JSON类型映射：

<dependency>
    <groupId>com.vladmihalcea</groupId>
    <artifactId>hibernate-types-55</artifactId> <!-- 匹配你的Hibernate版本 -->
    <version>2.19.0</version>
</dependency>

在DTO字段上添加@Type注解：

import com.vladmihalcea.hibernate.type.json.JsonNodeBinaryType;
import org.hibernate.annotations.Type;

public interface NotificationListDto {
    Long getId();
    String getName();
    @Type(JsonNodeBinaryType.class)
    JsonNode getChannels();
}

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