地铁查询系统
2023.6.10
1、优化返回两个站点之间最短路径功能：成为一个类，进行单元测试。
2、生成遍历车站类：要求尽可能快地遍历地铁的所有车站呢（只用经过一次，不用下车，就算经过车站）。
连接数据库：
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://10.99.112.73:3306/dab2?useUnicode=true&characterEncoding=utf8", "zsf", "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;
}
}
页面：
<?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"
android:background="@mipmap/dietie"
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>
页面：
<?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"
android:background="@mipmap/dietie"
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>
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;
}
}
}
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;
}
}
}