手把手教你搭建企业级资质证书档案管理系统

一、系统核心功能与架构设计

一个完整的资质证书档案管理系统需要包含证书信息录入、到期预警提醒、电子档案存储、权限分级管理四大核心模块。我们采用前后端分离架构，前端使用Vue.js 3 + Element Plus，后端使用Spring Boot 2.7，数据库使用MySQL 8.0，文件存储使用本地磁盘配合定时备份方案。

1.1 数据库表结构设计

创建数据库和用户：

```sql CREATE DATABASE certificate_db DEFAULT CHARACTER SET utf8mb4; CREATE USER 'cert_user'@'%' IDENTIFIED BY 'StrongPass123!'; GRANT ALL PRIVILEGES ON certificate_db. TO 'cert_user'@'%'; FLUSH PRIVILEGES; ```

创建核心数据表：

```sql USE certificate_db; CREATE TABLE certificate ( id BIGINT AUTO_INCREMENT PRIMARY KEY, certificate_number VARCHAR(100) NOT NULL UNIQUE COMMENT '证书编号', certificate_name VARCHAR(200) NOT NULL COMMENT '证书名称', certificate_type VARCHAR(50) COMMENT '证书类型：营业执照、资质证书等', issuing_authority VARCHAR(200) COMMENT '发证机构', issue_date DATE NOT NULL COMMENT '发证日期', expiry_date DATE NOT NULL COMMENT '到期日期', status TINYINT DEFAULT 1 COMMENT '状态：1-有效，2-即将到期，3-已过期', holder_department VARCHAR(100) COMMENT '持有部门', holder_person VARCHAR(50) COMMENT '保管人', physical_location VARCHAR(500) COMMENT '纸质版存放位置', digital_file_path VARCHAR(500) COMMENT '电子版文件路径', remarks TEXT COMMENT '备注', create_time DATETIME DEFAULT CURRENT_TIMESTAMP, update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_expiry_date (expiry_date), INDEX idx_status (status), INDEX idx_department (holder_department) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE reminder_log ( id BIGINT AUTO_INCREMENT PRIMARY KEY, certificate_id BIGINT NOT NULL, reminder_type VARCHAR(20) COMMENT '提醒类型：30天、15天、7天、到期', reminder_date DATE NOT NULL, notified_persons VARCHAR(500) COMMENT '被通知人邮箱，多个用逗号分隔', send_status TINYINT DEFAULT 0 COMMENT '0-未发送，1-已发送', send_time DATETIME, FOREIGN KEY (certificate_id) REFERENCES certificate(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; ```

二、后端服务搭建与核心功能实现

2.1 Spring Boot项目初始化

使用Spring Initializr创建项目，选择以下依赖：

• Spring Web
• Spring Data JPA
• MySQL Driver
• Spring Boot DevTools
• Java Mail Sender

application.yml配置文件：

```yaml server: port: 8080 servlet: context-path: /api spring: datasource: url: jdbc:mysql://localhost:3306/certificate_db?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai username: cert_user password: StrongPass123! driver-class-name: com.mysql.cj.jdbc.Driver jpa: hibernate: ddl-auto: update show-sql: true properties: hibernate: format_sql: true servlet: multipart: max-file-size: 50MB max-request-size: 50MB mail: host: smtp.office365.com port: 587 username: notifications@yourcompany.com password: YourEmailPassword properties: mail: smtp: auth: true starttls: enable: true file: upload: path: /data/certificate-files/ allowed-extensions: .pdf,.jpg,.jpeg,.png,.doc,.docx ```

2.2 证书到期预警服务实现

创建定时任务检查即将到期的证书：

```java @Component public class CertificateExpiryReminder { @Autowired private CertificateRepository certificateRepository; @Autowired private JavaMailSender mailSender; @Scheduled(cron = "0 0 9 ?") // 每天上午9点执行 public void checkExpiringCertificates() { LocalDate today = LocalDate.now(); LocalDate thirtyDaysLater = today.plusDays(30); LocalDate fifteenDaysLater = today.plusDays(15); LocalDate sevenDaysLater = today.plusDays(7); // 查询30天内到期的证书 List expiringCerts = certificateRepository .findByExpiryDateBetween(today, thirtyDaysLater); for (Certificate cert : expiringCerts) { long daysUntilExpiry = ChronoUnit.DAYS.between(today, cert.getExpiryDate()); String reminderType = getReminderType(daysUntilExpiry); if (reminderType != null) { sendReminderEmail(cert, reminderType, daysUntilExpiry); updateCertificateStatus(cert, daysUntilExpiry); } } } private String getReminderType(long days) { if (days == 30) return "30天"; if (days == 15) return "15天"; if (days == 7) return "7天"; if (days == 0) return "到期"; return null; } private void sendReminderEmail(Certificate cert, String type, long days) { SimpleMailMessage message = new SimpleMailMessage(); message.setTo(cert.getHolderPerson() + "@yourcompany.com"); message.setSubject("证书到期提醒 - " + cert.getCertificateName()); message.setText(String.format( "证书名称：%s\n证书编号：%s\n到期日期：%s\n剩余天数：%d天\n保管人：%s\n存放位置：%s\n\n请及时处理！", cert.getCertificateName(), cert.getCertificateNumber(), cert.getExpiryDate(), days, cert.getHolderPerson(), cert.getPhysicalLocation() )); mailSender.send(message); } } ```

