Is it possible to create a QueryDSL Predicate with a String of raw SQL?

6
votes

I have a Java, GraphQL, Hibernate, PostgreSQL, QueryDSL application that queries a very large PostgreSQL table with over 275 columns.

I've created a GraphQL schema with the 25 most popular columns as query-able fields. I'd like to add a generic "field" input type that consists of a name (the db column name + "_" + operation (like gte, gt, contains, etc.) and a value (the value the user is searching for).

So when the user (in GraphiQL) enters something like (field:{name:"age_gt", value:"50"}) as a search input to the GraphQL query, I can come up with: "age > 50".

All that works fine, but when it's time to create the Predicate and add it to the whole query ( booleanBuilder.and(new Predicate) ), I cannot figure out how to create a Predicate that just contains a raw String of SQL ("age > 50").

I've created several Predicates the "right" way using my entity POJO tied to Hibernate and the jpa generated "Q" object. But I need the ability to add one or more Predicates that are just a String of SQL. I'm not even sure if the ability exists, the documentation for QueryDSL Predicates is non-existent.

I'm thinking PredicateOperation() might be the answer, but again, no documentation and I cannot find any examples online.

My apologies for not posting code, all my stuff is behind a firewall on a different network so there's no cut and paste to my internet machine.

1
javapostgresqlhibernatejpaquerydsl
Did you find a solution? - Wiebke
Wiebke, it has been a long time since I worked on this project. I reviewed my code just now and it doesn't look like I ever created a Predicate with just a string of SQL. But I do remember completing the task and I was able to do everything I needed to do without this capability. Also, nowhere in my project code do I use PredicateOperation. I do parse out the field and operation from the key, then send the operation through a simple case statement and build the Predicate that way. So if I get key,value of (age_lt, 50) the case statement creates: Predicate p = path.lt(50); Hope this helps. - PaulG

1 Answers

0
votes

In Hibernate its possible to inject arbitrary SQL using custom functions or the FUNCTION-function (introduced in JPA 2.1). In QueryDSL its possible to inject arbitrary JPQL/HQL through TemplateExpressions. Combined you get:

Expressions.numberTemplate("FUNCTION('SUM', {0}), x)

However, age > 50 as expression is probably valid JPQL as well, so one can just write:

Expressions.numberTemplate("SUM(age)")

Either way, its probably best to create a visitor that traverses the GraphQL query and creates the proper expression in QueryDSL, as TemplateExpressions are prone to SQL injection.