SQL Spring Boot JPA 查询不为空

Spring Boot JPA Query for not null

提问by Bhetzie

I'm using spring boot JPAs and I want to only return values where the status id is not null. What's the best way to query for this?

我正在使用 spring boot JPA，我只想返回状态 ID 不为空的值。查询此内容的最佳方法是什么？

Domain

领域

@ManyToOne
@JoinColumn(name = "entity_status_id")
private entityStatusLookup entityStatusLookup;

EntityController

实体控制器

public interface EntityRepository extends CrudRepository<Batch, String> {   

    public Page<Entity> findByUploadUserOrderByUploadDateDesc(String userId, Pageable page);

    public Entity findByEntityId(String entityId);

}

api

接口

@RequestMapping(value="/entity/user", method=RequestMethod.GET)
public HttpEntity<PagedResources<Entity>> getEntityByUser(Pageable page, PagedResourcesAssembler assembler) {
    String user = SecurityContextHolder.getContext().getAuthentication().getName();
    Page<Enity> entityItems = entityRepository.findByUploadUserOrderByUploadDateDesc(user, page);

    return new ResponseEntity<>(assembler.toResource(entityItems), HttpStatus.OK);
}

I realize that I could loop through the returned pages and look for nulls to remove, but I'd rather have the query just return values that are not null. I'm not sure what the best way to query for not null on the entity status id.

我意识到我可以遍历返回的页面并查找要删除的空值，但我宁愿让查询只返回不为空的值。我不确定在实体状态 id 上查询 not null 的最佳方法是什么。

回答by baao

You can do that easily in your Interface

你可以在你的界面中轻松做到这一点

public interface EntityRepository extends CrudRepository<Batch, String> {   
    Iterable<Entity> findByStatusIdNotNull();
}

See the docsfor more options

有关更多选项，请参阅文档

回答by Vpn_talent

In case if you want to get a column from your Entity then

如果您想从实体中获取一列，则

and your entity structure like

和你的实体结构像

    @Entity
    @Table(name = "digital_assets")
    public class DigitalAssets implements Serializable{

            /**
             * 
             */
            private static final long serialVersionUID = -2583935636995953450L;

            @Id
            @GeneratedValue(strategy = IDENTITY)
            @Column(name = "id", unique = true, nullable = false)
            private Integer id;

            @Column(name = "company_id")
            private Integer companyId;

            @Column(name = "media_category_id")
            private Integer mediaCategoryId;

            @Column(name="description",columnDefinition="MEDIUMTEXT")
            private String description;

            @Column(name="document_file_name")
            private String documentFileName;

            @Column(name="document_content_type")
            private String documentContentType;

            @Column(name="document_file_size")
            private Integer documentFileSize;

            @Column(name="link_url")
            private String linkUrl;

            @Column(name="status")
            private String status;

            @Column(name="reason_to_reject",columnDefinition="MEDIUMTEXT")
            private String reasonToReject;

            @Column(name="is_deleted")
            private boolean isDeleted;

            @Column(name="rating")
            private Integer rating;

            @Column(name="height")
            private Integer height;

            @Column(name="width")
            private Integer width;

            @Column(name="creator_id")
            private Integer creatorId;

            @Column(name="updater_id")
            private Integer updaterId;

            @Column(name="created_at")
            private Date createdAt;

            @Column(name="updated_at")
            private Date updatedAt;

            @Column(name="tags",columnDefinition="MEDIUMTEXT")
            private String tags;

            @Column(name="geo_location_name")
            private String geoLocationName;

            @Column(name="geo_location_short_name")
            private String geoLocationShortName;

            @Column(name="taken_timestamp")
            private Date takenTimestamp;

            @Column(name="latitude",columnDefinition="FLOAT")
            private Double latitude;

            @Column(name="longitude",columnDefinition="FLOAT")
            private Double longitude;

            @Column(name="notes",columnDefinition="MEDIUMTEXT")
            private String notes;

            @Column(name="reason_to_approve",columnDefinition="MEDIUMTEXT")
            private String reasonToApprove;

            @Column(name="taxonomy_id")
            private Integer taxonomyId;

            @OneToMany(mappedBy="digitalAssets")
            private Set<SkuImages> skuImages;

            @Column(name="asset_xref")
            private String assetXRef;

            @Column(name = "asset_name")
            private String assetName;
}


@Query("select d.id from DigitalAssets d where d.assetName =:assetName and (:mediaCategoryId is null or mediaCategoryId =:mediaCategoryId)  and  d.companyId=:companyId")
public Optional<Integer> findIdByAssetNameAndMediaCategoryIdAndCompanyId(String assetName, Integer mediaCategoryId, Integer companyId);