3

My question is rather simple: I have this query:

 SELECT * FROM TABLE t WHERE 
    (SELECT count(*) FROM TABLE tbis WHERE  TRUNC(t.date) = TRUNC(tbis.date))>1 ;

the field date is a timestamp.

How do I do that with the criteria API?

I have something like this (trunc() is valid with my db server, the sql request works.):

Criteria crit = session.createCriteria(Myclass.class, "t");
crit.createAlias("t.date", "dateT");

DetachedCriteria subcrit = DetachedCriteria.forclass(MyClass.class, "tbis");
subcrit.createAlias("tbis.date", "dateTbis");

subcrit.add(Restrictions.sqlRestriction("TRUNC(dateT) = TRUNC(dateTbis)"));

subcrit.setProjection(Projections.count("id"));

crit .add(Subqueries.gt(1, subcrit));
return crit.list();

Somehow in the log, i got: org.hibernate.QueryException: not an association: date

I tried various things, but couldn't get it to work...

I tried: 

Criteria crit = session.createCriteria(Myclass.class, "t");

DetachedCriteria subcrit = DetachedCriteria.forclass(MyClass.class, "tbis");

subcrit.add(Restrictions.sqlRestriction("TRUNC(t.date) = TRUNC(tbis.date)"));

subcrit.setProjection(Projections.count("id"));

crit .add(Subqueries.gt(1, subcrit));

return crit.list();

and the SQL condition generated was:

TRUNC(t.date) = TRUNC(tbis.date))

with the log:

18:34:04,461 WARN  [JDBCExceptionReporter] SQL Error: 904, SQLState: 42000
18:34:04,461 ERROR [JDBCExceptionReporter] ORA-00904: "T"."DATE": invalid identifier

18:34:04,461 WARN  [CriteriaAdapter] list exception: 
org.hibernate.exception.SQLGrammarException: could not execute query

Also

Criteria crit = session.createCriteria(Myclass.class, "t");

DetachedCriteria subcrit = DetachedCriteria.forclass(MyClass.class, "tbis");

subcrit.add(Restrictions.sqlRestriction("TRUNC({t}.date) = TRUNC({tbis}.date)"));

subcrit.setProjection(Projections.count("id"));

crit .add(Subqueries.gt(1, subcrit));

return crit.list();

with the condition becoming:

TRUNC({t}.date) = TRUNC({tbis}.date))

and the logs:

18:36:28,206 WARN  [TxConnectionManager] Connection error occured: org.jboss.resource.connectionmanager.TxConnectionManager$TxConnectionEventListener@1b3febd3[state=NORMAL mc=org.jboss.resource.adapter.jdbc.xa.XAManagedConnection@4cb16baf handles=1 lastUse=1343284588160 permit=true trackByTx=true mcp=org.jboss.resource.connectionmanager.JBossManagedConnectionPool$OnePool@7f16f514 context=org.jboss.resource.connectionmanager.InternalManagedConnectionPool@3c34353b xaResource=org.jboss.resource.adapter.jdbc.xa.XAManagedConnection@4cb16baf txSync=null]
java.lang.NullPointerException
at oracle.jdbc.driver.T4C8Oall.getNumRows(T4C8Oall.java:1153)
[...]
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)

Or even: 

Criteria crit = session.createCriteria(Myclass.class, "t");

DetachedCriteria subcrit = DetachedCriteria.forclass(MyClass.class, "tbis");

subcrit.add(Restrictions.sqlRestriction("TRUNC({t.date}) = TRUNC({tbis.date})"));

subcrit.setProjection(Projections.count("id"));

crit .add(Subqueries.gt(1, subcrit));

return crit.list();

With the same result as previously.

I think criteria is unable to detect the alias of the subquery... But I have to access that value, any workaround?

mabi
  • 5,279
  • 2
  • 43
  • 78
alith
  • 131
  • 2
  • 10
  • have you tried `TRUNC( dateT ) = TRUNC( dateTbis )`? note the spaces – Firo Jul 27 '12 at 06:47
  • no, but I will, but basically i resigned my self and wrote a 300 long function generating a HQL query. – alith Aug 02 '12 at 23:22
  • It's probably too late now, but `createAlias()` expects a table, you can't alias columns. I'd go ahead with the second snippet but try to escape the reserved word 'date': `TRUNC(t."date")`. Note [this question](http://stackoverflow.com/questions/1162381/how-do-i-escape-a-reserved-word-in-oracle) with regard to identifier escaping. – mabi Nov 20 '13 at 08:56

1 Answers1

0

First you can try if you shouldn't put the { } only around the alias in the sqlRestriction :

subcrit.add(Restrictions.sqlRestriction("TRUNC({t}.date) = TRUNC({tbis}.date)"));

If you don't want to compare a part of the date fields (like the days), but want to see if the entire date fields are equal, you can just let Hibernate do the comparison like this:

Restrictions.eq(t.date, tbis.date)

If you want to compare a part of a date field, I refer you to this question.

Community
  • 1
  • 1
K.C.
  • 2,084
  • 2
  • 25
  • 38
  • The referenced question just says to use `sqlRestriction`, too? And yeah AFAIK `TRUNC()` returns the date portion of the timestamp. – mabi Nov 20 '13 at 08:47
  • well, the first answer used HQL and not sqlRestriction. But I think I see the problem now. The { } in the sqlRestriction should be only round the alias: like this: subcrit.add(Restrictions.sqlRestriction("TRUNC({t}.date) = TRUNC({tbis}.date)")); I will put it in my anwer – K.C. Nov 20 '13 at 09:24