farepersistent.java

用户管理的系统设计

package persistent;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import persistent.FlightMsg;

public class FarePersistent 
{
	public List getAllCity(Connection con) throws Exception
	{
		PreparedStatement ps = con.prepareStatement("select origin from fare union select destination from fare");
		ResultSet rs = ps.executeQuery();
		List fares = new ArrayList();
		while(rs.next())
		{
			fares.add(rs.getString("origin"));
		}
		return fares;
	}
	
	public List getAllFlight(Connection con) throws Exception
	{
		PreparedStatement ps = con.prepareStatement("select route_desc from dbo.fare");
		ResultSet rs = ps.executeQuery();
		List flight = new ArrayList();
		while(rs.next())
		{
			flight.add(rs.getString("route_desc"));
		}
		return flight;
	}
	
	public List getAllFlightID(Connection con) throws Exception
	{
		PreparedStatement ps = con.prepareStatement("select route_code from dbo.flight_schedule");
		ResultSet rs = ps.executeQuery();
		List flightID = new ArrayList();
		while(rs.next())
		{
			flightID.add(rs.getString("route_code"));
		}
		return flightID;
	}
	
	public List getFlightMsg(Connection con,String cityname)throws Exception
	{
		List flightlist = new ArrayList();
		String strsql = "select fare.route_code,origin,destination,start_time,arrive_date,first_seat,bus_seat,eco_seat from fare,flight_schedule where fare.route_code = flight_schedule.route_code and (origin=? or destination=?)";
		try
		{
			PreparedStatement ps = con.prepareStatement(strsql);
			ps.setString(1,cityname);
			ps.setString(2,cityname);
			ResultSet rs = ps.executeQuery();
			
			while(rs.next())
			{
				flightlist.add(selectFlightMsg(rs));
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{}
		return flightlist;
	}
	
	public FlightMsg getFlightSchedule(Connection con,String schedulename)throws Exception
	{
		String strsql = "select * from flight_schedule where route_code=?";
		FlightMsg flightmsg = new FlightMsg();
		try
		{
			PreparedStatement ps = con.prepareStatement(strsql);
			ps.setString(1,schedulename);
			ResultSet rs = ps.executeQuery();
			while(rs.next())
			{
				flightmsg.setRoute_code(rs.getString("route_code"));
				flightmsg.setStart_time(rs.getString("start_time"));
				flightmsg.setArrive_date(rs.getString("arrive_date"));
				flightmsg.setFirst_seat(rs.getInt("first_seat"));
				flightmsg.setBus_seat(rs.getInt("bus_seat"));
				flightmsg.setEco_seat(rs.getInt("eco_seat"));
				
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return flightmsg;
	}
	
	public FlightMsg selectFlightMsg(ResultSet rs) throws Exception
	{
		FlightMsg flight = new FlightMsg();
		try
		{
			flight.setRoute_code(rs.getString("route_code"));
			flight.setOrigin(rs.getString("origin"));
			flight.setDestination(rs.getString("destination"));
			flight.setStart_time(rs.getString("start_time"));
			flight.setArrive_date(rs.getString("arrive_date"));
			flight.setFirst_seat(rs.getInt("first_seat"));
			flight.setBus_seat(rs.getInt("bus_seat"));
			flight.setEco_seat(rs.getInt("eco_seat"));
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}
		return flight;
	}
	
	public FareVo getFlightFare(Connection con,String flightname)throws Exception
	{
		String strsql = "select * from fare where route_desc=?";
		FareVo farevo = new FareVo();
		try
		{
			PreparedStatement ps = con.prepareStatement(strsql);
			ps.setString(1,flightname);
			ResultSet rs = ps.executeQuery();
			while(rs.next())
			{
				farevo.setRoute_code(rs.getString("route_code"));
				farevo.setRoute_desc(rs.getString("route_desc"));
				farevo.setOrigin(rs.getString("origin"));
				farevo.setDestination(rs.getString("destination"));
				farevo.setFirst_fare(rs.getFloat("first_fare"));
				farevo.setBus_fare(rs.getFloat("bus_fare"));
				farevo.setEco_fare(rs.getFloat("eco_fare"));
				
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return farevo;
	}
	
	public FareVo select(ResultSet rs) throws Exception
	{
		FareVo farevo = new FareVo();
		farevo.setRoute_code(rs.getString("route_code"));
		farevo.setRoute_desc(rs.getString("route_desc"));
		farevo.setOrigin(rs.getString("origin"));
		farevo.setDestination(rs.getString("destination"));
		farevo.setFirst_fare(rs.getFloat("first_fare"));
		farevo.setBus_fare(rs.getFloat("bus_fare"));
		farevo.setEco_fare(rs.getFloat("eco_fare"));
		return farevo;
	}
}