[Hibernate-issues] [Hibernate-JIRA] Commented: (HHH-1700) HAVING clause not supported in Criteria API

[Alexandre K. (JIRA) <no...@at...>]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-1700?pa=
ge=3Dcomments#action_23481 ]=20

Alexandre KOZLOV commented on HHH-1700:
---------------------------------------

The same problem with aliases, but I can't resolve it...

If I didn't use alias at all, the generated SQL looks correct:
select pos_val_cod from POSITION this_ where this_.POS_DARR=3D? group by po=
s_val_cod having min(pos_Cours) > 10

But it gives

Hibernate: select pos_val_cod from POSITION this_ where this_.POS_DARR=3D? =
group by pos_val_cod having min(pos_Cours) > 10
java.lang.NullPointerException
=09at org.hibernate.loader.criteria.CriteriaLoader.getResultColumnOrRow(Cri=
teriaLoader.java:107)
Exception come originally from CriteriaLoader who can't find aliases.

If I call your class with an alias (with the second StringBuffer, of course=
), SQL is:
select pos_val_cod as toto from POSITION this_ where this_.POS_DARR=3D? gro=
up by pos_val_cod having min(pos_Cours) > 10

But it doesn't work neither:
INFO  - could not read column value from result set: toto; Echec de convers=
ion dans la repr=C3=A9sentation interne
WARN  - SQL Error: 17059, SQLState: null
ERROR - Echec de conversion dans la repr=C3=A9sentation interne
org.hibernate.exception.GenericJDBCException: could not execute query

Any ideas? Hibernate 3.1.

> HAVING clause not supported in Criteria API
> -------------------------------------------
>
>          Key: HHH-1700
>          URL: http://opensource.atlassian.com/projects/hibernate/browse/H=
HH-1700
>      Project: Hibernate3
>         Type: New Feature

>   Components: query-criteria
>     Versions: 3.1.3
>  Environment: Hibernate 3.1.3, Oracle 10g (not DB specific)
>     Reporter: Manuel Dominguez Sarmiento
>     Priority: Minor
>  Attachments: CustomProjections.java
>
> Original Estimate: 2 hours
>         Remaining: 2 hours
>
> Currently the Criteria API does not support the HAVING clause.  I have cr=
eate a factory class which creates Projection instances using sqlGroupProje=
ction.  Probably not the best approach, but an idea which could be further =
developed.  These factory methods could be included in the Projections fact=
ory class.
> Usage example w/one property:
> criteria.setProjection(CustomProjections.groupByHaving("id", Hibernate.LO=
NG, "count(distinct id) >=3D 3"));
> Also allows aliases:
> criteria.setProjection(CustomProjections.groupByHaving("id", "idAlias", H=
ibernate.LONG, "count(distinct idAlias) >=3D 3"));
> Usage example w/many properties:
> String[] columns =3D new String[2];
> columns[0] =3D "id";
> columns[1] =3D "name";
> String[] aliases =3D new String[2];
> aliases[0] =3D "idAlias";
> aliases[1] =3D "nameAlias";
> Type[] types =3D new Type[2];
> types[0] =3D Hibernate.LONG;
> types[1] =3D Hibernate.STRING;
> criteria.setProjection(CustomProjections.groupByHaving(columns, aliases, =
types, "count(distinct id) >=3D 3 and name=3D'george'"));

--=20
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://opensource.atlassian.com/projects/hibernate/secure/Administrators=
.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira