4.12

1.完成了数据库的设计：

存储：线路号，车站唯一标识ID，线路的各个站名，车站的换乘信息等信息

 

 2.单线的站点查询：

输入开始站点和终点站点的名称，点击查询，能够查询出途径的站点。按照 起点到终点 排序。

部分代码：

复制代码
package com;

import java.sql.*;

public class DB {
    public Connection conn = null;
    public Statement stmt = null;
    public ResultSet rs = null;
    String url = "jdbc:mysql://localhost:3306/subway?useSSL=false&serverTimezone=UTC";
    String username = "root";
    String password = "1506583922";
    public DB(){
        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection(url,username,password);
            stmt = conn.createStatement();
        }catch(SQLException se){
            // 处理 JDBC 错误
            se.printStackTrace();
        }catch(Exception e){
            // 处理 Class.forName 错误
            e.printStackTrace();
        }
    }
    public void close(){
        try {
            if (rs!=null){
                rs.close();
            }
            if (stmt!=null){
                stmt.close();
            }
            if (conn!=null){
                conn.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }


}
复制代码
复制代码
package main;

import com.BaseBean;
import com.DB;
import dao.Sub;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import javax.xml.transform.Result;
import java.io.IOException;
import java.io.PrintWriter;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.sql.*;

@WebServlet("/Subway1")
public class Subway1 extends HttpServlet {
    @Override
    public void init() throws ServletException {
    }
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {

        resp.setContentType("text/html;charset=UTF-8");
        PrintWriter out = resp.getWriter();
        //
        ArrayList<String> StationList = new ArrayList<>();
        //连接数据库，创建数据库对象
        DB db = new DB();
        //从表单获取数据

        String start = new String(req.getParameter("start_station").getBytes("ISO8859-1"),"UTF-8");
        String end = new String(req.getParameter("end_station").getBytes("ISO8859-1"),"UTF-8");
        int s_num = 0;
        int e_num = 0;
        try {


            Sub sub = new Sub();

            s_num = sub.getStationNum(start);
            e_num = sub.getStationNum(end);

            if (s_num>e_num){

                for (int i = e_num; i <= s_num; i++) {
                    BaseBean data = sub.getStationByNum(i);
                    String na = data.base_name;
                    StationList.add(na);
                }

            } else if (e_num>s_num) {

                for (int i = e_num; i >= s_num; i--) {
                    BaseBean data = sub.getStationByNum(i);
                    String na = data.base_name;
                    StationList.add(na);
                }
            }else {

            }

            out.println("<!DOCTYPE html> \n" +
                        "<html>\n" +
                        "<head><title>" + "查询结果" + "</title></head>\n" +
                        "<body bgcolor=\"#f0f0f0\">\n" +
                        "<h1 align=\"center\">" + "查询结果" + "</h1>\n"
            );

            for (int i =  StationList.size()-1; i >=0; i--) {
                String n = StationList.get(i);
                out.println(n);
            }

            out.println("</body></html>");
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            db.close();
        }

    }
    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        doGet(req, resp);
    }
    @Override
    public void destroy() {

    }
}
复制代码
复制代码
package com;

public class BaseBean {

    public String base_id;
    public String base_line;
    public int base_num;
    public String base_name;

}
复制代码

 

复制代码
package dao;

import com.BaseBean;
import com.DB;

import java.sql.PreparedStatement;
import java.sql.SQLException;

public class Sub {

    public BaseBean getStationByNum(int num){

        DB db = new DB();
        BaseBean base = new BaseBean();

        try {
            String sql = "SELECT * FROM basictable WHERE num = " + num ;
            db.rs =db.stmt.executeQuery(sql);

            if (db.rs.next()){
                base.base_id=db.rs.getString("id");
                base.base_line=db.rs.getString("line");
                base.base_num=db.rs.getInt("num");
                base.base_name=db.rs.getString("nam");

            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally{

        }
        db.close();
        return base;
    }

    public int getStationNum(String name){

        DB db = new DB();
        BaseBean base = new BaseBean();

        String na = "'" + name + "'";
        try {
            String selectQuery = "SELECT * FROM basictable WHERE nam="+na;
            db.rs =db.stmt.executeQuery(selectQuery);

            if (db.rs.next()){
                base.base_id=db.rs.getString("id");
                base.base_line=db.rs.getString("line");
                base.base_num=db.rs.getInt("num");
                base.base_name=db.rs.getString("nam");

            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally{

        }
        db.close();
        return base.base_num;
    }

}
posted on 2024-06-12 11:25  柟  阅读(18)  评论(0)    收藏  举报