使用SQL语句实现最短路线问题-MySQL

今天学习了一种直接用sql语句实现查询最短路径的方法，为我们的系统开发提供了便利。

具体是使用SQL语句递归实现的

SQL语句：

String sql ="WITH RECURSIVE transfer (start_station, stop_station, stops, path) AS (\n" +
                    "    SELECT station_name, next_station, 1, CAST(CONCAT(line_name,station_name , '->', line_name,next_station) AS CHAR(1000))\n" +
                    "    FROM bj_subway WHERE station_name = ?\n" +
                    "    UNION ALL\n" +
                    "    SELECT p.start_station, e.next_station, stops + 1, CONCAT(p.path, '->', e.line_name, e.next_station)\n" +
                    "    FROM transfer p\n" +
                    "             JOIN bj_subway e\n" +
                    "                  ON p.stop_station = e.station_name AND (INSTR(p.path, e.next_station) = 0)\n" +
                    ")\n" +
                    "SELECT * FROM transfer WHERE stop_station = ?;\n";

 我将其写入了一个方法，返回所有的查询到的信息

代码参考：

package dao;

import com.BaseBean;
import com.DB;
import com.SubwayBean;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class Sub {

    public ArrayList<SubwayBean> ArrayTransferStation(String station1, String station2){

        ArrayList<SubwayBean> resultArray = new ArrayList<>();
        DB db = new DB();
        PreparedStatement preparedStatement=null;
        ResultSet rs = null;
        int i=0;

        try {
            String sql ="WITH RECURSIVE transfer (start_station, stop_station, stops, path) AS (\n" +
                    "    SELECT station_name, next_station, 1, CAST(CONCAT(line_name,' ',station_name , '--->', line_name,' ',next_station) AS CHAR(1000))\n" +
                    "    FROM station_table WHERE station_name = ?\n" +
                    "    UNION ALL\n" +
                    "    SELECT p.start_station, e.next_station, stops + 1, CONCAT(p.path, '--->', e.line_name, ' ',e.next_station)\n" +
                    "    FROM transfer p\n" +
                    "             JOIN station_table e\n" +
                    "                  ON p.stop_station = e.station_name AND (INSTR(p.path, e.next_station) = 0)\n" +
                    ")\n" +
                    "SELECT * FROM transfer WHERE stop_station =?;\n";

            preparedStatement=db.conn.prepareStatement(sql);
            preparedStatement.setString(1,station1);
            preparedStatement.setString(2,station2);
            rs = preparedStatement.executeQuery();

            while (rs.next())
            {
                i++;
                SubwayBean subwayBean = new SubwayBean();
                subwayBean._StartStation = (String) rs.getObject(1);
                subwayBean._EndStation = (String) rs.getObject(2);
                subwayBean._Number = rs.getObject(3);
                subwayBean._Path = (String) rs.getObject(4);
                resultArray.add(subwayBean);

            }
            if(i==0)
            {
                String result = "未查询到有关信息，输入不正确或输入为空，请输入有效信息";
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally{
            db.close();
        }
        return resultArray;
    }
  
}