Springboot 之 使用JPA做数据筛选

本文章来自【知识林】

在网站系统开发过程中搜索筛选功能随处可见，在JPA中提供的筛选功能只要简单做下封装将非常好用。在文章《Springboot 之 使用JPA对数据进行排序》和《Springboot 之 使用JPA进行分页操作》中讲述了JPA的排序和分页，在本篇文章中将以前两个测试项目的数据做一下筛选功能的描述及详情的测试。

• 修改接口对象

筛选功能需要继承于JpaSpecificationExecutor接口，修改IUserService如下：

import com.zslin.model.User;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;

/**
 * Created by 钟述林 393156105@qq.com on 2016/10/21 17:02.
 */
public interface IUserService extends JpaRepository<User, Integer>, JpaSpecificationExecutor<User> {
}

• 创建筛选条件DTO对象

public class SearchDto {

    private String key;
    private String operation;
    private Object value;

    public SearchDto(String key, String operation, Object value) {
        this.key = key;
        this.operation = operation;
        this.value = value;
    }

    ……省去get和set方法……
}

• 封装筛选对象

这步非常关键，内容也相对较多：

package com.zslin.tools;

import org.springframework.data.jpa.domain.Specification;

import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;

/**
 * Created by 钟述林 393156105@qq.com on 2016/10/21 17:16.
 */
public class BaseSearch<T> implements Specification<T> {

    public static final String GRATE_EQUAL = "ge"; //大于等于
    public static final String GRATE_THEN = "gt"; //大于
    public static final String LESS_EQUAL = "le"; //小于等于
    public static final String LESS_THEN = "lt"; //小于
    public static final String LIKE_BEGIN = "likeb"; // like '%?'
    public static final String LIKE_END = "likee"; //like '?%'
    public static final String LIKE = "like"; //like '%?%'
    public static final String LIKE_BEGIN_END = "likebe"; //like '%?%'
    public static final String NOT_LIKE_BEGIN = "nlikeb"; //not like '%?'
    public static final String NOT_LIKE_END = "nlikee"; //not like '?%'
    public static final String NOT_LIKE = "nlike"; //not like '%?%'
    public static final String NOT_LIKE_BEGIN_END = "nlikebe"; // not like '%?%'
    public static final String EQUAL = "eq"; //equal =
    public static final String NOT_EQUAL = "neq"; // not equal   !=
    public static final String IS_NULL = "isnull"; //is null

    private SearchDto criteria;
    public BaseSearch(SearchDto criteria) {
        this.criteria = criteria;
    }

    @Override
    public Predicate toPredicate
            (Root<T> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
        String opt = criteria.getOperation();
        String key = criteria.getKey();
        String value = criteria.getValue().toString();
        if (opt.equalsIgnoreCase(GRATE_EQUAL)) { //大于等于
            return builder.greaterThanOrEqualTo(
                    root.<String> get(key), value);
        } else if(opt.equalsIgnoreCase(GRATE_THEN)) { //大于
            return builder.greaterThan(root.<String> get(key), value);
        } else if(opt.equalsIgnoreCase(LESS_EQUAL)) { //小于等于
            return builder.lessThanOrEqualTo(root.<String>get(key), value);
        } else if(opt.equalsIgnoreCase(LESS_THEN)) { //小于
            return builder.lessThan(root.<String>get(key), value);
        } else if(opt.equalsIgnoreCase(LIKE_BEGIN)) { // like '%?'
            return builder.like(root.<String>get(key), "%"+value);
        } else if(opt.equalsIgnoreCase(LIKE_END)) { // like '?%'
            return builder.like(root.<String>get(key), value+"%");
        } else if(opt.equalsIgnoreCase(LIKE) || opt.equalsIgnoreCase(LIKE_BEGIN_END)) { //like '%?%'
            return builder.like(root.<String>get(key), "%"+value+"%");
        } else if(opt.equalsIgnoreCase(NOT_LIKE_BEGIN)) { // not like '%?'
            return builder.notLike(root.<String>get(key), "%"+value);
        } else if(opt.equalsIgnoreCase(NOT_LIKE_END)) { // not like '?%'
            return builder.notLike(root.<String> get(key), value + "%");
        } else if(opt.equalsIgnoreCase(NOT_LIKE) || opt.equalsIgnoreCase(NOT_LIKE_BEGIN_END)) { //not like '%?%'
            return builder.notLike(root.<String> get(key), "%"+value+"%");
        } else if(opt.equalsIgnoreCase(EQUAL)) { //equal
            return builder.equal(root.get(key), value);
        } else if(opt.equalsIgnoreCase(NOT_EQUAL)) { //not equal
            return builder.notEqual(root.get(key), value);
        } else if(opt.equalsIgnoreCase(IS_NULL)) { // is null
            return builder.isNull(root.get(key));
        }
        return null;
    }
}

