App端地铁查询 设计思路 源程序代码、运行结果截图、编程总结分析。

关于地铁查询App 的设计思路。

设计思路：我们沿用pc端的数据库设计结构，减少了我们的工作量。查询方法以及设计思路都很javaweb设计的大同小异。

在设计中我们遇到的主要问题就是安卓Studio中的mysql数据库的连接。（查询数据库时主线程与子线程冲突的问题解决）

查询大致分为三大类：一：线路查询。二：站点查询。三：起点与终点查询（完成换乘与最短路径的查询）。

为了代码更简洁，更加的有层次化。以便于后期的维护，我们把每个查询方法都写了一个类。如果哪个查询出现错误我们团队可以很快找到问题所在并解决它。

我们把查询的方法都写在dbutil中，以便于修改查询方法。

源程序代码：、

数据库的连接以及查询方法类

package com.example.underground;

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

public class DBUtil {

    //连接数据库
    public Connection getConnection()  {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
        Connection con = null;
        try {
            con = DriverManager.getConnection("jdbc:mysql://192.168.46.129:3306/dab2?useUnicode=true&characterEncoding=utf8", "root", "1234");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return con;
    }

    //check1
    public String getCheck1(String text) throws SQLException {

        String data="此地铁线路途径站点如下："+"\n";
        Connection connection = getConnection();
        PreparedStatement preparedStatement = null;
        String sql = "select distinct station_name from bj_subway where line_name like ?";
        preparedStatement=connection.prepareStatement(sql);
        preparedStatement.setString(1, "%"+text+"%");
        ResultSet rs=preparedStatement.executeQuery();

        while(rs.next()){
            data+=rs.getString("station_name");
            data+="\n";

        }
        return data;
    }

    //check2
    public String getCheck2(String text) throws SQLException {

        String data="途径该站点的线路如下："+"\n";
        Connection connection = getConnection();
        PreparedStatement preparedStatement = null;
        String sql = "select distinct line_name from bj_subway where station_name = ?";
        preparedStatement=connection.prepareStatement(sql);
        preparedStatement.setString(1, text);
        ResultSet rs=preparedStatement.executeQuery();

        while(rs.next()){
            data+=rs.getString("line_name");
            data+="\n";

        }
        return data;
    }

    //check3
    public String getCheck3(String text1,String text2) throws SQLException {

        String data="";
        Connection connection = getConnection();
        PreparedStatement preparedStatement = null;
        String sql = "WITH RECURSIVE transfer (start_station, stop_station, stops, path) AS ( SELECT station_name, next_station, 1, CAST(CONCAT(line_name,station_name , '\n', line_name,next_station) AS CHAR(1000)) FROM bj_subway WHERE station_name = ? UNION ALL SELECT p.start_station, e.next_station, stops + 1, CONCAT(p.path, '\n', e.line_name, e.next_station) FROM transfer p JOIN bj_subway e ON p.stop_station = e.station_name AND (INSTR(p.path, e.next_station) = 0) ) SELECT * FROM transfer WHERE stop_station = ?";
        preparedStatement=connection.prepareStatement(sql);
        preparedStatement.setString(1, text1);
        preparedStatement.setString(2, text2);
        ResultSet rs=preparedStatement.executeQuery();

        while(rs.next()){
            int stops=rs.getInt("stops")+1;
            data=data+"共经过"+stops+"站：\n";
            data+=rs.getString("path");
            data+="\n";
            break;

        }
        return data;
    }


}


查询1的java文件：

package com.example.underground;

import androidx.appcompat.app.AppCompatActivity;

import android.os.Bundle;
import android.os.StrictMode;
import android.text.TextUtils;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;

import java.sql.SQLException;

public class Check1Activity extends AppCompatActivity implements View.OnClickListener {

    private DBUtil util;
    private EditText etCheck1;
    private Button btnCheck1;
    private TextView tvCheck1;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_check1);
        setTitle("线路查询");

