java Hibernate使用date where子句过滤单列数据

Question

假设一个表ABC有两列，即ID&；创建日期

我想获取那些被创建的ID，比如说'09-11-2017'和'09-17-2017'

下面的SQL查询工作正常，但我想使用hibernate实现相同的逻辑

select ID from ABC where between TO_DATE('09-11-2017', 'MM-DD-YYYY')  AND TO_DATE('09-17-2017', 'MM-DD-YYYY')

我的代码不起作用

public List getData(final Date startDate, final Date endDate){

    String sqlString = "select ID from ABC where CREATED_DATE between :startDate and :endDate";
    SQLQuery query = getSession().createSQLQuery(sqlString);
    query.setParameter(CREATED_DATE);
    return query.list();
}

Answer 1

它不起作用，因为您没有设置正确的参数：

String sqlString = "select ID from ABC where CREATED_DATE between :startDate and :endDate";
SQLQuery query = getSession().createSQLQuery(sqlString);
query.setParameter("startDate ", start_date);
query.setParameter("endDate", end_date);
return query.list();

Answer 2

您缺少结束日期参数：

query.setParameter(CREATED_DATE, startDate);
query.setParameter(END_DATE, endDate);

Answer 3

参数必须与查询上的字符串匹配

试试这个：

public List getData(final Date startDate, final Date endDate){

    String sqlString = "select ID from ABC where CREATED_DATE between :startDate and :endDate";
    SQLQuery query = getSession().createSQLQuery(sqlString);
    query.setParameter("startDate",startDate);
    query.setParameter("endDate",endDate);
    return query.list();
}

pscar13