获取无重复name+firstName的Doctor记录的Hibernate查询语句

To solve this problem—fetching doctor records with unique (name, firstName) pairs (returning any one record per duplicate group)—you can use a subquery-based HQL approach that’s portable across most databases. Here’s how to implement it:

1. HQL Query (Recommended for Portability) #

Assuming your entity class is named Employee (adjust to match your actual entity name), the query would look like this:

SELECT e FROM Employee e
WHERE e.id IN (
    SELECT MIN(sub.id) 
    FROM Employee sub 
    WHERE sub.job = 'doctor'
    GROUP BY sub.name, sub.firstName
)

How This Works: #

• The inner subquery filters all records where job = 'doctor', then groups them by name and firstName. For each group, it selects the smallest id (you could also use MAX(sub.id) if you prefer the latest record instead—either works as long as it’s consistent).
• The outer query retrieves the full Employee entities corresponding to those selected ids, ensuring you get exactly one record per unique (name, firstName) pair for doctors.

This matches your expected result: it excludes duplicate (name, firstName) groups (like the Konan toto group where id 3 is skipped) and filters out non-doctor records entirely.

2. Criteria API Alternative #

If you prefer using Hibernate’s Criteria API (useful for dynamic queries), here’s how to build the same logic:

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Employee> query = cb.createQuery(Employee.class);
Root<Employee> root = query.from(Employee.class);

// Subquery to get min id per (name, firstName) group for doctors
Subquery<Long> subquery = query.subquery(Long.class);
Root<Employee> subRoot = subquery.from(Employee.class);
subquery.select(cb.min(subRoot.get("id")))
        .where(cb.equal(subRoot.get("job"), "doctor"))
        .groupBy(subRoot.get("name"), subRoot.get("firstName"));

// Outer query to select entities with ids in the subquery result
query.select(root)
     .where(root.get("id").in(subquery));

List<Employee> result = entityManager.createQuery(query).getResultList();

Notes: #

• If you don’t need the full entity and just want specific fields, you can modify the outer query to select individual columns instead of e.
• Avoid using database-specific features like PostgreSQL’s DISTINCT ON unless you’re certain your application will only run on that database—subqueries are more portable.

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