JPA, custom query and dates
datetime, java, jpa
Solution
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`.
Problem
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)