1.调用存储函数

在网上找了好多调用存储过程的例子,全是注解的方法,结果一顿操作调用要么报错,要么没有返回结果,最后发现还是@Query注解牛逼,直接就执行成功了,还简单方便~

    @Query(nativeQuery = true,value = "call generaterOrderNo('order_info','`code`',?1,8)")
    String generaterOrderNo(String orderNamePre);

2.JPA使用自定义sql

直接在接口中定义方法然后使用@Query注解就可以 设置 nativeQuery = true就可以

@Query(nativeQuery = true,value = "SELECT id,icon,price,shop_name  FROM commodity WHERE if( ?1 is not null and ?1 != '',shop_name LIKE CONCAT('%',?1,'%'),1=1)")
Page<List<Map<String,Object>>> findCommodityList(String shopName,PageRequest page);

3.JPA分页动态条件查询、返回查询结果总数 代码条件查询版


        Specification<CommodityEntity> specification = new Specification<CommodityEntity>() {
            @Override
            public Predicate toPredicate(Root<CommodityEntity> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
                List<Predicate> predicates = new ArrayList<>();
                predicates.add(criteriaBuilder.equal(root.<Integer>get("isDelete"), IS_DELETE));
                if (Validator.isNotEmpty(shopName)){
                    predicates.add(criteriaBuilder.lessThanOrEqualTo(root.<String>get("shopName"), shopName));
                }
                return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()]));
            }
        };
        return  commodityRepository.findAll(specification,page);

4.JPA分页动态条件查询、自定义查询结果返回的字段

再往网上查了好多方法,最终自己研究发现这种方法对业务有非常非常强的要求的时候非常好用灵活,贴一下整体关键代码,以备以后自己也忘记了

首先是这样可以实现分页查询 返给前端数据总条数 用于分页判断,返回的结果为json格式非常利于数据处理,动态条件查询非常方便添加、返回的结果字段可以自己控制,不查询的字段也不会在返回结果中以null值显示。

控制层    

 /**
     * 获取商品列表
     * @param shopName 商品名称条件
     * @param currentPage 页码
     * @param pageSize 条数
     * @return JSONObject
     */
    @RequestMapping("/list")
    public JSONObject list(@RequestParam(name = "shopName",required = false)String shopName,
                           @RequestParam(name = "currentPage",defaultValue = "1")Integer currentPage,
                           @RequestParam(name = "pageSize",defaultValue = "10")Integer pageSize){
        JSONObject resultData = this.start();
        PageRequest page = this.buildPageRequest(currentPage, pageSize);
        try{
            Page<List<Map<String, Object>>> commodityEntities = goodsService.queryAndCommodity(shopName,page);
            resultData.put("data",commodityEntities.getContent());
            resultData.put("count",commodityEntities.getTotalElements());
        }catch (Exception e){
            log.info(e,"商品列表获取异常 {}",e);
            return setErrorMessage(resultData);
        }
        return success(resultData);
    }


分页的组装

    public PageRequest buildPageRequest(int pageNumber, int pageSize, Sort sort) {
        return PageRequest.of(pageNumber-1, pageSize, sort);

    }
    public PageRequest buildPageRequest(int pageNumber, int pageSize) {
        return PageRequest.of(pageNumber-1, pageSize);

    }




业务层

    @Override
    public Page<List<Map<String, Object>>> queryAndCommodity(String shopName, PageRequest page) {
        return commodityRepository.findCommodityList(shopName, page);
    }


jpa接口

    /**
     * 条件查询商品列表
     * @param shopName 商品名称
     * @return 商品集合
     */
    @Query(nativeQuery = true,value = "SELECT id,icon,price,shop_name  FROM commodity WHERE if( ?1 is not null and ?1 != '',shop_name LIKE CONCAT('%',?1,'%'),1=1)")
    Page<List<Map<String,Object>>> findCommodityList(String shopName,PageRequest page);

返回的结果示例

{
    "msg": null,
    "code": 200,
    "cost": 39,
    "data": [
        {
            "icon": "https://ss2.bdstatic.com/70cFvnSh_Q1YnxGkpoWK1HF6hhy/it/u=3557750652,71235332&fm=26&gp=0.jpg",
            "shop_name": "商品7",
            "id": 7,
            "price": 666
        }
    ],
    "count": 1
}