JPA Criteria api with oracle12c JSON_VALUE function

Question

I'm trying to crete Criteria API query with JSON_VALUE function() is Oracle12c:

select * from orderswhere (JSON_VALUE(duty, '$.department') in ( ? , ? , ? ,? , ? , ? , ?));

MODEL:

@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();
 };
 }

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.

ERROC CODE:

 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

• Alex.Bob: error message:2017年02月15日 14:22:13.210 WARN 23176 --- [nio-8080-exec-7] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 40454, SQLState: 99999 2017年02月15日 14:22:13.210 ERROR 23176 --- [nio-8080-exec-7] o.h.engine.jdbc.spi.SqlExceptionHelper : ORA-40454: path expression not a literal

Answer 1

The reason this is not working, is that in the background, Hibernate will generate such an SQL:

SELECT * FROM T WHERE JSON_VALUE(?, ?)=?

.. 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();
 }
}

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"));

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.

• karl li: Thanks for your answer, it really helps. I have used this solution for function "JSON_EXISTS", but this time I have no luck...can you pls take a look at my question? stackoverflow.com/questions/75681678/…