利用Mysql创建一个表Car
用Eclipse创建一个Dynamic Web Project
在lib目录下导入Mysql的jar包
创建如下文件
package com.bean; public class Car { private String code; private String name; private String brand; private String time; private double oil; private int powers; private int exhaust; private double price; private String image; public String getCode() { return code; } public void setCode(String code) { this.code = code; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getBrand() { return brand; } public void setBrand(String brand) { this.brand = brand; } public String getTime() { return time; } public void setTime(String time) { this.time = time; } public double getOil() { return oil; } public void setOil(double oil) { this.oil = oil; } public int getPowers() { return powers; } public void setPowers(int powers) { this.powers = powers; } public int getExhaust() { return exhaust; } public void setExhaust(int exhaust) { this.exhaust = exhaust; } public double getPrice() { return price; } public void setPrice(double price) { this.price = price; } public String getImage() { return image; } public void setImage(String image) { this.image = image; } }
package com.dao; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import com.bean.Car; public class CarDao { private Connection conn; private PreparedStatement stat; private ResultSet rs; //获取总页数 public int getPageCount(int pageSize) throws Exception{ conn= DBConnection.getconnection(); String sql= "select count(*) from car"; stat=conn.prepareStatement(sql); rs= stat.executeQuery(); rs.next(); int rowCount = rs.getInt(1); int pageCount = (int)Math.ceil((1.0*rowCount/pageSize)); conn.close(); return pageCount; } //获取第几页的数据 public ArrayList<Car> getPageCar(int pageNo,int pageSize){ ArrayList<Car> list = new ArrayList<Car>(); try { conn=DBConnection.getconnection(); String sql="select * from car limit ?,?"; stat=conn.prepareStatement(sql); stat.setInt(1, (pageNo-1)*pageSize); stat.setInt(2, pageSize); rs=stat.executeQuery(); while(rs.next()){ Car c = new Car(); c.setCode(rs.getString(1)); c.setName(rs.getString(2)); c.setBrand(rs.getString(3)); c.setTime(rs.getString(4)); c.setOil(rs.getDouble(5)); c.setPowers(rs.getInt(6)); c.setExhaust(rs.getInt(7)); c.setPrice(rs.getDouble(8)); c.setImage(rs.getString(9)); list.add(c); } } catch (Exception e) { // TODO 自动生成的 catch 块 e.printStackTrace(); } finally{ try { conn.close(); } catch (SQLException e) { // TODO 自动生成的 catch 块 e.printStackTrace(); } } return list; } public ArrayList<Car> select(){ ArrayList<Car> list = new ArrayList<Car>(); try { conn=DBConnection.getconnection(); String sql= "select * from car"; stat=conn.prepareStatement(sql); rs = stat.executeQuery(); while(rs.next()){ Car c = new Car(); c.setCode(rs.getString(1)); c.setName(rs.getString(2)); c.setBrand(rs.getString(3)); c.setTime(rs.getString(4)); c.setOil(rs.getDouble(5)); c.setPowers(rs.getInt(6)); c.setExhaust(rs.getInt(7)); c.setPrice(rs.getDouble(8)); c.setImage(rs.getString(9)); list.add(c); } } catch (Exception e) { // TODO 自动生成的 catch 块 e.printStackTrace(); } finally{ try { conn.close(); } catch (SQLException e) { // TODO 自动生成的 catch 块 e.printStackTrace(); } } return list; } public Car select(String key){ Car c = null; try { conn= DBConnection.getconnection(); String sql = "select * from car where code=?"; stat= conn.prepareStatement(sql); stat.setString(1, key); rs= stat.executeQuery(); while(rs.next()){ c = new Car(); c.setCode(rs.getString(1)); c.setName(rs.getString(2)); c.setBrand(rs.getString(3)); c.setTime(rs.getString(4)); c.setOil(rs.getDouble(5)); c.setPowers(rs.getInt(6)); c.setExhaust(rs.getInt(7)); c.setPrice(rs.getDouble(8)); c.setImage(rs.getString(9)); } } catch (Exception e) { // TODO 自动生成的 catch 块 e.printStackTrace(); } return c; } }
package com.dao; import java.sql.Connection; import java.sql.DriverManager; import java.util.ResourceBundle; public class DBConnection { public static Connection getconnection() throws Exception{ String drv = ResourceBundle.getBundle("db").getString("drv"); String url = ResourceBundle.getBundle("db").getString("url"); String uid = ResourceBundle.getBundle("db").getString("uid"); String pwd = ResourceBundle.getBundle("db").getString("pwd"); Class.forName(drv); Connection conn = DriverManager.getConnection(url,uid,pwd); return conn; } }
db.properties文件是保护文件，里面要写用户密码。如下：
drv=com.mysql.jdbc.Driver url=jdbc:mysql://127.0.0.1:3306/mydb?characterEncoding=GBK uid=root pwd=
com.servlet包下的文件都是servlet类型的
package com.servlet; import java.io.IOException; import java.util.ArrayList; 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 com.bean.Car; import com.dao.CarDao; /** * Servlet implementation class Showservlet */ @WebServlet("/show") public class Showservlet extends HttpServlet { private static final long serialVersionUID = 1L; private final int PAGESIZE =3; /** * @see HttpServlet#HttpServlet() */ public Showservlet() { super(); // TODO Auto-generated constructor stub } /** * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response) */ protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { //获取数据 int pageNo = 1; String s = request.getParameter("pg"); if(s !=null){ pageNo=Integer.parseInt(s); } //处理数据 try { int pageCount = new CarDao().getPageCount(PAGESIZE); ArrayList<Car> list = new CarDao().getPageCar(pageNo, PAGESIZE); int pagePrev = pageNo>1?pageNo-1:1; int pageNext = pageNo<pageCount?pageNo+1:pageCount; request.setAttribute("pageNow", pageNo); request.setAttribute("pagePrev", pagePrev); request.setAttribute("pageNext", pageNext); request.setAttribute("pageCount", pageCount); request.setAttribute("cars",list); } catch (Exception e) { // TODO 自动生成的 catch 块 e.printStackTrace(); } //跳转 request.getRequestDispatcher("index.jsp").forward(request, response); } /** * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response) */ protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // TODO Auto-generated method stub doGet(request, response); } }
index.jsp内容如下：
<%@page import="com.bean.Car"%> <%@page import="java.util.ArrayList"%> <%@ page language="java" contentType="text/html; charset=utf-8" pageEncoding="utf-8"%> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <title>Insert title here</title> <script> function a(){ document.getElementById("ff").submit(); } </script> </head> <body> <h1>显示分页</h1> <% ArrayList<Car> list =(ArrayList<Car>)request.getAttribute("cars"); for(Car c : list){ %> <div> <span style="100px; display:inline-block;"><%=c.getCode() %></span> <span style="300px; display:inline-block;"><%=c.getName() %></span> <span style="100px; display:inline-block;"><%=c.getOil() %></span> </div> <% } %> <%--分页内容 --%> <a href="show?pg=1">首页</a> <a href="show?pg=<%=request.getAttribute("pagePrev") %>">上一页</a> <% int pageCount = (int)request.getAttribute("pageCount"); for(int i=1; i<=pageCount;i++){ %> <a href="show?pg=<%=i %>"><%=i %></a> <% } %> <a href="show?pg=<%=request.getAttribute("pageNext") %>">下一页</a> <a href="show?pg=<%=pageCount%>">末页</a> <form id="ff" method="get" action="show"> <select name="pg" onchange="a()"> <% for(int i=1;i<=pageCount;i++){ int p = (int)request.getAttribute("pageNow"); if(p==i){ %> <option value="<%=i %>" selected="selected"><%=i %></option> <% } else{ %> <option value="<%=i %>" ><%=i %></option> <% } } %> </select> <%-- <input type="submit" value="GO"/>--%> </form> </body> </html>
显示如下：
