如何用SQL及Spring Data Criteria实现多条件关联查询过滤

Alright, let's tackle this problem step by step. First, I'll fix and extend your SQL to handle multi-condition filtering, then show you how to implement this cleanly with Spring Data Criteria.

Step 1: Fixing & Extending the SQL #

First, let's address a small issue in your original single-condition SQL: you need to specify the column(s) to group by (like user.id) to avoid syntax errors. Now, for combining city and role filters, we have two reliable approaches:

Approach 1: Grouped Query with HAVING Clauses #

This approach joins all necessary tables, filters relevant records, then uses HAVING to enforce both conditions:

SELECT u.*
FROM user u
INNER JOIN user_city uc ON u.id = uc.user_id
INNER JOIN city c ON c.id = uc.city_id
INNER JOIN user_role ur ON u.id = ur.user_id
INNER JOIN role r ON r.id = ur.role_id
WHERE c.name IN ('LA', 'Berlin') OR r.name = 'Admin'
GROUP BY u.id
HAVING 
  -- Ensure the user is linked to ALL specified cities
  COUNT(DISTINCT c.name) = 2
  -- Ensure the user has the Admin role (at least once)
  AND SUM(CASE WHEN r.name = 'Admin' THEN 1 ELSE 0 END) >= 1;

Notes:

• Using INNER JOIN instead of LEFT JOIN here since we only care about users with matching cities/roles.
• COUNT(DISTINCT c.name) prevents duplicate city entries from skewing the count.
• The SUM(CASE...) checks for the presence of the Admin role without being affected by other roles.

Approach 2: Subquery Intersection (Better Performance) #

This method splits the conditions into separate subqueries, then returns users that match both sets—this avoids Cartesian product issues from joining multiple many-to-many tables:

SELECT u.*
FROM user u
WHERE 
  -- Users linked to both LA and Berlin
  u.id IN (
    SELECT uc.user_id
    FROM user_city uc
    JOIN city c ON uc.city_id = c.id
    WHERE c.name IN ('LA', 'Berlin')
    GROUP BY uc.user_id
    HAVING COUNT(DISTINCT c.name) = 2
  )
  -- Users with the Admin role
  AND u.id IN (
    SELECT ur.user_id
    FROM user_role ur
    JOIN role r ON ur.role_id = r.id
    WHERE r.name = 'Admin'
  );

Step 2: Implementing with Spring Data Criteria #

Assuming your entities use @ManyToMany for the associations (like User having Set<City> and Set<Role>), here's how to build the specification:

1. Define the Specification Class #

import jakarta.persistence.criteria.*;
import org.springframework.data.jpa.domain.Specification;
import java.util.Set;

public class UserSpecifications {

    public static Specification<User> withCitiesAndRoles(Set<String> cityNames, Set<String> roleNames) {
        return (root, query, cb) -> {
            // Subquery for city condition: users linked to all specified cities
            Subquery<Long> citySubquery = query.subquery(Long.class);
            Root<User> cityRoot = citySubquery.from(User.class);
            Join<User, City> cityJoin = cityRoot.join("cities", JoinType.INNER);
            
            citySubquery.select(cityRoot.get("id"))
                    .where(cityJoin.get("name").in(cityNames))
                    .groupBy(cityRoot.get("id"))
                    .having(cb.equal(cb.countDistinct(cityJoin.get("name")), cityNames.size()));

            // Subquery for role condition: users linked to all specified roles
            Subquery<Long> roleSubquery = query.subquery(Long.class);
            Root<User> roleRoot = roleSubquery.from(User.class);
            Join<User, Role> roleJoin = roleRoot.join("roles", JoinType.INNER);
            
            roleSubquery.select(roleRoot.get("id"))
                    .where(roleJoin.get("name").in(roleNames))
                    .groupBy(roleRoot.get("id"))
                    .having(cb.equal(cb.countDistinct(roleJoin.get("name")), roleNames.size()));

            // Combine both conditions with AND
            return cb.and(
                    root.get("id").in(citySubquery),
                    root.get("id").in(roleSubquery)
            );
        };
    }
}

2. Update the Repository #

Make sure your repo extends JpaSpecificationExecutor to support specifications:

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;

public interface UserRepository extends JpaRepository<User, Long>, JpaSpecificationExecutor<User> {
}

3. Use the Specification in Service #

Parse your request parameters (split cities=LA,Berlin into a Set<String>) and call the repo:

import org.springframework.stereotype.Service;
import java.util.List;
import java.util.Set;

@Service
public class UserService {

    private final UserRepository userRepository;

    public UserService(UserRepository userRepository) {
        this.userRepository = userRepository;
    }

    public List<User> getFilteredUsers(Set<String> cityNames, Set<String> roleNames) {
        Specification<User> filterSpec = UserSpecifications.withCitiesAndRoles(cityNames, roleNames);
        return userRepository.findAll(filterSpec);
    }
}

Key Takeaways #

• The subquery approach is generally more performant for complex many-to-many filters.
• Using COUNT(DISTINCT) ensures we don't count duplicate associations (e.g., a user linked to LA twice).
• Spring Data Specifications make it easy to reuse and combine filter conditions as your requirements grow.

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