使用jdbc对mysql查询数据实现分页并且显示在前段页面。在开始前创建一个访问数据库的工具类DBUtil
/**
*数据库工具类
* @author 13468
*
*/
public class DBUtil {
private static DBUtil dbUtil=new DBUtil();
private DBUtil() {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException {
String url="jdbc:mysql://localhost:3306/booksys?useUnicode=true&characterEncoding=utf-8";
return DriverManager.getConnection(url,"root","root");
}
}
在dao层建立数据库jdbc访问层接口和访问接口的实现类访问接口,建立三个方法
//获得用户总数
int getUserCount();
//获取总页数
int getUserPage(int PageeSize);
//获取分页数据
List<UserInfo> getUsersPages(int currentPage,int pageSize);
访问接口实现,来实现上面接口的方法
/**
* 获取数据总条数
*/
@Override
public int getUserCount() {
Connection connection=null;
PreparedStatement preparedStatement=null;
ResultSet resultSet=null;
int count=0;
try {
connection=DBUtil.getConnection();
String sql="select count(*) from user";
preparedStatement=(PreparedStatement) connection.prepareStatement(sql);
resultSet=preparedStatement.executeQuery();
if(resultSet.next()) {
count=resultSet.getInt(1);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
if(resultSet!=null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(preparedStatement!=null) {
try {
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(connection!=null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return count;
}
/**
* 算出总页数
*/
@Override
public int getUserPage(int PageeSize) {
int count=getUserCount();//获取总条数
int pages=(count+PageeSize-1)/PageeSize;
return pages;
}
//获取分页数据
@Override
public List<UserInfo> getUsersPages(int currentPage, int pageSize) {
Connection connection=null;
PreparedStatement preparedStatement=null;
ResultSet resultSet=null;
List<UserInfo>list=new ArrayList<UserInfo>();
try {
connection=DBUtil.getConnection();
String sql="select * from user limit ?,?";
preparedStatement=(PreparedStatement) connection.prepareStatement(sql);
preparedStatement.setInt(1, (currentPage-1)*pageSize);
preparedStatement.setInt(2, pageSize);
resultSet=preparedStatement.executeQuery();
while (resultSet.next()) {
int id=resultSet.getInt(1);
String username=resultSet.getString(2);
String password=resultSet.getString(3);
String name=resultSet.getString(4);
String email=resultSet.getString(5);
String phone=resultSet.getString(6);
list.add(new UserInfo(id,username,password,name,email,phone));
}
}catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
if(resultSet!=null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(preparedStatement!=null) {
try {
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(connection!=null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return list;
}
service层省略,但是这个例子是创建了service层的,下面再创建控制层的servlet来调用上面的方法
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
/**
* 分页显示用户信息
*/
if (action.equals("showusers")) {
String cupage=request.getParameter("currentPage");
//获取总页数
int pages=service.getUserPage(pageSize);
//获取当前页码
currentPage=(cupage==null)?1:Integer.parseInt(cupage);
//如果当前页码小于1,则赋值为1,表示为首页
if(currentPage<1) {
currentPage=1;
}
//如果当前页数大于总页数,则赋值为总页数值,表示为尾页
if(currentPage>pages) {
currentPage=pages;
}
//将当前的页码保存在session中
request.getSession().setAttribute("currentPage", currentPage);
//将总页数保存在session中
request.getSession().setAttribute("pages", pages);
//准备分页的user数据
List<UserInfo> userInfo=service.getUsersPages(currentPage, pageSize);
//将user放入到session中
request.getSession().setAttribute("userInfo", userInfo);
//跳转到userman.jsp页面
response.sendRedirect("admin/userman.jsp");
}
}
通过前面的准备,现在在前段页面JSP中接收页码
<div id="page" class="page_div">
<a href="/BookSystem/UserServlet?action=showusers¤tPage=1" id="firstpage">首页</a>
<a href="/BookSystem/UserServlet?action=showusers¤tPage=${currentPage-1 }" id="prevpage">上一页</a>
<span>${currentPage }/${pages }</span>
<a href="/BookSystem/UserServlet?action=showusers¤tPage=${currentPage+1 }" id="nextpage">下一页</a>
<a href="/BookSystem/UserServlet?action=showusers¤tPage=${pages }" id="lastpage">末页</a>
</div>