如何使用 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'
推荐答案
我发现 规范 来自 spring 数据的 api.
假设我们有一个名为 Product
的实体和一个名为 title
的 JSON(B) 类型的属性.
我假设此属性包含不同语言的产品标题.例如:{"EN":"Multicolor LED light", "EL":"Πολύχρωμο LED φώς"}
.
下面的源代码通过作为参数传递的标题和语言环境找到一个(或更多,以防它不是唯一字段)产品.
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屋!