自定义sql2000分页问题

andying 发布于 2014/08/21 10:53
阅读 616
收藏 1

@JFinal 你好,想跟你请教个问题:

我用的是SQL2000数据库,按你说的自定义一个AnsiSqlDialect出来.

package com.demo.common;

import com.jfinal.plugin.activerecord.dialect.AnsiSqlDialect;

public class MyAnsiDialect extends AnsiSqlDialect  {

	public void forPaginate(StringBuilder sql, int pageNumber, int pageSize,
			String select, String sqlExceptSelect,String keyId) {
		System.out.println("-------MyAnsiDialect.forPaginate--------------------------------");
		int notInPageNum = (pageNumber-1)*pageSize;
		sql.append(" select top ").append(pageSize).append(" ");
		sql.append(sqlExceptSelect).append(" where ").append(keyId).append(" not in (");
		sql.append("select top ").append(notInPageNum).append(sqlExceptSelect).append(" order by ").append(keyId);
		sql.append(") t order by ").append(keyId);
		
		System.out.println("--------------- 拼接后的分页语句 ---------------------------");
		System.out.println(sql);
		
	}
}

 

同时在配置类中也引用了这个自定义的AnsiSqlDialect 方言

public void configPlugin(Plugins me) {
         ...
         ...
         ....
	arpSQL2000.setDialect(new MyAnsiDialect());

	}

 

然后在Controll中有一list方法,分页显示

public void list(){
		int curPageNo=1;
		int pageNum=5;
		
		Page<Quotation>   pageRec=Quotation.dao.paginate(
				curPageNo,
				pageNum,
				" select *  ",
				" from ( "+
				" select t1.id,t1.dh,t1.dDate,t1.vTec_Dh,t1.nMK_id,t1.vCustNo,t1.vContatPer,t1.vContaTEL "+
				" ,t1.vCurrencyNo,t1.phr,t1.phr_name,t1.lphbz,t1.phrq,t2.StyleCode as PadNo,t2.size_fw as SizeRange "+
				" from Sal_Quotation t1 " +
				" left join  Mould_Style t2 on t1.nMK_id = t2.id "+
				" left join  customs_matKind t3  on t2.vCustoms_code=t3.kindCode "+
				" left join pay_mstr  t4 on t1.vPayTerm=t4.pay_code "+
				" left join SCR_Transport_Mstr t5  on t1.vDelyTerm=t5.transCode "+
				" left join Base_customs_type t6  on t1.vCustomsFormat=t6.vCode "+
				"    ) t1  ","t1.id");
		
		List<Quotation>  recs=pageRec.getList();
		for (Quotation quotation : recs) {
			System.out.println(quotation.getStr("dh"));
		}
		renderText("OK.");
	}

 

 

运行报错:

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: 索引 1 超出范围。

跟踪发现是Model.Class中的paginate方法中的如下代码:

List result = Db.query(conn, "select count(*) " + DbKit.replaceFormatSqlOrderBy(sqlExceptSelect), paras);

 result没有得到值,也就是这个查询没运行成功,

"select count(*) " + DbKit.replaceFormatSqlOrderBy(sqlExceptSelect),

以上语句生成的SQL语句如下,在ms-sql查询分析器中运行是没问题的,:

Sql: select count(*)  from ( select t1.id,t1.dh,t1.dDate,t1.vTec_Dh,t1.nMK_id,t1.vCustNo,t1.vContatPer,t1.vContaTEL ,t1.vCurrencyNo,t1.phr,t1.phr_name,t1.lphbz,t1.phrq,t2.StyleCode as PadNo,t2.size_fw as SizeRange from Sal_Quotation t1 left join Mould_Style t2 on t1.nMK_id = t2.id left join customs_matKind t3 on t2.vCustoms_code=t3.kindCode left join pay_mstr t4 on t1.vPayTerm=t4.pay_code left join SCR_Transport_Mstr t5 on t1.vDelyTerm=t5.transCode left join Base_customs_type t6 on t1.vCustomsFormat=t6.vCode ) t1

请问问题出现在哪儿?

 

 

加载中
1
andying
andying

十分感谢@jFinal大大不厌其烦的回答

问题终解决,是由于,isTakeOverDbPaginate,isTakeOverModelPaginate没有重载造成的,并且要返回false,现把MyAnsiDialect类代码再完整贴出来:

package com.demo.common;

import java.sql.Connection;
import java.sql.SQLException;

import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.plugin.activerecord.dialect.AnsiSqlDialect;

public class MyAnsiDialect extends AnsiSqlDialect  {
	@Override
	public void forPaginate(StringBuilder sql, int pageNumber, int pageSize,
			String select, String sqlExceptSelect) {
	    System.out.println("-------MyAnsiDialect.forPaginate--------------------------------");
		int notInPageNum = (pageNumber-1)*pageSize;
		sql.append(" select top ").append(pageSize).append(" ").append(select);
		sql.append(sqlExceptSelect).append(" where id  not in (");
		sql.append("select top ").append(notInPageNum).append("  id ");
		sql.append(sqlExceptSelect).append(" order by id desc ");
		sql.append(" )  order by id  desc ");
		System.out.println("-------- 拼接后的分页语句 ---------------------------");
		System.out.println(sql.toString());
	}
	
	@Override
	public boolean isTakeOverDbPaginate() {
		return false;
	}

	@Override
	public boolean isTakeOverModelPaginate() {
		return false;
	}
	
}

 

