2

I'm switching from deprecated (unfortunately) Hibernate Criteria API to JPA Criteria API. We have a custom Order (from Hibernate) interface implementation to redefine SQL generated for it. The case is quite sophisticated as we need to use a giant SELECT with subqueries. We implemented toSqlString method of the interface to return this huge SQL and we need a way to migrate it to JPA Criteria API.

The question is: is there a way in JPA Criteria API to redefine the SQL generated? Or is there a weird way to use Hibernate Order with JPA Criteria API?

Thank you!

UPDATE Although @Tobias Liefke suggestion is quite interesting, my SQL varies too much to create a function class per SQL. I tried implementing a single function class and passing the SQL there as an argument but that didn't work (the rendered SQL was enclosed in single quotes thus it was sent to the database as parameter and not as part of the generated query)

Dmitry Senkovich
  • 5,521
  • 8
  • 37
  • 74

1 Answers1

3

You can't use SQL fragments in JPQL or criteria queries...

... except when ...

1. Calling a function

JPA and Hibernate allow to use functions in their expressions, for example:

... ORDER BY trim(entity.label) ASC

Resp.

query.orderBy(criteriaBuilder.asc(
    criteriaBuilder.function("trim", String.class, root.get(ExampleEntity_.label))));

The problem is, that this is not really the call to the SQL function trim, but the call to a JPA function, which must be registered (Hibernate does this already for the most common SQL functions).

Fortunately you can define your own JPA functions in a DialectResolver:

public class MyDialectResolver implements DialectResolver {

  public Dialect resolveDialect(final DialectResolutionInfo info) {
    Dialect dialect = StandardDialectResolver.INSTANCE.resolve(info);
    dialect.registerFunction("myOrderFunction", ...);
    return dialect;
  }

}

registerFunction takes two parameters, the first is the name of the function in JPA, the other is the mapping to SQL.

Don't forget to declare your dialect resolver in your persistence.xml:

<persistence-unit name="database">
  <properties>
    <property name="hibernate.dialect_resolvers" 
              value="my.package.MyDialectResolver" />
  </properties>
</persistence-unit>

You could now create your own function in your SQL server which contains your huge SQL and register that as function:

dialect.registerFunction("myOrderFunction", 
  new StandardSQLFunction("myOrderFunctionInSQL", StringType.INSTANCE));

Or you could write your own mapping, which includes your huge SQL:

public class MyOrderFunction implements SQLFunction {

  public String render((Type firstArgumentType, List arguments,
      SessionFactoryImplementor factory) throws QueryException) {
    return my_huge_SQL;
  }

  // ...
}

And register that one:

dialect.registerFunction("myOrderFunction",  new MyOrderFunction());

Another advantage of this solution: you could define different SQLs depending on the actual database dialect.

2. Using a formula

You could use an additional attribute for your entity:

@Formula("my huge SQL")
private String orderAttribute;

You could now sort by this attribute:

... ORDER BY entity.orderAttribute ASC

Resp.

query.orderBy(criteriaBuilder.asc(root.get(ExampleEntity_.orderAttribute))));

I only recommend this solution, if you need the result of the huge SQL in your model anyway. Otherwise it will only pollute your entity model and add the SQL to every query of your entity (except you mark it with @Basic(fetch = FetchType.lazy) and use byte code instrumentation).

A similar solution would be to define a @Subselect entity with the huge SQL - with the same drawbacks.

Tobias Liefke
  • 8,637
  • 2
  • 41
  • 58
  • very interesting, thank you very much! so, I may use the first solution without creating an actual function in e.g. Oracle and simply implementing SQLFunction interface, right? – Dmitry Senkovich Oct 09 '18 at 16:05
  • Right. In fact the first solution contains two "flavors": One with a custom SQL function and one with the mapping interface. – Tobias Liefke Oct 09 '18 at 16:11
  • that's just great! wonderful solution, I guess, the best in such case:) – Dmitry Senkovich Oct 09 '18 at 16:12
  • the approach doesn't seem to be working. eventually, the function returns SQL but it gets enclosed into brackets just as usual string. even Hibernate shows its type as VARCHAR. is there a way to render the function as SQL, not string? – Dmitry Senkovich Oct 12 '18 at 12:58
  • Which kind of brackets do you mean? Strings are usually enclosed in single quotes (`'`), not in brackets. I've implemented more than one `SQLFunction` myself and am sure, that there is usually no quote or bracket added by Hibernate. – Tobias Liefke Oct 15 '18 at 12:54
  • sorry, my bad, I meant quotes. I see HIbernate generating a SQL query where my rendered function is marked as `?` meaning it is a parameters to the query. Then I see Hibernate logs it as `VARCHAR2` parameter – Dmitry Senkovich Oct 15 '18 at 13:08
  • Which Hibernate version do you use and how do you include the function in your criteria? There has to be some kind of error, otherwise Hibernate functions would never work. – Tobias Liefke Nov 01 '18 at 12:31
  • sorry for not reply so long. the Hibernate version is 5.2.17.FINAL. I've tried to add it like `criteriaBuilder.equal(myFunction, true)`, something like this. I don't quite remember and don't have the code because we've decided to simply concatenate SQL – Dmitry Senkovich Nov 01 '18 at 15:23
  • the function was simply returning its first argument. and this argument was the order SQL – Dmitry Senkovich Nov 01 '18 at 15:24
  • That is the problem, because any literal argument is converted to a parameter and the argument you receive is just the parameter name and _not_ the plain argument. You should stick to my example and return the plain SQL from your function. – Tobias Liefke Nov 01 '18 at 16:31
  • I mean I did just like you showed. I've created a function returning String. But this string is not fixed, it is passed to this function as String parameter. Sorry, maybe I misunderstand you – Dmitry Senkovich Nov 01 '18 at 16:37
  • If you look at my example, I wrote `return my_huge_SQL;`. I didn't say that you could use the arguments to provide different SQLs - which you can't. You will have to write one function for each of your SQL fragments, if you can't build the difference between the fragments as simple argument. I mean if the SQLs differ like this `... where attribute = 123` and `... where attribute = 456` you could indeed use the 123 resp. 456 as argument and would still only need one function. – Tobias Liefke Nov 01 '18 at 16:46
  • it is weird it is not working in case I want to pass an argument. However, in my case I would have to write a function class per sql. I don't think it is possible for me. Anyway, thanks for suggestion! – Dmitry Senkovich Nov 01 '18 at 16:52