Hibernate. Optimizing sqlRestriction

Question

How can I optimize the following without sqlRestriction:

public List<SomeEntity> getEntityByYearMonthAndDay(Calendar cal) {
        Criteria criteria = helper.createCriteria();
        criteria.add(sqlRestriction("(DATE(date_column) = ?)", cal.getTime(), org.hibernate.type.StandardBasicTypes.DATE));
        return criteria.list();
}

SomeEntity looks like:

    @Entity
    @Table(name="some_table")
    public class SomeEntity extends Identifiable {


        @Column(name = "date_column")
        private Calendar dateColumn;

//Getters and setters 
    }

In the DB I have such representation: date_column => datetime (YYYY-MM-DD HH:mm:ss).

It's obvious that logic compares only date truncating time values.

Igor Petrov · Accepted Answer · 2013-03-19 15:12:21Z

3

Yeah, guys :) So if you want not to use built-in MySQL DATE(...) function and use only JPA:

public List<SomeEntity> getEntityByYearMonthAndDay(Calendar cal) {
    Criteria criteria = helper.createCriteria();
    Calendar leftBorder = Calendar.getInstance();
    leftBorder.setTime(cal.getTime());
    Calendar rightBorder = Calendar.getInstance();
    rightBorder.setTime(cal.getTime());
    rightBorder.add(Calendar.DATE, 1);
    Conjunction dateConjunction = Restrictions.conjunction();
    dateConjunction.add(Restrictions.eq("dateColumn", leftBorder));
    dateConjunction.add(Restrictions.lt("dateColumn", rightBorder));
    criteria.add(dateConjunction);
    return criteria.list();
}

answered Mar 19, 2013 at 15:12

Igor Petrov

1012 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Hibernate. Optimizing sqlRestriction

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related