• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Search between two dates columns using jpa

 
Jrcastro Ribeiro
Greenhorn
Posts: 29
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Guys!

I have two colunms: startDate and endDate, and i want to return all the objects between the startDate and the endDate

Using sql I can do the search, but with jpa I'm not getting due to syntax.

Thats my sql (working)




JPQL (not working)




And thats the method im using:


How can i do the same search using JPA?
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jrcastro Ribeiro wrote:Thats my sql (working)


On which database is that statement valid SQL?

I would expect to see something likeAnd a similar condition if you want to check the endDate as well
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jrcastro Ribeiro wrote:And thats the method im using:

You should of course use the correct syntax in your JPQL query. If you are using the BETWEEN operator, it should be something likeAnd you should set both parameters as well using setParameter. And you should of course use the appropriate parameter names as well! In your query you are using :date so you should query.setParameter("date", date); (and not using "names" as the first parameter)
 
Jrcastro Ribeiro
Greenhorn
Posts: 29
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Roel De Nijs wrote:
Jrcastro Ribeiro wrote:Thats my sql (working)


On which database is that statement valid SQL?

I would expect to see something likeAnd a similar condition if you want to check the endDate as well



Using Mysql

But I cannot relate these two columns to show the objects that begin on startDate and ends at endDate ?

My idea was to have two text fields, one that would receive the end date and another with the start date, passing as parameter values, and then returning a list of objects with the relatives dates
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jrcastro Ribeiro wrote:Using Mysql

Very hard to believe! The appropriate syntax of the BETWEEN operator is expr BETWEEN min AND max.

Jrcastro Ribeiro wrote:But I cannot relate these two columns to show the objects that begin on startDate and ends at endDate ?

Sure you can! But not with the BETWEEN operator. Just use startDate >= date1 AND endDate <= date2
 
Jrcastro Ribeiro
Greenhorn
Posts: 29
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Roel De Nijs wrote:
Jrcastro Ribeiro wrote:Using Mysql

Very hard to believe! The appropriate syntax of the BETWEEN operator is expr BETWEEN min AND max.

Jrcastro Ribeiro wrote:But I cannot relate these two columns to show the objects that begin on startDate and ends at endDate ?

Sure you can! But not with the BETWEEN operator. Just use startDate >= date1 AND endDate <= date2



Here the SS, seem to be working

Something like that?



And date 1 and date 2 are the parameter




http://i.imgur.com/jdXPBcX.png
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jrcastro Ribeiro wrote:Here the SS, seem to be working

Maybe you should verify your results a little bit better and with greater eye for detail before claiming it is working!

Jrcastro Ribeiro wrote:Something like that?



And date 1 and date 2 are the parameter

Yes! And depending if the dates should be included you use >= (included) or > (not included). But don't use string concatenation! Use parameter placeholders insteadAnd then use
 
Jrcastro Ribeiro
Greenhorn
Posts: 29
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Back with some problems =/



It's saying that i cannot use the operator with this type of variable, but why?

im using jDatachosser, And that's how I get the dates








 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jrcastro Ribeiro wrote:It's saying that i cannot use the operator with this type of variable, but why?

Because date1 and date2 are strings! Should be java.util.Date of course (like in your entity mapping).

So your method signature should be
 
Jrcastro Ribeiro
Greenhorn
Posts: 29
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Roel De Nijs wrote:
Jrcastro Ribeiro wrote:It's saying that i cannot use the operator with this type of variable, but why?

Because date1 and date2 are strings! Should be java.util.Date of course (like in your entity mapping).

So your method signature should be


Thank you, and sorry for taking your time
Yes, was forgetting the parse:









But same problem =/ , really don't know what is wrong.

As you can see at the ScreenShot, the database has the dates in the same format that I am sending



http://i.imgur.com/CNKODdt.png
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jrcastro Ribeiro wrote:But same problem =/ , really don't know what is wrong.

Very weird! Should normally work without any problem. If you google for "jpql date less than", you'll see this solution is used each time.

Just to be sure: can you try qualifying the properties in your JPQL query and add space before comparison operators? So using u.startDate >= :date1 and u.endDate <= :date2.

If it doesn't work, please share the stack trace as well.
 
Jrcastro Ribeiro
Greenhorn
Posts: 29
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Roel De Nijs wrote:
Jrcastro Ribeiro wrote:But same problem =/ , really don't know what is wrong.

Very weird! Should normally work without any problem. If you google for "jpql date less than", you'll see this solution is used each time.

Just to be sure: can you try qualifying the properties in your JPQL query and add space before comparison operators? So using u.startDate >= :date1 and u.endDate <= :date2.

If it doesn't work, please share the stack trace as well.



i dont know why I never see those little mistakes hehe, I was forgetting to put u.starDate, Now its working like a charm on the jtable.
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jrcastro Ribeiro wrote:Now its working like a charm on the jtable.

Glad to hear it's working now!
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic