如何在Spring Data中查询JSONB字段并映射为Java对象

我有一个读取产品信息的Repository，代码如下：

import org.springframework.data.jdbc.repository.query.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface ProductRepository extends org.springframework.data.repository.Repository<Product, Long> {

    @Query("SELECT\n" +
        "   p.id,\n" +
        "   p.locations \n" +
        " FROM product p" +
        " WHERE p.id = :productId \n")
    List<Product> queryProducts(@Param("productId") Long productId);
}

数据库中locations字段类型为jsonb，存储对象数组，示例值：[{"city":"New York", "country": "USA"}, {"city":"Madrid", "country": "Spain"}]

对应的实体类：

Product.java #

import lombok.Value;
import org.springframework.data.relational.core.mapping.Table;

import java.util.List;

@Value
@Table
public class Product {
    Long           id;
    List<Location> locations;
}

Location.java #

import lombok.Value;

@Value
public class Location {
    String city;
    String country;
}

运行时抛出异常：

java.lang.IllegalStateException: We need at least one condition
at org.springframework.util.Assert.state(Assert.java:76)
Suppressed: reactor.core.publisher.FluxOnAssembly$OnAssemblyException:

Original Stack Trace:
at org.springframework.util.Assert.state(Assert.java:76)
at org.springframework.data.jdbc.core.convert.SqlGenerator.buildConditionForBackReference(SqlGenerator.java:311)
at org.springframework.data.jdbc.core.convert.SqlGenerator.getFindAllByProperty(SqlGenerator.java:289)
at org.springframework.data.jdbc.core.convert.DefaultDataAccessStrategy.findAllByPath(DefaultDataAccessStrategy.java:303)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:568)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:351)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
at jdk.proxy4/jdk.proxy4.$Proxy101.findAllByPath(Unknown Source)
at org.springframework.data.jdbc.core.convert.MappingJdbcConverter$ResolvingRelationalPropertyValueProvider.getPropertyValue(MappingJdbcConverter.java:379)
at org.springframework.data.jdbc.core.convert.MappingJdbcConverter$ResolvingRelationalPropertyValueProvider.getPropertyValue(MappingJdbcConverter.java:310)
at org.springframework.data.relational.core.conversion.MappingRelationalConverter$2.getPropertyValue(MappingRelationalConverter.java:480)
at org.springframework.data.relational.core.conversion.MappingRelationalConverter$2.getPropertyValue(MappingRelationalConverter.java:461)
at org.springframework.data.mapping.model.PersistentEntityParameterValueProvider.getParameterValue(PersistentEntityParameterValueProvider.java:71)
at org.springframework.data.relational.core.conversion.MappingRelationalConverter$ConvertingParameterValueProvider.getParameterValue(MappingRelationalConverter.java:1164)
at org.springframework.data.mapping.model.SpELExpressionParameterValueProvider.getParameterValue(SpELExpressionParameterValueProvider.java:49)
at org.springframework.data.mapping.model.ClassGeneratingEntityInstantiator.extractInvocationArguments(ClassGeneratingEntityInstantiator.java:301)
at org.springframework.data.mapping.model.ClassGeneratingEntityInstantiator$EntityInstantiatorAdapter.createInstance(ClassGeneratingEntityInstantiator.java:273)
at org.springframework.data.mapping.model.ClassGeneratingEntityInstantiator.createInstance(ClassGeneratingEntityInstantiator.java:98)
at org.springframework.data.relational.core.conversion.MappingRelationalConverter.read(MappingRelationalConverter.java:444)
at org.springframework.data.relational.core.conversion.MappingRelationalConverter.readAggregate(MappingRelationalConverter.java:339)
at org.springframework.data.relational.core.conversion.MappingRelationalConverter.readAggregate(MappingRelationalConverter.java:302)
at org.springframework.data.jdbc.core.convert.MappingJdbcConverter.readAndResolve(MappingJdbcConverter.java:287)
at org.springframework.data.jdbc.core.convert.JdbcConverter.readAndResolve(JdbcConverter.java:106)
at org.springframework.data.jdbc.core.convert.EntityRowMapper.mapRow(EntityRowMapper.java:82)
at org.springframework.data.jdbc.repository.support.JdbcQueryLookupStrategy$PostProcessingRowMapper.mapRow(JdbcQueryLookupStrategy.java:335)
at org.springframework.data.jdbc.repository.query.AbstractJdbcQuery$ConvertingRowMapper.mapRow(AbstractJdbcQuery.java:196)
at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:94)
at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:61)
at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:733)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:658)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:723)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:748)
at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.query(NamedParameterJdbcTemplate.java:178)
at org.springframework.data.jdbc.repository.query.AbstractJdbcQuery.lambda$createSingleReadingQueryExecution$3(AbstractJdbcQuery.java:165)
at org.springframework.data.jdbc.repository.query.StringBasedJdbcQuery.execute(StringBasedJdbcQuery.java:145)
at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:170)
at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:158)
at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:164)
at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:143)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:385)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:220)

