Spring WebFlux+R2DBC操作PostgreSQL JSONB的动态查询与格式转换问题

1. 实现@Query注解的动态参数查询 #

基础动态参数绑定 #

直接用命名参数绑定替代硬编码，Spring Data R2DBC支持通过@Param注解将方法参数映射到SQL中的占位符，彻底避免硬编码值或字段名：

public interface ProductRepository extends ReactiveCrudRepository<Product, Long> {
    // 动态指定JSONB嵌套字段名和匹配值
    @Query("SELECT * FROM products WHERE pdata->>:nestedKey = :targetValue")
    Flux<Product> findByNestedProperty(@Param("nestedKey") String nestedKey, @Param("targetValue") String targetValue);
}

复杂动态多条件查询 #

如果需要根据可选参数动态拼接SQL，推荐使用Spring Data R2DBC的Criteria API，无需手动拼接SQL，同时规避SQL注入风险：

public interface ProductRepository extends ReactiveCrudRepository<Product, Long>, ReactiveQueryByExampleExecutor<Product> {
    default Flux<Product> findByDynamicFilters(String category, Integer minPrice) {
        Criteria criteria = Criteria.empty();
        
        if (category != null) {
            // 匹配JSONB中的category字段
            criteria = criteria.and("pdata.category").is(category);
        }
        if (minPrice != null) {
            // 匹配JSONB中的price字段大于等于指定值
            criteria = criteria.and("pdata.price").greaterThanOrEqualTo(minPrice);
        }
        
        return findAll(criteria);
    }
}

2. 解决JSONB字段返回带转义字符串的问题 #

默认情况下R2DBC会将JSONB字段映射为String类型，导致返回结果出现转义字符。可以通过以下两种方式解决：

方式1：使用JsonObject类型映射 #

直接将实体类中的pdata字段声明为org.springframework.data.relational.core.sql.JsonObject，Spring Data R2DBC会自动处理JSON的序列化/反序列化：

import org.springframework.data.annotation.Id;
import org.springframework.data.relational.core.mapping.Column;
import org.springframework.data.relational.core.sql.JsonObject;

public class Product {
    @Id
    private Long id;
    
    // 标注字段类型为jsonb，自动映射为JsonObject
    @Column(columnDefinition = "jsonb")
    private JsonObject pdata;

    // Getter & Setter
}

方式2：映射为自定义POJO #

如果需要将JSONB字段直接映射为业务POJO，需要添加自定义转换器并注册到R2DBC配置中：

步骤1：定义业务POJO

public class ProductData {
    private String category;
    private Double price;
    private String brand;

    // Getter & Setter
}

步骤2：实现序列化/反序列化转换器

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.io.IOException;

// 从数据库JSON字符串转为POJO
@ReadingConverter
public class JsonbToProductDataConverter implements Converter<String, ProductData> {
    private final ObjectMapper objectMapper;

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

    @Override
    public ProductData convert(String source) {
        try {
            return objectMapper.readValue(source, ProductData.class);
        } catch (IOException e) {
            throw new IllegalArgumentException("Failed to parse JSON to ProductData", e);
        }
    }
}

// 从POJO转为数据库JSON字符串
@WritingConverter
public class ProductDataToJsonbConverter implements Converter<ProductData, String> {
    private final ObjectMapper objectMapper;

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

    @Override
    public String convert(ProductData source) {
        try {
            return objectMapper.writeValueAsString(source);
        } catch (IOException e) {
            throw new IllegalArgumentException("Failed to serialize ProductData to JSON", e);
        }
    }
}

步骤3：注册转换器到R2DBC配置

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.r2dbc.convert.R2dbcCustomConversions;
import org.springframework.data.r2dbc.dialect.PostgresDialect;
import com.fasterxml.jackson.databind.ObjectMapper;
import java.util.List;

@Configuration
public class R2dbcConfig {
    @Bean
    public R2dbcCustomConversions r2dbcCustomConversions(ObjectMapper objectMapper) {
        return R2dbcCustomConversions.of(PostgresDialect.INSTANCE,
                List.of(new JsonbToProductDataConverter(objectMapper), new ProductDataToJsonbConverter(objectMapper)));
    }
}

步骤4：修改实体类字段类型

import org.springframework.data.annotation.Id;
import org.springframework.data.relational.core.mapping.Column;

public class Product {
    @Id
    private Long id;
    
    @Column(columnDefinition = "jsonb")
    private ProductData pdata;

    // Getter & Setter
}

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