Grails with Criteria

I want to use NVL Function in Grails Criteria for orderBy clause. How shall I used? I have tried multiple approach. Can anybody help me out ?

SQL Query to converted into Grails Criteria : Select * forom Domain_Table order by NVL(field1,field2) ASC

Tried Approach 1:

Domain.createCriteria().list(max:10,offset:10){
   order(field1,'ASC')
   order(field2,'ASC')
}

Working properly, But Generating SQL Query like

Select * forom Domain_Table order by field1 ASC,field2 ASC

which is not satisfying my requirement

Approach 2 :

 Domain.createCriteria().list(max:10,offset:10){
       order(nvl(field1,field2),'ASC')
    }

Error : Nvl Not a property of domain class

Approach 3 :

Domain.createCriteria().list(max:10,offset:10){
   projections{
  addProjectionToList(Projections.sqlProjection("nvl(field1,field2) as description", ['description'] as String[], [Hibernate.STRING] as Type[]), 'description'))
           order('description,'ASC')
        }

Problem : I am getting the record based on the exact order . But, Again I am hitting database with description to get the Domain Which I don't want. One more option is there, I can put property(name,alias) which I'll get in response. But, I need the entire domain to be queried.

Please help me out.

---

You can use @Formula for an artificial field on Entity (let's name it nvl ) and then use it in ordering.

Or you can use custom implementation of Order

public class OrderBySqlFormula extends Order {
    private String sqlFormula;

    /**
     * Constructor for Order.
     * @param sqlFormula an SQL formula that will be appended to the resulting SQL query
     */
    protected OrderBySqlFormula(String sqlFormula) {
        super(sqlFormula, true);
        this.sqlFormula = sqlFormula;
    }

    public String toString() {
        return sqlFormula;
    }

    public String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery) throws HibernateException {
        return sqlFormula;
    }

    /**
     * Custom order
     *
     * @param sqlFormula an SQL formula that will be appended to the resulting SQL query
     * @return Order
     */
    public static Order sqlFormula(String sqlFormula) {
        return new OrderBySqlFormula(sqlFormula);
    }
}

and use criteria.addOrder(OrderBySqlFormula.sqlFormula("(a + b) desc"));

from here

http://blog.tremend.ro/2008/06/10/how-to-order-by-a-custom-sql-formulaexpression-when-using-hibernate-criteria-api/

---

Since you must to use a criteria, without modifying the domain class your third approach is the closest to a solution.

You can add the domain class id to the projections, grab all of the IDs, and finally, call Domain.getAll() to return the instances. You'll need two queries, but the second one uses the primary key, so it'll run as fast as possible.

def ids = Domain.createCriteria().list(max:10,offset:10){
   projections{
      property('id')
      sqlProjection("nvl(field1,field2) as description", 'description', Hibernate.STRING)           
   }
   order('description','ASC')
}*.getAt(0) // Only selects the first column: the ID.

def instances = Domain.getAll(ids)

链接地址: http://www.djcxy.com/p/24338.html

上一篇: 使用MYSQL时，Grails标准查询失败并出现synatx错误

下一篇: 符合标准的Grails