SpringBoot+SpringDataJPA如何实现自定义且自由度高的查询[多表，多查询条件，多排序条件，分页，自定义sql封装]

举个例子：我们要在已经搭建好了的JPA环境下实现联合多表，多条件，多排序条件，分页查询一个表格数据，下面的表格

返回类MyJSON：

public class  MyJSON {

    private String code;
    private String msg;
    private Object data;
    private Object extraData;
    private Integer total;

    public MyJSON(){
        super();
    }
    public MyJSON(String code,String msg){
        this.code=code;
        this.msg=msg;
    }
    public MyJSON(String code, Object data) {
        this.code = code;
        this.data = data;
    }
    public MyJSON(String code, Object data, Integer total) {
        this.code = code;
        this.data = data;
        this.total = total;
    }
    public MyJSON(String code, String msg, Object data) {
        this.code = code;
        this.msg = msg;
        this.data = data;
    }
    public MyJSON(String code) {
        this.code = code;
    }
    public MyJSON(String code, String msg, Object data, Integer total) {
        this.code = code;
        this.msg = msg;
        this.data = data;
        this.total = total;
    }

    public String getCode() {
        return code;
    }

    public void setCode(String code) {
        this.code = code;
    }

    public String getMsg() {
        return msg;
    }

    public void setMsg(String msg) {
        this.msg = msg;
    }

    public Object getData() {
        return data;
    }

    public void setData(Object data) {
        this.data = data;
    }

    public Integer getTotal() {
        return total;
    }

    public void setTotal(Integer total) {
        this.total = total;
    }

    public Object getExtraData() {
        return extraData;
    }

    public void setExtraData(Object extraData) {
        this.extraData = extraData;
    }

}

 Code类：

public class Code {
    public static final String SUCCESS="200";//查询结果成功
    public static final String ERROR="201";//出错
    public static final String SERVER_ERROR="202";//服务出错
    public static final String SESSION_TIMEOUT="203";//session过期

}

 

MOrderModel：实体类

//lombok自行百度，其中的@Data大概作用就是省去get、set方法
import lombok.Data;

@Data
public class MOrderModel {
    private int orderId;//订单id
    private Integer cusId;//顾客id
    private String nickName;//顾客昵称
    private String createTime;//下单时间

    public MOrderModel() {
    }

    public MOrderModel(int orderId, Integer cusId, String nickName，String createTime) {
        this.orderId = orderId;
        this.cusId = cusId;
        this.nickName = nickName;
        this.createTime = createTime;
    }
}

DataUtil：作用就是对从前端传过来需要排序的键值对的封装，返回字符串 “ order by AA desc,BB desc,CC asc ”,自己写order by也可以，接收map如：<"AA","desc">、<"BB","desc">、<"CC","asc">

import java.util.Map;

public class DataUtil {
    public static String orderby(Map<String,String> sortOrderMap){
        String queryOrder="";
        if(sortOrderMap.size()!=0){
            queryOrder+=" order by ";
        }
        for(Map.Entry<String, String> entry : sortOrderMap.entrySet()) {
            queryOrder+=" "+entry.getKey()+" "+entry.getValue()+", ";
        }
        if(sortOrderMap.size()!=0){
            queryOrder.substring(0,queryOrder.lastIndexOf(","));
        }
        return queryOrder;
    }

}

Controller：

    @PostMapping("findOrderList")
    @ResponseBody
    public MyJSON findOrderList(MOrderModel order, Integer pageIndex, Integer pageSize, String sortField, String sortOrder){
        Map<String,String> sortMap=new HashMap<>();
        if(StringUtils.isNotEmpty(sortField) && StringUtils.isNotEmpty(sortOrder)){
            sortMap.put(sortField,sortOrder);
        }
        Page<MOrderModel> commodityPage=orderService.findDataList(order,pageIndex,pageSize,sortMap);
        if(commodityPage!=null){
            List<MOrderModel> list=commodityPage.getContent();
            Object orderModel=list.get(0);
            return new MyJSON(Code.SUCCESS,list,Integer.parseInt(commodityPage.getTotalElements()+""));
        }else{
            return new MyJSON(Code.ERROR);
        }

    }

 

