Hibernate/JPA实现Position实体多字段去重计数的技术问题

I get it—translating that straightforward SQL into JPA Criteria can be tricky when you're dealing with distinct multi-field combinations. Let's fix that error and get your count working properly.

Why Your Original Code Failed #

The error happens because criteriaBuilder.countDistinct() expects an Expression (like a single field or combined expression), not an entire CriteriaQuery<Tuple>. To replicate your native SQL logic, we need to nest a distinct subquery inside a main query that counts its results.

Correct Criteria API Implementation #

Here's the working version that matches your original SQL exactly, while keeping your database-agnostic goal intact:

public long getCountDistinctInFlightPositions() {
    try (Session session = sessionFactory.openSession()) {
        CriteriaBuilder cb = session.getCriteriaBuilder();
        
        // Main query: count the number of rows from the subquery
        CriteriaQuery<Long> mainQuery = cb.createQuery(Long.class);
        
        // Subquery: get distinct (longitude, latitude, updateTime) tuples
        Subquery<Tuple> subQuery = mainQuery.subquery(Tuple.class);
        Root<Position> subRoot = subQuery.from(Position.class);
        
        subQuery.select(cb.tuple(
            subRoot.get("longitude"),
            subRoot.get("latitude"),
            subRoot.get("updateTime")
        )).distinct(true); // This adds the DISTINCT clause to the subquery
        
        // Count the results of the subquery
        mainQuery.select(cb.count(subQuery));
        
        return session.createQuery(mainQuery).getSingleResult();
    }
}

Breakdown of the Solution: #

1. Try-with-resources: Ensures the Session is closed automatically, avoiding resource leaks.
2. Subquery Setup: We define a subquery that fetches only the three fields we care about, marked with distinct(true) to get unique combinations.
3. Main Query: The main query simply counts the number of rows returned by the subquery—this is exactly what your native SQL's outer COUNT(*) does.

Alternative: Simplify with JPQL #

If you find Criteria API too verbose, a JPQL query works just as well (and is easier to read for this use case). It will still be translated correctly for H2 and MariaDB:

public long getCountDistinctInFlightPositions() {
    try (Session session = sessionFactory.openSession()) {
        String jpql = """
            SELECT COUNT(t) 
            FROM (SELECT DISTINCT p.longitude, p.latitude, p.updateTime 
                  FROM Position p) t
        """;
        return session.createQuery(jpql, Long.class).getSingleResult();
    }
}

This JPQL is a direct mirror of your native SQL, so it behaves exactly the same way across supported databases.

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