数据库课程设计——MySQL火车票售票系统[通俗易懂]
数据库课程设计——MySQL火车票售票系统[通俗易懂]
大家好，又见面了，我是你们的朋友全栈君。
数据库课程设计——火车票售票系统
很烦数据库课程设计？阅读本篇文章会让你不那么烦躁~
体验链接： http://www.shadowingszy.top/train-tickets/index.html
话不多说先放源代码： https://github.com/shadowings-zy/TrainTicketsSystem
由于数据库部署在了我自己的服务器上，所以如果各位想要修改源代码，或者搭建自己的系统，请自行根据下文的表结构搭建数据库，并且修改Beans.xml中数据库连接的部分。
其中train-tickets-back-end为后台工程文件。 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是用来调试接口的界面。
部署须知：
前端页面的开发需要安装node.js 之后进入train-tickets-front-end文件夹，输入npm install, 然后输入npm run start 访问localhost:3000即可进行开发。
前端页面部署需要先输入npm run build打包前端页面。 然后将打包出的build文件夹中的全部文件放到train-tickets-back-end/WebContent文件夹中。
最后进行后台的部署，将train-tickets-back-end项目打包成war文件后，直接丢到tomcat里。 向localhost:8080/TrainTickets/xxx (xxx为对应的接口)发起post请求就可以了。
项目截图
发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/144480.html原文链接：https://javaforall.cn