import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.model.*;
public class RouteService {
private Connection conn;
private PreparedStatement pstmt;
public RouteService(){
conn=new com.conn.Conn().getCon();
}
public Route queryByName(String name ) {
try {
pstmt=conn.prepareStatement("select * from sitename where name=?");
pstmt.setString(1,name);
ResultSet rs=pstmt.executeQuery();
if(rs.next()) {
Route r=new Route();
r.setName(rs.getString(1));
r.setTransfer(rs.getInt(2));
r.setNumber(rs.getInt(3));
return r;
}
return null;
}catch(SQLException e) {
e.printStackTrace();
return null;
}
}
public int judge(Route a,Route b) { //判断属于那种情况
int judge=0;
if(a.getTransfer()==0&&b.getTransfer()==0) { //不是换乘
if(a.getNumber()==b.getNumber()) {
judge=1; //在一条线
}
else judge=2; //不在一条线
}
else if(a.getTransfer()==1&&b.getTransfer()==1) {
int anumber1,anumber2,bnumber1,bnumber2;
anumber1=a.getNumber()%10;
anumber2=(a.getNumber()-anumber1)/10;
bnumber1=b.getNumber()%10;
bnumber2=(b.getNumber()-bnumber1)/10;
}
return judge;
}
public Route queryByNameFirst(String name ) { //first 表 姓名查询；
try {
pstmt=conn.prepareStatement("select * from first where name=?");
pstmt.setString(1,name);
ResultSet rs=pstmt.executeQuery();
if(rs.next()) {
Route r=new Route();
r.setId(rs.getInt(1));
r.setName(rs.getString(2));
return r;
}
return null;
}catch(SQLException e) {
e.printStackTrace();
return null;
}
}
public Route queryByNameSecond(String name ) { //second 表 姓名查询；
try {
pstmt=conn.prepareStatement("select * from second where name=?");
pstmt.setString(1,name);
ResultSet rs=pstmt.executeQuery();
if(rs.next()) {
Route r=new Route();
r.setId(rs.getInt(1));
r.setName(rs.getString(2));
return r;
}
return null;
}catch(SQLException e) {
e.printStackTrace();
return null;
}
}
public Route queryByNameThird(String name ) { //third 表 姓名查询；
try {
pstmt=conn.prepareStatement("select * from third where name=?");
pstmt.setString(1,name);
ResultSet rs=pstmt.executeQuery();
if(rs.next()) {
Route r=new Route();
r.setId(rs.getInt(1));
r.setName(rs.getString(2));
return r;
}
return null;
}catch(SQLException e) {
e.printStackTrace();
return null;
}
}
public Route queryByNameFourth(String name ) { //fourth 表 姓名查询；
try {
pstmt=conn.prepareStatement("select * from fourth where name=?");
pstmt.setString(1,name);
ResultSet rs=pstmt.executeQuery();
if(rs.next()) {
Route r=new Route();
r.setId(rs.getInt(1));
r.setName(rs.getString(2));
return r;
}
return null;
}catch(SQLException e) {
e.printStackTrace();
return null;
}
}
public Route queryByNameFifth(String name ) { //fifth 表 姓名查询；
try {
pstmt=conn.prepareStatement("select * from fifth where name=?");
pstmt.setString(1,name);
ResultSet rs=pstmt.executeQuery();
if(rs.next()) {
Route r=new Route();
r.setId(rs.getInt(1));
r.setName(rs.getString(2));
return r;
}
return null;
}catch(SQLException e) {
e.printStackTrace();
return null;
}
}
public Route queryByNameSix(String name ) { //six表 姓名查询；
try {
pstmt=conn.prepareStatement("select * from six where name=?");
pstmt.setString(1,name);
ResultSet rs=pstmt.executeQuery();
if(rs.next()) {
Route r=new Route();
r.setId(rs.getInt(1));
r.setName(rs.getString(2));
return r;
}
return null;
}catch(SQLException e) {
e.printStackTrace();
return null;
}
}
public Route queryByIdFirst(int id) { //first 表id查询；
try {
pstmt=conn.prepareStatement("select * from first where id=?");
pstmt.setInt(1,id);
ResultSet rs=pstmt.executeQuery();
if(rs.next()) {
Route r=new Route();
r.setId(rs.getInt(1));
r.setName(rs.getString(2));
return r;
}
return null;
}catch(SQLException e) {
e.printStackTrace();
return null;
}
}
public List<Route> conditionOne(Route start,Route finish) { //情况一 单线首尾非换乘点
int num;
num=start.getNumber();
switch (num) {
case 1:{
int i; //循环数组 的下标
int a,b,j;
int x; //循环 站点 的 id
Route s=new Route();
Route f=new Route();
s=queryByNameFirst(start.getName());
f=queryByNameFirst(finish.getName());
if(s.getId()>f.getId()) {
a=s.getId();
b=f.getId();
j=0;
}
else {
a=f.getId();
b=s.getId();
j=1;
}
List<Route> all=new ArrayList<Route>();
int m=a-b+1;
if(j==0) {
for(i=0,x=a;i<m;i++,a--) {
all.add(queryByIdFirst(a));
}
}else {
for(i=0,x=b;i<m;i++,b++) {
all.add(queryByIdFirst(b));
}
}
return all;
}
case 2:{}
case 3:{}
case 4:{}
case 5:{}
case 6:{}
}
return null;
}
}
public class Route {
private int id;
private String name;
private int transfer; //判断
private int number;
public int getTransfer() {
return transfer;
}
public void setTransfer(int transfer) {
this.transfer = transfer;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
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 class Conn {
public Connection getCon() {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
String url="jdbc:mysql://localhost/subway?serverTimezone=UTC";
String user="root";
String password="335801836";
Connection conn =DriverManager.getConnection(url,user,password);
System.out.println(conn.getMetaData().getURL()); //验证
return conn;
}catch(Exception e) {
e.printStackTrace();
return null;
}
}
}
<body>
<form method="post" action="namequery.jsp">
<p>起始点<input type="text" name="start" value=""><p>
<p>终 点<input type="text" name="finish" value=""><p>
<P><input type="submit" value="查询"><p>
</form>
</body>
<body>
<%request.setCharacterEncoding("utf-8"); %>
<jsp:useBean id="rs" class="com.service.RouteService"></jsp:useBean>
<%
String start=request.getParameter("start");
String finish=request.getParameter("finish");
Route Rstart=rs.queryByName(start);
Route Rfinish=rs.queryByName(finish);
/* out.print( Rstrat.getTransfer()); */
int judge=rs.judge(Rstart, Rfinish);
%>
<%
int l=0;
switch(judge){
case 1:{
List all=rs.conditionOne(Rstart, Rfinish);
Iterator iter = all.iterator();
while(iter.hasNext()){
Route route=(Route)iter.next();
out.print(route.getName()+" ");
}
}break;
case 2:
case 3:
}
%>
</body>
<body>
<%
int i=0;
switch(i){
%><%
case 0:out.print("ddddddddddd");break;
case 2:out.print("cuo");
}
%>
</body>