Hibernate中如何将PostgreSQL的JSONB映射到Projection字段？

在PostgreSQL中使用包含JSONB字段的表，实体类通过JsonBinaryType将JSONB映射为MutableMap<String, Any>，定义Projection接口后，使用原生SQL查询时触发Hibernate异常：

No Dialect mapping for JDBC type: 1111; nested exception is org.hibernate.MappingException: No Dialect mapping for JDBC type: 1111

相关代码 #

实体类

@Entity
@Table(name = "test_table")
@TypeDef(name = "jsonb", typeClass = JsonBinaryType::class)
class TestEntity(

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "test_id")
    var main: Main? = null,

    @Column(name = "id")
    val id: String,

    @Column(name = "fallback_id")
    var fallbackId: String? = null,

    @Column(name = "properties", columnDefinition = "jsonb")
    @Type(type = "jsonb")
    val properties: MutableMap<String, Any> = mutableMapOf(),
)

Projection接口

interface TestProjection {
    val id: String
    val fallbackId: String
    val properties: MutableMap<String, Any>
}

Repository原生查询代码

@Repository
interface TestRepository : JpaRepository<TestEntity, UUID> {
    fun findByFallbackIdIn(fallbackIds: Set<String>): Set<TestEntity>

    @Query(
        nativeQuery = true,
        value = """
            SELECT 
                cast(t.id as varchar) as id,
                cast(t.fallback_id as varchar) as fallback_id,
                t.properties as properties
            FROM test_table t
            WHERE t.id < :last_uuid
            ORDER by t.id DESC
            LIMIT :batch_size
        """
    )
    fun findTestForSomeLogic(
        @Param("last_uuid") lastUuid: UUID,
        @Param("batch_size") batchSize: Int,
    ): List<TestProjection>
}

方法1：自定义Hibernate方言添加JSONB映射 #

创建自定义PostgreSQL方言，显式将JSONB类型关联到JsonBinaryType：

import org.hibernate.dialect.PostgreSQLDialect
import org.hibernate.type.descriptor.sql.Types
import com.vladmihalcea.hibernate.type.json.JsonBinaryType

class CustomPostgreSQLDialect : PostgreSQLDialect() {
    init {
        registerColumnType(Types.JAVA_OBJECT, "jsonb")
        registerHibernateType(Types.JAVA_OBJECT, JsonBinaryType::class.java.name)
    }
}

在配置文件中指定使用该方言：

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

方法2：原生查询转字符串后手动反序列化 #

修改原生查询，将JSONB转为字符串，再在业务层反序列化为Map：

1. 调整Repository查询：

@Query(
    nativeQuery = true,
    value = """
        SELECT 
            cast(t.id as varchar) as id,
            cast(t.fallback_id as varchar) as fallback_id,
            cast(t.properties as text) as properties
        FROM test_table t
        WHERE t.id < :last_uuid
        ORDER by t.id DESC
        LIMIT :batch_size
    """
)
fun findTestForSomeLogic(
    @Param("last_uuid") lastUuid: UUID,
    @Param("batch_size") batchSize: Int,
): List<TestProjectionWithStringProperties>

2. 创建字符串类型的Projection：

interface TestProjectionWithStringProperties {
    val id: String
    val fallbackId: String
    val properties: String
}

3. 业务层转换（以Jackson为例）：

val objectMapper = ObjectMapper().registerKotlinModule()
val rawProjections = testRepository.findTestForSomeLogic(lastUuid, batchSize)
val finalResult = rawProjections.map {
    val propertiesMap = objectMapper.readValue(it.properties, mutableMapOf<String, Any>().javaClass)
    // 按需封装成目标数据结构
}

方法3：改用JPQL查询 #

放弃原生查询，使用JPQL让Hibernate自动处理类型映射：

@Query(
    value = """
        SELECT t.id as id, t.fallbackId as fallbackId, t.properties as properties
        FROM TestEntity t
        WHERE t.id < :last_uuid
        ORDER by t.id DESC
    """,
    nativeQuery = false
)
fun findTestForSomeLogic(
    @Param("last_uuid") lastUuid: UUID,
    pageable: Pageable
): List<TestProjection>

调用时传入分页参数实现限制：

val batchSize = 100
testRepository.findTestForSomeLogic(lastUuid, PageRequest.of(0, batchSize))

方法4：使用JPA转换器实现类型转换 #

创建全局转换器，自动处理JSONB与Map的双向转换：

import jakarta.persistence.AttributeConverter
import jakarta.persistence.Converter
import com.fasterxml.jackson.databind.ObjectMapper
import com.fasterxml.jackson.module.kotlin.registerKotlinModule
import java.io.IOException

@Converter(autoApply = true)
class JsonbMapConverter : AttributeConverter<MutableMap<String, Any>, String> {
    private val objectMapper = ObjectMapper().registerKotlinModule()

    override fun convertToDatabaseColumn(attribute: MutableMap<String, Any>?): String? {
        return attribute?.let { objectMapper.writeValueAsString(it) }
    }

    override fun convertToEntityAttribute(dbData: String?): MutableMap<String, Any>? {
        return dbData?.let {
            try {
                objectMapper.readValue(it, mutableMapOf<String, Any>().javaClass)
            } catch (e: IOException) {
                null
            }
        }
    }
}

修改实体类的properties字段配置：

@Column(name = "properties", columnDefinition = "jsonb")
@Convert(converter = JsonbMapConverter::class)
val properties: MutableMap<String, Any> = mutableMapOf()

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