Win a copy of Head First Android this week in the Android forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Tim Cooke
  • Campbell Ritchie
  • Paul Clapham
  • Ron McLeod
  • Liutauras Vilda
Sheriffs:
  • Jeanne Boyarsky
  • Rob Spoor
  • Bear Bibeault
Saloon Keepers:
  • Jesse Silverman
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Piet Souris
  • Al Hobbs
  • salvin francis

Why oracle keep complaining "Invalid number" when using hibernate

 
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Why are you treating your date type as a String?
 
pengpeng lin
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 !
 
You showed up just in time for the waffles! And this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic