27 Mayıs 2007 Pazar

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;
}

}


Hiç yorum yok: