由于springboot jpa 原生的 findAll 是从第0页开始的，因此如果想使用自带的分页配合jqgrid使用需要进行处理。
1、在查询时，先进行-1操作
@GetMapping("/goods/list")
public @ResponseBody Page<SkuInfo> queryGoodsInfos(String venderSku, Pageable pageable)
{
if(!StringUtils.isEmpty(venderSku))
{
return skuRepository.findAllByVenderSku(venderSku,pageable);
}
else
{
Pageable reallyPageable=new PageRequest(pageable.getPageNumber()==0?0:pageable.getPageNumber()-1,pageable.getPageSize(),pageable.getSort());
return skuRepository.findAll(reallyPageable);
}
}
2、在返回内容到前台显示时，进行当前页面+1操作
jsonReader: {
root: "content", // json中代表实际deliverInfo 这个
page: "number+1", // json中代表当前页码的数据
total: "totalPages", // json中代表页码总数的数据
records: "totalElements" // json中代表数据行总数的数据
// subgrid: {root: "data"}
}
下面附上实例，实例A为最简单的直接使用findAll
controller如下：
/** * 商品信息管理界面 */ @Controller public class GoodsInfoController { @Autowired private SkuRepository skuRepository; @GetMapping("/goods/man") public String getGoodsInfoIndex() { return "admin/goods"; } // @GetMapping("/goods/{venderSku}") // public @ResponseBody List<SkuInfo> getGoodsInfo(@PathVariable String venderSku) // { // return skuRepository.findSkuInfosByVenderSku(venderSku); // } @GetMapping("/goods/list") public @ResponseBody Page<SkuInfo> queryGoodsInfos(String venderSku, Pageable pageable) { if(!StringUtils.isEmpty(venderSku)) { return skuRepository.findAllByVenderSku(venderSku,pageable); } else { Pageable reallyPageable=new PageRequest(pageable.getPageNumber()==0?0:pageable.getPageNumber()-1,pageable.getPageSize(),pageable.getSort()); return skuRepository.findAll(reallyPageable); } } }
jqgrid相关的js如下：
function pageInit() { jQuery("#grid").jqGrid({ url: '/goods/list', datatype: "json", ajaxGridOptions: {contentType: 'application/json; charset=utf-8'}, // postData:{beginDate:'2017-02-02',endDate:'2018-03-03',orderId:333}, colNames: ['商家SKU', '可销库存', '批发价', '有效期至', '单体价', '加盟价', '连锁价', '医疗价', '商业价', '最后修改时间'], colModel: [ {name: 'venderSku', index: 'venderSku', width: 80}, {name: 'skuStock', index: 'skuStock', width: 20}, { name: 'skuPrice', index: 'skuPrice', align: "left", sortable: false, width: 80, formatter: 'number', formatoptions: {thousandsSeparator: ",", defaulValue: "", decimalPlaces: 2} }, {name: 'validTime', index: 'validTime', width: 30}, { name: 'monomersPrice', index: 'monomersPrice', align: "left", sortable: false, width: 30, formatter: 'number', formatoptions: {thousandsSeparator: ",", defaulValue: "", decimalPlaces: 2} }, { name: 'joinInPrice', index: 'joinInPrice', align: "left", sortable: false, width: 30, formatter: 'number', formatoptions: {thousandsSeparator: ",", defaulValue: "", decimalPlaces: 2} }, { name: 'chainPrice', index: 'chainPrice', align: "left", sortable: false, width: 30, formatter: 'number', formatoptions: {thousandsSeparator: ",", defaulValue: "", decimalPlaces: 2} }, { name: 'medicalCarePrice', index: 'medicalCarePrice', align: "left", sortable: false, width: 30, formatter: 'number', formatoptions: {thousandsSeparator: ",", defaulValue: "", decimalPlaces: 2} }, { name: 'businessPrice', index: 'businessPrice', align: "left", sortable: false, width: 30, formatter: 'number', formatoptions: {thousandsSeparator: ",", defaulValue: "", decimalPlaces: 2} }, { name: 'lastModifyTime', index: 'lastModifyTime', width: 100, formatter: "date", formatoptions: {srcformat: 'Y-m-d H:i:s', newformat: 'Y-m-d H:i:s'} } ], rowNum: 10, rowList: [10, 20, 30], pager: '#pager2', sortname: 'venderSku', mtype: "GET", postData: $("#searchForm").serializeJSON(), viewrecords: true, sortorder: "desc", emptyrecords: "暂无数据", autowidth: true, rownumbers:true, caption: "用户ERP中提取到的商品信息", loadonce: false, //如果为ture则数据只从服务器端抓取一次，之后所有操作都是在客户端执行，翻页功能会被禁用 loadComplete: function () { }, prmNames: { page: "page", // 表示请求页码的参数名称 rows: "size", // 表示请求行数的参数名称 totalrows: "totalElements"//表示一共有多少条数据 //sort: "sortFieldName", // 表示用于排序的列名的参数名称 //order: "sort" // 表示采用的排序方式的参数名称 }, jsonReader: { root: "content", // json中代表实际模型数据的入口 page: "number+1", // json中代表当前页码的数据 total: "totalPages", // json中代表页码总数的数据 records: "totalElements" // json中代表数据行总数的数据 // subgrid: {root: "data"} } }); }
实例B，需要传入beginDate，endDate这种同一个参数多个值的，即简单查询搞不定的。需要引入JpaSpecificationExecutor
repositroy如下：
public interface DeliverRepository extends JpaRepository<DeliverInfo,Long>, JpaSpecificationExecutor<DeliverInfo> { /** * 获取指定回写状态订单 * @param rewriteState * @return */ List<DeliverInfo> findByrewriteState(int rewriteState); /** * 更新回写状态 * @param rewriteState * @param orderId * @return */ @Transactional @Modifying @Query(value = "update DeliverInfo set rewriteState=:rewriteState where orderId=:orderId") int updateReWriteState(@Param("rewriteState") Integer rewriteState, @Param("orderId") Long orderId); /** * 更新重试次数 * @param retryTimes * @param orderId * @return */ @Transactional @Modifying @Query(value = "update DeliverInfo set retry_times=:retryTimes where orderId=:orderId") int updateretryTimes(@Param("retryTimes") Integer retryTimes, @Param("orderId") Long orderId);
congroller如下：
@Controller public class DeliverInfoManController { @Autowired private DeliverRepository deliverRepository; @GetMapping("/deliver/man") public String getDeliverInfoIndex() { return "admin/deliver"; } @GetMapping("/deliver/list") public @ResponseBody Page<DeliverInfo> getDeliverInfoList(DeliverInfoQuery query,Pageable pageable) { return this.getDataList(query, pageable); } private Page<DeliverInfo> getDataList(DeliverInfoQuery deliverInfoQuery,Pageable pageable) { Specification<DeliverInfo> querySpecifi = new Specification<DeliverInfo>() { public Predicate toPredicate(Root<DeliverInfo> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) { List<Predicate> predicates = new ArrayList<>(); if (!StringUtils.isEmpty(deliverInfoQuery.getBeginDate())) { //大于或等于传入时间 predicates.add(criteriaBuilder.greaterThanOrEqualTo(root.get("orderTime").as(String.class), deliverInfoQuery.getBeginDate())); } if (!StringUtils.isEmpty(deliverInfoQuery.getEndDate())) { //小于或等于传入时间 predicates.add(criteriaBuilder.lessThanOrEqualTo(root.get("orderTime").as(String.class), deliverInfoQuery.getEndDate())); } if (deliverInfoQuery != null) { Class<? extends DeliverInfoQuery> clazz = deliverInfoQuery.getClass(); Field[] fields = clazz.getDeclaredFields(); for (Field tmpField : fields) { tmpField.setAccessible(true); try { if (tmpField.get(deliverInfoQuery) != null && !tmpField.getName().equals("beginDate") && !tmpField.getName().equals("endDate")) //不为空的查询参数才拼查询条件,并且要去掉额外加上的时间范围条件 { String name = tmpField.getName(); predicates.add(criteriaBuilder.equal(root.get(name), tmpField.get(deliverInfoQuery))); // if (tmpField.getType().equals(String.class) && !StringUtils.isEmpty((String)tmpField.get(orderQuery))) //只拼字符串查询条件的，因为目前只需要按照 订单号、退款状态来查询 // { // String name = tmpField.getName(); // predicates.add(criteriaBuilder.equal(root.get(name), tmpField.get(orderQuery))); // } } } catch (IllegalAccessException e) { e.printStackTrace(); } } } // and到一起的话所有条件就是且关系，or就是或关系 return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()])); } }; Sort sort = new Sort(Sort.Direction.DESC, "orderTime"); Pageable reallyPageable=new PageRequest(pageable.getPageNumber()==0?0:pageable.getPageNumber()-1,pageable.getPageSize(),pageable.getSort()); return deliverRepository.findAll(querySpecifi,reallyPageable); } }
jqgrid 相关js如下：
function pageInit() { jQuery("#grid").jqGrid({ url: '/deliver/list', datatype: "json", ajaxGridOptions: {contentType: 'application/json; charset=utf-8'}, // postData:{beginDate:'2017-02-02',endDate:'2018-03-03',orderId:333}, colNames: ['订单号', '配送方式', '商家编码', '快递单号', '物流公司编号', '物流公司名称', '回写标示', '重试次数'], colModel: [ {name: 'orderId', index: 'orderId', width: 40}, {name: 'shipmentType', index: 'shipmentType', width: 40}, {name: 'customerCode', index: 'customerCode', width: 40}, {name: 'deliveryId', index: 'deliveryId', width: 30}, {name: 'logiNo', index: 'logiNo', width: 20}, {name: 'logiCompany', index: 'logiCompany', width: 40}, {name: 'rewriteState', index: 'rewriteState', width: 20}, {name: 'retryTimes', index: 'retryTimes', width: 20} ], rowNum: 10, rowList: [10, 20, 30,5], pager: '#pager2', sortname: 'orderId', mtype: "GET", postData: $("#searchForm").serializeJSON(), viewrecords: true, sortorder: "desc", emptyrecords: "暂无数据", autowidth: true, rownumbers:true, caption: "从ERP种获取到的待回写物流信息", loadonce: false, //如果为ture则数据只从服务器端抓取一次，之后所有操作都是在客户端执行，翻页功能会被禁用 loadComplete: function () { }, prmNames: { page: "page", // 表示请求页码的参数名称 rows: "size", // 表示请求行数的参数名称 totalrows: "totalElements"//表示一共有多少条数据 //sort: "sortFieldName", // 表示用于排序的列名的参数名称 //order: "sort" // 表示采用的排序方式的参数名称 }, jsonReader: { root: "content", // json中代表实际deliverInfo 这个 page: "number+1", // json中代表当前页码的数据 total: "totalPages", // json中代表页码总数的数据 records: "totalElements" // json中代表数据行总数的数据 // subgrid: {root: "data"} } }); }
