数据库分页

MySQL中分页

SELECT * FROM users LIMIT [start],[step]
例如：limit2,5 //取3 4 5 6 7 start是索引（从0开始）

public List<UserBean> getData(int fromIndex,int count){
		List<UserBean> userList = new ArrayList<UserBean>();
		Connection conn = Database.getConnection();
		PreparedStatement pstmt = null;
		String sql = "select name,password,email from users limit ?,?";
		ResultSet rs = null;
		try {
			pstmt = (PreparedStatement) conn.prepareStatement(sql);
			pstmt.setInt(1, fromIndex);
			pstmt.setInt(2, count);
			rs = pstmt.executeQuery();
			while(rs.next()) {
				UserBean userbean = new UserBean();
				userbean.setUserName(rs.getString("name"));
				userbean.setPassword(rs.getString("password"));
				userbean.setEmail(rs.getString("email"));
				userList.add(userbean);
			}
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return userList;
	}

public int getUserCount(){
		int userCount = 0;
		Connection conn = Database.getConnection();
		PreparedStatement pstmt = null;
		String sql = "select count(*) from users";
		ResultSet rs = null;
		try {
			pstmt = (PreparedStatement) conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while(rs.next()) {
				userCount++;
			}
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return userCount;
	}

		String currentPageStr = request.getParameter("currentPage");
		int currentPage = 1;
		if(currentPageStr != null && !currentPageStr.equals("")) {
			currentPage = Integer.parseInt(currentPageStr);
		}
		 Page page = new Page();
		 UserDao userDao = new UserDao();
		 int columnCount = userDao.getUserCount();
		 page.setColumnCount(columnCount);
		 
		 int fromIndex = (currentPage-1) * page.getCount();
		 int mod = page.getColumnCount()%page.getCount();
		 boolean b = (fromIndex+10)>page.getColumnCount();
		 int count = b?(mod):(10);
		 List<UserBean> subUserList = new ArrayList<UserBean>();
		 userDao.getData(fromIndex,count);

获取全部内容后分页

• 获取全部数据

public List<UserBean> getAllData(){
		List<UserBean> userList = new ArrayList<UserBean>();
		Connection conn = Database.getConnection();
		PreparedStatement pstmt = null;
		String sql = "select name,password,email from users";
		ResultSet rs = null;
		try {
			pstmt = (PreparedStatement) conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while(rs.next()) {
				UserBean userbean = new UserBean();
				userbean.setUserName(rs.getString("name"));
				userbean.setPassword(rs.getString("password"));
				userbean.setEmail(rs.getString("email"));
				userList.add(userbean);
			}
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return userList;
	}

• 处理数据

		//获取当前页
		String currentPagestr = request.getParameter("currentPage");
		int currentPage = 1;
		if(currentPagestr != null && !currentPagestr.equals("")) {
			currentPage = Integer.parseInt(currentPagestr);
		}
		//获取数据
		HttpSession session = request.getSession();
		List<UserBean> userList = null;
		if(session.getAttribute("userList") == null) {
			userList = userdao.getAllData();
		}else {
			userList = (List<UserBean>) session.getAttribute("userList");
		}	
		session.setAttribute("userList", userList);
		//对当前页进行设置
		Page page = new Page();
		page.setColumnCount(userList.size());
		page.setCurrentPage(currentPage);
		//子列表
		List<UserBean> subUserList = new ArrayList<UserBean>();
		int fromIndex = (currentPage-1) * page.getCount();
		int yushu = page.getColumnCount()%page.getCount() ;
		int toIndex = (currentPage < page.getPageCount())?(fromIndex + page.getCount()):(fromIndex + yushu);
		subUserList = userList.subList(fromIndex, toIndex);