層級關系：
一、Util包
包裡面寫一個JDBCTools.java檔案
功能：實作資料庫連接配接傳回一個Connection對象，并且可以實作資料庫相應資源的關閉！
注意事項：
1、定義成員變量
1 private static Connection connection ;
2 private static String url="jdbc:mysql://localhost:3306/info?useUnicode=true&CharacterEncoding=utf-8";
3 private static String user="root";
4 private static String password="000429"; 2、使用靜态代碼塊包圍加載驅動的部分，隻執行一次！
1 static {
2 try {
3 Class.forName("com.mysql.jdbc.Driver");
4 } catch (ClassNotFoundException e) {
5 // TODO 自動生成的 catch 塊
6 e.printStackTrace();
7 }
8 } 3、寫一個靜态方法（傳回值是Connection）getConnection( )
1 public static Connection getConnection() {
2
3 try {
4 connection=(Connection) DriverManager.getConnection(url,user,password);
5 } catch (SQLException e) {
6 // TODO 自動生成的 catch 塊
7 e.printStackTrace();
8 }
9
10 return connection;
11 } 4、方法重載releas( )按連接配接對象 connection 表的工作空間 preparedStatement 和結果集 resultSet 的順序依次關閉（注意判斷null）
public static void release(Connection connection,PreparedStatement preparedStatement ,ResultSet resultSet)
{
if(connection!=null) {
try {
connection.close();
} catch (SQLException e) {
// TODO 自動生成的 catch 塊
e.printStackTrace();
}
}
if(preparedStatement!= null) {
try {
preparedStatement.close();
} catch (SQLException e) {
// TODO 自動生成的 catch 塊
e.printStackTrace();
}
}
if(resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
// TODO 自動生成的 catch 塊
e.printStackTrace();
}
}
}
public static void release(Connection connection,PreparedStatement preparedStatement)
{
if(connection!=null) {
try {
connection.close();
} catch (SQLException e) {
// TODO 自動生成的 catch 塊
e.printStackTrace();
}
}
if(preparedStatement!= null) {
try {
preparedStatement.close();
} catch (SQLException e) {
// TODO 自動生成的 catch 塊
e.printStackTrace();
}
}
}
下面給出這個包（類）完整代碼
1 package Util;
2
3 import java.sql.DriverManager;
4 import java.sql.PreparedStatement;
5 import java.sql.ResultSet;
6 import java.sql.SQLException;
7
8 import com.mysql.jdbc.Connection;
9
10 public class JBDCTools {
11 private static Connection connection ;
12 private static String url="jdbc:mysql://localhost:3306/info?useUnicode=true&CharacterEncoding=utf-8";
13 private static String user="root";
14 private static String password="000429";
15 static {
16 try {
17 Class.forName("com.mysql.jdbc.Driver");
18 } catch (ClassNotFoundException e) {
19 // TODO 自動生成的 catch 塊
20 e.printStackTrace();
21 }
22 }
23 public static Connection getConnection() {
24
25 try {
26 connection=(Connection) DriverManager.getConnection(url,user,password);
27 } catch (SQLException e) {
28 // TODO 自動生成的 catch 塊
29 e.printStackTrace();
30 }
31
32 return connection;
33 }
34 public static void release(Connection connection,PreparedStatement preparedStatement ,ResultSet resultSet)
35 {
36 if(connection!=null) {
37 try {
38 connection.close();
39 } catch (SQLException e) {
40 // TODO 自動生成的 catch 塊
41 e.printStackTrace();
42 }
43 }
44 if(preparedStatement!= null) {
45 try {
46 preparedStatement.close();
47 } catch (SQLException e) {
48 // TODO 自動生成的 catch 塊
49 e.printStackTrace();
50 }
51 }
52 if(resultSet != null) {
53 try {
54 resultSet.close();
55 } catch (SQLException e) {
56 // TODO 自動生成的 catch 塊
57 e.printStackTrace();
58 }
59 }
60 }
61 public static void release(Connection connection,PreparedStatement preparedStatement)
62 {
63 if(connection!=null) {
64 try {
65 connection.close();
66 } catch (SQLException e) {
67 // TODO 自動生成的 catch 塊
68 e.printStackTrace();
69 }
70 }
71 if(preparedStatement!= null) {
72 try {
73 preparedStatement.close();
74 } catch (SQLException e) {
75 // TODO 自動生成的 catch 塊
76 e.printStackTrace();
77 }
78 }
79 }
80 } 二、資料結構層（enity包定義Student類）
1 package enity;
2
3 import java.sql.Date;
4
5 public class Student {
6 private Integer id;
7 private String studentname;
8 private String score;
9 private Date birthday;
10 public Integer getId() {
11 return id;
12 }
13 public Student(Integer id, String studentname, String score, Date birthday) {
14 super();
15 this.id = id;
16 this.studentname = studentname;
17 this.score = score;
18 this.birthday = birthday;
19 }
20
21 @Override
22
23 public String toString() {
24 return "Student [id=" + id + ", studentname=" + studentname + ", score=" + score + ", birthday=" + birthday + "]";
25 }
26 public void setId(Integer id) {
27 this.id = id;
28 }
29 public String getstudentname() {
30 return studentname;
31 }
32 public void setstudentname(String studentname) {
33 this.studentname = studentname;
34 }
35 public String getScore() {
36 return score;
37 }
38 public void setScore(String score) {
39 this.score = score;
40 }
41 public Date getBirthday() {
42 return birthday;
43 }
44 public void setBirthday(Date birthday) {
45 this.birthday = birthday;
46 }
47
48 } 三、repository包（StudentRepository類）資料層，完成資料庫的相關操作
1、注意方法的傳回值是否需要封裝資料結構用List集合，比如添加方法，需要傳回一個資料結構集合給servlet，以便于servlet傳回資料+視圖
方法内部的Connection 和 ResultSet 初始化 要為 null 在最外部
1 public List<Student> findAll(){
2 //初始化null值的三個常用變量
3 Connection connection = null;
4 ResultSet resultSet=null;
5 PreparedStatement preparedStatement=null;
6 List<Student> list=new ArrayList<>(); 2、調用JDBCTools類的靜态連接配接方法進行連接配接，
寫SQL語句，執行SQL語句，
封裝從資料庫讀取出來的資料結構類，
用List集合，最後釋放資源。
1 package repository;
2
3 import java.sql.ResultSet;
4 import java.sql.SQLException;
5 import java.util.ArrayList;
6 import java.util.List;
7
8 import com.mysql.jdbc.Connection;
9 import com.mysql.jdbc.PreparedStatement;
10
11 import Util.JBDCTools;
12 import enity.Student;
13
14 public class StudentRepository {
15 public StudentRepository() {};
16 public List<Student> findAll(){
17 //初始化null值的三個常用變量
18 Connection connection = null;
19 ResultSet resultSet=null;
20 PreparedStatement preparedStatement=null;
21 //初始化完畢，定義一個List集合存放學生類資料結構
22
23 List<Student> list=new ArrayList<>();
24
25 try {
26 //擷取連接配接
27 connection=JBDCTools.getConnection();
28 //SQL語句
29 String sql="select * from student;";
30 //定義工作表空間
31
32 preparedStatement = (PreparedStatement) connection.prepareStatement(sql);
33 //執行SQL語句
34
35 resultSet = preparedStatement.executeQuery();
36 //初始化學生類
37
38 Student student=null;
39 //周遊結果集
40 while(resultSet.next()){
41 Integer id=resultSet.getInt(1);
42 String name=resultSet.getString(2);
43 String score = resultSet.getString(3);
44 java.sql.Date date = resultSet.getDate(4);
45 student = new Student (id,name,score,(java.sql.Date) date);
46 list.add(student);
47 }
48 } catch (SQLException e) {
49 // TODO 自動生成的 catch 塊
50 e.printStackTrace();
51 }finally {
52 //釋放資源
53 JBDCTools.release(connection,preparedStatement,resultSet);
54 }
55 return list;
56 }
57 //添加
58
59 public List<Student> add(String studentname,String score) {
60 Connection connection = null;
61 PreparedStatement preparedStatement=null;
62 List<Student> list=new ArrayList<>();
63 try {
64 connection=JBDCTools.getConnection();
65 String SQL="INSERT INTO student(studentname,score,birthday) VALUE(?,?,?);";
66 //
67 preparedStatement = (PreparedStatement) connection.prepareStatement(SQL);
68 connection.setAutoCommit(false);
69 //
70 preparedStatement.setString(1, studentname);
71 preparedStatement.setString(2, score);
72 preparedStatement.setDate(3, new java.sql.Date(5));
73 int flag=preparedStatement.executeUpdate();
74 //
75 connection.commit();
76 //
77 if(flag>0) {
78 System.out.println("成功添加了"+flag+"條資料！");
79 }
80 else {
81 System.out.println("添加失敗！");
82 }
83 } catch (SQLException e) {
84 // TODO 自動生成的 catch 塊
85 e.printStackTrace();
86 }finally {
87 JBDCTools.release(connection,preparedStatement);
88 }
89 //傳回List集合
90 return list;
91 }
92 //根據ID删除資料
93
94 public static void deleteByid(Integer id) {
95 Connection connection=null;
96 java.sql.PreparedStatement preparedStatement = null;
97 connection = JBDCTools.getConnection();
98 String sql = "DELETE FROM student where id = ?";
99 try {
100 preparedStatement = connection.prepareStatement(sql);
101 preparedStatement.setInt(1, id);
102 preparedStatement.executeUpdate();
103 } catch (SQLException e) {
104 // TODO 自動生成的 catch 塊
105 e.printStackTrace();
106 }
107 finally {
108 JBDCTools.release(connection,preparedStatement);
109 }
110 }
111
112
113 //根據學号查找
114 public Student findById(Integer id) {
115 Connection connection=null;
116 PreparedStatement preparedStatement=null;
117 ResultSet resultSet=null;
118 Student student=null;
119
120 connection=JBDCTools.getConnection();
121 String sql="SELECT * FROM student where id = ?";
122 try {
123 preparedStatement=(PreparedStatement) connection.prepareStatement(sql);
124 preparedStatement.setInt(1,id);
125 resultSet = preparedStatement.executeQuery();
126 while(resultSet.next())
127 {
128 Integer id1=resultSet.getInt(1);
129 String name=resultSet.getString(2);
130 String score = resultSet.getString(3);
131 java.sql.Date date = resultSet.getDate(4);
132 student = new Student (id1,name,score,(java.sql.Date) date);
133 }
134 } catch (SQLException e) {
135 // TODO 自動生成的 catch 塊
136 e.printStackTrace();
137 }finally {
138 JBDCTools.release(connection,preparedStatement,resultSet);
139 }
140 return student;
141 }
142
143
144
145
146 //修改
147 public void update (Integer id,String studentname,String score) {
148 Connection connection = null;
149 PreparedStatement preparedStatement=null;
150 try {
151 connection=JBDCTools.getConnection();
152 String SQL="UPDATE student set studentname=?,score=? where id=?";
153 preparedStatement = (PreparedStatement) connection.prepareStatement(SQL);
154 connection.setAutoCommit(false);
155 preparedStatement.setString(1, studentname);
156 preparedStatement.setString(2, score);
157 preparedStatement.setInt(3, id);
158 int flag=preparedStatement.executeUpdate();
159 connection.commit();
160 if(flag>0) {
161 System.out.println("成功修改了"+flag+"條資料！");
162 }
163 else {
164 System.out.println("修改失敗！");
165 }
166 } catch (SQLException e) {
167 // TODO 自動生成的 catch 塊
168 e.printStackTrace();
169 }finally {
170 JBDCTools.release(connection,preparedStatement);
171 }
172 }
173
174
175 } 四、servlet層
功能：傳回資料+視圖到使用者界面，和使用者直接互動！
1、表單的資料是post或者是get
href是get，對應doGet方法
2、doPost方法：
首先要進行中文亂碼問題：
1 request.setCharacterEncoding("utf-8");
2 response.setCharacterEncoding("utf-8");
3 response.setContentType("application/json;charset=utf-8"); 互動過程：
①擷取前台jsp檔案的資料：用request.getParameter()方法
調用資料處理層 studentRepository 類的相應方法！
②使用response.sendRedirect()方法重定向（一般是傳回本頁面servlet）
3、doGet方法：
功能：主要在處理前台 hre f連結請求。
①首先擷取前台資料
②其次擷取背景資料（也就是List集合中的資料，調用studentRepository類方法傳回List對象值并儲存！）
request轉發！
也就是把資料一并轉發到前台界面
1 List<Student> list= studentRepository.findAll();
2 request.setAttribute("list", list);
3 request.getRequestDispatcher("index.jsp").forward(request,response); 下面給出servlet層的完整代碼：
1 package com.southwind.servlet;
2
3 import java.io.IOException;
4 import java.util.List;
5
6 import javax.servlet.ServletException;
7 import javax.servlet.annotation.WebServlet;
8 import javax.servlet.http.HttpServlet;
9 import javax.servlet.http.HttpServletRequest;
10 import javax.servlet.http.HttpServletResponse;
11
12 import enity.Student;
13 import repository.StudentRepository;
14
15 /**
16 * Servlet implementation class studentservlet
17 */
18 @WebServlet("/studentservlet")
19 public class studentservlet extends HttpServlet {
20 private StudentRepository studentRepository=new StudentRepository();
21 private static final long serialVersionUID = 1L;
22
23 /**
24 * @see HttpServlet#HttpServlet()
25 */
26 public studentservlet() {
27 super();
28 // TODO Auto-generated constructor stub
29 }
30
31 /**
32 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
33 */
34 protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
35 // TODO Auto-generated method stub
36 String method = request.getParameter("method");
37 if(method==null)
38 {
39 method="findAll";
40 }
41 switch(method) {
42 case "findAll":
43 List<Student> list= studentRepository.findAll();
44 request.setAttribute("list", list);
45 request.getRequestDispatcher("index.jsp").forward(request,response);
46 break;
47 case "delete":
48 String idStr= request.getParameter("id");
49 Integer id= Integer.parseInt(idStr);
50 StudentRepository.deleteByid(id);
51 list= studentRepository.findAll();
52 request.setAttribute("list", list);
53 request.getRequestDispatcher("index.jsp").forward(request,response);
54 break;
55 case "findById":
56 idStr=request.getParameter("id");
57 id=Integer.parseInt(idStr);
58 request.setAttribute("student",studentRepository.findById(id));
59 request.getRequestDispatcher("update.jsp").forward(request, response);
60 break;
61 }
62 //傳回視圖+資料
63 }
64 /**
65 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
66 */
67 protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
68 // TODO Auto-generated method stub
69 //處理編碼問題
70 request.setCharacterEncoding("utf-8");
71 response.setCharacterEncoding("utf-8");
72 response.setContentType("application/json;charset=utf-8");
73 //處理完畢
74 String method = request.getParameter("method");
75 switch (method) {
76 case "add":
77 String studentname=request.getParameter("studentname");
78 String score=request.getParameter("score");
79 response.getWriter().write(studentname+score);
80 studentRepository.add(studentname,score);
81 break;
82 case "update":
83 String idStr= request.getParameter("id");
84 Integer id= Integer.parseInt(idStr);
85 studentname=request.getParameter("studentname");
86 score=request.getParameter("score");
87 studentRepository.update(id, studentname, score);
88 break;
89 }
90 response.sendRedirect("http://localhost:8080/bilibili/studentservlet");
91 }
92 } 五、jsp層（畫前台界面，直接接收使用者的資料）
1、表單和servlet的關聯
<form action="${pageContext.request.contextPath}/studentservlet" method="post">
如果有不需要讓使用者選擇的字段，可以使用隐藏的input标簽：
<input type="hidden" name="method" value="add">
1 <%@ page language="java" contentType="text/html; charset=UTF-8"
2 pageEncoding="UTF-8"%>
3
4 <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
5 <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
6 <html>
7 <head>
8 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
9 <title>Insert title here</title>
10 </head>
11 <body>
12 <table>
13 <tr>
14 <th>編号</th>
15 <th>姓名</th>
16 <th>成績</th>
17 <th>注冊日期</th>
18 </tr>
19 <tr>
20 <a href="add.jsp">添加</a>
21 </tr>
22 <c:forEach items="${list}" var="student">
23 <tr>
24 <td>${student.id}</td>
25 <td>${student.studentname}</td>
26 <td>${student.score}</td>
27 <td>${student.birthday}</td>
28 <td>
29 <a href="${pageContext.request.contextPath}/studentservlet?method=delete&id=${student.id}">删除</a>
30 <a href="${pageContext.request.contextPath}/studentservlet?method=findById&id=${student.id}">修改</a>
31 </td>
32 </tr>
33 </c:forEach>
34 </table>
35 </body>
36 </html> add.jsp
1 <%@ page language="java" contentType="text/html; charset=UTF-8"
2 pageEncoding="UTF-8"%>
3 <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
4 <html>
5 <head>
6 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
7 <title>插入</title>
8 </head>
9 <body>
10 <form action="${pageContext.request.contextPath}/studentservlet" method="post">
11 姓名：<input type="text" name="studentname"/><br/>
12 成績：<input type="text" name="score"/><br/>
13 <input type="hidden" name="method" value="add">
14 <input type="submit" value="送出"/>
15 </form>
16 </body>
17 </html> update.jsp
1 <%@ page language="java" contentType="text/html; charset=UTF-8"
2 pageEncoding="UTF-8"%>
3 <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
4 <html>
5 <head>
6 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
7 <title>修改</title>
8 </head>
9 <body>
10 <form action="${pageContext.request.contextPath}/studentservlet" method="post">
11 編号：<input type="text" name="id" value="${student.id }" readonly/><br>
12 姓名：<input type="text" name="studentname" value="${student.studentname }"/><br/>
13 成績：<input type="text" name="score"value="${student.score }"/><br/>
14 <input type="hidden" name="method" value="update">
15 <input type="submit" value="修改"/>
16 </form>
17 </body>
18 </html>