SpringDataJPA（四）多条件查询接口JpaSpecificationExecutor的使用

浅浅的花香味﹌ 2021-12-17 02:37 760阅读 0赞

## 源码 ##

**GitHub：** [https://github.com/291685399/springboot-learning/tree/master/springboot-springdatajpa04][https_github.com_291685399_springboot-learning_tree_master_springboot-springdatajpa04]

## JpaSpecificationExecutor接口 ##

*  JpaSpecificationExecutor接口是单独存在的，完全独立的
 *  该接口主要是提供了多条件查询的支持，并且可以在查询中添加分页与排序
 *  该接口需要配合其他接口（例如Repository、CurdRepository、JPARepository、PagingAndSortingRepositor）进行使用，要不然注入实现接口对象的时候会报错，在IOC容器中不能找到该bean

JpaSpecificationExecutor接口中声明了五个方法，下面将介绍这五个方法的使用

public interface JpaSpecificationExecutor<T> { 
    
        Optional<T> findOne(@Nullable Specification<T> var1);
    
        List<T> findAll(@Nullable Specification<T> var1);
    
        Page<T> findAll(@Nullable Specification<T> var1, Pageable var2);
    
        List<T> findAll(@Nullable Specification<T> var1, Sort var2);
    
        long count(@Nullable Specification<T> var1);
    }

## 使用JpaSpecificationExecutor接口 ##

**pom.xml：**

<dependencies>
        
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    
        
        **<dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>**
    
        
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
    
        
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
    </dependencies>

**application.properties：**

# datasource
    spring.datasource.url=jdbc:mysql://127.0.0.1:3306/springboot-springdatajpa04?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=true
    spring.datasource.driver-class-name=com.mysql.jdbc.Driver
    spring.datasource.username=root
    spring.datasource.password=root
    # springdatajpa
    #打印出自动生产的SQL，方便调试的时候查看
    spring.jpa.show-sql=true
    #更新数据库表结构
    spring.jpa.hibernate.ddl-auto=update
    #对打印的sql进行格式化，方便查看
    spring.jpa.properties.hibernate.format_sql=true
    spring.jpa.properties.hibernate.use_sql_comments=true
    #指定生成表名的存储引擎为InneoDB
    spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect

**User：**

@Entity
    @Data
    @Table(name = "user")
    public class User implements Serializable { 
    
        @Id
        @GeneratedValue(strategy = GenerationType.AUTO)
        @Column(name = "id")
        private int id;
        private String name;
        private Integer age;
        private String sex;
        private String address;
        private String phone;
    }

**UserJPASpecificationExecutor：**

public interface UserJPASpecificationExecutor extends JpaRepository<User, Integer>, JpaSpecificationExecutor<User> { 
    }

**UserRepositoryTests：**

@RunWith(SpringRunner.class)
    @SpringBootTest
    public class UserJPASpecificationExecutorTests { 
    
        @Autowired
        private UserJPASpecificationExecutor userJPASpecificationExecutor;
    
        /** * findOne */
        @Test
        public void findAllTest1() { 
            Specification<User> specification = new Specification<User>() { 
                @Override
                public Predicate toPredicate(Root<User> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) { 
                    //select * from user where phone='12345697842'
                    return criteriaBuilder.equal(root.get("phone"), "12345697842");
                }
            };
            Optional<User> userOptional = userJPASpecificationExecutor.findOne(specification);
            User user = userOptional.get();
            System.out.println(user);
        }
    
        /** * List<T> findAll(@Nullable Specification<T> var1); */
        @Test
        public void findAllTest2_1() { 
            /** * Specification<User>：用于封装查询条件 */
            Specification<User> specification = new Specification<User>() { 
                /** * Predicate：封装单个查询条件 * @param root：查询对象的属性封装，或者说是User的包装类 * @param criteriaQuery：封装了要执行的查询中的各个部分的信息，select、from、order by * @param criteriaBuilder：查询条件的构造器，定义不同的查询条件 * @return */
                @Override
                public Predicate toPredicate(Root<User> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) { 
                    //select * from user where name='张三'
                    Predicate predicate = criteriaBuilder.equal(root.get("name"), "张三");
                    return predicate;
                }
            };
            List<User> userList = userJPASpecificationExecutor.findAll(specification);
            System.out.println(userList);
        }
    
        /** * List<T> findAll(@Nullable Specification<T> var1); */
        @Test
        public void findAllTest2_2() { 
            Specification<User> specification = new Specification<User>() { 
                @Override
                public Predicate toPredicate(Root<User> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) { 
                    //select * from user where name='张三' and age ='34'
                    List<Predicate> predicateList = new ArrayList<>();
                    predicateList.add(criteriaBuilder.equal(root.get("name"), "张三"));
                    predicateList.add(criteriaBuilder.equal(root.get("age"), 34));
                    Predicate[] predicates = new Predicate[predicateList.size()];
                    return criteriaBuilder.and(predicateList.toArray(predicates));
                    //或者使用：return criteriaBuilder.and(criteriaBuilder.equal(root.get("name"), "张三"),criteriaBuilder.equal(root.get("age"), 34));
                }
            };
            List<User> userList = userJPASpecificationExecutor.findAll(specification);
            System.out.println(userList);
        }
    
        /** * Page<T> findAll(@Nullable Specification<T> var1, Pageable var2); */
        @Test
        public void findAllTest3() { 
            Specification<User> specification = new Specification<User>() { 
                @Override
                public Predicate toPredicate(Root<User> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) { 
                    //select * from user where name='张三' and age ='34'
                    return criteriaBuilder.and(criteriaBuilder.equal(root.get("name"), "张三"), criteriaBuilder.equal(root.get("age"), 34));
                }
            };
            //order by id desc limit 0,1
            PageRequest pageRequest=new PageRequest(0,1,new Sort(Sort.Direction.DESC,"id"));
            Page<User> userPage = userJPASpecificationExecutor.findAll(specification, pageRequest);
            List<User> userList = userPage.getContent();
            System.out.println(userList);
        }
    
        /** * List<T> findAll(@Nullable Specification<T> var1, Sort var2); */
        @Test
        public void findAllTest4() { 
            Specification<User> specification = new Specification<User>() { 
                @Override
                public Predicate toPredicate(Root<User> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) { 
                    //select * from user where name='张三' and age ='34'
                    return criteriaBuilder.and(criteriaBuilder.equal(root.get("name"), "张三"), criteriaBuilder.equal(root.get("age"), 34));
                }
            };
            //order by id desc
            Sort sort = new Sort(new Sort.Order(Sort.Direction.DESC, "id"));
            List<User> userList = userJPASpecificationExecutor.findAll(specification, sort);
            System.out.println(userList);
        }
    
        /** * long count(@Nullable Specification<T> var1); */
        @Test
        public void findAllTest5() { 
            Specification<User> specification = new Specification<User>() { 
                @Override
                public Predicate toPredicate(Root<User> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) { 
                    //select * from user where name='张三'
                    return criteriaBuilder.equal(root.get("name"), "张三");
                }
            };
            long count = userJPASpecificationExecutor.count(specification);
            System.out.println(count);
        }
    }

[https_github.com_291685399_springboot-learning_tree_master_springboot-springdatajpa04]: https://github.com/291685399/springboot-learning/tree/master/springboot-springdatajpa04