当前位置： 首页 > news >正文

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

news 2026/7/8 19:54:57

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) ) ENGINE=InnoDB 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) ) ENGINE=InnoDB 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) ) ENGINE=InnoDB 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 ) ENGINE=InnoDB 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 ) ENGINE=InnoDB COMMENT='机型信息表';

1.3 索引优化策略

为提高查询性能，我们在关键字段上建立了索引：

  1. 用户表：phone、id_card
  2. 航班表：departure_airport+departure_time、arrival_airport+arrival_time
  3. 订单表：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 JpaRepository<Flight, Long>, JpaSpecificationExecutor<Flight> { @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 Page<FlightDTO> searchFlights(FlightQuery query, Pageable pageable) { Specification<Flight> spec = (root, query, cb) -> { List<Predicate> 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 库存扣减与并发控制

机票系统面临的主要挑战之一是高并发下的库存准确性问题。我们采用多种策略确保数据一致性：

  1. 数据库层面：使用乐观锁（version字段）
  2. 应用层面：分布式锁（Redis实现）
  3. 业务层面：预留座位机制
@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() { List<BookingReservation> expired = reservationRepository .findByExpireTimeBefore(LocalDateTime.now()); if (!expired.isEmpty()) { List<Long> flightIds = expired.stream() .map(BookingReservation::getFlightId) .distinct() .collect(Collectors.toList()); // 批量恢复库存 flightRepository.batchIncreaseSeats(flightIds, expired.size()); // 删除过期预留 reservationRepository.deleteAll(expired); } } }

3. 高级特性与性能优化

3.1 分库分表策略

当系统规模扩大时，单一数据库可能成为瓶颈。我们设计了以下分片策略：

  1. 水平分片：按航班日期分片，将不同日期的航班数据分散到不同库
  2. 垂直分片：将用户信息、订单信息和航班信息分离到不同库
public class FlightShardingAlgorithm implements PreciseShardingAlgorithm<LocalDate> { @Override public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<LocalDate> 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 缓存策略设计

合理使用缓存可以显著提高系统响应速度：

  1. 航班信息缓存：使用Redis缓存热门航线航班信息
  2. 查询结果缓存：缓存常见查询条件组合的结果
  3. 本地缓存：使用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 Caffeine<Object, 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 List<FlightDTO> 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: BookingHikariCP

4. 安全设计与异常处理

4.1 数据安全措施

  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 ResponseEntity<ErrorResponse> handleBusinessException(BusinessException ex) { ErrorResponse response = new ErrorResponse( "BUSINESS_ERROR", ex.getMessage(), System.currentTimeMillis() ); return ResponseEntity.badRequest().body(response); } @ExceptionHandler(ConcurrentBookingException.class) public ResponseEntity<ErrorResponse> handleConcurrentBookingException(ConcurrentBookingException ex) { ErrorResponse response = new ErrorResponse( "CONCURRENT_BOOKING", ex.getMessage(), System.currentTimeMillis() ); return ResponseEntity.status(HttpStatus.CONFLICT).body(response); } @ExceptionHandler(Exception.class) public ResponseEntity<ErrorResponse> 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 接口安全设计

  1. 认证与授权：使用JWT进行接口认证
  2. 参数校验：使用Hibernate Validator验证输入
  3. 防重放攻击：使用nonce和timestamp机制
  4. 限流保护：使用Guava RateLimiter或Redis实现接口限流
@RestController @RequestMapping("/api/bookings") public class BookingController { private final BookingService bookingService; @PostMapping @RateLimit(value = 10, duration = 1, unit = TimeUnit.MINUTES) public ResponseEntity<OrderDTO> 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 Cache<String, 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 } }
http://www.jsqmd.com/news/1149744/

相关文章：

  • Spring Boot 3.x 电商系统实战：Vue 3 + MySQL 8 构建农产品销售平台
  • Codex CLI本地智能编程助手：离线代码生成与多平台安装指南
  • Pearcleaner：macOS终极清理工具，免费解决应用残留难题
  • STM32温度控制系统：如何实现±0.5°C的高精度PID控制？
  • Cursor自定义模型接入：协议对齐与生产级稳定性实践
  • Minecraft视觉革命：Photon光影包如何重塑方块世界的光影美学
  • Textbus 5.0：面向专业场景的富文本底层框架
  • GitHub中文化插件：三分钟打造专属中文开发环境
  • 为什么Simple Clock是追求纯净体验的Android用户不可错过的选择？
  • 工业负载控制：TPD2017FN与PIC32MZ的智能驱动方案
  • Boss-Key：你的Windows隐私守护神，一键隐藏所有窗口的终极解决方案
  • NAU8224与PIC18LF27K40构建高效D类音频系统
  • System.Net.Http 4.2.0.0 程序集加载失败：3步定位 .NET Framework 4.8 版本冲突根源
  • 从手动录屏到智能归档：douyin-downloader如何重塑你的内容管理方式
  • 如何快速搭建网易云音乐永久直链解析API：面向开发者的完整指南
  • Codex API 实战指南：从命令行到 VS Code 的 Vibe Coding 工作流
  • WinForm 自定义属性 5 个核心特性详解：Category、Description、DefaultValue 实战
  • OpenClaw：Windows原生零代码智能体工作流编排工具
  • ArcGIS 10.8 合法安装与环境配置全指南
  • mac效率提升：批量设置文件默认打开方式
  • OpenSSH for Windows 8.1.0.0 部署对比：GUI安装 vs PowerShell命令 vs 手动配置
  • TongWeb 7.0 Spring Boot 应用外置容器部署：3步完成JAR到WAR的迁移与配置
  • CSAPP Shell Lab 信号竞争解决：3步阻塞SIGCHLD，避免addjob/deletejob冲突
  • 九江排队火锅实测测评｜理性避坑，就餐选型指南
  • 装机必备！这款BIOS检测工具能看主板信息，启动快捷键查询神器
  • APK安装器：在Windows上直接运行安卓应用的终极指南
  • ViT vs ResNet-50 图像分类对比：ImageNet-1k 上5%精度差异的归因分析
  • 2026最新7款基础版免费AI编程工具实测深度对比
  • Tomcat 8.5 Context 配置 3 种方式详解：server.xml vs context.xml vs META-INF
  • 腾讯云混元API KEY申请与安全配置全指南