结合servlet和jsp实现分页操作
在dao中提供方法
package dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import bean.Hero;
public class HeroDAO {
public int getTotal() {
int total = 0;
try {
Class.forName("com.mysql.jdbc.Driver");
Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8",
"root", "admin");
Statement s = c.createStatement();
String sql = "select count(*) from hero";
ResultSet rs = s.executeQuery(sql);
while (rs.next()) {
total = rs.getInt(1);
}
System.out.println("total:" + total);
s.close();
c.close();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return total;
}
public void add(Hero hero) {
try {
Class.forName("com.mysql.jdbc.Driver");
Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8",
"root", "admin");
String sql = "insert into hero values(null,?,?,?)";
PreparedStatement ps = c.prepareStatement(sql);
ps.setString(1, hero.name);
ps.setFloat(2, hero.hp);
ps.setInt(3, hero.damage);
ps.execute();
ResultSet rs = ps.getGeneratedKeys();
if (rs.next()) {
int id = rs.getInt(1);
hero.id = id;
}
ps.close();
c.close();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public void update(Hero hero) {
try {
Class.forName("com.mysql.jdbc.Driver");
Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8",
"root", "admin");
String sql = "update hero set name= ?, hp = ? , damage = ? where id = ?";
PreparedStatement ps = c.prepareStatement(sql);
ps.setString(1, hero.name);
ps.setFloat(2, hero.hp);
ps.setInt(3, hero.damage);
ps.setInt(4, hero.id);
ps.execute();
ps.close();
c.close();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public void delete(int id) {
try {
Class.forName("com.mysql.jdbc.Driver");
Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8",
"root", "admin");
Statement s = c.createStatement();
String sql = "delete from hero where id = " + id;
s.execute(sql);
s.close();
c.close();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public Hero get(int id) {
Hero hero = null;
try {
Class.forName("com.mysql.jdbc.Driver");
Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8",
"root", "admin");
Statement s = c.createStatement();
String sql = "select * from hero where id = " + id;
ResultSet rs = s.executeQuery(sql);
if (rs.next()) {
hero = new Hero();
String name = rs.getString(2);
float hp = rs.getFloat("hp");
int damage = rs.getInt(4);
hero.name = name;
hero.hp = hp;
hero.damage = damage;
hero.id = id;
}
s.close();
c.close();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return hero;
}
public List<Hero> list() {
return list(0, Short.MAX_VALUE);
}
public List<Hero> list(int start, int count) {
List<Hero> heros = new ArrayList<Hero>();
try {
Class.forName("com.mysql.jdbc.Driver");
Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8",
"root", "admin");
String sql = "select * from hero order by id desc limit ?,? ";
PreparedStatement ps = c.prepareStatement(sql);
ps.setInt(1, start);
ps.setInt(2, count);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
Hero hero = new Hero();
int id = rs.getInt(1);
String name = rs.getString(2);
float hp = rs.getFloat("hp");
int damage = rs.getInt(4);
hero.id = id;
hero.name = name;
hero.hp = hp;
hero.damage = damage;
heros.add(hero);
}
ps.close();
c.close();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return heros;
}
}
编写HeroListServlet.java代码
package servlet;
import java.util.List;
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import bean.Hero;
import dao.HeroDAO;
/**
* Servlet implementation class HeroListServlet
*/
@WebServlet("/HeroListServlet")
public class HeroListServlet extends HttpServlet {
protected void service(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setContentType("text/html; charset=UTF-8");
int start = 0;
int count = 5;
try {
start = Integer.parseInt(request.getParameter("start"));
} catch (NumberFormatException e) {
// 当浏览器没有传参数start时
}
int next = start + count;
List<Hero> heros = new HeroDAO().list(start, count);
request.setAttribute("next", next);
request.setAttribute("heros", heros);
request.getRequestDispatcher("listHero.jsp").forward(request, response);
}
}
编写HeroListServlet.java代码
package servlet;
import java.util.List;
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import bean.Hero;
import dao.HeroDAO;
/**
* Servlet implementation class HeroListServlet
*/
@WebServlet("/HeroListServlet")
public class HeroListServlet extends HttpServlet {
protected void service(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setContentType("text/html; charset=UTF-8");
int start = 0;
int count = 5;
try {
start = Integer.parseInt(request.getParameter("start"));
} catch (NumberFormatException e) {
// 当浏览器没有传参数start时
}
int next = start + count;
List<Hero> heros = new HeroDAO().list(start, count);
request.setAttribute("next", next);
request.setAttribute("heros", heros);
request.getRequestDispatcher("listHero.jsp").forward(request, response);
}
}
修改list.jsp实现下一页分页操作
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8" import="java.util.*"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<table align='center' border='1' cellspacing='0'>
<tr>
<td>id</td>
<td>name</td>
<td>hp</td>
<td>damage</td>
<td>edit</td>
<td>delete</td>
</tr>
<c:forEach items="${heros}" var="hero" varStatus="st">
<tr>
<td>${hero.id}</td>
<td>${hero.name}</td>
<td>${hero.hp}</td>
<td>${hero.damage}</td>
<td><a href="editHero?id=${hero.id}">edit</a></td>
<td><a href="deleteHero?id=${hero.id}">delete</a></td>
</tr>
</c:forEach>
<tr>
<td colspan="6" align="center">
<a href="?start=${next}">[下一页]</a>
</td>
</tr>
</table>
运行结果
同理上一页操作也是一样
在其中加入首页与尾页操作
套上bootstrap结果样式