先來看一看分頁的實現原理
萬能公式.jpg
項目目錄.PNG
首先,新建Java Web項目
一. 梳理業務邏輯
重定向到URL(跳轉到StudentViewAction頁面)//index.jsp頁面
1.從頁面接收可變的值
2.接收值有問題時,初始化為1
3.如果沒有問題,把String類型接收值強轉成Integer
4.實例DAO方法,調用findStudentListByPageCount()方法(該方法得到總條數)
5.計算總頁數:總頁數 = 總條數 % 頁容量
6.判斷接收到頁面傳來的值是否小于1頁
7.調用DAO中findStudentListByPageCount()(該方法獲取數據集合)
8.封裝打包頁面
9.轉發頁面
request.getRequestDispatcher("list.jsp").forward(request, response);
//request.getRequestDispatcher("list.jsp") 找到要轉發的頁面
//forward(request, response); 實現轉發
二. 實現界面展示
1.封裝工具類JDBCUtil.java文件, 作用是連接數據庫
package com.fyl.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
/**
* 文檔注釋(Java連接數據庫的工具類)
*
*/
public class JDBCUtil {
// 注意:四個屬性:驅動, 地址(URL), 用戶名, 密碼
// 驅動類:通過一個類名告訴java我現在使用的是什么數據庫
private static final String CONN_DRIVER = "com.mysql.jdbc.Driver";
// URL:告訴Java我的數據庫的具體位置(網絡標識:通過什么端口哪臺電腦獲取什么資源)
private static final String CONN_URL = "jdbc:mysql://127.0.0.1:3306/student_db?characterEncoding=UTF-8";
// 用戶名
private static final String CONN_USER_NAME = "root";
// 密碼
private static final String CONN_USER_PASS = "123456";
public static Connection getConn() {
// 創建方法的返回變量
Connection conn = null;
try {
// 1.加載驅動類 讓Java知道我們創建什么數據庫的實例
Class.forName(CONN_DRIVER);
// 通過已經加載好的驅動類給我們提供連接
conn = DriverManager.getConnection(CONN_URL, CONN_USER_NAME,
CONN_USER_PASS);
} catch (ClassNotFoundException e) {
System.out.println("add DriverManager error!");
e.printStackTrace();
} catch (SQLException e) {
System.out.println("SQL error!");
e.printStackTrace();
}
return conn;
}
public static void closeAll(ResultSet set, PreparedStatement ps,
Connection conn) {
try {
if (null != set) {
set.close();
}
if (null != ps) {
ps.close();
}
if (null != conn) {
conn.close();
}
} catch (SQLException e) {
System.out.println("closeAll ERROR!");
e.printStackTrace();
}
}
public static void main(String[] args) {
// 獲取數據庫連接
Connection conn = JDBCUtil.getConn();
Scanner scan = new Scanner(System.in);
while (true) {
System.out.println("請素輸入要查看的數據:");
int i = scan.nextInt();
int start = (i - 1) * 10;
int size = 10;
// 2.編寫SQL語句(查詢id > 0的數據, 連續查詢10條記錄)
String sql = "SELECT * FROM student WHERE s_id LIMIT ?,?";
// SELECT * FROM student WHERE s_id > 10 AND s_id <= (10 + 10)
// 3.運行SQL語句
try {
PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1, start);
ps.setInt(2, size);
// 4.執行后返回結果(execute執行Query結果)
ResultSet set = ps.executeQuery();
System.out.println("學生編號\t學生姓名\t學生年齡\t入學時間\t學費");
for (; set.next();) {
System.out.print(set.getInt("S_ID") + "\t");
System.out.print(set.getString("S_NAME") + "\t");
System.out.print(set.getInt("S_AGE") + "\t");
System.out.print(set.getDate("S_INTODATE") + "\t");
System.out.print(set.getDouble("S_MONEY") + "\t");
System.out.println();
}
} catch (SQLException e) {
System.out.println("select error");
e.printStackTrace();
}
}
}
}
2.創建數據庫實體類Student.java文件(Model層)
package com.fyl.entity;
import java.io.Serializable;
import java.util.Date;
/**
* 實體類
* @author Administrator
*
*/
public class Student implements Serializable {
private static final long serialVersionUID = 1L;//添加唯一標識
private Integer id;
private String name;
private int age;
private Date date;
private Double money;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public Date getDate() {
return date;
}
public void setDate(Date date) {
this.date = date;
}
public Double getMoney() {
return money;
}
public void setMoney(Double money) {
this.money = money;
}
}
3.index.jsp界面(呈現給用戶的第一個界面)
系統首頁// 重定向到URL
request.getRequestDispatcher("StudentViewAction").forward(request, response);
%>
4.新建servlet文件StudentViewAction.java(Controller層)
package com.fyl.web;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.fyl.dao.StudentDAO;
import com.fyl.dao.impl.StudentDAOImpl;
import com.fyl.entity.Student;
public class StudentViewAction extends HttpServlet {
private static final long serialVersionUID = 1L;
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doPost(request, response);
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//封裝數據給頁面
//整理頁面需要的數據
int pageIndex = 0;//頁面 //頁面每次請求傳過來的
int pageSize = 10;//
int totalCount = 0;
int totalPge = 0;
List list = null;
//從頁面接收可變的值
String pi = request.getParameter("pageIndex");
//pi有問題的時候,初始化為1
if (null == pi || "".equals(pi)) {
pi = "1";
}
//如果pi沒有問題的時候
pageIndex = Integer.parseInt(pi);
//從數據接收值
StudentDAO dao = new StudentDAOImpl();
//調用DAO方法
totalCount = dao.findStudentListByPageCount();
//計算總頁數
totalPge = totalCount % pageSize == 0?totalCount/pageSize:totalCount/pageSize + 1;
//判斷pageIndex的邊界值
if (pageIndex < 1) {
pageIndex = 1;
}
if (pageIndex > totalPge) {
pageIndex = totalPge;
}
//獲取數據集合
list = dao.findStudentListByPage(pageIndex, pageSize);
//封裝打包頁面
request.setAttribute("pageIndex", pageIndex);
request.setAttribute("pageSize", pageSize);
request.setAttribute("totalCount", totalCount);
request.setAttribute("totalPge", totalPge);
request.setAttribute("list", list);
//轉發頁面
request.getRequestDispatcher("list.jsp").forward(request, response);
}
}
新建list.jsp界面接收StudentViewAction傳來的值
數據展示function goUpdate(id){
window.location.href = "StudentFindByIDViewAction?id=" + id;
}
function goDelete(id){
var con = window.confirm("您確定刪除ID為" + id + "這條數據嗎?" );
if(con){
//刪除
window.location.href = "StudentDeleteAction?id=" + id;
}
}
function goPage(pageIndex){
window.location.href = "StudentViewAction?pageIndex="+pageIndex;
}
function goPage(pageIndex){
window.location.href = "StudentViewAction?pageIndex="+pageIndex;
}
function goAdd(){
window.location.href = "add.jsp";
}
一共查詢出${totalCount}條數據,每頁展示${pageSize}條,一共有${totalPage}頁,當前瀏覽的是第${pageIndex}頁 | |||||
|---|---|---|---|---|---|
| 學生ID | 學生姓名 | 學生年齡 | 入學時間 | 學費 | 操作 |
| ${s.id} | ${s.name} | ${s.age} | || | ||
三. 實現增刪查改
創建接口, 新建StudentDAO.java接口文件, 添加增刪查改方法
package com.fyl.dao;
import java.util.List;
import com.fyl.entity.Student;
public interface StudentDAO {
/**
* 更據id刪除
* @param id
* @return
* @throws RuntimeException
*/
public boolean deleteStudent(Integer id) throws RuntimeException;
/**
* 根據ID查詢單個學生對象
* @param id
* @return
* @throws RuntimeException
*/
public Student findStudentByID(Integer id) throws RuntimeException;
/*
* 添加學生方法
* @param student 要添加的學生
* @return 添加成功返回true 添加失敗返回false
* @throws RuntimeException
*/
public boolean insertStudent(Student student)throws RuntimeException;
/**
* 查詢數據庫的總條數
* @return 總條數
* @throws RuntimeException
*/
public int findStudentListByPageCount() throws RuntimeException;
/**
* 獲取分頁數集合
* @param pageIndex 頁碼
* @param pageSize 頁容量
* @return 已經分頁的list集合
* @throws RuntimeException
*/
public List findStudentListByPage(Integer pageIndex, Integer pageSize) throws RuntimeException;
/*
* 更新學生信息
* @param student
* @return
* @throws RuntimeException
*/
public boolean updateStudent(Student student) throws RuntimeException;
}
2.新建StudentDAOImpl.java文件,實現接口
package com.fyl.dao.impl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.fyl.dao.StudentDAO;
import com.fyl.entity.Student;
import com.fyl.util.JDBCUtil;
public class StudentDAOImpl implements StudentDAO {
// TODO
public int findStudentListByPageCount() throws RuntimeException {
// 1.創建方法的返回變量
int totalCount = 0;
// 3.獲取數據庫連接
Connection conn = JDBCUtil.getConn();
// 4.編寫SQL語句
String sql = "SELECT COUNT(S_ID) FROM STUDENT";
// 執行SQL語句
PreparedStatement ps = null;
ResultSet set = null;
try {
ps = conn.prepareStatement(sql);
set = ps.executeQuery();
//處理
if (set.next()) {
totalCount = set.getInt(1);
}
} catch (SQLException e) {
// TODO
e.printStackTrace();
} finally {
JDBCUtil.closeAll(set, ps, conn);
}
return totalCount;
}
// TODO
public List findStudentListByPage(Integer pageIndex,
Integer pageSize) throws RuntimeException {
List list = new ArrayList();
//2.1獲取數據庫連接
Connection conn = JDBCUtil.getConn();
//3. 創建SQL語句
String sql = "SELECT * FROM STUDENT WHERE S_ID LIMIT ?,?";
//4.執行SQL語句
PreparedStatement ps = null;
ResultSet set = null;
try {
ps = conn.prepareStatement(sql);
ps.setInt(1, (pageIndex-1) * pageSize);
ps.setInt(2, pageSize);
set = ps.executeQuery();
Student s = null;
while (set.next()) {
s = new Student();
//封裝數據
s.setId(set.getInt("S_ID"));
s.setName(set.getString("S_NAME"));
s.setAge(set.getInt("S_AGE"));
s.setMoney(set.getDouble("S_MONEY"));
s.setDate(set.getDate("S_INTODATE"));
// 將封裝好的Student對像裝入集合
list.add(s);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
JDBCUtil.closeAll(set, ps, conn);
}
return list;
}
public boolean insertStudent(Student student) throws RuntimeException {
// TODO Auto-generated method stub
//1.定義方法返回變量
boolean con = false;
//3. 獲取數據庫連接
Connection conn = JDBCUtil.getConn();
//4. 編寫SQL語句
String sql = "INSERT INTO STUDENT (S_NAME,S_AGE,S_INTODATE,S_MONEY) VALUES (?,?,?,?)";
// 5. 執行SQL語句
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(sql);
// 6. 是否有占位符賦值?
ps.setString(1, student.getName());
ps.setInt(2, student.getAge());
ps.setDate(3, new java.sql.Date(student.getDate().getTime()));
ps.setDouble(4, student.getMoney());
int count = ps.executeUpdate(); // 執行增 刪 改 SQL 返回int類型的受影響行數
// 7. 改變方法的返回值
con = count>0?true:false;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
// 2. 返回con
return con;
}
// TODO 根據id查詢
public Student findStudentByID(Integer id) throws RuntimeException {
//創建方法的返回值
Student student = null;
Connection conn = JDBCUtil.getConn();
//編寫SQL語句
String sql = "SELECT * FROM STUDENT WHERE S_ID = ?";
//執行SQL語句
PreparedStatement ps = null;
ResultSet set = null;
try {
ps = conn.prepareStatement(sql);
//是否有占位符
ps.setInt(1, id);
set = ps.executeQuery();
if(set.next()){
//創建實例對象封裝查詢數據
student = new Student();
student.setId(set.getInt("S_ID"));
student.setAge(set.getInt("S_AGE"));
student.setDate(set.getDate("S_INTODATE"));
student.setMoney(set.getDouble("S_MONEY"));
student.setName(set.getString("S_NAME"));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
JDBCUtil.closeAll(set, ps, conn);
}
return student;
}
// TODO 更新學生信息
public boolean updateStudent(Student student) throws RuntimeException {
//創建方法的返回值
boolean con = false;
//獲取數據庫連接
Connection conn = JDBCUtil.getConn();
//編寫SQL語句
String sql = "UPDATE STUDENT SET S_NAME=?,S_AGE=?,S_INTODATE=?,S_MONEY=? WHERE S_ID=?";
//執行SQL語句
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(sql);
//是否有占位符
ps.setString(1, student.getName());
ps.setInt(2, student.getAge());
ps.setDate(3, new java.sql.Date(student.getDate().getTime()));
ps.setDouble(4, student.getMoney());
ps.setInt(5, student.getId());
int count = ps.executeUpdate();
con = count>0?true:false;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return con;
}
// TODO delete
public boolean deleteStudent(Integer id) throws RuntimeException {
//創建方法的返回變量
boolean con = false;
//獲取數據庫鏈接
Connection conn = JDBCUtil.getConn();
//編寫SQL語句
String sql = "DELETE FROM STUDENT WHERE S_ID = ?";
//執行SQL語句
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(sql);
ps.setInt(1, id);
int count = ps.executeUpdate();
con = count > 0?true:false;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
JDBCUtil.closeAll(null, ps, conn);
}
return con;
}
// TODO main
public static void main(String[] args) {
StudentDAO dao = new StudentDAOImpl();
System.out.println(dao.findStudentListByPageCount());
}
}
3.創建servlet文件StudentAddAction.java接收用戶傳入的值,添加到數據庫并展示到list.jsp(增)
package com.fyl.web;
import java.io.IOException;
import java.io.PrintWriter;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.fyl.dao.StudentDAO;
import com.fyl.dao.impl.StudentDAOImpl;
import com.fyl.entity.Student;
public class StudentAddAction extends HttpServlet {
private static final long serialVersionUID = 1L;
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
this.doPost(request, response);
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//設置請求來源的編碼
request.setCharacterEncoding("UTF-8");
//1. 接收頁面數據
String studentName = request.getParameter("studentName");
String studentAge = request.getParameter("studentAge");
String intoDate = request.getParameter("intoDate");
String money = request.getParameter("money");
//2. 封裝
Student student = new Student();
student.setName(studentName);
student.setAge(Integer.parseInt(studentAge));
student.setMoney(Double.parseDouble(money));
// String 轉 時間
DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
try {
Date d = df.parse(intoDate);
student.setDate(d);
} catch (ParseException e) {
e.printStackTrace();
}
// 3. 創建DAO層對象添加到數據庫
StudentDAO dao = new StudentDAOImpl();
boolean con = dao.insertStudent(student);
if(con){
// 添加成功
response.sendRedirect("StudentViewAction");
}else{
// 添加失敗
// 通過服務器的響應流主動向客戶端發送信息
response.setCharacterEncoding("UTF-8");
response.setContentType("text/html; charset=UTF-8");
String msg = "";
PrintWriter out = response.getWriter();
out.print(msg);
out.flush();
out.close();
}
}
}
4.創建servlet文件StudentDeleteAction.java接收用戶傳入的值,刪除數據庫中指定文件并展示到list.jsp(刪)
package com.fyl.web;
import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.fyl.dao.StudentDAO;
import com.fyl.dao.impl.StudentDAOImpl;
public class StudentDeleteAction extends HttpServlet {
private static final long serialVersionUID = 1L;
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
this.doPost(request, response);
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//1. 確定編碼
request.setCharacterEncoding("UTF-8");
//2. 獲取頁面數據
String id = request.getParameter("id");
//3. 創建DAO方法執行刪除
StudentDAO dao = new StudentDAOImpl();
boolean con = dao.deleteStudent(Integer.parseInt(id));
if(con){
//添加成功
response.sendRedirect("StudentViewAction");
}else{
//添加失敗
response.setCharacterEncoding("UTF-8");
response.setContentType("text/html; charset=UTF-8");
String msg = "";
PrintWriter out = response.getWriter();
out.print(msg);
out.flush();
out.close();
}
}
}
創建servlet文件StudentFindByIDViewAction.java接收用戶傳入的值,查詢數據庫中指定文件并展示到list.jsp(查)
package com.fyl.web;
import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.fyl.dao.StudentDAO;
import com.fyl.dao.impl.StudentDAOImpl;
import com.fyl.entity.Student;
public class StudentFindByIDViewAction extends HttpServlet {
private static final long serialVersionUID = 1L;
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
this.doPost(request, response);
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//設置編碼
request.setCharacterEncoding("UTF-8");
//接收頁面輸入
String id = request.getParameter("id");
//創建DAO層對象
StudentDAO dao = new StudentDAOImpl();
Student student = dao.findStudentByID(new Integer(id));
request.setAttribute("stu", student);
request.getRequestDispatcher("update.jsp").forward(request, response);
}
}
6.創建servlet文件StudentUpdateAction.java接收用戶傳入的值,更新數據庫中指定文件并展示到list.jsp(改)
package com.fyl.web;
import java.io.IOException;
import java.io.PrintWriter;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.fyl.dao.StudentDAO;
import com.fyl.dao.impl.StudentDAOImpl;
import com.fyl.entity.Student;
public class StudentUpdateAction extends HttpServlet {
private static final long serialVersionUID = 1L;
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
this.doPost(request, response);
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//設置請求來源的編碼格式
request.setCharacterEncoding("UTF-8");
//1. 設置接收頁面數據
String studentId = request.getParameter("studentId");
String studentName = request.getParameter("studentName");
String studentAge = request.getParameter("studentAge");
String intoDate = request.getParameter("Date");
String money = request.getParameter("money");
//2. 封裝
Student student = new Student();
String studentId1 = studentId.trim();
student.setId(Integer.parseInt(studentId1));
student.setName(studentName);
student.setAge(Integer.parseInt(studentAge));
student.setMoney(Double.parseDouble(money));
//String轉時間
DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
try {
Date d = df.parse(intoDate);
student.setDate(d);
} catch (ParseException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
//3. 創建DAO層對象添加到數據庫
StudentDAO dao = new StudentDAOImpl();
boolean con = dao.updateStudent(student);
if(con)0.{
//添加成功
response.sendRedirect("StudentViewAction");
}else{
//添加失敗
//通過服務器的響應流主動向客戶端發送信息
response.setCharacterEncoding("UTF-8");
response.setContentType("text/html; charset=UTF-8");
String msg = "";
PrintWriter out = response.getWriter();
out.print(msg);
out.flush();
out.close();
}
}
}
)
方法)
 | 網游世界)
![php 不允許外部訪問,[日常] 解決mysql不允許外部訪問](http://pic.xiahunao.cn/php 不允許外部訪問,[日常] 解決mysql不允許外部訪問)
)
