完美的 jpa 多表原生sql 分页查询

小鱼儿 2022-01-20 23:07 562阅读 0赞

多表分页查询,强烈推荐原生sql

# 一般需要两个方法，一个查条数，一个查当前页数据 #

@Query(value = "" +
                "" +
                " select " +
                "a.id," +
                "a.title," +
                "a.article_abstract," +
                "a.author_id," +
                "a.like_count ," +
                "a.read_count," +
                "a.tags," +
                "a.create_time," +
                "a.update_time," +
                "a.type," +
                "a.`status` " +
                " from re_article_category rac " +
                "" +
                " left join " +
                " article a " +
                "" +
                " on a.id=rac.article_id " +
                "" +
                " where rac.category_id= :catId" +
                " and a.status = :status limit :start,:size",nativeQuery = true)
        List<Object[]> pageByCatId(@Param("catId") Integer catId,@Param("status")String status,@Param("start")long start, @Param("size")int size);
    
       @Query(value = "select" +
               " count(a.id)" +
               " from re_article_category rac" +
               "" +
               " left join " +
               " article a" +
               "" +
               " on a.id=rac.article_id" +
               "" +
               " where rac.category_id= :catId" +
               " and a.status= :status",nativeQuery = true)
        Integer countByCat(@Param("catId") Integer catId,@Param("status") String status);

# service 层转换： #

//根据分类获取所有文章
        @Override
        public Result getPageByCatId(Integer catId, Integer limit, Integer pageNo) { 
    
      PageRequest pageRequest = PageRequest.of(pageNo, limit);//借助计算起始位置
            int total=blogDao.countByCat(catId,BLOG_STATUS_NORMAL);// 计算数据总条数
            List<Object[]> records=blogDao.pageByCatId(catId,BLOG_STATUS_NORMAL,pageRequest.getOffset(),pageRequest.getPageSize());// 获取分页数据
            Page<Object[]> page =new Page<>(records,limit,pageNo,total);
               page.transferFromSqlList(BlogModel.class);// 转换分页数据到具体的java bean
            return Result.success(page);
    
    
        }
    }

# Page.java(自定义工具） #

