jpa查询

查询

Lambda单表/多表查询

单表查询

repo层

package com.my.kj.repo;

import java.util.List;
import java.util.Map;

public interface ForumPostsRepo extends JpaRepository<ForumPostsEntity,Long> {
    //使用的jpa内置的方法，在repo层对应service层，所有只需要带两个分页的参数就好了
    Page<ForumPostsEntity> findAll(Specification<ForumPostsEntity> businessSpec, Pageable pageable);

server/serverImpl层

//对应controller层
PageResultVo<List<ForumPostsVo>>pageInfo(ForumPostsDTO forumPostsDTO);

    @Override
    public PageResultVo<List<ForumPostsVo>> pageInfo(ForumPostsDTO forumPostsDTO) {
        //ForumPostsDTO必须继承了PageVO 
        //getFormThemeSpe，就是我的查询条件方法
        Specification<ForumPostsEntity> businessSpec = getFormThemeSpe(forumPostsDTO);
        Pageable pageable = PageRequest.of(forumPostsDTO.getPageNum()-1,forumPostsDTO.getPageSize());
        //findAll我repo层的方法
        Page<ForumPostsEntity> page = forumPostsRepo.findAll(businessSpec,pageable);
        PageResultVo<List<ForumPostsVo>> ret = new PageResultVo<>();
        long totalElements = page.getTotalPages();
        //获取总页数
        ret.setTotal(totalElements);
        if(totalElements>0){
            //把得到的数据使用分页的形式显示出来，使用stream流来输出
            //map将流中的一个值转换成一个新的值
            List<ForumPostsVo> data = page.getContent().stream().map(v -> {
                //再把v转成一个字符串对象
                String dataStr = JsonUtils.toJSON(v);
                再把VO转成一个对象
                return JsonUtils.fromJSON(dataStr,ForumPostsVo.class);
                //通过一个 Stream 对象生成 List 对象
            }).collect(Collectors.toList());
            ret.setData(data);
        }
        return ret;
    }
        //查询条件方法
        private Specification<ForumThemeEntity> getFormThemeSpe(ForumThemeDTO forumTheme1DTO) {
        List<Predicate> predicateList = new LinkedList<>();
        //jpa的方式来判断
        return (root,cq,cb) ->{
            //获取到这个字段值
            String title = forumTheme1DTO.getTitle();
            //字符串判断的方式
            if(StringUtils.isNotBlank(title)){
            	//去掉空格
                title = title.trim();
                //like模糊查询
                predicateList.add(cb.like(root.get("title"),"%"+ title + "%"));
            }
            //数字类型的判断
            Integer degreeOfHeat = forumTheme1DTO.getDegreeOfHeat();
            if(Objects.nonNull(degreeOfHeat)){
                predicateList.add(cb.equal(root.get("degreeOfHeat"),degreeOfHeat));
            }
            //Boolean类型的判断
            Boolean isHot = forumTheme1DTO.getIsHot();
            if(Objects.nonNull(isHot)){
                predicateList.add(cb.equal(root.get("isHot"),isHot));
            }
            String province = locationDTO.getProvince();
            //不为空
            if (StringUtils.isNotBlank(province)) {
                //判断值里面有没有省这个字
                if (province.contains("省")){
                    //有的话把省替换为空
                    province = province.replace("省","");
                 //判断有没有市
                }else if (province.contains("市")){
                    //有就替换
                    province = province.replace("市","");
                }
                province = province.trim();
                predicateList.add(cb.like(root.get("province"), "%" + province + "%"));
            return cb.and(predicateList.toArray(new Predicate[0]));
        };
    }

controller层

    @ApiOperation(value = "查找新闻",notes = "分页查询")
    @PostMapping("/forumPosts/postList")
    public TradeMessages<PageResultVo<List<ForumPostsVo>>>postList(@Validated @RequestBody ForumPostsDTO dto){
        PageResultVo<List<ForumPostsVo>> pageResultVo = forumPostsService.pageInfo(dto);
        return Response.success(pageResultVo);
    }

但这种只适于单表查询，如果是多表的话就不行了

---

多表查询

repo层

public interface ForumPostsRepo extends JpaRepository<ForumPostsEntity,Long> {
 //value里面查询的p.id什么的就是对应数据库的字段/后面跟的别名(必须要)对应的则是你Vo的字段
 @Query(value = "select p.id,p.title poststitle,theme.title themetitle,p.is_top isTop,p.thumbs thumbs,p.reviews reviews,p.forwarded forwarded,p.tread tread,p.create_user createUser,p.create_time createTime,p.modify_user modifyUser,p.modify_time modifyTime from ys_forum_posts p " +
            " Left JOIN ys_posts_theme_rlt rlt on rlt.post_id=p.id " +
            " Left JOIN ys_forum_theme theme on rlt.theme_id=theme.id " +
            " Left JOIN ys_user u on u.id=p.create_user " +
            //这些都是数据库字段
            " where p.is_delete = 0 and p.is_top = 0 AND p.is_posts=1  and p.status =2 " +
            //如果有查询条件的话，就直接在后面加
            //forumPostsDTO.categoryId对应的是Vo的字段，p.categoryId对于的则是数据库表的字段
            " and  IF (IFNULL(:#{#forumPostsDTO.categoryId},'') != '',p.id = :#{#forumPostsDTO.categoryId}, 1=1) " +
            " and  IF (IFNULL(:#{#forumPostsDTO.title},'') != '', p.title like concat('%',:#{#forumPostsDTO.title},'%'), 1=1) " +
            " and  IF (IFNULL(:#{#forumPostsDTO.isTop},'') != '', p.is_top = :#{#forumPostsDTO.isTop},1 = 1) "+
            " order by p.create_time desc limit :#{#forumPostsDTO.pageStart}, :#{#forumPostsDTO.pageSize} ",nativeQuery = true)
    List<Map<String, Object>> findPostAndTheme(@Param("forumPostsDTO") ForumPostsDTO forumPostsDTO);

    //查询总记录数
    @Query(value = "select count(*) from ys_forum_posts p " +
            " Left JOIN ys_posts_theme_rlt rlt on rlt.post_id=p.id " +
            " Left JOIN ys_forum_theme theme on rlt.theme_id=theme.id " +
            " Left JOIN ys_user u on u.id=p.create_user " +
            " where p.is_delete = 0 and p.is_top = 1 and p.status =2 " +
            " and  IF (IFNULL(:#{#forumPostsDTO.categoryId},'') != '',p.id = :#{#forumPostsDTO.categoryId}, 1=1) " +
            " and  IF (IFNULL(:#{#forumPostsDTO.keywords},'') != '', p.title like concat('%',:#{#forumPostsDTO.keywords},'%'), 1=1) " +
            " and  IF (IFNULL(:#{#forumPostsDTO.isTop},'') != '', p.is_top = :#{#forumPostsDTO.isTop},1 = 1) "+
            " order by p.create_time desc limit :#{#forumPostsDTO.pageStart}, :#{#forumPostsDTO.pageSize} ",nativeQuery = true)
    Long  countPostAndTheme(@Param("forumPostsDTO") ForumPostsDTO forumPostsDTO);

server/serverImpl层

 PageResultVo<List<ForumThemeVo>> pagePostsTheme(ForumPostsDTO forumPostsDTO);

    @Override
    public PageResultVo<List<ForumThemeVo>> pagePostsTheme(ForumPostsDTO forumPostsDTO) {
        forumPostsDTO.countPageStart();
        PageResultVo<List<ForumThemeVo>> pageResultVo = new  PageResultVo<List<ForumThemeVo>>();
        //findPostAndTheme是我repo层的方法
        List<Map<String,Object>> list = forumPostsRepo.findPostAndTheme(forumPostsDTO);
        //查询条件不等于空和大于0的
        if(list!=null && list.size()>0){
            //用stream流输出
            //map将流中的一个值转换成一个新的值
            List<ForumThemeVo> vos = list.stream().map(m -> {
            	//把m转成字符串对象
                String s = JsonUtils.toJSON(m);
                //把得到的结构转换为VO
                ForumThemeVo forumThemeVo = JsonUtils.fromJSON(s,ForumThemeVo.class);
                return forumThemeVo;
            //通过 Stream 生成一个列表
            }).collect(Collectors.toList());
            pageResultVo.setData(vos); 
        }
        //总记录数
        Long total = forumPostsRepo.countPostAndTheme(forumPostsDTO);
        pageResultVo.setTotal(total);
        return pageResultVo;
    }

controller层

    @ApiOperation(value = "查找帖子",notes = "分页查询")
    @PostMapping("/forumPosts/commentList")
    public TradeMessages<PageResultVo<List<ForumThemeVo>>> commentList(@Validated @RequestBody ForumPostsDTO forumPostsDTO) {
        PageResultVo<List<ForumThemeVo>> pageResultVo = forumPostsService.pagePostsTheme(forumPostsDTO);
        return Response.success(pageResultVo);
    }

这种就适用于多表，单表也可以查