Java 17 + MySQL 8.0 机票系统数据库设计：5张核心表与3个关键业务逻辑实现

Java 17 MySQL 8.0 机票系统数据库设计5张核心表与3个关键业务逻辑实现在当今数字化出行时代一个高效可靠的机票预订系统对航空公司和旅客都至关重要。本文将深入探讨基于Java 17和MySQL 8.0的机票系统数据库设计与核心业务实现提供可直接应用于生产环境的解决方案。1. 数据库架构设计与核心表实现1.1 数据库选型与版本考量MySQL 8.0作为当前主流的关系型数据库相比早期版本提供了多项关键改进事务性能提升优化了InnoDB引擎事务处理能力提高2倍JSON支持增强原生JSON数据类型和丰富的JSON函数窗口函数支持OVER子句等高级分析功能原子DDL确保数据字典操作的原子性-- 创建数据库时指定字符集和排序规则 CREATE DATABASE airline_reservation CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;1.2 五张核心表设计1.2.1 用户表(customer)CREATE TABLE customer ( customer_id BIGINT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(50) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL COMMENT 使用BCrypt加密, real_name VARCHAR(100) NOT NULL, gender ENUM(MALE, FEMALE, OTHER), birth_date DATE, phone VARCHAR(20) NOT NULL, email VARCHAR(100), id_card VARCHAR(18) NOT NULL UNIQUE COMMENT 身份证号, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_phone (phone), INDEX idx_id_card (id_card) ) ENGINEInnoDB COMMENT旅客信息表;1.2.2 航班表(flight)CREATE TABLE flight ( flight_id BIGINT PRIMARY KEY AUTO_INCREMENT, flight_number VARCHAR(10) NOT NULL COMMENT 如CA1234, airline_code VARCHAR(2) NOT NULL COMMENT 航空公司代码, aircraft_type VARCHAR(20) NOT NULL, departure_airport VARCHAR(50) NOT NULL, arrival_airport VARCHAR(50) NOT NULL, departure_time DATETIME NOT NULL, arrival_time DATETIME NOT NULL, total_seats INT NOT NULL COMMENT 总座位数, available_seats INT NOT NULL COMMENT 可用座位数, base_price DECIMAL(10,2) NOT NULL, status ENUM(SCHEDULED, DELAYED, CANCELLED, COMPLETED) DEFAULT SCHEDULED, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_departure (departure_airport, departure_time), INDEX idx_arrival (arrival_airport, arrival_time), INDEX idx_flight_number (flight_number), CONSTRAINT chk_time CHECK (arrival_time departure_time) ) ENGINEInnoDB COMMENT航班信息表;1.2.3 订单表(booking_order)CREATE TABLE booking_order ( order_id BIGINT PRIMARY KEY AUTO_INCREMENT, order_number VARCHAR(20) NOT NULL UNIQUE COMMENT 订单编号, customer_id BIGINT NOT NULL, flight_id BIGINT NOT NULL, seat_class ENUM(ECONOMY, BUSINESS, FIRST) NOT NULL, seat_number VARCHAR(10), passenger_name VARCHAR(100) NOT NULL, passenger_id_card VARCHAR(18) NOT NULL, contact_phone VARCHAR(20) NOT NULL, total_amount DECIMAL(10,2) NOT NULL, payment_status ENUM(UNPAID, PAID, REFUNDED, PARTIAL_REFUND) DEFAULT UNPAID, order_status ENUM(PENDING, CONFIRMED, CANCELLED, COMPLETED) DEFAULT PENDING, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (customer_id) REFERENCES customer(customer_id), FOREIGN KEY (flight_id) REFERENCES flight(flight_id), INDEX idx_customer (customer_id), INDEX idx_flight (flight_id), INDEX idx_order_number (order_number) ) ENGINEInnoDB COMMENT订单表;1.2.4 管理员表(admin)CREATE TABLE admin ( admin_id BIGINT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(50) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL, real_name VARCHAR(100) NOT NULL, role ENUM(SUPER_ADMIN, FLIGHT_MANAGER, ORDER_MANAGER) NOT NULL, phone VARCHAR(20) NOT NULL, email VARCHAR(100) NOT NULL, last_login TIMESTAMP NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINEInnoDB COMMENT管理员表;1.2.5 机型表(aircraft)CREATE TABLE aircraft ( aircraft_id BIGINT PRIMARY KEY AUTO_INCREMENT, model VARCHAR(50) NOT NULL UNIQUE COMMENT 机型名称, manufacturer VARCHAR(100) NOT NULL, economy_seats INT NOT NULL, business_seats INT NOT NULL, first_seats INT NOT NULL, total_seats INT GENERATED ALWAYS AS (economy_seats business_seats first_seats) STORED, cruising_speed INT COMMENT 巡航速度(km/h), max_range INT COMMENT 最大航程(km), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINEInnoDB COMMENT机型信息表;1.3 索引优化策略为提高查询性能我们在关键字段上建立了索引用户表phone、id_card航班表departure_airportdeparture_time、arrival_airportarrival_time订单表customer_id、flight_id、order_number提示对于高频查询但更新较少的表可以考虑使用覆盖索引来避免回表操作。2. 核心业务逻辑实现2.1 订单创建流程订单创建是系统的核心功能需要处理并发预订和库存扣减问题。我们采用乐观锁机制确保数据一致性。Service Transactional public class BookingServiceImpl implements BookingService { private final FlightRepository flightRepository; private final OrderRepository orderRepository; private final IdGenerator idGenerator; Override public OrderDTO createOrder(OrderRequest request) { // 1. 验证航班信息 Flight flight flightRepository.findById(request.getFlightId()) .orElseThrow(() - new BusinessException(航班不存在)); // 2. 检查座位可用性 if (flight.getAvailableSeats() 0) { throw new BusinessException(该航班已无余票); } // 3. 创建订单使用乐观锁 int updated flightRepository.reduceSeatWithLock( flight.getFlightId(), flight.getVersion() ); if (updated 0) { throw new ConcurrentBookingException(座位已被其他用户预订请重新选择); } // 4. 生成订单 BookingOrder order new BookingOrder(); order.setOrderNumber(idGenerator.generateOrderNumber()); order.setCustomerId(request.getCustomerId()); order.setFlightId(flight.getFlightId()); order.setSeatClass(request.getSeatClass()); order.setPassengerName(request.getPassengerName()); order.setPassengerIdCard(request.getPassengerIdCard()); order.setContactPhone(request.getContactPhone()); order.setTotalAmount(calculatePrice(flight, request.getSeatClass())); order.setPaymentStatus(PaymentStatus.UNPAID); order.setOrderStatus(OrderStatus.PENDING); orderRepository.save(order); // 5. 返回订单DTO return convertToDTO(order); } private BigDecimal calculatePrice(Flight flight, SeatClass seatClass) { // 根据舱位等级计算价格逻辑 // ... } }2.2 航班查询优化航班查询需要考虑多种筛选条件和分页需求我们使用JPA Specification实现动态查询。Repository public interface FlightRepository extends JpaRepositoryFlight, Long, JpaSpecificationExecutorFlight { Modifying Query(UPDATE Flight f SET f.availableSeats f.availableSeats - 1, f.version f.version 1 WHERE f.flightId :flightId AND f.version :version) int reduceSeatWithLock(Param(flightId) Long flightId, Param(version) Long version); } Service public class FlightQueryServiceImpl implements FlightQueryService { private final FlightRepository flightRepository; Override public PageFlightDTO searchFlights(FlightQuery query, Pageable pageable) { SpecificationFlight spec (root, query, cb) - { ListPredicate predicates new ArrayList(); if (StringUtils.isNotBlank(query.getDepartureAirport())) { predicates.add(cb.equal( root.get(departureAirport), query.getDepartureAirport() )); } if (StringUtils.isNotBlank(query.getArrivalAirport())) { predicates.add(cb.equal( root.get(arrivalAirport), query.getArrivalAirport() )); } if (query.getDepartureDate() ! null) { predicates.add(cb.between( root.get(departureTime), query.getDepartureDate().atStartOfDay(), query.getDepartureDate().plusDays(1).atStartOfDay() )); } if (query.getMinPrice() ! null) { predicates.add(cb.greaterThanOrEqualTo( root.get(basePrice), query.getMinPrice() )); } if (query.getMaxPrice() ! null) { predicates.add(cb.lessThanOrEqualTo( root.get(basePrice), query.getMaxPrice() )); } predicates.add(cb.greaterThan( root.get(availableSeats), 0 )); return cb.and(predicates.toArray(new Predicate[0])); }; return flightRepository.findAll(spec, pageable) .map(this::convertToDTO); } }2.3 库存扣减与并发控制机票系统面临的主要挑战之一是高并发下的库存准确性问题。我们采用多种策略确保数据一致性数据库层面使用乐观锁version字段应用层面分布式锁Redis实现业务层面预留座位机制Service public class InventoryServiceImpl implements InventoryService { private final FlightRepository flightRepository; private final RedisLockHelper redisLockHelper; private final BookingReservationRepository reservationRepository; private static final String FLIGHT_LOCK_PREFIX flight:lock:; private static final long LOCK_EXPIRE_TIME 30; // 秒 Override public boolean reserveSeat(Long flightId, Long customerId, SeatClass seatClass) { String lockKey FLIGHT_LOCK_PREFIX flightId; try { // 获取分布式锁 boolean locked redisLockHelper.tryLock(lockKey, LOCK_EXPIRE_TIME); if (!locked) { throw new ConcurrentBookingException(系统繁忙请稍后再试); } Flight flight flightRepository.findById(flightId) .orElseThrow(() - new BusinessException(航班不存在)); if (flight.getAvailableSeats() 0) { return false; } // 创建预留记录 BookingReservation reservation new BookingReservation(); reservation.setFlightId(flightId); reservation.setCustomerId(customerId); reservation.setSeatClass(seatClass); reservation.setExpireTime(LocalDateTime.now().plusMinutes(15)); reservationRepository.save(reservation); // 扣减库存 flight.setAvailableSeats(flight.getAvailableSeats() - 1); flightRepository.save(flight); return true; } finally { // 释放锁 redisLockHelper.unlock(lockKey); } } Scheduled(fixedRate 60000) // 每分钟检查一次过期预留 public void cleanupExpiredReservations() { ListBookingReservation expired reservationRepository .findByExpireTimeBefore(LocalDateTime.now()); if (!expired.isEmpty()) { ListLong flightIds expired.stream() .map(BookingReservation::getFlightId) .distinct() .collect(Collectors.toList()); // 批量恢复库存 flightRepository.batchIncreaseSeats(flightIds, expired.size()); // 删除过期预留 reservationRepository.deleteAll(expired); } } }3. 高级特性与性能优化3.1 分库分表策略当系统规模扩大时单一数据库可能成为瓶颈。我们设计了以下分片策略水平分片按航班日期分片将不同日期的航班数据分散到不同库垂直分片将用户信息、订单信息和航班信息分离到不同库public class FlightShardingAlgorithm implements PreciseShardingAlgorithmLocalDate { Override public String doSharding(CollectionString availableTargetNames, PreciseShardingValueLocalDate shardingValue) { // 按年份分库每年一个库 int year shardingValue.getValue().getYear(); String dbSuffix db_ (year % 2); // 示例中只分2个库 for (String each : availableTargetNames) { if (each.endsWith(dbSuffix)) { return each; } } throw new IllegalArgumentException(); } }3.2 缓存策略设计合理使用缓存可以显著提高系统响应速度航班信息缓存使用Redis缓存热门航线航班信息查询结果缓存缓存常见查询条件组合的结果本地缓存使用Caffeine缓存少量高频访问数据Configuration EnableCaching public class CacheConfig { Bean public CacheManager cacheManager(RedisConnectionFactory factory) { RedisCacheConfiguration config RedisCacheConfiguration.defaultCacheConfig() .entryTtl(Duration.ofMinutes(30)) .disableCachingNullValues() .serializeKeysWith(RedisSerializationContext.SerializationPair .fromSerializer(new StringRedisSerializer())) .serializeValuesWith(RedisSerializationContext.SerializationPair .fromSerializer(new GenericJackson2JsonRedisSerializer())); return RedisCacheManager.builder(factory) .cacheDefaults(config) .transactionAware() .build(); } Bean public CaffeineObject, Object caffeineConfig() { return Caffeine.newBuilder() .expireAfterWrite(10, TimeUnit.MINUTES) .maximumSize(1000); } Bean public CacheManager localCacheManager() { CaffeineCacheManager cacheManager new CaffeineCacheManager(); cacheManager.setCaffeine(caffeineConfig()); return cacheManager; } } Service CacheConfig(cacheNames flights) public class FlightCacheServiceImpl implements FlightCacheService { private final FlightRepository flightRepository; Cacheable(key #flightId, unless #result null) Override public FlightDTO getFlightById(Long flightId) { return flightRepository.findById(flightId) .map(this::convertToDTO) .orElse(null); } Cacheable(key T(com.example.util.CacheKeyGenerator).generateKey(#query)) Override public ListFlightDTO searchFlights(FlightQuery query) { // 实际查询逻辑 } }3.3 数据库连接池优化正确的连接池配置对系统稳定性至关重要参数推荐值说明maximumPoolSizeCPU核心数 * 2 有效磁盘数最大连接数minimumIdle同maximumPoolSize最小空闲连接idleTimeout600000 (10分钟)空闲连接超时时间maxLifetime1800000 (30分钟)连接最大生命周期connectionTimeout30000 (30秒)获取连接超时时间# application.yml配置示例 spring: datasource: hikari: maximum-pool-size: 20 minimum-idle: 20 idle-timeout: 600000 max-lifetime: 1800000 connection-timeout: 30000 pool-name: BookingHikariCP4. 安全设计与异常处理4.1 数据安全措施敏感数据加密密码使用BCrypt加密身份证号等敏感信息加密存储public class SecurityUtils { private static final int BCRYPT_STRENGTH 12; public static String encryptPassword(String rawPassword) { return BCrypt.hashpw(rawPassword, BCrypt.gensalt(BCRYPT_STRENGTH)); } public static boolean checkPassword(String rawPassword, String encryptedPassword) { return BCrypt.checkpw(rawPassword, encryptedPassword); } public static String encryptIdCard(String idCard) { // 使用AES加密身份证号 // ... } public static String decryptIdCard(String encryptedIdCard) { // AES解密 // ... } }4.2 异常处理框架设计统一的异常处理机制提供友好的错误信息ControllerAdvice public class GlobalExceptionHandler { ExceptionHandler(BusinessException.class) public ResponseEntityErrorResponse handleBusinessException(BusinessException ex) { ErrorResponse response new ErrorResponse( BUSINESS_ERROR, ex.getMessage(), System.currentTimeMillis() ); return ResponseEntity.badRequest().body(response); } ExceptionHandler(ConcurrentBookingException.class) public ResponseEntityErrorResponse handleConcurrentBookingException(ConcurrentBookingException ex) { ErrorResponse response new ErrorResponse( CONCURRENT_BOOKING, ex.getMessage(), System.currentTimeMillis() ); return ResponseEntity.status(HttpStatus.CONFLICT).body(response); } ExceptionHandler(Exception.class) public ResponseEntityErrorResponse handleException(Exception ex) { ErrorResponse response new ErrorResponse( INTERNAL_ERROR, 系统内部错误请稍后再试, System.currentTimeMillis() ); return ResponseEntity.internalServerError().body(response); } } public class ErrorResponse { private String code; private String message; private long timestamp; // 构造方法、getter和setter }4.3 接口安全设计认证与授权使用JWT进行接口认证参数校验使用Hibernate Validator验证输入防重放攻击使用nonce和timestamp机制限流保护使用Guava RateLimiter或Redis实现接口限流RestController RequestMapping(/api/bookings) public class BookingController { private final BookingService bookingService; PostMapping RateLimit(value 10, duration 1, unit TimeUnit.MINUTES) public ResponseEntityOrderDTO createOrder( Valid RequestBody OrderRequest request, RequestHeader(Authorization) String token) { // 验证JWT token if (!JwtUtils.validateToken(token)) { throw new UnauthorizedException(无效的访问令牌); } Long customerId JwtUtils.getCustomerId(token); request.setCustomerId(customerId); OrderDTO order bookingService.createOrder(request); return ResponseEntity.ok(order); } } public interface RateLimit { int value() default 10; int duration() default 1; TimeUnit unit() default TimeUnit.MINUTES; } Aspect Component public class RateLimitAspect { private final CacheString, AtomicInteger counterCache Caffeine.newBuilder() .expireAfterWrite(1, TimeUnit.MINUTES) .build(); Around(annotation(rateLimit)) public Object around(ProceedingJoinPoint joinPoint, RateLimit rateLimit) throws Throwable { String key generateKey(joinPoint); AtomicInteger count counterCache.get(key, k - new AtomicInteger(0)); if (count.incrementAndGet() rateLimit.value()) { throw new RateLimitExceededException(操作过于频繁请稍后再试); } try { return joinPoint.proceed(); } finally { // 计数处理 } } private String generateKey(ProceedingJoinPoint joinPoint) { // 生成基于方法和参数的key } }