基于数据库实现案例

本文目录导读：

1. 案例一：图书管理系统
2. 案例二：电商订单系统
3. 案例三：员工考勤系统
4. 案例四：酒店管理系统

我来提供几个基于数据库实现的完整案例,涵盖不同场景和技术栈。

图书管理系统

数据库设计

-- 创建数据库
CREATE DATABASE library_system;
USE library_system;
-- 图书表
CREATE TABLE books (
    book_id INT PRIMARY KEY AUTO_INCREMENT,
    isbn VARCHAR(20) UNIQUE NOT NULL,VARCHAR(200) NOT NULL,
    author VARCHAR(100) NOT NULL,
    publisher VARCHAR(100),
    publish_date DATE,
    category VARCHAR(50),
    total_count INT DEFAULT 1,
    available_count INT DEFAULT 1,
    location VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 读者表
CREATE TABLE readers (
    reader_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    id_card VARCHAR(18) UNIQUE NOT NULL,
    phone VARCHAR(20),
    email VARCHAR(100),
    address VARCHAR(200),
    level VARCHAR(20) DEFAULT '普通读者',
    max_borrow INT DEFAULT 5,
    status VARCHAR(20) DEFAULT '正常',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 借阅记录表
CREATE TABLE borrow_records (
    record_id INT PRIMARY KEY AUTO_INCREMENT,
    book_id INT NOT NULL,
    reader_id INT NOT NULL,
    borrow_date DATE NOT NULL,
    due_date DATE NOT NULL,
    return_date DATE,
    status VARCHAR(20) DEFAULT '借出',
    renew_count INT DEFAULT 0,
    FOREIGN KEY (book_id) REFERENCES books(book_id),
    FOREIGN KEY (reader_id) REFERENCES readers(reader_id)
);
-- 罚款记录表
CREATE TABLE fines (
    fine_id INT PRIMARY KEY AUTO_INCREMENT,
    record_id INT NOT NULL,
    reader_id INT NOT NULL,
    amount DECIMAL(10,2),
    reason VARCHAR(200),
    status VARCHAR(20) DEFAULT '未缴纳',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (record_id) REFERENCES borrow_records(record_id),
    FOREIGN KEY (reader_id) REFERENCES readers(reader_id)
);

核心功能实现（Python + MySQL）

import mysql.connector
from datetime import datetime, timedelta
class LibrarySystem:
    def __init__(self, host='localhost', user='root', password='', database='library_system'):
        self.conn = mysql.connector.connect(
            host=host,
            user=user,
            password=password,
            database=database
        )
        self.cursor = self.conn.cursor(dictionary=True)
    # 借书功能
    def borrow_book(self, reader_id, book_id):
        try:
            # 开启事务
            self.conn.start_transaction()
            # 检查读者状态和借阅数量
            self.cursor.execute("""
                SELECT status, max_borrow FROM readers WHERE reader_id = %s
            """, (reader_id,))
            reader = self.cursor.fetchone()
            if not reader or reader['status'] != '正常':
                raise Exception("读者状态异常")
            # 检查图书是否可借
            self.cursor.execute("""
                SELECT available_count FROM books 
                WHERE book_id = %s AND available_count > 0
            """, (book_id,))
            book = self.cursor.fetchone()
            if not book:
                raise Exception("图书不可借")
            # 检查当前借阅数量
            self.cursor.execute("""
                SELECT COUNT(*) as count FROM borrow_records 
                WHERE reader_id = %s AND status = '借出'
            """, (reader_id,))
            current_count = self.cursor.fetchone()['count']
            if current_count >= reader['max_borrow']:
                raise Exception("已达到最大借阅数量")
            # 创建借阅记录
            borrow_date = datetime.now().date()
            due_date = borrow_date + timedelta(days=30)
            self.cursor.execute("""
                INSERT INTO borrow_records (book_id, reader_id, borrow_date, due_date)
                VALUES (%s, %s, %s, %s)
            """, (book_id, reader_id, borrow_date, due_date))
            # 更新图书库存
            self.cursor.execute("""
                UPDATE books SET available_count = available_count - 1 
                WHERE book_id = %s
            """, (book_id,))
            self.conn.commit()
            return {"success": True, "message": "借书成功"}
        except Exception as e:
            self.conn.rollback()
            return {"success": False, "message": str(e)}
    # 还书功能
    def return_book(self, record_id):
        try:
            self.conn.start_transaction()
            # 更新借阅记录
            return_date = datetime.now().date()
            self.cursor.execute("""
                UPDATE borrow_records 
                SET return_date = %s, status = '已还'
                WHERE record_id = %s AND status = '借出'
            """, (return_date, record_id))
            if self.cursor.rowcount == 0:
                raise Exception("借阅记录不存在或已归还")
            # 获取图书ID
            self.cursor.execute("""
                SELECT book_id, due_date FROM borrow_records WHERE record_id = %s
            """, (record_id,))
            record = self.cursor.fetchone()
            # 检查是否逾期
            if return_date > record['due_date']:
                days = (return_date - record['due_date']).days
                fine_amount = days * 0.5  # 每天0.5元罚款
                self.cursor.execute("""
                    INSERT INTO fines (record_id, reader_id, amount, reason)
                    SELECT %s, reader_id, %s, '逾期归还'
                    FROM borrow_records WHERE record_id = %s
                """, (record_id, fine_amount, record_id))
                message = f"还书成功，产生逾期罚款 {fine_amount} 元"
            else:
                message = "还书成功"
            # 更新图书库存
            self.cursor.execute("""
                UPDATE books SET available_count = available_count + 1 
                WHERE book_id = %s
            """, (record['book_id'],))
            self.conn.commit()
            return {"success": True, "message": message}
        except Exception as e:
            self.conn.rollback()
            return {"success": False, "message": str(e)}
    # 查询图书
    def search_books(self, keyword, category=None):
        query = """
            SELECT b.*, 
                   (b.total_count - b.available_count) as borrowed_count
            FROM books b
            WHERE b.title LIKE %s OR b.author LIKE %s OR b.isbn LIKE %s
        """
        params = [f'%{keyword}%'] * 3
        if category:
            query += " AND b.category = %s"
            params.append(category)
        self.cursor.execute(query, params)
        return self.cursor.fetchall()
    # 获取逾期未还图书
    def get_overdue_books(self):
        self.cursor.execute("""
            SELECT br.record_id, r.name as reader_name, b.title as book_title,
                   br.due_date, DATEDIFF(CURDATE(), br.due_date) as overdue_days
            FROM borrow_records br
            JOIN readers r ON br.reader_id = r.reader_id
            JOIN books b ON br.book_id = b.book_id
            WHERE br.status = '借出' AND br.due_date < CURDATE()
        """)
        return self.cursor.fetchall()

电商订单系统

数据库设计

CREATE DATABASE ecommerce;
USE ecommerce;
-- 用户表
CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20),
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP
);
-- 商品分类表
CREATE TABLE categories (
    category_id INT PRIMARY KEY AUTO_INCREMENT,
    parent_id INT DEFAULT NULL,
    name VARCHAR(50) NOT NULL,
    description TEXT,
    FOREIGN KEY (parent_id) REFERENCES categories(category_id)
);
-- 商品表
CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    category_id INT,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL,
    stock INT DEFAULT 0,
    sales INT DEFAULT 0,
    image_url VARCHAR(500),
    status VARCHAR(20) DEFAULT '上架',
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(category_id)
);
-- 购物车表
CREATE TABLE cart (
    cart_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT DEFAULT 1,
    add_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_cart_item (user_id, product_id),
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);
-- 订单表
CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    order_no VARCHAR(30) UNIQUE NOT NULL,
    user_id INT NOT NULL,
    total_amount DECIMAL(12,2) NOT NULL,
    pay_amount DECIMAL(12,2) NOT NULL,
    freight_amount DECIMAL(10,2),
    status VARCHAR(20) DEFAULT '待支付',
    address VARCHAR(200) NOT NULL,
    receiver VARCHAR(50) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    pay_time DATETIME,
    delivery_time DATETIME,
    receive_time DATETIME,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);
