举个例子：我们要在已经搭建好了的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());
}
}