Dianthus（二） SpringDateJPA实现复杂SQL查询、分页

由于本人之前用的mybatis比较多。这次是硬着头皮使用了
SpringDateJPA，它的功能很强大，但是我对它的了解少之甚少。这里只列举本项目使用的查询方法。

1.@Query定义查询

• 1.1 项目的dao层的接口继承了JpaRepository接口，所以基本的增删改查 可以通过.save() .findAll() .getOne 实现。

public interface JpaRepository<T, ID> extends PagingAndSortingRepository<T, ID>, QueryByExampleExecutor<T> {
    List<T> findAll();

    List<T> findAll(Sort var1);

    List<T> findAllById(Iterable<ID> var1);

    <S extends T> List<S> saveAll(Iterable<S> var1);

    void flush();

    <S extends T> S saveAndFlush(S var1);

    void deleteInBatch(Iterable<T> var1);

    void deleteAllInBatch();

    T getOne(ID var1);

    <S extends T> List<S> findAll(Example<S> var1);

    <S extends T> List<S> findAll(Example<S> var1, Sort var2);
}

• 一般的SQL 都可以使用@Query注解来完成，如：

    /**
     * 查询子菜单
     *
     * @param parentId
     * @return
     */
    @Query("select m from DSysMenu  m where m.parentId =:parentId and m.enableFlag = 1 order by m.menuOrder")
    List<DSysMenu> getChildMenus(@Param("parentId") String parentId);

也可以在@Query 里加上 nativeQuery = true ，使用原生的SQL

 @Query(value = "select distinct  m.* from  d_sys_menu m right  join   d_sys_role_menu  mr on m.id = mr.menu_id " +
            "where mr.role_id in ( :roleIds )  and m.enable_flag = 1  order by m.menu_order ",
            countQuery = "select count(distinct m.*) from  d_sys_menu m right  join   d_sys_role_menu  mr on m.id = mr.menu_id " +
                    "where mr.role_id in ( :roleIds )  and m.enable_flag = 1  order by m.menu_order ",
            nativeQuery = true)
    List<DSysMenu> getAllMenuByRole(@Param("roleIds") List<String> roleIds);

同样也可以实现分页

 @Override
    public Page exchangeList(Integer currentPage, Integer pageSize, String userId) {
        Pageable pageable = PageRequest.of(currentPage, pageSize);
        return repository.exchangeList(pageable, userId);
    }

 @Query("select t from ReliefDetail t where  t.exchangeUserId =:userId and " +
            "t.enableFlag = 1 order by t.reliefType ,t.createTime desc")
    Page exchangeList(Pageable pageable,String userId);

2. JpaSpecificationExecutor 实现动态多条件查询

通过实现 Specification 中的 toPredicate 方法来定义动态查询，通过 CriteriaBuilder 来创建查询条件

 public BusinessMessage listReliefDetails(Integer currentPage, Integer pageSize,
                                             String applicantId, String exchangeUserId,
                                             Integer reliefType) {
        BusinessMessage message = new BusinessMessage();
        try {
            //排序
            List<Sort.Order> orders = new ArrayList<>();
            orders.add(new Sort.Order(Sort.Direction.DESC, "createTime"));
            //分页参数
            Pageable pageable = PageRequest.of(currentPage, pageSize, Sort.by(orders));
            Page<ReliefDetail> page;
            Specification<ReliefDetail> spec = (Root<ReliefDetail> root, CriteriaQuery<?> query, CriteriaBuilder cb) -> {
                List<Predicate> pl = new ArrayList<Predicate>();
                //申请人ID
                if (!StringUtils.isEmpty(applicantId)) {
                    pl.add(cb.equal(root.<String>get("applicantId"), applicantId));
                }
                //状态
                if (null != reliefType) {
                    pl.add(cb.equal(root.<String>get("reliefType"), reliefType));
                }
                //交换人ID
                if (!StringUtils.isEmpty(exchangeUserId)) {
                    pl.add(cb.equal(root.<String>get("exchangeUserId"), exchangeUserId));
                }
                // 查询出未删除的
                pl.add(cb.equal(root.<Integer>get("enableFlag"), 1));
                return cb.and(pl.toArray(new Predicate[0]));
            };
            page = repository.findAll(spec, pageable);
            message.setData(page);
            message.setSuccess(true);
            message.setMsg("查询换班列表成功");
        } catch (Exception e) {
            e.printStackTrace();
            message.setMsg("查询换班列表失败");
        }
        return message;
    }

3.使用EntityManager的createNativeQuery做多表关联动态查询

createNativeQuery（）是用原生的sql 来做一些复杂查询 ，简单粗暴 。

package com.taiji.duty.service.dm.impl;

import com.taiji.duty.common.BusinessMessage;
import com.taiji.duty.common.MingPageModel;
import com.taiji.duty.dao.dm.DutyKindRepository;
import org.hibernate.query.internal.NativeQueryImpl;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.math.BigInteger;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

import static com.taiji.duty.util.DateUtil.DATE_PATTERN;

