Hibernate标准——在select语句中排除group by子句

Question

我在sql server中写过:

select max(processed_trans_id) from EQUITY_TRANSACTION where transaction_date <= '2016-06-22' and company_id=75 group by comp_acct_id

但是当我尝试在Hibernate Criteria中编写相同的内容时，像这样:

DetachedCriteria dCriteria = DetachedCriteria.forClass(EquityTransaction.class)
            .add(Restrictions.eq("clientCompany.id", sb.getClientCompany().getId()))
            .add(Restrictions.le("transactionDate", sb.getQualifyDate()))
            .setProjection(Projections.projectionList()
                    .add(Projections.max("processedTransaction.id"), "processedTransaction.id")
                    .add(Projections.groupProperty("holderCompanyAccount.id")));

我从hibernate得到这个sql查询:

select max(processed_trans_id), comp_acct_id from EQUITY_TRANSACTION where transaction_date <= '2016-06-22' and company_id=75 group by comp_acct_id

如何在Hibernate条件中复制原始SQL查询?

Answer 1

我可以使用下面的查询解决这个问题。效果很好!

DetachedCriteria dCriteriaEt1 = DetachedCriteria.forClass(EquityTransaction.class, "et1")
                    .add(Restrictions.eq("clientCompany.id", decldDvd.getClientCompany().getId()))
                    .add(Restrictions.le("transactionDate", decldDvd.getQualifyDate()))
                    .setProjection(Projections.projectionList()
                            .add(Projections.max("processedTransaction.id"), "processedTransaction.id"))
                    .add(Restrictions.eqProperty("et1.holderCompanyAccount.id", "et2.holderCompanyAccount.id"));
            logger.info("Retrieving all qualified company account balances...");
            Criteria getQualifiedBalances = dao.getSession().createCriteria(EquityTransaction.class, "et2")
                    .add(Subqueries.propertyIn("processedTransaction.id", dCriteriaEt1))
                    .add(Restrictions.gt("balance", 0l))
                    .setProjection(Projections.projectionList()
                            .add(Projections.property("holderCompanyAccount.id"),"holderCompanyAccount.id")
                            .add(Projections.property("balance"), "balance"))
                    .addOrder(Order.asc("holderCompanyAccount.id"))
                    .addOrder(Order.desc("processedTransaction.id"))
                    .setResultTransformer(new AliasToBeanNestedResultTransformer(EquityTransaction.class));