MySQL

在MySQL中，可以用 Limit 来查询第 m 列到第 n 列的记录，

例如： select * from tablename limit m, n

sql="select * from users order by userId limit "+(pageNow-1)*pageSize+","+pageSize ; 

获取分页后每页的数据：

//分页显示用户的 信息public ArrayList getUsersByFenye(int pageSize,int pageNow){String sql="select * from users order by userId limit "+(pageNow-1)*pageSize+","+pageSize ;SqlHelper sqlHelper=new SqlHelper();ArrayList al=new ArrayList();ResultSet rs=sqlHelper.executeQuery(sql, null);try {while(rs.next()){User user=new User();user.setUserId(rs.getInt(1));user.setUsername(rs.getString(2));user.setPassword(rs.getString(3));user.setPhone(rs.getString(4));user.setGrade(rs.getInt(5));	al.add(user);}} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}return al;}

　　

获取总页数：

public int getPageCount (int pageSize){String sql="select count(*) from users";int pageCount=0;	SqlHelper sqlHelper=new SqlHelper();ResultSet rs=sqlHelper.executeQuery(sql, null);try {if(rs.next()){int rowCount=0;rowCount=rs.getInt(1);pageCount=(rowCount-1)/pageSize+1;}} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}return pageCount;}　　

SQL Server

但是，在SQL Server中，不支持 Limit 语句。怎么办呢？

解决方案：

虽然SQL Server不支持 Limit ，但是它支持 TOP。

 ps=ct.prepareStatement("select top "+pageSize+" * from users where userId not in (select top "+pageSize*(pageNow-1)+" userId from users)");