建立数据库
数据库 HotelDB
表 Users
列名 | 类型 | 属性1 | 属性2 | 描述 |
---|---|---|---|---|
id | int | 主键 | 自增 | 编号 |
name | varchar | 20 | 非空 | 姓名 |
tel | varchar | 11 | 非空 | 手机号 |
cardId | varchar | 18 | 非空 | 身份证号 |
要求
创建54条用户数据
只做查询
数据库文件
DROP DATABASE IF EXISTS HotelDB;
CREATE DATABASE HotelDB;
USE HotelDB;
CREATE TABLE Users
(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20) NOT NULL,
tel VARCHAR(11) NOT NULL,
cardId VARCHAR(18) NOT NULL
);
INSERT INTO Users VALUES(NULL,'张1','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张2','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张3','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张4','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张5','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张6','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张7','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张8','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张9','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张10','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张11','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张12','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张13','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张14','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张15','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张16','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张17','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张18','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张19','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张20','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张21','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张22','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张23','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张24','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张25','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张26','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张27','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张28','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张29','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张30','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张31','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张32','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张33','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张34','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张35','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张36','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张37','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张38','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张39','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张40','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张41','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张42','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张43','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张44','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张45','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张46','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张47','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张48','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张49','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张50','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张51','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张52','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张53','13000000000','123456789012345678');
INSERT INTO Users VALUES(NULL,'张54','13000000000','123456789012345678');
SELECT * FROM Users;
#第1页
SELECT * FROM Users LIMIT 0,5;
#第2页
SELECT * FROM Users LIMIT 5,5;
#第3页
SELECT * FROM Users LIMIT 10,5;
#第4页
SELECT * FROM Users LIMIT 15,5;
page
(page - 1) * 5
实体类User
public class Users {
private int id;
private String name;
private String tel;
private String cardId;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getTel() {
return tel;
}
public void setTel(String tel) {
this.tel = tel;
}
public String getCardId() {
return cardId;
}
public void setCardId(String cardId) {
this.cardId = cardId;
}
public Users() {
super();
}
public Users(int id, String name, String tel, String cardId) {
super();
this.id = id;
this.name = name;
this.tel = tel;
this.cardId = cardId;
}
}
基本工具类
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class BaseDao {
private String driver="com.mysql.jdbc.Driver";
private String url="jdbc:mysql://localhost:3306/HotelDB";
private String name="root";
private String pwd="123456";
public Connection getCon(){
Connection con = null;
try {
Class.forName(driver);
con = DriverManager.getConnection(url, name, pwd);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return con;
}
public void closeAll(Connection con, PreparedStatement ps, ResultSet rs){
try {
if (rs != null) {
rs.close();
}
if (ps != null) {
ps.close();
}
if (con != null) {
con.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
数据访问类
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
public class UsersDao extends BaseDao {
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
public ArrayList<Users> getUsers(int page){
ArrayList<Users> list = new ArrayList<Users>();
String sql = "select * from Users limit ?,5";
try {
con = this.getCon();
ps = con.prepareStatement(sql);
ps.setInt(1, (page-1)*5);
rs = ps.executeQuery();
while(rs.next()){
Users user = new Users(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4));
list.add(user);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
this.closeAll(con, ps, rs);
}
return list;
}
public int getAllPage(){
int allPage = 0;
String sql = "select count(id) from Users";
try {
con = this.getCon();
ps = con.prepareStatement(sql);
rs = ps.executeQuery();
if(rs.next()){
int count = rs.getInt(1);
allPage = count / 5;
if (count % 5 != 0) {
allPage++;
}
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
this.closeAll(con, ps, rs);
}
return allPage;
}
}
index.jsp
<%@ page language="java" import="java.util.*, dao.*" pageEncoding="utf-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
UsersDao ud = new UsersDao();
//获取总页数
int allPage = ud.getAllPage();
//获取当前页
int currentPage = 1;
if(request.getParameter("page") != null){
currentPage = Integer.parseInt(request.getParameter("page"));
}
//获取上一页
int prePage = currentPage - 1;
if(prePage == 0){
prePage = 1;
}
//获取下一页
int nextPage = currentPage + 1;
if(nextPage > allPage){
nextPage = allPage;
}
ArrayList<Users> list = ud.getUsers(currentPage);
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>">
<title>My JSP 'index.jsp' starting page</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">
<!--
<link rel="stylesheet" type="text/css" href="styles.css">
-->
<script type="text/javascript">
function selectPage(){
var page = document.getElementById("slt").value;
location.href="index.jsp?page="+page;
}
function txtPage(allPage){
var page = document.getElementById("txt").value;
if(page == ""){
alert("页码不能为空");
} else if(isNaN(page) == true){
alert("页码必须是纯数字");
} else if(page < 1 || page > allPage) {
alert("页码超出范围");
} else {
location.href="index.jsp?page="+page;
}
}
</script>
</head>
<body>
<table border="1" width="600px">
<tr>
<th>编号</th>
<th>姓名</th>
<th>手机号</th>
<th>身份证号</th>
</tr>
<%for(Users user : list){ %>
<tr>
<td><%=user.getId() %></td>
<td><%=user.getName() %></td>
<td><%=user.getTel() %></td>
<td><%=user.getCardId() %></td>
</tr>
<%} %>
</table>
<a href="index.jsp?page=1">首页</a>
<a href="index.jsp?page=<%=prePage %>">上一页</a>
<%if(currentPage <= 3){
for(int i = 1; i <= currentPage + 2; i++){
%>
<a href="index.jsp?page=<%=i %>"><%=i %></a>
<%
}
%>...<%
} else if(currentPage >= allPage - 2){
%>...<%
for(int i = currentPage - 2; i <= allPage; i++){
%>
<a href="index.jsp?page=<%=i %>"><%=i %></a>
<%
}
} else {
%>...<%
for(int i = currentPage - 2; i <= currentPage + 2; i++){
%>
<a href="index.jsp?page=<%=i %>"><%=i %></a>
<%
}
%>...<%
}
%>
<a href="index.jsp?page=<%=nextPage %>">下一页</a>
<a href="index.jsp?page=<%=allPage %>">尾页</a>
<br/>
总页数:<%=currentPage %>/<%=allPage %>页
<select onchange="selectPage()" id="slt">
<%for(int i = 1; i <= allPage; i++){ %>
<option value="<%=i %>"
<%if(i == currentPage){%>
selected="selected"
<%} %>
><%=i %></option>
<%} %>
</select>
<input id="txt" style="width:40px" />
<input type="button" value="Go" onclick="txtPage(<%=allPage %>)"/>
</body>
</html>
多种分页方式不会一起全部使用,但是会组合使用,多敲代码,一定要多敲代码。。