int demostartid=0,demoendid=0;
ArrayList<String> array=new ArrayList<String>();
/*
* 起始点与终点在一条线上且非换乘站
*/
public ArrayList<String> Connectonlyoneline(Line line) throws Exception {
ArrayList<String> array1 = new ArrayList<String>();
int num = 0;
Connection connection = jdbcUtil.getconnection();
String sql = "select * from firstline f1 JOIN firstline f2 on f1.Line=f2.Line WHERE f1.StopName=? AND f2.StopName=?";
//保证起始站（同时有可能是中转站）与终点站（同时有可能是中转站）在同一条线路上
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setString(1, line.getStartstopname());
pstmt.setString(2, line.getEndstopname());
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
line.setTemporaryline(rs.getInt("Line"));
}
System.out.println(line.getTemporaryline());
array.add("乘坐" + line.getTemporaryline() + "号线");
num = select(line);
//起始站编号小于终点站，即起始站在前，终点站在后
if (num == 1) {
String sql1 = "select * from firstline where StopID>=(select StopID from firstline where StopName=? ) and StopID<=(select StopID from firstline where StopName=? ) and Line=?";
/*
* 找到这样的一些站：它们的ID号大于起始站的ID号，
* 它们的ID号小于结束站的ID号，并且它们都是同一条线上的站点
*/
PreparedStatement pstmt1 = connection.prepareStatement(sql1);
pstmt1.setString(1, line.getStartstopname());
pstmt1.setString(2, line.getEndstopname());
pstmt1.setInt(3, line.getTemporaryline());
ResultSet rs1 = pstmt1.executeQuery();
while (rs1.next()) {
//遍历结果集
array.add(rs1.getString("StopName"));
}
jdbcUtil.close(pstmt1);
jdbcUtil.close(rs1);
} else if (num == 2) {
//起始站编号大于终点站，即起始站在后，终点站在前
String sql2 = "select * from firstline where StopID<=(select StopID from firstline where StopName=? ) and StopID>=(select StopID from firstline where StopName=? ) and Line=?";
/*
* 同上
*/
PreparedStatement pstmt2 = connection.prepareStatement(sql2);
pstmt2.setString(1, line.getStartstopname());
pstmt2.setString(2, line.getEndstopname());
pstmt2.setInt(3, line.getTemporaryline());
ResultSet rs2 = pstmt2.executeQuery();
while (rs2.next()) {
array1.add(rs2.getString("StopName"));
}
for (int i = 0; i < array1.size(); i++) {//测试是否有值
System.out.print(array1.get(i) + " ");
}
for (int j = array1.size() - 1; j >= 0; j--) {//将站名倒序传入
array.add(array1.get(j));
}
jdbcUtil.close(pstmt2);
jdbcUtil.close(rs2);
}
jdbcUtil.close(connection);
jdbcUtil.close(pstmt);
jdbcUtil.close(rs);
return array;
}
/*
* 两条不同的、有交点的线路
*/
public ArrayList<String> Connecttwoline(Line line) throws Exception {
int num = 0;
Connection connection = jdbcUtil.getconnection();
String sql = "select * from changeline where ID1=? and ID2=? ";
//根据交点线路查询中转站
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setInt(1, line.getOriginline());
pstmt.setInt(2, line.getFinishline());
ResultSet rs = pstmt.executeQuery();
while (rs.next())
{
line.setMiddlestop(rs.getString("ChangeStopName"));
}
//当前起始点与结束点,存放
line.setTemporarystartstopname(line.getStartstopname());
line.setTemporaryendstopname(line.getEndstopname());
String sql1="select * from firstline where Line=? and StopName=? ";
//根据起始线路和中转站名称标出中转站位置
PreparedStatement pstmt1 = connection.prepareStatement(sql1);
pstmt1.setInt(1, line.getOriginline());
pstmt1.setString(2, line.getMiddlestop());
ResultSet rs1 = pstmt1.executeQuery();
while(rs1.next()) {
line.setEndstopname(rs1.getString("StopName"));
//起始点不变，结束点设置为中转点
}
//将起始点和中转点所在线路一致的放入临时站线路
line.setStartstopname(line.getTemporarystartstopname());
line.setTemporaryline(line.getOriginline());
array.add("乘坐"+line.getOriginline()+"号线");
num = select(line);
if (num == 1) {
String sql2 = "select * from firstline where StopID>=(select StopID from firstline where StopName=? ) and StopID<=(select StopID from firstline where StopName=? ) and Line=? ";
/*
* 找到这样的一些站：它们的ID号大于起始站的ID号，
* 它们的ID号小于结束站的ID号，并且它们都是同一条线上的站点
*/
PreparedStatement pstmt2 = connection.prepareStatement(sql2);
pstmt2.setString(1, line.getStartstopname());
pstmt2.setString(2, line.getEndstopname());
pstmt2.setInt(3, line.getTemporaryline());
ResultSet rs2 = pstmt2.executeQuery();
while (rs2.next()) {
//遍历结果集
array.add(rs2.getString("StopName"));
}
jdbcUtil.close(pstmt2);
jdbcUtil.close(rs2);
} else if (num == 2) {
ArrayList<String> array1=new ArrayList<String>();
//起始站编号大于终点站，即起始站在后，终点站在前
String sql2 = "select * from firstline where StopID<=(select StopID from firstline where StopName=? ) and StopID>=(select StopID from firstline where StopName=? ) and Line=?";
/*
* 同上
*/
PreparedStatement pstmt2 = connection.prepareStatement(sql2);
pstmt2.setString(1, line.getStartstopname());
pstmt2.setString(2, line.getEndstopname());
pstmt2.setInt(3, line.getTemporaryline());
ResultSet rs2 = pstmt2.executeQuery();
while (rs2.next()) {
array1.add(rs2.getString("StopName"));
}
for (int i = 0; i < array1.size(); i++) {//测试是否有值
System.out.print(array1.get(i) + " ");
}
for (int j = array1.size() - 1; j >= 0; j--) {//将站名倒序传入
array.add(array1.get(j));
}
jdbcUtil.close(pstmt2);
jdbcUtil.close(rs2);
}
array.add("转乘"+line.getFinishline()+"号线");
/*
*
*
*
*
*/
line.setStartstopname(line.getMiddlestop());
//将起始点设为中转点
line.setEndstopname(line.getTemporaryendstopname());
//将结束点回归
line.setTemporaryline(line.getFinishline());
//将中转点和结束点所在线路一致的放入临时站线路
num = select(line);
if (num == 1) {
String sql3 = "select * from firstline where StopID>=(select StopID from firstline where StopName=?) and StopID<=(select StopID from firstline where StopName=? ) and Line=? ";
/*
* 找到这样的一些站：它们的ID号大于起始站的ID号，
* 它们的ID号小于结束站的ID号，并且它们都是同一条线上的站点
*/
PreparedStatement pstmt3 = connection.prepareStatement(sql3);
pstmt3.setString(1, line.getStartstopname());
pstmt3.setString(2, line.getEndstopname());
pstmt3.setInt(3, line.getTemporaryline());
ResultSet rs3= pstmt3.executeQuery();
while (rs3.next()) {
//遍历结果集
array.add(rs3.getString("StopName"));
}
System.out.println(line.getStartstopname());
System.out.println(line.getTemporaryline());
System.out.println(line.getEndstopname());
System.out.println(line.getFinishline());
jdbcUtil.close(pstmt3);
jdbcUtil.close(rs3);
} else if (num == 2) {
ArrayList<String> array1=new ArrayList<String>();
//起始站编号大于终点站，即起始站在后，终点站在前
String sql3 = "select * from firstline where StopID<=(select StopID from firstline where StopName=? ) and StopID>=(select StopID from firstline where StopName=? ) and Line=?";
/*
* 同上
*/
PreparedStatement pstmt3 = connection.prepareStatement(sql3);
pstmt3.setString(1, line.getStartstopname());
pstmt3.setString(2, line.getEndstopname());
pstmt3.setInt(3, line.getTemporaryline());
ResultSet rs3 = pstmt3.executeQuery();
while (rs3.next()) {
array1.add(rs3.getString("StopName"));
}
for (int i = 0; i < array1.size(); i++) {//测试是否有值
System.out.print(array1.get(i) + " ");
}
for (int j = array1.size() - 1; j >= 0; j--) {//将站名倒序传入
array.add(array1.get(j));
}
jdbcUtil.close(pstmt3);
jdbcUtil.close(rs3);
}
jdbcUtil.close(connection);
jdbcUtil.close(pstmt);
jdbcUtil.close(rs);
jdbcUtil.close(pstmt1);
jdbcUtil.close(rs1);
return array;
}
浙公网安备 33010602011771号