        util = new DBUtil();
        //忘了干啥的了
        StrictMode.ThreadPolicy policy = new StrictMode.ThreadPolicy.Builder().permitAll().build();
        StrictMode.setThreadPolicy(policy);

        etCheck1 = findViewById(R.id.et_check1);
        tvCheck1 = findViewById(R.id.tv_check1);
        btnCheck1 = findViewById(R.id.btn_check1);

        btnCheck1.setOnClickListener(this);
    }

    @Override
    public void onClick(View view) {
        switch (view.getId()) {
            case R.id.btn_check1:
                String text = etCheck1.getText().toString().trim();
                String data= "";
                if(TextUtils.isEmpty(text)){
                    data="输入为空！";
                }
                else {
                    try {
                        data = util.getCheck1(text);
                    } catch (SQLException e) {
                        data = "线路不存在！";
                    }
                    if(data.equals("此地铁线路途径站点如下：\n")){
                        data="线路不存在！";
                    }
                }
                tvCheck1.setText(data);
                break;
        }
    }
}

查询2的java文件：

package com.example.underground;

import androidx.appcompat.app.AppCompatActivity;

import android.os.Bundle;
import android.os.StrictMode;
import android.text.TextUtils;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;

import java.sql.SQLException;

public class Check2Activity extends AppCompatActivity implements View.OnClickListener {

    private DBUtil util;
    private EditText etCheck2;
    private Button btnCheck2;
    private TextView tvCheck2;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_check2);
        setTitle("站点查询");

        util = new DBUtil();
        //忘了干啥的了
        StrictMode.ThreadPolicy policy = new StrictMode.ThreadPolicy.Builder().permitAll().build();
        StrictMode.setThreadPolicy(policy);

        etCheck2 = findViewById(R.id.et_check2);
        tvCheck2 = findViewById(R.id.tv_check2);
        btnCheck2 = findViewById(R.id.btn_check2);

        btnCheck2.setOnClickListener(this);
    }

    @Override
    public void onClick(View view) {
        switch (view.getId()) {
            case R.id.btn_check2:
                String text = etCheck2.getText().toString().trim();
                String data= "";
                if(TextUtils.isEmpty(text)){
                    data="输入为空！";
                }
                else {
                    try {
                        data = util.getCheck2(text);
                    } catch (SQLException e) {
                        data = "站点不存在！";
                    }
                    if(data.equals("途径该站点的线路如下：\n")){
                        data="站点不存在！";
                    }
                }
                tvCheck2.setText(data);
                break;
        }
    }
}


查询3的java文件：

package com.example.underground;

import androidx.appcompat.app.AppCompatActivity;

import android.os.Bundle;
import android.os.StrictMode;
import android.text.TextUtils;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;

import java.sql.SQLException;

public class Check3Activity extends AppCompatActivity implements View.OnClickListener {

    private DBUtil util;
    private EditText etCheck31;
    private EditText etCheck32;
    private Button btnCheck3;
    private TextView tvCheck3;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_check3);
        setTitle("起点—终点查询");

        util = new DBUtil();
        //忘了干啥的了
        StrictMode.ThreadPolicy policy = new StrictMode.ThreadPolicy.Builder().permitAll().build();
        StrictMode.setThreadPolicy(policy);

        etCheck31 = findViewById(R.id.et_check31);
        etCheck32 = findViewById(R.id.et_check32);
        tvCheck3 = findViewById(R.id.tv_check3);
        btnCheck3 = findViewById(R.id.btn_check3);

        btnCheck3.setOnClickListener(this);
    }

    @Override
    public void onClick(View view) {
        switch (view.getId()) {
            case R.id.btn_check3:
                String text1 = etCheck31.getText().toString().trim();
                String text2 = etCheck32.getText().toString().trim();
                String data= "";
                if(TextUtils.isEmpty(text1)||TextUtils.isEmpty(text2)){
                    data="输入为空！";
                }
                else {
                    try {
                        data = util.getCheck3(text1,text2);
                    } catch (SQLException e) {
                        data = "两站点间不能通行！";
                    }
                    if(data.equals("")){
                        data="两站点间不能通行！";
                    }
                }
                tvCheck3.setText(data);
                break;
        }
    }
}

