Hibernate实现分页及条件查询（查询指定年月完成订单）

盛年不重来，一日难再晨。

前言

Hibernate是一款良好实现JPA的持久层框架，其全自动的特性为我们省去了许多麻烦。但是同时在某些方面却少了些灵活性，可我们仍不用为此担心，因为其提供了实用的API来规避以上的问题。

大致思路

示例实现的是查询时构建条件和分页。

代码实现

Controller

package com.xxx.xxx.controller.order;


import com.xxx.xxx.entity.Order;
import com.xxx.xxx.service.OrderService;
import com.xxx.xxx.controller.order.vo.orderVO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import java.text.ParseException;

/**
 * 订单控制层
 */
@RestController
@RequestMapping("/order")

public class OrderController {

    @Autowired
    private OrderService orderService;

    /**
     * 获取用户已完成订单列表。
     *
     */
    @GetMapping("/getfinishedorder")
    public Result getFinishedOrder(@RequestParam(value = "page", required = false, defaultValue = "1") Integer page,
                                   @RequestParam(value = "size", required = false, defaultValue = "10") Integer size,
                                   @RequestParam(value = "month", required = false) Integer month,
                                   @RequestParam(value = "year", required = false) Integer year,
                                   @RequestParam(value = "customerId", required = false) Integer customerId) throws ParseException {
        //分页规则：从0（limit从零开始即为第1条）开始每页10条，按创建时间降序排列。
        PageRequest pageRequest = new PageRequest(page - 1, size, new Sort(Sort.Direction.DESC, "createDate"));
        Page<Order> Orders = OrderService.getFinishedOrder(customerId, month, year, pageRequest);
         Page<OrderVO> vos=Orders.map(OrderVO::convertEntity2VO);
        return Result.success(vos);
    }
}

OrderServiceImpl

package com.xxx.xxx.service.impl;

import com.xxx.xxx.entity.Order;
import com.xxx.xxx.repository.OrderRepository;
import com.lamp.common.service.MerchantOrderService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;

import javax.persistence.criteria.Predicate;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.temporal.TemporalAdjusters;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

@Service
public class OrderServiceImpl implements OrderService {

    @Autowired
    private OrderRepository orderRepository;

    @Override
    public Page<Order> getFinishedOrder(Integer customerId, Integer month, Integer year, Pageable pageable) throws ParseException {
        //未指定年份月份时，默认查询当年当月。数据库中DateTime：2020-08-05 17:43:03
        Calendar calendar=Calendar.getInstance();
        if (month==null && year==null){
            //月份从0开始，所以+1。
            month=calendar.get(Calendar.MONTH) + 1;
            year=calendar.get(Calendar.YEAR);
        }else if (month==null && year!=null){
            month=calendar.get(Calendar.MONTH) + 1;
        }else {
            year=calendar.get(Calendar.YEAR);
        }
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String start = month<=10?year+"-0"+month+"-01 00:00:00":year+"-"+month+"-01 00:00:00";
        String end = month+1<=10?year+"-0"+(month+1)+"-01 00:00:00":year+"-"+(month+1)+"-01 00:00:00";
        Date startTime = simpleDateFormat.parse(start);
        Date endTime = simpleDateFormat.parse(end);
        Specification<MerchantOrder> specification=(root,criteriaQuery,criteriaBuilder)->{
            Predicate predicate = criteriaBuilder.equal(root.get("customer").get("customerId").as(Integer.class), customerId);
            Predicate status = criteriaBuilder.equal(root.get("status").as(Order.OrderStatus.class), Order.OrderStatus.finish);
            Predicate timeHorizon = criteriaBuilder.between(root.get("createDate").as(Date.class),startTime,endTime);
            criteriaQuery.where(predicate,status,timeHorizon);
            return criteriaQuery.getRestriction();
        };
        return OrderRepository.findAll(specification, pageable);
    }
}

OrderRepository

package com.lamp.common.repository;

import com.xxx.xxx.entity.Order;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;

public interface MerchantOrderRepository extends JpaRepository<Order,Integer>, JpaSpecificationExecutor<Order> {

}