/**
 * @ClassName DutyScheduleServiceImpl
 * @Description
 * @Author H.M
 * @Date 2020/3/17
 */
@Service
@Transactional(rollbackFor = Exception.class)
public class DutyScheduleServiceImpl implements DutyScheduleService {


    @PersistenceContext
    private EntityManager entityManager;


    @Override
    public MingPageModel listSchedules(Integer currentPage, Integer pageSize, String userId,
                                       String monthDate, String day, String startDate,
                                       String endDate, String shiftId, String name, String dutyKindId,String dutyDateAsc) {
        Pageable pageable = PageRequest.of(currentPage, pageSize);
        StringBuffer dataSql = new StringBuffer("SELECT t.shift_name ,t.shift_office,ds.*,k.duty_kind_name FROM " +
                "d_duty_schedule ds LEFT JOIN d_shift t  ON ds.duty_shift_id = t.id" +
                " LEFT JOIN d_duty_kind_shift ks ON t.id = ks.shift_id " +
                "LEFT JOIN d_duty_kind k ON k.id = ks.duty_kind_id");
        StringBuilder countSql = new StringBuilder("SELECT count(*) FROM d_duty_schedule ds " +
                "LEFT JOIN d_shift t  ON ds.duty_shift_id = t.id " +
                "LEFT JOIN d_duty_kind_shift ks ON t.id = ks.shift_id" +
                " LEFT JOIN d_duty_kind k ON k.id = ks.duty_kind_id");
        //拼接sql 条件
        StringBuilder whereSql = new StringBuilder(" WHERE 1 = 1");
        if (!StringUtils.isEmpty(userId)) {
            whereSql.append(" AND  ds.duty_user_id =:userId ");
        }
        if (!StringUtils.isEmpty(monthDate)) {
            whereSql.append(" AND ds.duty_month_date =:monthDate ");
        }
        if (!StringUtils.isEmpty(day)) {
            whereSql.append(" AND ds.duty_date =:day ");
        }
        if (!StringUtils.isEmpty(startDate)) {
            whereSql.append(" AND ds.duty_date >= :startDate  ");
        }
        if (!StringUtils.isEmpty(endDate)) {
            whereSql.append(" AND ds.duty_date <= :endDate ");
        }
        if (!StringUtils.isEmpty(shiftId)) {
            whereSql.append(" AND ds.duty_shift_id = :shiftId ");
        }
        if (!StringUtils.isEmpty(dutyKindId)) {
            whereSql.append(" AND k.id = :dutyKindId ");
        }
        if (!StringUtils.isEmpty(name)) {
            name = name + "%";
            whereSql.append(" AND ds.duty_user_name like :name");
        }

        //组装sql语句
        dataSql.append(whereSql).append(" order by  ds.duty_date " + dutyDateAsc + " , t.shift_order ");
        countSql.append(whereSql);
        //创建本地sql查询实例
        Query dataQuery = entityManager.createNativeQuery(dataSql.toString());
        Query countQuery = entityManager.createNativeQuery(countSql.toString());

        //设置参数
        if (!StringUtils.isEmpty(userId)) {
            dataQuery.setParameter("userId", userId);
            countQuery.setParameter("userId", userId);
        }
        if (!StringUtils.isEmpty(monthDate)) {
            dataQuery.setParameter("monthDate", monthDate);
            countQuery.setParameter("monthDate", monthDate);
        }
        if (!StringUtils.isEmpty(day)) {
            dataQuery.setParameter("day", day);
            countQuery.setParameter("day", day);
        }
        if (!StringUtils.isEmpty(startDate)) {
            Date start = DateUtil.stringToDate(startDate, "yyyy-MM-dd");
            dataQuery.setParameter("startDate", start);
            countQuery.setParameter("startDate", start);
        }
        if (!StringUtils.isEmpty(endDate)) {
            Date end = DateUtil.stringToDate(endDate, "yyyy-MM-dd");
            dataQuery.setParameter("endDate", endDate);
            countQuery.setParameter("endDate", endDate);
        }
        if (!StringUtils.isEmpty(shiftId)) {
            dataQuery.setParameter("shiftId", shiftId);
            countQuery.setParameter("shiftId", shiftId);
        }
        if (!StringUtils.isEmpty(dutyKindId)) {
            dataQuery.setParameter("dutyKindId", dutyKindId);
            countQuery.setParameter("dutyKindId", dutyKindId);
        }
        if (!StringUtils.isEmpty(name)) {
            dataQuery.setParameter("name", name);
            countQuery.setParameter("name", name);
        }
        //设置分页
        dataQuery.setFirstResult((int) pageable.getOffset());
        dataQuery.setMaxResults(pageable.getPageSize());
        BigInteger count = (BigInteger) countQuery.getSingleResult();
        Integer total = (int) count.longValue();
        dataQuery.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        List<Map<String, Object>> content2 = total > pageable.getOffset() ?
                dataQuery.getResultList() : Collections.<Map<String, Object>>emptyList();
        MingPageModel pageModel = new MingPageModel(content2, total, pageSize, currentPage);
        return pageModel;
    }

}