利用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="width:100px; display:inline-block;"><%=c.getCode() %></span>
<span style="width:300px; display:inline-block;"><%=c.getName() %></span>
<span style="width: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>
显示如下：