jsp+servlet+jdbc 實現crud 最簡單的版本

2024-04-27 15:09:33
字體：大中小
來源：轉載
供稿：網友
史上最簡單的jsp+servlet+jdbc實現CRUD項目，已經經歷了千錘百煉的測試與修改，健壯性一級棒！沒有任何問題。而且簡明易懂，內附大量注釋，是不可多得的好例子。為了保證簡單，甚至沒有把每個servlet里的jdbc連接單獨拿出來，直觀。數據庫也一并給出。使用本項目時，首先訪問的登錄頁面，即：http://localhost:8080/demo1/login.jsp，用戶名root，密碼root，也可以用庫里面的其它用戶名、密碼。輸入正確進入welcome頁然后跳到查看學生信息頁，輸入錯誤進error.jsp，然后自動跳轉回登錄頁。
目錄結構:
先創建庫。
寫前端吧。
******************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>Insert title here</title><style type="text/CSS">body {	background-image:url('images/yellowbg.png');	background-size: cover;  /* 圖片平鋪拉伸，適應屏幕 */}h1{  /* 標題居中  */	margin:100px auto;	text-align: center;}form{  /* 表單居中  */	width:300px;	height:220px;	margin: 100px auto;}.item{    /* item與itemV的設定純粹是為了讓出入框左對齊，名字右對齊    */	width:80px;	display:inline-block;	text-align: right;}.itemV{	width:180px;	display:inline-block;	text-align: right;}#btn{    /* 按鈕居中  */	margin-left: 80px;}</style></head><body>	<h1>歡迎登錄學生管理系統！</h1>		<form action="loginServlet" method="post">	<span class="item">用戶名:</span> 	<input type="text" name="username" /></span><br><br>	<span class="item">密碼:</span> 	<input type="text" name="passWord" /></span><br><br><br>	<div id="btn"> 	<input type="submit" value="登錄" /> 	<input type="reset" value="重置" />	</div>	</form>	</body></html>
welcome.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>Insert title here</title><style>body {	background-image:url('images/greenbg.png');	background-size: cover;}h1{	margin:150px auto;	text-align: center;}span{	color:red;	font-family:fantasy;}</style></head><body>	<h1>登錄成功，歡迎<span><%=request.getAttribute("username") %></span>來到學生管理系統！</h1>	<center><p>5秒鐘后系統會自動跳轉到查看全部學生信息頁面...</p></center>		<%response.setHeader("refresh","5;url=findAllServlet"); %></body></html>
error.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>Insert title here</title><style>body {	background-image:url('images/yellowbg.png');	background-size: cover;}h1{	margin:150px auto;	text-align: center;}span{	color:red;	font-family:fantasy;}</style></head><body>	<h1>登錄失敗，<span><%=request.getAttribute("username") %></span>的用戶名或者密碼錯誤！</h1>	<center><p>5秒鐘后系統會自動跳轉到登錄頁面...</p></center>		<%response.setHeader("refresh","5;url=login.jsp"); %></body></html>listStudent.jsp
<%@ page language="java" contentType="text/html; charset=utf-8"    pageEncoding="utf-8"%><%@page import="java.util.List" %><%@page import="com.bean.Student" %><!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>Insert title here</title><style>body {	background-image:url('images/greenbg.png');	background-size: cover;  /* 圖片平鋪拉伸，適應屏幕 */}h1{	margin:50px auto;	text-align: center;}table {	width:600px;	margin:50px auto;    border-collapse: collapse;    text-align: center;}table,th,td{border:1px solid black;}th{height:50px;}a:link {color:black;}      /* 未訪問鏈接*/a:visited {color:black;} a:hover {color:#f00;}  /* 鼠標移動到鏈接上 */a:active {color:#f60;}  /* 已點擊 */#toAdd{ text-align: center;}</style></head><body>	<h1>歡迎來到查看學生頁面</h1>	<table style="border: 1px">		<tr>			<th>ID</th>			<th>username</th>			<th>password</th>			<th>sex</th>			<th>address</th>			<th colspan="3">操作</th>		</tr>		<%  List<Student> ss = (List<Student>)request.getAttribute("ss"); 			for(Student s : ss){		%>		<tr>			<td><%=s.getId() %></td>			<td><%=s.getUsername() %></td>			<td><%=s.getPassword() %></td>			<td><%=s.getSex() %></td>			<td><%=s.getAddress() %></td>			<td colspan="3">				<a href="update.jsp?id=<%=s.getId()%>&username=<%=s.getUsername()%>&password=<%=s.getPassword()%>&sex=<%=s.getSex()%>&address=<%=s.getAddress()%>">修改</a>				  				<a href="deleteServlet?id=<%=s.getId()%>" onclick="return confirm('確定刪除？')">刪除</a>			</td>		</tr>		<% 			}		%>	</table>	<div id="toAdd"><a href="add.jsp">增加學生</a></div>		</body></html>add.jsp
<%@ page language="java" contentType="text/html; charset=utf-8"    pageEncoding="utf-8"%><%@page import="com.bean.Student" %><!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>Insert title here</title><style type="text/css">body {	background-image:url('images/greenbg.png');	background-size: cover;}h1{	margin:50px auto;	text-align: center;}form{	width:310px;	margin: 100px auto;}.item{	color:red;	width:100px;	display:inline-block; 	text-align:right;}.btn{	width:50px;}</style></head><body>	<h1>歡迎來到新增學生頁面</h1>	<form action="addServlet" method="post">		<span class="item">ID：</span>       <span><input type="text" name="id" /></span><br>		<span class="item">username：</span> <span><input type="text" name="username" /></span><br>		<span class="item">password：</span> <span><input type="text" name="password" /></span><br>		<span class="item">sex：</span>      <span><input type="text" name="sex" /></span><br>		<span class="item">address：</span>  <span><input type="text" name="address" /></span><br>		<br><br>		<center>			<input class="btn" type="submit" value="提交" />			<input class="btn" type="reset" value="重置" />		</center>	</form></body></html>update.jsp
<%@ page language="java" contentType="text/html; charset=utf-8"    pageEncoding="utf-8"%><%@page import="com.bean.Student" %><!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>Insert title here</title><style type="text/css">body {	background-image:url('images/greenbg.png');	background-size: cover;}h1{	margin:50px auto;	text-align: center;}form{	width:310px;	margin: 100px auto;}.item{	color:red;	width:100px;	display:inline-block; 	text-align:right;}.btn{	width:50px;}</style></head><body>	<%		String id = request.getParameter("id");		String username = request.getParameter("username");		String password = request.getParameter("password");		String sex = request.getParameter("sex");		String address = request.getParameter("address");	%>		<!-- id是唯一定位元素的標識符，不能修改，但可以顯示給人看，此時不要用disabled限制別人訪問，因為		 它會使id的name連同value都作廢，不能提交給servlet；用read-only，它可以提交。	 -->	<h1>歡迎來到修改學生信息頁面</h1>	<form action="updateServlet" method="post">		<span class="item">ID：</span>       <span><input type="text" name="id" value="<%=id %>" readonly="readonly"/></span><br>		<span class="item">username：</span> <span><input type="text" name="username" value="<%=username %>" /></span><br>		<span class="item">password：</span> <span><input type="text" name="password" value="<%=password %>" /></span><br>		<span class="item">sex：</span>      <span><input type="text" name="sex" value="<%=sex %>" /></span><br>		<span class="item">address：</span>  <span><input type="text" name="address" value="<%=address %>" /></span><br>		<br><br>		<center>			<input class="btn" type="submit" value="提交" />			<input class="btn" type="reset" value="重置" />		</center>	</form></body></html>
******************web.xml**********************
web.xml
<?xml version="1.0" encoding="UTF-8"?><web-app version="2.5" 	xmlns="http://java.sun.com/xml/ns/javaee" 	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 	xsi:schemaLocation="http://java.sun.com/xml/ns/javaee 	http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">      <welcome-file-list>    <welcome-file>login.jsp</welcome-file>  </welcome-file-list>      <servlet>  	<servlet-name>UpdateServlet</servlet-name>  	<servlet-class>com.servlet.UpdateServlet</servlet-class>  </servlet>  <servlet-mapping>  	<servlet-name>UpdateServlet</servlet-name>  	<url-pattern>/updateServlet</url-pattern>  </servlet-mapping>      <servlet>  	<servlet-name>FindAllServlet</servlet-name>  	<servlet-class>com.servlet.FindAllServlet</servlet-class>  </servlet>  <servlet-mapping>  	<servlet-name>FindAllServlet</servlet-name>  	<url-pattern>/findAllServlet</url-pattern>  </servlet-mapping>      <servlet>    <servlet-name>LoginServlet</servlet-name>    <servlet-class>com.servlet.LoginServlet</servlet-class>  </servlet>  <servlet-mapping>    <servlet-name>LoginServlet</servlet-name>    <url-pattern>/loginServlet</url-pattern>  </servlet-mapping>    	  <servlet>    <servlet-name>ListServlet</servlet-name>    <servlet-class>com.servlet.ListServlet</servlet-class>  </servlet>  <servlet-mapping>    <servlet-name>ListServlet</servlet-name>    <url-pattern>/listServlet</url-pattern>  </servlet-mapping>	      <servlet>  	<servlet-name>DeleteServlet</servlet-name>  	<servlet-class>com.servlet.DeleteServlet</servlet-class>  </servlet>  <servlet-mapping>  	<servlet-name>DeleteServlet</servlet-name>  	<url-pattern>/deleteServlet</url-pattern>  </servlet-mapping>      <servlet>  	<servlet-name>AddServlet</servlet-name>  	<servlet-class>com.servlet.AddServlet</servlet-class>  </servlet>  <servlet-mapping>  	<servlet-name>AddServlet</servlet-name>  	<url-pattern>/addServlet</url-pattern>  </servlet-mapping>  </web-app>
******************Bean類**********************
Student.java
package com.bean;public class Student {	PRivate int id;	private String username;	private String password;	private int sex;	private String address;	public int getId() {		return id;	}	public void setId(int id) {		this.id = id;	}	public String getUsername() {		return username;	}	public void setUsername(String username) {		this.username = username;	}	public String getPassword() {		return password;	}	public void setPassword(String password) {		this.password = password;	}	public int getSex() {		return sex;	}	public void setSex(int sex) {		this.sex = sex;	}	public String getAddress() {		return address;	}	public void setAddress(String address) {		this.address = address;	}}
******************Servlet類**********************LoginServlet.java
package com.servlet;import java.io.IOException;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import javax.servlet.ServletException;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;public class LoginServlet extends HttpServlet {		 protected void doGet(HttpServletRequest request,              HttpServletResponse response) throws ServletException, IOException {  		//第一步，先把字符集設定成utf-8，否則萬一瀏覽器送來的是中文就會出現亂碼		request.setCharacterEncoding("utf-8");		 		//先把瀏覽器輸入的用戶名、密碼拿出來。         String un = request.getParameter("username");        String pw = request.getParameter("password");                //把用戶名設置到request的屬性里，這樣得會兒跳轉到歡迎或者錯誤頁面時好知道叫什么        request.setAttribute("username", un);                //此時開始在數據庫中搜尋有沒有同時滿足用戶名、密碼的行，有就對，沒有就錯：        //先加載lib目錄下的java-connect-MySQL.jar驅動包		try {			Class.forName("com.mysql.jdbc.Driver");						//選擇驅動類，連接地址、賬號密碼，連接MySQL			String driverClass="com.mysql.jdbc.Driver";			String url="jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf-8";			String sqlusername="root";			String sqlpassword="root";			Connection conn = DriverManager.getConnection(url, sqlusername,  			        sqlpassword);						//編寫SQL語句，這里不要用statement了，換用preparedstatement，因為			//preparedstatement可以設置？為形參，然后set各個形參的實際值，statement沒有此			//功能。最后執行更新語句。			//此外，？不要加單雙引號，否則報錯java.sql.SQLException: Parameter index out of range (0 < 1 ).			String sql = "select * from student where username = ? and password = ?";  			PreparedStatement ps = conn.prepareStatement(sql);  	        ps.setString(1, un);	        ps.setString(2, pw);	        ResultSet rs=ps.executeQuery();	        	        if(rs.next()){	        	request.getRequestDispatcher("welcome.jsp").forward(request, response);	        }else{	        	request.getRequestDispatcher("error.jsp").forward(request, response);	        }	        		} catch (ClassNotFoundException | SQLException e) {			e.printStackTrace();		}      }        protected void doPost(HttpServletRequest request,              HttpServletResponse response) throws ServletException, IOException {          doGet(request, response);      }  }
FindAllServlet.java
package com.servlet;import java.io.IOException;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.util.ArrayList;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.bean.Student;public class FindAllServlet extends HttpServlet{		//重寫doGet方法	protected void doGet(HttpServletRequest request,          HttpServletResponse response) throws ServletException, IOException {  				try {			//先加載lib目錄下的java-connect-mysql.jar驅動包			Class.forName("com.mysql.jdbc.Driver");  						//選擇驅動類，連接地址、賬號密碼，連接MySQL			String driverClass="com.mysql.jdbc.Driver";			String url="jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf-8";			String sqlusername="root";			String sqlpassword="root";			Connection conn = DriverManager.getConnection(url, sqlusername,  			        sqlpassword);									//編寫SQL語句，執行，拿到結果集			String sql = "select * from student";  			Statement statement = conn.createStatement();            ResultSet resultSet = statement.executeQuery(sql);                        //把結果集的東西倒進ArrayList            List<Student> ss = new ArrayList<Student>();            while(resultSet.next()){            	Student s = new Student();				s.setId(resultSet.getInt("id"));				s.setUsername(resultSet.getString("username"));				s.setPassword(resultSet.getString("password"));				s.setSex(resultSet.getInt("sex"));				s.setAddress(resultSet.getString("address"));								//后臺打印，試看有沒有拿到				/*String ms = resultSet.getInt("id")+resultSet.getString("username");				System.out.println(ms);*/								ss.add(s);            }                        //ArrayList放進request的屬性里，這樣jsp頁面就能request.getAttribute("ss")            //拿出ArrayList了。            request.setAttribute("ss", ss);              resultSet.close();            statement.close();					} catch (SQLException | ClassNotFoundException e) {			// TODO Auto-generated catch block			e.printStackTrace();		}						//跳轉到顯示頁面		request.getRequestDispatcher("listStudent.jsp")      	.forward(request, response);     }        protected void doPost(HttpServletRequest request,              HttpServletResponse response) throws ServletException, IOException {          doGet(request, response);      }  }
AddServlet.java
package com.servlet;import java.io.IOException;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.util.ArrayList;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.bean.Student;public class AddServlet extends HttpServlet{		//重寫doGet方法	protected void doGet(HttpServletRequest request,          HttpServletResponse response) throws ServletException, IOException {  		request.setCharacterEncoding("utf-8");				//從jsp頁面獲取要添加的新對象的各項值		int id = Integer.valueOf(request.getParameter("id"));		String username = request.getParameter("username");		String password = request.getParameter("password");		int sex = Integer.valueOf(request.getParameter("sex"));		String address = request.getParameter("address");						try {			//先加載lib目錄下的java-connect-mysql.jar驅動包			Class.forName("com.mysql.jdbc.Driver");  						//選擇驅動類，連接地址、賬號密碼，連接MySQL			String driverClass="com.mysql.jdbc.Driver";			String url="jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf-8";			String sqlusername="root";			String sqlpassword="root";			Connection conn = DriverManager.getConnection(url, sqlusername,  			        sqlpassword);						//編寫SQL語句，這里不要用statement了，換用preparedstatement，因為			//preparedstatement可以設置？為形參，然后set各個形參的實際值，statement沒有此			//功能。最后執行更新語句。			//此外，？不要加單雙引號，否則報錯java.sql.SQLException: Parameter index out of range (0 < 1 ).			String sql = "insert into student values("+id+",'"+username+"','"+password+"',"+sex+",'"+address+"')";  			PreparedStatement ps = conn.prepareStatement(sql);              ps.executeUpdate();                        //關閉連接            ps.close();              conn.close();  					} catch (SQLException | ClassNotFoundException e) {			// TODO Auto-generated catch block			e.printStackTrace();		}						//跳轉到顯示頁面		response.sendRedirect("findAllServlet");		/*request.getRequestDispatcher("listStudent.jsp")      	.forward(request, response); */    }        protected void doPost(HttpServletRequest request,              HttpServletResponse response) throws ServletException, IOException {          doGet(request, response);      }  }
DeleteServlet.java
package com.servlet;import java.io.IOException;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.util.ArrayList;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.bean.Student;/** * 寫DeleteServlet時遇到極大的困難，代碼都對，前臺jsp里<a href="deleteServlet?id=<%=s.getId()%>">刪除</a> * 對，后臺接收id的值，然后刪除也對，但是就是進不來servlet，前臺提示找不到404。后來關了服務器、清理、重啟、前臺改了若干數據、刪除，問題好了。 * 可能是服務器沒有重啟的原因，也有可能是前臺的緩存造成的找不到，僅僅刷新是不夠的，緩存沒有變，必須跳轉頁面，才有新緩存。 * @author Administrator * */public class DeleteServlet extends HttpServlet{		//重寫doGet方法	protected void doGet(HttpServletRequest request,          HttpServletResponse response) throws ServletException, IOException {  				//從jsp頁面獲取要修改的元素的id和各項修改后的值		int id = Integer.valueOf(request.getParameter("id"));				try {			//先加載lib目錄下的java-connect-mysql.jar驅動包			Class.forName("com.mysql.jdbc.Driver");  						//選擇驅動類，連接地址、賬號密碼，連接MySQL			String driverClass="com.mysql.jdbc.Driver";			String url="jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf-8";			String sqlusername="root";			String sqlpassword="root";			Connection conn = DriverManager.getConnection(url, sqlusername,  			        sqlpassword);						//編寫SQL語句，這里不要用statement了，換用preparedstatement，因為			//preparedstatement可以設置？為形參，然后set各個形參的實際值，statement沒有此			//功能。最后執行更新語句。			//此外，？不要加單雙引號，否則報錯java.sql.SQLException: Parameter index out of range (0 < 1 ).			String sql = "delete from student where id = ?";  			PreparedStatement ps = conn.prepareStatement(sql);              ps.setInt(1, id);            ps.executeUpdate();                        //關閉連接            ps.close();              conn.close();  					} catch (SQLException | ClassNotFoundException e) {			// TODO Auto-generated catch block			e.printStackTrace();		}						//跳轉到顯示頁面		response.sendRedirect("findAllServlet");		/*request.getRequestDispatcher("listStudent.jsp")      	.forward(request, response); */    }        protected void doPost(HttpServletRequest request,              HttpServletResponse response) throws ServletException, IOException {          doGet(request, response);      }  }
UpdateServlet.java
package com.servlet;import java.io.IOException;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.util.ArrayList;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.bean.Student;public class UpdateServlet extends HttpServlet{		//重寫doGet方法	protected void doGet(HttpServletRequest request,          HttpServletResponse response) throws ServletException, IOException {  				request.setCharacterEncoding("utf-8");				//從jsp頁面獲取要修改的元素的id和各項修改后的值		int id = Integer.valueOf(request.getParameter("id"));		String username = request.getParameter("username");		String password = request.getParameter("password");		int sex = Integer.valueOf(request.getParameter("sex"));		String address = request.getParameter("address");				try {			//先加載lib目錄下的java-connect-mysql.jar驅動包			Class.forName("com.mysql.jdbc.Driver");  						//選擇驅動類，連接地址、賬號密碼，連接MySQL			String driverClass="com.mysql.jdbc.Driver";			String url="jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf-8";			String sqlusername="root";			String sqlpassword="root";			Connection conn = DriverManager.getConnection(url, sqlusername,  			        sqlpassword);						//編寫SQL語句，這里不要用statement了，換用preparedstatement，因為			//preparedstatement可以設置？為形參，然后set各個形參的實際值，statement沒有此			//功能。最后執行更新語句。			//此外，？不要加單雙引號，否則報錯java.sql.SQLException: Parameter index out of range (0 < 1 ).			String sql = "update student set username = ?, password = ?, sex = ?, address = ? where id = ?";  			PreparedStatement ps = conn.prepareStatement(sql);              ps.setString(1, username);            ps.setString(2, password);            ps.setInt(3, sex);            ps.setString(4, address);            ps.setInt(5, id);            ps.executeUpdate();                        //關閉連接            ps.close();              conn.close();  					} catch (SQLException | ClassNotFoundException e) {			// TODO Auto-generated catch block			e.printStackTrace();		}						//跳轉到顯示頁面		response.sendRedirect("findAllServlet");		/*request.getRequestDispatcher("listStudent.jsp")      	.forward(request, response); */    }        protected void doPost(HttpServletRequest request,              HttpServletResponse response) throws ServletException, IOException {          doGet(request, response);      }  }