JPA クエリは null 以外のレコードを取得しません JPQL IS NOT NULL は NULL を持つオブジェクトを返します IS NOT NULL は常に true J​​PA クエリを返します

I am trying to fetch data from a large table. I am using jpa named query to fetch using pageable parameter.

In the result there are some rows which has NULL values.

So I am trying to fetch NOT NULL columns using jpa query and DTO projections.

But the query returns the records with NULL values.

Here is my query:

@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);
}

Result:

I used findByHasPasswordAndFkDataModelIsNotNull and this is returning non null records. I don't understand why the named query is not working.

Here is my Entity class:

@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;
    }
}

My DTO is:

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