CODEWITHSUNDEEP
javamysqlhibernate
Rahul P
Rahul P

Reputation: 1113

Hibernate: sorting data by date & time

In mysql database I have a table named bug_list

tables name: bug_list

id    projectId    created_date  
1     5            2012-06-30 13:30:57  
2     5            2012-07-30 10:30:58  
3     5            2012-07-30 12:30:44  
4     5            2012-07-31 13:30:14  
5     5            2012-07-31 14:30:32

I want to sort it by month (like jan, feb... ) in the first result,
sort it by week day (like mon, tue, wed) in the second result
and sort it by hour (like 2hrs range say 8-10am, 10-12pm....) in the third result

I can't understand from criteria query. Can somebody explain how to resolve this issue?

Upvotes: 1

Views: 5147

Answers (2)

mostar
mostar

Reputation: 4831

HERE you can find something useful.

Start by extending Hibernate Order class:

public class CustomizedOrderBy extends Order {
    private String sqlExpression;

    protected CustomizedOrderBy(String sqlExpression) {
        super(sqlExpression, true);
        this.sqlExpression = sqlExpression;
    }

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

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

    public String toString() {
        return sqlExpression;
    }

}

Then use some SQL formula:

criteria.addOrder(CustomizedOrderBy.sqlFormula("MONTH("+sortBy.getInnerName()+")"+ asc));
criteria.addOrder(CustomizedOrderBy.sqlFormula("WEEKDAY("+sortBy.getInnerName()+")"+ asc));
criteria.addOrder(CustomizedOrderBy.sqlFormula("HOUR("+sortBy.getInnerName()+")"+ asc));

Upvotes: 1

Puggan Se
Puggan Se

Reputation: 5846

  1. "i want to sort it by month(like jan, feb... ) in first result"

    ORDER BY MONTH(created_date)

  2. "sort it by weekly(like mon, tue, wen) in second result"

    ORDER BY WEEKDAY(created_date)

  3. sort it by hour(like 2hrs range say 8-10am, 10-12pm....) in third result 

    ORDER BY HOUR(created_date)

    or by time spans

    ORDER BY CASE
    WHEN HOUR(created_date) BETWEEN 8 AND 9 THEN 1
    WHEN HOUR(created_date) BETWEEN 10 AND 12 THEN 2
    WHEN HOUR(created_date) BETWEEN 13 AND 17 THEN 3
    ELSE 4
    END

Upvotes: 2

Related Questions