首页 > 程序开发 > Web开发 > Jsp >

jsp+jdbc+MySQL分页

2014-09-16

MySQL自带的limit方式可以完成真分页。具体结构和代码如下: index jsp My JSP & 39;index jsp& 39; starting page

MySQL自带的limit方式可以完成真分页。具体结构和代码如下:

\


index.jsp

<喎"http://www.2cto.com/kf/ware/vc/" target="_blank" class="keylink">vcD4KPHByZSBjbGFzcz0="brush:java;"><%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%> <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %> <% String path = request.getContextPath(); String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/"; %> My JSP &#39;index.jsp&#39; starting page

查看所有成员信息

Person.java

package com.ibm.pojo;

public class Person {
	/**
	 * Person类属性:id,姓名,年龄
	 */
	private int id;
	private String name;
	private int age;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public int getAge() {
		return age;
	}
	public void setAge(int age) {
		this.age = age;
	}
}
PageDao.java

package com.ibm.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.ibm.pojo.Person;
import com.ibm.util.DBConn;
//分页Dao
public class PageDao {
	private int pageSize = 5;// 每页记录数
	//getter setter
	public int getPageSize() {
		return pageSize;
	}
	public void setPageSize(int pageSize) {
		this.pageSize = pageSize;
	}
	public int getPages(){
		/**
		 * SQL语句计算查询的总记录数
		 * @return 查询person的总页数
		 */
		int totalPages = 0;//总页数totalPages
		DBConn conn = new DBConn();
		String sql = "select * from person";
		ResultSet rs = conn.executeQuery(sql);
		try {
			while(rs.next()){
				int totalRecords = rs.getInt("id");//总记录数totalRecords
				totalPages=totalRecords%pageSize==0?totalRecords/pageSize:totalRecords/pageSize+1;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return totalPages;
	}
	
	public List show(int currentPage){
		/**
		 * @return 返回当前页查询结果的List
		 */
		DBConn conn = new DBConn();
		Person p;
		int beginRecord = (currentPage-1)*pageSize;//开始记录
		int endRecord = pageSize;//从开始到结束的记录数
		String sql = "select * from person limit "+beginRecord+","+endRecord+"";
		List personList = new ArrayList();
		ResultSet resultSet = conn.executeQuery(sql);
		try {//resultSet的get方法赋值得到一个Person对象
			while(resultSet.next()){
				p = new Person();
				p.setId(resultSet.getInt("id"));
				p.setName(resultSet.getString("name"));
				p.setAge(resultSet.getInt("age"));
				System.out.println("age:"+p.getAge());
				System.out.println("name:"+p.getName());
				System.out.println("id:"+p.getId());
				personList.add(p);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			conn.close();
		}
		return personList;
	}
}

ShowPerson.java

package com.ibm.servlet;

import java.io.IOException;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.ibm.dao.PageDao;
import com.ibm.pojo.Person;

public class ShowPerson extends HttpServlet {
	/**
	 * 分页显示Person的servlet
	 */
	private static final long serialVersionUID = 1L;

	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		PageDao dao = new PageDao();
		/**
		 * personList由PageDao的show方法获得:返回一个Person类型的List,在jsp上foreach即可。
		 * currentPage由页面参数和隐藏域获得:主要操作页面之间的跳转,在地址中?方式传值。action中使用隐藏域,初始值通过getParameter方式获得。
		 * totalPages由pageDao的getPages方法获得:判断尾页,则下一页超链接不显示。
		 */
		int currentPage = Integer.parseInt(request.getParameter("currentPage"));
		List personList = dao.show(currentPage);
		request.setAttribute("personList", personList);
		request.setAttribute("currentPage",currentPage);
		request.setAttribute("totalPages", dao.getPages());
		request.getRequestDispatcher("show.jsp").forward(request, response);
	}

	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		doGet(request,response);
	}
}

show.jsp

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>



  
    
    My JSP &#39;index.jsp&#39; starting page
	
	
	    
	
	
  
  
  
  <script type="text/javascript">
  function jump(){
  page = document.getElementById("page").value;
  if(page<0||page>${totalPages}){
  	return;
  }else
  	location.href="/page/ShowPerson?currentPage="+page;
  }
  </script>
    
    	
id name age
${row.id} ${row.name} ${row.age}
首页 上一页 第${currentPage}页 下一页 尾页 到第

Dbconn.java

package com.ibm.util;
 

import java.sql.*;

public class DBConn {
	private Connection conn;
	private Statement stmt;

	public Connection getConn() {
		return conn;
	}

	public void setConn(Connection conn) {
		this.conn = conn;
	}

	public Statement getStmt() {
		return stmt;
	}

	public void setStmt(Statement stmt) {
		this.stmt = stmt;
	}

	public DBConn() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8", "root", "root");
			stmt = conn.createStatement();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public ResultSet executeQuery(String sql) {
		try {
			ResultSet rs = stmt.executeQuery(sql);
			return rs;
		} catch (SQLException e) {

			e.printStackTrace();
			return null;
		}
	}

	public boolean executeUpdate(String sql) {
		try {
			stmt.executeUpdate(sql);
		} catch (SQLException e) {

			e.printStackTrace();
		}
		return true;
	}

	public void close() {
		try {
			stmt.close();
			conn.close();
		} catch (SQLException e) {

			e.printStackTrace();
		}
	}
}



相关文章
最新文章
热点推荐