CODEWITHSUNDEEP
javahibernatejpahibernate-criteriacriteria-api
Dmitry Senkovich
Dmitry Senkovich

Reputation: 5911

Custom SQL for Order in JPA Criteria API

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)

Upvotes: 2

Views: 3757

Answers (1)

Tobias Liefke
Tobias Liefke

Reputation: 9022

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.

Upvotes: 4

Related Questions