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

}

A Simple Servlet Example 2

A simple java servlet which gets requests from a web page as parameter, process the request, set response attributes and forwards to destination page
----------------------------------------------

/*
 * SearchServlet.java
 *
 * Created on 23 Dec 2006 Saturday, 17:45
 */

package com.myairline.servlets;

import java.io.*;
import java.net.*;
import java.sql.*;
import java.util.ArrayList;
import java.util.Iterator;

import javax.servlet.*;
import javax.servlet.http.*;

import com.myairline.db.DatabaseConn;
import com.myairline.db.AirportDB;
import com.myairline.db.FlightDB;
import com.myairline.db.PassengerDB;
import com.myairline.db.TicketDB;

import com.myairline.type.Airport;
import com.myairline.type.Flight;
import com.myairline.type.Ticket;
import com.myairline.type.Passenger;
import com.myairline.type.CreditCard;

import com.myairline.common.MyConstants;


/**
 *
 * @author ozkansari
 * @version
 */
public class SearchServlet extends HttpServlet {

 AirportDB airportCheck;
 ArrayList airportList;

 FlightDB flightCheck;
 ArrayList flightList1, flightList2;
 PassengerDB passengerCheck;
 TicketDB ticketCheck;

 Flight currentFlight, currentFlight2 ;