JFinal
JFinal
如果 isTakeOverDbPaginate isTakeOverModelPaginate 返回 true 的话,你可以去覆盖 takeOverDbPaginate 与 takeOverModelPaginate 方法来实现分页,只不过要多写点代码,这两个 takeOver 中需要实现查询总记录数,以及计算总页数的逻辑,具体可以参考 AnsiSqlDialect 中的 takeOver 方法
0
JFinal
JFinal
    最后一个String 型的参数: "t1.id" 是干什么的? 前面的 sql 中没有带问号,所以这个参数放这里肯定是错误的
0
andying
andying
这个是一个自增量字段,用来order by 的
0
andying
andying

我把这个t1.id去掉可以正常运行了,但好像没有运行我自定义的分页方法,输出的SQL语句如下,查的是所有记录?

select *  
  from (  
	    select t1.id,t1.dh,t1.dDate,t1.vTec_Dh,t1.nMK_id,t1.vCustNo,t1.vContatPer,t1.vContaTEL  ,t1.vCurrencyNo,t1.phr,t1.phr_name,t1.lphbz,t1.phrq,t2.StyleCode as PadNo,t2.size_fw as SizeRange 
		from Sal_Quotation t1 
		left join  Mould_Style t2 on t1.nMK_id = t2.id  
		left join  customs_matKind t3  on t2.vCustoms_code=t3.kindCode 
		left join pay_mstr  t4 on t1.vPayTerm=t4.pay_code  
	    left join SCR_Transport_Mstr t5  on t1.vDelyTerm=t5.transCode  
        left join Base_customs_type t6  on t1.vCustomsFormat=t6.vCode    
 ) t1

怎么回事?

0
andying
andying

确定查的是所有记录,并没有走自定义类MyAnsiDialect中的代码,也就是说并没有生成拼接的分页SQL语句.在配置类configPlugin中,我不是已经设置启用这个方言了吗?为什么没走这段代码?是哪里配置有问题吗?

JFinal
JFinal
String keyId 这个参数必须去掉
0
JFinal
JFinal

    因为你并没有覆盖掉 AnsiSqlDialect 中的 paginate 方法,所以你自己的方法没有被调用,方法定义如下:

public void forPaginate(StringBuilder sql, int pageNumber, int pageSize, String select, String sqlExceptSelect)
    String keyId 这个参数必须去掉,否则不算方法覆盖,无法多态
0
andying
andying

Model.class中的paginate方法如下:

private Page<M> paginate(Config config, Connection conn, int pageNumber, int pageSize, String select, String sqlExceptSelect, Object... paras) {
...
...
//这句没有正确运行自定义方言?
config.dialect.forPaginate(sql, pageNumber, pageSize, select, sqlExceptSelect);
List<M> list = find(conn, sql.toString(), paras);
return new Page<M>(list, pageNumber, pageSize, totalPage, (int)totalRow);
}

查看源码发现,这个方法是分页的必经之路呀,怎么没走作用?

config.dialect.forPaginate(sql, pageNumber, pageSize, select, sqlExceptSelect);



 

JFinal
JFinal
你的实现多了个参数,String keyId,必须不会认
0
andying
andying

引用来自“JFinal”的评论

    因为你并没有覆盖掉 AnsiSqlDialect 中的 paginate 方法,所以你自己的方法没有被调用,方法定义如下:

public void forPaginate(StringBuilder sql, int pageNumber, int pageSize, String select, String sqlExceptSelect)
    String keyId 这个参数必须去掉,否则不算方法覆盖,无法多态
那我这个KeyId如何设置?
JFinal
JFinal
也可以只是个问号占位,然后 keyId 放在后面的 Object... paras 列表之中
JFinal
JFinal
直接放 sqlExceptSelect 这个参数之中啊
0
andying
andying

去掉这个keyId还是不行,还是没走这段代码:

public class MyAnsiDialect extends AnsiSqlDialect  {

	@Override
	public void forPaginate(StringBuilder sql, int pageNumber, int pageSize,String select,
			String sqlExceptSelect) {
		System.out.println("-------MyAnsiDialect.forPaginate--------------------------------");
		int notInPageNum = (pageNumber-1)*pageSize;
		sql.append(" select top ").append(pageSize).append(" ");
		sql.append(sqlExceptSelect).append(" where id  not in (");
		sql.append("select top ").append(notInPageNum).append(sqlExceptSelect).append(" order by id");
		sql.append(") t order by t1.id");
		
		System.out.println("--------------- 拼接后的分页语句 ---------------------------");
		System.out.println(sql);
	}
	
}


 

0
andying
andying

引用来自“andying”的评论

去掉这个keyId还是不行,还是没走这段代码:

public class MyAnsiDialect extends AnsiSqlDialect  {

	@Override
	public void forPaginate(StringBuilder sql, int pageNumber, int pageSize,String select,
			String sqlExceptSelect) {
		System.out.println("-------MyAnsiDialect.forPaginate--------------------------------");
		int notInPageNum = (pageNumber-1)*pageSize;
		sql.append(" select top ").append(pageSize).append(" ");
		sql.append(sqlExceptSelect).append(" where id  not in (");
		sql.append("select top ").append(notInPageNum).append(sqlExceptSelect).append(" order by id");
		sql.append(") t order by t1.id");
		
		System.out.println("--------------- 拼接后的分页语句 ---------------------------");
		System.out.println(sql);
	}
	
}


 

这样都还不能overide原来的代码?

JFinal
JFinal
代码没生效,重新编译一下,启动下服务,eclipse 有时候会抽风
返回顶部
顶部