javaweb期末作业-基于MySQL和Tomcat+IDEA+JSP和Servlet宾馆管理系统+文档说明（高分项目）

package tool; import config.GCON; import display.ExtensionOrderView; import display.OrderView; import display.InComeView; import entity.*; import java.sql.*; import java.util.ArrayList; import java.util.HashMap; //数据库查询功能 public class Query { //房间指定型号对应的空和非空房间数量 public static ArrayList<Integer> getNumofRoom(String roomType){ Connection connection = null; PreparedStatement preparedStatement = null; ResultSet resultSet = null; ArrayList<Integer>list =new ArrayList<Integer>() ; try{ connection = DataBase.getConnection(); preparedStatement = connection.prepareStatement( "select * from (select roomType,roomStatus ,count(*) from room group by roomType,roomStatus) c where c.roomStatus='空' and c.roomType='"+roomType+"'"); //获取结果数据集 resultSet = preparedStatement.executeQuery(); while (resultSet.next()) { list.add( resultSet.getInt("count(*)")) ; } preparedStatement = connection.prepareStatement( "select * from (select roomType,roomStatus ,count(*) from room group by roomType,roomStatus) c where c.roomStatus='非空' and c.roomType='"+roomType+"'"); //获取结果数据集 resultSet = preparedStatement.executeQuery(); while (resultSet.next()) { list.add( resultSet.getInt("count(*)")) ; } return list ; }catch(Exception exception) { exception.printStackTrace(); } return null ; } //获得已入住的orderview public static OrderView getFullOrderViews(String roomid) { ArrayList<OrderView> fullOrderViews = new ArrayList<OrderView>(); Connection connection = null; PreparedStatement preparedStatement = null; ResultSet resultSet = null; try { connection = DataBase.getConnection(); preparedStatement = connection.prepareStatement("select * from orderviews where roomNumber='"+roomid+"' and orderStatus='已入住'"); //获取结果数据集 resultSet = preparedStatement.executeQuery(); while (resultSet.next()) { OrderView orderView = new OrderView(); orderView.setOrderNumder(resultSet.getString("orderNumber")); orderView.setCustomer(resultSet.getString("customerName")); orderView.setRoomNumber(resultSet.getString("roomNumber")); orderView.setRoomType(resultSet.getString("roomType")); orderView.setOrderTime(resultSet.getDate("orderTime")); orderView.setCheckInTime(resultSet.getDate("checkInTime")); orderView.setCheckOutTime(resultSet.getDate("checkOutTime")); int days = (int)((orderView.getCheckOutTime().getTime() - orderView.getCheckInTime().getTime())/1000/60/60/24/1); orderView.setDays(days); orderView.setCustomerPhoneNumber(resultSet.getString("customerPhoneNumber")); orderView.setPrice(resultSet.getInt("totalMoney")); orderView.setOrderStatus(resultSet.getString("orderStatus")); return orderView; } } catch(Exception exception) { exception.printStackTrace(); } return null; } //删除waiter public static void deleteWaiter(Waiter waiter) { Connection connection = null; Statement statement = null; try { connection = DataBase.getConnection(); statement = connection.createStatement(); statement.execute("DELETE FROM waiter where waiterID='" + waiter.getWaiterID() + "'"); } catch(Exception exception) { exception.printStackTrace(); } } //更新waiter public static void editWaiter(Waiter waiter) { Connection connection = null; Statement statement = null; try { connection = DataBase.getConnection(); statement = connection.createStatement(); statement.execute("UPDATE waiter SET waiterIDCard='" + waiter.getWaiterIDCard() + "' where waiterID ='"+waiter.getWaiterID() +"'"); statement.execute("UPDATE waiter SET waiterName='" + waiter.getWaiterName() + "' where waiterID ='"+waiter .getWaiterID() +"'"); statement.execute("UPDATE waiter SET waiterBirthday='" + waiter.getWaiterBirthday() + "' where waiterID ='"+waiter.getWaiterID() +"'"); statement.execute("UPDATE waiter SET waiterPassword='" + waiter.getWaiterPassword() + "' where waiterID ='"+waiter.getWaiterID() +"'"); statement.execute("UPDATE waiter SET waiterJoinDate='" + waiter.getWaiterJoinDate() + "' where waiterID ='"+waiter.getWaiterID() +"'"); statement.execute("UPDATE waiter SET waiterPhoneNumber='" + waiter.getWaiterPhoneNumber() + "'where waiterID ='"+waiter.getWaiterID() +"'"); statement.execute("UPDATE waiter SET remarks='" + waiter.getRemarks() + "'where waiterID ='"+waiter.getWaiterID() +"'"); } catch(Exception exception) { exception.printStackTrace(); } } //使用incomeView public static ArrayList<InComeView> getInComeView(){ Connection connection = null; PreparedStatement preparedStatement = null; ResultSet resultSet = null; Waiter waiter =null; ArrayList<InComeView>inComeViews =new ArrayList<InComeView>(); try { connection = DataBase.getConnection(); String sql = "select * from incomeView"; preparedStatement = connection.prepareStatement(sql); resultSet = preparedStatement.executeQuery(); while (resultSet.next()) { inComeViews.add( new InComeView(resultSet.getDate("co").toString() ,resultSet.getInt("tot"), resultSet.getInt("num")) ); } } catch(Exception exception) { exception.printStackTrace(); } return inComeViews; } //根据waiterid 来返回waiter public static Waiter getWaiter(String waiterID){ Connection connection = null; PreparedStatement preparedStatement = null; ResultSet resultSet = null; Waiter waiter =null ; try { connection = DataBase.getConnection(); String sql ="select * from waiter where waiterID='"+waiterID+"'" ; preparedStatement = connection.prepareStatement(sql); resultSet = preparedStatement.executeQuery(); while (resultSet.next()) { waiter =new Waiter(resultSet.getString("waiterID") ,resultSet.getString("waiterName") ,resultSet.getDate("waiterBirthday") ,resultSet.getString("waiterIDCard") ,resultSet.getString("waiterPassword") ,resultSet.getDate("waiterJoinDate") ,resultSet.getString("waiterPhoneNumber") ,resultSet.getString("remarks")) ; return waiter ; } } catch(Exception exception) { exception.printStackTrace(); } return null ; } //根据房间的型号查询房间的相关信息 public static HashMap<String,RoomTypeAndPrice> getRoomTypeMap(){ Connection connection = null; PreparedStatement preparedStatement = null; ResultSet resultSet = null; HashMap<String,RoomTypeAndPrice> map =new HashMap<String, RoomTypeAndPrice>() ; try { connection = DataBase.getConnection(); String sql ="select * from roomtypeandprice " ; preparedStatement = connection.prepareStatement(sql); resultSet = preparedStatement.executeQuery(); while (result