@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
请问问题出现在哪儿?
十分感谢@jFinal大大不厌其烦的回答
问题终解决,是由于,isTakeOverDbPaginate,isTakeOverModelPaginate没有重载造成的,并且要返回false,现把MyAnsiDialect类代码再完整贴出来:
packagecom.demo.common;importjava.sql.Connection;importjava.sql.SQLException;importcom.jfinal.plugin.activerecord.Page;importcom.jfinal.plugin.activerecord.Record;importcom.jfinal.plugin.activerecord.dialect.AnsiSqlDialect;publicclassMyAnsiDialectextendsAnsiSqlDialect{@OverridepublicvoidforPaginate(StringBuildersql,intpageNumber,intpageSize,Stringselect,StringsqlExceptSelect){System.out.println("-------MyAnsiDialect.forPaginate--------------------------------");intnotInPageNum=(pageNumber-1)*pageSize;sql.append("selecttop").append(pageSize).append("").append(select);sql.append(sqlExceptSelect).append("whereidnotin(");sql.append("selecttop").append(notInPageNum).append("id");sql.append(sqlExceptSelect).append("orderbyiddesc");sql.append(")orderbyiddesc");System.out.println("--------拼接后的分页语句---------------------------");System.out.println(sql.toString());}@OverridepublicbooleanisTakeOverDbPaginate(){returnfalse;}@OverridepublicbooleanisTakeOverModelPaginate(){returnfalse;}}如果isTakeOverDbPaginateisTakeOverModelPaginate返回true的话,你可以去覆盖takeOverDbPaginate与takeOverModelPaginate方法来实现分页,只不过要多写点代码,这两个takeOver中需要实现查询总记录数,以及计算总页数的逻辑,具体可以参考AnsiSqlDialect中的takeOver方法 最后一个String型的参数: "t1.id"是干什么的?前面的sql中没有带问号,所以这个参数放这里肯定是错误的这个是一个自增量字段,用来orderby的
我把这个t1.id去掉可以正常运行了,但好像没有运行我自定义的分页方法,输出的SQL语句如下,查的是所有记录?
select*from(selectt1.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.StyleCodeasPadNo,t2.size_fwasSizeRangefromSal_Quotationt1leftjoinMould_Stylet2ont1.nMK_id=t2.idleftjoincustoms_matKindt3ont2.vCustoms_code=t3.kindCodeleftjoinpay_mstrt4ont1.vPayTerm=t4.pay_codeleftjoinSCR_Transport_Mstrt5ont1.vDelyTerm=t5.transCodeleftjoinBase_customs_typet6ont1.vCustomsFormat=t6.vCode)t1怎么回事?
确定查的是所有记录,并没有走自定义类MyAnsiDialect中的代码,也就是说并没有生成拼接的分页SQL语句.在配置类configPlugin中,我不是已经设置启用这个方言了吗?为什么没走这段代码?是哪里配置有问题吗?
StringkeyId这个参数必须去掉因为你并没有覆盖掉AnsiSqlDialect中的paginate方法,所以你自己的方法没有被调用,方法定义如下:
publicvoidforPaginate(StringBuildersql,intpageNumber,intpageSize,Stringselect,StringsqlExceptSelect) StringkeyId这个参数必须去掉,否则不算方法覆盖,无法多态Model.class中的paginate方法如下:
privatePage<M>paginate(Configconfig,Connectionconn,intpageNumber,intpageSize,Stringselect,StringsqlExceptSelect,Object...paras){......//这句没有正确运行自定义方言?config.dialect.forPaginate(sql,pageNumber,pageSize,select,sqlExceptSelect);List<M>list=find(conn,sql.toString(),paras);returnnewPage<M>(list,pageNumber,pageSize,totalPage,(int)totalRow);}查看源码发现,这个方法是分页的必经之路呀,怎么没走作用?
config.dialect.forPaginate(sql,pageNumber,pageSize,select,sqlExceptSelect);你的实现多了个参数,StringkeyId,必须不会认
因为你并没有覆盖掉AnsiSqlDialect中的paginate方法,所以你自己的方法没有被调用,方法定义如下:
publicvoidforPaginate(StringBuildersql,intpageNumber,intpageSize,Stringselect,StringsqlExceptSelect) StringkeyId这个参数必须去掉,否则不算方法覆盖,无法多态也可以只是个问号占位,然后keyId放在后面的Object...paras列表之中直接放sqlExceptSelect这个参数之中啊去掉这个keyId还是不行,还是没走这段代码:
publicclassMyAnsiDialectextendsAnsiSqlDialect{@OverridepublicvoidforPaginate(StringBuildersql,intpageNumber,intpageSize,Stringselect,StringsqlExceptSelect){System.out.println("-------MyAnsiDialect.forPaginate--------------------------------");intnotInPageNum=(pageNumber-1)*pageSize;sql.append("selecttop").append(pageSize).append("");sql.append(sqlExceptSelect).append("whereidnotin(");sql.append("selecttop").append(notInPageNum).append(sqlExceptSelect).append("orderbyid");sql.append(")torderbyt1.id");System.out.println("---------------拼接后的分页语句---------------------------");System.out.println(sql);}}
去掉这个keyId还是不行,还是没走这段代码:
publicclassMyAnsiDialectextendsAnsiSqlDialect{@OverridepublicvoidforPaginate(StringBuildersql,intpageNumber,intpageSize,Stringselect,StringsqlExceptSelect){System.out.println("-------MyAnsiDialect.forPaginate--------------------------------");intnotInPageNum=(pageNumber-1)*pageSize;sql.append("selecttop").append(pageSize).append("");sql.append(sqlExceptSelect).append("whereidnotin(");sql.append("selecttop").append(notInPageNum).append(sqlExceptSelect).append("orderbyid");sql.append(")torderbyt1.id");System.out.println("---------------拼接后的分页语句---------------------------");System.out.println(sql);}}代码没生效,重新编译一下,启动下服务,eclipse有时候会抽风
版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。