Jsp(增删改查)
数据表
DROP TABLE IF EXISTS `user`; CREATE TABLE `user` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `username` varchar(20) NOT NULL, `password` varchar(16) NOT NULL, `type` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; /*Data for the table `user` */ insert into `user`(`id`,`username`,`password`,`type`) values (1,'AAA','AAA','管理员'); insert into `user`(`id`,`username`,`password`,`type`) values (2,'BBB','BBB','管理员'); insert into `user`(`id`,`username`,`password`,`type`) values (3,'111','111','普通用户'); insert into `user`(`id`,`username`,`password`,`type`) values (4,'222','222','普通用户'); insert into `user`(`id`,`username`,`password`,`type`) values (5,'333','333','普通用户');
建立web项目 / 添加jre, tomcat / 引入包(mysql-connector-java-5.1.7-bin.jar)
编写 实体类 / DAO接口
User.java
public class User { private Integer id; private String username; private String password; private String type; // 构造器 / get/set方法省略 ... @Override public String toString() { return "ID:" + id + ", Username:" + username + ", Type:" + type; } }
IUserDao.java
public interface IUserDao { List<User> findList(); User findById(int id); int add(User user); int update(User user); int deleteById(int id); }
BaseDao.java
public class BaseDao { public static final String DRIVER = "com.mysql.jdbc.Driver"; public static final String URL = "jdbc:mysql://localhost:3306/crud_jsp"; public static final String USER = "root"; public static final String PASSWORD = "root"; // 同胞类 / 子类 protected Connection conn = null; protected PreparedStatement ps = null; protected ResultSet rs = null; // 连接DB public void getConnection() { try { Class.forName(DRIVER); conn = DriverManager.getConnection(URL, USER, PASSWORD); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } } // 关闭DB public void closeDBResources() { try { if (rs != null) rs.close(); if (ps != null) ps.close(); if (conn != null) conn.close(); } catch (SQLException e) { e.printStackTrace(); } } /** * 执行记录更新 * * @param sql * @param obj * @return */ public int executeUpdate(String sql, Object... obj) { this.getConnection(); // 连接DB int num = 0; try { ps = conn.prepareStatement(sql); for (int i = 0; i < obj.length; i++) { ps.setObject(i + 1, obj[i]); } num = ps.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } finally { this.closeDBResources(); // 关闭DB } return num; } /** * 执行数据查询 * * @param sql * @param obj * @return */ public ResultSet executeQuery(String sql, Object... obj) { this.getConnection(); // 连接DB try { ps = conn.prepareStatement(sql); for (int i = 0; i < obj.length; i++) { ps.setObject(i + 1, obj[i]); } rs = ps.executeQuery(); } catch (SQLException e) { e.printStackTrace(); } // 在所调用的方法中关闭DB return rs; } }
UserDaoImpl.java
public class UserDaoImpl extends BaseDao implements IUserDao { @Override public List<User> findList() { List<User> list = new ArrayList<User>(); rs = executeQuery("SELECT * FROM USER"); try { while (rs.next()) { User user = new User(rs.getInt("id"), rs.getString("username"), rs.getString("password"), rs.getString("type")); list.add(user); } } catch (SQLException e) { e.printStackTrace(); } finally { super.closeDBResources(); // 调用executeQuery后关闭DB } return list; } @Override public User findById(int id) { User user = null; rs = executeQuery("SELECT * FROM USER WHERE ID = ?", id); try { if (rs.next()) user = new User(rs.getInt("id"), rs.getString("username"), rs.getString("password"), rs.getString("type")); } catch (SQLException e) { e.printStackTrace(); } finally { super.closeDBResources(); // 调用executeQuery后关闭DB } return user; } @Override public int add(User user) { return executeUpdate("INSERT INTO USER(USERNAME, PASSWORD, TYPE) VALUES(?, ?, ?)", user.getUsername(), user.getPassword(), user.getType()); } @Override public int update(User user) { return executeUpdate("UPDATE USER SET USERNAME = ?, PASSWORD = ?, TYPE = ? WHERE ID = ?", user.getUsername(), user.getPassword(), user.getType(), user.getId()); } @Override public int deleteById(int id) { return executeUpdate("DELETE FROM USER WHERE ID = ?", id); } }
编写 ui/jsp 控制器/jsp
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>Insert title here</title>
</head>
<body>
<a href="${pageContext.request.contextPath }/ui/user/LoginUI.jsp">登录</a>
<a href="${pageContext.request.contextPath }/ui/user/RegistUI.jsp">注册</a>
</body>
</html>
LoginUI.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="${pageContext.request.contextPath }/jsp/ListJsp.jsp" method="post">
用户名：<input type="text" name="username" value=""><br>
<!-- -->
密 码：<input type="password" name="password" value=""><br>
<!-- -->
<input type="submit" value="登录">
<!-- -->
<input type="reset" value="取消">
<!-- -->
<a href="${pageContext.request.contextPath }/ui/user/RegistUI.jsp">注册用户</a>
</form>
</body>
</html>
ListJsp.jsp
<%@page import="org.apache.jasper.tagplugins.jstl.core.ForEach"%>
<%@page import="cn.ding.jsp.entity.User"%>
<%@page import="java.util.List"%>
<%@page import="cn.ding.jsp.dao.impl.UserDaoImpl"%>
<%@page import="cn.ding.jsp.dao.IUserDao"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme() + "://" + request.getServerName() + ":" + request.getServerPort() + path + "/";
%>
<%@ 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>ListJsp</title>
</head>
<body>
<base href="<%=basePath%>">
<%
IUserDao userDao = new UserDaoImpl();
List<User> list = userDao.findList();
application.setAttribute("userList", list);
%>
<jsp:forward page="/ui/user/ListUI.jsp"/>
</body>
</html>
ListUI.jsp
<%@page import="java.util.List"%> <%@page import="cn.ding.jsp.entity.User"%> <%@ 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> </head> <body> <table border=1> <tr> <td>用户名</td> <td>密码</td> <td>用户类型</td> <td colspan="2" align="center">数据操作</td> </tr> <% List<User> userList = (List<User>) application.getAttribute("userList"); for (int i = 0; i < userList.size(); i++) { User user = userList.get(i); %> <tr> <td><%=user.getUsername()%></td> <td><%=user.getPassword()%></td> <td><%=user.getType()%></td> <td><a href="${pageContext.request.contextPath }/jsp/DeleteJsp.jsp?id=<%=user.getId()%>">删除操作</a></td> <td><a href="${pageContext.request.contextPath }/ui/user/UpdateUI.jsp?id=<%=user.getId()%>">更新操作</a></td> </tr> <% } %> </table> <a href="${pageContext.request.contextPath }/ui/user/AddUI.jsp"> 新建用户 </a> </body> </html>
AddUI.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="${pageContext.request.contextPath }/jsp/AddJsp.jsp" method="post">
用户名：<input type="text" name="username"><br>
<!-- -->
密 码：<input type="password" name="password"><br>
<!-- -->
用户类型： <select name="type">
<option value="管理员">管理员</option>
<option value="普通用户">普通用户</option>
</select><br>
<!-- -->
<input type="submit" value="创建用户">
<!-- -->
<input type="reset" value="取消创建">
</body>
</html>
AddJsp.jsp
<%@page import="cn.ding.jsp.dao.impl.UserDaoImpl"%> <%@page import="cn.ding.jsp.dao.IUserDao"%> <% String path = request.getContextPath(); String basePath = request.getScheme() + "://" + request.getServerName() + ":" + request.getServerPort() + path + "/"; %> <%@ 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> <% request.setCharacterEncoding("utf-8"); %> <jsp:useBean id="user" class="cn.ding.jsp.entity.User"> <jsp:setProperty name="user" property="*" /><!-- 将Jsp输入的所有值 匹配Bean中的属性 --> </jsp:useBean> <% IUserDao userDao = new UserDaoImpl(); userDao.add(user); %>} <jsp:forward page="/jsp/ListJsp.jsp"/> </body> </html>
UpdateUI.jsp
<%@page import="cn.ding.jsp.entity.User"%> <%@page import="cn.ding.jsp.dao.impl.UserDaoImpl"%> <%@page import="cn.ding.jsp.dao.IUserDao"%> <%@ 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> <% IUserDao userDao = new UserDaoImpl(); int id = Integer.parseInt(request.getParameter("id")); User user = userDao.findById(id); %> <form action="${pageContext.request.contextPath }/jsp/UpdateJsp.jsp" method="post"> <input type="hidden" name="id" value="<%=user.getId() %>"/> 用户名：<input type="text" name="username" value="<%=user.getUsername() %>"><br> <!-- --> 密 码：<input type="password" name="password" value="$<%=user.getPassword() %>"><br> <!-- --> 用户类型： <select name="type"> <option value="管理员">管理员</option> <option value="普通用户">普通用户</option> </select><br> <!-- --> <input type="submit" value="修改用户"> </body> </html>
UpdateJsp.jsp
<%@page import="cn.ding.jsp.dao.impl.UserDaoImpl"%> <%@page import="cn.ding.jsp.dao.IUserDao"%> <%@ 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> </head> <body> <% request.setCharacterEncoding("utf-8"); %> <jsp:useBean id="user" class="cn.ding.jsp.entity.User"> <jsp:setProperty name="user" property="*" /><!-- 将Jsp输入的所有值 匹配Bean中的属性 --> </jsp:useBean> <% IUserDao userDao = new UserDaoImpl(); userDao.update(user); %>} <jsp:forward page="/jsp/ListJsp.jsp"/> </body> </html>
DeleteJsp.jsp
<%@page import="cn.ding.jsp.dao.impl.UserDaoImpl"%> <%@page import="cn.ding.jsp.dao.IUserDao"%> <% String path = request.getContextPath(); String basePath = request.getScheme() + "://" + request.getServerName() + ":" + request.getServerPort() + path + "/"; %> <%@ 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>DeleteByIdJsp</title> </head> <body> <% IUserDao userDao = new UserDaoImpl(); int id = Integer.parseInt(request.getParameter("id")); userDao.deleteById(id); %> <jsp:forward page="/jsp/ListJsp.jsp" /> </body> </html>
RegistUI.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="${pageContext.request.contextPath }/jsp/RegistJsp.jsp" method="post">
用户名：<input type="text" name="username" value=""><br>
<!-- -->
密 码：<input type="password" name="password" value=""><br>
<!-- -->
用户类型： <select name="type">
<option value="管理员">管理员</option>
<option value="普通用户">普通用户</option>
</select><br>
<!-- -->
<input type="submit" value="提交">
<!-- -->
<input type="reset" value="取消">
<!-- -->
<a href="${pageContext.request.contextPath }/ui/user/LoginUI.jsp">返回登录</a>
</form>
</body>
</html>
RegistJsp.jsp
<%@page import="cn.ding.jsp.dao.impl.UserDaoImpl"%> <%@page import="cn.ding.jsp.dao.IUserDao"%> <% String path = request.getContextPath(); String basePath = request.getScheme() + "://" + request.getServerName() + ":" + request.getServerPort() + path + "/"; %> <%@ 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> <% request.setCharacterEncoding("utf-8"); %> <jsp:useBean id="user" class="cn.ding.jsp.entity.User"> <jsp:setProperty name="user" property="*" /><!-- 将Jsp输入的所有值 匹配Bean中的属性 --> </jsp:useBean> <% IUserDao userDao = new UserDaoImpl(); userDao.add(user); %> <jsp:forward page="/ui/user/LoginUI.jsp"/> </body> </html>
浙公网安备 33010602011771号