今日共同报告-11-完善Android端最短路径查询功能

今日共同报告-11

今天我完善了昨天试验的安卓端的最短路径查询，主要由我来完成，现在我们的系统可以输出全部可能的路线。

同时我稍稍润色了一下web端系统的外观布局和内部代码，系统已经基本完全定型了。

合作照片

安卓端效果展示：

现在只是一个粗略的界面，之后会完善。可以看到是可以查询出来的，显示不完全的问题之后当然会解决

 

 部分代码：

package com.example.dataapplication.DataBase;

import android.annotation.SuppressLint;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.example.dataapplication.Infomation.SubwayBean;

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

public class CRUD {
    private DBHelper dbHelper;

    public CRUD (Context context){dbHelper = new DBHelper(context);}

    @SuppressLint("Range")
    public ArrayList<SubwayBean> ArrayTransferStation(String station1, String station2){

        SQLiteDatabase db = dbHelper.getReadableDatabase();
        ArrayList<SubwayBean> resultArray = new ArrayList<>();
        int i=0;
        String sql =
                    "WITH RECURSIVE transfer(start_station, stop_station, stops, paths) AS (\n" +
                    "SELECT station_name, next_station, 1 stops, \n" +
                    "       line_name||station_name||'->'||line_name||next_station AS paths\n" +
                    "FROM bj_subway\n" +
                    "WHERE station_name = ?\n" +
                    "UNION ALL \n" +
                    "SELECT t.start_station, s.next_station, stops+1, paths||'->'||s.line_name||s.next_station\n" +
                    "FROM transfer t\n" +
                    "JOIN bj_subway s \n" +
                    "ON (t.stop_station = s.station_name AND instr(paths, s.next_station)=0)\n" +
                    ")\n" +
                    "SELECT *\n" +
                    "FROM transfer\n" +
                    "WHERE stop_station = ?;";

        Cursor cursor = db.rawQuery(sql,new String[]{station1,station2});

        if (cursor.moveToFirst()){
            do {
                i++;
                SubwayBean subwayBean = new SubwayBean();
                subwayBean._StartStation = cursor.getString(cursor.getColumnIndex("start_station"));
                subwayBean._EndStation = cursor.getString(cursor.getColumnIndex("stop_station"));
                subwayBean._Number = cursor.getString(cursor.getColumnIndex("stops"));
                subwayBean._Path = cursor.getString(cursor.getColumnIndex("paths"));
                resultArray.add(subwayBean);
            }while (cursor.moveToNext());
        }
        if(i==0)
        {
            return null;
        }
        cursor.close();
        db.close();
        return resultArray;
    }

    @SuppressLint("Range")
    public SubwayBean BestTransferStation(String station1, String station2){

        SQLiteDatabase db = dbHelper.getReadableDatabase();
        String sql =
                "WITH RECURSIVE transfer(start_station, stop_station, stops, paths) AS (\n" +
                        "SELECT station_name, next_station, 1 stops, \n" +
                        "       line_name||station_name||'->'||line_name||next_station AS paths\n" +
                        "FROM bj_subway\n" +
                        "WHERE station_name = ?\n" +
                        "UNION ALL \n" +
                        "SELECT t.start_station, s.next_station, stops+1, paths||'->'||s.line_name||s.next_station\n" +
                        "FROM transfer t\n" +
                        "JOIN bj_subway s \n" +
                        "ON (t.stop_station = s.station_name AND instr(paths, s.next_station)=0)\n" +
                        ")\n" +
                        "SELECT *\n" +
                        "FROM transfer\n" +
                        "WHERE stop_station = ?;";

        Cursor cursor = db.rawQuery(sql,new String[]{station1,station2});
        SubwayBean subwayBean = new SubwayBean();
        if (cursor.moveToFirst()){
            subwayBean._StartStation = cursor.getString(cursor.getColumnIndex("start_station"));
            subwayBean._EndStation = cursor.getString(cursor.getColumnIndex("stop_station"));
            subwayBean._Number = cursor.getString(cursor.getColumnIndex("stops"));
            subwayBean._Path = cursor.getString(cursor.getColumnIndex("paths"));
        }
        cursor.close();
        db.close();
        return  subwayBean;
    }

}

 

package com.example.dataapplication;

import androidx.appcompat.app.AppCompatActivity;
import androidx.recyclerview.widget.LinearLayoutManager;
import androidx.recyclerview.widget.RecyclerView;

import android.content.Intent;
import android.os.Bundle;

import com.example.dataapplication.DataBase.CRUD;
import com.example.dataapplication.Infomation.SubwayBean;
import com.example.dataapplication.adapter.ListViewAdapter;

import java.util.ArrayList;
import java.util.List;

public class FindActivity extends AppCompatActivity {

    private RecyclerView mList;
    private List<SubwayBean> mData;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_find);
        mList = (RecyclerView) this.findViewById(R.id.re_1);
        initData();
    }
    private void initData() {
        mData = new ArrayList<>();
        CRUD crud = new CRUD(this);
        //导入数据

        Intent intent = getIntent();
        String start = intent.getStringExtra("start_text");
        String end = intent.getStringExtra("end_text");
        ArrayList<SubwayBean> subList = crud.ArrayTransferStation(start,end);

        for (int i = 0; i < subList.size(); i++) {

            SubwayBean subway = new SubwayBean();
            SubwayBean subData =subList.get(i);
            subway._StartStation = subData._StartStation;
            subway._EndStation = subData._EndStation;
            subway._Number = subData._Number;
            subway._Path = subData._Path;
            mData.add(subway);
        }
        //设置布局管理器
        LinearLayoutManager linearLayoutManager = new LinearLayoutManager(this);
        mList.setLayoutManager(linearLayoutManager);
        //创建适配器
        ListViewAdapter adapter = new ListViewAdapter(mData);
        //设置到listview里
        mList.setAdapter(adapter);
    }
    protected void onRestart() {
        super.onRestart();
        setContentView(R.layout.activity_find);
        mList = (RecyclerView) this.findViewById(R.id.re_1);
        initData();
    }

}