使用Pager + Sqls.create自定义SQL，查询数据库中视图，没有按pager的20条查出，而是全部查出22条

	public Object queryProgressAllCnd(NutMap map) throws BusinessException {
		System.out.println(map);
		Map returnMap = new NutMap();

		SysUser user = (SysUser)Mvcs.getHttpSession().getAttribute(Globals.SESSION_USER_CODE);
		//分页开始
        int pageNumber = map.getInt("pageNumber");
        int pageSize = map.getInt("pageSize");
        Pager pager = new Pager();
        pager.setPageNumber(pageNumber);
        pager.setPageSize(pageSize);
        


    	List<ProjectProgressV> list = new ArrayList<ProjectProgressV>();
		String sqlstr2 = " select * from V_PROGRESS_TJ a where 1=1 ";
		
        //提取条件
		String cndProgress_user = map.getString("cndProgress_user").trim();
		String cndProgress_projectId = map.getString("cndProgress_projectId").trim();
		String cndProgress_tripId = map.getString("cndProgress_tripId").trim();
		if(cndProgress_user!=null && !"".equals(cndProgress_user)){
			sqlstr2 = sqlstr2 + " and a.userId = '"+cndProgress_user+"' ";
		}
		if(cndProgress_projectId!=null && !"".equals(cndProgress_projectId)){
			sqlstr2 = sqlstr2 + " and a.projectId = '"+cndProgress_projectId+"' ";
		}
		if(cndProgress_tripId!=null && !"".equals(cndProgress_tripId)){
			int temp = Integer.valueOf(cndProgress_tripId);
			sqlstr2 = sqlstr2 + " and a.tripId like '%"+temp+"%' ";
		}
		
		
		long total = Daos.queryCount(dao,sqlstr2);
		sqlstr2 = sqlstr2 + " order by a.tripId desc,a.workDate asc";
		Sql sql2 = Sqls.create(sqlstr2);
		//分页
		sql2.setPager(pager);
		
		sql2.setCallback(Sqls.callback.entities());
		sql2.setEntity(dao.getEntity(ProjectProgressV.class));
		dao.execute(sql2);
		//设置总记录数
		pager.setRecordCount((int)total);
		list = sql2.getList(ProjectProgressV.class);
		//return list;
		
        returnMap.put("total", pager.getRecordCount());
        returnMap.put("rows", list);
        return returnMap; 
        
	}

其中pager中参数：size: 20, total: 0, page: 1/0
最后结果：

returnMap.put("rows", list);

中的list有22条记录，都出来了，没有按20条一页来查

之前也是自定义SQL + pager分页，因为是数据库表，一直OK，这次用视图，才出现的，咋弄啊？

wendal

1楼•2655天前

这语句没必要用自定义sql啊

qq_7fafbecc 2楼•2655天前

因为我想保证灵活性的

wendal

3楼•2655天前

不见得灵活性在哪里

qq_7fafbecc 4楼•2655天前

额。。。好吧

qq_7fafbecc 5楼•2655天前

那为啥，pager没分页成功呢

wendal

6楼•2655天前

贴日志

qq_7fafbecc 7楼•2655天前

===================================Action调用开始：【com.shlx.blood.action.ProjectProgressAction】=====方法：【queryProgressAllCnd】=========================>
2017-08-16 17:21:59,654 com.shlx.blood.service.ComServices.ExcuteServices(ComServices.java:80) INFO  - FunctionId : B04.04.01 params:{cndProgress_projectId=, cndProgress_user=, cndProgress_tripId=, page=1, rows=20, pageNumber=1, pageSize=20}
2017-08-16 17:21:59,654 org.nutz.dao.impl.sql.run.NutDaoExecutor.printSQL(NutDaoExecutor.java:388) DEBUG - SELECT * FROM SYSFUNCTION  WHERE functionid=?
    |         1 |
    |-----------|
    | B04.04.01 |
  For example:> "SELECT * FROM SYSFUNCTION  WHERE functionid='B04.04.01'"
