Class exercise, no service and servlet written
Database
SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; ---------------------------- -- Table structure for students ---------------------------- DROP TABLE IF EXISTS `student`; CREATE TABLE `student` ( `stuid` int NOT NULL, `stuname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL, `stusex` int NULL DEFAULT NULL, PRIMARY KEY (`stuid`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic; ---------------------------- -- Records of students ---------------------------- INSERT INTO `student` VALUES (222, 'aaa', 0); INSERT INTO `student` VALUES (211164301, 'Zhang Nianzu', 0); INSERT INTO `student` VALUES (211164302, '杨天宇', 1); INSERT INTO `student` VALUES (211164303, 'Zhang Shuai', 1); INSERT INTO `student` VALUES (211164304, '李文豪', 1); SET FOREIGN_KEY_CHECKS = 1;
index.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="java.sql.*"%>
<%@ page import="com.dao.UserDao" %>
<%@ page import="com.dao.impl.UserDaoImpl" %>
<%@ page import="java.util.List" %>
<%@ page import="com.entity.User" %>
<html>
<head>
<title>Open data table through JSP</title>
</head>
<body>
<center>
<table border="1px" style="background: #7fd6ff">
<tr>
<td>id</td>
<td>Name</td>
<td>Gender</td>
<td>Delete</td>
<td>Modify</td>
</tr>
<%
UserDao ud = new UserDaoImpl();
List<User> userList = ud.findAllUserList();
for (User user : userList) {
out.println("<tr>" +
"<td>" + user.getStuid() + "</td>" +
"<td>" + user.getStuname() + "</td>" +
"<td>" + (user.getStusex()==1?"Male":"Female") + "</td>" +
"<td><a href='deleteUser.jsp?stuid=" + user.getStuid() + "'>Delete</a></td>" +
"<td><a href='updateUser.jsp?stuid=" + user.getStuid() + " & amp;stuname=" + user.getStuname() + " & amp;stusex=" + user.getStusex() + "'>Modify</a></td>" +
"</tr>");
}
%>
</table>
</center>
</body>
</html>
update.jsp
<%@ page import="com.dao.impl.UserDaoImpl" %>
<%@ page import="com.dao.UserDao" %>
<%@ page import="com.entity.User" %><%--
Created by IntelliJ IDEA.
User: 53172
Date: 2023/11/8
Time: 9:05
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>Title</title>
</head>
<body>
<%
String stuid = request.getParameter("stuid");
String stuname = request.getParameter("stuname");
String stusex = request.getParameter("stusex");
stusex=(stusex.equals("male")?"1":"0");
UserDao ud = new UserDaoImpl();
ud.updateUser(new User(Integer.parseInt(stuid),stuname,Integer.parseInt(stusex)));
response.sendRedirect("index.jsp");
%>
</body>
</html>
updateUser.jsp
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>Update user</title>
</head>
<body>
<%
String stuid = request.getParameter("stuid");
String stuname = request.getParameter("stuname");
String stusex = request.getParameter("stusex");
String sex = stusex.equals("1")?"male":"female";
%>
<form action="update.jsp" method="post">
<input value="<%=stuid%>" name="stuid" type="hidden">
Name:<input value="<%=stuname%>" name="stuname"><br/>
Gender:<input value="<%=sex%>" name="stusex"><br/>
<input type="submit">
</form>
</body>
</html>
deleteUser.jsp
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>Delete user</title>
</head>
<body>
<%
String stuid = request.getParameter("stuid");
UserDao ud = new UserDaoImpl();
ud.deleteUser(Integer.parseInt(stuid));
response.sendRedirect("index.jsp");
%>
</body>
</html>
jdbc.properties
driver=com.mysql.cj.jdbc.Driver url=jdbc:mysql://127.0.0.1:3306/test uname=root upwd=123456
BaseDao.java
package com.dao.impl;
import com.dao.BaseDao;
import com.dao.UserDao;
import com.entity.User;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class UserDaoImpl extends BaseDao implements UserDao {
public List<User> findAllUserList() {
getConnection();
List<User> userList=new ArrayList<>();
try {
String sql = "select * from student";
ResultSet rs = selectSql(sql,null);
while (rs.next()){
User user = new User();
user.setStuid(rs.getInt("stuid"));
user.setStuname(rs.getString("stuname"));
user.setStusex(rs.getInt("stusex"));
userList.add(user);
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
closeConnection();
return userList;
}
@Override
public int deleteUser(int stuid) {
getConnection();
String sql = "delete from student where student = ?";
int i = editSql(sql, new Object[]{stuid});
closeConnection();
return i;
}
@Override
public int updateUser(User user) {
//Modify user information
getConnection();
String sql = "update student set stuname = ?,stusex = ? where stuid = ?";
int i = editSql(sql, new Object[]{user.getStuname(),user.getStusex(),user.getStuid()});
closeConnection();
return i;
}
}
UserDao.java
package com.dao;
import com.entity.User;
import java.util.List;
public interface UserDao {
public List<User> findAllUserList();
public int deleteUser(int stuid);
public int updateUser(User user);
}
User.java
package com.entity;
public class User {
int stuid,stusex;
String stuname;
public User(){};
public User(int stuid, String stuname, int stusex) {
this.stuid = stuid;
this.stuname = stuname;
this.stusex = stusex;
}
public int getStuid() {
return stuid;
}
public void setStuid(int stuid) {
this.stuid = stuid;
}
public String getStuname() {
return stuname;
}
public void setStuname(String Stuname) {
this.stuname = stuname;
}
public int getStusex() {
return stusex;
}
public void setStusex(int stusex) {
this.stusex = stusex;
}
@Override
public String toString() {
return "User{" +
"stuid='" + stuid + ''' +
", stuname='" + stuname + ''' +
", stusex='" + stusex + ''' +
'}';
}
}
UserDaoImpl.java
package com.dao.impl;
import com.dao.BaseDao;
import com.dao.UserDao;
import com.entity.User;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class UserDaoImpl extends BaseDao implements UserDao {
public List<User> findAllUserList() {
getConnection();
List<User> userList=new ArrayList<>();
try {
String sql = "select * from student";
ResultSet rs = selectSql(sql,null);
while (rs.next()){
User user = new User();
user.setStuid(rs.getInt("stuid"));
user.setStuname(rs.getString("stuname"));
user.setStusex(rs.getInt("stusex"));
userList.add(user);
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
closeConnection();
return userList;
}
@Override
public int deleteUser(int stuid) {
getConnection();
String sql = "delete from student where student = ?";
int i = editSql(sql, new Object[]{stuid});
closeConnection();
return i;
}
@Override
public int updateUser(User user) {
//Modify user information
getConnection();
String sql = "update student set stuname = ?,stusex = ? where stuid = ?";
int i = editSql(sql, new Object[]{user.getStuname(),user.getStusex(),user.getStuid()});
closeConnection();
return i;
}
}