2005-03-03 分页(sql实现)
来源:互联网 发布:mysql菜鸟教程 编辑:程序博客网 时间:2024/05/16 10:39
1)SqlServer分页查询
select top 3 * from newsnews where Number not in (select top 3 Number from newsnews order by Number asc) order by Number asc
select top [pagesize] * from table where id not in ( select top [pagesize*(currentpage-1)] id from table [查询条件] order by id ) and [查询条件] order by id
实际使用:
/** *//**
* 分页显示
* @return ArrayList结果集
*/
public java.util.ArrayList findAllNews(String number,String title,String date,int pagesize,int page) ...{
ArrayList newslist = new ArrayList();
StringBuffer sql = new StringBuffer();
try ...{
conn = manager.getConnection();
stat = conn.createStatement();
sql.append("select top ");
sql.append(pagesize);
sql.append(" * from newsnews ");
sql.append("where Number not in (select top ");
sql.append(pagesize*page);
sql.append(" Number from newsnews ");
sql.append(" order by Number asc) ");
sql.append(" order by Number asc");
System.out.println("分页查询代码:"+sql.toString());
set = stat.executeQuery(sql.toString());
while (set.next()) ...{
InforBean infor = new InforBean();
infor.setNumber(set.getInt("number")+"");
infor.setTitle(set.getString("title"));
infor.setPosttime(set.getString("posttime"));
newslist.add(infor);
infor =null;
}
} catch (SQLException e) ...{
e.printStackTrace();
} finally ...{
try ...{
conn.close();
set.close();
} catch (SQLException e1) ...{
e1.printStackTrace();
}
}
return newslist;
}
* 分页显示
* @return ArrayList结果集
*/
public java.util.ArrayList findAllNews(String number,String title,String date,int pagesize,int page) ...{
ArrayList newslist = new ArrayList();
StringBuffer sql = new StringBuffer();
try ...{
conn = manager.getConnection();
stat = conn.createStatement();
sql.append("select top ");
sql.append(pagesize);
sql.append(" * from newsnews ");
sql.append("where Number not in (select top ");
sql.append(pagesize*page);
sql.append(" Number from newsnews ");
sql.append(" order by Number asc) ");
sql.append(" order by Number asc");
System.out.println("分页查询代码:"+sql.toString());
set = stat.executeQuery(sql.toString());
while (set.next()) ...{
InforBean infor = new InforBean();
infor.setNumber(set.getInt("number")+"");
infor.setTitle(set.getString("title"));
infor.setPosttime(set.getString("posttime"));
newslist.add(infor);
infor =null;
}
} catch (SQLException e) ...{
e.printStackTrace();
} finally ...{
try ...{
conn.close();
set.close();
} catch (SQLException e1) ...{
e1.printStackTrace();
}
}
return newslist;
}
2)oracle或者DB2的分页查询
Oracle数据库使用关键字:rowid或rownum
DB2:rowid或rownum ()
SELECT * FROM ( SELECT row_.*, rownum rownum_ FROM (...... ) row_ WHERE rownum <= ?) WHERE rownum_ > ?
这种用嵌套3层的分页sql是处理效率最高的办法,你找不到更高效的方法。
实际使用:
/** *//**
* 把一般的SQL语句构造成分页的SQL语句
* @param querySql-查询结果
* @param page-页码
* @param pageSize-每页记录数
* @return String-分页的SQL语句
*/
public static String getPageSQL(String querySql, int page, int pageSize) ...{
int startNum = (page - 1) * pageSize + 1;
int endNum = startNum + pageSize - 1;
//获取数据,并根据实际情况分页。
StringBuffer sb = new StringBuffer(
" select * from ( select rownum rnm, a.* from (");
sb.append(querySql);
sb.append(" ) a where rownum <=");
sb.append(endNum).append(" ) where rnm >= ").append(startNum);
String pageSql = sb.toString();
return pageSql;
}
* 把一般的SQL语句构造成分页的SQL语句
* @param querySql-查询结果
* @param page-页码
* @param pageSize-每页记录数
* @return String-分页的SQL语句
*/
public static String getPageSQL(String querySql, int page, int pageSize) ...{
int startNum = (page - 1) * pageSize + 1;
int endNum = startNum + pageSize - 1;
//获取数据,并根据实际情况分页。
StringBuffer sb = new StringBuffer(
" select * from ( select rownum rnm, a.* from (");
sb.append(querySql);
sb.append(" ) a where rownum <=");
sb.append(endNum).append(" ) where rnm >= ").append(startNum);
String pageSql = sb.toString();
return pageSql;
}
- 2005-03-03 分页(sql实现)
- C# DataGridView分页功能(SQL分页存储过程实现)
- SQL Server 2005 中实现数据分页
- Sql Server 2005 ROW_NUMBER 函数实现分页
- Sql Server 2005 ROW_NUMBER 函数实现分页
- SQL 2005的ROW_NUMBER()实现分页功能
- php+SQL实现分页
- SQL实现分页
- sql语句 实现分页
- SQL实现分页
- sql语句实现分页
- Sql Server实现分页
- SQL实现分页
- SQL语句实现分页
- SQL实现分页
- SQL分页实现
- sql server 实现分页
- sql server实现分页
- 基于.NET 2.0的GIS开源项目SharpMap分析手记(十二):SharpMap WMS服务及其Openlayers访问
- 基于J2EE的Ajax宝典------ AjaxTags
- windows命令大全
- GridView用法
- 模式识别技术
- 2005-03-03 分页(sql实现)
- 关于华为,一次不做评价的陈述
- 好男人千万不要娶上海女人做老婆
- Visual Studio 2008 Express Developer Center
- 番茄花园网址
- 得到帐单号码存储过程
- bash初步学习笔记
- Windows XP Service Pack 2 简体中文版
- ActionScript 3 Cookbook 6.4创建自定义可视化对象(翻译:转载请注明)