SpringBoot 从入门到入门（八 ORM-JPA-5 复杂查询）

基于方法名的查询，尽管很简单，但是满足不了复杂查询，可以使用JpaSpecificationExecutor来进行复杂查询

一.数据库中创建user、user_comment表

user_comment.user_id = user.id

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 NOT NULL,
  `email` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO `test`.`user`(`id`, `name`, `email`) VALUES (1, 'admin', '张三@李四.com');

 

CREATE TABLE `user_comment` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `comment` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO `test`.`user_comment`(`id`, `user_id`, `comment`) VALUES (1, 1, 'comment1');

二.生成entity

生成entity步骤参见 SpringBoot 从入门到入门（五 ORM-JPA-1） 

三.修改IUserRepository，继承 JpaSpecificationExecutor

package com.example.demo.repository;

import com.example.demo.entity.UserEntity;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.stereotype.Repository;



@Repository
public interface IUserRepository extends JpaRepository<UserEntity,Integer>, JpaSpecificationExecutor<UserEntity> {


}

JpaSpecificationExecutor接口方法中接收Specification作为参数 

三.编写Specification

为了演示方便，在Demo1Application文件中创建Specification

在类中新增implements Specification的子类

    private class MySpec implements Specification<UserEntity>{


        @Override
        public Predicate toPredicate(Root<UserEntity> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
            return null;
        }
    }

在toPredicate方法中，编写查询内容

private class MySpec implements Specification<UserEntity>{

        @Override
        public Predicate toPredicate(Root<UserEntity> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
            Path<Integer> id = root.get("id");
            Path<String> name = root.get("name");
            Path<String> email = root.get("email");
            Predicate predicate = criteriaBuilder.or(criteriaBuilder.like(name,"%ad%"),criteriaBuilder.equal(email,"张三@李四.com"));
            return criteriaBuilder.and(predicate,criteriaBuilder.equal(id,1));
        }
    }

上述例子SQL为

select userentity0_.id as id1_0_, userentity0_.email as email2_0_, userentity0_.name as name3_0_ from user userentity0_ where (userentity0_.name like ? or userentity0_.email=?) and userentity0_.id=1

四.创建路由并访问

    @GetMapping("/user/all")
    public List<UserEntity> userAll() {
        List<UserEntity> list = userRepository.findAll(new MySpec());
        return list;
    }

五.重复第三步，可实现复杂查询

1.JOIN

• 修改UserEntity，增加字段，配置关联关系 

  private Set<UserCommentEntity> userCommentEntity;
  
      @OneToMany
      @JoinColumn(name = "user_id",referencedColumnName = "id")
      public Set<UserCommentEntity> getUserCommentEntity(){
          return userCommentEntity;
      }
      public void setUserCommentEntity(Set<UserCommentEntity> userCommentEntity){
          this.userCommentEntity = userCommentEntity;
      }

  编写toPredicate

      private class MySpec implements Specification<UserEntity>{
  
          @Override
          public Predicate toPredicate(Root<UserEntity> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
              Path<Integer> id = root.get("id");
              Path<String> name = root.get("name");
              Path<String> email = root.get("email");
              Predicate predicate = criteriaBuilder.or(criteriaBuilder.like(name,"%ad%"),criteriaBuilder.equal(email,"张三@李四.com"));
              Join<UserEntity, UserCommentEntity> join = root.join("userCommentEntity",JoinType.LEFT);
              return criteriaBuilder.and(predicate,criteriaBuilder.equal(id,1));
          }
      }

  等同于

  select userentity0_.id as id1_0_, userentity0_.email as email2_0_, userentity0_.name as name3_0_ from user userentity0_ left outer join user_comment usercommen1_ on userentity0_.id=usercommen1_.user_id where (userentity0_.name like ? or userentity0_.email=?) and userentity0_.id=1