-- 订单明细表
CREATE TABLE order_items (
    item_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    product_name VARCHAR(200),
    product_image VARCHAR(500),
    price DECIMAL(10,2),
    quantity INT,
    total_price DECIMAL(10,2),
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

核心功能实现（Java + JDBC）

public class OrderService {
    // 创建订单
    public boolean createOrder(Order order, List<CartItem> cartItems) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnection();
            conn.setAutoCommit(false);
            // 生成订单号
            String orderNo = generateOrderNo(conn);
            // 计算订单金额
            double totalAmount = 0;
            for (CartItem item : cartItems) {
                // 检查库存
                if (!checkStock(conn, item.getProductId(), item.getQuantity())) {
                    throw new RuntimeException("商品库存不足：" + item.getProductName());
                }
                totalAmount += item.getPrice() * item.getQuantity();
            }
            // 插入订单
            String sql = "INSERT INTO orders (order_no, user_id, total_amount, pay_amount, " +
                        "address, receiver, phone) VALUES (?, ?, ?, ?, ?, ?, ?)";
            ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, orderNo);
            ps.setInt(2, order.getUserId());
            ps.setDouble(3, totalAmount);
            ps.setDouble(4, totalAmount);
            ps.setString(5, order.getAddress());
            ps.setString(6, order.getReceiver());
            ps.setString(7, order.getPhone());
            ps.executeUpdate();
            // 获取订单ID
            rs = ps.getGeneratedKeys();
            int orderId = 0;
            if (rs.next()) {
                orderId = rs.getInt(1);
            }
            // 插入订单明细并更新库存
            for (CartItem item : cartItems) {
                insertOrderItem(conn, orderId, item);
                updateStock(conn, item.getProductId(), item.getQuantity());
            }
            // 清空购物车
            clearCart(conn, order.getUserId());
            conn.commit();
            return true;
        } catch (Exception e) {
            if (conn != null) {
                try {
                    conn.rollback();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            throw new RuntimeException("创建订单失败：" + e.getMessage());
        } finally {
            DBUtil.close(conn, ps, rs);
        }
    }
    // 取消订单
    public boolean cancelOrder(String orderNo, int userId) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnection();
            conn.setAutoCommit(false);
            // 查询订单状态
            String querySql = "SELECT order_id, status FROM orders WHERE order_no = ? AND user_id = ?";
            ps = conn.prepareStatement(querySql);
            ps.setString(1, orderNo);
            ps.setInt(2, userId);
            rs = ps.executeQuery();
            if (!rs.next()) {
                throw new RuntimeException("订单不存在");
            }
            String status = rs.getString("status");
            if (!"待支付".equals(status)) {
                throw new RuntimeException("当前订单状态不能取消");
            }
            int orderId = rs.getInt("order_id");
            // 查询订单明细，恢复库存
            String itemsSql = "SELECT product_id, quantity FROM order_items WHERE order_id = ?";
            ps = conn.prepareStatement(itemsSql);
            ps.setInt(1, orderId);
            rs = ps.executeQuery();
            List<ItemInfo> items = new ArrayList<>();
            while (rs.next()) {
                ItemInfo item = new ItemInfo();
                item.setProductId(rs.getInt("product_id"));
                item.setQuantity(rs.getInt("quantity"));
                items.add(item);
            }
            // 恢复库存
            for (ItemInfo item : items) {
                String updateStockSql = "UPDATE products SET stock = stock + ? WHERE product_id = ?";
                ps = conn.prepareStatement(updateStockSql);
                ps.setInt(1, item.getQuantity());
                ps.setInt(2, item.getProductId());
                ps.executeUpdate();
            }
            // 更新订单状态
            String updateOrderSql = "UPDATE orders SET status = '已取消' WHERE order_id = ?";
            ps = conn.prepareStatement(updateOrderSql);
            ps.setInt(1, orderId);
            ps.executeUpdate();
            conn.commit();
            return true;
        } catch (Exception e) {
            if (conn != null) {
                try {
                    conn.rollback();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            throw new RuntimeException("取消订单失败：" + e.getMessage());
        } finally {
            DBUtil.close(conn, ps, rs);
        }
    }
    // 支付订单
    public boolean payOrder(String orderNo, int userId, PaymentType paymentType) {
        // 使用事务和乐观锁实现
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnection();
            conn.setAutoCommit(false);
            // 使用乐观锁防止并发问题
            String sql = "UPDATE orders SET status = '已支付', pay_time = NOW() " +
                        "WHERE order_no = ? AND user_id = ? AND status = '待支付'";
            ps = conn.prepareStatement(sql);
            ps.setString(1, orderNo);
            ps.setInt(2, userId);
            int rows = ps.executeUpdate();
            if (rows == 0) {
                throw new RuntimeException("订单状态已变化，请刷新后重试");
            }
            conn.commit();
            return true;
        } catch (Exception e) {
            if (conn != null) {
                try {
                    conn.rollback();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            throw new RuntimeException("支付失败：" + e.getMessage());
        } finally {
            DBUtil.close(conn, ps, rs);
        }
    }
    // 查询用户订单列表（分页）
    public PageResult<OrderVO> queryOrders(int userId, int pageNum, int pageSize, String status) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnection();
            // 构建查询条件
            StringBuilder whereSql = new StringBuilder(" WHERE user_id = ?");
            List<Object> params = new ArrayList<>();
            params.add(userId);
            if (status != null && !"".equals(status)) {
                whereSql.append(" AND status = ?");
                params.add(status);
            }
            // 查询总数
            String countSql = "SELECT COUNT(*) FROM orders" + whereSql;
            ps = conn.prepareStatement(countSql);
            setParams(ps, params);
            rs = ps.executeQuery();
            int total = 0;
            if (rs.next()) {
                total = rs.getInt(1);
            }
            // 分页查询
            String listSql = "SELECT * FROM orders" + whereSql + 
                           " ORDER BY create_time DESC LIMIT ?, ?";
            ps = conn.prepareStatement(listSql);
            setParams(ps, params);
            ps.setInt(params.size() + 1, (pageNum - 1) * pageSize);
            ps.setInt(params.size() + 2, pageSize);
            rs = ps.executeQuery();
            List<OrderVO> orders = new ArrayList<>();
            while (rs.next()) {
                OrderVO vo = convertToVO(rs);
                // 获取订单明细
                vo.setItems(queryOrderItems(conn, rs.getInt("order_id")));
                orders.add(vo);
            }
            PageResult<OrderVO> pageResult = new PageResult<>();
            pageResult.setList(orders);
            pageResult.setTotal(total);
            pageResult.setPageNum(pageNum);
            pageResult.setPageSize(pageSize);
            pageResult.setTotalPage((int) Math.ceil((double) total / pageSize));
            return pageResult;
        } catch (Exception e) {
            throw new RuntimeException("查询订单失败：" + e.getMessage());
        } finally {
            DBUtil.close(conn, ps, rs);
        }
    }
    // 商品销量统计
    public List<ProductStat> getTopProducts(int topN) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT p.product_id, p.name, p.sales, " +
                        "SUM(oi.quantity) as total_sold " +
                        "FROM products p " +
                        "LEFT JOIN order_items oi ON p.product_id = oi.product_id " +
                        "LEFT JOIN orders o ON oi.order_id = o.order_id " +
                        "AND o.status IN ('已支付', '已发货', '已完成') " +
                        "GROUP BY p.product_id, p.name, p.sales " +
                        "ORDER BY total_sold DESC LIMIT ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, topN);
            rs = ps.executeQuery();
            List<ProductStat> stats = new ArrayList<>();
            while (rs.next()) {
                ProductStat stat = new ProductStat();
                stat.setProductId(rs.getInt("product_id"));
                stat.setProductName(rs.getString("name"));
                stat.setTotalSales(rs.getInt("total_sold"));
                stats.add(stat);
            }
            return stats;
        } catch (Exception e) {
            throw new RuntimeException("统计销量失败：" + e.getMessage());
        } finally {
            DBUtil.close(conn, ps, rs);
        }
    }
}

