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 数据库连接池优化

正确的连接池配置对系统稳定性至关重要：

参数	推荐值	说明
maximumPoolSize	CPU核心数 * 2 + 有效磁盘数	最大连接数
minimumIdle	同maximumPoolSize	最小空闲连接
idleTimeout	600000 (10分钟)	空闲连接超时时间
maxLifetime	1800000 (30分钟)	连接最大生命周期
connectionTimeout	30000 (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
    }
}