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());

    }

}

巴特西

SpringBoot+SpringDataJPA如何实现自定义查询[多表，多条件，分页，自定义sql封装]

最新文章

热门文章