JavaWeb (五) Jsp+Servlet+MYSQL实现简单的增删改查
项目结构目录：
项目环境:
IDEA
JDK 1.8
Tomcat 9
Mysql5.7
MysqlWorkBench 6.3
数据库相关配置如下：
1 create table t_user 2 ( 3 uid int(10) not null auto_increment, 4 uname varchar(100) not null, 5 pwd varchar(100) not null, 6 primary key(uid) 7 );
源代码：
负责与数据库交互DAO层
package web.dao; import web.entity.Admin; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.ArrayList; import java.util.List; /** *adminDao.java * 直接和数据库交互 */ public class adminDao { public List<Admin> getAllAdmin(){ List<Admin> list = new ArrayList<>(); Connection conn = Dbhelper.getConnection(); String sql ="select * from t_user"; try { PreparedStatement pst = conn.prepareStatement(sql); ResultSet rs = pst.executeQuery(); while(rs.next()) { Admin admin = new Admin(); admin.setUid(rs.getInt("uid")); admin.setUname(rs.getString("uname")); admin.setPwd(rs.getString("pwd")); list.add(admin); } rs.close(); pst.close(); }catch (Exception e) { e.printStackTrace(); } return list; } public boolean addAdmin(Admin admin) { String sql = "insert into t_user(uid,uname,pwd) values(default ,?,?)"; Connection connection = Dbhelper.getConnection(); try{ PreparedStatement pst = connection.prepareStatement(sql); pst.setString(1,admin.getUname()); pst.setString(2,admin.getPwd()); int count = pst.executeUpdate(); pst.close(); return count>0?true:false; }catch (Exception e){ e.printStackTrace(); } return false; } public boolean updateAdmin(Admin admin) { String sql = "update t_user set uname=?,pwd=? where id=?"; Connection connection = Dbhelper.getConnection(); try{ PreparedStatement pst = connection.prepareStatement(sql); pst.setString(1,admin.getUname()); pst.setString(2,admin.getPwd()); pst.setInt(3,admin.getUid()); int count = pst.executeUpdate(); pst.close(); return count>0?true:false; }catch (Exception e) { e.printStackTrace(); } return false; } public boolean deleteAdmin(int uid) { String sql = "delete from t_user where uid = ?"; Connection connection = Dbhelper.getConnection(); try { PreparedStatement pst = connection.prepareStatement(sql); pst.setInt(1,uid); int count = 0; count = pst.executeUpdate(); pst.close(); return count>0?true:false; }catch (Exception e) { e.printStackTrace(); } return false; } public Admin selectAdminByUid(int uid) { Connection connection = Dbhelper.getConnection(); String sql = "select * from t_user where uid = "+ uid; Admin admin =null; try { PreparedStatement pst = connection.prepareStatement(sql); ResultSet resultSet = pst.executeQuery(); while (resultSet.next()) { admin= new Admin(); admin.setUname(resultSet.getString("uname")); admin.setUid(resultSet.getInt("uid")); admin.setPwd(resultSet.getString("pwd")); } resultSet.close(); pst.close(); }catch (Exception e) { e.printStackTrace(); } return admin; } }
package web.dao; import java.sql.Connection; import java.sql.DriverManager; /** *Dbhelper.java * 连接数据库 */ public class Dbhelper { private static String URL = "jdbc:mysql://localhost:3306/roombase"; // 数据库地址 private static String userName = "root"; private static String passWord = "zxc2505004"; private static String DRIVER = "com.mysql.jdbc.Driver"; private static Connection conn = null; public Dbhelper() { } /** * 获取连接 * @return */ public static Connection getConnection() { if(null == conn){ try { Class.forName(DRIVER); conn = DriverManager.getConnection(URL,userName,passWord); }catch (Exception e) { e.printStackTrace(); } } return conn; } /** *测试数据库是否联通 * @param args */ public void main(String []args) { System.err.println(getConnection()); } }
ENTITY层：
package web.entity; import java.io.Serializable; public class Admin implements Serializable { private static final long serialVersinID = 1L; private int uid; private String uname; private String pwd; public static long getSerialVersinID() { return serialVersinID; } public int getUid() { return uid; } public void setUid(int uid) { this.uid = uid; } public String getUname() { return uname; } public void setUname(String uname) { this.uname = uname; } public String getPwd() { return pwd; } public void setPwd(String pwd) { this.pwd = pwd; } @Override public String toString() { return "Admin{" + "uid=" + uid + ", uname='" + uname + '\'' + ", pwd='" + pwd + '\'' + '}'; } }
Controller层：
package web.web.servlet; import web.dao.adminDao; import web.entity.Admin; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import java.io.IOException; /** * 请求控制层 *AddServlet.java */ public class AddServlet extends HttpServlet { private static final long serialVersionUID = 1L; @Override protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { this.doPost(req,resp); } @Override protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { String uname = req.getParameter("uname"); String pwd = req.getParameter("pwd"); Admin admin = new Admin(); admin.setUname(new String(uname.getBytes("ISO-8859-1"),"UTF-8")); admin.setPwd(new String(pwd.getBytes("ISO-8859-1"),"UTF-8")); adminDao dao = new adminDao(); dao.addAdmin(admin); req.getRequestDispatcher("ShowServlet").forward(req,resp);//内部重定向 } }
package web.web.servlet; import web.dao.adminDao; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import java.io.IOException; /** * deleteServlet.java */ public class deleteServlet extends HttpServlet { @Override protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { this.doPost(req,resp); } @Override protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { String idStr = req.getParameter("uid"); if(idStr!=null&&!idStr.equals("")) { int id = Integer.valueOf(idStr); adminDao dao = new adminDao(); dao.deleteAdmin(id); } req.getRequestDispatcher("ShowServlet").forward(req,resp); } }
package web.web.servlet; import web.dao.adminDao; import web.entity.Admin; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.util.List; /** * ShowServlet.java */ public class ShowServlet extends HttpServlet { private static final long serialVersionUID = 1L; protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { this.doPost(req, resp); } protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { adminDao dao = new adminDao(); List<Admin> list = dao.getAllAdmin(); req.setAttribute("list", list); req.getRequestDispatcher("index.jsp").forward(req, resp); } }
package web.web.servlet; import web.dao.adminDao; import web.entity.Admin; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import java.io.IOException; /** * UpdateServlet.JAVA */ public class UpdateServlet extends HttpServlet { @Override protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { String idStr = req.getParameter("uid"); if (idStr != null && !idStr.equals("")) { int id = Integer.valueOf(idStr); adminDao dao = new adminDao(); Admin admin = dao.selectAdminByUid(id); req.setAttribute("admin", admin); } req.getRequestDispatcher("update.jsp").forward(req, resp); } @Override protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { String uname = req.getParameter("uname"); String pwd = req.getParameter("pwd"); String idStr = req.getParameter("uid"); Admin admin = new Admin(); admin.setUid(Integer.valueOf(idStr)); admin.setUname(new String(uname.getBytes("ISO-8859-1"),"UTF-8")); admin.setPwd(new String(pwd.getBytes("ISO-8859-1"),"UTF-8")); adminDao dao = new adminDao(); dao.updateAdmin(admin); req.getRequestDispatcher("ShowServlet").forward(req, resp); } }
JSP页面:
add.jsp
<%-- Created by IntelliJ IDEA. User: ZHENGZHIQIANG Date: 2019/3/2 Time: 16:14 To change this template use File | Settings | File Templates. --%> <%@ page contentType="text/html;charset=UTF-8" language="java" %> <html> <head> <title>ADD</title> </head> <body> <form action="AddServlet" method="post"> <table border="1" > <tr> <td colspan="2"><h1>Add Administrator</h1></td> </tr> <tr> <td>Add Account number </td> <td><input type="text" name="uname"/></td> </tr> <tr> <td>Administrator Password</td> <td><input type="password" name="pwd"/></td> </tr> <tr> <td colspan="2"> <input type="submit" value="Submint"> <input type="reset" value="Flush"> </td> </tr> </table> </form> </body> </html>
index.jsp
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %> <%-- Created by IntelliJ IDEA. User: ZHENGZHIQIANG Date: 2019/3/2 Time: 9:59 To change this template use File | Settings | File Templates. --%> <%@ page contentType="text/html;charset=UTF-8" language="java" %> <html> <head> <title>Show</title> <style type="text/css"> table { border: 1px solid pink; margin: 0 auto; td{ width: 150px; border: 1px solid pink; text-align: center; } </style> </head> <body> <table> <tr> <td>NUMBER</td> <td>ACCOUNT NUMBER</td> <td>PASSWORD</td> <td>OPERATER</td> </tr> <c:forEach items="${list}" var="item"> <tr> <td>${item.uid}</td> <td>${item.uname}</td> <td>${item.pwd}</td> <td><a href="DeleteServlet?uid=${item.uid}">DELETE</a>-----<a href="UpdateServlet?uid=${item.uid}">EDIT</a> </td> </tr> </c:forEach> <tr> <td colspan="2" style="text-align: left"><a href="add.jsp">ADD administrator</a> </td> </tr> </table> </body> </html>
update.jsp
<%-- Created by IntelliJ IDEA. User: ZHENGZHIQIANG Date: 2019/3/2 Time: 16:34 To change this template use File | Settings | File Templates. --%> <%@ page contentType="text/html;charset=UTF-8" language="java" %> <html> <head> <title>Edit</title> </head> <body> <form action="UpdateServlet" method="post" style="align-items: center"> <table border="1" > <tr> <td colspan="2"><h1>Edit administrator info</h1></td> </tr> <tr> <td>uid</td> <td><input type="text" name="uid" value="${admin.uid}" readonly="readonly"/></td> </tr> <tr> <td>uname</td> <td><input type="text" name="uid" value="${admin.uname}" /></td> </tr> <tr> <td>pwd</td> <td><input type="text" name="uid" value="${admin.pwd}" /></td> </tr> <tr> <td colspan="2"> <input type="submit" value="Submit"/> <input type="button" value="Back" onclick="history.go(-1)"/> </td> </tr> </table> </form> </body> </html>
XML配置文件：
<?xml version="1.0" encoding="UTF-8"?> <web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_4_0.xsd" version="4.0"> <display-name>WebBasic</display-name> <welcome-file-list> <welcome-file>index.html</welcome-file> <welcome-file>index.jsp</welcome-file> </welcome-file-list> <servlet> <servlet-name>AddServlet</servlet-name> <servlet-class>web.web.servlet.AddServlet</servlet-class> </servlet> <servlet-mapping> <servlet-name>AddServlet</servlet-name> <url-pattern>/AddServlet</url-pattern> </servlet-mapping> <servlet> <servlet-name>DeleteServlet</servlet-name> <servlet-class>web.web.servlet.deleteServlet</servlet-class> </servlet> <servlet-mapping> <servlet-name>DeleteServlet</servlet-name> <url-pattern>/DeleteServlet</url-pattern> </servlet-mapping> <servlet> <servlet-name>UpdateServlet</servlet-name> <servlet-class>web.web.servlet.UpdateServlet</servlet-class> </servlet> <servlet-mapping> <servlet-name>UpdateServlet</servlet-name> <url-pattern>/UpdateServlet</url-pattern> </servlet-mapping> <servlet> <servlet-name>ShowServlet</servlet-name> <servlet-class>web.web.servlet.ShowServlet</servlet-class> </servlet> <servlet-mapping> <servlet-name>ShowServlet</servlet-name> <url-pattern>/ShowServlet</url-pattern> </servlet-mapping> </web-app>
源代码如下：
