第一次就对作业(5)

按线路查询,按站点查询

public class OneTwoDao {
    ArrayList<String> array=new ArrayList<String>();
    /*
     *     按线路查询
     */
    public ArrayList<String> select_line(Line line) throws Exception {
        int num = 0;
        Connection connection = jdbcUtil.getconnection();
        String sql = "select * from firstline  WHERE line=?";
        PreparedStatement pstmt = connection.prepareStatement(sql);
        pstmt.setInt(1, line.getTemporaryline());
        ResultSet rs = pstmt.executeQuery();
        while (rs.next()) {
            String station = (rs.getString("StopName"));
            array.add(station);
        }

        String sql1 = "select * from changeline  WHERE ID1=? or ID2=?";
        PreparedStatement pstmt1 = connection.prepareStatement(sql1);
        pstmt1.setInt(1, line.getTemporaryline());
        pstmt1.setInt(2, line.getTemporaryline());
        ResultSet rs1 = pstmt1.executeQuery();
        while (rs1.next()) {
            int ID1 = (rs1.getInt("ID1"));
            int ID2 = (rs1.getInt("ID2"));
            String station = (rs1.getString("ChangeStopName")) +"--->" + ID1 + "号线" + "转"  + ID2 + "号线";
            array.add(station);
        }

        jdbcUtil.close(connection);
        jdbcUtil.close(pstmt);
        jdbcUtil.close(rs);
        jdbcUtil.close(pstmt1);
        jdbcUtil.close(rs1);
        return  array;
    }
    public ArrayList<String> select_station(Line line) throws Exception {
        int flag = 0;
        Connection connection = jdbcUtil.getconnection();

        String sql1 = "select * from changeline  WHERE ChangeStopName=?";
        PreparedStatement pstmt1 = connection.prepareStatement(sql1);
        pstmt1.setString(1, line.getStation());
        ResultSet rs1 = pstmt1.executeQuery();
        while (rs1.next()) {
            int ID1 = (rs1.getInt("ID1"));
            int ID2 = (rs1.getInt("ID2"));
            String line2 = String.valueOf(ID1) ;
            String line3 = String.valueOf(ID2) ;
            String line4 = line2 + "号线" + "转" + line3 + "号线";
            array.add(line4);
            flag = 1;
        }
        if(flag == 0)
        {
            String sql = "select * from firstline  WHERE StopName=?";
            PreparedStatement pstmt = connection.prepareStatement(sql);
            pstmt.setString(1, line.getStation());
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                int line1 = (rs.getInt("line")) ;
                String line2 = String.valueOf(line1) + "号线";
                array.add(line2);
            }
            jdbcUtil.close(pstmt);
            jdbcUtil.close(rs);
            return  array;
        }
        jdbcUtil.close(connection);
        jdbcUtil.close(pstmt1);
        jdbcUtil.close(rs1);
        return  array;
    }
}