如何使用Spring Data JPA查询jsonb列? [英] How do I use spring data jpa to query jsonb column?

问题描述

我在针对Postgres 9.4实例正确获取本机查询时遇到问题.

I'm having a problem getting this native query right against a postgres 9.4 instance.

我的存储库有一个方法:

My repository has a method:

 @Query(value = "SELECT t.* " +
            "FROM my_table t " +
            "WHERE t.field_1 = ?1 " +
            "AND t.field_2 = 1 " +
            "AND t.field_3 IN ?2 " +
            "AND t.jsonb_field #>> '{key,subkey}' = ?3",
            nativeQuery = true)
    List<Entity> getEntities(String field1Value,
                                   Collection<Integer> field3Values,
                                   String jsonbFieldValue);

但是日志显示如下:

SELECT t.* FROM my_table t 
WHERE t.field_1 = ?1 
  AND t.field_2 = 1 
  AND t.field_3 IN ?2 
  AND t.jsonb_field ? '{key,subkey}' = ?3

我得到这个异常:

内部异常:org.postgresql.util.PSQLException:无值 为参数2指定.

Internal Exception: org.postgresql.util.PSQLException: No value specified for parameter 2.

我直接在方法调用之前记录了参数，并且都已提供.

I logged the parameters directly before method invocation, and they are all supplied.

我不确定为什么#>>在日志中显示?.我是否需要转义#>>?我是否需要格式化IN的集合?我需要转义json路径吗?

I'm not sure why #>> shows ? in the log. Do I need to escape #>>? Do I need to format the collection for IN? Do I need to escape the json path?

当我直接对数据库执行查询时，它可以工作.示例:

When I execute the query directly against the db, it works. Example:

SELECT *
FROM my_table t
WHERE t.field_1 = 'xxxx'
  AND t.field_2 = 1
  AND t.field_3 IN (13)
  AND t.jsonb_field #>> '{key,subkey}' = 'value'

推荐答案

我发现.
下面的源代码通过标题和语言环境(作为参数)找到了一个(或更多情况下，如果不是唯一字段的话)产品.

I found very helpful the Specification api from spring data.
Let's say we have an entity with name Product and a property with name title of type JSON(B).
I assume that this property contains the title of the Product in different languages. An example could be: {"EN":"Multicolor LED light", "EL":"Πολύχρωμο LED φώς"}.
The source code below finds a (or more in case it is not a unique field) product by title and locale passed as arguments.

@Repository
public interface ProductRepository extends JpaRepository<Product, Integer>, JpaSpecificationExecutor<Product> {
}


public class ProductSpecification implements Specification<Product> {

    private String locale;
    private String titleToSearch;

    public ProductSpecification(String locale, String titleToSearch) {
        this.locale = locale;
        this.titleToSearch = titleToSearch;
    }

    @Override
    public Predicate toPredicate(Root<Product> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
        return builder.equal(builder.function("jsonb_extract_path_text", String.class, root.<String>get("title"), builder.literal(this.locale)), this.titleToSearch);
    }
}


@Service
public class ProductService {

    @Autowired
    private ProductRepository productRepository;

    public List<Product> findByTitle(String locale, String titleToSearch) {
        ProductSpecification cs = new ProductSpecification(locale, titleToSearch);
        return productRepository.find(cs);
        // Or using lambda expression - without the need of ProductSpecification class.
//      return productRepository.find((Root<ProductCategory> root, CriteriaQuery<?> query, CriteriaBuilder builder) -> {
//          return builder.equal(builder.function("jsonb_extract_path_text", String.class, root.<String>get("title"), builder.literal(locale)), titleToSearch);
//      });
    }
}

您可以在此处找到有关使用Spring Data方式的另一个答案. .
希望有帮助.

You can find another answer about the way you should use the Spring Data here.
Hope that helps.

这篇关于如何使用Spring Data JPA查询jsonb列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！