1.首先创建数据库和环境搭建
2.创建javabean类
public class Customer {
public static final int page_size=3;
private String cid;
private String cname;
private String gender;
private String birthday;
private String tel;
private String des;
public String getCid() {
return cid;
}
public void setCid(String cid) {
this.cid = cid;
}
public String getCname() {
return cname;
}
public void setCname(String cname) {
this.cname = cname;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public String getBirthday() {
return birthday;
}
public void setBirthday(String birthday) {
this.birthday = birthday;
}
public String getTel() {
return tel;
}
public void setTel(String tel) {
this.tel = tel;
}
public String getDes() {
return des;
}
public void setDes(String des) {
this.des = des;
}
}
3.dao层编写连接池,及配置c3p0.xml
public class JDBCUtil {
private static ComboPooledDataSource datasource=new ComboPooledDataSource("mysql_c3p0_config");
public static Connection getConnection(){
try {
return datasource.getConnection();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
public static DataSource getPool(){
return datasource;
}
}
<named-config name="mysql_c3p0_config">
<!-- 指定连接数据源的基本属性 -->
<property name="user">root</property>
<property name="password">1234</property>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/customermanger</property>
<!-- 若数据库中连接数不足时, 一次向数据库服务器申请多少个连接 -->
<property name="acquireIncrement">5</property>
<!-- 初始化数据库连接池时连接的数量 -->
<property name="initialPoolSize">5</property>
<!-- 数据库连接池中的最小的数据库连接数 -->
<property name="minPoolSize">5</property>
<!-- 数据库连接池中的最大的数据库连接数 -->
<property name="maxPoolSize">10</property>
<!-- C3P0 数据库连接池可以维护的 Statement 的个数 -->
<property name="maxStatements">20</property>
<!-- 每个连接同时可以使用的 Statement 对象的个数 -->
<property name="maxStatementsPerConnection">5</property>
</named-config>
4.CustomerDao对数据库进行查询,获取数据
public class CustomerDao {
public List <Customer> find(int page){
List <Customer> list=new ArrayList<Customer>();
Connection con=JDBCUtil.getConnection();
PreparedStatement pre=null;
ResultSet rs=null;
String sql="select * from customer limit ?,? ";
try {
pre=con.prepareStatement(sql);
pre.setInt(1, (page-1)*Customer.page_size);
pre.setInt(2, Customer.page_size);
rs=pre.executeQuery();
while(rs.next()){
Customer cu=new Customer();
cu.setCid( rs.getString(1));
cu.setCname( rs.getString(2));
cu.setGender(rs.getString(3));
cu.setBirthday(rs.getString(4));
cu.setTel(rs.getString(5));
cu.setDes(rs.getString(6));
list.add(cu);
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
if(rs!=null)rs.close();
if(pre!=null)pre.close();
if(con!=null)con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
for(Customer cu:list){
System.out.println(cu.toString());
}
return list;
}
public int findCount(){
int count=0;
Connection con=JDBCUtil.getConnection();
PreparedStatement pre=null;
ResultSet rs=null;
String sql="select count(*) from customer";
try {
pre=con.prepareStatement(sql);
rs=pre.executeQuery();
while(rs.next()){
count= rs.getInt(1);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
try {
if(rs!=null)rs.close();
if(pre!=null)pre.close();
if(con!=null)con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return count;
}
}
5.显示页面jsp
<html>
<head>
<base href="<%=basePath%>">
<title>顾客信息查询</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
</head>
<body>
<table align="center" width="700" border="1">
<tr align="center">
<td>id</td>
<td>姓名</td>
<td>性别</td>
<td>生日</td>
<td>电话</td>
<td>留言</td>
</tr>
<% List<Customer> list= (List<Customer>) request.getAttribute("list") ;
for(Customer cu:list){
%>
<tr align="center">
<td><%=cu.getCid()%></td>
<td><%=cu.getCname() %></td>
<td><%=cu.getGender() %></td>
<td><%= cu.getBirthday()%></td>
<td><%= cu.getTel()%></td>
<td><%=cu.getDes() %></td>
</tr>
<%
}
%>
<td align="center" colspan="5">
<%=request.getAttribute("bar") %>>
</td>
</table>
</body>
</html>
6.servlet分页将数据转发给jsp
public class CustomerServlet extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
int currpage=1;
if(req.getParameter("page")!=null){
currpage=Integer.parseInt(req.getParameter("page"));
}
CustomerDao dao=new CustomerDao();
List<Customer> list=dao.find(currpage);
req.setAttribute("list", list);
int pages;
int count=dao.findCount();
if(count%Customer.page_size==0){
pages=count/Customer.page_size;
}else{
pages=count/Customer.page_size+1;
}
StringBuffer sb=new StringBuffer();
for(int i=1;i<=pages;i++){
if(i==currpage){
sb.append("【"+i+"】");
}else{
sb.append("<a href='/CustomerManager/servlet?page="+i+"'>"+i+"</a>");
}
sb.append(" ");
}
req.setAttribute("bar", sb.toString());
req.getRequestDispatcher("/jsp/list.jsp").forward(req, resp);
}
}
别忘了配置servlet
<web-app version="3.0"
xmlns="http://java.sun.com/xml/ns/javaee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd">
<servlet>
<servlet-name>CustomerServlet</servlet-name>
<servlet-class>cn.lvle.servlet.CustomerServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>CustomerServlet</servlet-name>
<url-pattern>/servlet</url-pattern>
</servlet-mapping>
</web-app>
最后来看看运行效果