CODEWITHSUNDEEP
javasqlhibernatehibernate-criteria
mtyson
mtyson

Reputation: 8550

How to express this SQL in Hibernate Criteria (Possible?)

Please take a look at this SO answer:

MySQL get row position in ORDER BY

Basically, getting the position of a row when there is an order by clause. Is it possible to express this in Hibernate criteria?

Here is the query as I have it. What I'm really looking for is an elegant way to set dynamic where clause params:

SELECT x.id, 
       x.position
  FROM (SELECT dwq.id,
               @rownum := @rownum + 1 AS position
          FROM default_work_queue dwq
          JOIN (SELECT @rownum := 0) r 
          WHERE dwq.type=4 *AND FOO=BAR*
      ORDER BY dwq.description ASC) x
 WHERE x.id=540;

That is, I want to be able to add 0-n number of extra 'and foo=bar' to the noted part of the quere, without doing a bunch of string manipulation.

Upvotes: 0

Views: 248

Answers (1)

Firo
Firo

Reputation: 30803

it is not possible with criteria because criteria does not support arbitrary sql in the FROM-clause. However here's another idea for this query

// select the count of all elements before the wanted element
int position = session.createCriteria(Work.class)
    .add(restrictions)
    .add(Subqueries.lt("description", DetachedCriteria.For(Work.class)
        .add(Restriction.eq("id", id))
        .setProjection(Projection.Property("description"))))
    .setProjection(Projection.RowCout())
    .UniqueResult<int>();

you could optimise the subquery away if you have the description already loaded

Upvotes: 1

Related Questions