hibernate-issues

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

[Manuel D. S. (JIRA) <no...@at...>]

HAVING clause not supported in Criteria API
-------------------------------------------

         Key: HHH-1700
         URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-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

Currently the Criteria API does not support the HAVING clause.  I have create a factory class which creates Projection instances using sqlGroupProjection.  Probably not the best approach, but an idea which could be further developed.  These factory methods could be included in the Projections factory class.

Usage example w/one property:
criteria.setProjection(CustomProjections.groupByHaving("id", Hibernate.LONG, "count(distinct id) >= 3"));

Also allows aliases:
criteria.setProjection(CustomProjections.groupByHaving("id", "idAlias", Hibernate.LONG, "count(distinct idAlias) >= 3"));

Usage example w/many properties:

String[] columns = new String[2];
columns[0] = "id";
columns[1] = "name";

String[] aliases = new String[2];
aliases[0] = "idAlias";
aliases[1] = "nameAlias";

Type[] types = new Type[2];
types[0] = Hibernate.LONG;
types[1] = Hibernate.STRING;

criteria.setProjection(CustomProjections.groupByHaving(columns, aliases, types, "count(distinct id) >= 3 and name='george'"));


-- 
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

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

[Lionel (JIRA) <no...@at...>]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-1700?page=comments#action_23134 ] 

Lionel commented on HHH-1700:
-----------------------------

Hi !

I need this too !!
It would be very usefull.

Your idea works nearly fine, the only problem is that you add the alias in the group by clause
The sql generated looks like this: select count(id), xxx as myAlias from table group by xxx as myAlias having count(...)>0
Simply use another StringBuffer without the alias to specify the groupBy.

Tested on Hibernate 3.0.5 / Oracle 10g

> HAVING clause not supported in Criteria API
> -------------------------------------------
>
>          Key: HHH-1700
>          URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-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 create a factory class which creates Projection instances using sqlGroupProjection.  Probably not the best approach, but an idea which could be further developed.  These factory methods could be included in the Projections factory class.
> Usage example w/one property:
> criteria.setProjection(CustomProjections.groupByHaving("id", Hibernate.LONG, "count(distinct id) >= 3"));
> Also allows aliases:
> criteria.setProjection(CustomProjections.groupByHaving("id", "idAlias", Hibernate.LONG, "count(distinct idAlias) >= 3"));
> Usage example w/many properties:
> String[] columns = new String[2];
> columns[0] = "id";
> columns[1] = "name";
> String[] aliases = new String[2];
> aliases[0] = "idAlias";
> aliases[1] = "nameAlias";
> Type[] types = new Type[2];
> types[0] = Hibernate.LONG;
> types[1] = Hibernate.STRING;
> criteria.setProjection(CustomProjections.groupByHaving(columns, aliases, types, "count(distinct id) >= 3 and name='george'"));

-- 
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

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

[Manuel D. S. (JIRA) <no...@at...>]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-1700?page=comments#action_23139 ] 

Manuel Dominguez Sarmiento commented on HHH-1700:
-------------------------------------------------

True, dumb mistake.  I didn't thoroughly test the code with aliases.

> HAVING clause not supported in Criteria API
> -------------------------------------------
>
>          Key: HHH-1700
>          URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-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 create a factory class which creates Projection instances using sqlGroupProjection.  Probably not the best approach, but an idea which could be further developed.  These factory methods could be included in the Projections factory class.
> Usage example w/one property:
> criteria.setProjection(CustomProjections.groupByHaving("id", Hibernate.LONG, "count(distinct id) >= 3"));
> Also allows aliases:
> criteria.setProjection(CustomProjections.groupByHaving("id", "idAlias", Hibernate.LONG, "count(distinct idAlias) >= 3"));
> Usage example w/many properties:
> String[] columns = new String[2];
> columns[0] = "id";
> columns[1] = "name";
> String[] aliases = new String[2];
> aliases[0] = "idAlias";
> aliases[1] = "nameAlias";
> Type[] types = new Type[2];
> types[0] = Hibernate.LONG;
> types[1] = Hibernate.STRING;
> criteria.setProjection(CustomProjections.groupByHaving(columns, aliases, types, "count(distinct id) >= 3 and name='george'"));

-- 
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

[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

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

[Manuel D. S. (JIRA) <no...@at...>]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-1700?page=comments#action_23490 ] 

Manuel Dominguez Sarmiento commented on HHH-1700:
-------------------------------------------------

I've executed a query similar to the one you describe (same query, different table) directly in Oracle and it works fine. Judging from the error code, it seems like the ? parameter replacement does not match the SQL type in the table. Make sure you pass the correct array of Hibernate Type instances to the CustomProjections factory, that sounds like the culprit. You can get instances of Type from static fields in class Hibernate (Hibernate.LONG, Hibernate.STRING, etc.)

> HAVING clause not supported in Criteria API
> -------------------------------------------
>
>          Key: HHH-1700
>          URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-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 create a factory class which creates Projection instances using sqlGroupProjection.  Probably not the best approach, but an idea which could be further developed.  These factory methods could be included in the Projections factory class.
> Usage example w/one property:
> criteria.setProjection(CustomProjections.groupByHaving("id", Hibernate.LONG, "count(distinct id) >= 3"));
> Also allows aliases:
> criteria.setProjection(CustomProjections.groupByHaving("id", "idAlias", Hibernate.LONG, "count(distinct idAlias) >= 3"));
> Usage example w/many properties:
> String[] columns = new String[2];
> columns[0] = "id";
> columns[1] = "name";
> String[] aliases = new String[2];
> aliases[0] = "idAlias";
> aliases[1] = "nameAlias";
> Type[] types = new Type[2];
> types[0] = Hibernate.LONG;
> types[1] = Hibernate.STRING;
> criteria.setProjection(CustomProjections.groupByHaving(columns, aliases, types, "count(distinct id) >= 3 and name='george'"));

-- 
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