package TangXuefang;

import java.sql.*;
import java.util.Vector;


public class DAO {
    java.sql.Connection con;
    java.sql.PreparedStatement pst;
    java.sql.ResultSet rs;
    public DAO() {
        try {
            //加載驅(qū)動
            Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        }
        try {
            jbInit();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    public void getcon() {
        //一個連接的方法
        try {
            con = DriverManager.getConnection(
                    "jdbc:microsoft:sqlserver://127.0.0.1:1433;DatabaseName=Myticket",
                    "sa", "");

        } catch (SQLException ex) {
            ex.printStackTrace();
        }

    }

//一個判斷輸入是否區(qū)分大小寫的方法
    public String[] verdict(bean a) {
        getcon();
        String[] v = new String[2];
        try {
            pst = con.prepareStatement("select leavecity,destination from ticketinfo where (leavecity=? and destination=?)");
            pst.setString(1, a.getLeavecity());
            pst.setString(2, a.getDestination());
            rs = pst.executeQuery();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        try {
            if (rs.next()) {
                v[0] = rs.getString(1);
                v[1] = rs.getString(2);
            }
        } catch (SQLException ex1) {
            ex1.printStackTrace();
        } finally {
            try {
                rs.close();
                pst.close();
                con.close();

            } catch (SQLException ex2) {
                ex2.printStackTrace();
            }

        }

        return v;
    }


    //一個查詢的方法
    public Vector select(bean a) {
        getcon();
        Vector v = new Vector();
        try {
            pst = con.prepareStatement("select flightno from ticketinfo where (leavecity=? and destination=? and econum>=?)");
            pst.setString(1, a.getLeavecity());
            pst.setString(2, a.getDestination());
            pst.setInt(3, Integer.parseInt(a.getEconum()));
            rs = pst.executeQuery();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        try {
            while (rs.next()) {

                v.addElement("\n" + rs.getString(1));

            }
        } catch (SQLException ex1) {
            ex1.printStackTrace();
        } finally {
            try {
                rs.close();
                pst.close();
                con.close();

            } catch (SQLException ex2) {
                ex2.printStackTrace();
            }

        }

        return v;
    }

    private void jbInit() throws Exception {
    }

}