sql语句实现分页技术

下面是三层嵌套的sql语句,使用其可以实现web前端的分页效果,要注意书写格式

select user_id, user_name, password, contact_tel, email, create_date from( select rownum rn, user_id, user_name, password, contact_tel, email, create_date from ( select user_id, user_name, password, contact_tel, email, create_date from t_user where user_id <> 'root' order by user_id ) where rownum <= 4 ) where rn > 1

示例:

/** * 分页查询 * @param pageNo 第几页 * @param pageSize 每页多少条数据 * @return pageModel */ public PageModel<User> findUserList(int pageNo, int pageSize){ StringBuffer sbSql = new StringBuffer(); sbSql.append("select user_id, user_name, password, contact_tel, email, create_date ") .append("from ") .append("( ") .append("select rownum rn, user_id, user_name, password, contact_tel, email, create_date ") .append("from ") .append("( ") .append("select user_id, user_name, password, contact_tel, email, create_date from t_user where user_id <> 'root' order by user_id ") .append(") where rownum <= ? ") .append(") where rn > ? "); Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; PageModel<User> pageModel = null; try{ conn = DbUtil.getConnection(); pstmt = conn.prepareStatement(sbSql.toString()); pstmt.setInt(1, pageNo * pageSize); pstmt.setInt(2, (pageNo - 1) * pageSize); rs = pstmt.executeQuery(); List<User> userList = new ArrayList<User>(); 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")); userList.add(user); } pageModel = new PageModel<User>(); pageModel.setList(userList); pageModel.setTotalRecords(getTotalRecords(conn)); pageModel.setPageSize(pageSize); pageModel.setPageNo(pageNo); }catch(SQLException e){ e.printStackTrace(); }finally{ DbUtil.close(rs); DbUtil.close(pstmt); DbUtil.close(conn); } return pageModel; }

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值