MainActivity的java文件（主要实现app端页面的跳转功能）

package com.example.underground;

import androidx.appcompat.app.AppCompatActivity;

import android.content.Intent;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;

public class MainActivity extends AppCompatActivity implements View.OnClickListener {

    private Button btnCheck1, btnCheck2, btnCheck3;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        setTitle("地铁查询系统");
        btnCheck1 = findViewById(R.id.btn_check1);
        btnCheck2 = findViewById(R.id.btn_check2);
        btnCheck3 = findViewById(R.id.btn_check3);

        btnCheck1.setOnClickListener(this);
        btnCheck2.setOnClickListener(this);
        btnCheck3.setOnClickListener(this);
    }

    @Override
    public void onClick(View view) {
        switch (view.getId()) {
            case R.id.btn_check1:
                startActivity(new Intent(this,Check1Activity.class));
                break;
            case R.id.btn_check2:
                startActivity(new Intent(this,Check2Activity.class));
                break;
            case R.id.btn_check3:
                startActivity(new Intent(this,Check3Activity.class));
                break;
        }
    }
}

查询主界面（activity_main.xml）:

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical"
    tools:context=".MainActivity">

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_marginLeft="20dp"
        android:layout_marginTop="40dp"
        android:layout_marginRight="20dp"
        android:gravity="center"
        android:orientation="horizontal">

        <TextView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="地铁查询系统"
            android:textSize="30sp"
            android:textColor="@color/black" />

    </LinearLayout>


    <Button
        android:id="@+id/btn_check1"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_marginLeft="20dp"
        android:layout_marginTop="20dp"
        android:layout_marginRight="20dp"
        android:text="线路查询"
        android:textSize="30sp" />

    <Button
        android:id="@+id/btn_check2"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_marginLeft="20dp"
        android:layout_marginTop="20dp"
        android:layout_marginRight="20dp"
        android:text="站点查询"
        android:textSize="30sp" />

    <Button
        android:id="@+id/btn_check3"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_marginLeft="20dp"
        android:layout_marginTop="20dp"
        android:layout_marginRight="20dp"
        android:text="起点-终点查询"
        android:textSize="30sp" />


</LinearLayout>

查询1的界面（activity_check1）:

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical"
    tools:context=".MainActivity">

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_marginLeft="30dp"
        android:layout_marginTop="40dp"
        android:layout_marginRight="30dp"
        android:gravity="center"
        android:orientation="horizontal">

        <TextView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="线路查询"
            android:textColor="@color/black"
            android:textSize="30sp" />

    </LinearLayout>

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_marginLeft="20dp"
        android:layout_marginTop="15dp"
        android:layout_marginRight="20dp"
        android:gravity="center_vertical"
        android:orientation="horizontal">

        <EditText
            android:id="@+id/et_check1"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:layout_marginLeft="10dp"
            android:gravity="center_horizontal"
            android:hint="请输入要查询的线路"
            android:selectAllOnFocus="true"
            android:inputType="text"
            android:paddingLeft="10dp"
            android:textSize="18sp" />

    </LinearLayout>


    <Button
        android:id="@+id/btn_check1"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_marginLeft="100dp"
        android:layout_marginTop="10dp"
        android:layout_marginRight="100dp"
        android:text="线路查询"
        android:textSize="30sp" />

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_marginLeft="30dp"
        android:layout_marginTop="10dp"
        android:layout_marginRight="30dp"
        android:gravity="center"
        android:orientation="horizontal">

        <ScrollView
            android:id="@+id/sv"
            android:layout_width="fill_parent"
            android:layout_height="wrap_content"
            >
        <TextView
            android:id="@+id/tv_check1"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="null"
            android:textColor="@color/black"
            android:textSize="25sp" />
        </ScrollView>

    </LinearLayout>


