在HQL查询的“where”子句中使用日期

I am using the SQLite database and have the following persistent class (simplified):

我正在使用SQLite数据库并具有以下持久化类(简化):

public class Project
{
    public virtual int Id { get; set; }
    public virtual DateTime StartDate { get; set; }
}

which is mapped to this table in the database:

在数据库中映射到此表:

CREATE TABLE projects (
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 
    start_date DATETIME
)

Now I need to write a query that will select all the projects that have started in a given month.

现在我需要编写一个查询,它将选择在给定月份内已启动的所有项目。

In SQL I could use:

在SQL中我可以使用:

SELECT id FROM projects WHERE strftime('%m', start_date) = '12'

What I dislike about this query is that it uses the database specific function "strftime".

我不喜欢这个查询是它使用数据库特定的函数“strftime”。

So the following HQL is dependent on the underlying database:

所以以下HQL依赖于底层数据库:

// Get all projects that started in December (no matter which year)
var projects = session
    .CreateQuery(
        "from Project p " +
        "where strftime('%m', p.StartDate) = :month")
    .SetParameter("month", "12")
    .List<Project>();

I have also tried "from Project p where p.StartDate.Month = 12" but it didn't work.

我也试过“从项目p,其中p.StartDate.Month = 12”但它没有用。

So using HQL or criteria API is it possible to write such a query in a database agnostic way?

那么使用HQL或标准API是否可以以数据库无关的方式编写这样的查询?

3 个解决方案

#1

If you're regularly querying against months, days, years, you shouldn't really be storing your date as a DateTime column - it makes the queries incredibly inefficient. You could easily create a "Month" column and query against that (and your DBA would love you again)

如果您经常查询数月,日,年,那么您不应该将日期存储为DateTime列 - 它会使查询效率极低。你可以轻松地创建一个“月”列并对其进行查询(而你的DBA会再次爱你)

#2

I would highly recommend you to write a custom function.

我强烈建议你写一个自定义函数。

http://ayende.com/Blog/archive/2006/10/01/UsingSQLFunctionsInNHibernate.aspx

I don't mean the sql function, I mean the NHibernate function that is registered with RegisterFunction to enhance the NHibernate dialect.

我不是指sql函数,我的意思是用RegisterFunction注册的NHibernate函数来增强NHibernate方言。

Another probably better example: http://ayende.com/Blog/archive/2007/04/27/Paged-data--Count-with-NHibernate-The-really-easy-way.aspx

另一个可能更好的例子:http://ayende.com/Blog/archive/2007/04/27/Paged-data--Count-with-NHibernate-The-really-easy-way.aspx

#3

-1

from Project p where p.StartDate.ToString("M");

来自Project p,其中p.StartDate.ToString(“M”);

#1

#2