• 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 Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Criteria Like Query on java.sql.Data DD-??-YYY or ??-MM-YYYY or DD-MM-????

 
author and cow tipper
Posts: 5009
1
Hibernate Spring Tomcat Server
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I love the Hibernate Criteria API, but was wondering if anyone had a big brain and could help me with a like query on dates? I've seen alot of date examples, but they stick to less than or greater than examples, and skip 'like.'

I was thinking, could you use the criteria API and like to do a query where you know the month and date, but not the year? What about month and year, but not the date? It seems equals, greater than and less than on java.sql.Date are the standard. Is the java.sql.Date object all or nothing when doing a query?

I did find this link very useful, but it deals more with less than and greater than (date ranges) as opposed to like:

Using Hibernate Criteria Tutorial - Date Ranges etc.

Thanks.

-Cameron McKenzie

Hmmmm....Just digging some more. Found these example:


There doesn't appear to be an official way of selecting * from a table where eg 'date is january 2005'. So far i've found 8 different ways!!

1. where date like '2005-01-%'
2. where DATE_FORMAT(date,'%Y-%m')='2005-01'
3. where EXTRACT(YEAR_MONTH FROM date)='200501'
4. where YEAR(date)='2005' and MONTH(date)='1'
5. where substring(date,1,7)='2005-01'
6. where date between '2005-01-01' and '2005-01-31'
7. where date >= '2005-01-01' and date <= '2005-01-31'



Samples for doing Like Date Search
[ November 20, 2007: Message edited by: Cameron McKenzie ]
 
ranger
Posts: 17347
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I think built in to Hiernate/JPA There aren't any Date functions that you can use to get like like functionality. I Like that "like like".

Anyway it then comes down to Database vendor specific functions. For example in Query HQL/JPA-QL if there is a statement that it cannot parse, it passes it through as is to the database. So if I used an Oracle specific function like to_date or to_number, then Hibernate can't parse it and will just pass it to the databse, which if it is Oracle, will understand it and run that function.

Mark
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic