Win a copy of Kotlin in Action this week in the Kotlin forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

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

Cameron Wallace McKenzie
author and cow tipper
Saloon Keeper
Posts: 4968
Hibernate Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • 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.


-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 ]
Mark Spritzler
Posts: 17309
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • 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.

  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!