Hibernate中用Criteria实现子查询、分组与Having计数查询

Got it, let's walk through how to convert your SQL query into Hibernate Criteria code. First, let's recap what your query does: it finds all person_id values that are linked to multiple unique user_id entries in the zrm_actor table, skipping deleted records and any rows where person_id is null.

I'll show you two ways to do this: a simplified approach that's logically equivalent (and more efficient) and a direct translation that mirrors your original subquery structure, in case you need to stick close to the original SQL.

Simplified, Efficient Version #

This cuts out the explicit subquery by using countDistinct directly on userId while grouping by personId—it gets the exact same result as your original query but with fewer layers:

// Assuming your entity class is ZrmActor, with fields matching the table columns (personId, userId, deleted)
Criteria criteria = session.createCriteria(ZrmActor.class, "ac");

// Filter out records marked as deleted
criteria.add(Restrictions.ne("deleted", 1));
// Skip any rows where person_id is null
criteria.add(Restrictions.isNotNull("personId"));

// Set up projections to group by personId and count unique userIds
ProjectionList projectionList = Projections.projectionList()
    .add(Projections.groupProperty("personId"), "person_id")
    .add(Projections.countDistinct("userId"), "user_count");

criteria.setProjection(projectionList);

// Add the HAVING clause: only keep groups where there's more than one unique userId
criteria.add(Restrictions.gt("user_count", 1));

// Run the query and get your list of personIds
List<Long> duplicatePersonIds = criteria.list();

Direct Subquery Mirror #

If you want to strictly follow the structure of your original SQL (with the subquery), use a DetachedCriteria for the subquery part, then build the main query on top of it:

// First, create the subquery to get distinct person_id + user_id pairs from non-deleted records
DetachedCriteria subQuery = DetachedCriteria.forClass(ZrmActor.class, "ac")
    .add(Restrictions.ne("deleted", 1))
    .setProjection(Projections.distinct(
        Projections.projectionList()
            .add(Projections.property("personId"), "person_id")
            .add(Projections.property("userId"), "user_id")
    ));

// Now build the main query using the subquery as the data source
Criteria mainCriteria = session.createCriteria(subQuery, "ap");

// Exclude any null person_id values
mainCriteria.add(Restrictions.isNotNull("person_id"));

// Group by person_id and count how many entries each has
mainCriteria.setProjection(Projections.projectionList()
    .add(Projections.groupProperty("person_id"), "person_id")
    .add(Projections.rowCount(), "record_count")
);

// Apply the HAVING condition to keep only groups with more than one entry
mainCriteria.add(Restrictions.gt("record_count", 1));

// Execute and retrieve the results
List<Long> duplicatePersonIds = mainCriteria.list();

Both methods will give you the exact same output. The simplified version is usually better to go with since it avoids an extra subquery layer, which can help with database performance.

内容的提问来源于stack exchange，提问作者sajjad jafari