</LinearLayout>

查询2的界面（avtivity_check2）:

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical"
    tools:context=".MainActivity">

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_marginLeft="20dp"
        android:layout_marginTop="40dp"
        android:layout_marginRight="20dp"
        android:gravity="center"
        android:orientation="horizontal">

        <TextView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="站点查询"
            android:textColor="@color/black"
            android:textSize="30sp" />

    </LinearLayout>

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_marginLeft="20dp"
        android:layout_marginTop="15dp"
        android:layout_marginRight="20dp"
        android:gravity="center_vertical"
        android:orientation="horizontal">

        <EditText
            android:id="@+id/et_check2"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:layout_marginLeft="10dp"
            android:gravity="center_horizontal"
            android:hint="请输入要查询的站点"
            android:inputType="text"
            android:paddingLeft="10dp"
            android:textSize="18sp" />

    </LinearLayout>

    <Button
        android:id="@+id/btn_check2"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_marginLeft="100dp"
        android:layout_marginTop="10dp"
        android:layout_marginRight="100dp"
        android:text="站点查询"
        android:textSize="30sp" />

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_marginLeft="30dp"
        android:layout_marginTop="10dp"
        android:layout_marginRight="30dp"
        android:gravity="center"
        android:orientation="horizontal">

        <ScrollView
            android:id="@+id/sv"
            android:layout_width="fill_parent"
            android:layout_height="wrap_content"
            >
        <TextView
            android:id="@+id/tv_check2"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="null"
            android:textColor="@color/black"
            android:textSize="25sp" />
        </ScrollView>

    </LinearLayout>


</LinearLayout>

查询3的界面(activity_check3):

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical"
    tools:context=".MainActivity">

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_marginLeft="20dp"
        android:layout_marginTop="40dp"
        android:layout_marginRight="20dp"
        android:gravity="center"
        android:orientation="horizontal">

        <TextView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="起点-终点查询"
            android:textColor="@color/black"
            android:textSize="30sp" />

    </LinearLayout>

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_marginLeft="20dp"
        android:layout_marginTop="10dp"
        android:layout_marginRight="20dp"
        android:gravity="center_vertical"
        android:orientation="vertical">

        <EditText
            android:id="@+id/et_check31"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:layout_marginLeft="10dp"
            android:gravity="center_horizontal"
            android:hint="请输入要查询的起点"
            android:selectAllOnFocus="true"
            android:inputType="text"
            android:paddingLeft="10dp"
            android:textSize="18sp" />

        <EditText
            android:id="@+id/et_check32"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:layout_marginLeft="10dp"
            android:layout_marginTop="10dp"
            android:gravity="center_horizontal"
            android:hint="请输入要查询的终点"
            android:selectAllOnFocus="true"
            android:inputType="text"
            android:paddingLeft="10dp"
            android:textSize="18sp" />

    </LinearLayout>

    <Button
        android:id="@+id/btn_check3"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_marginLeft="70dp"
        android:layout_marginTop="10dp"
        android:layout_marginRight="70dp"
        android:text="起点-终点查询"
        android:textSize="30sp" />

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_marginLeft="30dp"
        android:layout_marginTop="10dp"
        android:layout_marginRight="30dp"
        android:gravity="center"
        android:orientation="horizontal">

        <ScrollView
            android:id="@+id/sv"
            android:layout_width="fill_parent"
            android:layout_height="wrap_content"
            >
        <TextView
            android:id="@+id/tv_check3"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="null"
            android:textColor="@color/black"
            android:textSize="25sp" />
        </ScrollView>

    </LinearLayout>


</LinearLayout>

运行结果截图：

 

 

编程总结分析：本次地铁查询app端的编写我们团队学会了很多的新技术例如：安卓STUDIO连接mysql数据库与javaweb之间的区别。

在开发中我与我的队友之间的默契更好，编码也更加的规范了。通过本次的结对开发我们未来的合作水平会越来越高。