首先,讓我們來看下數據庫建表語句:
CREATE TABLE behavior_reports (id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '報告ID',report_type VARCHAR(50) NOT NULL COMMENT '報告類型(daily, weekly, monthly)',start_date DATE NOT NULL COMMENT '開始日期',end_date DATE NOT NULL COMMENT '結束日期',total_users INT DEFAULT 0 COMMENT '總用戶數',active_users INT DEFAULT 0 COMMENT '活躍用戶數',total_visits INT DEFAULT 0 COMMENT '總訪問量',avg_duration INT DEFAULT 0 COMMENT '平均停留時長(秒)',bounce_rate DECIMAL(5,2) DEFAULT 0 COMMENT '跳出率(%)',conversion_rate DECIMAL(5,2) DEFAULT 0 COMMENT '轉化率(%)',retention_rate DECIMAL(5,2) DEFAULT 0 COMMENT '留存率(%)',report_data JSON COMMENT '詳細報告數據',created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間',INDEX idx_report_type (report_type),INDEX idx_date_range (start_date, end_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='行為分析報告表';
然后,引入依賴opencsv
<dependency><groupId>com.opencsv</groupId><artifactId>opencsv</artifactId><version>5.7.1</version></dependency></dependencies>
實現步驟
1、創建一個工具類CsvImportUtil
package com.useranalysis.util;import com.opencsv.CSVReader;
import com.opencsv.exceptions.CsvValidationException;
import com.useranalysis.entity.BehaviorReport;
import com.useranalysis.mapper.BehaviorReportMapper;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Component;import java.io.FileReader;
import java.io.IOException;
import java.math.BigDecimal;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;/*** CSV 數據導入工具類* 用于將 CSV 文件數據導入到數據庫中*/
@Slf4j
@Component
@RequiredArgsConstructor
public class CsvImportUtil {private final BehaviorReportMapper behaviorReportMapper;/*** 導入行為分析報告數據* @param csvFilePath CSV 文件路徑* @return 導入的記錄數*/public int importBehaviorReports(String csvFilePath) {int importCount = 0;//1、借助CSVReader讀取CSV文件try (CSVReader reader = new CSVReader(new FileReader(csvFilePath))) {// 2、跳過CSV文件的表頭reader.readNext();//3、逐行解析 CSV 文件,把每行數據轉換為 BehaviorReport 對象。String[] line;List<BehaviorReport> reports = new ArrayList<>();//4、把解析后的 BehaviorReport 對象添加到列表中while ((line = reader.readNext()) != null) {try {BehaviorReport report = parseBehaviorReport(line);reports.add(report);importCount++;//5、每100條記錄批量插入一次if (reports.size() >= 100) {behaviorReportMapper.batchInsert(reports);reports.clear();}} catch (Exception e) {log.error("解析行數據失敗: {}", String.join(",", line), e);}}//6、插入剩余不足100條的記錄if (!reports.isEmpty()) {behaviorReportMapper.batchInsert(reports);}log.info("成功導入 {} 條行為分析報告數據", importCount);} catch (IOException | CsvValidationException e) {log.error("導入 CSV 文件失敗: {}", csvFilePath, e);throw new RuntimeException("導入 CSV 文件失敗", e);}//7、返回導入的記錄數return importCount;}/*** 解析單行 CSV 數據為 BehaviorReport 對象* @param line CSV 行數據* @return BehaviorReport 對象*/private BehaviorReport parseBehaviorReport(String[] line) {DateTimeFormatter dateFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");DateTimeFormatter timestampFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");//創建 BehaviorReport 對象BehaviorReport report = new BehaviorReport();//設置 BehaviorReport 對象的屬性report.setId(Long.parseLong(line[0]));report.setReportType(line[1]);report.setStartDate(LocalDate.parse(line[2], dateFormatter));report.setEndDate(LocalDate.parse(line[3], dateFormatter));report.setTotalUsers(Integer.parseInt(line[4]));report.setActiveUsers(Integer.parseInt(line[5]));report.setTotalVisits(Integer.parseInt(line[6]));report.setAvgDuration(Integer.parseInt(line[7]));report.setBounceRate(new BigDecimal(line[8]));report.setConversionRate(new BigDecimal(line[9]));report.setRetentionRate(new BigDecimal(line[10]));report.setReportData(line[11]);report.setCreatedAt(LocalDateTime.parse(line[12], timestampFormatter));//返回 BehaviorReport 對象return report;}
}
2、創建實體類BehaviorReport
package com.useranalysis.entity;import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.AllArgsConstructor;import java.time.LocalDate;
import java.time.LocalDateTime;
import java.math.BigDecimal;@Data
@NoArgsConstructor
@AllArgsConstructor
public class BehaviorReport {private Long id;private String reportType;private LocalDate startDate;private LocalDate endDate;private Integer totalUsers;private Integer activeUsers;private Integer totalVisits;private Integer avgDuration;private BigDecimal bounceRate;private BigDecimal conversionRate;private BigDecimal retentionRate;private String reportData;private LocalDateTime createdAt;
}
3、創建mapper接口BehaviorReportMapper
package com.useranalysis.mapper;import com.useranalysis.entity.BehaviorReport;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;import java.util.List;@Mapper
public interface BehaviorReportMapper {/*** 批量插入行為分析報告數據* @param reports 報告數據列表* @return 插入的記錄數*/int batchInsert(@Param("reports") List<BehaviorReport> reports);
}
4、創建xml映射BehaviorReportMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.useranalysis.mapper.BehaviorReportMapper"><insert id="batchInsert" parameterType="java.util.List">INSERT INTO behavior_reports (id, report_type, start_date, end_date, total_users, active_users,total_visits, avg_duration, bounce_rate, conversion_rate,retention_rate, report_data, created_at) VALUES <foreach collection="reports" item="report" separator=",">(#{report.id}, #{report.reportType}, #{report.startDate},#{report.endDate}, #{report.totalUsers}, #{report.activeUsers},#{report.totalVisits}, #{report.avgDuration}, #{report.bounceRate},#{report.conversionRate}, #{report.retentionRate}, #{report.reportData},#{report.createdAt})</foreach></insert>
</mapper>
使用方法
1、首先,確保已經創建了數據庫表
2、在需要導入數據的地方調用
@Autowired
private CsvImportUtil csvImportUtil;// 在需要導入數據的地方調用
String csvFilePath = "src/main/resources/static/CSV_test/behavior_reports.csv";
int importCount = csvImportUtil.importBehaviorReports(csvFilePath);
csv直接導入數據庫中
1、創建數據庫表
2、編寫測試類或controller接口
測試類CsvImportTest
package com.useranalysis;import com.useranalysis.util.CsvImportUtil;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.ActiveProfiles;@SpringBootTest
@ActiveProfiles("test")
public class CsvImportTest {@Autowiredprivate CsvImportUtil csvImportUtil;@Testpublic void testImportCsv() {// CSV文件路徑String csvFilePath = "src/main/resources/static/CSV_test/behavior_reports.csv";// 導入數據int importCount = csvImportUtil.importBehaviorReports(csvFilePath);// 打印導入結果System.out.println("成功導入 " + importCount + " 條記錄");}
}
controller接口CsvImportController
package com.useranalysis.controller;import com.useranalysis.util.CsvImportUtil;
import lombok.RequiredArgsConstructor;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;@RestController
@RequestMapping("/api/csv")
@RequiredArgsConstructor
public class CsvImportController {private final CsvImportUtil csvImportUtil;@PostMapping("/import")public ResponseEntity<String> importCsv() {try {String csvFilePath = "src/main/resources/static/CSV_test/behavior_reports.csv";int importCount = csvImportUtil.importBehaviorReports(csvFilePath);return ResponseEntity.ok("成功導入 " + importCount + " 條記錄");} catch (Exception e) {return ResponseEntity.internalServerError().body("導入失敗:" + e.getMessage());}}
}
3、使用命令執行
運行測試類
mvn test -Dtest=CsvImportTest
通過api接口導入
curl -X POST http://localhost:8080/api/csv/import