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

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

返回类MyJSON：

public classMyJSON {privateString code;privateString msg;privateObject data;privateObject extraData;privateInteger total;publicMyJSON(){super();

}publicMyJSON(String code,String msg){this.code=code;this.msg=msg;

}publicMyJSON(String code, Object data) {this.code =code;this.data =data;

}publicMyJSON(String code, Object data, Integer total) {this.code =code;this.data =data;this.total =total;

}publicMyJSON(String code, String msg, Object data) {this.code =code;this.msg =msg;this.data =data;

}publicMyJSON(String code) {this.code =code;

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

}publicString getCode() {returncode;

}public voidsetCode(String code) {this.code =code;

}publicString getMsg() {returnmsg;

}public voidsetMsg(String msg) {this.msg =msg;

}publicObject getData() {returndata;

}public voidsetData(Object data) {this.data =data;

}publicInteger getTotal() {returntotal;

}public voidsetTotal(Integer total) {this.total =total;

}publicObject getExtraData() {returnextraData;

}public voidsetExtraData(Object extraData) {this.extraData =extraData;

}

}

Code类：

public classCode {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方法

importlombok.Data;

@Datapublic classMOrderModel {private int orderId;//订单id

private Integer cusId;//顾客id

private String nickName;//顾客昵称 private String createTime;//下单时间

publicMOrderModel() {

}public MOrderModel(intorderId, 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如：、、

importjava.util.Map;public classDataUtil {public static String orderby(MapsortOrderMap){

String queryOrder="";if(sortOrderMap.size()!=0){

queryOrder+=" order by ";

}for(Map.Entryentry : sortOrderMap.entrySet()) {

queryOrder+=" "+entry.getKey()+" "+entry.getValue()+", ";

}if(sortOrderMap.size()!=0){

queryOrder.substring(0,queryOrder.lastIndexOf(","));

}returnqueryOrder;

}

}

Controller：

@PostMapping("findOrderList")

@ResponseBodypublicMyJSON findOrderList(MOrderModel order, Integer pageIndex, Integer pageSize, String sortField, String sortOrder){

Map sortMap=new HashMap<>();if(StringUtils.isNotEmpty(sortField) &&StringUtils.isNotEmpty(sortOrder)){

sortMap.put(sortField,sortOrder);

}

Page commodityPage=orderService.findDataList(order,pageIndex,pageSize,sortMap);if(commodityPage!=null){

List list=commodityPage.getContent();

Object orderModel=list.get(0);return new MyJSON(Code.SUCCESS,list,Integer.parseInt(commodityPage.getTotalElements()+""));

}else{return newMyJSON(Code.ERROR);

}

}

ServiceImpl：

参数：

order：需要传过来的多条件查询

pageIndex：0开始的页码

pageSize：每页的条数 sortOrderMap是前端传过来的需要排序的表格列名及排序方向的键值对(多组合排序)

@Autowired

private MOrderRepository orderRepository;

public Page findDataList(MOrderModel order, Integer pageIndex, Integer pageSize,MapsortOrderMap) {

Pageable pageable=PageRequest.of(pageIndex,pageSize);

StringBuffer querySql=newStringBuffer();

querySql.append("select * from order o " +

"where o.id=:id");//

querySql.append(DataUtil.orderby(sortOrderMap));//这里直接用封装好的order by

StringBuffer countSql=newStringBuffer();

countSql.append("select count(*) " +

"from order o " +

"where o.id=:id");

Map params=new HashMap<>();//封装参数

params.put("id",order.getId());try{

Page page = orderRepository.findDataList(querySql.toString(), countSql.toString(), params, entityManager, pageable, MOrderModel.class);returnpage;

}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;

@Repositorypublic interface MOrderRepository extends JpaSpecificationExecutor, JpaRepository,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;

@Repositorypublic interfaceBaseRepository {

default Page findDataList(String querySql, String countSql, Map params, EntityManager entityManager, Pageable pageable,Class clazz) throwsIllegalAccessException, 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.Entryentry : params.entrySet()) {//加入参数

listQuery.setParameter(entry.getKey(), entry.getValue());

}

Query countQuery=entityManager.createNativeQuery(countSql);for (Map.Entryentry : params.entrySet()) {

countQuery.setParameter(entry.getKey(), entry.getValue());

}

List> list= listQuery.getResultList();//由于上面已经将结果映射成了map所以这里直接转化成Map没问题

List resultList=new ArrayList<>();for(Map map:list){//遍历map将map转化为实体类bean

T bean=clazz.newInstance();//实例化T，可能会抛出两个异常IllegalAccessException、InstantiationException

for(Map.Entry entry:map.entrySet()){//格式化Timestamp为String类型,数据库中日期类型为Timestamp，在这里需要转化一下，直接在前端使用

if(entry.getValue() instanceofTimestamp){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());

}

}