Execute Queries on a Database

A class that makes connection to database and run queries.
----------------------------------------------

/*
 * FlightDB.java
 *
 * Created on 24 Dec 2006 Sunday, 06:00
 *
 */

package com.myairline.db;

import java.sql.*;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.Date;
import java.sql.PreparedStatement;

import com.myairline.type.Flight;
import com.myairline.type.Route;
import com.myairline.type.Plane;
import com.myairline.type.Airport;
import com.myairline.common.MyConstants;

/**
 *
 * @author ozkansari
 */
public class FlightDB {

Connection connection;

/** Creates a new instance of UserDB */
public FlightDB() {
   DatabaseConn dbConn = new DatabaseConn();
   connection = dbConn.getConnection();
}

public ArrayList searchFlight() {
   return null;
}

public boolean insertNewFlight() {
   return false;
}

public boolean deleteFlight() {
   return false;
}

public void scheduleFlight() {
}

/**
     * 
     * 
     * @param fromAirportId
     * @param toAirportId
     * @param flightDate
     * @param returnDate
     * @param passengerCount
     */
public ArrayList getFlightList( int fromAirportId, int toAirportId, Date flightDate, int passengerCount  ) {

   ArrayList flightList = new ArrayList();

   try {
       Statement statement = connection.createStatement();
       String q = "SELECT * FROM `flight` f , `route` r, `plane` p WHERE " +
               "f.idRoute = r.idRoute AND " +
               "f.idPlane = p.idPlane AND " +
               "f.passenger_count + " + passengerCount + "<= p.capacity " + " AND  " +
               "r.start_airport =" + fromAirportId + " AND  " +
               "r.end_airport =" + toAirportId + " AND  " +
               "flightDate LIKE \"" + flightDate +"%\"" ;

       ResultSet rs = statement.executeQuery(q);

       AirportDB airportcheck = new AirportDB();
       Airport fromAirport = airportcheck.getAirport(fromAirportId );
       Airport toAirport = airportcheck.getAirport(toAirportId );
  
       while (rs.next()) {
      
           Flight flight = new Flight();
           Route route = new Route();
           Plane plane = new Plane();
      
           flight.setFlightNo(rs.getInt("f.flight_no"));
           flight.setIdPlane(rs.getInt("f.idPlane")) ;
           flight.setIdRoute(rs.getInt("f.idRoute")) ;
           flight.setFlightDate(rs.getDate("f.flightDate"));
           flight.setPassengerCount(rs.getInt("f.passenger_count")) ;
           flight.setTicketFare(rs.getInt("f.ticket_fare")) ;
           flight.setFlightStatus(rs.getString("f.status")) ;
      
           route.setIdRoute(rs.getInt("r.IdRoute"));
           route.setStartAirportId(rs.getInt("r.start_airport"));
           route.setEndAirportId(rs.getInt("r.end_airport"));
           route.setMiles(rs.getInt("r.miles"));
           route.setStartAirport( fromAirport );
           route.setEndAirport( toAirport );
      
           plane.setIdPlane(rs.getInt("p.IdPlane"));
           plane.setPlaneType(rs.getString("p.planeType"));
           plane.setCapacity(rs.getShort("p.capacity"));
      
           flight.setFlightPlane( plane ) ;
           flight.setFlightRoute( route ) ;
      
           flightList.add(flight);
       }
  
   } catch (SQLException e) {
       e.printStackTrace();
   }

   return flightList;
}

}