JPA命名查询无法过滤NULL记录，派生查询却正常？

尝试从大表中查询数据，使用带Pageable参数的JPA命名查询结合DTO投影，期望获取fkDataModel和metadata字段不为NULL的记录，但查询结果仍包含NULL值。然而使用findByHasPasswordAndFkDataModelIsNotNull派生查询却能返回正确的非NULL记录，无法理解命名查询失效的原因。

相关代码 #

Repository代码 #

@Repository
public interface ListElementRepository extends JpaRepository<ListElement,Integer> {
    @Query("""
        SELECT new com.mavenir.cms.ext.dto.ListElementDto(l.id,l.name,l.fkDataModel,l.metadata,l.hasPassword) 
        from ListElement l
        where l.hasPassword=:hasPassword and l.fkDataModel is not null and l.metadata is not null
    """)
    Page<ListElementDto> findByHasPassword(String hasPassword, Pageable pageable);
}

Entity类 #

@Entity
@Table(name = "yang_list")
public class ListElement {
    private Integer id;
    private String name;
    private String yangSchema;
    private String scopeType;
    private String visibility;
    private Integer sequence;
    private Integer fkDataModel;
    private Integer fkModule;
    private String moduleName;
    private String confdPath;
    private String nodeType;
    private List<String> columns;
    private String rebootAttributes;
    private String metadata;
    private String keyLeaf;

    private String isAugmentedNode;
    private String augmentedNamespace;
    private String augTargetPath;
    private String augTargetModuleNs;
    private String isLiTableEnabled;

    private String hasPassword;

    public ListElement() {
    }

    public ListElement(String name, String scopeType, String moduleName) {
        this.name = name;
        this.scopeType = scopeType;
        this.moduleName = moduleName;
    }

    @Id
    @Column(name = "id", nullable = false, unique = true)
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @JsonIgnore
    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    @Column(name = "name", nullable = false)
    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @Column(name = "yang_schema", nullable = false)
    @JsonIgnore
    public String getYangSchema() {
        return yangSchema;
    }

    public void setYangSchema(String yangSchema) {
        this.yangSchema = yangSchema;
    }

    @Column(name = "scope_type", nullable = false)
    public String getScopeType() {
        return scopeType;
    }

    public void setScopeType(String scopeType) {
        this.scopeType = scopeType;
    }

    @Column(name = "visibility", nullable = false)
    public String getVisibility() {
        return visibility;
    }

    public void setVisibility(String visibility) {
        this.visibility = visibility;
    }

    @Column(name = "sequence", nullable = false)
    @JsonIgnore
    public Integer getSequence() {
        return sequence;
    }

    public void setSequence(Integer sequence) {
        this.sequence = sequence;
    }

    @Column(name = "fk_yang_module")
    @JsonIgnore
    public Integer getFkModule() {
        return fkModule;
    }

    public void setFkModule(Integer fkModule) {
        this.fkModule = fkModule;
    }

    @Transient
    public String getModuleName() {
        return moduleName;
    }

    public void setModuleName(String moduleName) {
        this.moduleName = moduleName;
    }

    @Column(name = "confd_path")
    public String getConfdPath() {
        return confdPath;
    }

    public void setConfdPath(String confdPath) {
        this.confdPath = confdPath;
    }
    @Column(name = "node_type")
    public String getNodeType() {
        return nodeType;
    }

    public void setNodeType(String nodeType) {
        this.nodeType = nodeType;
    }

    @Convert(converter = StringListConverter.class)
    @Column(name = "columns", length = 1000)
    public List<String> getColumns() {
        return columns;
    }

    public void setColumns(List<String> columns) {
        this.columns = columns;
    }

    @Column(name = "reboot_attributes")
    public String getRebootAttributes() {
        return rebootAttributes;
    }

    public void setRebootAttributes(String rebootAttributes) {
        this.rebootAttributes = rebootAttributes;
    }
    @Column(name = "yang_metadata")
    public String getMetadata() {
        return metadata;
    }

    public void setMetadata(String metadata) {
        this.metadata = metadata;
    }
    @Column(name = "fk_data_module")
    @JsonIgnore
    public Integer getFkDataModel() {
        return fkDataModel;
    }

    public void setFkDataModel(Integer fkDataModel) {
        this.fkDataModel = fkDataModel;
    }

    @Column(name = "key_leaf")
    public String getKeyLeaf() {
        return keyLeaf;
    }

    public void setKeyLeaf(String keyLeaf) {
        this.keyLeaf = keyLeaf;
    }

    @Column(name = "is_augmented_node")
    public String getIsAugmentedNode() {
        return isAugmentedNode;
    }

    public void setIsAugmentedNode(String isAugmentedNode) {
        this.isAugmentedNode = isAugmentedNode;
    }

    @Column(name = "augmented_namespace")
    public String getAugmentedNamespace() {
        return augmentedNamespace;
    }

    public void setAugmentedNamespace(String augmentedNamespace) {
        this.augmentedNamespace = augmentedNamespace;
    }

    @Column(name = "aug_target_path")
    public String getAugTargetPath() {
        return augTargetPath;
    }

    public void setAugTargetPath(String augTargetPath) {
        this.augTargetPath = augTargetPath;
    }

    @Column(name = "aug_target_ns")
    public String getAugTargetModuleNs() {
        return augTargetModuleNs;
    }

    public void setAugTargetModuleNs(String augTargetModuleNs) {
        this.augTargetModuleNs = augTargetModuleNs;
    }


    @Column(name = "is_li_table_enabled", nullable = false)
    public String getIsLiTableEnabled() {
        return isLiTableEnabled;
    }

    public void setIsLiTableEnabled(String isLiTableEnabled) {
        this.isLiTableEnabled = isLiTableEnabled;
    }

    // 注意：此处代码不完整，需修正
    // public JSONObject returnMetaData(String prefix,Integer cId,Boolean 

    @Column(name = "has_password")
    public String getHasPassword() {
        return hasPassword;
    }

    public void setHasPassword(String hasPassword) {
        this.hasPassword = hasPassword;
    }
}

DTO类 #

@AllArgsConstructor
@Getter
@Setter
public class ListElementDto {
    private Integer id;
    private String name;
    private Integer fkDataModel;
    private String metadata;
    private String hasPassword;
}

排查与解决方案 #

1. 查看实际执行的SQL
   开启JPA的SQL日志输出，确认命名查询生成的SQL是否包含正确的过滤条件：

   spring.jpa.show-sql=true
   spring.jpa.properties.hibernate.format_sql=true
   

   检查生成的SQL中是否同时存在fk_data_module IS NOT NULL和yang_metadata IS NOT NULL的条件，若缺失则说明JPQL语句存在问题。

2. 检查数据库数据
   确认返回的“NULL”记录在数据库中对应的字段是SQL NULL还是空字符串/"null"字符串：

   • 若为空字符串，需在JPQL中补充过滤条件：and l.metadata != ''
   • 若为"null"字符串，需添加and l.metadata != 'null'

3. 验证实体映射正确性
   确认fkDataModel和metadata的@Column注解是否正确映射到数据库列：

   • fkDataModel对应fk_data_module，metadata对应yang_metadata，若列名拼写错误，会导致过滤条件失效。

4. 修正Entity类代码
   Entity类中存在不完整的方法定义public JSONObject returnMetaData(...)，需补全或删除该行代码，避免编译错误影响JPA映射。

5. 测试参数绑定
   确认传入hasPassword参数的值与数据库中存储的格式一致（如大小写、字符集），避免因参数不匹配导致where条件失效。

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