 /** Processes requests for both HTTP <code>GET</code> and <code>POST</code> methods.
     * @param request servlet request
     * @param response servlet response
     */
 protected void processRequest(HttpServletRequest request, HttpServletResponse response)
 throws ServletException, IOException {
  
     // Page will be forwarded to  :::
     String destinationPage = "";
  
     // Make database connection  :::
     DatabaseConn dbConn = new DatabaseConn();
     Connection connection = dbConn.getConnection();
  
     // get command parameter that will decide which operation is performed  :::
     String cmd = (String) request.getParameter("cmd");
  
     // get session variable  :::
     HttpSession sessionVar = request.getSession();
  
     // Check command and perform the proper operation :::
     if( cmd.equalsIgnoreCase("flight_search") ) {
         String source = (String) request.getParameter("source");
      
         // Get Airport List to send them to next page as bean :::
         airportCheck = new AirportDB();
         ArrayList airportList = airportCheck.getAirportList();
      
         // Set Attribute and destination Page :::
         if( source.equalsIgnoreCase("customer") )
             destinationPage = "/customer/flightSearch.jsp";
         else if( source.equalsIgnoreCase("agency") )
             destinationPage = "/agency/flightSearch.jsp";
         request.setAttribute( "airportList", airportList );
     }
  
     else if( cmd.equalsIgnoreCase("get_flights") ) {
         String returnFlight = (String) request.getParameter("return");
      
         // Get parameters :::
         int fromAirportId = Integer.parseInt( request.getParameter("fromAirportsList") );
         int toAirportId = Integer.parseInt( request.getParameter("toAirportsList") ) ;
         int day1 = Integer.parseInt( request.getParameter("daydropdown1") );
         int month1 = Integer.parseInt( request.getParameter("monthdropdown1") );
         int year1 = Integer.parseInt( request.getParameter("yeardropdown1") );
         Date departDate = new Date( year1-1900, month1, day1 );
         int passengerCount = Integer.parseInt( request.getParameter("passengerCount") );
      
         // Get Airport List to send them to next page as bean :::
         flightCheck = new FlightDB();
         flightList1 = flightCheck.getFlightList( fromAirportId,toAirportId,departDate,passengerCount);
      
      
         Date returnDate = null;
         if( returnFlight.equalsIgnoreCase( "yes" ) ) {
             int day2 = Integer.parseInt( request.getParameter("daydropdown2") );
             int month2 = Integer.parseInt( request.getParameter("monthdropdown2") );
             int year2 = Integer.parseInt( request.getParameter("yeardropdown2") );
             returnDate = new Date( year2-1900, month2, day2 );
          
             flightList2 = flightCheck.getFlightList( toAirportId,fromAirportId,returnDate,passengerCount);
             request.setAttribute( "flightList2", flightList2 );
         }
      
         // Set Attribute and destination Page :::
         String source = (String) request.getParameter("source");
         if( source.equalsIgnoreCase("customer") )
             destinationPage = "/customer/flightResults.jsp";
         else if( source.equalsIgnoreCase("agency") )
             destinationPage = "/agency/flightResults.jsp";
      
         request.setAttribute( "passengerCount", ""+passengerCount );
         request.setAttribute( "flightList1", flightList1 );

     }
  
     else if( cmd.equalsIgnoreCase("selected_flight") ) {
      
         // Define variables :::
         Iterator flightIterator ,flightIterator2;
      
         // Get source: Agency or Customer?
         String source = (String) request.getParameter("source");
      
         // Get flight selection :::
         int flightSelection = Integer.parseInt( request.getParameter("flightSelection") );

         // Get number of passengers :::
         int passengerCount = Integer.parseInt( request.getParameter("passengerCount") );
         request.setAttribute( "passengerCount", ""+passengerCount );
      
         // Get depart flight Info :::
         flightIterator = flightList1.iterator();
         while( flightIterator.hasNext() ) {
             currentFlight = (Flight) flightIterator.next();
          
             if( currentFlight.getFlightNo() == flightSelection ) {
                 request.setAttribute( "selectedFlight", currentFlight );
             }
         }
      
         if( request.getParameter("flightSelection2") != null ) {

             int flightSelection2 = Integer.parseInt( request.getParameter("flightSelection2") );

             // Get return  flight Info :::
             flightIterator2 = flightList2.iterator();
             while( flightIterator2.hasNext() ) {
                 currentFlight2 = (Flight) flightIterator2.next();

                 if( currentFlight2.getFlightNo() == flightSelection2 ) {
                     request.setAttribute( "selectedFlight2", currentFlight2 );
                 }
             }
         }
         // Set Attribute and destination Page :::
         if( source.equalsIgnoreCase("customer") )
             destinationPage = "/customer/payment.jsp";
         else if( source.equalsIgnoreCase("agency") ) {
          
             // Get sale selection: Single or Group?
             String saleSelection = (String) request.getParameter("saleSelection");
             if( saleSelection.equalsIgnoreCase( "single" ) )
                 destinationPage = "/agency/singleSale.jsp";
             else if( saleSelection.equalsIgnoreCase( "group" ) )
                 destinationPage = "/agency/groupSale.jsp";
         }
      
      
     } else if( cmd.equalsIgnoreCase("payFlight") ) {
      
         // Define ::
         ArrayList departTicketList = new ArrayList();
         ArrayList returnTicketList = new ArrayList();
         ArrayList passengerList = new ArrayList();
         int selectedFlightNo, selectedFlightNo2;
      
         // Get selected depart flight No :::
         selectedFlightNo = Integer.parseInt( (String) request.getParameter("flightNo") ) ;
      
         // Get and set credit card info parameters :::
         CreditCard cardInfo = new CreditCard();
         cardInfo.setCardOwner( (String) request.getParameter("cardOwner") );
         cardInfo.setCardNo( (String) request.getParameter("cardNo") );
         int day = Integer.parseInt( request.getParameter("daydropdown1") );
         int month = Integer.parseInt( request.getParameter("monthdropdown1") );
         int year = Integer.parseInt( request.getParameter("yeardropdown1") );
         cardInfo.setExpireDate( new Date( year-1900, month, day ) );
         cardInfo.setSecurityCode( (String) request.getParameter("securityCode") ) ;
      
         // Ticket sale for the specified number of passengers
         int passengerCount = Integer.parseInt( request.getParameter("passengerCount") );
         for( int i=1; i<=passengerCount ; i++ ) {
          
             // Get and set Passenger info parameters :::
             Passenger passenger = new Passenger();
             passenger.setName( (String) request.getParameter("name"+i) );
             passenger.setSurname( (String) request.getParameter("surname"+i) )  ;
             passenger.setAge( Integer.parseInt( (String) request.getParameter("age"+i) ) );
             passenger.setSex( request.getParameter("gender"+i).charAt(0) );
             passenger.setPassportId( (String) request.getParameter("passportId"+i) ) ;
             passenger.setAddress( (String) request.getParameter("address"+i) );
             passenger.setPhoneNo( (String) request.getParameter("phoneNo"+i) ) ;
          
             // Set ticket info :::
             Ticket departTicket = new Ticket();
             departTicket.setFlightNo( selectedFlightNo );
             departTicket.setFarePaid( currentFlight.getTicketFare() );
             departTicket.setTicketStatus( "active" );
             departTicket.setMyFlight( currentFlight );
          
             // Create a new depart ticket :::
             ticketCheck = new TicketDB();
             int idTicket = ticketCheck.insertTicket( departTicket );
             passenger.setIdTicket( idTicket );
             departTicket.setIdTicket( idTicket );
          
             // Create a new passenger :::
             passengerCheck = new PassengerDB();
             boolean check = passengerCheck.insertNewPassenger( passenger );
          
             if( request.getParameter("flightNo2") != null ) {
              
                 // Create a new passenger
                 Passenger passenger2 = new Passenger( passenger );
              
                 // get selected return flight no
                 selectedFlightNo2 = Integer.parseInt( (String) request.getParameter("flightNo2") ) ;
              
                 // Set ticket info :::
                 Ticket returnTicket = new Ticket();
                 returnTicket.setFlightNo( selectedFlightNo2 );
                 returnTicket.setFarePaid( currentFlight2.getTicketFare() );
                 returnTicket.setTicketStatus( "active" );
                 returnTicket.setMyFlight( currentFlight2 );

                 // Create a new depart ticket :::
                 ticketCheck = new TicketDB();
                 int idTicket2 = ticketCheck.insertTicket( returnTicket );
                 passenger2.setIdTicket( idTicket2 );
                 returnTicket.setIdTicket( idTicket2 );
              
                 // Create a new passenger entry for return flight
                 boolean check2 = passengerCheck.insertNewPassenger( passenger2 );
              
                 // 
                 returnTicketList.add( returnTicket );
              
             }
          
             // Set Attributes ::
             departTicketList.add( departTicket );
             passengerList.add( passenger );

         } // end-for-loop
      
         if( request.getParameter("flightNo2") != null ) {
             request.setAttribute( "ticketList2", returnTicketList );
             request.setAttribute( "flight2", currentFlight2 );
         }
         request.setAttribute( "flight", currentFlight );
         request.setAttribute( "passengerCount", ""+passengerCount );
         request.setAttribute( "ticketList", departTicketList );
         request.setAttribute( "passengerList", passengerList );

         // Set destination Page :::
         String source = (String) request.getParameter("source");
         if( source.equalsIgnoreCase("customer") )
             destinationPage = "/customer/showTicket.jsp";
         else if( source.equalsIgnoreCase("agency") )
             destinationPage = "/agency/showTicket.jsp";
      

     }
     else if( cmd.equalsIgnoreCase("payGroupFlight") ) {
      
         int agencyId = Integer.parseInt( (String) request.getParameter("agencyId") );
         int passengerCount = Integer.parseInt( request.getParameter("passengerCount") );
  
         // Get selected depart flight No :::
         int departFlightNo = Integer.parseInt( (String) request.getParameter("flightNo") ) ;
      
         // get selected return flight no
         if( request.getParameter("flightNo2") != null ) {
             int returnFlightNo = Integer.parseInt( (String) request.getParameter("flightNo2") ) ;
         }
      
         if( request.getParameter("flightNo2") != null ) {
             request.setAttribute( "flight2", currentFlight2 );
         }
         request.setAttribute( "flight", currentFlight );
         request.setAttribute( "passengerCount", ""+passengerCount );
      
         destinationPage = "/agency/showGroupTicket.jsp";

     }
  
     // Clean your garbage :::
     dbConn.closeConnection();
  
     // Go to destination page :::
     RequestDispatcher dispatcher = getServletContext().getRequestDispatcher(destinationPage);
     dispatcher.forward(request, response);
 }

