CODEWITHSUNDEEP
javadatetimejpa
Alexandre Alves
Alexandre Alves

Reputation: 421

JPA, custom query and dates

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)

Upvotes: 4

Views: 85165

Answers (4)

Richard Sitze
Richard Sitze

Reputation: 8463

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.

Upvotes: 6

Rahul
Rahul

Reputation: 45080

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);

Upvotes: 0

Sathesh S
Sathesh S

Reputation: 1323

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);

Upvotes: 2

ktj
ktj

Reputation: 21

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

Upvotes: 1

Related Questions