带有oracle12c JSON_VALUE函数的JPA Criteria API [英] JPA Criteria api with oracle12c JSON_VALUE function
问题描述
我正在尝试使用JSON_VALUE function()创建Criteria API查询是Oracle12c:
I'm trying to crete Criteria API query with JSON_VALUE function() is Oracle12c:
select * from orderswhere (JSON_VALUE(duty, '$.department') in ( ? , ? , ? ,? , ? , ? , ?));
型号:
@Entity
@Table(name = "orders")
@EntityListeners(AuditingEntityListener.class)
public class Trade implements Serializable {
@Convert(converter = JpaConverterJson.class)
private Map<String, Object> duty;
.....
}
private Specification<InvoicePurchase> reportFormToSpec(Map<String, Object>
params) {
return (root, query, cb) -> {
List<Predicate> predicates = new ArrayList<>();
if (!StringUtils.isEmpty(params.get("department"))) {
Predicate predicate = cb.like(cb.function("json_value", String.class,
root.get("duty"),
cb.literal("$.department"))
, params.get("department"));
predicates.add(predicate);
}
return query.where(predicates.toArray(new Predicate[predicates.size()])).getRestriction();
};
}
但是出现异常:o.h.engine.jdbc.spi.SqlExceptionHelper:ORA-40454:路径表达式不是文字
But getting exception: o.h.engine.jdbc.spi.SqlExceptionHelper: ORA-40454:path expression not a literal
我正在网上搜索很长时间.但是没用.请提供帮助或尝试提供一些想法,以实现这一目标.
I am searching for a long time on net. But no use. Please help or try to give some ideas how to achieve this.
错误代码:
2017-11-23 12:26:55.271 WARN 11972 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 40454, SQLState: 99999
2017-11-23 12:26:55.271 ERROR 11972 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper : ORA-40454: path expression not a literal
推荐答案
不起作用的原因是,Hibernate在后台会生成这样的SQL:
The reason this is not working, is that in the background, Hibernate will generate such an SQL:
SELECT * FROM T WHERE JSON_VALUE(?, ?)=?
..,这不适用于Oracle.
.. and this does not work with Oracle.
解决方案是内联这些值.首先是一个小助手类:
The solution is, to inline these values. First a small helper class:
public class HibernateInlineExpression extends LiteralExpression<String> {
public HibernateInlineExpression(CriteriaBuilder criteriaBuilder, String literal) {
super((CriteriaBuilderImpl) criteriaBuilder, literal);
}
@Override
public String render(RenderingContext renderingContext) {
return getLiteral();
}
}
使用上述方法,您现在可以在谓词中执行JSON表达式:
Using the above, you can now do JSON expressions in predicates:
cb.equal(cb.function("JSON_VALUE", String.class,
new HibernateInlineExpression(cb, "JSON_COLUMN"),
new HibernateInlineExpression(cb, "'$.my.json.property'")),
new LiteralExpression<>((CriteriaBuilderImpl) cb, String.class, "somevalue"));
当然,以上是特定于Hibernate的实现,EclipseLink等的用户必须提出类似的建议.请参阅我的有关使用JSON函数的博客文章来自JPA/Hibernate的Oracle 的一些其他技巧.
Of course the above is a Hibernate-specific implementation, users of EclipseLink, etc. have to come up with something similar. See my blog post about using JSON functions of Oracle from JPA/Hibernate for some other tricks.
这篇关于带有oracle12c JSON_VALUE函数的JPA Criteria API的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!