|
|
楼主 |
发表于 2023-4-29 18:55:41
|
显示全部楼层
本帖最后由 她与晚风 于 2023-4-29 19:00 编辑
package bj;
import MyUtil.DepartmentUser;
import MyUtil.EmoloyeeUser;
import jakarta.servlet.ServletException;
import jakarta.servlet.annotation.WebServlet;
import jakarta.servlet.http.HttpServlet;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;
import jakarta.servlet.http.HttpSession;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
@WebServlet({"/dept/List","/dept/Delete","/dept/Detail","/dept/Update","/dept/login"})
public class ServletList extends HttpServlet {
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
String sql;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/myemployees","root","123456");
connection.setAutoCommit(false);
if("/dept/List".equals(request.getServletPath())){
HttpSession session = request.getSession(false);
if(session == null || session.getAttribute("username") == null){
System.out.println("------------------------------------");
//request.getRequestDispatcher("/index.jsp").forward(request,response);
response.sendRedirect(request.getContextPath() + "/index.jsp");
}
ArrayList<DepartmentUser> arrayList = new ArrayList<>();
sql = "select department_id,department_name,manager_id,city from departments e left join locations l on e.location_id = l.location_id";
preparedStatement = connection.prepareStatement(sql);
resultSet = preparedStatement.executeQuery();
while (resultSet.next()){
String department_id = resultSet.getString("department_id");
String department_name = resultSet.getString("department_name");
String manager_id = resultSet.getString("manager_id");
String city = resultSet.getString("city");
DepartmentUser departmentUser = new DepartmentUser(department_id,department_name,manager_id,city);
arrayList.add(departmentUser);
}
request.setAttribute("list",arrayList);
connection.setAutoCommit(true);
request.getRequestDispatcher("/deptList.jsp").forward(request,response);
} else if ("/dept/Delete".equals(request.getServletPath())) {
HttpSession session = request.getSession(false);
if(session == null || session.getAttribute("username") == null){
response.sendRedirect(request.getContextPath());
}
connection.setAutoCommit(false);
String department_id = request.getParameter("department_id");
sql = "delete from departments where department_id = ?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, Integer.parseInt(department_id));
int i = preparedStatement.executeUpdate();
sql = "delete from employees where department_id = ?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, Integer.parseInt(department_id));
int j = preparedStatement.executeUpdate();
connection.setAutoCommit(true);
if(i !=0 || j != 0){
System.out.println("操作成功");
}
response.sendRedirect(request.getContextPath() + "/dept/List");
} else if ("/dept/Detail".equals(request.getServletPath())) {
HttpSession session = request.getSession(false);
if(session == null || session.getAttribute("username") == null){
System.out.println("------------------------------------");
response.sendRedirect(request.getContextPath() + "/index.jsp");
}
connection.setAutoCommit(false);
ArrayList arrayList = new ArrayList();
String department_id = request.getParameter("department_id");
sql = "select employee_id,last_name,job_id,manager_id,department_id,hiredate from employees where department_id = ?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, Integer.parseInt(department_id));
resultSet = preparedStatement.executeQuery();
while (resultSet.next()){
String employee_id= resultSet.getString("employee_id");
String last_name = resultSet.getString("last_name");
String job_id = resultSet.getString("job_id");
String manager_id = resultSet.getString("manager_id");
department_id = resultSet.getString("department_id");
String hiredate = resultSet.getString("hiredate");
EmoloyeeUser emoloyeeUser = new EmoloyeeUser(employee_id,last_name,job_id,manager_id,department_id,hiredate);
arrayList.add(emoloyeeUser);
}
connection.setAutoCommit(true);
request.setAttribute("detail",arrayList);
request.getRequestDispatcher("/deptDetail.jsp").forward(request,response);
} else if ("/dept/Update".equals(request.getServletPath())) {
HttpSession session = request.getSession(false);
if(session == null || session.getAttribute("username") == null){
response.sendRedirect(request.getContextPath());
}
connection.setAutoCommit(false);
String department_id = request.getParameter("department_id");
System.out.println(department_id);
String department_name = request.getParameter("department_name");
String manager_id = request.getParameter("manager_id");
String city = request.getParameter("city");
sql = "update departments set department_name = ?,manager_id = ? where department_id = ?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,department_name);
preparedStatement.setInt(2, Integer.parseInt(manager_id));
preparedStatement.setInt(3, Integer.parseInt(department_id));
int j = preparedStatement.executeUpdate();
sql = "update locations set city = ? where location_id = (select location_id from departments where department_id = ?)";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,city);
preparedStatement.setInt(2, Integer.parseInt(department_id));
int i = preparedStatement.executeUpdate();
if(i != 0 && j != 0){
System.out.println("修改成功");
}
connection.setAutoCommit(true);
response.sendRedirect(request.getContextPath() + "/dept/List");
} else if ("/dept/login".equals(request.getServletPath())) {
HttpSession session = request.getSession();
String username = request.getParameter("username");
String password = request.getParameter("password");
session.setAttribute("username",username);
sql = "select * from t_login where username = ? and password = ?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,username);
preparedStatement.setInt(2, Integer.parseInt(password));
resultSet = preparedStatement.executeQuery();
if(resultSet.next()){
System.out.println("登录成功");
connection.setAutoCommit(true);
response.sendRedirect(request.getContextPath() + "/dept/List");
}else {
System.out.println("登录失败");
}
}
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
} catch (SQLException e) {
try {
connection.rollback();
} catch (SQLException ex) {
throw new RuntimeException(ex);
}
throw new RuntimeException(e);
}finally {
if(resultSet != null){
try {
resultSet.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if(preparedStatement != null){
try {
preparedStatement.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if(connection != null){
try {
connection.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
}
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request,response);
}
}
|
|
( 粤ICP备18085999号-1 | 粤公网安备 44051102000585号)
狗仔卡
发表于 2023-4-29 11:25:50
置顶卡
千斤顶
显身卡
发表于 2023-4-29 17:31:05
