结对作业小节

package org.example.utils;

import lombok.SneakyThrows;
import org.example.pojo.Station;
import org.jetbrains.annotations.NotNull;

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

public class SqlUtils {
    private static String driver;
    private static String url;
    private static String username;
    private static String password;
    private static Connection connection;
    private static PreparedStatement preparedStatement;
    static {
        try {
            driver = "com.mysql.jdbc.Driver";
            url = "jdbc:mysql://localhost:3307/subway";
            username = "root";
            password = "123456";
            connection = DriverManager.getConnection(url, username, password);
        }
        catch (Exception e){
            e.printStackTrace();
        }
    }
    @SneakyThrows
    public static Station getstation(int id){
        preparedStatement=connection.prepareStatement("select * from line where stationid=?", ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
        preparedStatement.setInt(1,id);
        ResultSet resultSet=preparedStatement.executeQuery();
        resultSet.next();
        if(resultSet.getRow()<1) return null;
        Station station=new Station();
        station.setStationid(id);
        station.setStationname(resultSet.getString("stationname"));
        List<String> lines=new ArrayList<>();
        if(resultSet.getInt(3)>0) lines.add("地铁1号线");
        if(resultSet.getInt(4)>0) lines.add("地铁八通线");
        if(resultSet.getInt(5)>0) lines.add("地铁2号线");
        if(resultSet.getInt(6)>0) lines.add("地铁4号线");
        if(resultSet.getInt(7)>0) lines.add("地铁大兴线");
        if(resultSet.getInt(8)>0) lines.add("地铁5号线");
        if(resultSet.getInt(9)>0) lines.add("地铁6号线");
        if(resultSet.getInt(10)>0) lines.add("地铁7号线");
        if(resultSet.getInt(11)>0) lines.add("地铁8号线");
        if(resultSet.getInt(12)>0) lines.add("地铁9号线");
        if(resultSet.getInt(13)>0) lines.add("地铁10号线");
        if(resultSet.getInt(14)>0) lines.add("地铁13号线");
        if(resultSet.getInt(15)>0) lines.add("地铁14号线");
        if(resultSet.getInt(16)>0) lines.add("地铁15号线");
        if(resultSet.getInt(17)>0) lines.add("地铁房山线");
        if(resultSet.getInt(18)>0) lines.add("地铁昌平线");
        if(resultSet.getInt(19)>0) lines.add("地铁亦庄线");
        if(resultSet.getInt(20)>0) lines.add("机场线");
        station.setLines(lines);
        List<Station> stations=new ArrayList<>();
        for(int i:getId(id)){
            Station temp=getstation(i);
            if(temp!=null) stations.add(temp);
        }
        station.setStations(stations);
        preparedStatement.close();
        connection.close();
        return station;
    }
    @SneakyThrows
    public static List<Integer> getId(int id){
        preparedStatement=connection.prepareStatement("select * from transfer where startid=?", ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
        preparedStatement.setInt(1,id);
        ResultSet resultSet=preparedStatement.executeQuery();
        List<Integer> Id=new ArrayList<>();
        while (resultSet.next()){
            Id.add(resultSet.getInt(2));
        }
        preparedStatement.close();
        return Id;
    }
    @SneakyThrows
    public static List<String> getAllstations(String line){
        preparedStatement=connection.prepareStatement("select * from line where ?>0 order by stationid",ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
        preparedStatement.setString(1,line);
        ResultSet resultSet=preparedStatement.executeQuery();
        List<String> stations=new ArrayList<>();
        while (resultSet.next()){
           stations.add(resultSet.getString("stationname"));
        }
        return stations;
    }
    @SneakyThrows
    public static List<String> getlines(String stationname){
        preparedStatement=connection.prepareStatement("select * from line where stationname like ?",ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
        preparedStatement.setString(1,stationname);
        ResultSet resultSet=preparedStatement.executeQuery();
        resultSet.next();
        List<String> lines=new ArrayList<>();
        if(resultSet.getInt(3)>0) lines.add("地铁1号线");
        if(resultSet.getInt(4)>0) lines.add("地铁八通线");
        if(resultSet.getInt(5)>0) lines.add("地铁2号线");
        if(resultSet.getInt(6)>0) lines.add("地铁4号线");
        if(resultSet.getInt(7)>0) lines.add("地铁大兴线");
        if(resultSet.getInt(8)>0) lines.add("地铁5号线");
        if(resultSet.getInt(9)>0) lines.add("地铁6号线");
        if(resultSet.getInt(10)>0) lines.add("地铁7号线");
        if(resultSet.getInt(11)>0) lines.add("地铁8号线");
        if(resultSet.getInt(12)>0) lines.add("地铁9号线");
        if(resultSet.getInt(13)>0) lines.add("地铁10号线");
        if(resultSet.getInt(14)>0) lines.add("地铁13号线");
        if(resultSet.getInt(15)>0) lines.add("地铁14号线");
        if(resultSet.getInt(16)>0) lines.add("地铁15号线");
        if(resultSet.getInt(17)>0) lines.add("地铁房山线");
        if(resultSet.getInt(18)>0) lines.add("地铁昌平线");
        if(resultSet.getInt(19)>0) lines.add("地铁亦庄线");
        if(resultSet.getInt(20)>0) lines.add("机场线");
        return lines;
    }
}