实例一:
当数据库里的时间取的是服务器系统时间时如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;
}
}