database.java

Java语言编写的员工信息管理系统

import java.sql.*;
import java.util.Vector;
import java.util.*;

public class Database {
	Connection connector;
	public Database(){ 		
		System.out.println("数据库连接器");	
		try{
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");//连接数据库
			connector=DriverManager.getConnection("jdbc:odbc:Inn"," "," ");
		}catch (Exception e) {
            e.printStackTrace();
        }   		
	}
	
	//	从某个结果集获取房间信息
	public Room getRoomFromResultSet(ResultSet resultset){
		Room room = new Room();	
		try {
			room.id = resultset.getString("roomNo").trim();
			room.roomClass = resultset.getString("roomClass").trim();
			room.fee = Integer.parseInt(resultset.getString("roomFee").trim());
			room.state = resultset.getString("roomState").trim();
			room.phone = resultset.getString("roomPhone").trim();
			return room;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
        }		
	}
//	从某个结果集获取预定客人信息
	public ReserveGuest getReserveGuestFromResultSet(ResultSet resultset){
		ReserveGuest guest = new ReserveGuest();	
		try {
			guest.id = Integer.parseInt(resultset.getString("reserveGuestNo").trim());
			guest.name = resultset.getString("guestName").trim();
			guest.ArrivalDate = resultset.getString("arriveDate").trim();
			guest.entourageNum = Integer.parseInt(resultset.getString("guestCount").trim());			
			guest.StaffId = resultset.getString("staffNo").trim();	
			return guest;
		} catch (Exception e) {
			e.printStackTrace();// 把该 Throwable 和它的跟踪情况打印到标准错误流。
			return null;
        }		
	}
	//从结果集中获取所有入住客人的信息
	public EnrolGuest getEnrolGuestFromResultSet(ResultSet resultset){
		EnrolGuest guest = new EnrolGuest();	
		try {
			guest.id = Integer.parseInt(resultset.getString("enrolGuestNo").trim());
			guest.name = resultset.getString("name").trim();
			guest.sex = Integer.parseInt(resultset.getString("sex").trim());
			guest.certificateId = resultset.getString("IC").trim();
			guest.nationality = resultset.getString("nationality").trim();
			guest.entourageNum = Integer.parseInt(resultset.getString("guestCount").trim());
			guest.phone = resultset.getString("phoneNumber").trim();
			guest.address = resultset.getString("address").trim();
			guest.postalcode = resultset.getString("postalcode").trim();
			guest.ArrivalDate = resultset.getString("arrivalDate").trim();
			guest.desposit = Integer.parseInt(resultset.getString("deposit").trim());
			guest.ServiceExpenses = Integer.parseInt(resultset.getString("serviceExpenses").trim());
			guest.RoomExpenses = Integer.parseInt(resultset.getString("roomExpenses").trim());
			guest.CheckOutDate = resultset.getString("checkOutDate").trim();
			guest.StaffId = resultset.getString("staffNo").trim();	
			return guest;
		} catch (Exception e) {
			e.printStackTrace();// 把该 Throwable 和它的跟踪情况打印到标准错误流。
			return null;
        }		
	}
//	从某个结果集获取交易信息
	public Transaction getTransactionFromResultSet(ResultSet resultset){
		Transaction transaction = new Transaction();	
		try {
			transaction.id = Integer.parseInt(resultset.getString("transactionNo").trim());
			
			transaction.date= resultset.getString("transactionDate").trim();
			transaction.guestId = Integer.parseInt(resultset.getString("enrolGuestNo").trim());
			
			transaction.serviceId = resultset.getString("serviceNo").trim();
			transaction.serviceFee = Integer.parseInt(resultset.getString("serviceFee").trim());			
			transaction.staffId= resultset.getString("staffNo").trim();
			return transaction;
		} catch (Exception e) {
			e.printStackTrace();// 把该 Throwable 和它的跟踪情况打印到标准错误流。
			return null;
        }		
	}
	//客人享受服务的时候,为了安全, 获取入住客人信息
	public EnrolGuest getEnrolGuestForWaiterFromResultSet(ResultSet resultset){
		EnrolGuest guest = new EnrolGuest();	
		try {
			guest.id = Integer.parseInt(resultset.getString("enrolGuestNo").trim());
			guest.name = resultset.getString("name").trim();
			guest.phone = resultset.getString("phoneNumber").trim();			
			return guest;
		} catch (Exception e) {
			e.printStackTrace();// 把该 Throwable 和它的跟踪情况打印到标准错误流。
			return null;
        }		
	}
	
//	从某个结果集获取服务信息
	public Service getServiceFromResultSet(ResultSet resultset){
		Service service = new Service();	
		try {
			service.id = resultset.getString("serviceNo").trim();
			service.name = resultset.getString("ServiceName").trim();
			service.description = resultset.getString("ServiceDescription").trim();			
			return service;
		} catch (Exception e) {
			e.printStackTrace();// 把该 Throwable 和它的跟踪情况打印到标准错误流。
			return null;
        }		
	}
	
//获取空房列表
	public Vector getBlankRoomList()	{
		Vector roomList = new Vector();
		try{			
       		CallableStatement proc = 
       			connector.prepareCall("{ call sp_getBlankRoomList }");        	
        	proc.execute();        	
        	ResultSet resultset = proc.getResultSet();
			while (resultset.next()) {				
				Room room = new Room();	
            	room = getRoomFromResultSet(resultset);
				roomList.addElement(room);		//加入房间列表
            }            
        }catch (Exception e) {
            e.printStackTrace();
        }
		///////////////////////////////////		
		return roomList;		
	}
//	保存预订信息	
	public void newReserveInfo(Reserve ri) {
		Reserve reserveInfo = new Reserve();
		reserveInfo = ri;
		try {
			//添加预订客人个人信息，并生成标志号    	
			CallableStatement proc = connector
					.prepareCall("{ call sp_insertIntoReserveGuest(?,?,?,?) }");
			proc.setString(1, reserveInfo.reserveGuest.name);	
			proc.setString(2, reserveInfo.reserveGuest.ArrivalDate);
			proc.setInt(3, reserveInfo.reserveGuest.entourageNum);			
			proc.setString(4, reserveInfo.reserveGuest.StaffId);
			proc.execute();
			ResultSet resultset = proc.getResultSet();
			int guestId = 0;
			//获得预订客人标号(自动产生)
			while (resultset.next()) {
				guestId = resultset.getInt("reserveGuestNo");
				System.out.println("预订客户号码：" + guestId);
			}
			//更新客人的预订的房间信息    
			Room room = new Room();
			String roomId = "";
			for (Enumeration ee = reserveInfo.roomList.elements(); ee
					.hasMoreElements();) {
				room = (Room) ee.nextElement();
				roomId = room.id;				
				CallableStatement proc2 = connector
						.prepareCall("{ call sp_insertIntoReserve(?,?) }");
				proc2.setInt(1, guestId);
				proc2.setString(2, roomId);
				proc2.execute();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
//	获得数据库中的预订信息：reserveGuest, roomlist of the reserveGuest
	public Vector getReserveInfoList(){			
		Vector reserveInfoList=new Vector();
		try{
			//store procedure : sp_getAllGuestList
       		CallableStatement proc = 
       			connector.prepareCall("{ call sp_getReserveGuestList }");        	
        	proc.execute();        	
        	ResultSet resultset = proc.getResultSet();
			while (resultset.next()) {		
				Reserve reserveInfo = new Reserve();
				reserveInfo.reserveGuest = getReserveGuestFromResultSet(resultset);
				//存储该客人的预订信息到数组中			
            	reserveInfoList.addElement(reserveInfo);
            }     
			resultset.close();
        }catch (Exception e) {        	
            e.printStackTrace();//把该 Throwable 和它的跟踪情况打印到标准错误流。
        }
		//////////////////////////////////		
		try{
            for (int i=0;i<reserveInfoList.size();i++) 
  			{
  				Reserve reserveInfo = new Reserve();  					
  				reserveInfo=(Reserve)reserveInfoList.elementAt(i); 
  				int rgId = 	reserveInfo.reserveGuest.id;  	
  	       		CallableStatement proc = 
  	       		connector.prepareCall("{ call sp_getReserveRoomListByGId(?) }");        	
  	        	proc.setInt(1,rgId);
            	proc.execute();        	
  	        	ResultSet resultset = proc.getResultSet();
            	Vector RoomList = new Vector();             	          	           	
            	while(resultset.next()){
            		Room room=new Room();	
            		room = getRoomFromResultSet(resultset);
					RoomList.addElement(room);        		
            	}
            	reserveInfo.roomList=RoomList;             	           	
            	reserveInfoList.setElementAt(reserveInfo,i);    
            	resultset.close();
     		}                       	
        }catch (Exception e) {
            e.printStackTrace();//把该 Throwable 和它的跟踪情况打印到标准错误流。
        }
		return reserveInfoList;
	}
	
//	删除预定信息
	public void deleteReserveInfo(int guestId){
		try{			
       		CallableStatement proc = 
       			connector.prepareCall("{ call sp_deleteReservationInfo(?) }");
       		proc.setInt(1, guestId);
        	proc.execute();    
        	proc.close();        	
        }catch (Exception e) {
            e.printStackTrace();//把该 Throwable 和它的跟踪情况打印到标准错误流。
        }
  	}
//	保存入房登记信息	
	public void newEnrolInfo(Enrol ri) {
		Enrol enrolInfo = new Enrol();
		enrolInfo = ri;
		try {
			//添加预订客人个人信息，并生成标志号