带有非字符串类型过滤器的Criteria查询- JPA/Hibernate Spring Boot

Question

我正试图从表MyEntity中获取行数，使用带过滤器的条件查询。

这个过滤器(函数参数)是一个JsonNode, key (JsonNode过滤器)作为实体的列，value (JsonNode过滤器)可以是String或JsonNode类型，这取决于用户传递的过滤器。

。

如果filter为{"policy": {"id": "123"}}，则搜索值为json类型{"id": "123"}的列policy，

如果filter为{"string_column": "test_string"}，则搜索值为字符串类型test_string的列string_column。

使用的数据库是PostgreSQL

我的代码-

public Long getCountForMyEntity(JsonNode filter) {
CriteriaBuilder criteriaBuilder = sessionFactory.getCriteriaBuilder();
CriteriaQuery<Long> query = criteriaBuilder.createQuery(Long.class);
Root<MyEntity> root = query.from(MyEntity.class);
query.select(criteriaBuilder.count(root));
Iterator<Map.Entry<String, JsonNode>> filterFields = filter.fields();
while(filterFields.hasNext()) {
Map.Entry<String, JsonNode> field = filterFields.next();
String   fieldName  = field.getKey();
JsonNode fieldValue = field.getValue();
Object value = fieldValue;
System.out.println(fieldValue + " : " + fieldValue.getNodeType());
if (fieldValue.getNodeType() == JsonNodeType.STRING) {
value = fieldValue.asText();
}
query.where(criteriaBuilder.equal(root.get(fieldName), value));
}
return sessionFactory.getCurrentSession().createQuery(query).getSingleResult();
}

当过滤器值为字符串时，这适用于获取计数，但当过滤器值为json时，我得到以下错误-

44- [WARN ] 2021-10-26 14:42:35 [http-nio-9090-exec-7] o.h.e.j.s.SqlExceptionHelper [][][][] - SQL Error: 0, SQLState: 42883
45- [ERROR] 2021-10-26 14:42:35 [http-nio-9090-exec-7] o.h.e.j.s.SqlExceptionHelper [][][][] - ERROR: operator does not exist: json = unknown
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Position: 121
46- [WARN ] 2021-10-26 14:42:35 [http-nio-9090-exec-7] o.s.w.s.m.m.a.ExceptionHandlerExceptionResolver [][][][] - Resolved [org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet]

我已经尝试像这样直接传递fieldValue，但仍然是相同的错误-

query.where(criteriaBuilder.equal(root.get(fieldName), fieldValue));

你知道我哪里做错了吗?是否有过滤json/非字符串值的好方法?

Answer 1

使用JSONB代替JSON或将JSON转换为JSONB。不能比较JSON值。您可以比较文本值。您可以使用JSONB类型来解决这个问题，它以分解的二进制格式存储。该类型的值可以比较。

SELECT 
'["dramatic" ,"women", "political"]'::json::text =  
'["dramatic","women","political"]'::json::text      -- yields false!

SELECT 
'["dramatic" ,"women", "political"]'::jsonb =  
'["dramatic","women","political"]'::jsonb           -- yields true

所以你的查询应该像这样工作以消除错误。

SELECT * 
FROM movie_test 
WHERE tags::jsonb = '["dramatic","women","political"]'::jsonb

Answer 2

@JoinColumn注释指定用作目标实体上的外键的列的名称。第一个字段应该是id，也许有这个问题，所以试试这个，或者它可能发生，因为这里的数据字段可以有一个空值，空不是Double，空也不是null。这里你可以通过

在这里查看数据字段

Answer 3

从Narasimha的强制转换技术中获得灵感，但强制转换为String而不是jsonb。它先将存储在db中的json转换为String，然后再将传入的JsonNode转换为String。

query.where(criteriaBuilder.equal(root.get(fieldName).as(String.class), fieldValue.toString()));

完整代码如下-

public Long getCountForMyEntity(JsonNode filter) {
CriteriaBuilder criteriaBuilder = sessionFactory.getCriteriaBuilder();
CriteriaQuery<Long> query = criteriaBuilder.createQuery(Long.class);
Root<MyEntity> root = query.from(MyEntity.class);
query.select(criteriaBuilder.count(root));
Iterator<Map.Entry<String, JsonNode>> filterFields = filter.fields();
while(filterFields.hasNext()) {
Map.Entry<String, JsonNode> field = filterFields.next();
String   fieldName  = field.getKey();
JsonNode fieldValue = field.getValue();
System.out.println(fieldValue + " : " + fieldValue.getNodeType());
if (fieldValue.getNodeType() == JsonNodeType.STRING) {
query.where(criteriaBuilder.equal(root.get(fieldName), fieldValue.asText()));
} else {
query.where(criteriaBuilder.equal(root.get(fieldName).as(String.class), fieldValue.toString()));
}
}
return sessionFactory.getCurrentSession().createQuery(query).getSingleResult();
}