<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ page import="java.sql.*, java.io.*, javax.servlet.*, javax.servlet.http.*" %>
<html>
<head>
<title>插入数据到数据库</title>
</head>
<body>
<%
request.setCharacterEncoding("UTF-8");
String name = request.getParameter("name");
String sex = request.getParameter("sex");
String birthday = request.getParameter("birthday");
String department = request.getParameter("department");
String role = request.getParameter("role");
String password = request.getParameter("password");
String message = "";
Connection conn = null;
PreparedStatement pstmt = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/gs?useSSL=false";
String username = "root";
String password1 = "123456"; // 修改为你的数据库密码
conn = DriverManager.getConnection(url, username, password1);
// 查询当前数据库中最大的工号
String getMaxIdQuery = "SELECT MAX(SUBSTRING(id, 5) + 0) AS max_id FROM employee WHERE id LIKE '2019%'";
pstmt = conn.prepareStatement(getMaxIdQuery);
ResultSet maxIdResult = pstmt.executeQuery();
int maxId = 0;
if (maxIdResult.next()) {
maxId = maxIdResult.getInt("max_id");
}
// 生成下一个工号
String newId = "2019" + String.format("%04d", (maxId + 1));
// 检查是否存在相同的工号
String checkQuery = "SELECT * FROM employee WHERE id=?";
pstmt = conn.prepareStatement(checkQuery);
pstmt.setString(1, newId);
ResultSet existingRecord = pstmt.executeQuery();
if (existingRecord.next()) {
// 如果存在相同的工号，则不执行插入操作
message = "工号 " + newId + " 已存在，不能重复添加。";
} else {
// 执行插入操作
String insertQuery = "INSERT INTO employee (id, name, sex, birthday, department, role, password) VALUES (?, ?, ?, ?, ?, ?, ?)";
pstmt = conn.prepareStatement(insertQuery);
pstmt.setString(1, newId);
pstmt.setString(2, name);
pstmt.setString(3, sex);
pstmt.setString(4, birthday);
pstmt.setString(5, department);
pstmt.setString(6, role);
pstmt.setString(7, password);
int rowsAffected = pstmt.executeUpdate();
if (rowsAffected > 0) {
message = "插入成功！";
} else {
message = "插入失败";
}
}
} catch (SQLException | ClassNotFoundException e) {
message = "发生错误: " + e.getMessage();
e.printStackTrace();
} finally {
// 关闭连接和声明
try {
if (pstmt != null) {
pstmt.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
%>
<h2>插入结果：</h2>
<p><%= message %></p>
</body>
</html>