 // <editor-fold defaultstate="collapsed" desc="HttpServlet methods. Click on the + sign on the left to edit the code.">
 /** Handles the HTTP <code>GET</code> method.
     * @param request servlet request
     * @param response servlet response
     */
 protected void doGet(HttpServletRequest request, HttpServletResponse response)
 throws ServletException, IOException {
     processRequest(request, response);
 }

 /** Handles the HTTP <code>POST</code> method.
     * @param request servlet request
     * @param response servlet response
     */
 protected void doPost(HttpServletRequest request, HttpServletResponse response)
 throws ServletException, IOException {
     processRequest(request, response);
 }

 /** Returns a short description of the servlet.
     */
 public String getServletInfo() {
     return "Short description";
 }
 // </editor-fold>
}

A simple JSP File

A simple JSP file which gets results from request attributes and display them through iterations
----------------------------------------------

<%@page contentType="text/html" import="java.util.*, com.myairline.type.*" %>
<%@page pageEncoding="UTF-8"%>
<%--
The taglib directive below imports the JSTL library. If you uncomment it,
you must also add the JSTL library to the project. The Add Library... action
on Libraries node in Projects view can be used to add the JSTL 1.1 library.
--%>
<%--
<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%> 
--%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
   "http://www.w3.org/TR/html4/loose.dtd">

<html>
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    <link href="resources/style.css" rel="stylesheet" type="text/css">
    <title>Flight Results</title>

</head>

<%    
// Get and set attributes :::
ArrayList flightList1 = (ArrayList) request.getAttribute("flightList1");
ArrayList flightList2 = (ArrayList) request.getAttribute("flightList2");
int passengerCount = Integer.parseInt( (String) request.getAttribute( "passengerCount") );

%>

<body>

