3

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)

Alexandre Alves
  • 411
  • 2
  • 10
  • 20

4 Answers4

6

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.

Richard Sitze
  • 8,262
  • 3
  • 36
  • 48
  • thanks I updated the question, after validating not problem. I should also mention that when a record is created in the database, the startdate is automatically set by the application with a object of type date. and it saves correctly without error. – Alexandre Alves Mar 18 '13 at 04:49
  • You're really leaving everyone guessing. Please show: a) SQL used to create the table, and b) relevant portions of the Java Entity. – Richard Sitze Mar 18 '13 at 05:29
  • 1
    One piece still missing in your code - the variables & code that setup the query. How are those variables declared? They must be java Date or Calendar, right? – Richard Sitze Mar 18 '13 at 15:16
  • Accepting mostly because the above comment actually help me find my problem and resolution. Thanks – Alexandre Alves Mar 25 '13 at 03:12
1

I think that there should be a space between :(colon) and startDate in between. May be it is considering :startDate as a single word. Try this once

ktj
  • 21
  • 1
  • 5
1

Try 

String query = "SELECT t FROM Tickets t  WHERE t.startdate > ?1 AND t.enddate < ?2 ORDER BY t.status DESC";

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

Sathesh S
  • 1,253
  • 3
  • 23
  • 54
0
Query q = em.createQuery(query).setParameter("startDate", startDate, TemporalType.TIMESTAMP).setParameter("endDate", endDate, TemporalType.DATE);

If you carefully look at the setParameter you're using, it says that this setParameter requires a Positional Parameter, whereas, seeing your query, it seems you've used Named Parameter.

Hence, the IllegalStateException. Either change your query to provide Positional Parameters, or the setParameter to provide Named Parameters as input.

This is how you provide Positional Parameter in the query.

String query = "SELECT t FROM Tickets t  WHERE t.startdate > ?1 AND t.enddate < ?2 ORDER BY t.status DESC";
....
Query q = em.createQuery(query).setParameter(1, startDate, TemporalType.TIMESTAMP).setParameter(2, endDate, TemporalType.DATE);
Rahul
  • 44,383
  • 11
  • 84
  • 103
  • Thanks for the info. I've tried as you mentioned but still same issue. And I only get this when q.getResultList is called. until then the variables are properly filled although in the debug mode I see the jpql query being marker as "startdate > ?" And thanks a lot for the explanation, I would have missed it for a long the named vs positional. – Alexandre Alves Mar 18 '13 at 04:57
  • it says `IllegalArgumentException` now, which means either the local variable `startDate` is not `Date` type. That seems to be the only likely case. – Rahul Mar 18 '13 at 05:14
  • that was to show that the line was actually being executed. I done it for testing when forcing the type to be string. I still get the state exception. – Alexandre Alves Mar 18 '13 at 06:17
  • Hm.. there is also a setParameter method for setting a named parameter, and I think the code in question description uses it: http://docs.oracle.com/javaee/6/api/javax/persistence/Query.html#setParameter(java.lang.String,%20java.util.Calendar,%20javax.persistence.TemporalType) – Zhenya Apr 01 '15 at 08:16