JSP實現servlet對資料庫的增刪查改操作
>前期需要:
1. 一個JavaBean頁面對資訊進行儲存
此處取名為JDBC_Register
package JavaBean;
import java.io.Serializable;
public class JDBC_Register implements Serializable{
private static final long serialVersionUID = 1L;
private String name;
private String username;
private String password1;
private String phone;
private String mailbox;
private String where;
private String usertype;
public JDBC_Register() {
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword1() {
return password1;
}
public void setPassword1(String password1) {
this.password1 = password1;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public String getMailbox() {
return mailbox;
}
public void setMailbox(String mailbox) {
this.mailbox = mailbox;
}
public String getWhere() {
return where;
}
public void setWhere(String where) {
this.where = where;
}
public String getUsertype() {
return usertype;
}
public void setUsertype(String usertype) {
this.usertype = usertype;
}
}
- 一個顯示頁面information.jsp
-
以下顯示主要程式碼< -
<table border=3 class="two">
<tr bgcolor="#CCCCFF" style="color:whitesmoke">
<th> 姓 名 </th>
<th> 學 號 </th>
<th> 密 碼 </th>
<th> 手 機 </th>
<th> 郵 箱 </th>
<th> 住 址 </th>
<th> 類 型 </th>
<!-- <th> 操 作
</th> -->
</tr>
<%
List<JDBC_Register> list = (List<JDBC_Register>)request.getAttribute("list");
if (list == null||list.size()<1){
out.print("暫時沒有學生註冊資訊");
}else
{
for(JDBC_Register people:list){
%>
<tr>
<td><%= people.getName() %></td>
<td><%= people.getUsername() %></td>
<td><%= people.getPassword1() %></td>
<td><%= people.getPhone()%></td>
<td><%= people.getMailbox()%></td>
<td><%= people.getWhere()%></td>
<td><%= people.getUsertype()%></td>
</tr>
<%
}
}
%>
</table>
- 顯示全部資料庫內容
-
以下顯示主要程式碼< -
@WebServlet("/JDBC_select")
public class JDBC_select extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
request.setCharacterEncoding("UTF-8");
response.setContentType("text/html;charset=UTF-8");
response.setCharacterEncoding("UTF-8");
response.getHeader("text/html; charset=UTF-8");
PrintWriter out = response.getWriter();
try {
Class.forName("com.mysql.jdbc.Driver");
String Url = "jdbc:mysql://127.0.0.1:3306/dbmis?characterEncoding=utf8&useUnicode=true&serverTimezone=UTC";
String Username = "root";
String Password = "123456789";
Connection Conn = DriverManager.getConnection(Url, Username, Password);
Statement Sta = Conn.createStatement();
String sql = "SELECT * FROM tb_useraccount";
ResultSet RS = Sta.executeQuery(sql);
List<JDBC_Register> list = new ArrayList<JDBC_Register>();
while (RS.next()) {
/* 建立Serverlt JDBC_Register類 */
JDBC_Register people = new JDBC_Register();
/* 從資料庫拉取的資料需要URLDecoder解碼 */
people.setName(URLDecoder.decode(RS.getString("name"),"UTF-8");
people.setUsername(URLDecoder.decode(RS.getString("username"),"UTF-8")); people.setPassword1(URLDecoder.decode(RS.getString("password"),"UTF-8"));
people.setPhone(URLDecoder.decode(RS.getString("phone"),"UTF-8"));
people.setMailbox(URLDecoder.decode(RS.getString("mailbox"),"UTF-8"));
people.setWhere(URLDecoder.decode(RS.getString("where"),"UTF-8"));
people.setUsertype(URLDecoder.decode(RS.getString("usertype"),"UTF-8"));
list.add(people);
System.out.print(list);
out.print(people);
}
request.setAttribute("list", list);
/* 關閉通道 */
RS.close();
Sta.close();
Conn.close();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
}
request.getRequestDispatcher("information.jsp").forward(request, response);
}
}
- 增加一條資料
-
以下顯示主要程式碼< -
@WebServlet("/JDBC_add")
public class JDBC_add extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("UTF-8");
response.setCharacterEncoding("UTF-8");
response.setContentType("text/html;charset=UTF-8");
response.getHeader("text/html; charset=UTF-8");
PrintWriter out = response.getWriter();
String name=request.getParameter("name");
String username=request.getParameter("username");
String password=request.getParameter("password");
String phone=request.getParameter("phone");
String mailbox=request.getParameter("mailbox");
String where=request.getParameter("where");
String usertype = request.getParameter("usertype");
try {
Class.forName("com.mysql.jdbc.Driver");
String Url = "jdbc:mysql://127.0.0.1:3306/dbmis?characterEncoding=utf8&useUnicode=true&serverTimezone=UTC";
String Username = "root";
String Password = "123456789";
Connection Conn = DriverManager.getConnection(Url,Username,Password);
String sql_insert = "INSERT INTO tb_useraccount VALUES(?,?,?,?,?,?,?)";
PreparedStatement PreSta = Conn.prepareStatement(sql_insert);
PreSta.setString(1, name);
PreSta.setString(2, username);
PreSta.setString(3, password);
PreSta.setString(4, phone);
PreSta.setString(5, mailbox);
PreSta.setString(6, where);
PreSta.setString(7, usertype);
int row = PreSta.executeUpdate();
}catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
finally{
}
response.sendRedirect("information.jsp?name="+URLEncoder.encode(name,"UTF-8")+"&username="+username);
}
}
- 刪除一條資料
-
以下顯示主要程式碼< -
@WebServlet("/JDBC_delete")
public class JDBC_delete extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("UTF-8");
response.setCharacterEncoding("UTF-8");
response.setContentType("text/html;charset=UTF-8");
response.getHeader("text/html; charset=UTF-8");
PrintWriter out = response.getWriter();
String name=request.getParameter("name");
try {
Class.forName("com.mysql.jdbc.Driver");
String Url = "jdbc:mysql://127.0.0.1:3306/dbmis?characterEncoding=utf8&useUnicode=true&serverTimezone=UTC";
String Username = "root";
String Password = "123456789";
Connection Conn = DriverManager.getConnection(Url,Username,Password);
String sql ="DELETE FROM tb_useraccount WHERE name= ?";
PreparedStatement PreSta = Conn.prepareStatement(sql);
PreSta.setString(1, name);
int row = PreSta.executeUpdate();
out.println("成功刪除"+row+"條關於-> "+usertype+" "+name+"的資料！");
}catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
finally{
}
response.sendRedirect("information.jsp?name="+URLEncoder.encode(name,"UTF-8")+"&username="+username);
}
}
- 查詢一條資料並返回顯示
-
以下顯示主要程式碼< -
@WebServlet("/JDBC_search")
public class JDBC_search extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("UTF-8");
response.setCharacterEncoding("UTF-8");
response.setContentType("text/html;charset=UTF-8");
response.getHeader("text/html; charset=UTF-8");
PrintWriter out = response.getWriter();
String name=request.getParameter("name");
String username=request.getParameter("username");
String password=request.getParameter("password");
String phone=request.getParameter("phone");
String mailbox=request.getParameter("mailbox");
String where=request.getParameter("where");
String usertype = request.getParameter("usertype");
try {
Class.forName("com.mysql.jdbc.Driver");
String Url = "jdbc:mysql://127.0.0.1:3306/dbmis?characterEncoding=utf8&useUnicode=true&serverTimezone=UTC";
String Username = "root";
String Password = "123456789";
Connection Conn = DriverManager.getConnection(Url,Username,Password);
String sql ="SELECT * FROM tb_useraccount WHERE name= ? OR username= ? OR password= ? OR phone= ? OR mailbox= ? OR `where` = ? OR usertype= ?";
PreparedStatement PreSta = Conn.prepareStatement(sql);
PreSta.setString(1, name);
PreSta.setString(2, username);
PreSta.setString(3, password);
PreSta.setString(4, phone);
PreSta.setString(5, mailbox);
PreSta.setString(6, where);
PreSta.setString(7, usertype);
//需要返回結果需要executeQuery
ResultSet res = PreSta.executeQuery();
List<JDBC_Register> list = new ArrayList<JDBC_Register>();
while (res.next()) {
JDBC_Register people = new JDBC_Register();
people.setName(URLDecoder.decode(res.getString("name"),"UTF-8"));
people.setUsername(URLDecoder.decode(res.getString("username"),"UTF-8"));
people.setPassword1(URLDecoder.decode(res.getString("password"),"UTF-8"));
people.setPhone(URLDecoder.decode(res.getString("phone"),"UTF-8"));
people.setMailbox(URLDecoder.decode(res.getString("mailbox"),"UTF-8"));
people.setWhere(URLDecoder.decode(res.getString("where"),"UTF-8"));
people.setUsertype(URLDecoder.decode(res.getString("usertype"),"UTF-8"));
list.add(people);
System.out.print(list);
out.print(people);
}
request.setAttribute("list", list);
res.close();
PreSta.close();
Conn.close();
}catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
}
request.getRequestDispatcher("information.jsp").forward(request, response);
}
}
- 修改一條資料
-
以下顯示主要程式碼< -
@WebServlet("/JDBC_change")
public class JDBC_change extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
try {
Class.forName("com.mysql.jdbc.Driver");
String Url = "jdbc:mysql://127.0.0.1:3306/dbmis?characterEncoding=utf8&useUnicode=true&serverTimezone=UTC";
String Username = "root";
String Password = "123456789";
Connection Conn = DriverManager.getConnection(Url,Username,Password);
String sql = "UPDATE tb_useraccount SET name=? WHERE username=?";
PreparedStatement PreSta = Conn.prepareStatement(sql);
PreSta.setString(1, name);
PreSta.setString(2, username);
int row = PreSta.executeUpdate();
System.out.println("成功修改"+row+"條關於-> "+usertype+" "+name+"的資料！");
}catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
}
request.getRequestDispatcher("information.jsp").forward(request, response);
}
}
需要使用 ？進行sql引數嵌入時
寫法：
Class.forName("com.mysql.jdbc.Driver");
String Url = "jdbc:mysql://127.0.0.1:3306/";
String Username = "root";
String Password = "123456789";
Connection Conn = DriverManager.getConnection(Url,Username,Password);
String sql = "INSERT INTO tb_useraccount VALUES(?,?,?,?,?,?,?)";
//需要加入引數就需要Conn.perpareStatement(sql)
PreparedStatement PreSta = Conn.prepareStatement(sql);
PreSta.setString(1, name);
PreSta.setString(2, username);
PreSta.setString(3, password);
PreSta.setString(4, phone);
PreSta.setString(5, mailbox);
PreSta.setString(6, where);
PreSta.setString(7, usertype);
//此時不需要帶入引數()
int row = PreSta.executeUpdate();
不需要進行sql引數時
Class.forName("com.mysql.jdbc.Driver");
String Url = "jdbc:mysql://127.0.0.1:3306/dbmis?characterEncoding=utf8&useUnicode=true&serverTimezone=UTC";
String Username = "root";
String Password = "123456789";
Connection Conn = DriverManager.getConnection(Url, Username, Password);
//直接建立Conn.createStatement()
Statement Sta = Conn.createStatement();
String sql = "SELECT * FROM tb_useraccount";
//此時需要帶上引數(sql)
ResultSet RS = Sta.executeQuery(sql);
相關文章
- mybatis實現MySQL資料庫的增刪改查MyBatisMySql資料庫
- Python操作SQLServer資料庫增刪改查PythonSQLServer資料庫
- Node+Vue實現對資料的增刪改查Vue
- YII1 增、刪、改、查資料庫操作資料庫
- 連線資料庫並實現增、刪、改、查資料庫
- JDBC連線資料庫實現增刪改查JDBC資料庫
- mysql資料增刪改查操作MySql
- 自寫的使用PDO對mysql資料庫的增刪改查操作類MySql資料庫
- Golang原生sql操作Mysql資料庫增刪改查GolangMySql資料庫
- 利用Java的API實現HBase資料庫的增刪查改JavaAPI資料庫
- 使用express+mongoose對mongodb實現增刪改查操作ExpressMongoDB
- Go實現對MySQL的增刪改查GoMySql
- Java實現簡單的增刪改查操作Java
- JDBC連線資料庫實現增刪改查前端互動JDBC資料庫前端
- 關於mongodb資料庫的增刪改查MongoDB資料庫
- 修改thinkphp的主頁面,連線資料庫,實現增刪改查PHP資料庫
- 使用Chatgpt編寫的PHP資料庫pdo操作類（增刪改查）ChatGPTPHP資料庫
- Flutter資料庫Sqflite之增刪改查Flutter資料庫
- mogoose 建立資料庫並增刪改查Go資料庫
- MongoDB增刪改查操作MongoDB
- SpringMVC+Spring Data JPA實現增刪改查操作SpringMVC
- 資料庫的簡介和MySQL增刪改查資料庫MySql
- C# 資料操作系列 - 12 NHibernate的增刪改查C#
- ORM實操之資料的增刪改查ORM
- js實現表格的增刪改查JS
- 使用Mongoose類庫實現簡單的增刪改查Go
- Android 中關於增刪改查資料庫表實踐Android資料庫
- PHP操作MongoDB（增刪改查）PHPMongoDB
- MySQL資料庫 ---MySQL表的增刪改查(進階)MySql資料庫
- MySql 表資料的增、刪、改、查MySql
- node+express對本地檔案的增刪改查操作Express
- 運用layui實現增刪改查UI
- C# 資料操作系列 - 8. EF Core的增刪改查C#
- MySQL基礎操作(增刪改查)MySql
- JS字串操作之增刪改查JS字串
- C# 資料操作系列 - 15 SqlSugar 增刪改查詳解C#SqlSugar
- 關於連線資料庫實現增刪改查並且網頁上表現出來資料庫網頁
- Oracle、mysql資料庫增、刪、改OracleMySql資料庫