第一次结对作业-今日共同报告-01
今天的软工课程上，王老师让我们两个人一组，来完成结对作业
我（彭锁群）和结对伙伴 任烁玚 结组，合作完成地铁查询系统 。
今天编程任务主要由任烁玚完成， 我负责 一部分的编程任务和主要的测试任务。
我和伙伴的每日合作照：
今天的进度报告：
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;
}
}
