hibernate按时间段查询带时分秒日期的方法

hibernate按时间段查询带时分秒日期的方法

 

当数据库里的时间取的是服务器系统时间时如2008-10-05 23:33:39.0这种时间，要根据此字段来按时间段查询时，普通的方法来查，是查不到<=这个时间的数据的，必须要通过如下方式来设置起始日期和结束日期的时分秒才行：

Java.util.Date nd=new java.util.Date()

nd.setHours(hours);//起始日期小时为0，结束日期小时为23
nd.setMinutes(minutes);//起始日期分为0，结束日期分为59
nd.setSeconds(seconds);//起始日期秒为0，结束日期秒为59

 

DAO代码示例如下：

public List findAll(java.util.Date tdTimeB, java.util.Date tdTimeE, String tkPlate,
    String crName, Integer tdStatus, SplitPage sp) {
   try {
    StringBuffer sb = new StringBuffer();
    sb.append("from BlTransportDispatchView b where 1=1 ");
    if (tdTimeB != null && !"".equals(tdTimeB) && tdTimeE != null && !"".equals(tdTimeE)) {
     sb.append(" and (b.tdTime >= :tdTimeB and b.tdTime <= :tdTimeE)");
    }
    if (tkPlate != null && !"".equals(tkPlate)) {
     sb.append(" and b.tkPlate like :tkPlate");
    }
    if (crName != null && !"".equals(crName)) {
     sb.append(" and b.crName like :crName");
    }
    if(tdStatus!=-1){
     sb.append(" and b.tdStatus = :tdStatus");
    }
    sb.append(" order by b.tdTime,b.crName,b.tkPlate");
    Session session = getHibernateTemplate().getSessionFactory().getCurrentSession();
    Query q = session.createQuery(sb.toString());
    Query qc = session.createQuery("select count(*) " + sb.toString());
    if (tdTimeB != null && !"".equals(tdTimeB) && tdTimeE != null && !"".equals(tdTimeE)) {
    tdTimeB.setHours(0);
     tdTimeB.setMinutes(0);
     tdTimeB.setSeconds(0);
     tdTimeE.setHours(23);
     tdTimeE.setMinutes(59);
     tdTimeE.setSeconds(59);
    q.setTimestamp("tdTimeB", tdTimeB);
     q.setTimestamp("tdTimeE", tdTimeE);
     qc.setTimestamp("tdTimeB", tdTimeB);
     qc.setTimestamp("tdTimeE", tdTimeE);
    }
    if (tkPlate != null && !"".equals(tkPlate)) {
     q.setString("tkPlate", "%" + tkPlate + "%");
     qc.setString("tkPlate", "%" + tkPlate + "%");
    }
    if (crName != null && !"".equals(crName)) {
     q.setString("crName", "%" + crName + "%");
     qc.setString("crName", "%" + crName + "%");
    }
    if(tdStatus!=-1){
     q.setInteger("tdStatus", tdStatus);
     qc.setInteger("tdStatus", tdStatus);
    }
    List lc = qc.list();
    Iterator iter = lc.iterator();
    Integer count = 0;
    while (iter.hasNext()) {
     count = (Integer) iter.next();
    }
    sp.setCountResult(count);// 总记录数
    sp.setTotalPage((count + sp.getMaxResults() - 1) / sp.getMaxResults());// 总页数
    q.setFirstResult(sp.getFirstResult());// 分页开始记录数
    q.setMaxResults(sp.getMaxResults());// 每页最大记录数
    session.flush();
    return q.list();
   } catch (RuntimeException re) {
    throw re;
   }
}

 

实例二：

import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;

public class GenHql {
private String hql = "";

public static void main(String args[]) {
GenHql genHql = new GenHql();
Timestamp startTime = Timestamp.valueOf("2010-03-12 19:11:40");
Timestamp endTime = Timestamp.valueOf("2010-03-16 19:11:40");
genHql.setHqlByTimestamp(startTime);
String hql1 = "select * from Test t where 1=1 " + genHql.getHql();
genHql.setHqlByTimestamp(startTime, endTime);
String hql2 = "select * from Test t where 1=1 " + genHql.getHql();
System.out.println("开始时间到现在" + hql1);
System.out.println("开始时间到结束时间" + hql2);
}

// 根据开始时间和结束时间生成hql语句
public void setHqlByTimestamp(Timestamp startTime, Timestamp endTime) {
String beginDate = "";
String endDate = "";
SimpleDateFormat f = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

if (startTime != null && startTime.toString() != "") {
// startTime = Timestamp.valueOf("2010-03-12 19:11:40");
Calendar cal = Calendar.getInstance();
cal.setTime(startTime);
Date begin = cal.getTime();
beginDate = f.format(begin);
hql += " and t.timestamp>=to_date('" + beginDate
+ "','YYYY-MM-DD HH24:MI:SS')";
}

if (endTime == null || endTime.toString().equals("")) {
Date date = new Date();
endDate = f.format(date);
hql += " and t.timestamp<=to_date('" + endDate
+ "','YYYY-MM-DD HH24:MI:SS')";
} else {
// startTime = Timestamp.valueOf("2010-03-12 19:11:40");
Calendar cal = Calendar.getInstance();
cal.setTime(endTime);
Date end = cal.getTime();
endDate = f.format(end);
hql += " and t.timestamp<=to_date('" + endDate
+ "','YYYY-MM-DD HH24:MI:SS')";
}
System.out.println(hql);
}

// 根据开始时间生成hql语句
public void setHqlByTimestamp(Timestamp startTime) {
String beginDate = "";
String endDate = "";
hql = "";
SimpleDateFormat f = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

if (startTime != null && startTime.toString() != "") {
// startTime = Timestamp.valueOf("2010-03-12 19:11:40");
Calendar cal = Calendar.getInstance();
cal.setTime(startTime);
Date begin = cal.getTime();
beginDate = f.format(begin);
hql = " and t.timestamp>=to_date('" + beginDate
+ "','YYYY-MM-DD HH24:MI:SS')";
}
}

public String getHql() {
return hql;
}

public void setHql(String hql) {
this.hql = hql;
}
}