大家好，又见面了，我是你们的朋友全栈君。

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

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

体验链接：
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请求就可以了。

项目截图