[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-2381) Criteria API : Order by Native SQL

[Sami Dalouche (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2381?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_27945 ] 

Sami Dalouche commented on HHH-2381:
------------------------------------

OK, Here is a sample implementation of the Order class that can be used to produce Native SQL ORDER BY Clauses :

public class NativeSQLOrder extends Order {
private static final long serialVersionUID = 1L;
private final static String PROPERTY_NAME = "uselessAnyways";
private boolean ascending;
private String sql;

protected NativeSQLOrder(String sql, boolean ascending) {
super(PROPERTY_NAME, ascending);
this.sql = sql;
this.ascending = ascending;

}

@Override
public String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery)
throws HibernateException {
StringBuilder fragment = new StringBuilder();
fragment.append("(");
fragment.append(sql);
fragment.append(")");
fragment.append(ascending ? " asc" : " desc");
return StringHelper.replace(fragment.toString(), "{alias}",
criteriaQuery.getSQLAlias(criteria));
}

}

criteria.addOrder(new Order("whatever SQL using {alias}", true));

Regards,
Sami Dalouche

> Criteria API : Order by Native SQL 
> -----------------------------------
>
>                 Key: HHH-2381
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2381
>             Project: Hibernate3
>          Issue Type: Improvement
>          Components: query-criteria
>            Reporter: Sami Dalouche
>
> Hi,
> It would be nice to be able to specify the ORDER BY clause using Native SQL. (It is currently doable by subclassing the Order class, but some nice solution would allow the order to specify attributes on another table..)
> Let's take a simple example : I have a distance("attribute", geometry) function, that is of course, proprietary.
> I am quering table A, which I can join to table B to get the "attribute", on which I want to apply my distance() function.
> If I create my own Order subclass, and addOrder on the criteria, the  distance("attribute, geometry) is going to refer to the main criteria's class mapping. 
> So, something like addOrder("objectpath.joined", new MyOrder(...)); would be perfect.
> What do you think ?
> Regards,
> Sami Dalouche

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