基于Java实现的排课系统.zip

package cn.edu.sanxiau.www.dao.impl; import java.io.IOException; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import cn.edu.sanxiau.www.dao.ClassRoomDao; import cn.edu.sanxiau.www.model.ClassRoom; import cn.edu.sanxiau.www.model.PaiKe; import cn.edu.sanxiau.www.model.PaiRoom; import cn.edu.sanxiau.www.model.RoomCourse; import cn.edu.sanxiau.www.model.User2; import frame.utils.dbutil.JdbcUtils; public class ClassRoomDaoImpl implements ClassRoomDao { @Override public List<ClassRoom> queryAllClassroom() { Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; // 查询数据库 try { conn = JdbcUtils.getConnection(); String sql = "SELECT * FROM classroom"; pstmt = conn.prepareStatement(sql); rs = pstmt.executeQuery(); List<ClassRoom> classroom_db_list = new ArrayList<ClassRoom>(); while (rs.next()) { ClassRoom classroom_db = new ClassRoom(); classroom_db.setRoomId(rs.getInt("roomId")); classroom_db.setRoomName(rs.getString("roomName")); classroom_db.setCapacity(rs.getString("capacity")); classroom_db.setState(rs.getString("state")); classroom_db_list.add(classroom_db); } return classroom_db_list; } catch (ClassNotFoundException | IOException | SQLException e) { e.printStackTrace(); } return null; } @Override public int addClassRoomByClassRoomr(ClassRoom classroom) { Connection conn = null; PreparedStatement pstmt = null; try { conn = JdbcUtils.getConnection(); String sql = "INSERT INTO classroom (roomId,roomName,capacity,state) VALUES(?,?,?,?)"; pstmt = conn.prepareStatement(sql); pstmt.setInt(1, classroom.getRoomId()); pstmt.setString(2, classroom.getRoomName()); pstmt.setString(3, classroom.getCapacity()); pstmt.setString(4, classroom.getState()); int m = pstmt.executeUpdate(); return m; } catch (Exception e) { e.printStackTrace(); } finally { try { pstmt.close(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } return 0; } @Override public int deleteClassRoomByClassRoomId(int roomId) { Connection conn = null; PreparedStatement pstmt = null; try { conn = JdbcUtils.getConnection(); String sql = "DELETE FROM classroom WHERE roomId=?"; pstmt = conn.prepareStatement(sql); pstmt.setInt(1, roomId); int m = pstmt.executeUpdate(); return m; } catch (Exception e) { e.printStackTrace(); } return 0; } @Override public ClassRoom queryClassRoomByClassRoomId(int roomId) { Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; // 查询数据库 try { conn = JdbcUtils.getConnection(); String sql = "SELECT * FROM classroom WHERE roomId=?"; pstmt = conn.prepareStatement(sql); pstmt.setInt(1, roomId); rs = pstmt.executeQuery(); while (rs.next()) { ClassRoom classroom_db = new ClassRoom(); classroom_db.setRoomId(rs.getInt("roomId")); classroom_db.setRoomName(rs.getString("roomName")); classroom_db.setCapacity(rs.getString("capacity")); classroom_db.setState(rs.getString("state")); return classroom_db; } } catch (ClassNotFoundException | IOException | SQLException e) { e.printStackTrace(); } return null; } @Override public int updateClassRoomByClassRoom(ClassRoom classroom) { Connection conn = null; PreparedStatement pstmt = null; try { conn = JdbcUtils.getConnection(); String sql = "UPDATE classroom SET roomName=?,capacity=?,state=? WHERE roomId=?"; pstmt = conn.prepareStatement(sql); pstmt.setString(1, classroom.getRoomName()); pstmt.setString(2, classroom.getCapacity()); pstmt.setString(3, classroom.getState()); pstmt.setInt(4, classroom.getRoomId()); int m = pstmt.executeUpdate(); return m; } catch (Exception e) { e.printStackTrace(); } return 0; } @Override public int queryClassRoomTotalRecord() { Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); String sql = "SELECT count(*) FROM classroom"; pstmt = conn.prepareStatement(sql); rs = pstmt.executeQuery(); if (rs.next()) { int count = rs.getInt(1); return count; } } catch (Exception e) { e.printStackTrace(); } return 0; } @Override public List<ClassRoom> queryCurrentPageDataList(int i, int ps) { Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); String sql = "SELECT * FROM classroom LIMIT ?,?"; pstmt = conn.prepareStatement(sql); pstmt.setInt(1, i); pstmt.setInt(2, ps); rs = pstmt.executeQuery(); List<ClassRoom> classroomList = new ArrayList<>(); while (rs.next()) { ClassRoom classroom_db = new ClassRoom(); classroom_db.setRoomId(rs.getInt("roomId")); classroom_db.setRoomName(rs.getString("roomName")); classroom_db.setCapacity(rs.getString("capacity")); classroom_db.setState(rs.getString("state")); classroomList.add(classroom_db); } return classroomList; } catch (Exception e) { e.printStackTrace(); } return null; } ////////////////////////////////////////////////////// @Override public List<PaiKe> queryAllCourse() { Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; // 查询数据库 try { conn = JdbcUtils.getConnection(); String sql = "SELECT classroom.roomId, classroom.roomName, classroom.capacity, classroom.state, room_course.date, room_course.time FROM classroom INNER JOIN room_course ON classroom.roomId = room_course.roomId"; pstmt = conn.prepareStatement(sql); rs = pstmt.executeQuery(); List<PaiKe> paike_db_list = new ArrayList<PaiKe>(); while (rs.next()) { PaiKe paike_db = new PaiKe(); paike_db.setRoomId(rs.getInt("classroom.roomId")); paike_db.setRoomName(rs.getString("classroom.roomName")); paike_db.setCapacity(rs.getString("classroom.capacity")); paike_db.setState(rs.getString("classroom.state")); paike_db.setDate(rs.getString("room_course.date")); paike_db.setTime(rs.getString("room_course.time")); paike_db_list.add(paike_db); } return paike_db_list; } catch (ClassNotFoundException | IOException | SQLException e) { e.printStackTrace(); } return null; } @Override public List<ClassRoom> queryClassRoomByCourseId(int courseId) { Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; // 查询数据库 try { conn = JdbcUtils.getConnection(); String sql = "SELECT classroom.roomName, classroom.roomId, classroom.capacity, classroom.state FROM course INNER JOIN room_course ON course.courseId = room_course.courseId INNER JOIN classroom ON room_course.roomId = classroom.roomId where courseId=?"; pstmt = conn.prepareStatement(sql); pstmt.setInt(1, courseId); rs = pstmt.executeQuery(); List<ClassRoom> classRoom_db_list = new ArrayList<ClassRoom>(); while (rs.next()) { ClassRoom classRoom_db = new ClassRoom(); classRoom_db.setRoomId(rs.getInt("roomId")); classRoom_db.setRoomName(rs.getString("roomName")); classRoom_db.setCapacity(rs.getString("capacity")); classRoom_db.setState(rs.getString("state")); classRoom_db_list.add(classRoom_db); } return classRoom_db_list; } catch (ClassNotFoundException | IOException | SQLException e) { e.printStackTrace(); } return null; } @Override public String queryRoomNameByCourseId(int courseId) { Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; // 查询数据库 try { conn = JdbcUtils.getConnection(); String sql = "SELECT classroom.roomName FROM (classroom , course) INNER JOIN room_course ON course.courseId = room_course.courseId AND room_course.roomId = classroo