员工考勤系统

数据库设计

CREATE DATABASE attendance_system;
USE attendance_system;
-- 部门表
CREATE TABLE departments (
    dept_id INT PRIMARY KEY AUTO_INCREMENT,
    dept_name VARCHAR(50) NOT NULL,
    manager_id INT,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 员工表
CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_no VARCHAR(20) UNIQUE NOT NULL,
    name VARCHAR(50) NOT NULL,
    gender CHAR(2),
    birth_date DATE,
    phone VARCHAR(20),
    email VARCHAR(100),
    dept_id INT,
    position VARCHAR(50),
    hire_date DATE,
    salary DECIMAL(10,2),
    status VARCHAR(20) DEFAULT '在职',
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);
-- 考勤记录表
CREATE TABLE attendance_records (
    record_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_id INT NOT NULL,
    work_date DATE NOT NULL,
    check_in_time DATETIME,
    check_out_time DATETIME,
    status VARCHAR(20) DEFAULT '正常',
    late_minutes INT DEFAULT 0,
    early_minutes INT DEFAULT 0,
    overtime_hours DECIMAL(4,1) DEFAULT 0,
    remark VARCHAR(200),
    FOREIGN KEY (emp_id) REFERENCES employees(emp_id),
    UNIQUE KEY unique_attendance (emp_id, work_date)
);
-- 请假表
CREATE TABLE leave_requests (
    leave_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_id INT NOT NULL,
    leave_type VARCHAR(20) NOT NULL, -- 年假/事假/病假/调休
    start_time DATETIME NOT NULL,
    end_time DATETIME NOT NULL,
    hours DECIMAL(4,1),
    reason TEXT,
    status VARCHAR(20) DEFAULT '待审批',
    approver_id INT,
    approve_time DATETIME,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (emp_id) REFERENCES employees(emp_id),
    FOREIGN KEY (approver_id) REFERENCES employees(emp_id)
);
-- 加班申请
CREATE TABLE overtime_requests (
    overtime_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_id INT NOT NULL,
    overtime_date DATE NOT NULL,
    start_time TIME,
    end_time TIME,
    hours DECIMAL(4,1),
    reason VARCHAR(200),
    status VARCHAR(20) DEFAULT '待审批',
    approver_id INT,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (emp_id) REFERENCES employees(emp_id)
);

