Spring Data非实体列查询：能否用方法名查询person_id非空的Email

Great question! You don't need to write a custom @Query for this requirement—Spring Data JPA's query derivation mechanism can handle this perfectly, as long as your entity mapping aligns with the database structure.

Let's break this down based on how your Email entity is mapped:

Case 1: Email entity has a direct personId field #

If you've added a personId field to your Email entity (matching the database column), your repository method will work exactly as you wrote it:

public interface EmailRepository extends JpaRepository<Email, Integer> {
    // This works! Spring Data will generate the correct query
    List<Email> findByEmailAddressAndPersonIdNotNull(String emailAddress);
}

Spring Data will automatically generate a query equivalent to:

SELECT e FROM Email e WHERE e.emailAddress = ?1 AND e.personId IS NOT NULL

Case 2: Email entity only has a Person association #

If your Email entity uses a @ManyToOne association to Person instead of a direct personId field (like this):

@Entity
public class Email {
    @Id
    private int id;
    private String emailAddress;
    
    @ManyToOne
    @JoinColumn(name = "person_id")
    private Person person;
    // getters/setters
}

You can adjust the method name to check for a non-null Person association (which translates to a non-null person_id in the database):

public interface EmailRepository extends JpaRepository<Email, Integer> {
    List<Email> findByEmailAddressAndPersonIsNotNull(String emailAddress);
}

This will generate the same underlying SQL logic, checking that the person_id column isn't null while matching the email address.

In both scenarios, Spring Data takes care of translating the method name into a valid JPQL query, so no custom @Query is required.

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