【MySQL】火车票售票系统(数据库课程设计)

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/u011748319/article/details/82808061

数据库课程设计——火车票售票系统

很烦数据库课程设计?阅读本篇文章会让你不那么烦躁~

话不多说先放源代码:
https://github.com/shadowings-zy/TrainTicketsSystem

由于数据库部署在了我自己的服务器上,所以如果各位想要修改源代码,或者搭建自己的系统,请自行根据下文的表结构搭建数据库,并且修改Beans.xml中数据库连接的部分。

其中TrainTickets为后台工程文件。
train-tickets-front-end为前端工程文件。

系统开发平台:

开发工具:eclipse,webstorm
开发语言:Java,HTML,CSS,JavaScript
数据库:MySQL
中间件:tomcat 8.0
后台框架:SpringMVC
前端框架:React(前端只是做了一个展示,不是很重要)

系统功能:

在本系统中,前端和后台使用json进行数据交互,下面的每一个功能均对应着一个接口,括号中为接口的URL,具体实现可以参考源代码。

用户层面:

添加用户,即注册。(/register)
根据用户名和密码获取用户的所有信息,即登录。 (/login)
修改用户。 (/updateUser)

订单层面:

添加订单,在本系统中添加订单就意味着锁定座位。(/lockSeat)
修改订单。(/updateOrder)
删除订单。(/deleteOrder)
根据用户ID获取订单信息。(/queryOrder)

火车层面:

根据车站获取列车ID。(/getTrainByStop)
根据列车ID获取列车信息。(/getTrainByTrainId)
列车换乘查询。(/getTransfer)
查询列车座位剩余。(/getRemainSeats)
查询所有通列车的城市。(/getAllCities)

数据库设计(表结构加表数据):

用户表:

在这里插入图片描述

在这里插入图片描述

火车表:

在这里插入图片描述

在这里插入图片描述

火车经停车站表:

在这里插入图片描述

在这里插入图片描述

火车状态表(是否停运):

在这里插入图片描述

在这里插入图片描述

火车座次表:

在这里插入图片描述

在这里插入图片描述

订单表:

在这里插入图片描述

在这里插入图片描述

难点SQL语句设计:

以下SQL语句均基于上述表结构进行的设计,数据库名为12307

//根据起至车站查询火车
select a.train_id, train_name, a.stop_id as start_index, b.stop_id as end_index, a.station_name as start, b.station_name as end, a.arrive_time as start_time, b.arrive_time as end_time, (b.mileage - a.mileage) as mileage 
from `12307`.stop as a,`12307`.stop as b,`12307`.train as c 
where a.city_name = '沈阳' and b.city_name = '济南' and a.stop_id < b.stop_id and a.train_id = b.train_id and a.train_id = c.train_id and a.train_id not in (
	select train_id
	from `12307`.status
	where date = '2019-12-12' and status = '停运')
//根据起至车站查询换乘火车
select a.train_id as first_tid, d.train_id as second_tid, e.train_name as first_tname, f.train_name as second_tname, b.station_name as transfer_station
from `12307`.stop as a, `12307`.stop as b, `12307`.stop as c, `12307`.stop as d, `12307`.train as e, `12307`.train as f
where a.city_name = '广州' and d.city_name = '长春' and a.train_id = b.train_id and b.station_name = c.station_name and c.train_id = d.train_id and b.train_id <> c.train_id and a.stop_id < b.stop_id and c.stop_id < d.stop_id and b.arrive_time < c.arrive_time
and a.train_id not in (select train_id from `12307`.status where date = '2019-10-10' and status = '停运')
and d.train_id not in (select train_id from `12307`.status where date = '2019-10-10' and status = '停运')
and a.train_id = e.train_id and d.train_id = f.train_id
//查询余票数量
select t.train_id, s.seat_type, count(s.seat_type)
from `12307`.train as t, `12307`.seat as s
where t.train_id = 5 and t.train_id = s.train_id and s.seat_id not in (
	select seat_id
	from `12307`.order
	where train_id = 5 and date = '2019-01-02' and ((start_stop_id >= 2 and start_stop_id < 3) or (end_stop_id > 2 and end_stop_id <= 3)))
