Is it possible to use analytic functions in Hibernate?

Question

Is there a way to use sql-server like analytic functions in Hibernate?

Something like

select foo from Foo foo where f.x = max(f.x) over (partition by f.y)

Answer 1

You are after a native SQL query.

If you are using JPA the syntax is:

Query q = em.createNativeQuery("select foo.* from Foo foo " +
                               "where f.x = max(f.x) over " +
                               "(partition by f.y)", Foo.class);

If you need to return multiple types, take a look at the SQLResultSetMapping annotation.

If you're using the the Hibernate API directly:

Query q = session.createSQLQuery("select {foo.*} from Foo foo " +
                                 "where f.x = max(f.x) over "+
                                 "(partition by f.y)");
q.addEntity("foo", Foo.class);

See 10.4.4. Queries in native SQL in the Hibernate documentation for more details.

In both APIs you can pass in parameters as normal using setParameter.

Answer 2

Another approach would be to use the mapping. Please see this article: https://forums.hibernate.org/viewtopic.php?f=1&t=998482

I am against the usage of native SQL queries in Hibernate... you lose the benefits of having a mapping:-)