2.3 文件上传服务实现

创建文件上传控制器：

```java @RestController @RequestMapping("/api/certificates") public class FileUploadController { @Value("${file.upload.path}") private String uploadPath; @Value("${file.upload.allowed-extensions}") private String allowedExtensions; @PostMapping("/{id}/upload") public ResponseEntity uploadFile( @PathVariable Long id, @RequestParam("file") MultipartFile file) { // 验证文件类型 String originalFilename = file.getOriginalFilename(); String extension = originalFilename.substring( originalFilename.lastIndexOf(".")).toLowerCase(); if (!Arrays.asList(allowedExtensions.split(",")) .contains(extension)) { return ResponseEntity.badRequest() .body("不支持的文件类型，仅支持：" + allowedExtensions); } // 生成唯一文件名 String newFilename = UUID.randomUUID().toString() + extension; Path filePath = Paths.get(uploadPath, newFilename); try { Files.createDirectories(filePath.getParent()); file.transferTo(filePath.toFile()); // 更新数据库记录 certificateRepository.updateFilePath(id, filePath.toString().replace("\\", "/")); return ResponseEntity.ok(Map.of( "filename", newFilename, "path", filePath.toString() )); } catch (IOException e) { return ResponseEntity.status(500) .body("文件上传失败：" + e.getMessage()); } } @GetMapping("/download/{filename}") public ResponseEntity downloadFile( @PathVariable String filename) { Path filePath = Paths.get(uploadPath, filename); try { Resource resource = new UrlResource(filePath.toUri()); if (resource.exists() && resource.isReadable()) { return ResponseEntity.ok() .header(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=\"" + filename + "\"") .contentType(MediaType.APPLICATION_OCTET_STREAM) .body(resource); } } catch (Exception e) { // 处理异常 } return ResponseEntity.notFound().build(); } } ```

三、前端管理界面开发

3.1 Vue.js项目初始化

创建Vue项目并安装必要依赖：

```bash npm create vue@latest certificate-manager cd certificate-manager npm install element-plus axios dayjs npm install --save-dev sass ```

3.2 证书列表页面实现

创建CertificateList.vue组件：

```vue ```

3.3 文件上传组件实现

创建FileUpload.vue组件：

```vue ```

四、系统部署与维护

4.1 生产环境部署配置

创建Docker部署文件：

```dockerfile 后端Dockerfile FROM openjdk:11-jre-slim WORKDIR /app COPY target/certificate-manager.jar app.jar RUN mkdir -p /data/certificate-files EXPOSE 8080 ENTRYPOINT ["java", "-jar", "app.jar"] ```

创建docker-compose.yml：

```yaml version: '3.8' services: mysql: image: mysql:8.0 environment: MYSQL_ROOT_PASSWORD: RootPass123! MYSQL_DATABASE: certificate_db MYSQL_USER: cert_user MYSQL_PASSWORD: StrongPass123! volumes: - mysql_data:/var/lib/mysql ports: - "3306:3306" command: --default-authentication-plugin=mysql_native_password backend: build: ./backend depends_on: - mysql environment: SPRING_DATASOURCE_URL: jdbc:mysql://mysql:3306/certificate_db SPRING_DATASOURCE_USERNAME: cert_user SPRING_DATASOURCE_PASSWORD: StrongPass123! volumes: - certificate_files:/data/certificate-files ports: - "8080:8080" frontend: build: ./frontend ports: - "80:80" depends_on: - backend volumes: mysql_data: certificate_files: ```

4.2 数据备份脚本

创建自动备份脚本backup.sh：

```bash !/bin/bash BACKUP_DIR="/backup/certificate_db" DATE=$(date +%Y%m%d_%H%M%S) BACKUP_FILE="$BACKUP_DIR/certificate_$DATE.sql" mkdir -p $BACKUP_DIR 备份数据库 mysqldump -u root -pRootPass123! certificate_db > $BACKUP_FILE 备份文件目录 tar -czf "$BACKUP_DIR/files_$DATE.tar.gz" /data/certificate-files 删除7天前的备份 find $BACKUP_DIR -name ".sql" -mtime +7 -delete find $BACKUP_DIR -name ".tar.gz" -mtime +7 -delete 记录日志 echo "$(date): 备份完成，文件：$BACKUP_FILE" >> /var/log/certificate_backup.log ```

设置定时任务：

```bash 每天凌晨2点执行备份 crontab -e 0 2 /opt/certificate-manager/scripts/backup.sh ```

4.3 系统监控配置

创建健康检查接口：

```java @RestController @RequestMapping("/api/health") public class HealthController { @Autowired private