浏览 4915 次
锁定老帖子 主题:oracle中日期查询问题
精华帖 (0) :: 良好帖 (0) :: 新手帖 (0) :: 隐藏帖 (0)
|
|
---|---|
作者 | 正文 |
发表时间:2006-07-26
Action层的代码: pageIndex = this.parameters.getInt("pageIndex"); String submitTime = this.parameters.getString("submitTime"); Long serverId = this.parameters.getLong( "serverId"); String bodyStatus = this.parameters.getString( "bodyStatus"); Long roomId = this.parameters.getLong( "roomId"); String mobileNo= this.parameters.getString("mobileNo"); Long userId = this.parameters .getLong( "userId" ); MessageQuery mq = new MessageQuery(); String start = submitTime + " 00:00:00"; String end = submitTime + " 23:59:59"; mq.setBodyStatus(bodyStatus); mq.setEndTime(Format.toDate(end)); mq.setMobileNo(mobileNo); mq.setRoomId(roomId); mq.setServerId(serverId); mq.setStartTime(Format.toDate(start)); mq.setUserId(userId); List list = Global.getChatMessageService().searchChatMessages(mq,pageSize,pageIndex); int pageAmount = Global.countPage(pageSize, Global.getChatMessageService().searchCount(mq)); 注:submitTime代表日期,这里要查询的是一天的记录! MessageQuery 辅助类代码: public class MessageQuery { private Date startTime; private Date endTime; private Long serverId; private String bodyStatus; private Long roomId; private String mobileNo; private Long userId; public void genQueryCondition(Query query) { if(this.startTime != null) { query.setDate("startTime", this.startTime); } if(this.endTime != null) { query.setDate("endTime", this.endTime); } if(this.serverId != null) { query.setLong("serverId", this.serverId.longValue()); } if(this.bodyStatus != null && !"".equals(this.bodyStatus)) { query.setString("bodyStatus", "%" + this.bodyStatus + "%"); } if(this.roomId != null) { query.setLong("roomId", this.roomId.longValue()); } if(this.mobileNo != null && !"".equals(this.mobileNo)) { query.setString("mobile", "%" + this.mobileNo + "%"); } if(this.userId != null) { query.setLong("userId", this.userId.longValue()); } } public String getHQLCondition() { StringBuffer buffer = new StringBuffer("from ChatMessage chatMessage where "); if(this.startTime != null) { buffer.append("chatMessage.submitTime >= :startTime"); } if(this.endTime != null) { buffer.append(" and chatMessage.submitTime <= :endTime"); } if(this.serverId != null) { buffer.append(" and chatMessage.serverId = :serverId"); } if(this.bodyStatus != null && !"".equals(bodyStatus)) { buffer.append(" and chatMessage.bodyStatus like :bodyStatus"); } if(this.roomId != null) { buffer.append(" and chatMessage.roomId = :roomId"); } if(this.mobileNo != null && !"".equals(mobileNo)){ buffer.append(" and chatMessage.mobileNo like :mobileNo"); } if(this.userId != null) { buffer.append(" and chatMessage.userId = :userId"); } buffer.append(" order by chatMessage.id desc"); return buffer.toString(); } public String getBodyStatus() { return bodyStatus; } public String getMobileNo() { return mobileNo; } public Long getRoomId() { return roomId; } public Long getServerId() { return serverId; } public Date getStartTime() { return startTime; } public Long getUserId() { return userId; } public Date getEndTime() { return endTime; } public void setEndTime(Date endTime) { this.endTime = endTime; } public void setBodyStatus(String bodyStatus) { this.bodyStatus = bodyStatus; } public void setMobileNo(String mobileNo) { this.mobileNo = mobileNo; } public void setRoomId(Long roomId) { this.roomId = roomId; } public void setServerId(Long serverId) { this.serverId = serverId; } public void setStartTime(Date submitTime) { this.startTime = submitTime; } public void setUserId(Long userId) { this.userId = userId; } } service层代码: public List searchChatMessages(MessageQuery mq , int pageSize, int pageIndex) { Session session = getSession(); Query query = session.createQuery( mq.getHQLCondition()); mq.genQueryCondition(query); query.setMaxResults(pageSize); query.setFirstResult(pageIndex * pageSize); return query.list(); } public int searchCount(MessageQuery mq) { Session session = getSession(); Query query = session.createQuery(mq.getHQLCondition()); mq.genQueryCondition(query); List total = query.list(); return total.size(); } 运行以后没有报任何错误后台显示的sql语句也是正常的,明明有符合查询条件的数据,但是返回来的结果一直都是[],很疑惑,问题出在哪里呢? 声明:ITeye文章版权属于作者,受法律保护。没有作者书面许可不得转载。
推荐链接
|
|
返回顶楼 | |
发表时间:2006-07-26
请先搜索相关帖子:
http://forum.iteye.com/viewtopic.php?t=20450&highlight=oracle+date |
|
返回顶楼 | |
发表时间:2006-07-27
呵呵,谢谢dogstar提供的链接!上面的方法基本都用过了,没有能够解决问题的!我发现自己遇到的这个问题在论坛中很少被提到,但是用oracle数据库可能不小心就会遇到!
我最终的做法: f(this.startTime != null && !"".equals(this.startTime)) { buffer.append("chatMessage.submitTime >=to_date('"); buffer.append(this.startTime); buffer.append("','YYYY-MM-DD HH24:MI:SS')"); } if(this.endTime != null && !"".equals(this.endTime)) { buffer.append(" and chatMessage.submitTime <=to_date('"); buffer.append(this.endTime); buffer.append("','YYYY-MM-DD HH24:MI:SS')"); } 替换原来的: if(this.startTime != null) { buffer.append("chatMessage.submitTime >= :startTime"); } if(this.endTime != null) { buffer.append(" and chatMessage.submitTime <= :endTime"); } 应该是个日期转换问题! |
|
返回顶楼 | |
发表时间:2006-07-27
继续上面的问题!
StringBuffer buffer = new StringBuffer("from ChatMessage cMessage where cMessage.userId="); buffer.append(userId); buffer.append(" and cMessage.roomId="); buffer.append(roomId); buffer.append(" and cMessage.submitTime >=?"); Date time = null; try { time = format.parse(beginTime); } catch (ParseException e) { e.printStackTrace(); } List list = this.getHibernateTemplate().find(buffer.toString(), time); 我用的是Hibernate 3,数据库还是oracle9i的,这里的time格式是"yyyy-MM-dd HH:hh:ss".这条查询语句能被正确的执行! 我导入的是: import org.springframework.orm.hibernate3.support.HibernateDaoSupport; 是不是可以说this.getHibernateTemplate().find(string,object)内部对于日期进行了某些转换? |
|
返回顶楼 | |