java.sql.Date Required in Native Query

andrew.trujillo · **Joined:** Thu Nov 12, 2009 1:03 pm **Posts:** 1

I am doing a native SQL query in JPA, but I get an error if I try to set a java.util.Date as a parameter.

Code:

[JDBCExceptionReporter] The value of input host variable or parameter number "1" cannot be used because of its data type.

The following code demonstrates the problem:

Code:

@PersistenceContext
HibernateEntityManager em;

String sql="SELECT * FROM TABLE1 WHERE DATE_CHANGED >= :changeDate";

public List getRecordsChanged() {
      Date date = DateUtils.addMonths(new Date(), -1);
      return em.createNativeQuery(sql, Table1.class)
         .setParameter("changeDate", date)
         .getResultList();
}

It works if I change it to:

Code:

@PersistenceContext
HibernateEntityManager em;

String sql="SELECT * FROM TABLE1 WHERE DATE_CHANGED >= :changeDate";

public List getRecordsChanged() {
      Date date = DateUtils.addMonths(new Date(), -1);
      return em.createNativeQuery(sql, Table1.class)
         .setParameter("changeDate", new java.sql.Date(date.getTime()))
         .getResultList();
}

Is there a property that I can set so that Hibernate will do this conversion automatically?

EDIT: Nevermind. Should have been:

Code:

@PersistenceContext
HibernateEntityManager em;

String sql="SELECT * FROM TABLE1 WHERE DATE_CHANGED >= :changeDate";

public List getRecordsChanged() {
      Date date = DateUtils.addMonths(new Date(), -1);
      return em.createNativeQuery(sql, Table1.class)
         .setParameter("changeDate", date, TemporalType.DATE)
         .getResultList();
}

venodrg · **Joined:** Fri Mar 19, 2010 2:37 pm **Posts:** 5

Hi andrew,
To solve this issue you have to set actual type parameter.

If you want to Compare only Date (e.g. "YYYY-MM-DD" ) then use > query.setDate("parameterName", datevalue);
If you want to compare Time only (e.g. hh:mm24:ss ) then use > query.setTime("parameterName", datevalue);

If you wanted to compare Date and Time (e.g. "YYYY-MM-DD hh:mm24:ss" ) then use > query.setTimestamp("parameterName", datevalue);

It is better not to use new java.sql.Date(date.getTime()))

But using TemporalType is also the right way too.

Regards,
Venod