JPA, custom query and dates

Question

I'm facing a strange issue. I've search including here in stack overflow and for JPA and Custom query I should specified the parameter. So I have a query string since I have over 14 fields but I'm facing issues with the dates. I'm always getting the IllegalStateException

INFO: query STRING = SELECT t FROM Tickets t  WHERE t.startdate > :startDate AND t.enddate < :endDate ORDER BY t.status DESC
WARNING: #{ticketController.search}: java.lang.IllegalStateException: Query argument startDate not found in the list of parameters provided during query execution.

as for my query:

Query q = em.createQuery(query).setParameter("startDate", startDate, TemporalType.TIMESTAMP).setParameter("endDate", endDate, TemporalType.DATE);

Although I'm getting that the parameter is not found, I have it in the setParameter and also set in the query as seen in the INFO line.

Any ideas?

Thanks in advance

EDIT:

INFO: query STRING = SELECT t FROM Tickets t  WHERE t.startdate > ?1 AND t.enddate < ?2 ORDER BY t.status DESC
WARNING: #{ticketController.search}: java.lang.IllegalStateException: Query argument 1 not found in the list of parameters provided during query execution.

q = em.createQuery(query).setParameter(1, startDate, TemporalType.TIMESTAMP).setParameter(2, endDate, TemporalType.TIMESTAMP);

Also and as advised, I've checked that the Date I'm using is java.util.Date. and in the entity class I have as Timestamp. But still I cannot have this working and not sure where I am failing.

Just to make sure that all the things are as they should, I forced the query to be string and I got the correct Exception:

INFO: query STRING = SELECT t FROM Tickets t  WHERE t.startdate > :startDate AND t.enddate < :endDate ORDER BY t.status DESC
WARNING: #{ticketController.search}: java.lang.IllegalArgumentException: You have attempted to set a value of type class java.lang.String for parameter startDate with expected type of class java.util.Date 

But then again, I change to date and it fails :S I've checked the reasons for this IllegalStateException:

And from the debug and from the javadoc I get the following: getResultList

IllegalStateException - if called for a Java Persistence query language UPDATE or DELETE statement.

I'm not doing a update nor delete :/

EDIT 2: Adding the Entity relevant part:

@Basic(optional = false)
@NotNull
@Column(name = "startdate")
@Temporal(TemporalType.TIMESTAMP)
private Date startdate;
@Column(name = "enddate")
@Temporal(TemporalType.TIMESTAMP)
private Date enddate;

AS for the database creating script the columns are being created like this:

  startdate timestamp with time zone NOT NULL,
  endate timestamp with time zone,

If I do a normal SQL query like: "select * from tbl_tickets where startdate > '2012-02-01 00:00:00' and enddate < '2013-03-18 23:59:50'"

I get the desired results. I guess I could do with native query but that would be going around the problem and not fixing this issue, right?

EDIT 3: Although I had everything set up properly, the init of the bean was calling again the query without the args ( sorry and thank you all for your help. It helped me checking what was amiss)

Answer 1

javadoc for both

setParameter(String name, java.util.Date value, TemporalType temporalType)`
setParameter(String name, java.util.Calendar value, TemporalType temporalType)`

states:

Throws: IllegalArgumentException - if the parameter name does not correspond to a parameter of the query or if the value argument is of incorrect type

Since you didn't provide full code, verify that:

• Java value startDate is of type java.util.Date or java.util.Calendar.

• SQL column startDate has valid SQL date type TIMESTAMP.