Hibernate JPA中的Specification条件查询

具体实现

1、创建一个Maven项目，在pom.xml中配置依赖

    <!--添加MySQL数据库依赖-->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <scope>runtime</scope>
     </dependency>
     <!--添加Hibernate的jpa依赖-->
     <dependency>
       <groupId>org.springframework.boot</groupId>
       <artifactId>spring-boot-starter-data-jpa</artifactId>
     </dependency>

2、在application.properties中配置数据库连接

#数据源
spring.datasource.url=jdbc:mysql://localhost:3306/zhao?serverTimezone=Asia/Shanghai&useSSL=false&characterEncoding=utf8
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

3、创建实体类（Shop ）

@Entity
public class Shop {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    private String shop_name;
    private Double price;
    private Double total;
    private String details;
    private Integer quantity;
    private String jurisdiction;
    //省略getter和setter方法

4、创建接口（ShopRepository）继承jpa

public interface ShopRepository extends JpaRepository<Shop,Integer>, JpaSpecificationExecutor<Shop> {

}

5、创建业务层接口（ShopService）

public interface ShopService {
    Page<Shop> pageShop(Pageable pageable,String shop_name,Double price,Integer quantity,String jurisdiction);
}

6、创建业务实现层（ShopServiceImpl）

@Service
public class ShopServiceImpl implements ShopService {
    @Autowired
    ShopRepository shopRepository;
    //使用的是普通形式
    @Override
    public Page<Shop> pageShop(Pageable pageable, String shop_name, Double price, Integer quantity,String jurisdiction) {
        Specification<Shop> spec = (root, criteriaQuery, criteriaBuilder) -> {
            Predicate predicate = criteriaBuilder.conjunction();
            if (shop_name != null) {
                predicate = criteriaBuilder.and(predicate, criteriaBuilder.equal(root.get("shop_name").as(String.class), shop_name));
            }
            if(quantity != null) {
                predicate = criteriaBuilder.and(predicate, criteriaBuilder.equal(root.get("quantity").as(Integer.class), quantity));
            }
            if(jurisdiction != null){
                predicate = criteriaBuilder.and(predicate, criteriaBuilder.equal(root.get("jurisdiction").as(String.class), jurisdiction));
            }
            criteriaQuery.distinct(true).where(predicate);
            return criteriaQuery.getRestriction();
        };
        Page<Shop> shop = shopRepository.findAll(spec,pageable);
        return shop;
    }
    //List类型
     @Autowired
    ShopRepository shopRepository;
    @Override
    public Page<Shop> pageShop(Pageable pageable, String shop_name, Double price, Integer quantity, List<String> jurisdiction, String role_name) {
        Specification<Shop> spec = (root, criteriaQuery, criteriaBuilder) -> {
            List<Predicate> list = new ArrayList<Predicate>();
            if (price != null) {
                Predicate isDeleted = criteriaBuilder.equal(root.get("price"), price);
                list.add(isDeleted);
            }
            if(jurisdiction != null) {
                CriteriaBuilder.In<Object> in = criteriaBuilder.in(root.get("jurisdiction"));
                for(String id:jurisdiction) {
                    in.value(id);
                }
                list.add(in);
            }
            Predicate[] p = new Predicate[list.size()];
            return criteriaBuilder.and(list.toArray(p));
        };
        Page<Shop> shop = shopRepository.findAll(spec,pageable);
        return shop;
    }
}

7、创建控制层（ShopController）

@Controller
@RequestMapping("shop")
public class ShopController {
    @Autowired
    private ShopService shopService;

    @RequestMapping("findAll")
    @ResponseBody
    public Page<Shop> find(@RequestParam(value = "shop_name", required = false)String shop_name,
                           @RequestParam(value = "price", required = false)Double price,
                           @RequestParam(value = "quantity", required = false)Integer quantity,
                           @RequestParam(value = "jurisdiction", required = false)String jurisdiction,
                           @RequestParam(value = "page", required = false, defaultValue = "1") Integer page,
                           @RequestParam(value = "size", required = false, defaultValue = "5") Integer size){
        Sort sort = Sort.by(Sort.Order.asc("id"));
        Pageable pageable = PageRequest.of(page-1, size, sort);
        Page<Shop> shop = shopService.pageShop(pageable,shop_name,price,quantity,jurisdiction);
        return shop;
    }
}

8、使用测试工具测试

这是单表的条件查询，到此结束！！！

二个表一对一关联查询

1、创建第二个实体类（Jurisdiction）

@Entity
public class Jurisdiction {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    private String role_name;
    //把jurisdiction看作是关联第一个表的id（唯一标识）
    private String jurisdiction;
    //省略getter和setter方法
    }

2、稍微的修改第一个实体类（Shop）

 //把之前的这个属性换成实体类的形式
 private String jurisdiction;

如下：

 @OneToOne(fetch = FetchType.EAGER)
 @JoinColumn(name = "jurisdiction")
 private Jurisdiction jurisdiction;

3、接下来不用做任何改变即可查询出关联表的信息
到此，一对一关联查询结束！！！