我猜测需要自定义映射器实现jsonb到Java对象的转换，但不知道具体操作方法，求解决思路。

1. 问题根源 #

Spring Data JDBC默认会把List<Location>识别为关联实体集合，尝试执行关联查询（比如查找关联的location表数据），但实际上locations是存储在同一个表的jsonb字段里，不是关联表，因此触发了错误的查询逻辑，导致断言失败。

2. 实现自定义类型转换器 #

需要实现Spring Data JDBC的Converter接口，完成数据库jsonb字段与List<Location>的双向转换，这里用Spring默认集成的Jackson处理Json序列化/反序列化。

步骤1：编写Json转换器 #

import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.springframework.core.convert.converter.Converter;
import org.springframework.data.convert.ReadingConverter;
import org.springframework.data.convert.WritingConverter;

import java.util.List;

// 从数据库读取jsonb时转换为List<Location>
@ReadingConverter
public class JsonbToListLocationConverter implements Converter<String, List<Location>> {

    private final ObjectMapper objectMapper;

    public JsonbToListLocationConverter(ObjectMapper objectMapper) {
        this.objectMapper = objectMapper;
    }

    @Override
    public List<Location> convert(String source) {
        try {
            return objectMapper.readValue(source, new TypeReference<List<Location>>() {});
        } catch (Exception e) {
            throw new RuntimeException("Failed to convert jsonb to List<Location>", e);
        }
    }
}

// 写入数据库时把List<Location>转换为json字符串
@WritingConverter
public class ListLocationToJsonbConverter implements Converter<List<Location>, String> {

    private final ObjectMapper objectMapper;

    public ListLocationToJsonbConverter(ObjectMapper objectMapper) {
        this.objectMapper = objectMapper;
    }

    @Override
    public String convert(List<Location> source) {
        try {
            return objectMapper.writeValueAsString(source);
        } catch (Exception e) {
            throw new RuntimeException("Failed to convert List<Location> to jsonb", e);
        }
    }
}

如果是PostgreSQL环境，读取时可能会拿到PGobject类型，需要额外添加读取转换器：

import org.postgresql.util.PGobject;
import org.springframework.core.convert.converter.Converter;
import org.springframework.data.convert.ReadingConverter;

import java.sql.SQLException;
import java.util.List;

@ReadingConverter
public class PGobjectToListLocationConverter implements Converter<PGobject, List<Location>> {

    private final ObjectMapper objectMapper;

    public PGobjectToListLocationConverter(ObjectMapper objectMapper) {
        this.objectMapper = objectMapper;
    }

    @Override
    public List<Location> convert(PGobject source) {
        try {
            return objectMapper.readValue(source.getValue(), new TypeReference<List<Location>>() {});
        } catch (SQLException | com.fasterxml.jackson.core.JsonProcessingException e) {
            throw new RuntimeException("Failed to convert PGobject to List<Location>", e);
        }
    }
}

步骤2：注册转换器到Spring Data JDBC #

创建配置类，将自定义转换器注册到JdbcCustomConversions中：

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.jdbc.core.convert.JdbcCustomConversions;

import java.util.Arrays;

@Configuration
public class JdbcConverterConfig {

    @Bean
    public JdbcCustomConversions jdbcCustomConversions(ObjectMapper objectMapper) {
        return new JdbcCustomConversions(Arrays.asList(
                new JsonbToListLocationConverter(objectMapper),
                new ListLocationToJsonbConverter(objectMapper),
                new PGobjectToListLocationConverter(objectMapper) // PostgreSQL环境需要
        ));
    }
}

3. 明确字段映射关系 #

修改Product类，用@Column注解明确指定locations对应的数据库字段名，避免Spring Data JDBC将其识别为关联实体：

import lombok.Value;
import org.springframework.data.relational.core.mapping.Column;
import org.springframework.data.relational.core.mapping.Table;

import java.util.List;

@Value
@Table
public class Product {
    Long id;
    @Column("locations")
    List<Location> locations;
}

4. 验证查询 #

此时Repository的自定义查询会正常工作，Spring Data JDBC会使用自定义转换器将jsonb字段转换成List<Location>，不会再尝试执行关联查询。

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