以前是用mysql写得,分页,可以实现效果,但是现在想要sqlserver数据库,不知道怎么总是报错,求教??
将sql改为String sql = "select top "+pageSize+" * from stu where id not in(select top "+pageSize*(pageIndex-1)+" id from stu)";总是报一些莫名其妙的错误,求大神,告诉我改怎么做。。
//dao类
public Map stuSelect(int pageIndex,int pageSize) throws Exception{ Map map = new HashMap();
List<StuInfo> list = new ArrayList<StuInfo>();
String sql = "select * from stu where 1=1";
sql += " order by id limit "+((pageIndex-1)*pageSize)+","+pageSize+"";
//String sql = "select top "+pageSize+" * from stu where id not in(select top "+pageSize*(pageIndex-1)+" id from stu)"; System.out.println(sql);
StuInfo info = null;
Connection con = this.getCon();
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(sql);
while(rs.next()){
info = new StuInfo();
info.setId(rs.getInt("id"));
info.setName(rs.getString("name"));
info.setAge(rs.getInt("age"));
list.add(info);
}
map.put("list", list);
map.put("totalSize", this.getTotalCnt(sql));
this.Close(con, st, rs);
return map; }//BaseDao类public int getTotalCnt(String sql) throws Exception{
StringBuffer cntSql = new StringBuffer("select count(*) ");
String sqlold = sql.substring(sql.indexOf("from"),sql.indexOf("limit")); //去除分页
cntSql.append(sqlold);
int count = 0;
Connection con = this.getCon();
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(cntSql.toString());
if(rs.next())
count = rs.getInt(1);
else
count = 0;
this.Close(con, st, rs);
return count;
}
将sql改为String sql = "select top "+pageSize+" * from stu where id not in(select top "+pageSize*(pageIndex-1)+" id from stu)";总是报一些莫名其妙的错误,求大神,告诉我改怎么做。。
//dao类
public Map stuSelect(int pageIndex,int pageSize) throws Exception{ Map map = new HashMap();
List<StuInfo> list = new ArrayList<StuInfo>();
String sql = "select * from stu where 1=1";
sql += " order by id limit "+((pageIndex-1)*pageSize)+","+pageSize+"";
//String sql = "select top "+pageSize+" * from stu where id not in(select top "+pageSize*(pageIndex-1)+" id from stu)"; System.out.println(sql);
StuInfo info = null;
Connection con = this.getCon();
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(sql);
while(rs.next()){
info = new StuInfo();
info.setId(rs.getInt("id"));
info.setName(rs.getString("name"));
info.setAge(rs.getInt("age"));
list.add(info);
}
map.put("list", list);
map.put("totalSize", this.getTotalCnt(sql));
this.Close(con, st, rs);
return map; }//BaseDao类public int getTotalCnt(String sql) throws Exception{
StringBuffer cntSql = new StringBuffer("select count(*) ");
String sqlold = sql.substring(sql.indexOf("from"),sql.indexOf("limit")); //去除分页
cntSql.append(sqlold);
int count = 0;
Connection con = this.getCon();
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(cntSql.toString());
if(rs.next())
count = rs.getInt(1);
else
count = 0;
this.Close(con, st, rs);
return count;
}
解决方案 »
- struts2中如何实现action到jsp的跳转
- 处理doPost()中问乱码
- 谁能给用hibernate对视图查询的步骤,方法和配置的详细说明阿
- 在java类中如何调用action对应的URL?
- 烦人的打印,一起讨论,来者有分
- zebra条码打印机888-TT如何写程序进行控制
- 使用SERVLET如何在日志中纪录用户下载文件的总时间长度?
- 用jsp做的购物车模块总是提示:对不起,提交出错,请稍后重试。各位大侠帮忙看看。跪谢
- 高分相送!!!!!一经解决立刻给分!!!jsp中将附件(客户端)存入数据库(服务器端)及取出.
- APP发送POST请求到后台,如何防止别人获得请求后,模拟发送POST?
- 求浏览器的兼容问题??
- 网络爬虫
改为String sqlold = sql.substring(sql.indexOf("from"),sql.indexOf("in"));就报如下错误java.sql.SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '10 * from stu where id not in(select top 0 id from stu)' at line 1