databaseconnector.java

java酒店管理系统

            	room.guestRoomId = resultset.getString("guestRoomId").trim();
				room.guestRoomClass = resultset.getString("roomClass").trim();
				room.guestRoomFee = Integer.parseInt(resultset.getString("roomFee").trim());
				room.guestRoomState = Integer.parseInt(resultset.getString("roomState").trim());
				blankRoomList.addElement(room);		//加入空房间列表
            }
            resultset.close();	//关闭数据库连接
            connect.close();
        }catch (Exception e) {
            e.printStackTrace();//把该 Throwable 和它的跟踪情况打印到标准错误流。
        }
System.out.println("blankRoomList's size: "+blankRoomList.size());
		///////////////////////////////////		
		return blankRoomList;		
	}//getBlankRoomList end
	//数据库连接器获取数据库的所有房间列表
	public Vector getAllRoomList(){			
		Vector roomList = new Vector();
		try{
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");//连接数据库
       		Connection connect=DriverManager.getConnection("jdbc:odbc:hotelmanager"," "," ");       				
       		
        	String sql="select guestRoomId,roomClass,roomFee,roomState ,"+
        				"roomPhoneNumber from GuestRoom ";
        	
        	PreparedStatement prepareState=connect.prepareCall(sql);//设定数据库查寻条件            
            ResultSet resultset=prepareState.executeQuery();//执行数据库查寻 
            while(resultset.next()) {
            	//注意：Get***要和上面列出的一致
            	GuestRoom room=new GuestRoom();	
            	room.guestRoomId = resultset.getString("guestRoomId").trim();
				room.guestRoomClass = resultset.getString("roomClass").trim();
				room.guestRoomFee = Integer.parseInt(resultset.getString("roomFee").trim());
				room.guestRoomState = Integer.parseInt(resultset.getString("roomState").trim());
				room.guestRoomPhone = resultset.getString("roomPhoneNumber").trim();
				roomList.addElement(room);		//加入房间列表
            }
            resultset.close();	//关闭数据库连接
            connect.close();
        }catch (Exception e) {
            e.printStackTrace();//把该 Throwable 和它的跟踪情况打印到标准错误流。
        }
System.out.println("TotalRoomList's size: "+roomList.size());
		///////////////////////////////////		
		return roomList;		
	}//getBlankRoomList end
	////////////////////////////////////////////////////////////////////
	public Vector getReserveInfoList(){
		////获得数据库中的预订信息表
		System.out.println("Database：获得数据库中的预订信息表");
		Vector reserveInfoList=new Vector();
		//////////////////////////////////		
		try{
			System.out.println("连接数据库。。。");
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");//连接数据库
       		Connection connect=DriverManager.getConnection("jdbc:odbc:hotelmanager"," "," ");       				
       		System.out.println("成功连接数据库");
       		
        	String sql="select reserveGuestId,guestName,guestPhone,arriveDate,guestNum from ReserveGuestInfo";
        	
        	PreparedStatement prepareState=connect.prepareCall(sql);//设定数据库查寻条件            
            ResultSet resultset=prepareState.executeQuery();//执行数据库查寻 
            while(resultset.next()) {
            	//注意：Get***要和上面列出的一致
            	ReserveInfo reserveInfo = new ReserveInfo();
            	int guestId= Integer.parseInt(resultset.getString("reserveGuestId").trim());  
            	reserveInfo.setGuestId( guestId );
            	reserveInfo.setGuestName( resultset.getString("guestName").trim());
            	reserveInfo.setGuestPhone(resultset.getString("guestPhone").trim());
            	reserveInfo.setArriveDate(resultset.getString("arriveDate").trim());
            	reserveInfo.setGuestNum(Integer.parseInt(
            				resultset.getString("guestNum").trim() ));
            	//存储该客人的预订信息到数组中			
            	reserveInfoList.addElement(reserveInfo);
            }
            resultset.close();
            ////////////////////////
            
            for (int i=0;i<reserveInfoList.size();i++) 
  			{
  				ReserveInfo reserveInfo = new ReserveInfo();
  					
  				reserveInfo=(ReserveInfo)reserveInfoList.elementAt(i); 
  				int rgId = 	reserveInfo.getGuestId();
  				
  				//获取对应的客人预订的房间号
            	String sqlroom="select GuestRoom.guestRoomId,roomClass,roomFee,roomState from GuestRoom ,ReserveRoomInfo where reserveGuestId=? and GuestRoom.guestRoomId=ReserveRoomInfo.guestRoomId";
        		
        		PreparedStatement prepareGetRoom=connect.prepareCall(sqlroom);//设定数据库查寻条件            
            	prepareGetRoom.clearParameters();
            	prepareGetRoom.setInt(1,rgId);
            	Vector RoomList = new Vector(); 
            	ResultSet resultRoomList=prepareGetRoom.executeQuery();            	           	
            	while(resultRoomList.next()){
            		GuestRoom room=new GuestRoom();	
            		room.guestRoomId = resultRoomList.getString("guestRoomId").trim();
					room.guestRoomClass = resultRoomList.getString("roomClass").trim();
					room.guestRoomFee = Integer.parseInt(resultRoomList.getString("roomFee").trim());
					room.guestRoomState = Integer.parseInt(resultRoomList.getString("roomState").trim());
					RoomList.addElement(room);		//加入空房间列表            		
            	}
            	reserveInfo.setChoosenRoomList(RoomList); 
            	           	
            	reserveInfoList.setElementAt(reserveInfo,i);
            	resultRoomList.close();	
  				
     		}
     	//////////////////////////////
            
           
            connect.close();//关闭数据库连接
        }catch (Exception e) {
            e.printStackTrace();//把该 Throwable 和它的跟踪情况打印到标准错误流。
        }
        
        
System.out.println("reserveInfoList's size: "+reserveInfoList.size());
		return reserveInfoList;
	}
	//////////////////////////////////////////////////////////////////////////////////
	//获取登记信息
	public Vector getCheckInInfoList(){
  		Vector checkInInfoList=new Vector();
  		System.out.println("获得数据库中的登记信息");
  		//////////////////////////////////		
		try{
			System.out.println("连接数据库。。。");
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");//连接数据库
       		Connection connect=DriverManager.getConnection("jdbc:odbc:hotelmanager"," "," ");       				
       		System.out.println("成功连接数据库");
       		
        	String sql="select checkInGuestId,guestName,certificateKind,certificateId,sex,guestNum,stayDayNum,note,desposit,arriveDate from CheckInGuestPersonInfo";
        	
        	PreparedStatement prepareState=connect.prepareCall(sql);//设定数据库查寻条件            
            ResultSet resultset=prepareState.executeQuery();//执行数据库查寻 
            while(resultset.next()) {            	
            	CheckInInfo checkInInfo = new CheckInInfo();
            	int guestId= Integer.parseInt(resultset.getString("checkInGuestId").trim());  
            	checkInInfo.setCheckInGuestId( guestId );
            	checkInInfo.setGuestName( resultset.getString("guestName").trim());
            	checkInInfo.setCertificateKind(Integer.parseInt(
            				resultset.getString("certificateKind").trim() ));
            	checkInInfo.setCertificateId( resultset.getString("certificateId").trim());
            	checkInInfo.setSex(Integer.parseInt(
            				resultset.getString("sex").trim() ));
            	checkInInfo.setGuestNum(Integer.parseInt(
            				resultset.getString("guestNum").trim() ));
            	checkInInfo.setStayDayNum(Integer.parseInt(
            				resultset.getString("stayDayNum").trim() ));
            	checkInInfo.setNote(resultset.getString("note").trim());            	
            	checkInInfo.setDesposit(Integer.parseInt(
            				resultset.getString("desposit").trim() ));
            	checkInInfo.setArriveDate(resultset.getString("arriveDate").trim());
            				
            	//存储该客人的预订信息到数组中			
            	checkInInfoList.addElement(checkInInfo);
            }
            resultset.close();
            ////////////////////////
            
            for (int i=0;i<checkInInfoList.size();i++) 
  			{
  				CheckInInfo checkInInfo = new CheckInInfo();
  					
  				checkInInfo=(CheckInInfo)checkInInfoList.elementAt(i); 
  				int checkId = 	checkInInfo.getCheckInGuestId();
  				
  				//获取对应的客人的登记的房间号
            	String sqlroom="select GuestRoom.guestRoomId,roomClass,roomFee,roomState from GuestRoom ,CheckInGuestRoomInfo where checkInGuestId=? and GuestRoom.guestRoomId=CheckInGuestRoomInfo.guestRoomId";
    		
        		PreparedStatement prepareGetRoom=connect.prepareCall(sqlroom);//设定数据库查寻条件            
            	prepareGetRoom.clearParameters();
            	prepareGetRoom.setInt(1,checkId);
            	Vector RoomList = new Vector(); 
            	ResultSet resultRoomList=prepareGetRoom.executeQuery();            	           	
            	while(resultRoomList.next()){
            		GuestRoom room=new GuestRoom();	
            		room.guestRoomId = resultRoomList.getString("guestRoomId").trim();
					room.guestRoomClass = resultRoomList.getString("roomClass").trim();
					room.guestRoomFee = Integer.parseInt(resultRoomList.getString("roomFee").trim());
					room.guestRoomState = Integer.parseInt(resultRoomList.getString("roomState").trim());
					RoomList.addElement(room);		//加入空房间列表            		
            	}
            	checkInInfo.setChoosenRoomList(RoomList); 
            	           	
            	checkInInfoList.setElementAt(checkInInfo,i);
            	resultRoomList.close();	
  				
     		}
     	//////////////////////////////
            connect.close();//关闭数据库连接
        }catch (Exception e) {
            e.printStackTrace();//把该 Throwable 和它的跟踪情况打印到标准错误流。
        }
        
System.out.println("checkInInfoList's size: "+checkInInfoList.size()); 
  		/////////////////////////////// 		
  		return checkInInfoList;
  	}
  	//////////////////////////////////////////////////////////////////////////////////
	public Account getAccountInfo(String id) {
		
  		System.out.println("数据库连接器将验证登陆信息，返回成功标志");  
  		String aId=id;		
  		//存放帐户信息
 		Account accountInfo =new Account();
  		try{
			System.out.println("连接数据库。。。");
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");//连接数据库
       		Connection connect=DriverManager.getConnection("jdbc:odbc:hotelmanager"," "," ");       				
       		System.out.println("成功连接数据库");
        	String sql="select accountId,password,"+
        	"accountClass,accountName,accountSex,accountAddress,"+
        	"accountWage from AccountInfo where accountId=?";
        	 			
        	PreparedStatement prepareState=connect.prepareCall(sql);//设定数据库查寻条件            
        	prepareState.clearParameters();
        	prepareState.setString(1,aId);        	
            ResultSet resultset=prepareState.executeQuery();//执行数据库查寻 
            while(resultset.next()) {            
            	
            	accountInfo.setAccountId(resultset.getString("accountId").trim());
            	accountInfo.setPassword(resultset.getString("password"));
            	accountInfo.setAccountClass(resultset.getInt("accountClass"));
            	accountInfo.setAccountName(resultset.getString("accountName").trim());
            	accountInfo.setAccountSex(resultset.getInt("accountSex"));
            	accountInfo.setAccountAddress(resultset.getString("accountAddress"));
            	accountInfo.setAccountWage(resultset.getInt("accountWage"));
				
            }
            resultset.close();	//关闭数据库连接
            connect.close();
        }catch (Exception e) {
            e.printStackTrace();//把该 Throwable 和它的跟踪情况打印到标准错误流。
        }
  		return accountInfo;  				
  	}	

}