4.18

/**
 * function：起终查询
 */
public static String shortest(User user) {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
    StringBuilder shortestPath = new StringBuilder();
    boolean foundResult = false; // 初始化找到结果的标志

    try {
        connection = JDBCUtils.getConn();

        String startingStation = user.getStarting_Station();
        String terminusStation = user.getTerminus_Station();

        // 假设你有一个名为 "station" 的表，代表车站网络
        String sql = "WITH RECURSIVE transfer (Starting_Station, Terminus_Station, stops, path) AS (\n" +
                "    SELECT Starting_Station, Terminus_Station, 1, CONCAT(' --from ', Starting_Station, ' to ', Terminus_Station)\n" +
                "    FROM station\n" +
                "    WHERE Starting_Station = ?\n" +
                "    UNION ALL\n" +
                "    SELECT s.Starting_Station, p.Terminus_Station, p.stops + 1, CONCAT(p.path, ' --from ', s.Line, ' to ', s.Starting_Station)\n" +
                "    FROM transfer p\n" +
                "    JOIN station s ON p.Terminus_Station = s.Starting_Station\n" +
                "    WHERE s.Starting_Station NOT IN (SELECT Terminus_Station FROM transfer WHERE Line = s.Line)\n" +
                ")\n" +
                "SELECT Starting_Station, Terminus_Station, stops, path\n" +
                "FROM transfer\n" +
                "WHERE Terminus_Station = ?;";

        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, startingStation);
        preparedStatement.setString(2, terminusStation);

        resultSet = preparedStatement.executeQuery();

        while (resultSet.next()) {
            String station = resultSet.getString("Starting_Station");
            shortestPath.append("站名：").append(station).append("\n"); // 追加车站名
            foundResult = true; // 设置找到结果的标志为 true
        }

        if (!foundResult) {
            shortestPath.append("没有找到路径"); // 当未找到路径时追加消息
        }

    } catch (SQLException e) {
        throw new RuntimeException(e);
    } finally {
        // 在 finally 块中关闭资源
        try {
            if (resultSet != null) {
                resultSet.close();
            }
            if (preparedStatement != null) {
                preparedStatement.close();
            }
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace(); // 处理或记录异常
        }
    }

    System.out.println(shortestPath.toString());

    return shortestPath.toString();
}