Micronaut+Kotlin中如何将JsonNode存储为PostgreSQL的JSONB类型

问题说明 #

将包含JsonNode属性的实体保存到PostgreSQL时，因JsonNode无法直接映射到JSONB类型报错，错误信息如下：

Caused by: io.micronaut.data.exceptions.DataAccessException: Error executing PERSIST: Unable to set PreparedStatement value: Can't infer the SQL type to use for an instance of com.fasterxml.jackson.databind.node.ObjectNode. Use setObject() with an explicit Types value to specify the type to use.

数据库表结构：

CREATE TABLE IF NOT EXISTS configuration_data (
   id SERIAL NOT NULL PRIMARY KEY,
   namespace_name TEXT NOT NULL,
   dimension_id TEXT NOT NULL,
   dimension_value TEXT,
   path TEXT,
   data JSONB,
   version BIGINT DEFAULT 0
);

对应实体类：

@MappedEntity(value = "configuration_data")
data class ConfigurationDataEntity(
    @field:Id @GeneratedValue
    val id: Long,
    @MappedProperty("namespace_name")
    val namespaceName: String,
    @MappedProperty("dimension_id")
    val dimensionId: String,
    @MappedProperty("dimension_value")
    val dimensionValue: String,
    @MappedProperty("path")
    val path: String,
    @MappedProperty("data")
    val data: JsonNode,
    @MappedProperty("version")
    val version: Long,
)

解决方法 #

方法1：实现Micronaut TypeConverter #

通过自定义类型转换器，将JsonNode与PostgreSQL的PGobject（JSONB类型载体）互相转换：

1.1 JsonNode转PGobject转换器

import com.fasterxml.jackson.databind.JsonNode
import com.fasterxml.jackson.databind.ObjectMapper
import org.postgresql.util.PGobject
import jakarta.inject.Singleton
import io.micronaut.core.convert.ConversionContext
import io.micronaut.core.convert.TypeConverter
import java.util.Optional

@Singleton
class JsonNodeToPGobjectConverter(private val objectMapper: ObjectMapper) : TypeConverter<JsonNode, PGobject> {
    override fun convert(source: JsonNode, targetType: Class<PGobject>, context: ConversionContext): Optional<PGobject> {
        return try {
            val pgObject = PGobject()
            pgObject.type = "jsonb"
            pgObject.value = objectMapper.writeValueAsString(source)
            Optional.of(pgObject)
        } catch (e: Exception) {
            context.reject(e)
            Optional.empty()
        }
    }
}

1.2 PGobject转JsonNode转换器

import com.fasterxml.jackson.databind.JsonNode
import com.fasterxml.jackson.databind.ObjectMapper
import org.postgresql.util.PGobject
import jakarta.inject.Singleton
import io.micronaut.core.convert.ConversionContext
import io.micronaut.core.convert.TypeConverter
import java.util.Optional

@Singleton
class PGobjectToJsonNodeConverter(private val objectMapper: ObjectMapper) : TypeConverter<PGobject, JsonNode> {
    override fun convert(source: PGobject, targetType: Class<JsonNode>, context: ConversionContext): Optional<JsonNode> {
        return if ("jsonb" == source.type) {
            try {
                Optional.of(objectMapper.readTree(source.value))
            } catch (e: Exception) {
                context.reject(e)
                Optional.empty()
            }
        } else {
            Optional.empty()
        }
    }
}

Micronaut会自动识别并使用这些转换器，无需额外配置实体类。

方法2：使用JPA AttributeConverter #

若项目基于JPA，可实现AttributeConverter并绑定到实体属性：

2.1 实现转换器

import com.fasterxml.jackson.databind.JsonNode
import com.fasterxml.jackson.databind.ObjectMapper
import jakarta.persistence.AttributeConverter
import jakarta.persistence.Converter
import org.postgresql.util.PGobject
import java.sql.SQLException

@Converter(autoApply = true)
class JsonNodeJsonbConverter(private val objectMapper: ObjectMapper) : AttributeConverter<JsonNode, PGobject> {
    override fun convertToDatabaseColumn(attribute: JsonNode?): PGobject? {
        if (attribute == null) return null
        val pgObject = PGobject()
        pgObject.type = "jsonb"
        try {
            pgObject.value = objectMapper.writeValueAsString(attribute)
        } catch (e: Exception) {
            throw SQLException("转换JsonNode到JSONB失败", e)
        }
        return pgObject
    }

    override fun convertToEntityAttribute(dbData: PGobject?): JsonNode? {
        if (dbData == null || dbData.value == null) return null
        return try {
            objectMapper.readTree(dbData.value)
        } catch (e: Exception) {
            throw SQLException("转换JSONB到JsonNode失败", e)
        }
    }
}

2.2 绑定到实体属性

修改实体类的data属性，添加@Convert注解：

@MappedProperty("data")
@Convert(converter = JsonNodeJsonbConverter::class)
val data: JsonNode,

注意事项 #

• 确保项目已引入PostgreSQL JDBC驱动依赖
• ObjectMapper由Micronaut默认注入，无需手动实例化

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