诡异问题之一
下面是一段分页的代码:
public Vector getContents(double eachNum,int pageNum,String tabName,String[] fids,String condition,String jspName)
{
Vector contents=new Vector();
int max=pageNum*(int)eachNum;
int min=(pageNum-1)*(int)eachNum;
String s= " ";
try
{
DBConnect db=new DBConnect();
Connection conn=db.getConn();
Statement stmt2=conn.createStatement();
ResultSet rs2=stmt2.executeQuery( "select count(*) from "+tabName+ " "+condition);
rs2.next();
int totalNum=rs2.getInt(1);
rs2.close();
stmt2.close();
totalPageNum=1;
if (totalNum/eachNum> (int)(totalNum/eachNum))
{
totalPageNum=(int)(totalNum/eachNum)+1;
}else
{
totalPageNum=(int)(totalNum/eachNum);
}
getBeforeNext(totalNum,pageNum,totalPageNum,jspName);
//System.out.print( "总共有 "+totalPageNum+ "页 "+ " <BR> ");
//开始取数据
String sql;
if(eachNum!=0){
sql = "select top " + (int) eachNum + " ";
}else{
sql= "select ";
}
for (int m=0;m <fids.length-1;m++ )
{
sql=sql+fids[m]+ ", ";
}
sql=sql+fids[fids.length-1];
if(pageNum!=1){
sql = sql + " from " + tabName +
" where id not in (select top " + min +
" id from " + tabName + " "+condition+ ") "+condition;
}else{
sql = sql + " from " + tabName+ " "+ condition;
}
s=sql;
System.out.println( "sql: "+sql);
Statement stmt=conn.createStatement();
ResultSet rs=stmt.executeQuery(sql);
while (rs.next())
{
String[] content=new String[fids.length];
for (int f=0;f <fids.length ;f++ )
{
content[f]=rs.getString(f+1);
}
contents.add(content);
}
rs.close();
stmt.close();
conn.close();
}catch(Exception e)
{
System.out.println( "从数据库中取值时出错： "+ "sql: "+s+e.toString());
}
return contents;
}
现在问题出来了------------------数据库ACCESS
当这样定义参数时:
String tabName = "show ";//表名
String[] fids = {
"path ", "time ", "id "};//列名
String jspName = "newcshow.jsp ";显示分页的JSP
String condition = "order by 'id ' ";排序方式
int pageNum=1;当前第几页
int eachNum = 5;每页多少条
控制台输出的SQL语句是:sql:select top 5 path,time,id from show order by 'id '
但是返回的记录数并不符合定义的eachNum,而是返回了表中的所有记录
当把String condition = "order by id ";改成这样的时候TOMCAT报出这样的错误:
从数据库中取值时出错：sql:java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] 试图执行的查询中不包含作为合计函数一部分的特定表达式 'id ' 。
但是直接在方法中执行ResultSet rs=stmt.executeQuery( "select top 5 path,time,id from show order by id ");是没有错误的
请教高手帮忙测试一下 找出问题所在
[解决办法]
可以肯定的是order by id而不是order by 'id '.
还真的是诡异阿，呵呵。
先把缓存清空，再试