2017-08-16 17:21:59,664 org.nutz.ioc.impl.NutIoc.get(NutIoc.java:151) DEBUG - Get 'projectProgressService'<class com.shlx.blood.service.business.ProjectProgressService>
2017-08-16 17:21:59,664 org.nutz.dao.impl.sql.run.NutDaoExecutor.printSQL(NutDaoExecutor.java:388) DEBUG - SELECT * FROM USER_ROLE  WHERE USERID=?
    |    1 |
    |------|
    | 0007 |
  For example:> "SELECT * FROM USER_ROLE  WHERE USERID='0007'"
2017-08-16 17:21:59,664 org.nutz.dao.impl.sql.run.NutDaoExecutor.printSQL(NutDaoExecutor.java:388) DEBUG - select * from(select row_number()over(order by __tc__)__rn__,* from(select top 1 0 __tc__,    * FROM ROLE  WHERE rolecode=?)t)tt where __rn__ > 0 order by __rn__ 
    |  1 |
    |----|
    | 05 |
  For example:> "select * from(select row_number()over(order by __tc__)__rn__,* from(select top 1 0 __tc__,    * FROM ROLE  WHERE rolecode='05')t)tt where __rn__ > 0 order by __rn__ "
2017-08-16 17:21:59,664 org.nutz.dao.impl.sql.run.NutDaoExecutor.printSQL(NutDaoExecutor.java:388) DEBUG - select * from(select row_number()over(order by __tc__)__rn__,* from(select top 1 0 __tc__,    * FROM ROLE_FUNCTION  WHERE rolecode=? AND functionid=?)t)tt where __rn__ > 0 order by __rn__ 
    |  1 |         2 |
    |----|-----------|
    | 05 | B04.04.01 |
  For example:> "select * from(select row_number()over(order by __tc__)__rn__,* from(select top 1 0 __tc__,    * FROM ROLE_FUNCTION  WHERE rolecode='05' AND functionid='B04.04.01')t)tt where __rn__ > 0 order by __rn__ "
{cndProgress_projectId=, cndProgress_user=, cndProgress_tripId=, page=1, rows=20, pageNumber=1, pageSize=20}
2017-08-16 17:22:00,351 org.nutz.dao.impl.sql.run.NutDaoExecutor.printSQL(NutDaoExecutor.java:388) DEBUG - select count(1) from ( select * from V_PROGRESS_TJ a where 1=1 )as _nutz_tmp_2fcc7fqrvcgi0q9ob1d63rmjce
2017-08-16 17:22:00,363 org.nutz.dao.impl.sql.run.NutDaoExecutor.printSQL(NutDaoExecutor.java:388) DEBUG - select * from V_PROGRESS_TJ a where 1=1  order by a.tripId desc,a.workDate asc
===================================Action调用结束：【com.shlx.blood.action.ProjectProgressAction】=====方法：【queryProgressAllCnd】==============耗时：3420毫秒===========>

qq_7fafbecc 8楼•2655天前

日志的sql里好像没有和page有关的东西？

wendal

9楼•2655天前

你的pageNumber是0 吧?

qq_7fafbecc 10楼•2655天前

{cndProgress_projectId=, cndProgress_user=, cndProgress_tripId=, page=1, rows=20, pageNumber=1, pageSize=20}

我打印了参数的

System.out.println(map);

wendal

11楼•2655天前

打印一下 Json.toJson(pager) 执行查询之前

qq_7fafbecc 12楼•2655天前

    |    1 |
    |------|
    | 0007 |
  For example:> "SELECT * FROM USER_ROLE  WHERE USERID='0007'"
2017-08-16 17:38:19,279 org.nutz.dao.impl.sql.run.NutDaoExecutor.printSQL(NutDaoExecutor.java:388) DEBUG - select * from(select row_number()over(order by __tc__)__rn__,* from(select top 1 0 __tc__,    * FROM ROLE  WHERE rolecode=?)t)tt where __rn__ > 0 order by __rn__ 
    |  1 |
    |----|
    | 05 |
  For example:> "select * from(select row_number()over(order by __tc__)__rn__,* from(select top 1 0 __tc__,    * FROM ROLE  WHERE rolecode='05')t)tt where __rn__ > 0 order by __rn__ "
