Spring Data JPA多属性联合查询：关联用户与联系人表

Got it, let's break down how to solve this joined query problem between your User and Contact tables based on multiple input parameters. First, let's fix a couple of small issues in your entity class to ensure proper mapping, then dive into practical query solutions.

First, Fix Minor Issues in Your User Entity #

Looking at your provided code, there are a few typos and missing details that need correcting for the JPA mapping to work as expected:

• usertId should be userId (matches the referencedColumnName in your @JoinColumn)
• The username field is missing its type declaration (should be private String username;)
• The @OneToMany mapping is mostly correct, but you might want to explicitly define fetch behavior if needed (default is LAZY, which is usually preferred)

Here's the corrected snippet:

@Entity
@Table(name = "user")
public class User {
    private String userId; // Fixed typo
    private Collection<Contact> contacts;
    private String userType;
    private String username; // Added type declaration

    @OneToMany
    @JoinColumn(name = "user_id", referencedColumnName = "user_id")
    public Collection<Contact> getContacts() {
        return contacts;
    }

    // Add getters and setters for all fields
}

For context, I’ll assume your Contact entity looks something like this (since it wasn’t provided):

@Entity
@Table(name = "contact")
public class Contact {
    private String contactId;
    private String email;
    private String phoneNumber;
    private String userId; // Foreign key linking to User table

    // Getters and setters
}

Option 1: JPQL Query with Named Parameters #

If your input parameters are mostly predictable (and you want a straightforward solution), a JPQL query with named parameters works great. It handles optional parameters by checking if they’re null before applying filters.

Create a repository interface extending JpaRepository:

@Repository
public interface UserRepository extends JpaRepository<User, String> {

    @Query("SELECT DISTINCT u FROM User u JOIN u.contacts c WHERE " +
           "(:username IS NULL OR u.username = :username) AND " +
           "(:userType IS NULL OR u.userType = :userType) AND " +
           "(:email IS NULL OR c.email = :email) AND " +
           "(:phoneNumber IS NULL OR c.phoneNumber = :phoneNumber)")
    List<User> findUsersWithContactDetails(
        @Param("username") String username,
        @Param("userType") String userType,
        @Param("email") String email,
        @Param("phoneNumber") String phoneNumber
    );
}

• The (:param IS NULL OR ...) pattern lets you ignore parameters that aren’t provided (null values won’t affect the query)
• Use LEFT JOIN instead of JOIN if you want to include users who don’t have associated contact records
• DISTINCT ensures you don’t get duplicate User entries if a user has multiple contacts

Option 2: Criteria API for Dynamic Queries #

If you need more flexibility (e.g., parameters change based on user input), the Criteria API lets you build queries dynamically. This is ideal when you don’t know which parameters will be provided upfront.

First, define a custom repository interface:

public interface UserCustomRepository {
    List<User> findUsersByFilters(String username, String userType, String email, String phoneNumber);
}

Then implement it:

@Repository
public class UserCustomRepositoryImpl implements UserCustomRepository {

    @PersistenceContext
    private EntityManager entityManager;

    @Override
    public List<User> findUsersByFilters(String username, String userType, String email, String phoneNumber) {
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<User> query = cb.createQuery(User.class);
        Root<User> userRoot = query.from(User.class);
        // Use JoinType.LEFT if you want to include users without contacts
        Join<User, Contact> contactJoin = userRoot.join("contacts", JoinType.INNER);

        List<Predicate> predicates = new ArrayList<>();

        // Add filters only if the parameter is non-null/non-empty
        if (username != null && !username.isBlank()) {
            predicates.add(cb.equal(userRoot.get("username"), username));
        }
        if (userType != null && !userType.isBlank()) {
            predicates.add(cb.equal(userRoot.get("userType"), userType));
        }
        if (email != null && !email.isBlank()) {
            predicates.add(cb.equal(contactJoin.get("email"), email));
        }
        if (phoneNumber != null && !phoneNumber.isBlank()) {
            predicates.add(cb.equal(contactJoin.get("phoneNumber"), phoneNumber));
        }

        query.where(cb.and(predicates.toArray(new Predicate[0])));
        return entityManager.createQuery(query).getResultList();
    }
}

Finally, extend your main repository with the custom interface:

public interface UserRepository extends JpaRepository<User, String>, UserCustomRepository {
}

Quick Tips #

• Avoid N+1 Queries: If you want to load the contacts collection along with users (instead of fetching them later), use JOIN FETCH in your JPQL query:

  SELECT DISTINCT u FROM User u JOIN FETCH u.contacts c WHERE ...
  

• Case-Insensitive Searches: To make filters like username or email case-insensitive, use the LOWER() function:

  (:username IS NULL OR LOWER(u.username) = LOWER(:username))
  

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