Win a copy of Testing JavaScript Applications this week in the HTML Pages with CSS and JavaScript forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Bear Bibeault
  • Ron McLeod
  • Jeanne Boyarsky
  • Paul Clapham
Sheriffs:
  • Tim Cooke
  • Liutauras Vilda
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • fred rosenberger
  • salvin francis
Bartenders:
  • Piet Souris
  • Frits Walraven
  • Carey Brown

Search between two dates columns using jpa

 
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?
 
Sheriff
Posts: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • 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: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • 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: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • 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: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • 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: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • 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: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • 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: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • 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!
 
Liar, liar, pants on fire! refreshing plug:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
    Bookmark Topic Watch Topic
  • New Topic