CODEWITHSUNDEEP
hibernatecriteria
Arjun Prajapati
Arjun Prajapati

Reputation: 273

How to write conditional order by clause criteria in hibernate?

I need to convert below query in hibernate criteria.

Any one have idea how to write this order by clause in hibernate?

select * from StudentMasterTable where SchoolId = 90 and SchoolName IS NOT NULL ORDER BY IIF(StudentType = 'O',StudentName,SchoolName)

Upvotes: 1

Views: 1452

Answers (2)

Arjun Prajapati
Arjun Prajapati

Reputation: 273

finally i found solution for this.

i created a custom class to override the criteria formula.

public class customOrderBy extends Order {
    private String sqlFormula;


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


    public static Order customOrder(String sqlFormula) {
        return new OrderBySqlFormula(sqlFormula);
    }
}

And i called this as below

criteria.addOrder(customOrderBy.customOrder("IIF(StudentType = 'O',StudentName,SchoolName)"));

Upvotes: 1

Rika
Rika

Reputation: 796

I believe i did something like this to deal with dynamic orderBys:

//make a method that will take in a string which is I guess your studentType column(you probably should have shown your entity class for StudentMasterTable, I assumed studentType was type of String). 

String orderByClause(String s){
String result = "";
if(s.equals('O'))
   result = "s.studentType";

   return result;

}

//then do the below

"select s from StudentMasterTable s where s.schoolId = '90' and s.schoolName is not    null
order by " +  orderByClause(s.studentType) + ",s.studentName,s.schoolName";

Upvotes: 0

Related Questions