4.20

今天开始小组编码了

连接数据库：

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>

2：

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：

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;
        }
    }
}