Suppose we want to make query between two dates in a timestamp field for example
We have a table colors to store different colors
1 2 3 4 5 6 | create table colors( color_id bigint(10)NOT NULL AUTO_INCREMENT, color varchar(50), add_date timestamp DEFAULT 0, status varchar(20), primary Key(color_id)); |
Pojo class for color table is
1 2 3 4 5 6 | public class Color implements java.io.Serializable { private Long colorId; private String color; private Timestamp addDate; private String status; //getter setter and constructor |
Above class shows that addDate is timestamp field and we want to query it as date for that we have casted timestamp field to date using cast() of hql. To fetching Color details with in date range we can use following code
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | public static void main(String[] s){ ColorDAO dao=new ColorDAO(); List<color> colorList=new ArrayList<color>(); colorList=dao.fetchAll(); for(Color cl:ColorList) { System.out.println(cl.colorId+" "+cl.color) } } public List<color> fetchAll() { Session session = HibernateUtil.getSessionFactory().openSession(); Transaction tx = null; List<color> obj = null; try { tx=session.beginTransaction(); String hql = "from Color as cl " + "where cast(cl.addDate as date) BETWEEN :from AND :to"; Query query = session.createQuery(hql).setFirstResult(0).setMaxResults(30); query.setParameter("from", "2014/4/1").setParameter("to", "2014/6/1"); obj = query.list(); tx.commit(); } catch (HibernateException e) { if (tx != null) { e.printStackTrace(); tx.rollback(); } } finally { session.close(); } return obj; } </color></color></color></color> |