厦门公交查询

数据库课的实习报告，发来纪念一下这个快要结束的学期，这些日子很累，也很难受，但总算熬过来了。

目录
1.实习题目 2
2.实习环境 2
3.实习过程 2
3.1.数据库设计 2
3.2.采集数据 2
3.2.1.分析数据 2
3.2.2.处理数据 2
3.2.2.1.生成站点表(stop) 2
3.2.2.2.生成线路表(line) 2
3.2.2.3.生成站点-线路表(bus) 2
3.2.2.导入数据 2
3.3.查询方案 2
3.3.1.线路查询 2
3.3.2.过站查询 2
3.3.3.直达乘车查询 2
3.3.4.一次换乘查询 2
3.3.5.两次换乘 2
3.3.查询优化 2
3.4.系统设计 2
3.4.1.系统架构 2
3.4.2.核心代码 2
3.5.系统使用 2
3.5.1.过站查询 2
3.5.2.线路查询 2
3.5.3.乘车查询 2


数据库实习报告
鲁超 10717218
（其他组员：A0717081 杜海星，A0717048 毕超，A0717096 高伟智，10717084 陈文静）
1.实习题目：
公交路线查询网站：提供如下功能：
1. 给定线路的沿途到站情况。
2. 经过某个给定站点的所有线路。
3. 给定起止站点，给出可达的换乘线路，包括换乘次数最少，总乘坐站数最少的选择。
2.实习环境：
硬件环境：Intel Centrino Duo T5450，1024M内存，160G硬盘
软件环境：Windows xp sp2，Mysql 5.0.27-community-nt
3.实习过程：
3.1．数据库设计：
设计三个关系：
线路（线路ID，线路名字）
站（站ID，站名）
线路_站（线路ID，站ID，序号）
ER图如下：
（附件）

