[hibernate-issues] [Hibernate-JIRA] Updated: (HHH-1043) Added HAVING Support to Criteria

[Chris Wilson (JIRA)]

     [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-1043?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Chris Wilson updated HHH-1043:
------------------------------

    Attachment: GenericLogicalExpression.java
                FunctionExpression.java
                MultiColumnGroupByProjection.java

I'm attaching a slightly better (IMHO) version of the GroupByHavingProjection. It supports querying on multiple-column properties (e.g. we have a composite primary key and want to group by it), and using any Criterion (Restrictions.*) for the Having clause.

You can use it like this:

{code}
Criterion having = new GenericLogicalExpression(
    new FunctionExpression("SUM", "mi.ltuQty"), "<>",
    new FunctionExpression("SUM", "mo.ltuQty"));
rl.setProjection(new MultiColumnGroupByProjection("id", having));
{code}

Which results in:

{code:sql}
group by line.id, line.request_site_id 
HAVING SUM(mi.ltu_qty) <> SUM(mo.ltu_qty);
{code}

However, it requires a change to CriteriaLoader.getResultColumnOrRow to support multiple columns (composite type properties):

{code}
protected Object getResultColumnOrRow(Object[] row,
    ResultTransformer transformer, ResultSet rs, SessionImplementor session)
throws SQLException, HibernateException
{
    final Object[] result;
    final String[] tableAliases;
    
    CriteriaQueryTranslator translator = getTranslator();
    
    if (translator.hasProjection())
    {
        Type[] types = translator.getProjectedTypes();
        result = new Object[types.length];
        int currentColumn = 0;
        String[] columnAliases = translator.getProjectedColumnAliases();

        for (int i = 0; i < types.length; i++)
        {
            int span = types[i].getColumnSpan(session.getFactory());
            result[i] = types[i].nullSafeGet(rs,
                ArrayHelper.slice(columnAliases, currentColumn, span),
                session, null);
            currentColumn += span;
        }
        
        tableAliases = translator.getProjectedAliases();
    }
    else
    {
        result = row;
        tableAliases = m_UserAliases;
    }
    
    return translator.getRootCriteria().getResultTransformer()
            .transformTuple(result, tableAliases);
}
{code}

If you need to apply this in a stable release of Hibernate without patching, you can use the RitaCriteriaQuery:

http://rita.wfplogistics.org/trac/browser/rita/src/org/wfp/rita/db/RitaCriteriaQuery.java

and call it like this:

{code}
RitaCriteriaQuery query = new RitaCriteriaQuery(criteria,
    m_Environment.getSessionFactory());
return ((SessionImplementor) m_Session).listCustomQuery(query,
    query.getQueryParameters());
{code}

> Added HAVING Support to Criteria
> --------------------------------
>
>                 Key: HHH-1043
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-1043
>             Project: Hibernate Core
>          Issue Type: Patch
>          Components: query-criteria
>    Affects Versions: 3.1 rc 1
>         Environment: 3.1 rc 1
>            Reporter: Tony Voss
>            Priority: Minor
>         Attachments: criteria-having-improvement.patch, criteria-having-improvement.zip, FunctionExpression.java, GenericLogicalExpression.java, GroupByHavingProjection.java, MultiColumnGroupByProjection.java
>
>
> I've added support for HAVING clauses to Criteria. Attached are my code changes.

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