通过这个封装的筛选对象基本可以满足我们大部份的筛选需求。

• 测试

private void print(List<User> list) {
    for(User u : list) {
        System.out.println(u.getId()+"==="+u.getUserName());
    }
}

@Test
public void test1() {
    List<User> list = userService.findAll(new BaseSearch<User>(new SearchDto("userName","eq", "user1")));
    print(list);
}

上面这个例子是测试筛选 userName等于user1的数据，是属于单一条件的筛选。

• 多条件筛选

多条件筛选相对较复杂，需要修改和增加以下代码：

为SearchDto增加属性

public class SearchDto {

    /** 拼接类型，and或者or */
    private String type;
    private String key;
    private String operation;
    private Object value;

    public SearchDto(String key, String operation, Object value) {
        this.key = key;
        this.operation = operation;
        this.value = value;
    }

    public SearchDto(String type, String key, String operation, Object value) {
        this.type = type;
        this.key = key;
        this.operation = operation;
        this.value = value;
    }

    ……省去get和set方法……
}

增加筛选条件的DTO对象

public class SearchSpeDto {

    /** 类型，and或者or */
    private String type;

    private Specifications spes;

    public SearchSpeDto(String type, Specifications spes) {
        this.type = type;
        this.spes = spes;
    }

    public Specifications getSpes() {
        return spes;
    }

    public String getType() {
        return type;
    }

    public void setSpes(Specifications spes) {
        this.spes = spes;
    }

    public void setType(String type) {
        this.type = type;
    }
}

创建筛选功能对象

public class SearchTools {

    public static Specification buildSpecification(SearchSpeDto... speDtos) {
        Specifications result = null;
        for(SearchSpeDto dto : speDtos) {
            if(result==null) {
                result = Specifications.where(dto.getSpes());
            } else {
                if("and".equalsIgnoreCase(dto.getType())) {
                    result = result.and(dto.getSpes());
                } else {
                    result = result.or(dto.getSpes());
                }
            }
        }
        return result;
    }

    public static SearchSpeDto buildSpeDto(String type, SearchDto... searchDtos) {
        SearchSpeDto speDtos = null;

        Specifications result = null;
        for(SearchDto dto : searchDtos) {
            if(result==null) {
                result = Specifications.where(new BaseSearch(dto));
            } else {
                if("and".equalsIgnoreCase(dto.getType())) {
                    result = result.and(new BaseSearch(dto));
                } else {
                    result = result.or(new BaseSearch(dto));
                }
            }
        }
        speDtos = new SearchSpeDto(type, result);
        return speDtos;
    }
}

测试代码

 @Test
public void test2() {
    List<User> list = userService.findAll(SearchTools.buildSpecification(
            SearchTools.buildSpeDto("and", new SearchDto("and", "id", "gt", 2)),
            SearchTools.buildSpeDto("and", new SearchDto("userName", "ne", "user5"),
                    new SearchDto("or", "userName", "ne", "user9"))
    ));
    print(list);
}

注意：通过这个例子已经可以传任意条件进行筛选。

示例代码：https://github.com/zsl131/spring-boot-test/tree/master/study10

本文章来自【知识林】