Nhập dữ liệu lớn
Cấu hình kết nối cơ sở dữ liệu (druid.properties)
Để cấu hình kết nối đến cơ sở dữ liệu MySQL bằng Druid, bạn cần định nghĩa các thuộc tính sau trong tệp druid.properties:
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/spring?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&useSSL=false&allowPublicKeyRetrieval=true&rewriteBatchedStatements=true
username=root
password=1234
initialSize=10
maxActive=50
maxWait=60000
Lớp đối tượng dữ liệu (Entity Class)
Định nghĩa một lớp Java để ánh xạ dữ liệu từ bảng cơ sở dữ liệu. Sử dụng annotation của EasyExcel để chỉ định cách ánh xạ cột.
import com.alibaba.excel.annotation.ExcelProperty;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
/**
* Lớp đại diện cho dữ liệu nhập/xuất Excel.
*/
@Data
@TableName("100w_test") // Tên bảng tương ứng trong cơ sở dữ liệu
public class TestPojo {
@ExcelProperty(value = "ID", index = 0) // Ánh xạ cột "ID" tại vị trí 0
private Integer id;
@ExcelProperty(value = "Tên", index = 1) // Ánh xạ cột "Tên" tại vị trí 1
private String name;
@ExcelProperty(value = "Tuổi", index = 2) // Ánh xạ cột "Tuổi" tại vị trí 2
private Integer age;
@ExcelProperty(value = "Ngày sinh", index = 3) // Ánh xạ cột "Ngày sinh" tại vị trí 3
private Integer bir; // Giả định đây là một giá trị số đại diện cho ngày sinh
}
Tiện ích kết nối JDBC (JDBCDruidUtils)
Một lớp tiện ích để quản lý việc kết nối và đóng các tài nguyên JDBC bằng Druid connection pool.
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
/**
* Tiện ích quản lý kết nối cơ sở dữ liệu sử dụng Druid.
*/
public class JDBCDruidUtils {
private static DataSource dataSource;
// Khối tĩnh để khởi tạo DataSource khi lớp được tải
static {
Properties properties = new Properties();
try (var inputStream = JDBCDruidUtils.class.getClassLoader().getResourceAsStream("druid.properties")) {
if (inputStream == null) {
throw new RuntimeException("Không tìm thấy tệp cấu hình druid.properties");
}
properties.load(inputStream);
dataSource = DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException("Lỗi khi khởi tạo Druid DataSource", e);
}
}
/**
* Lấy một kết nối từ pool.
* @return Connection đối tượng kết nối.
* @throws SQLException Nếu có lỗi khi lấy kết nối.
*/
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();
}
/**
* Đóng tài nguyên Statement và Connection.
* @param connection Đối tượng Connection cần đóng.
* @param statement Đối tượng Statement cần đóng.
*/
public static void close(Connection connection, Statement statement) {
closeStatement(statement);
closeConnection(connection);
}
/**
* Đóng tài nguyên ResultSet, Statement và Connection.
* @param connection Đối tượng Connection cần đóng.
* @param statement Đối tượng Statement cần đóng.
* @param resultSet Đối tượng ResultSet cần đóng.
*/
public static void close(Connection connection, Statement statement, ResultSet resultSet) {
closeResultSet(resultSet);
close(connection, statement);
}
private static void closeConnection(Connection connection) {
if (connection != null) {
try {
if (!connection.isClosed()) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
private static void closeStatement(Statement statement) {
if (statement != null) {
try {
if (!statement.isClosed()) {
statement.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
private static void closeResultSet(ResultSet resultSet) {
if (resultSet != null) {
try {
if (!resultSet.isClosed()) {
resultSet.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* Lấy đối tượng DataSource.
* @return DataSource đối tượng DataSource.
*/
public static DataSource getDataSource() {
return dataSource;
}
}
Lớp lắng nghe sự kiện EasyExcel (EasyExcelGeneralDataListener)
Lớp này xử lý từng hàng dữ liệu đọc được từ tệp Excel và lưu trữ theo lô vào cơ sở dữ liệu.
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
/**
* Listener để xử lý dữ liệu đọc từ Excel bằng EasyExcel.
* Lớp này nhận dữ liệu dưới dạng Map với key là chỉ số cột.
*/
public class EasyExcelGeneralDataListener extends AnalysisEventListener> {
/**
* Service hoặc Mapper để thực hiện nghiệp vụ (ví dụ: lưu vào DB).
*/
private IDataImportService dataImportService; // Sử dụng interface để linh hoạt
/**
* Danh sách tạm thời để lưu trữ dữ liệu đọc được.
*/
private final List<Map<Integer, String>> batchData = new ArrayList<>();
// Kích thước lô để lưu vào DB
private static final int BATCH_SIZE = 500000;
/**
* Constructor mặc định.
*/
public EasyExcelGeneralDataListener() {
}
/**
* Constructor với service để xử lý nghiệp vụ.
* @param dataImportService Service xử lý nghiệp vụ nhập dữ liệu.
*/
public EasyExcelGeneralDataListener(IDataImportService dataImportService) {
this.dataImportService = dataImportService;
}
/**
* Phương thức được gọi mỗi khi EasyExcel đọc được một hàng dữ liệu.
* @param data Dữ liệu của hàng hiện tại dưới dạng Map.
* @param context Ngữ cảnh phân tích.
*/
@Override
public void invoke(Map<Integer, String> data, AnalysisContext context) {
batchData.add(data);
// Kiểm tra nếu đã đủ kích thước lô thì lưu vào DB
if (batchData.size() >= BATCH_SIZE) {
saveBatchToDatabase();
}
}
/**
* Phương thức được gọi sau khi toàn bộ tệp Excel đã được phân tích.
* Đảm bảo lưu nốt dữ liệu còn lại trong batchData.
* @param context Ngữ cảnh phân tích.
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// Lưu nốt dữ liệu còn lại nếu có
if (!batchData.isEmpty()) {
saveBatchToDatabase();
}
}
/**
* Thực hiện lưu dữ liệu trong batchData vào cơ sở dữ liệu.
*/
private void saveBatchToDatabase() {
if (dataImportService != null && !batchData.isEmpty()) {
dataImportService.importBatchToDB(batchData);
// Xóa batchData để giải phóng bộ nhớ
batchData.clear();
}
}
// Định nghĩa một interface giả định cho service
public interface IDataImportService {
void importBatchToDB(List<Map<Integer, String>> dataList);
}
}
Thực thi lưu trữ vào cơ sở dữ liệu
Chi tiết về cách xử lý lô dữ liệu và lưu vào cơ sở dữ liệu bằng JDBC với cơ chế transaction.
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* Service thực hiện nghiệp vụ nhập dữ liệu từ Excel vào DB.
*/
public class DataImportService { // Đổi tên lớp cho rõ ràng hơn
/**
* Nhập một lô dữ liệu từ Excel vào bảng '100w_test'.
* Sử dụng PreparedStatemnt và addBatch để tối ưu hiệu suất.
* Transaction được quản lý để đảm bảo tính toàn vẹn dữ liệu.
*
* @param dataList Danh sách các bản ghi dữ liệu cần nhập.
* @return Map chứa kết quả thao tác (thành công/thất bại).
*/
public Map<String, Object> importBatchToDB(List<Map<Integer, String>> dataList) {
Map<String, Object> result = new HashMap<>();
if (dataList == null || dataList.isEmpty()) {
result.put("status", "warning");
result.put("message", "Danh sách dữ liệu rỗng, không có gì để nhập.");
return result;
}
Connection connection = null;
PreparedStatement preparedStatement = null;
long startTime = System.currentTimeMillis();
System.out.println("Bắt đầu nhập " + dataList.size() + " bản ghi vào cơ sở dữ liệu.");
try {
connection = JDBCDruidUtils.getConnection();
// Tắt chế độ tự động commit để quản lý transaction thủ công
connection.setAutoCommit(false);
// Câu lệnh SQL với các placeholder
String sql = "INSERT INTO 100w_test (id, name, age, bir) VALUES (?, ?, ?, ?)";
preparedStatement = connection.prepareStatement(sql);
// Duyệt qua danh sách dữ liệu và thêm vào batch
for (Map<Integer, String> record : dataList) {
// Đặt giá trị cho các placeholder. Cần xử lý chuyển đổi kiểu dữ liệu nếu cần.
// Giả định các giá trị trong Map là String và có thể chuyển đổi an toàn.
preparedStatement.setString(1, record.get(0)); // id
preparedStatement.setString(2, record.get(1)); // name
preparedStatement.setString(3, record.get(2)); // age
preparedStatement.setString(4, record.get(3)); // bir
// Thêm câu lệnh vào batch
preparedStatement.addBatch();
}
// Thực thi toàn bộ batch
preparedStatement.executeBatch();
// Commit transaction nếu mọi thứ thành công
connection.commit();
long endTime = System.currentTimeMillis();
System.out.println("Nhập thành công " + dataList.size() + " bản ghi.");
System.out.println("Thời gian thực hiện: " + (endTime - startTime) + " ms");
result.put("status", "success");
result.put("message", "Nhập dữ liệu thành công.");
} catch (SQLException e) {
result.put("status", "error");
result.put("message", "Lỗi khi nhập dữ liệu vào cơ sở dữ liệu.");
e.printStackTrace();
// Rollback transaction nếu có lỗi
if (connection != null) {
try {
connection.rollback();
System.err.println("Transaction đã được rollback.");
} catch (SQLException ex) {
ex.printStackTrace();
}
}
} finally {
// Đảm bảo đóng tài nguyên
JDBCDruidUtils.close(connection, preparedStatement);
}
return result;
}
}
Xuất dữ liệu lớn
Controller xử lý yêu cầu xuất dữ liệu
Controller này nhận yêu cầu HTTP GET, truy vấn dữ liệu và ghi ra tệp Excel theo từng phần.
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.enums.ExcelTypeEnum;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.metadata.Table;
import com.alibaba.excel.support.ExcelType;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.util.CollectionUtils;
import org.springframework.web.bind.annotation.GetMapping;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Properties;
/**
* Controller để xử lý yêu cầu xuất dữ liệu Excel.
*/
@Controller
public class ExcelExportController {
// Giả định có một Mapper hoặc Repository để truy vấn dữ liệu
@Autowired
private TestPojoMapper testPojoMapper; // Thay thế bằng tên Mapper thực tế của bạn
private static final int SHEET_DATA_ROWS = 1000000; // Số dòng dữ liệu mỗi Sheet
private static final int WRITE_DATA_ROWS = 200000; // Số dòng dữ liệu ghi mỗi lần
@GetMapping("/export-large-data")
public void exportLargeData(HttpServletResponse response) {
OutputStream outputStream = null;
ExcelWriter writer = null;
try {
// Cấu hình response cho việc tải tệp
response.setContentType("multipart/form-data");
response.setCharacterEncoding("utf-8");
String fileName = new String("large_data_export".getBytes(StandardCharsets.UTF_8), "ISO-8859-1");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
outputStream = response.getOutputStream();
writer = new ExcelWriter(outputStream, ExcelTypeEnum.XLSX); // Sử dụng ExcelWriter
// Định nghĩa tiêu đề bảng
Table titleTable = new Table();
List<List<String>> titles = new ArrayList<>();
titles.add(Arrays.asList("ID", "Tên", "Tuổi", "Ngày sinh"));
titleTable.setHead(titles);
// Lấy tổng số bản ghi
Integer totalCount = testPojoMapper.selectCount(null); // Giả định có phương thức này
// Tính toán số lượng Sheet và số lần ghi cho mỗi Sheet
int sheetNum = (totalCount + SHEET_DATA_ROWS - 1) / SHEET_DATA_ROWS;
int oneSheetWriteCount = (SHEET_DATA_ROWS + WRITE_DATA_ROWS - 1) / WRITE_DATA_ROWS;
int lastSheetWriteCount = (totalCount % SHEET_DATA_ROWS == 0) ? oneSheetWriteCount :
(totalCount % SHEET_DATA_ROWS + WRITE_DATA_ROWS - 1) / WRITE_DATA_ROWS;
long exportStartTime = System.currentTimeMillis();
System.out.println("Bắt đầu xuất dữ liệu. Tổng số bản ghi: " + totalCount);
List<List<String>> dataRows = new ArrayList<>();
// Vòng lặp ngoài: tạo các Sheet
for (int i = 0; i < sheetNum; i++) {
Sheet sheet = new Sheet(i + 1, i, TestPojo.class); // Tên sheet có thể tùy chỉnh
sheet.setSheetName("Dữ liệu Phần " + (i + 1));
// Xác định số lần ghi cho sheet hiện tại
int currentSheetWriteCount = (i == sheetNum - 1) ? lastSheetWriteCount : oneSheetWriteCount;
// Vòng lặp trong: ghi dữ liệu vào Sheet hiện tại theo từng lô
for (int j = 0; j < currentSheetWriteCount; j++) {
dataRows.clear(); // Xóa dữ liệu cũ để tái sử dụng list
// Tính toán chỉ số trang (page) và kích thước trang (pageSize)
int pageNumber = i * oneSheetWriteCount + j + 1; // Số trang tổng thể
int pageSize = WRITE_DATA_ROWS;
// Sử dụng PageHelper hoặc Mybatis-Plus Paging
Page<TestPojo> page = new Page<>(pageNumber, pageSize);
testPojoMapper.selectPage(page, null); // Truy vấn dữ liệu phân trang
List<TestPojo> records = page.getRecords();
if (!CollectionUtils.isEmpty(records)) {
// Chuyển đổi dữ liệu từ List<TestPojo> sang List<List<String>>
for (TestPojo item : records) {
dataRows.add(Arrays.asList(
String.valueOf(item.getId()),
item.getName(),
String.valueOf(item.getAge()),
String.valueOf(item.getBir())
));
}
}
// Ghi dữ liệu vào sheet
writer.write(dataRows, sheet, titleTable);
}
}
// Hoàn thành ghi và flush
writer.finish();
outputStream.flush();
long exportEndTime = System.currentTimeMillis();
System.out.println("Xuất dữ liệu hoàn tất. Thời gian: " + (exportEndTime - exportStartTime) + " ms");
} catch (IOException e) {
System.err.println("Lỗi I/O khi xuất dữ liệu Excel: " + e.getMessage());
e.printStackTrace();
} finally {
// Đóng các tài nguyên
if (writer != null) {
writer.close();
}
if (outputStream != null) {
try {
outputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
// Interface giả định cho Mybatis Mapper
public interface TestPojoMapper {
Integer selectCount(Object queryWrapper);
void selectPage(Page<TestPojo> page, Object queryWrapper);
}
}