使用Mysql数据库完成增删改查综合案例（JSP页面）
生活随笔
收集整理的這篇文章主要介紹了
使用Mysql数据库完成增删改查综合案例（JSP页面）
小編覺得挺不錯的,現在分享給大家,幫大家做個參考.
本案例頁面如下：
這是index.jsp頁面（包含模糊查詢）
<%@ page language="java" contentType="text/html; charset=UTF-8"pageEncoding="UTF-8"%> <%@ page import=" java.sql.* "%> <!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>首頁</title> </head> <body> <% //取查詢的名字String selectName = request.getParameter("txtSelect"); %><form action="index.jsp"> <h3 align='center'> 請輸入要查詢的的名字：<input type="text" name="txtSelect" value="<%=selectName==null?"":selectName%>"><input type="submit" name="btnSelect" value="開始查詢"> </h3> </form><table border="1" width="80%" align='center'><tr><td></td><td>序號</td><td>姓名</td><td>性別</td><td>出生日期</td><td>操作</td></tr><% //連接數據庫的五大參數String driverClass = "com.mysql.cj.jdbc.Driver";String serverIp = "localhost";String databaseName = "test3";String userName = "root";String pwd = "123456";String jdbcUrl = "jdbc:mysql://"+serverIp+":3306/"+databaseName+"?serverTimezone=Asia/Shanghai&useSSL=true";String sql = "select * from test3";//讀取JDBCClass.forName(driverClass);//鏈接數據庫Connection con = DriverManager.getConnection(jdbcUrl,userName,pwd);//如果為空，代表當前的狀態不是查詢，而是查詢所有的內容PreparedStatement ps ;if(selectName == null){//判斷查詢文本框里面是否有內容，如果沒有則執行查詢全部信息ps = con.prepareStatement(sql);}else{//如果有，則根據文本框的里面的內容在數據庫根據姓名查詢ps = con.prepareStatement("select * from test3 where user_name like ?");ps.setString(1,'%'+selectName+'%'); }//ResultSet是一個指向數據庫的變量，本質上是不保存任何數據的，執行查詢ResultSet rs = ps.executeQuery(); //boolean flag = rs.next(); //判斷返回指針是否還能繼續往下移動//顯示序號int i = 1; while( rs.next()){ %><tr><td><%out.print(i++);%></td><td><%=rs.getString("id") %></td><td><%=rs.getString("user_name")%></td><td><%=rs.getString("sex")%></td><td><%=rs.getString("birt") %></td><td><a href="insert_Jsp.jsp">添加</a><a href="editView.jsp?id=<%=rs.getString("id")%> ">編輯</a> <a href="delete_action.jsp?id=<%=rs.getString("id")%>">刪除</a></td></tr> <%} %> </table><h1 align="center">共計<%=i-1%>條記錄</h1> <% ps.close();con.close(); %></body> </html> 這是刪除邏輯頁面：delete_action.jsp（后臺運行，不顯示）
<%@ page language="java" contentType="text/html; charset=UTF-8"pageEncoding="UTF-8"%> <%@page import="java.sql.*" %> <!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>刪除的業務邏輯執行頁面</title> </head> <body> <%//獲取參數String id = request.getParameter("id");//連接數據庫的五大參數String driverClass = "com.mysql.cj.jdbc.Driver";String serverIp = "localhost";String databaseName ="test3";String user = "root";String pwd = "123456";//拼湊成一個完整的Url地址String jdbcUrl ="jdbc.mysql://"+serverIp+":3306/"+databaseName+"?serverTimezone=Asia/Shanghai%useSSL=true";//創建對象Connection con = DriverManager.getConnection(jdbcUrl,user,pwd);String sql = "delete from test3 where id = ?";PreparedStatement ps = con.prepareStatement(sql);//對應sql語句的問號跟上面從頁面獲取的參數相對應ps.setObject(1,id);//執行刪除ps.executeUpdate();//關閉連接ps.close();con.close();//完成刪除后跳轉回index頁面response.sendRedirect("index.jsp"); %> </body> </html> 這是用于添加的業務邏輯的運算：index_action.jsp（不顯示，后臺運算）
<%@ page language="java" contentType="text/html; charset=UTF-8"pageEncoding="UTF-8"%> <%@page import = "java.sql.*" %> <!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>添加</title> </head> <body> <%//獲取參數String name = request.getParameter("txtName");String sex = request.getParameter("txtSex");String birt = request.getParameter("txtBirthday");//連接數據庫的五大參數String driverClass = "com.mysql.cj.jdbc.Driver";String serverIp = "localhost";String databaseName = "test3";String userName = "root";String pwd = "123456";String jdbcUrl = "jdbc:mysql://"+serverIp+":3306/"+databaseName+"?serverTimezone=Asia/Shanghai&useSSL=true";Class.forName(driverClass);Connection con = DriverManager.getConnection(jdbcUrl,userName,pwd);String sql ="insert into test3(user_name,sex,birt) values(?,?,?)";PreparedStatement ps = con.prepareStatement(sql);ps.setString(1,name);ps.setString(2,sex);ps.setString(3,birt);ps.executeUpdate();con.close();response.sendRedirect("index.jsp"); %> </body> </html> 這是添加的顯示頁面：index.jsp（用戶客戶端）<%@ 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>添加信息</title> </head> <body> <%%><form action="insert.jsp">學號：<input type="text" name="txtId" ><br>姓名：<input type="text" name="txtName"><br>性別：<input type="text" name="txtSex"><br>出生年月日：<input type="text" name="txtBirthday"><br><input type="submit" value="確認添加"> </form> </body> </html> 修改的業務邏輯運算界面：update_action.jsp（不顯示，后臺執行）
<%@ page language="java" contentType="text/html; charset=UTF-8"pageEncoding="UTF-8"%> <%@ page import="java.sql.*" %> <!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>編輯業務邏輯頁面</title> </head> <body> <%//獲取參數String id = request.getParameter("txtId");String name = request.getParameter("txtUser");String sex = request.getParameter("selSex");String birt = request.getParameter("txtBirthday");//連接數據庫的五大參數String driverClass = "com.mysql.cj.jdbc.Driver";String serverIp = "localhost";String databaseName = "test3";String userName = "root";String pwd = "123456";//整合連接地址String jdbcUrl = "jdbc:mysql://"+serverIp+":3306/"+databaseName+"?serverTimezone=Asia/Shanghai&useSSL=true";Class.forName(driverClass);Connection con = DriverManager.getConnection(jdbcUrl,userName,pwd);//要執行的SQL語句String sql ="update test3 set user_name=?,sex=?,birt=? where id=?";PreparedStatement ps = con.prepareStatement(sql);ps.setString(1,name);ps.setString(2,sex);ps.setString(3,birt);ps.setObject(4,id);//執行修改ps.execute();ps.close();con.close();response.sendRedirect("index.jsp"); %> </body> </html> 修改的顯示界面：editView（由客戶端進行操作的顯示頁面）
<%@ page language="java" contentType="text/html; charset=UTF-8"pageEncoding="UTF-8"%><%@ page import="java.sql.*" %> <!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>老師講解的修改</title></head> <body> <% //獲取參數String id = request.getParameter("id");//連接數據庫的五大參數String driverClass = "com.mysql.jdbc.Driver";String databaseName = "test3";String serverIP = "localhost";String user = "root";String pwd = "123456";//拼湊成一個完整的Url地址String jdbcUrl = "jdbc:mysql://"+serverIP+":3306/"+databaseName+"?serverTimezone=Asia/Shanghai&useSSL=true";Class.forName(driverClass);Connection con = DriverManager.getConnection(jdbcUrl,user,pwd);String sql = "select * from test3 where id = ?";//創建對象PreparedStatement ps = con.prepareStatement(sql);//將字符串的id轉換成int型的idps.setObject(1, id);ResultSet rs= ps.executeQuery();if(rs.next()){ %><form action="update_action.jsp" ><input type="hidden" name="txtId" value="<%=rs.getString("id") %>">姓名：<input type ="text" name="txtUser" value="<%=rs.getString("user_name")%>"><br>性別：<select name="selSex"><option value="男" <%if(rs.getString("sex").equals("男")){out.print("selected");} %> >男</option><option value="女" <%if(rs.getString("sex").equals("女")){out.print("selected");} %>>女</option><option value="其他" <%if(rs.getString("sex").equals("其他")){out.print("selected");} %>>其他</option></select><br>出生日期：<input type ="text" name="txtBirthday" value="<%=rs.getString("birt") %>"><br><input type="submit" value="確認更新" name="btnsub"> </form><%}else{response.sendRedirect("error.jsp");}rs.close();ps.close();con.close(); %> </body> </html>
?
這是index.jsp頁面（包含模糊查詢）
<%@ page language="java" contentType="text/html; charset=UTF-8"pageEncoding="UTF-8"%> <%@ page import=" java.sql.* "%> <!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>首頁</title> </head> <body> <% //取查詢的名字String selectName = request.getParameter("txtSelect"); %><form action="index.jsp"> <h3 align='center'> 請輸入要查詢的的名字：<input type="text" name="txtSelect" value="<%=selectName==null?"":selectName%>"><input type="submit" name="btnSelect" value="開始查詢"> </h3> </form><table border="1" width="80%" align='center'><tr><td></td><td>序號</td><td>姓名</td><td>性別</td><td>出生日期</td><td>操作</td></tr><% //連接數據庫的五大參數String driverClass = "com.mysql.cj.jdbc.Driver";String serverIp = "localhost";String databaseName = "test3";String userName = "root";String pwd = "123456";String jdbcUrl = "jdbc:mysql://"+serverIp+":3306/"+databaseName+"?serverTimezone=Asia/Shanghai&useSSL=true";String sql = "select * from test3";//讀取JDBCClass.forName(driverClass);//鏈接數據庫Connection con = DriverManager.getConnection(jdbcUrl,userName,pwd);//如果為空，代表當前的狀態不是查詢，而是查詢所有的內容PreparedStatement ps ;if(selectName == null){//判斷查詢文本框里面是否有內容，如果沒有則執行查詢全部信息ps = con.prepareStatement(sql);}else{//如果有，則根據文本框的里面的內容在數據庫根據姓名查詢ps = con.prepareStatement("select * from test3 where user_name like ?");ps.setString(1,'%'+selectName+'%'); }//ResultSet是一個指向數據庫的變量，本質上是不保存任何數據的，執行查詢ResultSet rs = ps.executeQuery(); //boolean flag = rs.next(); //判斷返回指針是否還能繼續往下移動//顯示序號int i = 1; while( rs.next()){ %><tr><td><%out.print(i++);%></td><td><%=rs.getString("id") %></td><td><%=rs.getString("user_name")%></td><td><%=rs.getString("sex")%></td><td><%=rs.getString("birt") %></td><td><a href="insert_Jsp.jsp">添加</a><a href="editView.jsp?id=<%=rs.getString("id")%> ">編輯</a> <a href="delete_action.jsp?id=<%=rs.getString("id")%>">刪除</a></td></tr> <%} %> </table><h1 align="center">共計<%=i-1%>條記錄</h1> <% ps.close();con.close(); %></body> </html> 這是刪除邏輯頁面：delete_action.jsp（后臺運行，不顯示）
<%@ page language="java" contentType="text/html; charset=UTF-8"pageEncoding="UTF-8"%> <%@page import="java.sql.*" %> <!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>刪除的業務邏輯執行頁面</title> </head> <body> <%//獲取參數String id = request.getParameter("id");//連接數據庫的五大參數String driverClass = "com.mysql.cj.jdbc.Driver";String serverIp = "localhost";String databaseName ="test3";String user = "root";String pwd = "123456";//拼湊成一個完整的Url地址String jdbcUrl ="jdbc.mysql://"+serverIp+":3306/"+databaseName+"?serverTimezone=Asia/Shanghai%useSSL=true";//創建對象Connection con = DriverManager.getConnection(jdbcUrl,user,pwd);String sql = "delete from test3 where id = ?";PreparedStatement ps = con.prepareStatement(sql);//對應sql語句的問號跟上面從頁面獲取的參數相對應ps.setObject(1,id);//執行刪除ps.executeUpdate();//關閉連接ps.close();con.close();//完成刪除后跳轉回index頁面response.sendRedirect("index.jsp"); %> </body> </html> 這是用于添加的業務邏輯的運算：index_action.jsp（不顯示，后臺運算）
<%@ page language="java" contentType="text/html; charset=UTF-8"pageEncoding="UTF-8"%> <%@page import = "java.sql.*" %> <!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>添加</title> </head> <body> <%//獲取參數String name = request.getParameter("txtName");String sex = request.getParameter("txtSex");String birt = request.getParameter("txtBirthday");//連接數據庫的五大參數String driverClass = "com.mysql.cj.jdbc.Driver";String serverIp = "localhost";String databaseName = "test3";String userName = "root";String pwd = "123456";String jdbcUrl = "jdbc:mysql://"+serverIp+":3306/"+databaseName+"?serverTimezone=Asia/Shanghai&useSSL=true";Class.forName(driverClass);Connection con = DriverManager.getConnection(jdbcUrl,userName,pwd);String sql ="insert into test3(user_name,sex,birt) values(?,?,?)";PreparedStatement ps = con.prepareStatement(sql);ps.setString(1,name);ps.setString(2,sex);ps.setString(3,birt);ps.executeUpdate();con.close();response.sendRedirect("index.jsp"); %> </body> </html> 這是添加的顯示頁面：index.jsp（用戶客戶端）<%@ 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>添加信息</title> </head> <body> <%%><form action="insert.jsp">學號：<input type="text" name="txtId" ><br>姓名：<input type="text" name="txtName"><br>性別：<input type="text" name="txtSex"><br>出生年月日：<input type="text" name="txtBirthday"><br><input type="submit" value="確認添加"> </form> </body> </html> 修改的業務邏輯運算界面：update_action.jsp（不顯示，后臺執行）
<%@ page language="java" contentType="text/html; charset=UTF-8"pageEncoding="UTF-8"%> <%@ page import="java.sql.*" %> <!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>編輯業務邏輯頁面</title> </head> <body> <%//獲取參數String id = request.getParameter("txtId");String name = request.getParameter("txtUser");String sex = request.getParameter("selSex");String birt = request.getParameter("txtBirthday");//連接數據庫的五大參數String driverClass = "com.mysql.cj.jdbc.Driver";String serverIp = "localhost";String databaseName = "test3";String userName = "root";String pwd = "123456";//整合連接地址String jdbcUrl = "jdbc:mysql://"+serverIp+":3306/"+databaseName+"?serverTimezone=Asia/Shanghai&useSSL=true";Class.forName(driverClass);Connection con = DriverManager.getConnection(jdbcUrl,userName,pwd);//要執行的SQL語句String sql ="update test3 set user_name=?,sex=?,birt=? where id=?";PreparedStatement ps = con.prepareStatement(sql);ps.setString(1,name);ps.setString(2,sex);ps.setString(3,birt);ps.setObject(4,id);//執行修改ps.execute();ps.close();con.close();response.sendRedirect("index.jsp"); %> </body> </html> 修改的顯示界面：editView（由客戶端進行操作的顯示頁面）
<%@ page language="java" contentType="text/html; charset=UTF-8"pageEncoding="UTF-8"%><%@ page import="java.sql.*" %> <!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>老師講解的修改</title></head> <body> <% //獲取參數String id = request.getParameter("id");//連接數據庫的五大參數String driverClass = "com.mysql.jdbc.Driver";String databaseName = "test3";String serverIP = "localhost";String user = "root";String pwd = "123456";//拼湊成一個完整的Url地址String jdbcUrl = "jdbc:mysql://"+serverIP+":3306/"+databaseName+"?serverTimezone=Asia/Shanghai&useSSL=true";Class.forName(driverClass);Connection con = DriverManager.getConnection(jdbcUrl,user,pwd);String sql = "select * from test3 where id = ?";//創建對象PreparedStatement ps = con.prepareStatement(sql);//將字符串的id轉換成int型的idps.setObject(1, id);ResultSet rs= ps.executeQuery();if(rs.next()){ %><form action="update_action.jsp" ><input type="hidden" name="txtId" value="<%=rs.getString("id") %>">姓名：<input type ="text" name="txtUser" value="<%=rs.getString("user_name")%>"><br>性別：<select name="selSex"><option value="男" <%if(rs.getString("sex").equals("男")){out.print("selected");} %> >男</option><option value="女" <%if(rs.getString("sex").equals("女")){out.print("selected");} %>>女</option><option value="其他" <%if(rs.getString("sex").equals("其他")){out.print("selected");} %>>其他</option></select><br>出生日期：<input type ="text" name="txtBirthday" value="<%=rs.getString("birt") %>"><br><input type="submit" value="確認更新" name="btnsub"> </form><%}else{response.sendRedirect("error.jsp");}rs.close();ps.close();con.close(); %> </body> </html>
最后選擇整個項目，右鍵選擇Run As --> Run on Server 運行整個項目，然后將地址欄復制，在瀏覽器的地址欄粘貼，加上/index.jsp運行
?
轉載于:https://www.cnblogs.com/liaoyuanping-24/p/9197577.html
總結
以上是生活随笔為你收集整理的使用Mysql数据库完成增删改查综合案例（JSP页面）的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： log4j.xml 精选的log4j.x
- 下一篇： Redis 哨兵模式 带密码单机