ServiceImpl：

参数：
   order：需要传过来的多条件查询
　　pageIndex：0开始的页码
　　pageSize：每页的条数
   sortOrderMap是前端传过来的需要排序的表格列名及排序方向的键值对（多组合排序）

@Autowired
private MOrderRepository orderRepository;

public Page<MOrderModel> findDataList(MOrderModel order, Integer pageIndex, Integer pageSize,Map<String,String> sortOrderMap) {
        Pageable pageable=PageRequest.of(pageIndex,pageSize);
        StringBuffer querySql=new StringBuffer();
        querySql.append("select * from order o " +
                "where o.id=:id");//
        querySql.append(DataUtil.orderby(sortOrderMap));//这里直接用封装好的order by

        StringBuffer countSql=new StringBuffer();
        countSql.append("select count(*) " +
                "from order o " +
                "where o.id=:id");

        Map<String,Object> params=new HashMap<>();//封装参数
        params.put("id",order.getId());
        try {
            Page<MOrderModel> page = orderRepository.findDataList(querySql.toString(), countSql.toString(), params, entityManager, pageable, MOrderModel.class);
            return page;
        }catch(Exception e){
            e.printStackTrace();
            return null;
        }
    }

MOrderRepository： 只需要继承一下自定义的BaseRepository就可以了，主要代码在BaseRepository中

import com.mercury.admin.entity.MOrder;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.stereotype.Repository;

@Repository
public interface MOrderRepository<T> extends JpaSpecificationExecutor<MOrder>, JpaRepository<MOrder,Integer>,BaseRepository {

}

BaseRepository ：

import org.hibernate.query.internal.NativeQueryImpl;
import org.hibernate.transform.Transformers;
import org.springframework.cglib.beans.BeanMap;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Repository;

import javax.persistence.EntityManager;
import javax.persistence.Query;
import java.math.BigInteger;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

@Repository
public interface BaseRepository {

    default <T> Page<T> findDataList(String querySql, String countSql, Map<String,Object> params, EntityManager entityManager, Pageable pageable,Class<T> clazz) throws IllegalAccessException, InstantiationException {
        Query listQuery=entityManager.createNativeQuery(querySql);
        listQuery.setFirstResult((int) pageable.getOffset());
        listQuery.setMaxResults(pageable.getPageSize());
        listQuery.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);//自动映射成map[稍微影响一点查询效率，但是封装后比较方便查看对应的字段，不需要再使用Object[] obj,obj[0],obj[1]...的方式]
        for (Map.Entry<String, Object> entry : params.entrySet()) {//加入参数
            listQuery.setParameter(entry.getKey(), entry.getValue());
        }
        Query countQuery=entityManager.createNativeQuery(countSql);
        for (Map.Entry<String, Object> entry : params.entrySet()) {
            countQuery.setParameter(entry.getKey(), entry.getValue());
        }
        List<Map<String,Object>> list= listQuery.getResultList();//由于上面已经将结果映射成了map所以这里直接转化成Map没问题
        List<T> resultList=new ArrayList<>();
        for(Map<String,Object> map:list){//遍历map将map转化为实体类bean
            T bean=clazz.newInstance();//实例化T，可能会抛出两个异常IllegalAccessException、InstantiationException
            for(Map.Entry<String,Object> entry:map.entrySet()){//格式化Timestamp为String类型,数据库中日期类型为Timestamp，在这里需要转化一下，直接在前端使用
                if(entry.getValue() instanceof Timestamp){
                    try {
                        SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                        Date date=sdf.parse(entry.getValue()+"");
                        String dateStr = sdf.format(date);
                        map.put(entry.getKey(),dateStr);
                    } catch (ParseException e) {
                        e.printStackTrace();
                    }

                }
            }
            BeanMap.create(bean).putAll(map);

            resultList.add(bean);
        }
        BigInteger count=(BigInteger) countQuery.getSingleResult();
        return new PageImpl<>(resultList, pageable, count.longValue());
    }
}