大二上学期做过只有两号线的地铁查询系统,但是只能在控制台操作。这一次将线路加到了六条,并且要求web实现,下面简述一下设计思路和具体代码实现:
1.数据库建表
于我自己习惯而言,我写javaweb项目第一件事就是建库建表。六条线路,我选择了分六个表存储,这样的话实际操作起来,分离的每一条线会使dao层写函数方便很多。
每一个表中只有三列属性:id,name,nearline
建表完成后,就需要在dbutil中写数据库连接的代码,这部分代码是模板代码,只需要修改库名即可。
package dbutil;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DButil {
public static String url = "jdbc:mysql://localhost:3306/subway?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8";
public static String user = "root";
public static String password = "";
public static String driver = "com.mysql.cj.jdbc.Driver";
public static Connection getConn() {
Connection conn = null;
try {
Class.forName(driver);
// 连续数据库
conn = DriverManager.getConnection(url, user, password);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
public static void close(Statement state, Connection conn) {
if(state!=null) {
try {
state.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn!=null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close(ResultSet rs, Statement state, Connection conn) {
if(rs!=null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(state!=null) {
try {
state.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn!=null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
下一步我选择写Javabean,因为每个表只有三个属性,所以javabean也十分简单,只有三个属性,只需要写他们的get和set方法,以及有参数和无参数的构造函数即可。
package bean;
public class Bean {
private String name;
private int number;
private String nearline;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getNumber() {
return number;
}
public void setNumber(int number) {
this.number = number;
}
public String getNearline() {
return nearline;
}
public void setNearline(String nearline) {
this.nearline = nearline;
}
public Bean() {};
public Bean(String name,int number,String nearline)
{
this.name=name;
this.number=number;
this.nearline=nearline;
}
}
到这里我们的前期准备工作就已经做完了,现在还剩下dao包,servlet和前端界面以及输出界面没有书写,因为是两人团队项目,队友擅长前端,于是前端界面就交给他了。
在他写前端的同时,我自己大概写了dao包中的几个常用函数,如果以后还有需求,再往上添加。select函数必备,还有一个检测输入的站点在几号线的函数checkstation(),还有一个带参数的select函数,方便通过参数调用select每条线的功能,简化代码量。除此之外,最重要的函数就是在一条线上给定两个参数,将他们之间站名全部输出的函数substation。
package dao;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import bean.Bean;
import dbutil.DButil;
public class Dao {
public String[] substation(Bean[] sub,String s1,String s2)
{
ArrayList<String> stationlist =new ArrayList<String>();
int start=0;
int end=0;
int money;
int i;
for(i=0;i<sub.length;i++)
{
if(sub[i].getName().equals(s1))
start=i;
}//确定起始站
for(i=0;i<sub.length;i++)
{
if(sub[i].getName().equals(s2))
end=i;
}//确定终点站
if(start<end)
{
for(i=start;i<=end;i++)
{
stationlist.add(sub[i].getName());
}//输出站点信息
}
else
{
for(i=start;i>=end;i--)
{
stationlist.add(sub[i].getName());
}//输出站点信息
}
return stationlist.toArray(new String[stationlist.size()]);
}
public int checkstation(String start)
{
Dao dao=new Dao();
int i;
for(i=0;i<dao.selectno1().length;i++)
{
if(dao.selectno1()[i].getName().equals(start))
{
return 1;
}
}
for(i=0;i<dao.selectno2().length;i++)
{
if(dao.selectno2()[i].getName().equals(start))
{
return 2;
}
}
for(i=0;i<dao.selectno3().length;i++)
{
if(dao.selectno3()[i].getName().equals(start))
{
return 3;
}
}
for(i=0;i<dao.selectno4().length;i++)
{
if(dao.selectno4()[i].getName().equals(start))
{
return 4;
}
}
for(i=0;i<dao.selectno5().length;i++)
{
if(dao.selectno5()[i].getName().equals(start))
{
return 5;
}
}
for(i=0;i<dao.selectno6().length;i++)
{
if(dao.selectno6()[i].getName().equals(start))
{
return 6;
}
}
return 0;
}
public Bean[] select(int n)
{
Bean[] a=new Bean[] {};
Dao dao=new Dao();
if(n==1)
return dao.selectno1();
if(n==2)
return dao.selectno2();
if(n==3)
return dao.selectno3();
if(n==4)
return dao.selectno4();
if(n==5)
return dao.selectno5();
if(n==6)
return dao.selectno6();
else
return a;
}
public Bean[] selectno1()
{
ArrayList<Bean> list=new ArrayList();
Connection connection = DButil.getConn();
Statement statement=null;
ResultSet rs = null;
try
{
statement = connection.createStatement();
// 要执行的SQL语句
String sql = "select * from no1 ";
// 结果集
rs = statement.executeQuery(sql);
while(rs.next())
{
Bean selectbean=new Bean();
String name = rs.getString("name");
int number = rs.getInt("id");
String nearline = rs.getString("nearline");
selectbean.setName(name);
selectbean.setNumber(number);
selectbean.setNearline(nearline);
list.add(selectbean);
}
} catch (Exception e)
{
e.printStackTrace();
} finally
{
DButil.close(rs, statement, connection);
}
return list.toArray(new Bean[list.size()]);
}//获取一号线的所有信息
public Bean[] selectno2()
{
ArrayList<Bean> list=new ArrayList();
Connection connection = DButil.getConn();
Statement statement=null;
ResultSet rs = null;
try
{
statement = connection.createStatement();
// 要执行的SQL语句
String sql = "select * from no2 ";
// 结果集
rs = statement.executeQuery(sql);
while(rs.next())
{
Bean selectbean=new Bean();
String name = rs.getString("name");
int number = rs.getInt("id");
String nearline = rs.getString("nearline");
selectbean.setName(name);
selectbean.setNumber(number);
selectbean.setNearline(nearline);
list.add(selectbean);
}
} catch (Exception e)
{
e.printStackTrace();
} finally
{
DButil.close(rs, statement, connection);
}
return list.toArray(new Bean[list.size()]);
}//获取二号线的所有信息
public Bean[] selectno3()
{
ArrayList<Bean> list=new ArrayList();
Connection connection = DButil.getConn();
Statement statement=null;
ResultSet rs = null;
try
{
statement = connection.createStatement();
// 要执行的SQL语句
String sql = "select * from no3 ";
// 结果集
rs = statement.executeQuery(sql);
while(rs.next())
{
Bean selectbean=new Bean();
String name = rs.getString("name");
int number = rs.getInt("id");
String nearline = rs.getString("nearline");
selectbean.setName(name);
selectbean.setNumber(number);
selectbean.setNearline(nearline);
list.add(selectbean);
}
} catch (Exception e)
{
e.printStackTrace();
} finally
{
DButil.close(rs, statement, connection);
}
return list.toArray(new Bean[list.size()]);
}//获取三号线的全部信息
public Bean[] selectno4()
{
ArrayList<Bean> list=new ArrayList();
Connection connection = DButil.getConn();
Statement statement=null;
ResultSet rs = null;
try
{
statement = connection.createStatement();
// 要执行的SQL语句
String sql = "select * from no4 ";
// 结果集
rs = statement.executeQuery(sql);
while(rs.next())
{
Bean selectbean=new Bean();
String name = rs.getString("name");
int number = rs.getInt("id");
String nearline = rs.getString("nearline");
selectbean.setName(name);
selectbean.setNumber(number);
selectbean.setNearline(nearline);
list.add(selectbean);
}
} catch (Exception e)
{
e.printStackTrace();
} finally
{
DButil.close(rs, statement, connection);
}
return list.toArray(new Bean[list.size()]);
}//获取四号线的全部信息
public Bean[] selectno5()
{
ArrayList<Bean> list=new ArrayList();
Connection connection = DButil.getConn();
Statement statement=null;
ResultSet rs = null;
try
{
statement = connection.createStatement();
// 要执行的SQL语句
String sql = "select * from no5 ";
// 结果集
rs = statement.executeQuery(sql);
while(rs.next())
{
Bean selectbean=new Bean();
String name = rs.getString("name");
int number = rs.getInt("id");
String nearline = rs.getString("nearline");
selectbean.setName(name);
selectbean.setNumber(number);
selectbean.setNearline(nearline);
list.add(selectbean);
}
} catch (Exception e)
{
e.printStackTrace();
} finally
{
DButil.close(rs, statement, connection);
}
return list.toArray(new Bean[list.size()]);
}//获取五号线的全部信息
public Bean[] selectno6()
{
ArrayList<Bean> list=new ArrayList();
Connection connection = DButil.getConn();
Statement statement=null;
ResultSet rs = null;
try
{
statement = connection.createStatement();
// 要执行的SQL语句
String sql = "select * from no6 ";
// 结果集
rs = statement.executeQuery(sql);
while(rs.next())
{
Bean selectbean=new Bean();
String name = rs.getString("name");
int number = rs.getInt("id");
String nearline = rs.getString("nearline");
selectbean.setName(name);
selectbean.setNumber(number);
selectbean.setNearline(nearline);
list.add(selectbean);
}
} catch (Exception e)
{
e.printStackTrace();
} finally
{
DButil.close(rs, statement, connection);
}
return list.toArray(new Bean[list.size()]);
}//获取六号线的全部信息
}
与此同时,我的队友已经将界面做好,给了我接口,我很佩服队友的功力,因为这个界面不套模板做成这样,已经是很不错了。