核心功能实现（Node.js + MySQL）

const mysql = require('mysql2/promise');
const moment = require('moment');
class AttendanceService {
    constructor() {
        this.pool = mysql.createPool({
            host: 'localhost',
            user: 'root',
            password: 'password',
            database: 'attendance_system',
            waitForConnections: true,
            connectionLimit: 10,
            queueLimit: 0
        });
    }
    // 签到
    async checkIn(empId) {
        const connection = await this.pool.getConnection();
        try {
            await connection.beginTransaction();
            const now = moment();
            const today = now.format('YYYY-MM-DD');
            const time = now.format('HH:mm:ss');
            // 检查员工是否在职
            const [empResult] = await connection.execute(
                'SELECT * FROM employees WHERE emp_id = ? AND status = "在职"',
                [empId]
            );
            if (empResult.length === 0) {
                throw new Error('员工不存在或不在职');
            }
            // 检查是否已签到
            const [attResult] = await connection.execute(
                'SELECT * FROM attendance_records WHERE emp_id = ? AND work_date = ?',
                [empId, today]
            );
            if (attResult.length > 0) {
                if (attResult[0].check_in_time) {
                    throw new Error('今天已经签到过了');
                }
            }
            // 判断是否迟到（假设9:00上班）
            const standardTime = moment(`${today} 09:00:00`);
            const lateMinutes = now.diff(standardTime, 'minutes');
            let status = '正常';
            if (lateMinutes > 0) {
                status = '迟到';
            }
            // 插入或更新考勤记录
            const sql = `
                INSERT INTO attendance_records (emp_id, work_date, check_in_time, status, late_minutes)
                VALUES (?, ?, ?, ?, ?)
                ON DUPLICATE KEY UPDATE 
                check_in_time = VALUES(check_in_time),
                status = VALUES(status),
                late_minutes = VALUES(late_minutes)
            `;
            await connection.execute(sql, [
                empId, 
                today, 
                now.format('YYYY-MM-DD HH:mm:ss'),
                status,
                lateMinutes > 0 ? lateMinutes : 0
            ]);
            // 更新employee的今日打卡状态（可选）
            await connection.commit();
            return {
                success: true,
                data: {
                    empId,
                    checkInTime: now.format('YYYY-MM-DD HH:mm:ss'),
                    status,
                    lateMinutes: lateMinutes > 0 ? lateMinutes : 0
                }
            };
        } catch (error) {
            await connection.rollback();
            throw error;
        } finally {
            connection.release();
        }
    }
    // 签退
    async checkOut(empId) {
        const connection = await this.pool.getConnection();
        try {
            await connection.beginTransaction();
            const now = moment();
            const today = now.format('YYYY-MM-DD');
            // 检查是否有签到记录
            const [attResult] = await connection.execute(
                'SELECT * FROM attendance_records WHERE emp_id = ? AND work_date = ?',
                [empId, today]
            );
            if (attResult.length === 0 || !attResult[0].check_in_time) {
                throw new Error('请先签到');
            }
            if (attResult[0].check_out_time) {
                throw new Error('今天已经签退过了');
            }
            // 判断是否早退（假设18:00下班）
            const checkoutTime = moment(`${today} 18:00:00`);
            const earlyMinutes = checkoutTime.diff(now, 'minutes');
            let status = attResult[0].status;
            if (earlyMinutes > 0) {
                status = '早退';
                if (attResult[0].status === '正常') {
                    status = '早退';
                } else {
                    status = '迟到早退';
                }
            }
            // 计算加班时长
            let overtimeHours = 0;
            if (now.isAfter(checkoutTime)) {
                overtimeHours = parseFloat(now.diff(checkoutTime, 'hours', true).toFixed(1));
            }
            const updateSql = `
                UPDATE attendance_records 
                SET check_out_time = ?, status = ?, early_minutes = ?, overtime_hours = ?
                WHERE emp_id = ? AND work_date = ?
            `;
            await connection.execute(updateSql, [
                now.format('YYYY-MM-DD HH:mm:ss'),
                status,
                earlyMinutes > 0 ? earlyMinutes : 0,
                overtimeHours,
                empId,
                today
            ]);
            await connection.commit();
            return {
                success: true,
                data: {
                    empId,
                    checkOutTime: now.format('YYYY-MM-DD HH:mm:ss'),
                    status,
                    earlyMinutes: earlyMinutes > 0 ? earlyMinutes : 0,
                    overtimeHours
                }
            };
        } catch (error) {
            await connection.rollback();
            throw error;
        } finally {
            connection.release();
        }
    }
    // 申请请假
    async requestLeave(leaveData) {
        const connection = await this.pool.getConnection();
        try {
            await connection.beginTransaction();
            const { empId, leaveType, startTime, endTime, reason } = leaveData;
            // 检查请假时间是否有效
            if (new Date(endTime) <= new Date(startTime)) {
                throw new Error('请假结束时间必须大于开始时间');
            }
            // 计算请假小时数
            const hours = moment(endTime).diff(moment(startTime), 'hours', true);
            if (hours <= 0) {
                throw new Error('请假时长必须大于0');
            }
            // 检查是否有重叠的请假
            const [overlapResult] = await connection.execute(
                `SELECT * FROM leave_requests 
                 WHERE emp_id = ? 
                 AND status = '已批准'
                 AND start_time < ? 
                 AND end_time > ?`,
                [empId, endTime, startTime]
            );
            if (overlapResult.length > 0) {
                throw new Error('与已有的请假时间冲突');
            }
            // 检查是否有签到记录（请假当天不能签到）
            const leaveDates = this.getDatesBetween(startTime, endTime);
            for (const date of leaveDates) {
                const [attResult] = await connection.execute(
                    'SELECT * FROM attendance_records WHERE emp_id = ? AND work_date = ?',
                    [empId, date]
                );
                // 可以在这里处理已有签到的日期
            }
            // 插入请假记录
            const insertSql = `
                INSERT INTO leave_requests (emp_id, leave_type, start_time, end_time, hours, reason)
                VALUES (?, ?, ?, ?, ?, ?)
            `;
            await connection.execute(insertSql, [
                empId, 
                leaveType, 
                startTime, 
                endTime, 
                parseFloat(hours.toFixed(1)),
                reason
            ]);
            await connection.commit();
            return {
                success: true,
                message: '请假申请已提交'
            };
        } catch (error) {
            await connection.rollback();
            throw error;
        } finally {
            connection.release();
        }
    }
    // 查询员工考勤统计
    async getAttendanceStats(empId, month) {
        const connection = await this.pool.getConnection();
        try {
            const startDate = moment(month + '-01');
            const endDate = startDate.clone().endOf('month');
            const sql = `
                SELECT 
                    COUNT(DISTINCT work_date) as total_days,
                    SUM(CASE WHEN status = '正常' THEN 1 ELSE 0 END) as normal_days,
                    SUM(CASE WHEN status = '迟到' THEN 1 ELSE 0 END) as late_days,
                    SUM(CASE WHEN status = '早退' THEN 1 ELSE 0 END) as early_days,
                    SUM(CASE WHEN status = '迟到早退' THEN 1 ELSE 0 END) as late_early_days,
                    SUM(late_minutes) as total_late_minutes,
                    SUM(early_minutes) as total_early_minutes,
                    SUM(overtime_hours) as total_overtime_hours
                FROM attendance_records
                WHERE emp_id = ? AND work_date BETWEEN ? AND ?
            `;
            const [rows] = await connection.execute(sql, [
                empId,
                startDate.format('YYYY-MM-DD'),
                endDate.format('YYYY-MM-DD')
            ]);
            // 查询请假记录
            const [leaveRows] = await connection.execute(
                `SELECT 
                    leave_type,
                    COUNT(*) as leave_count,
                    SUM(hours) as total_hours
                 FROM leave_requests
                 WHERE emp_id = ? 
                 AND status = '已批准'
                 AND start_time BETWEEN ? AND ?
                 GROUP BY leave_type`,
                [empId, startDate.format('YYYY-MM-DD'), endDate.format('YYYY-MM-DD')]
            );
            return {
                success: true,
                data: {
                    month: month,
                    stats: rows[0],
                    leaves: leaveRows
                }
            };
        } catch (error) {
            throw error;
        } finally {
            connection.release();
        }
    }
    // 生成考勤报表
    async generateMonthlyReport(deptId, month) {
        const connection = await this.pool.getConnection();
        try {
            const startDate = moment(month + '-01').format('YYYY-MM-DD');
            const endDate = moment(month).endOf('month').format('YYYY-MM-DD');
            const sql = `
                SELECT 
                    e.emp_id,
                    e.emp_no,
                    e.name,
                    e.position,
                    d.dept_name,
                    COUNT(DISTINCT a.work_date) as work_days,
                    SUM(CASE WHEN a.status = '正常' THEN 1 ELSE 0 END) as normal_days,
                    SUM(CASE WHEN a.status = '迟到' THEN 1 ELSE 0 END) as late_count,
                    SUM(CASE WHEN a.status = '早退' THEN 1 ELSE 0 END) as early_count,
                    SUM(CASE WHEN a.status = '请假' THEN 1 ELSE 0 END) as leave_days,
                    SUM(a.late_minutes) as total_late_minutes,
                    SUM(a.overtime_hours) as total_overtime_hours,
                    AVG(a.late_minutes) as avg_late_minutes
                FROM employees e
                JOIN departments d ON e.dept_id = d.dept_id
                LEFT JOIN attendance_records a ON e.emp_id = a.emp_id 
                    AND a.work_date BETWEEN ? AND ?
                WHERE e.status = '在职'
                AND (e.dept_id = ? OR ? IS NULL)
                GROUP BY e.emp_id, e.emp_no, e.name, e.position, d.dept_name
                ORDER BY d.dept_name, e.emp_no
            `;
            const [rows] = await connection.execute(sql, [
                startDate,
                endDate,
                deptId || null,
                deptId || null
            ]);
            return {
                success: true,
                data: rows
            };
        } catch (error) {
            throw error;
        } finally {
            connection.release();
        }
    }
}

