SQLServer分页

  public static List getPageCommonUser(int currentPage) throws Exception {
        Connection conn = null;
        PreparedStatement prep = null;
        ResultSet rs = null;
        List result = new ArrayList();
        try {
            conn = DBTools.getConnection();
            int rowCount = getCount();
            int pageSize = getPageSize();
            //应获取的记录数
            int rows = (rowCount - (currentPage - 1) * getPageSize()) >
                       getPageSize() ?
                       pageSize : (rowCount - (currentPage - 1) * getPageSize());
            //分页的查询语句
            String sql = " SELECT TOP " + rows + " * FROM" +
                         " (SELECT TOP " + currentPage * getPageSize() +
                         " * FROM [commonUser] Order by id asc) a" +
                         " order by id desc";
            System.out.println("sql:" + sql);
            //out.println(str);
            prep = conn.prepareStatement(sql);
            rs = prep.executeQuery();
            while (rs.next()) {
                commonUserBean commonuser = new commonUserBean();
                commonuser.setId(rs.getInt("id"));
                commonuser.setLoginName(rs.getString("loginName"));
                commonuser.setTrueName(rs.getString("trueName"));
                commonuser.setPassword(rs.getString("password"));
                commonuser.setSex(rs.getInt("sex"));
                commonuser.setIdentityCard(rs.getString("identityCard"));
                commonuser.setProvince(rs.getInt("province"));
                commonuser.setAddress(rs.getString("address"));
                commonuser.setPost(rs.getString("post"));
                commonuser.setTelephone(rs.getString("telephone"));
                result.add(commonuser);
            }

        } finally {
            if (rs != null) {
                rs.close();
            }
            if (prep != null) {
                prep.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
        return result;

    }