Question

oracle 分页查询问题，求高手帮忙！

我在PL/SQL Developer 中测试的一个分页查询，没有问题，但是在java中，使用PreparedStatement却查询不出来结果，要么就查询出的记录数与PL/SQL Developer中出来的不一致，具体代码如下：
找不出什么问题，分页代码肯定没错，myeclipse中没报错，而且当查询pageNo=1,pageSize=10能查询出四条记录
public List<User> getUserList(int pageNo, int pageSize) {
StringBuilder sb = new StringBuilder();
sb.append("select * from ")
.append("(")
.append("select rownum rn, a.user_id, a.user_name, a.password, a.contact_tel, a.email, a.create_date from ")
.append("(")
.append("select user_id, user_name, password, contact_tel, email, create_date ")
.append("from t_user where user_name order by user_id")
.append(") a where rownum <= ? ")
.append(") where rn > ? ");

String sql = sb.toString();
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
List<User> user_list = new ArrayList<User>();

try {
conn = DbUtil.getConnection();
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, pageNo * pageSize);
pstmt.setInt(2, (pageNo-1) * pageSize);
System.out.println("sql=" + sql);
rs = pstmt.executeQuery();
while(rs.next()) {
User user = new User();
user.setUserId(rs.getString("user_id"));
user.setUserName(rs.getString("user_name"));
user.setPassword(rs.getString("password"));
user.setContactTel(rs.getString("contact_tel"));
user.setEmail(rs.getString("email"));
user.setCreateDate(rs.getTimestamp("create_date"));
user_list.add(user);
}
}catch (Exception e) {
e.printStackTrace();
}finally {
DbUtil.close(rs);
DbUtil.close(pstmt);
DbUtil.close(conn);
}
return user_list;
}

Answer 1

分页的示例：
分页

SELECT * FROM (
SELECT * FROM (
SELECT name, population,
rownum AS rn
FROM cities
WHERE state='Florida'
ORDER BY population DESC
) WHERE rownum <= 20
) WHERE rn > 10;

Answer 2

append("from t_user where user_name order by user_id")

里面这个user_name是什么意思？

追问

是用户名，根据用户名排序啊，分页查询是没有问题的，我在oracle中试验过

追答

没看懂，既然是排序，怎么会在where 后面呢，不应该是咋order by 后面