14 KiB
14 KiB
实体类与数据库脚本同步示例
基础同步示例
示例1: 简单用户实体
Java实体类 (User.java):
package com.fundplatform.user.entity;
import javax.persistence.*;
import java.time.LocalDateTime;
@Entity
@Table(name = "user")
public class User {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(name = "username", length = 50, nullable = false, unique = true)
private String username;
@Column(name = "email", length = 100)
private String email;
@Column(name = "phone", length = 20)
private String phone;
@Column(name = "is_active", columnDefinition = "TINYINT(1) DEFAULT 1")
private Boolean active;
@Column(name = "created_time", updatable = false)
private LocalDateTime createdTime;
@Column(name = "updated_time")
private LocalDateTime updatedTime;
// getters and setters...
}
对应SQL脚本 (user.sql):
CREATE TABLE `user` (
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '用户ID',
`username` VARCHAR(50) NOT NULL COMMENT '用户名',
`email` VARCHAR(100) COMMENT '邮箱地址',
`phone` VARCHAR(20) COMMENT '手机号码',
`is_active` TINYINT(1) DEFAULT 1 COMMENT '是否激活',
`created_time` DATETIME COMMENT '创建时间',
`updated_time` DATETIME COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_username` (`username`),
KEY `idx_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户基本信息表';
示例2: 订单实体（复杂类型）
Java实体类 (Order.java):
package com.fundplatform.order.entity;
import javax.persistence.*;
import java.math.BigDecimal;
import java.time.LocalDate;
import java.util.List;
@Entity
@Table(name = "fund_order")
public class Order {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long orderId;
@Column(name = "order_no", length = 32, nullable = false, unique = true)
private String orderNo;
@Column(name = "customer_id", nullable = false)
private Long customerId;
@Column(name = "project_id")
private Long projectId;
@Column(name = "amount", precision = 15, scale = 2, nullable = false)
private BigDecimal amount;
@Column(name = "order_date", nullable = false)
private LocalDate orderDate;
@Enumerated(EnumType.STRING)
@Column(name = "order_status", length = 20, nullable = false)
private OrderStatus status;
@Column(name = "payment_method", length = 20)
private String paymentMethod;
@Column(name = "remark", length = 500)
private String remark;
// 枚举定义
public enum OrderStatus {
PENDING, CONFIRMED, PROCESSING, COMPLETED, CANCELLED
}
}
对应SQL脚本 (fund_order.sql):
CREATE TABLE `fund_order` (
`order_id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '订单ID',
`order_no` VARCHAR(32) NOT NULL COMMENT '订单编号',
`customer_id` BIGINT NOT NULL COMMENT '客户ID',
`project_id` BIGINT COMMENT '项目ID',
`amount` DECIMAL(15,2) NOT NULL COMMENT '订单金额',
`order_date` DATE NOT NULL COMMENT '下单日期',
`order_status` VARCHAR(20) NOT NULL COMMENT '订单状态',
`payment_method` VARCHAR(20) COMMENT '支付方式',
`remark` VARCHAR(500) COMMENT '备注',
`created_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`updated_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`order_id`),
UNIQUE KEY `uk_order_no` (`order_no`),
KEY `idx_customer_id` (`customer_id`),
KEY `idx_order_date` (`order_date`),
KEY `idx_order_status` (`order_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='基金订单表';
字段变更同步示例
场景1: 添加新字段
原始实体类:
@Entity
@Table(name = "product")
public class Product {
@Id
private Long id;
@Column(length = 100)
private String name;
@Column(precision = 10, scale = 2)
private BigDecimal price;
}
修改后的实体类:
@Entity
@Table(name = "product")
public class Product {
@Id
private Long id;
@Column(length = 100, nullable = false) // 添加非空约束
private String name;
@Column(precision = 10, scale = 2, nullable = false) // 添加非空约束
private BigDecimal price;
// 新增字段
@Column(name = "category_id")
private Long categoryId;
@Column(length = 500)
private String description;
@Column(name = "is_available", columnDefinition = "TINYINT(1) DEFAULT 1")
private Boolean available;
}
变更脚本生成:
# 使用变更脚本生成器
python3 scripts/generate-change-scripts.py \
--entity fund-product/src/main/java/com/fundplatform/product/entity/Product.java \
--version v1.1.0 \
--description "为产品表添加分类和描述字段"
# 生成的脚本文件位置（部署目录）
deploy/sql/upgrade/v1.1.0/20240115_add_product_fields.sql
deploy/sql/upgrade/v1.1.0/rollback/20240115_add_product_fields_rollback.sql
生成的正向变更脚本 (20240115_add_product_fields.sql):
-- ============================================
-- 变更描述: 为产品表添加分类和描述字段
-- 变更时间: 2024-01-15 14:30:00
-- 影响版本: v1.1.0
-- 影响表: product
-- ============================================
-- 1. 添加字段
ALTER TABLE `product`
ADD COLUMN `category_id` BIGINT COMMENT '分类ID',
ADD COLUMN `description` VARCHAR(500) COMMENT '产品描述',
ADD COLUMN `is_available` TINYINT(1) DEFAULT 1 COMMENT '是否可用';
-- 2. 添加索引
ALTER TABLE `product`
ADD INDEX `idx_category_id` (`category_id`);
-- 3. 验证变更
SELECT COUNT(*) as new_columns FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'product' AND COLUMN_NAME IN ('category_id', 'description', 'is_available');
生成的回滚脚本 (20240115_add_product_fields_rollback.sql):
-- ============================================
-- 回滚描述: 撤销产品表字段添加变更
-- 回滚时间: 2024-01-15 14:30:00
-- 原版本: v1.1.0
-- 影响表: product
-- ============================================
-- 1. 删除索引
ALTER TABLE `product` DROP INDEX `idx_category_id`;
-- 2. 删除字段
ALTER TABLE `product`
DROP COLUMN `category_id`,
DROP COLUMN `description`,
DROP COLUMN `is_available`;
-- 3. 验证回滚
SELECT COUNT(*) as remaining_new_columns FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'product' AND COLUMN_NAME IN ('category_id', 'description', 'is_available');
场景2: 字段类型变更
实体类变更:
// 原始：String类型存储状态
@Column(length = 20)
private String status;
// 修改为：枚举类型
@Enumerated(EnumType.STRING)
@Column(length = 20)
private OrderStatus status;
public enum OrderStatus {
DRAFT, SUBMITTED, APPROVED, REJECTED
}
变更脚本:
-- 20240120_modify_order_status_type.sql
-- ============================================
-- 变更描述: 修改订单状态字段类型为枚举
-- 变更时间: 2024-01-20
-- ============================================
-- 1. 添加新字段
ALTER TABLE `order` ADD COLUMN `status_enum` VARCHAR(20) COMMENT '订单状态(枚举)';
-- 2. 数据迁移
UPDATE `order` SET `status_enum` = `status` WHERE `status` IN ('DRAFT', 'SUBMITTED', 'APPROVED', 'REJECTED');
-- 3. 删除旧字段
ALTER TABLE `order` DROP COLUMN `status`;
-- 4. 重命名新字段
ALTER TABLE `order` CHANGE `status_enum` `status` VARCHAR(20) COMMENT '订单状态';
-- 5. 添加约束
ALTER TABLE `order` ADD CONSTRAINT `chk_status_values`
CHECK (`status` IN ('DRAFT', 'SUBMITTED', 'APPROVED', 'REJECTED'));
文档同步示例
场景3: 设计文档更新
更新数据库设计文档:
# product 表结构文档
## 表基本信息
- **表名**: `product`
- **描述**: 产品信息表
- **引擎**: InnoDB
- **字符集**: utf8mb4
## 字段定义
| 字段名 | 类型 | 允许NULL | 默认值 | 描述 |
|--------|------|----------|--------|------|
| id | BIGINT | NO | AUTO_INCREMENT | 产品ID |
| name | VARCHAR(100) | NO | | 产品名称 |
| price | DECIMAL(10,2) | NO | | 产品价格 |
| category_id | BIGINT | YES | NULL | 分类ID |
| description | VARCHAR(500) | YES | NULL | 产品描述 |
| is_available | TINYINT(1) | YES | 1 | 是否可用 |
| created_time | DATETIME | YES | CURRENT_TIMESTAMP | 创建时间 |
| updated_time | DATETIME | YES | CURRENT_TIMESTAMP | 更新时间 |
## 索引信息
| 索引名 | 类型 | 字段 | 描述 |
|--------|------|------|------|
| PRIMARY | PRIMARY | id | 主键索引 |
| idx_category_id | NORMAL | category_id | 分类索引 |
## 变更历史
| 日期 | 版本 | 变更描述 | 变更人 |
|------|------|----------|--------|
| 2024-01-15 | v1.1.0 | 添加分类ID、产品描述和可用状态字段 | Zhang San |
| 2024-01-10 | v1.0.0 | 初始版本 | Li Si |
API文档更新:
# 产品管理API接口
## GET /api/products
获取产品列表
### 请求参数
| 参数名 | 类型 | 必填 | 描述 |
|--------|------|------|------|
| categoryId | Long | 否 | 分类产品筛选 |
| available | Boolean | 否 | 可用性筛选 |
### 响应示例
```json
{
"code": 200,
"data": {
"records": [
{
"id": 1,
"name": "基金产品A",
"price": 100.50,
"categoryId": 1,
"description": "这是一款优质基金产品",
"available": true,
"createdTime": "2024-01-15T10:30:00"
}
]
}
}
## 生产环境部署示例
### 场景4: 生产环境变更流程
**变更申请表:**
```markdown
# 数据库变更申请
## 基本信息
- **申请人**: 张三
- **申请时间**: 2024-01-15 15:00
- **变更类型**: 字段添加
- **影响系统**: 产品管理系统
- **预计 downtime**: 5分钟
## 变更详情
- **变更SQL**: src/main/resources/sql/upgrade/v1.1.0/20240115_add_product_fields.sql
- **回滚SQL**: src/main/resources/sql/upgrade/v1.1.0/rollback/20240115_add_product_fields_rollback.sql
- **验证SQL**: src/main/resources/sql/upgrade/v1.1.0/verify/20240115_add_product_fields_verify.sql
## 风险评估
- [x] 数据备份已完成
- [x] 回滚方案已准备
- [x] 测试环境验证通过
- [x] 监控告警已设置
## 执行计划
1. 20:00 - 开始变更
2. 20:01 - 执行备份
3. 20:02 - 执行变更脚本
4. 20:03 - 执行验证脚本
5. 20:04 - 功能测试
6. 20:05 - 变更完成
生产环境执行脚本:
#!/bin/bash
# production-deploy.sh
set -e
echo "=== 生产环境数据库变更部署 ==="
echo "变更版本: v1.1.0"
echo "执行时间: $(date)"
echo ""
# 1. 环境检查
echo "1. 环境检查..."
if [ -z "$DB_HOST" ] || [ -z "$DB_USER" ] || [ -z "$DB_PASSWORD" ]; then
echo "❌ 数据库连接信息不完整"
exit 1
fi
# 2. 数据库备份
echo "2. 执行数据库备份..."
BACKUP_FILE="/backup/proddb_$(date +%Y%m%d_%H%M%S).sql"
mysqldump -h$DB_HOST -u$DB_USER -p$DB_PASSWORD fund_platform > $BACKUP_FILE
echo "✓ 备份完成: $BACKUP_FILE"
# 3. 执行变更（使用部署目录）
echo "3. 执行变更脚本..."
mysql -h$DB_HOST -u$DB_USER -p$DB_PASSWORD fund_platform < \
deploy/sql/upgrade/v1.1.0/20240115_add_product_fields.sql
# 4. 验证变更
echo "4. 验证变更结果..."
mysql -h$DB_HOST -u$DB_USER -p$DB_PASSWORD fund_platform < \
deploy/sql/upgrade/v1.1.0/verify/20240115_add_product_fields_verify.sql
# 5. 记录变更日志
echo "5. 记录变更日志..."
mysql -h$DB_HOST -u$DB_USER -p$DB_PASSWORD fund_platform << EOF
INSERT INTO db_changelog (version, script_name, execute_time, operator, environment)
VALUES ('v1.1.0', '20240115_add_product_fields.sql', NOW(), '$USER', 'production');
EOF
echo "✓ 变更部署完成"
echo "请进行业务功能验证"
自动化验证示例
验证脚本使用
单个实体验证:
# 验证用户实体与数据库脚本同步性
python3 scripts/validate-entity-db-sync.py \
fund-user/src/main/java/com/fundplatform/user/entity/User.java \
fund-user/src/main/resources/sql/init/user.sql
# 输出示例：
# ✅ 字段一致: id -> id (BIGINT)
# ✅ 字段一致: username -> username (VARCHAR(50))
# ⚠️ 新增字段: phone (数据库中缺失)
# ❌ 类型不匹配: email长度应为100，当前为50
批量验证:
# 验证所有模块的实体同步性
./scripts/batch-validate-sync.sh
# 输出示例：
# === 批量验证实体类与数据库脚本同步 ===
# 检查: User -> user
# ✅ 实体类与数据库脚本同步一致
#
# 检查: Order -> fund_order
# ⚠️ 发现3个不一致项
# ❌ 字段email长度不匹配
# ⚠️ 缺少字段phone
# ⚠️ 缺少字段description
#
# === 检查完成 ===
# 错误: 1
# 警告: 2
CI集成配置
GitHub Actions配置:
name: Entity-DB Sync Check
on: [push, pull_request]
jobs:
sync-check:
runs-on: ubuntu-latest
steps:
- uses: actions/checkout@v3
- name: Setup Python
uses: actions/setup-python@v4
with:
python-version: '3.9'
- name: Install dependencies
run: pip install mysql-connector-python
- name: Run sync validation
run: |
chmod +x scripts/batch-validate-sync.sh
./scripts/batch-validate-sync.sh
- name: Generate change scripts
if: github.event_name == 'pull_request'
run: |
python3 scripts/generate-change-scripts.py --auto-generate
- name: Upload artifacts
if: failure()
uses: actions/upload-artifact@v3
with:
name: sync-report
path: sync-report.txt
Git Pre-commit Hook:
#!/bin/bash
# .git/hooks/pre-commit
# 检查是否有实体类修改
if git diff --cached --name-only | grep -q "entity/.*\.java"; then
echo "检测到实体类修改，运行同步检查..."
# 执行验证
if ! ./scripts/batch-validate-sync.sh; then
echo "❌ 实体类与数据库脚本不同步，请修复后再提交"
echo "提示：运行 'python3 scripts/generate-sql-from-entity.py <Entity.java>' 生成SQL"
exit 1
fi
# 自动生成变更脚本
echo "生成变更脚本..."
python3 scripts/generate-change-scripts.py --auto-generate
echo "✅ 同步检查通过"
fi
这些示例展示了从简单到复杂的各种同步场景，包括变更脚本生成、文档同步和生产环境部署的完整流程。