2017-08-16 17:38:19,286 org.nutz.dao.impl.sql.run.NutDaoExecutor.printSQL(NutDaoExecutor.java:388) DEBUG - select * from(select row_number()over(order by __tc__)__rn__,* from(select top 1 0 __tc__,    * FROM ROLE_FUNCTION  WHERE rolecode=? AND functionid=?)t)tt where __rn__ > 0 order by __rn__ 
    |  1 |         2 |
    |----|-----------|
    | 05 | B04.04.01 |
  For example:> "select * from(select row_number()over(order by __tc__)__rn__,* from(select top 1 0 __tc__,    * FROM ROLE_FUNCTION  WHERE rolecode='05' AND functionid='B04.04.01')t)tt where __rn__ > 0 order by __rn__ "
{cndProgress_projectId=, cndProgress_user=, cndProgress_tripId=, page=1, rows=20, pageNumber=1, pageSize=20}
2017-08-16 17:38:20,135 org.nutz.dao.impl.sql.run.NutDaoExecutor.printSQL(NutDaoExecutor.java:388) DEBUG - select count(1) from ( select * from V_PROGRESS_TJ a where 1=1 )as _nutz_tmp_11deepipjghaqo1jkcekoi9l2r
2017-08-16 17:38:20,143 org.nutz.dao.impl.entity.AnnotationEntityMaker.make(AnnotationEntityMaker.java:116) WARN  - No @Table found, fallback to use table name='project_progress_v' for type 'com.shlx.blood.entity.ProjectProgressV'
====开始打印Json.toJson(pager)==========>{
   "pageNumber": 1,
   "pageSize": 20,
   "pageCount": 0,
   "recordCount": 0
}
2017-08-16 17:38:20,149 org.nutz.dao.impl.sql.run.NutDaoExecutor.printSQL(NutDaoExecutor.java:388) DEBUG - select * from V_PROGRESS_TJ a where 1=1  order by a.tripId desc,a.workDate asc
===================================Action调用结束：【com.shlx.blood.action.ProjectProgressAction】=====方法：【queryProgressAllCnd】==============耗时：4676毫秒===========>

wendal

13楼•2655天前

不晓得了, 简化一下代码, 把问题缩小一点吧

qq_7fafbecc 14楼•2654天前

解决了，不用自定义SQL，改用dao.query()来分页可以查出来，
不知道是不是自定义sql是不是不支持视图的分页查询

		System.out.println(map);
		Map returnMap = new NutMap();

		SysUser user = (SysUser)Mvcs.getHttpSession().getAttribute(Globals.SESSION_USER_CODE);
		//分页开始
        int pageNumber = map.getInt("pageNumber");
        int pageSize = map.getInt("pageSize");
        Pager pager = new Pager();
        pager.setPageNumber(pageNumber);
        pager.setPageSize(pageSize);
        
        //定义Cnd
		Cnd cnd = (Cnd)Cnd.where("1", "=", "1");
        
        //提取条件
		String cndProgress_user = map.getString("cndProgress_user").trim();
		String cndProgress_projectId = map.getString("cndProgress_projectId").trim();
		String cndProgress_tripId = map.getString("cndProgress_tripId").trim();
		if(cndProgress_user!=null && !"".equals(cndProgress_user)){
			cnd = cnd.and("userId", "=", cndProgress_user);
		}
		if(cndProgress_projectId!=null && !"".equals(cndProgress_projectId)){
			cnd = cnd.and("projectId", "=", cndProgress_projectId);
		}
		if(cndProgress_tripId!=null && !"".equals(cndProgress_tripId)){
        	cnd = cnd.and("tripId", "like", "%"+cndProgress_tripId+"%");
		}
		cnd.desc("tripId").asc("workDate");
    	List<ProjectProgressV> list = new ArrayList<ProjectProgressV>();
    	//计算总记录数
    	int totalRowsCount = 0;
    	totalRowsCount = dao.count(ProjectProgressV.class, cnd);
    	list = dao.query(ProjectProgressV.class, cnd, pager);
 
        returnMap.put("total", totalRowsCount);
        returnMap.put("rows", list);
        return returnMap;

wendal

15楼•2654天前

自定义sql并不解析语义，不会区分对待的

请先登陆