<!-------------------------------------------------------------------------------------------------------------------------- 
    ****************************************************************************************************************************
          START FLIGHT SEARCH RESULTS FORM
    ****************************************************************************************************************************
    ------------------------------------------------------------------------------------------------------------------------ -->

    <table border="1" cellspacing="4" cellpadding="4">

        <thead>
            <tr>
                <th> Option </th>
                <th> Flight No </th>
                <th> Depart Date </th>
                <th> From </th>
                <th> To </th>
                <th> Price </th>
            </tr>
        </thead>
        <tbody>
    
        <form name="flightSelectForm" method="post" action="/MyAirline/SearchServlet?cmd=selected_flight&source=customer">
        
            <tr>
                <td colspan="6">
                    <b> DEPART FLIGHTS RESULTS </b>
                </td>
            </tr>
            <%
            // Add Elements to Depart Flight Results List -
            //************************************************************************************************************
            Iterator flightIterator = flightList1.iterator();
        
            boolean isEmpty1 = true;
            while( flightIterator.hasNext() ) {
                isEmpty1=false;
                Flight currentFlight = (Flight) flightIterator.next();
            %>
        
            <tr>
                <td>
                    <% out.println("<input type=\"radio\" name=\"flightSelection\" value=\""+ currentFlight.getFlightNo() +"\" />" ); %>
                </td>
            
                <td>
                    <%= currentFlight.getFlightNo()%>
                </td>
                
                <td>
                    <%= currentFlight.getFlightDate()%>
                </td>
            
                <td>
                    <%= currentFlight.getFlightRoute().getStartAirport().toString() %>
                </td>
            
                <td>
                    <%= currentFlight.getFlightRoute().getEndAirport().toString() %>
                </td>
            
                <td>
                    <%= currentFlight.getTicketFare()%>
                </td>
            </tr>
        
            <%
            } // end-while-
            //************************************************************************************************************
            %>
        
            <%
            if( isEmpty1 ) {
            %>
            <tr>
                <td colspan="6" > NO MATCHING RESULTS </td>
            </tr>
            <%
            } // -end-if-
            %>

            <tr>
                <td colspan="6">
                    <b> RETURN FLIGHTS RESULTS </b>
                </td>
            </tr>
        
            <%
            boolean isEmpty2 = true;
            if( flightList2 != null ) {
            %>

                <%
                // Add Elements to Depart Flight Results List -
                //************************************************************************************************************
                Iterator flightIterator2 = flightList2.iterator();

                isEmpty2 = true;
                while( flightIterator2.hasNext() ) {
                    isEmpty2=false;
                    Flight currentFlight2 = (Flight) flightIterator2.next();
                %>
        
            <tr>
                <td>
                    <% out.println("<input type=\"radio\" name=\"flightSelection2\" value=\""+ currentFlight2.getFlightNo() +"\" />" ); %>
                </td>
            
                <td>
                    <%= currentFlight2.getFlightNo()%>
                </td>
                
                <td>
                    <%= currentFlight2.getFlightDate()%>
                </td>
            
                <td>
                    <%= currentFlight2.getFlightRoute().getStartAirport().toString() %>
                </td>
            
                <td>
                    <%= currentFlight2.getFlightRoute().getEndAirport().toString() %>
                </td>
            
                <td>
                    <%= currentFlight2.getTicketFare()%>
                </td>
            </tr>
        
                <%
                } // end-while-
                //************************************************************************************************************
                %>
            
            <%
            } // -end-if-
            %>
        
        
            <%
            if( isEmpty2 ) {
            %>
            <tr>
                <td colspan="6" > NO MATCHING RESULTS </td>
            </tr>
            <%
            } // -end-if-
            %>
            
            <% if( isEmpty1 == false ) { %>
        
            <tr>
                <td> Passenger Count: </td>
                <td>
                    <%= passengerCount %>
                </td>
            </tr>
        
            <input type="hidden" name="passengerCount" value="<%= passengerCount %>" />
                
            <tr>
                <td align=center colspan="36">
                    <A href="/MyAirline/SearchServlet?cmd=flight_search&source=agency" target="mainFrame"> SEARCH AGAIN </A>
                </td>
            </tr>
            <tr>
                <td colspan="6" align=center>
                    <input type="submit" value="CONTINUE TO PAYMENT" name="payment" />
                </td>
            </tr>            

            <%
            } // -end-if-
            %>
        

        </form>
    
<!-------------------------------------------------------------------------------------------------------------------------- 
    ****************************************************************************************************************************
          END FLIGHT SEARCH RESULTS FORM
    ****************************************************************************************************************************
    ------------------------------------------------------------------------------------------------------------------------ -->
    
        </tbody>
    </table>

</body>
</html>