SQL语句：
建立stop表：
DROP TABLE IF EXISTS `stop`;
CREATE TABLE `stop` (
  `stopid` int(11) NOT NULL,
  `stopname` varchar(20) default NULL,
  PRIMARY KEY  (`stopid`),
  KEY `stopname` (`stopname`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

建立line表：
DROP TABLE IF EXISTS `line`;
CREATE TABLE `line` (
  `lineid` int(11) NOT NULL,
  `linename` varchar(20) default NULL,
  PRIMARY KEY  (`lineid`),
  KEY `linename` (`linename`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

建立bus表：

DROP TABLE IF EXISTS `bus`;
CREATE TABLE `bus` (
  `stopid` int(11) default NULL,
  `lineid` int(11) default NULL,
  `seq` int(11) default NULL,
  KEY `bus_stopid` (`stopid`),
  KEY `lineid_bus` (`lineid`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

3.2.采集数据：
3.2.1.分析数据：
因为没有可以使用的数据库，所以需要先采集数据，在互联网上可以下载到厦门的公交数据，为xml格式，以下是文件的一个片段：
<?xml version="1.0" encoding="utf-8" ?>
- <gj-root>
- <gj-info roadid="1">
  <road-info>厦大,博物馆,大生里,镇海路,中山路,眼科医院,斗西,二市,酿酒厂,文灶,金榜公园,火车站</road-info>
</gj-info>

分析该格式可知：xml版本为1.0，编码为utf-8，根节点为gj-root，每一路公交车的信息存于gj-info中，属性roadid为线路的名字，到站信息以逗号分隔的文本的形式存在road-info中。
3.2.2.处理数据：
采用Java的jdom类库来解析xml文件：
3.2.2.1.生成站点表(stop)：
代码如下：

package busdataprocess; 

import java.io.File; 
import java.io.FileOutputStream; 
import java.io.OutputStreamWriter; 
import java.io.PrintStream; 
import java.io.PrintWriter; 
import java.util.ArrayList; 
import java.util.Iterator; 
import java.util.List; 
import org.jdom.*; 
import org.jdom.input.SAXBuilder; 
import org.jr.util.*; 

public class stops { 

    public static final String LINE_SQL_FILE = "f:/sql/line.sql"; 
    public static final String STOP_SQL_FILE = "f:/sql/stop.sql"; 
    public static final String BUS_SQL_FILE = "f:/sql/bus.sql"; 

    public static void main(String[] args) { 
        PrintStream out = System.out; 
        ArrayList stop = new ArrayList(); 
        //HashSet stop = new HashSet(); 
        SAXBuilder builder = new SAXBuilder(); 
        out.println("OK"); 
        try { 
            Document doc = builder.build(new File("f:/bus.xml")); 
            Element root = doc.getRootElement(); 
            Element curr = null; 
            String[] stops = null; 
            PrintWriter pw = new PrintWriter(new OutputStreamWriter(new FileOutputStream(STOP_SQL_FILE)), true); 
            String sql = null; 
            int lineid = 0; 
            List busLine = root.getChildren("gj-info"); 
            Iterator iterator = busLine.iterator(); 
            while (iterator.hasNext()) { 
                curr = (Element) iterator.next(); 
                stops = StringUtil.split(curr.getChildText("road-info")); 
                for (int i = 0; i < stops.length; i++) { 
                   if(!stop.contains(stops[i])){ 
                        stop.add(stops[i]); 
                        out.println("adding "+stops[i]);                                 
                   } 
                     
                } 
            } 
            Iterator it = stop.iterator(); 
            String s = ""; 
            for(int i=0; i<stop.size();i++){ 
                  s = (String) it.next();//s is stop name 
                sql = "insert into stop values(" + lineid + ",\'" + s + "\');"; 
                pw.println(sql); 
                lineid++; 
            } 
       
            pw.flush(); 
            pw.close(); 
        } catch (Exception e) { 
            e.printStackTrace(); 
        } 
    } 
} 
 

该程序会生成一个stop.sql，其中包含在数据库中添加数据的sql语句。
3.2.2.2.生成线路表(line):
代码如下：

package busdataprocess; 

import java.io.File; 
import java.io.FileOutputStream; 
import java.io.OutputStreamWriter; 
import java.io.PrintStream; 
import java.io.PrintWriter; 
import java.util.Iterator; 
import java.util.List; 
import org.jdom.*; 
import org.jdom.input.SAXBuilder; 
import org.jr.util.*; 

public class line { 

    public static final String LINE_SQL_FILE = "f:/sql/line.sql"; 
    public static final String STOP_SQL_FILE = "f:/sql/stop.sql"; 
    public static final String BUS_SQL_FILE = "f:/sql/bus.sql"; 

    public static void main(String[] args) { 
        PrintStream out = System.out; 

        SAXBuilder builder = new SAXBuilder(); 
        try { 
            Document doc = builder.build(new File("f:/bus.xml")); 
            Element root = doc.getRootElement(); 
            Element curr = null; 
            String[] stops = null; 
            PrintWriter pw = new PrintWriter(new OutputStreamWriter(new FileOutputStream(LINE_SQL_FILE)), true); 
            int lineid = 0; 
            String linename = null; 
            List busLine = root.getChildren("gj-info"); 
            Iterator iterator = busLine.iterator(); 
            while (iterator.hasNext()) { 
                curr = (Element) iterator.next(); 
                linename = curr.getAttribute("roadid").getValue(); 
                pw.println("insert into line values(" + lineid + ",\'" + linename + "\');"); 
                lineid++; 
            } 
            pw.flush(); 
            pw.close(); 
        } catch (Exception e) { 
            e.printStackTrace(); 
        } 
    } 
} 

该程序会生成一个line.sql，其中包含在数据库中添加数据的sql语句。
3.2.2.3.生成站点-线路表(bus):
代码如下：

package busdataprocess; 

import java.io.File; 
import java.io.FileOutputStream; 
import java.io.OutputStreamWriter; 
import java.io.PrintStream; 
import java.io.PrintWriter; 
import java.util.HashMap; 
import java.util.Iterator; 
import java.util.List; 
import org.jdom.*; 
import org.jdom.input.SAXBuilder; 
import org.jr.util.*; 

public class bus { 

    public static final String LINE_SQL_FILE = "f:/sql/line.sql"; 
    public static final String STOP_SQL_FILE = "f:/sql/stop.sql"; 
    public static final String BUS_SQL_FILE = "f:/sql/bus.sql"; 

    public static void main(String[] args) { 
        PrintStream out = System.out; 
        HashMap stop = new HashMap(); 
        HashMap line = new HashMap(); 
        SAXBuilder builder = new SAXBuilder(); 
        try { 
            Document doc = builder.build(new File("f:/bus.xml")); 
            Element root = doc.getRootElement(); 
            Element curr = null; 
            String[] stops = null; 
            PrintWriter pw = new PrintWriter(new OutputStreamWriter(new FileOutputStream(BUS_SQL_FILE)), true); 
            String sql = null; 
            int lineid = 0; 
            int stopno = 0; 
            String linename = null; 
            List busLine = root.getChildren("gj-info"); 
            Iterator iterator = busLine.iterator(); 
            while (iterator.hasNext()) { 
                curr = (Element) iterator.next(); 
                stops = StringUtil.split(curr.getChildText("road-info")); 
                for (int i = 0; i < stops.length; i++) { 
                    if (!stop.containsKey(stops[i])) { 
                        stop.put(stops[i], stopno); 
                        out.println("add " + stops[i] + ", " + stopno); 
                        stopno++; 
                    } 
                } 
            } 
            int lineno = 0; 
            Iterator ite = busLine.iterator(); 
            while (ite.hasNext()) { 
                curr = (Element) ite.next(); 
                linename = curr.getAttribute("roadid").getValue(); 
                line.put(linename, lineno); 
                lineno++; 
            } 
            out.println(line.toString()); 
            out.println(stop.toString()); 
            lineid = 0; 
            iterator = busLine.iterator(); 
            while (iterator.hasNext()) { 
                curr = (Element) iterator.next(); 

                linename = curr.getAttribute("roadid").getValue(); 
                stops = StringUtil.split(curr.getChildText("road-info")); 
                for (int i = 0; i < stops.length; i++) { 
                    //sql = "insert into bus values(" +stops[i] + "," + lineid + "," + i + ")"; // stop.get(stops[i]) 
                    out.println(stops[i] + "     " + linename); 
                    sql = "insert into bus values(" + stop.get(stops[i]) + "," + line.get(linename) + "," + i + ");"; 
                    pw.println(sql); 
                } 
                lineid++; 
            } 
            pw.flush(); 
            pw.close(); 
        } catch (Exception e) { 
            e.printStackTrace(); 
        } 
    } 
} 

该程序会生成一个bus.sql，其中包含在数据库中添加数据的sql语句。

3.2.2.导入数据：
在DOS命令行使用以下命令导入数据(sql文件存在f:/sql)：
mysql –u root –p test<f:\sql\stop.sql
mysql –u root –p test<f:\sql\line.sql
mysql –u root –p test<f:\sql\bus.sql

3.3.查询方案：
3.3.1.线路查询：
线路查询指根据线路名查询该线路通过的所有站：
例如查询线路1通过的所有站，可以通过以下命令查询：
Select * from bus where lineid=1
执行结果如下图所示：


3.3.2.过站查询：
过站查询指通过特定站点的所有线路名称，可以通过以下的命令查询：
Select * from bus where stopid=0;
结果如下图所示：


3.3.3.直达乘车查询：
直达乘车查询指查询两个站点之间的公交车次，可以通过以下语句：
select B.lineid from (select lineid from bus where stopid = 0) A,(select lineid from bus where stopid = 1) B where A.lineid = B.lineid;

结果如下：


3.3.4.一次换乘查询：
当两个站点之间没有直接到达的公交车时，需要寻找第三个站点进行一次换乘，寻找第三个中间站点可以通过以下的sql语句：
select A.stopid from
(
select distinct stopid from bus where lineid in
(select lineid from bus where stopid = 0)
)A,
(
select distinct stopid from bus where lineid in
(select lineid from bus where stopid = 922)
)B
where A.stopid= B.stopid;

结果如下：


然后可以通过直达车次查询查到起始车站到中间车站以及中间车站到目标车站的车次，从而完成一套乘车方案。
3.3.5.两次换乘：
当一次换乘中的中间站点找不到时，就需要进行更多次数的换乘，发生二次换乘时，需要找到两个中间节点，本系统采用的方案是先找到两个中间节点之间的乘车方案（直达），可以使用以下的sql语句：
select A.lineid from
(select distinct lineid from bus where stopid in (select distinct stopid from bus where lineid in(select lineid from bus where stopid = 0))) A,
(select distinct lineid from bus where stopid in (select distinct stopid from bus where lineid in
(select lineid from bus where stopid = 67))) B
where A.lineid = B.lineid;


然后通过查找从起始车站到以上中间线路中的任意车站的直达车，然后换乘中间线路，然后查找从中间线路上的任意车站到目标线路上的直达车，即可完成一套乘车方案。

3.3.查询优化：
因为当换乘次数增多的时候，此时数据的查询速度增长，所以有必要采用优化措施：
采用建立索引的方式优化：
Create index bus_stopid on bus (stopid)
Create index bus_lineid on bus (lineid)
Create index stop_stopname on stop (stopname)

通过以上的索引，将原来的直达查询由10s缩短到0.2s左右，二次换乘的查询也由原来的两分钟缩短为1s，属于可以接受的范围。
3.4.系统设计：
3.4.1.系统架构：
系统采用基于java的B/S结构，采用mysql作为数据库，glassfish为应用服务器，使用jdbc方式连接数据库，方便对于sql语句调优。

将大多数业务逻辑，比如过站查询，线路查询封装在javabean中，通过jsp直接调用javabean来实现业务，从而降低了jsp和业务逻辑的耦合，提高了可靠性，可重用性。
3.4.2.核心代码：

package org.tiantian.busquery; 

import java.sql.Connection; 
import java.sql.ResultSet; 
import java.sql.SQLException; 
import java.sql.Statement; 
import java.util.ArrayList; 
import java.util.List; 
import java.util.logging.Level; 
import java.util.logging.Logger; 

public class BusQuery { 

    private Connection conn = null; 
    private ArrayList StopsList = new ArrayList(); 
    private ArrayList IdList = new ArrayList(); 
    private ArrayList DirectResultList = new ArrayList(); 
    private Statement sm = null; 
    private ResultSet rs = null; 
    private ArrayList midStopList = new ArrayList(); 

    public BusQuery() { 
        try { 
            conn = Db.getConnection(); 
            sm = conn.createStatement(); 
        } catch (SQLException ex) { 
            Logger.getLogger(BusQuery.class.getName()).log(Level.SEVERE, null, ex); 
        } 

    } 

    public ArrayList QueryByStop(String StopName) { 
        try { 
            String linename = ""; 
            rs = sm.executeQuery("select * from bus,line,stop where stop.stopname=" + "\'" + StopName + "\'" + " and line.lineid=bus.lineid and bus.stopid=stop.stopid;"); 
            // System.out.println("select * from bus,line,stop where stop.stopname=" + "\'" + StopName + "\'" + " and line.lineid=bus.lineid and bus.stopid=stop.stopid;"); 
            while (rs.next()) { 
                linename = rs.getString(5); 
                //System.out.println(linename); 
                IdList.add(linename); 
            } 
        } catch (SQLException ex) { 
            Logger.getLogger(BusQuery.class.getName()).log(Level.SEVERE, null, ex); 
        } 
        return IdList; 
    } 

    public ArrayList QueryById(String Id) { 
        try { 
            String stopname = ""; 
            rs = sm.executeQuery("select * from bus,line,stop where line.linename=" + Id + " and bus.lineid=line.lineid and bus.stopid=stop.stopid order by seq;"); 
            while (rs.next()) { 
                stopname = rs.getString(7); 
                StopsList.add(stopname); 
            } 

        } catch (SQLException ex) { 
            Logger.getLogger(BusQuery.class.getName()).log(Level.SEVERE, null, ex); 
        } 
        return StopsList; 
    } 

    public ArrayList findMidStop(int stop_from, int stop_to) { 
        try { 
            String sql = "select A.stopid from (select distinct stopid from bus where lineid in (select lineid from bus where stopid = " + stop_from + ") ) A, (select distinct stopid from bus where lineid in (select lineid from bus where stopid = " + stop_to + ")) B where A.stopid= B.stopid;"; 
            rs = sm.executeQuery(sql); 
            while (rs.next()) { 
                midStopList.add(rs.getInt(1)); 
            } 


        } catch (SQLException ex) { 
            Logger.getLogger(BusQuery.class.getName()).log(Level.SEVERE, null, ex); 
        } 
        return midStopList; 
    } 

    public ArrayList findMidLine(int stop_from, int stop_to) { 
        ArrayList midLineList = new ArrayList(); 
        try { 

            String sql = "select A.lineid from (select distinct lineid from bus where stopid in (select distinct stopid from bus where lineid in (select lineid from bus where stopid = " + stop_from + "))) A, (select distinct lineid from bus where stopid in (select distinct stopid from bus where lineid in (select lineid from bus where stopid =" + stop_to + "))) B where A.lineid = B.lineid;"; 

            rs = sm.executeQuery(sql); 
            while (rs.next()) { 
                midLineList.add(rs.getInt(1)); 
            } 


        } catch (SQLException ex) { 
            Logger.getLogger(BusQuery.class.getName()).log(Level.SEVERE, null, ex); 
        } 
        return midLineList; 
    } 

    public int getLineId(String linename) { 
        int lineId = -1; 
        try { 
            String sql = "select * from line where line.linename=\'" + linename + "\';"; 
            rs = sm.executeQuery(sql); 
            while (rs.next()) { 
                lineId = rs.getInt(1); 
            } 

        } catch (SQLException ex) { 
            Logger.getLogger(BusQuery.class.getName()).log(Level.SEVERE, null, ex); 
        } 
        return lineId; 
    } 

    public String getLineName(int lineid) { 
        String lineName = ""; 
        try { 
            String sql = "select * from line where line.lineid=\'" + lineid + "\';"; 
            rs = sm.executeQuery(sql); 
            while (rs.next()) { 
                lineName = rs.getString(2); 
            //  System.out.println(lineName); 
            } 

        } catch (SQLException ex) { 
            Logger.getLogger(BusQuery.class.getName()).log(Level.SEVERE, null, ex); 
        } 
        return lineName; 
    } 

    public int getStopId(String stopname) { 
        int stopId = -1; 
        try { 
            String sql = "select * from stop where stop.stopname=\'" + stopname + "\'"; 
            System.out.println(sql); 
            rs = sm.executeQuery(sql); 
            while (rs.next()) { 
                stopId = rs.getInt(1); 
            } 
        } catch (SQLException ex) { 
            Logger.getLogger(BusQuery.class.getName()).log(Level.SEVERE, null, ex); 
        } 
        return stopId; 
    } 

    public String getStopName(int stopid) { 
        String stopName = ""; 
        try { 
            String sql = "select * from stop where stop.stopid=\'" + stopid + "\'"; 
            System.out.println(sql); 
            rs = sm.executeQuery(sql); 
            while (rs.next()) { 
                stopName = rs.getString(2); 
            } 
        } catch (SQLException ex) { 
            Logger.getLogger(BusQuery.class.getName()).log(Level.SEVERE, null, ex); 
        } 
        return stopName; 
    } 

    public ArrayList findDirect(String from, String to) { 
        int from_id = this.getStopId(from); 
        int to_id = this.getStopId(to); 
        ArrayList al = this.findDirect(from_id, to_id); 
        ArrayList nameList = new ArrayList(); 

        for (int i = 0; i < al.size(); i++) { 

            nameList.add((String) getLineName(((Integer) al.get(i)))); 
            System.out.println(getLineName(((Integer) al.get(i)))); 


        } 
        return nameList; 
    } 

    public ArrayList findDirect(int stop_from, int stop_to) { 
        try { 
            int lineid; 
            DirectResultList.clear(); 
            rs = sm.executeQuery("select distinct B.lineid from (select distinct lineid from bus where stopid =" + stop_from + " ) A,(select distinct lineid from bus where stopid = " + stop_to + ") B where A.lineid=B.lineid "); 
            while (rs.next()) { 
                lineid = rs.getInt(1); 
                DirectResultList.add(lineid); 
            } 
        } catch (SQLException ex) { 
            Logger.getLogger(BusQuery.class.getName()).log(Level.SEVERE, null, ex); 
        } 
        return DirectResultList; 
    } 

    public int getDistance(int from, int to, int lineid) { 
        int start = 0; 
        int end = 0; 
        try { 
            rs = sm.executeQuery("select * from bus where stopid=" + from + " and lineid=" + lineid); 
            while (rs.next()) { 
                start = rs.getInt("seq"); 
            } 
            rs = sm.executeQuery("select * from bus where stopid=" + to + " and lineid=" + lineid); 
            while (rs.next()) { 
                end = rs.getInt("seq"); 
            } 
        } catch (SQLException ex) { 
            Logger.getLogger(BusQuery.class.getName()).log(Level.SEVERE, null, ex); 
        } 
        return (start - end) > 0 ? (start - end) : (end - start); 
    } 

    public static void main(String[] args) { 
        BusQuery instance = new BusQuery(); 
        List stops = instance.QueryById("0"); 
        System.out.println(instance.getDistance(4, 111, 9)); 
    } 
} 

评论

1 楼 shaomin 2008-10-08  

不错的讲
谢谢分享
参看下