本文目录导读：
我来提供几个基于数据库实现的完整案例,涵盖不同场景和技术栈。
图书管理系统
数据库设计
-- 创建数据库
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
