Hi all,
I'm new to Hibernate and came up with a problem when using HQL.
The problem is I want to search records from Oracle database according a column called 'txn_date', which its type is 'Date', and my HQL like this
=========================================================================
<sql-query name="job.getJobByDate">
select {job.*} from Newsletter_Message job
where to_char(

ateType,'yyyy-mm-dd hh24:mi') =
to_char(:jobDate, 'yyyy-mm-dd hh24:mi')
<return alias="job"
class="com.newsletter.domain.job.Job" />
</sql-query>
=========================================================================
and the method is :
=========================================================================
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 will generate the SQL like this:
=========================================================================
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')
=========================================================================
When execute, it keep complainning that
"org.springframework.dao.DataIntegrityViolationException: Hibernate operation: could not execute query"....
Caused by: java.sql.SQLException: ORA-01722: invalid number
I'm so confused that why oracle keep complainning "Invalid number"? I changed the sql to another format:
where to_char(

ateType,'yyyy-mm-dd hh24:mi') = :strDate
and set string to variable strDate but also got same exception.
Can anybody help me? Thanks a lot !!!