Why oracle keep complaining "Invalid number" when using hibernate

[pengpeng lin]

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 !!!

[pengpeng lin]

I tried another SQL with and it goes well, the code is :
=========================================================================
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();
}
});
}
=========================================================================

and Hibernate will generate SQL like this:
=========================================================================
select job0_.TXN_NO as TXN1_5_,
job0_.TXN_TYPE as TXN2_5_,
job0_.TXN_DATE as TXN3_5_,
from NEWSLETTER_MESSAGE job0_
where to_char(job0_.TXN_DATE, 'yyyy-mm-dd hh24:mi') = '2008-05-22 16:11'
=========================================================================

but the code seems such ugly , does anyone have other methods to avoid hardcode and database-binding ?

Thanks

[Paul Sturrock]

Why are you treating your date type as a String?

[pengpeng lin]

Because it is a column name, such as

to_date(txn_date, 'yyyy-mm-dd hh24:mi:ss') = another_string_date

so I think I should set it as String, am I right?

If it should be treated as Date format, how should I call the method? since the argument 'dateType' is string ?

Thank you very much for your quick response !