使用数据库函数转换 hibernate 标准中的列

发布于 2024-12-09 22:48:03 字数 520 浏览 0 评论 0原文

尽管用了 3 个小时的谷歌搜索和 API 搜索，我还是找不到任何关于是否可以在 Hibernate 标准查询中使用数据库函数的参考。具体来说：

我想访问 postgres 数据库中日期时间的日期部分并按其分组。我想象查询会类似于：

session.createCriteria(Exam.class)
  .setProjection(Projections.projectionList()
    .add(Projections.property("DATE(beginExam)").as("beginDate"))
    .add(Projections.groupProperty("beginDate")))
  .list();

这不起作用，给我一个“无法解析属性：Date(beginExam)...”异常。看起来这是一件非常简单的事情，我一定错过了一些东西。鉴于我也在动态地构建限制（我在示例中省略了它），似乎标准是用于此目的的休眠组件，但我愿意接受任何建议，但不回避整个问题通过建立我自己的小组。

谢谢

原文

Despite 3 hours of googling and searching the API I can't find any reference to whether it's possible to use database functions within a hibernate criteria query. To be specific:

I'd like to access the date portion of the datetime in a postgres database and group by that. I'd imagine the query would look something like:

session.createCriteria(Exam.class)
  .setProjection(Projections.projectionList()
    .add(Projections.property("DATE(beginExam)").as("beginDate"))
    .add(Projections.groupProperty("beginDate")))
  .list();

This does not work giving me a "could not resolve property: Date(beginExam)..." exception. It seems like this is a very simple thing to do, and I must be missing something. Given that I am also building restrictions dynamically (I've left that out in the example) it seems that criteria is the hibernate component to use for this, but I'm open to any suggestions at this point short of side stepping the whole issue by building my own group by.

Thanks

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

情徒 2024-12-16 22:48:03

您是否尝试过 Projections.sqlProjection 像这样

session.createCriteria(Exam.class)
  .setProjection(Projections.projectionList()
  .add(Projections.sqlProjection("date(beginExam) as beginDate", new String[] { "beginDate" }, new Type[] { StandardBasicTypes.DATE }))
  .add(Projections.groupProperty("beginDate")))
.list();

或投影像这样的 .sqlGroupProjection

session.createCriteria(Exam.class)
  .setProjection(Projections.sqlGroupProjection("date(beginExam) as beginDate", "beginDate", new String[] { "beginDate" }, new Type[] { StandardBasicTypes.DATE }))
.list();

Hibernate 在 SQL 查询中生成表别名，因此您可能需要将 {alias} 片段添加到 SQL 片段中才能使其工作：

session.createCriteria(Exam.class)
  .setProjection(Projections.projectionList()
  .add(Projections.sqlProjection("date({alias}.beginExam) as beginDate", new String[] { "beginDate" }, new Type[] { StandardBasicTypes.DATE }))
  .add(Projections.groupProperty("beginDate")))
.list();

或使用SQLGroupProjection：

session.createCriteria(Exam.class)
  .setProjection(Projections.sqlGroupProjection("date({alias}.beginExam) as beginDate", "beginDate", new String[] { "beginDate" }, new Type[] { StandardBasicTypes.DATE }))
.list();

Have you tried Projections.sqlProjection like this

session.createCriteria(Exam.class)
  .setProjection(Projections.projectionList()
  .add(Projections.sqlProjection("date(beginExam) as beginDate", new String[] { "beginDate" }, new Type[] { StandardBasicTypes.DATE }))
  .add(Projections.groupProperty("beginDate")))
.list();

or Projections.sqlGroupProjection like this

session.createCriteria(Exam.class)
  .setProjection(Projections.sqlGroupProjection("date(beginExam) as beginDate", "beginDate", new String[] { "beginDate" }, new Type[] { StandardBasicTypes.DATE }))
.list();

Hibernate generates table aliases in SQL queries, so you may need to add the {alias} fragment to your SQL fragment to make this work:

session.createCriteria(Exam.class)
  .setProjection(Projections.projectionList()
  .add(Projections.sqlProjection("date({alias}.beginExam) as beginDate", new String[] { "beginDate" }, new Type[] { StandardBasicTypes.DATE }))
  .add(Projections.groupProperty("beginDate")))
.list();

or with SQLGroupProjection:

session.createCriteria(Exam.class)
  .setProjection(Projections.sqlGroupProjection("date({alias}.beginExam) as beginDate", "beginDate", new String[] { "beginDate" }, new Type[] { StandardBasicTypes.DATE }))
.list();

回复收藏 0 原文