peninsula.java

一个基于Java servelet的hotel broker 程序 实现access数据库访问

          //String dburl ="jdbc:odbc:odbcName";//此为ODBC连接方式
          connection=DriverManager.getConnection(dburl);
          
          
          statement = connection.prepareStatement(sql);
          statement.setString(1, roomType);
          
          resultSet = statement.executeQuery();
         
          while(resultSet.next())
          {
           result.add(resultSet.getString("ROOM_NAME"));
          }	
    	}
    	catch(Exception ex)
    	{
    	   ex.printStackTrace();	
    	}
    	finally
        {
            if (connection != null)
            {
                try
                {
                    connection.close();
                }
                catch (Exception e)
                {
                    // do nothing
                }
            }
            
            if (statement != null)
            {
                try
                {
                    statement.close();
                }
                catch (Exception e)
                {
                    // do nothing
                }
            }
            
            if (resultSet != null)
            {
                try
                {
                    resultSet.close();
                }
                catch (Exception e)
                {
                    // do nothing
                }
            }
        
    }	
    return result;
  }
  
  public String InsertBooking(String name,int phoneNumber,int creditNumber,String roomName,Date checkin,Date checkout)
    {
    	final String sql1 = "SELECT ROOM_ID FROM ROOM WHERE ROOM_NAME = ?";
    	final String sql2 = "INSERT INTO CUSTOMER (CUSTOMER_NAME,CUSTOMER_PHONE_NUMBER,CUSTOMER_CREDIT_NUMBER) VALUES (?, ?, ?)";
    	final String sql3 = "SELECT CUSTOMER_ID FROM CUSTOMER WHERE CUSTOMER_NAME = ?";
    	final String sql4 = "INSERT INTO BOOKING (CUSTOMER_ID,ROOM_ID,BOOKING_IN_DATE,BOOKING_OUT_DATE) VALUES (?, ?, ?, ?)";
    	
    	int roomID = 0;
    	int customerID = 0;
    	
    	Connection connection = null;
        
        PreparedStatement statement1 = null;
        ResultSet resultSet1 = null;
        
        PreparedStatement statement2 = null;
        //ResultSet resultSet2 = null;
        
        PreparedStatement statement3 = null;
        ResultSet resultSet3 = null;
    	
    	PreparedStatement statement4 = null;
        //ResultSet resultSet4 = null;
    	
    	try
        {
    	  Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
          String dburl ="jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ=melbourne_peninsula.mdb";//此为NO-DSN方式
          //String dburl ="jdbc:odbc:odbcName";//此为ODBC连接方式
          
          connection=DriverManager.getConnection(dburl);
         
          statement1 = connection.prepareStatement(sql1);
          statement1.setString(1, roomName);
          resultSet1 = statement1.executeQuery();
          while(resultSet1.next())
          {
           roomID = resultSet1.getInt("ROOM_ID");
          }	
          
          statement2 = connection.prepareStatement(sql2);
          statement2.setString(1, name);
          statement2.setInt(2, phoneNumber);
          statement2.setInt(3, creditNumber);
          statement2.executeUpdate();
          
          statement3 = connection.prepareStatement(sql3);
          statement3.setString(1, name);
          resultSet3 = statement3.executeQuery();
          while(resultSet3.next())
          {
           customerID = resultSet3.getInt("CUSTOMER_ID");
          }	
          
          statement4 = connection.prepareStatement(sql4);
          statement4.setInt(1, customerID);
          statement4.setInt(2, roomID);
          statement4.setDate(3, checkin);
          statement4.setDate(4, checkout);
          statement4.executeUpdate();
    	}
    	catch(Exception ex)
    	{
    	   ex.printStackTrace();	
    	}
    	finally
        {
            if (connection != null)
            {
                try
                {
                    connection.close();
                }
                catch (Exception e)
                {
                    // do nothing
                }
            }
            
            if (statement1 != null)
            {
                try
                {
                    statement1.close();
                    statement2.close();
                    statement3.close();
                    statement4.close();
                }
                catch (Exception e)
                {
                    // do nothing
                }
            }
            
            if (resultSet1 != null)
            {
                try
                {
                    resultSet1.close();
                    resultSet3.close();
                }
                catch (Exception e)
                {
                    // do nothing
                }
            }
        
    }	
    return "SUCCESS";
  }
  
  ////////////////////////////////////////////////////////////////////////////////////////
  ////////////////////////////////////////////////////////////////////////////////////////
 
  public String checkAvailability(String roomType,Date checkinDate,Date checkoutDate)
  {
  	final String sql1 = "SELECT ROOM_ID FROM ROOM WHERE ROOM_TYPE = ? AND ROOM_STATUS = 0";
  	
  	//final String sql2 = "SELECT ROOM_ID FROM ROOM WHERE ROOM_TYPE = ?";
  	
  	final String sql2 ="SELECT TYPEID FROM TYPE WHERE TYPENAME = ?";
  	
  	final String sql3 = "SELECT CHECKOUT_DATE FROM CHECKIN WHERE ROOM_TYPE = ?";
  	
  	final String sql4 = "SELECT ROOM_ID,BOOKING_IN_DATE,BOOKING_OUT_DATE FROM BOOKING";
  	
  	Connection connection = null;
        
    PreparedStatement statement1 = null;
    ResultSet resultSet1 = null;
    
    PreparedStatement statement2 = null;
    ResultSet resultSet2 = null;
    
    PreparedStatement statement3 = null;
    ResultSet resultSet3 = null;
    
    Statement statement4 = null;
    ResultSet resultSet4 = null;
    
    /*Statement statement5 = null;
    ResultSet resultSet5 = null;*/
    
    ArrayList availableRoomID = new ArrayList();
    
    String result = null;
    
    try
        {
    	  Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
          String dburl ="jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ=melbourne_peninsula.mdb";//此为NO-DSN方式
          //String dburl ="jdbc:odbc:odbcName";//此为ODBC连接方式
          
          connection=DriverManager.getConnection(dburl);
         
          statement1 = connection.prepareStatement(sql1);
          statement1.setString(1, roomType);
          resultSet1 = statement1.executeQuery();
         
          while(resultSet1.next())
          {
           availableRoomID.add(resultSet1.getInt("ROOM_ID"));
          }
          
          int availableRoomNumber = availableRoomID.size();
        
          if(availableRoomID.isEmpty()) //indicates that there is no room available now
          {
          	//check the CHECKIN table for further information
          	
          	//get typeID from table TYPE with the given typeName
          	statement2 = connection.prepareStatement(sql2);
            statement2.setString(1, roomType);
            resultSet2 = statement2.executeQuery();
            
            resultSet2.next ();
            int typeID = resultSet2.getInt("TYPEID"); 
            
            //get the checkout date from checkin table with a specific roomType and compare it with the user selected checkin date
            statement3 = connection.prepareStatement(sql3);
            statement3.setInt(1, typeID);
            resultSet3 = statement3.executeQuery();
            
            while(resultSet3.next())
            {
            	Date checkout_date= resultSet3.getDate("CHECKOUT_DATE");
            	if(checkinDate.after(checkout_date))  //indicates that there is a room available at the time of user selected checkin date
            	{
            		result = "SUCCESS";
            	}  
            }
            if(result == null) result = "FAILURE"; //indicates no room is available for this user
          }  //if(availableRoomID.isEmpty()) 
         
          else //indicates that there is empty room now...
          {
          	//there is at least one room of user selected type empty right now
          	//however. still needs to check if this room has been booked during user selected time period as well
          	//return true if there is at least one match found
          	statement4 = connection.createStatement();
            resultSet4 = statement4.executeQuery(sql4);
            
            ArrayList bookedRoomID = new ArrayList();
            
            while(resultSet4.next())
            {
            	bookedRoomID.add(resultSet4.getInt("ROOM_ID"));
            }
            
            int bookedRoomNumber = bookedRoomID.size();
            
            //comparing the empty rooms with the booked rooms, see if there is a date period confrontation
            
            //comparing the empty rooms with the booked rooms,see if any empty rooms is been booked already
            ArrayList resultList = new ArrayList();   
    
            for(Iterator iterator = availableRoomID.iterator(); iterator.hasNext();)   
            {   
               Object temp = iterator.next();   
               if(bookedRoomID.contains(temp))
               {   
                  resultList.add(temp);   
               }   
            } 
            	
            int resultNumber = resultList.size();	
            
            if(resultList.isEmpty()) result = "SUCCESS"; //indicates that at least one empty room is not booked yet
            
            else
            //indicates that at least one empty room is been booked already
            //further check if there is any other unbooked empty room
            //if not, needs to check if there is a date period confrontation 
            {
            	if(resultNumber < availableRoomNumber) result = "SUCCESS"; //there is at least one unbooked empty room 
            	else if(resultNumber == availableRoomNumber) // every empty room with user selecetd Type is benn booked,needs to check date further
             	      {
             	      	ArrayList<Date> bookInDate = new ArrayList();
             	      	ArrayList<Date> bookOutDate = new ArrayList();
             	      	
             	      	resultSet4.beforeFirst();
             	      	
             	      	while(resultSet4.next())
             	      	{
             	      		bookInDate.add(resultSet4.getDate("BOOK_IN_DATE"));
             	      		bookOutDate.add(resultSet4.getDate("BOOK_OUT_DATE"));
             	      	} 
             	      		
             	        Iterator it_in = bookInDate.iterator();
             	        
             	        while(it_in.hasNext())
             	        {
             	        	if(checkoutDate.before((Date)it_in.next())) result = "SUCCESS";
             	        }
             	        
             	        Iterator it_out = bookOutDate.iterator();
             	        
             	        while(it_out.hasNext())
             	        {
             	        	if(checkinDate.after((Date)it_out.next())) result = "SUCCESS";
             	        }
             	        
             	        if(result == null) result = "FAILURE"; //no match found!
             	      }
            }
             
          } //else //indicates that there is empty room now...
          
        } //try
        catch(Exception ex)
        {
        	ex.printStackTrace();
        }
        finally
        {
            if (connection != null)
            {
                try
                {
                    connection.close();
                }
                catch (Exception e)
                {
                    // do nothing
                }
            }
            
            if (statement1 != null)
            {
                try
                {
                    statement1.close();
                    statement2.close();
                    statement3.close();
                    statement4.close();
                }
                catch (Exception e)
                {
                    // do nothing
                }
            }
            
            if (resultSet1 != null)
            {
                try
                {
                    resultSet1.close();
                    resultSet2.close();
                    resultSet3.close();
                    resultSet4.close();
                }
                catch (Exception e)
                {
                    // do nothing
                }
            }
        
    } //finally
   return result;
  } // public String checkAvailability(String roomType,Date checkinDate,Date checkoutDate)
}