group by s.seat_type
//查询余票具体座位情况
select t.train_id, s.seat_id, s.carriage, s.seat_type, s.seat_location
from `12307`.train as t, `12307`.seat as s
where t.train_id = 1 and t.train_id = s.train_id and s.seat_id not in (
	select seat_id
	from `12307`.order
	where train_id = 1 and date = '2019-01-01' and ((start_stop_id >= 0 and start_stop_id < 2) or (end_stop_id > 0 and end_stop_id <= 2)))
//锁定座位(实际上就是插入一条订单)
insert into `12307`.order (user_id, passenger_id, user_name, train_id, carriage, seat_type, seat_id, seat_location, start_stop_id, start_station_name, end_stop_id, end_station_name, date, create_at, status) values ('1', '000000000000000001', 'user1', '1', '1', '一等座', '0', '01A', '0', '沈阳北', 2, '深圳北', '2019-01-01', '2018-12-31 12:00:00', '未付款')

重点后台接口及返回样例:

注:所有请求的参数都为string类型,请求类型均为post请求,请求均可以跨域。

1、/login
请求参数:

{
    username, 
    password
}

返回结果:

{content: "{"idcard":"1","password":"123","status":"正常","telephone":"12","userId":"1","userName":"user1"}", discription: "successful", statusCode: "1000"}

2、/register
请求参数:

{
    username, 
    password,
    idcard,
    telephone
}

返回结果:

{content: "{"idcard":"4","password":"123","status":"正常","telephone":"4","userId":"303","userName":"user4"}", discription: "successful", statusCode: "1000"}

3、/updateUser
请求参数:

{
    username, 
    password,
    idcard,
    telephone
}

返回结果:

{content: "{"idcard":"4","password":"123","status":"正常","telephone":"4","userId":"303","userName":"user4"}", discription: "successful", statusCode: "1000"}

4、/queryOrder
请求参数:

{
    idcard
}

返回结果:

{content: "[{"carriage":"1","createAt":"2018-12-31","date":"2…trainName":"G5","userId":"1","userName":"user1"}]", discription: "successful", statusCode: "1000"}

5、/deleteOrder
请求参数:

{
    orderId
}

返回结果:

{content: "修改成功", discription: "successful", statusCode: "1000"}

6、/getTrainByStop
请求参数:

{
    fromCity,
    toCity,
    date
}

返回结果:

{content: "[{"end":"沈阳北","endIndex":"4","endTime":"17:00:00",…"trainId":"5","trainName":"G5","trainType":"高铁"}]", discription: "successful", statusCode: "1000"}

7、/getTransfer
请求参数:

{
    fromCity,
    toCity,
    date
}

返回结果:

{content: "[{"firstTid":"5","firstTname":"G5","secondTid":"3","secondTname":"G3","transferStation":"沈阳北"}]", discription: "successful", statusCode: "1000"}

8、/getRemainSeats
请求参数:

{
    trainId,
    date,
    fromStopId,
    toStopId
}

返回结果:

{content: "[{"carriage":"1","seatId":"0","seatLocation":"01A"…tId":"17","seatLocation":"02F","seatType":"二等座"}]", discription: "successful", statusCode: "1000"}

9、/lockSeat
请求参数:

 {
    userId,
    passengerId,
    userName,
    trainId,
    trainName,
    carriage,
    seatType,
    seatId,
    seatLocation,
    startTime,
    startStopId,
    startStationName,
    endStopId,
    endStationName,
    date,
    createAt,
    status
}

返回结果:

{content: "{"carriage":"2","createAt":"2018-12-31","date":"20…"trainName":"G1","userId":"1","userName":"user1"}", discription: "successful", statusCode: "1000"}

项目结构:

在这里插入图片描述

controller中的三个类分别对应着:
user相关的接口,order相关的接口和train相关的接口。

model.order,model.train,model.user分别对应着:
订单类实现及相关数据查询方法,火车类实现及相关数据查询方法和用户类实现及相关数据查询方法。

test中的SQLTest类是测试所有sql语句的方法。

tools中的两个类包含了对HTTP请求进行处理的方法。

Beans.xml包含了spring框架和jdbc的基本配置。

WebContent中的test.html是用来调试接口的界面。

部署须知:

后台的部署比较简单,将下载的工程打包成war文件后,直接丢到tomcat里,并向localhost:8080/TrainTickets/xxx (xxx为对应的接口)发起post请求就可以了。

前端的部署需要安装node.js
之后进入train-tickets-front-end文件夹,输入npm run start
访问localhost:3000即可。

项目截图

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

展开阅读全文

没有更多推荐了,返回首页