问题描述
如题,Oracle数据库里面有一批记录,现在我想根据其中一个叫:txn_date的字段来查询记录,在Hbm文件中我配置了该HQL,如下所示:<sql-query name="job.getJobByDate"> select {job.*} from Newsletter_Message job where to_char(:dateType,'yyyy-mm-dd hh24:mi') = to_char(:jobDate, 'yyyy-mm-dd hh24:mi') <return alias="job" class="com.newsletter.domain.job.Job" /></sql-query>在DAO方法中我使用了Spring的HibernateTemplate,方法如下:public List<Job> getJobByDate(final Date jobDate, final String dateType) { return (List<Job> getHibernateTemplate().executeFind( new HibernateCallback() { public Object doInHibernate(Session session) throws HibernateException { Query query = session.getNamedQuery"job.getJobByDate"); query.setString("dateType", dateType); query.setDate("jobDate",jobDate); return query.list(); } });}Hibernate生成的SQL语句如下select job.TXN_NO as TXN1_5_0_, job.TXN_TYPE as TXN2_5_0_, job.TXN_DATE as TXN3_5_0_, from Newsletter_Message job where to_char(?, 'yyyy-mm-dd hh24:mi') = to_char(?, 'yyyy-mm-dd hh24:mi')每次执行时,总是抛异常,说:org.springframework.dao.DataIntegrityViolationException: Hibernate operation: could not execute query; .....Caused by: java.sql.SQLException: ORA-01722: invalid number我实在不明白为什么会报这个错误,难道Hibernate在set date或者set string之前不会对其进行格式的转换吗?于是我又换了另外一个HQL语句,在代码里面提前将Date格式成String,但还是报错:where to_char(dateType,'yyyy-mm-dd hh24:mi') = :strDate 后来实在没办法了,用了一阴招,代码如下,测试通过,但是觉得这种代码太丑陋了。public List<Job> getJobByDate(final Date jobDate, final String dateType) { return getHibernateTemplate().executeFind(new HibernateCallback() { public Object doInHibernate(Session session)throws HibernateException { String strDate = DateUtil.convertDateToString(jobDate, "yyyy-MM-dd HH:mm"); Query query = session.createQuery("from Job job where "+ "to_char(" + dateType + ",'yyyy-mm-dd hh24:mi') = '"+ strDate + "'"); return (List<Job>) query.list();} });}不知道各位能否解析一下上面那个报错的原因,谢谢先了!
解决方案
把 query.setDate("jobDate",jobDate); 改成 query.setTimestamp("jobDate",jobDate); 试试
解决方案二:
Date转化成字符串后是Fri May 23 23:21:28 CST 2008 之类的东西,而你的HQL语句是'yyyy-mm-dd hh24:mi'格式的,当然要抛异常啊建议格式化一下date类型的数据,如Date date=new Date();SimpleDateFormat format=new SimpleDateFormat();format.applyPattern("yyyy-MM-dd HH:MM");String temp=format.format(date);