package com.blog.demo.common.util;
    
    import com.google.common.collect.Lists;
    import org.springframework.data.domain.PageImpl;
    
    import java.lang.reflect.Constructor;
    import java.util.ArrayList;
    import java.util.Arrays;
    import java.util.List;
    
    /** * 与具体ORM实现无关的分页参数及查询结果封装. * * * @param <T> Page中记录的类型. * * @author eagler006 email:eagler006@hotmail.com * @version 1.2 */
    public class Page<T> { 
    
    
    	//-- 分页参数 --//
    	protected int pageNo = 1;
    	protected int pageSize = 15;
    	protected int first =-1; //此处添加主要是为了方便分库分表时计算各表步进取值，当大于0时说明getFirst()函数不从pageNo计算获得
    	protected boolean autoCount = true;
    	protected String sort="id";
    	protected String dir= "ASC";
    	//-- 返回结果 --//
    	protected List result = Lists.newArrayList();
    	protected long totalCount = -1;
    
    	//-- 构造函数 --//
    	public Page() { 
    	}
       public Page(List<Object[]> data,int size,int pageNo,int totalCount){ 
    		setPageNo(pageNo);
    		setPageSize(size);
    		setTotalCount(totalCount);
    		setResult(data);
    
       }
    	public Page(int pageSize) { 
    		this.pageSize = pageSize;
    	}
    
    	public void transferFromSqlList(Class clazz){ 
    		List tResult = Lists.newArrayList();
    		ArrayList<Object> objArr;
    		for (Object objItem : result){ 
    			try { 
    				objArr = new ArrayList<Object>(Arrays.asList((Object[]) objItem));
    				Constructor<T> constructor = clazz.getDeclaredConstructor(objArr.getClass());
    				tResult.add(constructor.newInstance(objArr));
    			} catch (Exception e){ 
    				e.printStackTrace();
    			}
    		}
    		this.result = tResult;
    	}
    
    	public void copyPropertiesFromAnother(Page<?> page){ 
    		this.pageNo = page.getPageNo();
    		this.pageSize = page.getPageSize();
    		this.first = page.getFirst();
    		this.autoCount = page.isAutoCount();
    		this.sort = page.getSort();
    		this.dir = page.getDir();
    		this.totalCount = page.getTotalCount();
    	}
    
    	//-- 访问查询参数函数 --//
    	/** * 获得当前页的页号,序号从1开始,默认为1. */
    	public int getPageNo() { 
    		return pageNo;
    	}
    
    	/** * 设置当前页的页号,序号从1开始,低于1时自动调整为1. */
    	public void setPageNo(final int pageNo) { 
    		this.pageNo = pageNo;
    
    		if (pageNo < 1) { 
    			this.pageNo = 1;
    		}
    	}
    
    	public Page<T> pageNo(final int thePageNo) { 
    		setPageNo(thePageNo);
    		return this;
    	}
    
    	/** * 获得每页的记录数量,默认为1. */
    	public int getPageSize() { 
    		return pageSize;
    	}
    
    	/** * 设置每页的记录数量,低于1时自动调整为1. */
    	public void setPageSize(final int pageSize) { 
    		this.pageSize = pageSize;
    
    		if (pageSize < 1) { 
    			this.pageSize = 1;
    		}
    	}
    
    	public Page<T> pageSize(final int thePageSize) { 
    		setPageSize(thePageSize);
    		return this;
    	}
    
    	
    	
    	/** * @return the sort */
    	public String getSort() { 
    		return sort;
    	}
    
    	/** * @param sort the sort to set */
    	public void setSort(String sort) { 
    		this.sort = sort;
    	}
    
    	/** * @return the dir */
    	public String getDir() { 
    		return dir;
    	}
    
    	/** * @param dir the dir to set */
    	public void setDir(String dir) { 
    		this.dir = dir;
    	}
    
    	/** * 根据pageNo和pageSize计算当前页第一条记录在总结果集中的位置,序号从1开始. */
    	public int getFirst() { 
    		if(this.first>0)
    			return first;
    		return ((pageNo - 1) * pageSize) + 1;
    	}
    
    	
    
    	/** * @param first the first to set */
    	public void setFirst(int first) { 
    		this.first = first;
    	}
    
    	/** * 查询对象时是否自动另外执行count查询获取总记录数, 默认为false. */
    	public boolean isAutoCount() { 
    		return autoCount;
    	}
    
    	/** * 查询对象时是否自动另外执行count查询获取总记录数. */
    	public void setAutoCount(final boolean autoCount) { 
    		this.autoCount = autoCount;
    	}
    
    	public Page<T> autoCount(final boolean theAutoCount) { 
    		setAutoCount(theAutoCount);
    		return this;
    	}
    
    	//-- 访问查询结果函数 --//
    
    	/** * 取得页内的记录列表. */
    	public List getResult() { 
    		return result;
    	}
    
    	/** * 设置页内的记录列表. */
    	public void setResult(final List result) { 
    		this.result = result;
    	}
    
    	/** * 取得总记录数, 默认值为-1. */
    	public long getTotalCount() { 
    		return totalCount;
    	}
    
    	/** * 设置总记录数. */
    	public void setTotalCount(final long totalCount) { 
    		this.totalCount = totalCount;
    	}
    
    	/** * 根据pageSize与totalCount计算总页数, 默认值为-1. */
    	public long getTotalPages() { 
    		if (totalCount < 0) { 
    			return -1;
    		}
    
    		long count = totalCount / pageSize;
    		if (totalCount % pageSize > 0) { 
    			count++;
    		}
    		return count;
    	}
    
    	/** * 是否还有下一页. */
    	public boolean isHasNext() { 
    		return (pageNo + 1 <= getTotalPages());
    	}
    
    	/** * 取得下页的页号, 序号从1开始. * 当前页为尾页时仍返回尾页序号. */
    	public int getNextPage() { 
    		if (isHasNext()) { 
    			return pageNo + 1;
    		} else { 
    			return pageNo;
    		}
    	}
    
    	/** * 是否还有上一页. */
    	public boolean isHasPre() { 
    		return (pageNo - 1 >= 1);
    	}
    
    	/** * 取得上页的页号, 序号从1开始. * 当前页为首页时返回首页序号. */
    	public int getPrePage() { 
    		if (isHasPre()) { 
    			return pageNo - 1;
    		} else { 
    			return pageNo;
    		}
    	}
    }

# 效果： #

{
      "code": 0,
      "msg": "success",
      "data": {
        "pageNo": 1,
        "pageSize": 10,
        "first": 1,
        "autoCount": true,
        "sort": "id",
        "dir": "ASC",
        "result": [
          {
            "id": 73,
            "title": "标题",
            "content": null,
            "articleAbstract": "这是简介，请勿惊讶?",
            "tags": "string",
            "type": "1",
            "readCount": null,
            "likeCount": 1,
            "status": "0",
            "createTime": "2019-06-06T04:32:01.000+0000",
            "updateTime": "2019-06-06T04:32:01.000+0000",
            "categoryId": null
          },
          {
            "id": 107,
            "title": "正式文章",
            "content": null,
            "articleAbstract": "发布一篇文章",
            "tags": "string",
            "type": "0",
            "readCount": 0,
            "likeCount": 1,
            "status": "0",
            "createTime": "2019-06-06T10:32:11.000+0000",
            "updateTime": "2019-06-06T10:32:11.000+0000",
            "categoryId": null
          }
        ],
        "totalCount": 2,
        "prePage": 1,
        "hasNext": false,
        "nextPage": 1,
        "hasPre": false,
        "totalPages": 1
      }
    }