SpringCloud学习笔记（二）springDataJpa 进行条件查询、分页查询

快来打我* 2022-03-25 11:26 466阅读 0赞

1.jpa实现条件查询

@RequestMapping(value = "/search",method = RequestMethod.POST)
        public Result search (@RequestBody Label label) {
            List<Label> labelList = labelService.labelSearch(label);
            return new Result(true,StatusCode.OK,"查询成功",labelList);
        }

public List<Label> labelSearch(Label label){
    
            return labelDao.findAll(new Specification<Label>() {
    
                /**
                 * @param root 根对象,也就是要把条件封装到那个对象中。where 类名 = label.getId
                 * @param query 封装的都是查询关键字,比如group by,order by等
                 * @param cb  用来封装条件对象的,如果直接返回null,表示不需要任何条件
                 * @return
                 */
                @Override
                public Predicate toPredicate(Root<Label> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
    
                    List<Predicate> list = new ArrayList<>();
                    if (!ObjectUtils.isEmpty(label.getLabelname())) {
                        //      where labelname like "%小明%"
                        Predicate predicate = cb.like(root.get("labelname").as(String.class),"%" + label.getLabelname() + "");
                        list.add(predicate);
                    }
                    if (!ObjectUtils.isEmpty(label.getState())) {
                        //     state = #{state}
                        Predicate predicate = cb.like(root.get("state").as(String.class),label.getState());
                        list.add(predicate);
                    }
                    Predicate[] preArr = new Predicate[list.size()];
                    list.toArray(preArr);
                    //      where labelname like '%小明% and state = 1'
                    return cb.and(preArr);
                }
            });
        }

2.jpa实现分页查询

前端需要返回如下的结构

>     {
>       "code": 0,
>       "flag": true,
>       "message": "string",
>       "data": {
>         "total": 0,
>         "rows": [
>           {
>             "id": "string",
>             "labelname": "string",
>             "state": "string",
>             "count": 0,
>             "recommend": "string"
>           }
>         ]
>       }
>     }

/**
         * 标准分页
         */
        @RequestMapping(value = "/search/{page}/{size}",method = RequestMethod.POST)
        public Result search (@RequestBody Label label,@PathVariable int currentPage,@PathVariable int size) {
            Page<Label> page = labelService.pageQuery(label,currentPage,size);
            return new Result(true,StatusCode.OK,"查询成功",new PageResult<Label>(page.getTotalElements(),page.getContent()));
        }

如下为分页的具体实现：Pageable为接口，需要ctrl+h显示对应的实现类PageRequest.of,由于分页从0开始，但是前端为从1开始，所以需要currentPage-1

public Page<Label> pageQuery(Label label,int currentPage,int size){
            Pageable page = PageRequest.of(currentPage - 1,size);
            return labelDao.findAll(new Specification<Label>() {
    
                /**
                 * @param root 根对象,也就是要把条件封装到那个对象中。where 类名 = label.getId
                 * @param query 封装的都是查询关键字,比如group by,order by等
                 * @param cb  用来封装条件对象的,如果直接返回null,表示不需要任何条件
                 * @return
                 */
                @Override
                public Predicate toPredicate(Root<Label> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
    
                    List<Predicate> list = new ArrayList<>();
                    if (!ObjectUtils.isEmpty(label.getLabelname())) {
                        //      where labelname like "%小明%"
                        Predicate predicate = cb.like(root.get("labelname").as(String.class),"%" + label.getLabelname() + "");
                        list.add(predicate);
                    }
                    if (!ObjectUtils.isEmpty(label.getState())) {
                        //     state = #{state}
                        Predicate predicate = cb.like(root.get("state").as(String.class),label.getState());
                        list.add(predicate);
                    }
                    Predicate[] preArr = new Predicate[list.size()];
                    list.toArray(preArr);
                    //      where labelname like '%小明% and state = 1'
                    return cb.and(preArr);
                }
            },page);
        }

3.关联查询 @Query注解，**nativeQuery = true进行sql查询**

/**
    * 控制器
    */
    @RequestMapping(value="/waitlist/{labelId}/{page}/{size}",method= RequestMethod.GET)
    	public Result waitList(@PathVariable String labelId,@PathVariable int page,@PathVariable int size){
    		Page<Problem> pageable = problemService.waitList(labelId,page,size);
    		return new Result(true,StatusCode.OK,"查询成功",new PageResult<Problem>(pageable.getTotalElements(),pageable.getContent()));
    	}
    
    
    /**
    * service层
    */
    public Page<Problem> waitList(String labelId,int page,int size){
    	Pageable pageable = PageRequest.of(page - 1,size);
    	return problemDao.waitList(labelId,pageable);
    }
    
    
    /**
    * sql 语句进行查询
    */
        @Query(value = "select * from tb_problem,tb_pl where tb_problem.id = tb_pl.problemid and tb_problem.reply = 0 and tb_pl.labelid = ?",nativeQuery = true)
        public Page<Problem> waitList(String labelId, Pageable pageable);

4.更新操作 **@Modifying** 更新操作

@Modifying
        @Query(value = "update tb_article set thumbup = thumbup + 1 where id = ?",nativeQuery = true)
        public void updateThumbup(String id);