使用Hibernate Criteria API进行日期比较 [英] Date comparison using Hibernate Criteria API

问题描述

环境:休眠4Tomcat 6

Environment : Hibernate 4 Tomcat 6

问题:

我在MySQL数据库中有一个DATETIME类型的列(abhishekDate是列名).
我想将用户提供的日期与此DATETIME列进行比较.

I have a column in MySQL database with DATETIME type (abhishekDate is the column name).
I want to compare the user supplied date with this DATETIME column.

但是比较不应包含 DATETIME列的时间部分.比较应仅基于 DATE部分.

But the comparison should NOT INCLUDE the time part of the DATETIME column. The comparison should be based on DATE part only.

由于查询是动态的，因此我使用的是Criteria API.但是我无法使用Criteria API达到相同的目的.我试过下面的代码，但它不起作用.

Since the query is dynamic , I am using Criteria API. But I am not able to achieve the same using Criteria API. I have tried below code but its not working.

可以帮您编写此查询吗?

Can you please help on how to write this query ?

我对相同的方法使用正确的方法吗?

Am I using correct approach for the same ?

我得到的例外是:

Mar 08, 2016 9:26:59 PM org.apache.catalina.core.StandardWrapperValve invoke
SEVERE: Servlet.service() for servlet mvc-dispatcher threw exception
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'abhishekDate' in 'where clause'
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
    at com.mysql.jdbc.Util.getInstance(Util.java:381)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3558)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3490)

但是User类(或表)中存在abhishekDate

But abhishekDate is present in User class (or table)

代码:

Criteria criteria = getSession().createCriteria(User.class);
Conjunction and = Restrictions.conjunction();

and.add(Restrictions.sqlRestriction("year(abhishekDate) = ? ",abhishekDate,org.hibernate.type.StandardBasicTypes.DATE));
and.add(Restrictions.sqlRestriction("month(abhishekDate) = ? ",abhishekDate,org.hibernate.type.StandardBasicTypes.DATE));
and.add(Restrictions.sqlRestriction("day(abhishekDate) = ? ",abhishekDate,org.hibernate.type.StandardBasicTypes.DATE));

推荐答案

我尝试了另一种方法，它对我有用.我检查表中的日期是否在用户日期和用户日期之间，再加上一天，如下所示.

I tried another way and it is working for me . I check the date in my table is between the user date and user date plus one day as follow.

Calendar cal = Calendar.getInstance();
cal.setTime(userDate);
cr.add(Restrictions.ge("yourDate", cal.getTime()));
cal.add(Calendar.DATE, 1);
cr.add(Restrictions.le("yourDate", cal.getTime()));

这篇关于使用Hibernate Criteria API进行日期比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！