酒店管理系统

核心功能：实时库存和并发控制

-- 房间表
CREATE TABLE rooms (
    room_id INT PRIMARY KEY AUTO_INCREMENT,
    room_no VARCHAR(10) UNIQUE NOT NULL,
    room_type VARCHAR(20) NOT NULL, -- 标准间/大床房/套房
    price DECIMAL(10,2) NOT NULL,
    capacity INT,
    floor INT,
    status VARCHAR(20) DEFAULT '空闲', -- 空闲/入住/维修/预留
    description TEXT
);
-- 预订表
CREATE TABLE reservations (
    reservation_id INT PRIMARY KEY AUTO_INCREMENT,
    reservation_no VARCHAR(20) UNIQUE,
    guest_name VARCHAR(50) NOT NULL,
    guest_phone VARCHAR(20),
    room_type VARCHAR(20),
    room_id INT,
    check_in_date DATE NOT NULL,
    check_out_date DATE NOT NULL,
    num_guests INT,
    status VARCHAR(20) DEFAULT '已确认', -- 已确认/已入住/已取消/已完成
    total_amount DECIMAL(10,2),
    deposit DECIMAL(10,2),
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (room_id) REFERENCES rooms(room_id)
);

// 使用乐观锁处理并发预订
public class RoomReservationService {
    public synchronized ReservationResult reserveRoom(ReservationDTO dto) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnection();
            conn.setAutoCommit(false);
            // 1. 查询符合条件且有库存的房间
            String findRoomSql = """
                SELECT r.room_id, r.price, r.status
                FROM rooms r
                WHERE r.room_type = ? 
                AND r.status IN ('空闲', '预留')
                AND r.room_id NOT IN (
                    SELECT room_id FROM reservations
                    WHERE status IN ('已确认', '已入住')
                    AND room_id IS NOT NULL
                    AND (
                        (check_in_date < ? AND check_out_date > ?) OR
                        (check_in_date < ? AND check_out_date > ?)
                    )
                )
                LIMIT 1
                """;
            ps = conn.prepareStatement(findRoomSql);
            ps.setString(1, dto.getRoomType());
            ps.setDate(2, dto.getCheckInDate());
            ps.setDate(3, dto.getCheckOutDate());
            ps.setDate(4, dto.getCheckOutDate());
            ps.setDate(5, dto.getCheckInDate());
            rs = ps.executeQuery();
            if (!rs.next()) {
                throw new RuntimeException("没有符合条件且空闲的房间");
            }
            int roomId = rs.getInt("room_id");
            double price = rs.getDouble("price");
            // 2. 生成预订号
            String reservationNo = generateReservation