Hibernate条件查询

发布于 2024-10-11 13:33:34 字数 1114 浏览 1 评论 0原文

我正在尝试使用 Hibernate criteria api 执行子查询，但无法完全弄清楚如何执行它。假设有 2 个表，SHOPS 和 EMPLOYEES，其中 SHOPS 包含所有商店信息，EMPLOYEES 是所有商店中所有员工的大表（未设置外键）。我正在尝试编写一个查询，该查询从 SHOPS 表中检索商店 ID 和地址，然后通过 EMPLOYEES 表中的联接和计数来检索商店中的雇员人数。像这样的事情：

SELECT a.SHOP_ID, a.SHOP_ADDRESS, (SELECT COUNT(*) FROM
SHOP_EMPLOYEES b WHERE b.SHOP_ID = a.SHOP_ID) as NUM_EMPLOYEES FROM   <--Problem here
SHOPS a
WHERE
QUERY_STATUS ='Open'

所以我有一个 Java 类 Shop，其中包含 shopId、shopAdres、numEmployees 和类似的员工。

我的子查询：

DetachedCriteria subquery = DetachedCriteria.forClass(
  Employee.class, "b").add(
  Property.forName("b.shopId").eqProperty("a.shopId"))
  .setProjection(
    Projections.projectionList().add(
      Projections.rowCount()));

以及主要条件查询：

List shopListRet = session.createCriteria(Shop.class, "a")
  .setProjection(
    Projections.projectionList().add(
      Projections.property("a.shopId"))).add(Subquery..."DONT KNOW WHAT SHOULD COME HERE").list();

我的问题是：

如何将分离查询关联为子查询以收集 Shop 类中的类变量 numEmployees 中的计数结果？

谢谢 -J

原文

I am trying to execute a subquery using Hibernate criteria api but not been able to figure out completely how to go about it. Assuming there are 2 tables, SHOPS and EMPLOYEES, where SHOPS has all the shops information and EMPLOYEES is a big table of all the employess in all the shops (No foreign keys set). I am trying to write a query, which retrieves the shop id and address from the SHOPS table and then retrieves the number of employess in a shop by a join and count on EMPLOYEES table. Something like this:

SELECT a.SHOP_ID, a.SHOP_ADDRESS, (SELECT COUNT(*) FROM
SHOP_EMPLOYEES b WHERE b.SHOP_ID = a.SHOP_ID) as NUM_EMPLOYEES FROM   <--Problem here
SHOPS a
WHERE
QUERY_STATUS ='Open'

So I have a Java class Shop with shopId, shopAddres, numEmployees and similar for Employees.

My subquery:

DetachedCriteria subquery = DetachedCriteria.forClass(
  Employee.class, "b").add(
  Property.forName("b.shopId").eqProperty("a.shopId"))
  .setProjection(
    Projections.projectionList().add(
      Projections.rowCount()));

And a main criteria query on the lines of:

List shopListRet = session.createCriteria(Shop.class, "a")
  .setProjection(
    Projections.projectionList().add(
      Projections.property("a.shopId"))).add(Subquery..."DONT KNOW WHAT SHOULD COME HERE").list();

My question is:

How do i associate the Detached query as a subquery to collect the count result in a class variable numEmployees in Shop class?

Thanks
-J

分享到QQ

分享到微博

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

发布评论

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

浅黛梨妆こ 2024-10-18 13:33:34

使用 SQL 投影来添加子查询。

  String subSql = "SELECT COUNT(*) FROM SHOP_EMPLOYEES b WHERE b.SHOP_ID = a.SHOP_ID"
...
    .add( Projections.sqlProjection(
        subSql ,
        new String[] { "NUM_EMPLOYEES" },
        new Type[] { Hibernate.LONG }
    );

Use a SQL projection to add the subquery.

  String subSql = "SELECT COUNT(*) FROM SHOP_EMPLOYEES b WHERE b.SHOP_ID = a.SHOP_ID"
...
    .add( Projections.sqlProjection(
        subSql ,
        new String[] { "NUM_EMPLOYEES" },
        new Type[] { Hibernate.LONG }
    );

回复收藏 0 原文

~没有更多了~