AssetItemDto has the fields name and code , I want to findAll the records in the DB that matches any of the below conditions

1. FindAll where name = "A" and code = "B" (in case name and code are both there and are not null)
2. FindAll where name = "A" (in case name is there and code is null ) and vice versa .

How can I achieve the same using Criteria Query filling up the below function.

AssetItemDto
            
private String name;
private String code;

// Function I am using: 

Page<AssetItemDto> assetItemPage = assetItemService.findByCondition(assetItemDto,pageable);
                   
public Page<AssetItemDto> findByCondition(AssetItemDto assetItemDto , Pageable pageable) {
            
Page<AssetItem> entityPage = repository.findAll(assetItemDto,pageable);
                    List<AssetItem> entities = entityPage.getContent();
            
return new PageImpl<>(mapList(entities, AssetItemDto.class), pageable, entityPage.getTotalElements());
}

CodePudding user response：

You can use the @Query annotation at the top of a repository method for writing a custom query to the database table. Here's a sample according to your conditions. You can modify the method name and the table name accordingly.

    @Query("""
        SELECT * 
        FROM AssetItemDto
        where (name = 'A' AND code = 'B') OR (name = 'A' AND code IS NULL) OR (name IS NULL AND code = 'B');
""")
    List<AssetItemDto> findAllByCustomCondition();

CodePudding user response：

Do you looking for something like this?

Page<AssetItemEntity> findByCondition(String name, String code, Pageable pageable) {
    Specification<AssetItemEntity> specByBoth = (root, query, builder) ->
        builder.equal(root.get("code"), root.get("code"));
    Specification<AssetItemEntity> specByName = (root, query, builder) ->
        builder.equal(root.get("name"), name);
    Specification<AssetItemEntity> specByCode = (root, query, builder) ->
        builder.equal(root.get("code"), code);
    Specification<AssetItemEntity> specByNameIsNull = (root, query, builder) ->
        builder.isNull(root.get("name"));
    Specification<AssetItemEntity> specByCodeIsNull = (root, query, builder) ->
        builder.isNull(root.get("code"));

    var spec = specByBoth
        .or(specByName.and(specByCodeIsNull))
        .or(specByCode.and(specByNameIsNull));
    return assetItemRepository.findAll(spec, pageable);
}