最近项目中遇到了这样的情况,mysql数据库中的字段为datetime类型,通过Hibernate映射mysql中的某张表的实体对象类型为java.util.Date,但是我想查找包括当天的时间在内的所有数据信息,比如说:2008-08-08 到今天为止的所有数据,可是当天的数据无法取到,后来和同事找了相关的资料得以解决.
以下是mysql 数据库的表 :
CREATE TABLE `cl_t_loginfo` (
`Id` bigint(20) NOT NULL auto_increment,
`userType` int(1) default NULL,
`role` int(1) default NULL,
`user` varchar(20) default NULL,
`realName` varchar(50) default NULL,
`ip` varchar(20) default NULL,
`table` varchar(50) default NULL,
`tableId` bigint(20) default NULL,
`action` varchar(50) default NULL,
`createDate` datetime default NULL,
PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
这是根据表生成的映射文件 ClTLoginfo.hbm.xml
catalog="db_corplabel">
这是根据表结构生成的实体对象 ClTLoginfo.java
import java.util.Date;
public class ClTLoginfo implements java.io.Serializable {
private Long id;
private Integer userType;
private Integer role;
private String user;
private String realName;
private String ip;
private String tableName;
private Long tableId;
private String action;
private Date createDate;
private Integer operationType;
public ClTLoginfo() {
}
public ClTLoginfo(Long id) {
this.id = id;
}
public ClTLoginfo(Long id, Integer userType, Integer role, String user,
String realName, String ip, String tableName, Long tableId,
String action, Date createDate, Integer operationType) {
this.id = id;
this.userType = userType;
this.role = role;
this.user = user;
this.realName = realName;
this.ip = ip;
this.tableName = tableName;
this.tableId = tableId;
this.action = action;
this.createDate = createDate;
this.operationType = operationType;
}
public Long getId() {
return this.id;
}
public void setId(Long id) {
this.id = id;
}
public Integer getUserType() {
return this.userType;
}
public void setUserType(Integer userType) {
this.userType = userType;
}
public Integer getRole() {
return this.role;
}
public void setRole(Integer role) {
this.role = role;
}
public String getUser() {
return this.user;
}
public void setUser(String user) {
this.user = user;
}
public String getRealName() {
return this.realName;
}
public void setRealName(String realName) {
this.realName = realName;
}
public String getIp() {
return this.ip;
}
public void setIp(String ip) {
this.ip = ip;
}
public String getTableName() {
return this.tableName;
}
public void setTableName(String tableName) {
this.tableName = tableName;
}
public Long getTableId() {
return this.tableId;
}
public void setTableId(Long tableId) {
this.tableId = tableId;
}
public String getAction() {
return this.action;
}
public void setAction(String action) {
this.action = action;
}
public Date getCreateDate() {
return this.createDate;
}
public void setCreateDate(Date createDate) {
this.createDate = createDate;
}
public Integer getOperationType() {
return this.operationType;
}
public void setOperationType(Integer operationType) {
this.operationType = operationType;
}
}
这是我的Hibernate根据时间获取数据的方法
/*
* 分页查询指定时间段的系统日志
*/
public ArrayList getLoginfoByDate(int size, int current, Date startDate, Date endDate) {
ArrayList list = new ArrayList();
try
{
Session session = MySessionFactory.getSession();
Transaction ts = session.beginTransaction();
Query query = session.createQuery(" from ClTLoginfo as c where
c.createDate>=:startDate and c.createDate<=:endDate order by createDate desc");
query.setDate("startDate", startDate);
query.setDate("endDate", endDate);
query.setFirstResult((current-1)*size);
query.setMaxResults(size);
list = (ArrayList)query.list();
ts.commit();
session.close();
}catch(Exception e)
{
e.printStackTrace();
throw new Exception("分页查询指定时间段的系统日志异常");
}
finally
{
return list;
}
}
注意这里,因为数据库里映射的是datetime类型,在hibernate的实体对象对应的类型为 java.util.Date
如果我想获取今天的数据信息,那么用下边的赋值方式肯定无法获取当天的数据信息,
query.setDate("startDate", startDate);
query.setDate("endDate", endDate);
但是我怎么才能获取到当天的数据信息呢?如果我有个操作要看当天的日志怎么办?
我的解决方法很简单,需要将传递的Date类型参数做下重新转换,我可以通过表单获取当天的 年-月-日格式,
比如:2008-08-08,那我就需要将 2008-08-08 后加个时间
改为:2008-08-08 23:59:29,然后将完善的日期转换一下,
String dateStr = "2008-08-08 23:59:59";SimpleDateFormat myformat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Date date = null;
if(strDate.indexOf(":")!= -1)
{
date = myformat.parse(strDate);
}
else
{
date = myformat.parse(strDate);
}
然后可以将此时的这个date传递给这个方法需要的为Date类型的参数了,还有一点就是将上边的
query.setDate("startDate", startDate);
query.setDate("endDate", endDate);
改为:
query.setParameter("startDate", startDate);
query.setParameter("endDate", endDate);
最终结论是:
每个数据库的日期格式都是不一样的.
sqlserver类似字符串用两个单引号('日期字符串').
access是用两个井号(#日期字符串#),
oracle是用to_date(字符串,格式)函数的等.
Hibernate的Query的setDate()方法针对mysql数据库只会保留日期数据,而对时间部分会遗失,
如果在做>或者<之类的时间比较的情况下,不仅会比较前边的日期,还要比较后边的时间部分,
所以查了很多的资料,说可以试试setCalendar()方法.还有一种就是hibernate2.0版本以前有个
Session.find()方法,
Date date = new Date();;
Calendar rightNow = Calendar.getInstance();;
rightNow.setTime(date);;
rightNow.set(Calendar.HOUR_OF_DAY,23);;
rightNow.set(Calendar.MINUTE,59);;
rightNow.set(Calendar.SECOND,59);;
date = rightNow.getTime();;
List list = sess.find("from MyTable where myDate between ? and ?",
new Object[]{date,date},new Type[]{Hibernate.DATE,Hibernate.TIMESTAMP});;
但是这种方法在2.0版本后就不存在了
如果时间部分没有多大的用处,建议将数据库的datetime类型改为date类型是最简单的方法