0

I am filtering a search with criteria object. but the filter doesn't work for date. I made this for instance :

criteria.add(Restrictions.and(
            Restrictions.like("serialNumber", device.getSerialNumber()),
            Restrictions.like("installDate", device.getInstallDate()), // a date
            Restrictions.like("ipAdress", device.getIpAdress())));

then i made this :

else if (device.getInstallDate() != null) {
        criteria.add(Restrictions.like("installDate", device.getInstallDate()));
    }

Do you have any idea to filter by date ?

Jérôme Campeaux
  • 49
  • 2
  • 10

2 Answers2

0

Your code/approach looks fine. You may want to enable SQL logging to see what statements exactly are sent to the DB and what values are bound to the statement parameters. This should help you figure out the issue (the issue may be just some detail like e.g. dates with/without time parts, or something similar).

See also:

Hibernate show real SQL

Community
  • 1
  • 1
peter.petrov
  • 38,363
  • 16
  • 94
  • 159
0

To search for exact dates I use:

criteria.add(Restrictions.eq("installDate", device.getInstallDate()));

Note also that dates and timestamps are treated differently by the underlying database based on the corresponding SQL types. A field declared as a date will not include hours/minutes/etc. If the desire is to compare both date and time, be sure to use timestamp in the Hibernate declaration.

The fastest way to show the SQL statements is to set the show_sql property to true in your Hibernate configuration

wbdarby
  • 1,129
  • 12
  • 12
  • Thank you for reply, I will enable it. I tried your solution but same result as "like" – Jérôme Campeaux Jan 29 '15 at 11:36
  • How is the 'installDate' field declared in Hibernate? Can you write a SQL query that gives you the expected results? Have you logged the device.getInstallDate() value to see what is being compared - these must match to the millisecond. Also - remember that the timezone can play a factor. For example, in MySQL each connection has a time_zone. So a query that looks the same on the SQL command line may interpret the specified date differently based on the timezone. – wbdarby Jan 29 '15 at 13:34
  • ok so I have this ine DB 2012-05-10 00:00:00.000. so it try to compare 2012-05-10 with 2012-05-10 00:00:00.000 and obviously doesn't match. The problem is that I want to compare just the beggining of the date – Jérôme Campeaux Jan 29 '15 at 13:42
  • How is the field declared in the database? Are you using Hibernate to generate the schema or do you have a separate schema? The easiest thing to do is create a matching date before comparison - setting hours/minutes/seconds/milliseconds to 0. – wbdarby Jan 29 '15 at 13:48
  • with that i recover the list of devices in database: getSessionFactory().getCurrentSession().createCriteria(Device.class); and the date in my search doesn't match with the date in database – Jérôme Campeaux Jan 29 '15 at 13:53
  • You have two options: 1) Specify the type as 'date' in your Hibernate declaration, this will create a SQL DATE rather than TIMESTAMP. 2) Create a comparison date, something like: Calendar match = Calendar.newInstance(); match.setTime( device.getInstallDate() ); match.set( Calendar.HOUR_OF_DAY, 0 ); match.set( Calendar.MINUTE, 0 ): match.set( Calendar.SECOND, 0 ); match.set( Calendar.MILLISECOND, 0 ); criteria.add(Restrictions.eq("installDate", match.getTime())); – wbdarby Jan 29 '15 at 13:54
  • Specify the type as 'date' in your Hibernate declaration : Sorry i don't really understand this ? can you explain what do you mean ? – Jérôme Campeaux Jan 29 '15 at 14:00
  • If you are using annotations check the 'Person' declaration in this blog: http://blogs.sourceallies.com/2012/02/hibernate-date-vs-timestamp/ – wbdarby Jan 29 '15 at 14:04