Author：赵志乾
Date：2024-07-16
Declaration：All Right Reserved！！！1. 简介
仿真模型依赖的数据源通常有Excel文件、MySQL数据库两种；针对小数量、大数据量以及是否允许外部依赖等场景设计了一套通用数据源组件；该套数据源组件支持3种数据源：
- 小数据量且无外部依赖：ExcelDataSource
- 大数据量且无外部依赖：MultiExcelDataSource
- 允许外部依赖：MySqlDataSource
数据存取操作均通过接口IDataSource进行，依据实际场景不同，切换不同数据源实现即可；
2. 抽象数据源接口
public interface IDataSource {
// taskId为一次仿真的唯一标识
// containerName为数据库表名或者Sheet页名称
// items为要存储的数据
// clazz为数据类信息
<T> void store(String taskId, String containerName, List<T> items, Class<T> clazz);
<T> List<T> query(String taskId, String containerName, Class<T> clazz);
}3. ExcelDataSource
ExcelDataSource针对于小数据量场景，单个Excel即可存储所有数据；
public class ExcelDataSource implements IDataSource {
// excel文件路径
private final String path;
public ExcelDataSource(String path) {
this.path = path;
}
public ExcelDataSource() {
this("");
}
@Override
public <T> void store(String taskId, String containerName, List<T> items, Class<T> clazz) {
EasyExcelUtil.write(path + taskId, "data.xlsx", containerName, items, clazz);
}
@Override
public <T> List<T> query(String taskId, String containerName, Class<T> clazz) {
List<T> result = new ArrayList<>();
Path directoryPath = Paths.get(path + taskId);
try (Stream<Path> paths = Files.list(directoryPath)) {
paths.forEach(file -> {
String fileName = file.getFileName().getFileName().toString();
if(fileName.endsWith("xlsx")){
result.addAll(EasyExcelUtil.read(path + taskId, fileName, containerName, clazz));
}
});
} catch (IOException e) {
throw new RuntimeException(e);
}
return result;
}
}4. MultiExcelDataSource
MultiExcelDataSource针对大数据量而又不希望引入外部依赖的场景，其输入输出支持多Excel文件，以文件名数字后缀进行数据的切分；
public class MultiExcelDataSource implements IDataSource {
private final String path;
private final IDataSource excelDataSource;
public MultiExcelDataSource(String path) {
this.path = path;
excelDataSource = new ExcelDataSource(path);
}
public MultiExcelDataSource() {
this("");
}
@Override
public synchronized <T> void store(String taskId, String containerName, List<T> items, Class<T> clazz) {
int batchSize = 1000;
int fileNum = (items.size() + batchSize - 1) / batchSize;
for (int index = 0; index < fileNum; index++) {
List<T> subList = items.subList(index * batchSize, Math.min((index + 1) * batchSize, items.size()));
EasyExcelUtil.write(path + taskId, "data" + index + ".xlsx", containerName, subList, clazz);
}
}
@Override
public <T> List<T> query(String taskId, String containerName, Class<T> clazz) {
return excelDataSource.query(taskId, containerName, clazz);
}
}5. MySqlDataSource
MySqlDataSource适用于大数量场景；
@Data
@AllArgsConstructor
public class MySqlDataSource implements IDataSource {
private final String url;
private final String userName;
private final String password;
private final static int batchSize = 500;
@Override
public synchronized <T> void store(String taskId, String containerName, List<T> items, Class<T> clazz) {
Field[] fields = clazz.getDeclaredFields();
Map<String, Field> columnToFieldMap = getColumToFieldMap(fields, clazz);
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = DriverManager.getConnection(url, userName, password);
connection.setAutoCommit(false);
StringBuilder sql = new StringBuilder("INSERT INTO ");
sql.append(containerName).append("(task_id,");
List<String> columns = new ArrayList<>(columnToFieldMap.keySet());
for (int index = 0; index < columns.size(); index++) {
sql.append(columns.get(index)).append(",");
}
sql.setCharAt(sql.length() - 1, ')');
sql.append("VALUES(?,");
for (int index = 0; index < columns.size(); index++) {
sql.append("?,");
}
sql.setCharAt(sql.length() - 1, ')');
preparedStatement = connection.prepareStatement(sql.toString());
int totalBatch = (items.size() + batchSize - 1) / batchSize;
for (int index = 0; index < totalBatch; index++) {
preparedStatement.setString(1, taskId);
List<T> subList = items.subList(index * batchSize, Math.min((index + 1) * batchSize, items.size()));
for (int itemIndex = 0; itemIndex < subList.size(); itemIndex++) {
T item = subList.get(itemIndex);
for (int columnIndex = 0; columnIndex < columns.size(); columnIndex++) {
String column = columns.get(columnIndex);
Field field = columnToFieldMap.get(column);
Class columnClazz = field.getType();
if (columnClazz == String.class) {
preparedStatement.setString(columnIndex + 2, (String) field.get(item));
} else if (columnClazz == Integer.class) {
preparedStatement.setInt(columnIndex + 2, (Integer) field.get(item));
} else if (columnClazz == Long.class) {
preparedStatement.setLong(columnIndex + 2, (Long) field.get(item));
} else if (columnClazz == Float.class) {
preparedStatement.setFloat(columnIndex + 2, (Float) field.get(item));
} else if (columnClazz == Double.class) {
preparedStatement.setDouble(columnIndex + 2, (Double) field.get(item));
} else if (columnClazz == DateTime.class) {
preparedStatement.setTimestamp(columnIndex + 2, new Timestamp(((DateTime) field.get(item)).getMillis()));
} else {
throw new RuntimeException("类型不支持!type=" + field.getType().getTypeName());
}
}
preparedStatement.addBatch();
}
int[] updateCounts = preparedStatement.executeBatch();
for (int count : updateCounts) {
if (count < 1) {
throw new SQLException("数据库操作失败!");
}
}
connection.commit();
}
} catch (SQLException | IllegalAccessException e) {
e.printStackTrace();
if (connection != null) {
try {
connection.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
} finally {
try {
if (preparedStatement != null) preparedStatement.close();
if (connection != null) connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
@Override
public <T> List<T> query(String taskId, String containerName, Class<T> clazz) {
List<T> result = new ArrayList<>();
Field[] fields = clazz.getDeclaredFields();
Map<String, Field> columnToFieldMap = getColumToFieldMap(fields, clazz);
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = DriverManager.getConnection(url, userName, password);
StringBuilder sql = new StringBuilder("SELECT COUNT(0) FROM ");
sql.append(containerName).append(" WHERE task_id='").append(taskId).append("'");
preparedStatement = connection.prepareStatement(sql.toString());
resultSet = preparedStatement.executeQuery();
int total = 0;
if (resultSet.next()) {
total = resultSet.getInt(1);
}
resultSet.close();
preparedStatement.close();
preparedStatement = null;
resultSet = null;
int totalBatch = (total + batchSize - 1) / batchSize;
long id = 0l;
List<String> columns = new ArrayList<>(columnToFieldMap.keySet());
sql = new StringBuilder("SELECT id,");
for (int index = 0; index < columns.size(); index++) {
sql.append(columns.get(index)).append(",");
}
sql.setCharAt(sql.length() - 1, ' ');
sql.append(" FROM ").append(containerName)
.append(" WHERE task_id='").append(taskId).append("' AND id>").append(" ? ")
.append(" order by id asc")
.append(" limit ").append(batchSize);
System.out.println(sql.toString());
preparedStatement = connection.prepareStatement(sql.toString());
for (int index = 0; index < totalBatch; index++) {
preparedStatement.setLong(1, id);
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
T item = clazz.getConstructor().newInstance();
id = resultSet.getLong(1);
for (int columnIndex = 0; columnIndex < columns.size(); columnIndex++) {
Field field = columnToFieldMap.get(columns.get(columnIndex));
Class columnClazz = field.getType();
if (columnClazz == String.class) {
field.set(item, resultSet.getString(columnIndex + 2));
} else if (columnClazz == Integer.class) {
field.set(item, resultSet.getInt(columnIndex + 2));
} else if (columnClazz == Long.class) {
field.set(item, resultSet.getLong(columnIndex + 2));
} else if (columnClazz == Float.class) {
field.set(item, resultSet.getFloat(columnIndex + 2));
} else if (columnClazz == Double.class) {
field.set(item, resultSet.getDouble(columnIndex + 2));
} else if (columnClazz == DateTime.class) {
field.set(item, new DateTime(resultSet.getTimestamp(columnIndex + 2).getTime()));
} else {
throw new RuntimeException("类型不支持!type=" + field.getType().getTypeName());
}
}
result.add(item);
}
resultSet.close();
resultSet = null;
}
} catch (SQLException | IllegalAccessException | NoSuchMethodException | InvocationTargetException |
InstantiationException e) {
e.printStackTrace();
} finally {
try {
if (preparedStatement != null) preparedStatement.close();
if (connection != null) connection.close();
if (resultSet != null) {
resultSet.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
return result;
}
private <T> Map<String, Field> getColumToFieldMap(Field[] fields, Class<T> clazz) {
Map<String, Field> columnToFieldMap = new HashMap<>();
for (Field field : fields) {
field.setAccessible(true);
ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
if (excelProperty != null) {
columnToFieldMap.put(toSnakeCase(field.getName()), field);
}
}
return columnToFieldMap;
}
private String toSnakeCase(String camelCase) {
if (camelCase == null || camelCase.isEmpty()) {
return camelCase;
}
StringBuilder snakeCase = new StringBuilder();
boolean capitalizeNext = false;
if (!Character.isUpperCase(camelCase.charAt(0))) {
snakeCase.append(camelCase.charAt(0));
} else {
capitalizeNext = true;
}
for (int i = 1; i < camelCase.length(); i++) {
char c = camelCase.charAt(i);
if (Character.isUpperCase(c) && (!Character.isUpperCase(camelCase.charAt(i - 1)) || capitalizeNext)) {
snakeCase.append('_');
c = Character.toLowerCase(c);
capitalizeNext = false;
}
snakeCase.append(c);
}
return snakeCase.toString();
}
}6. 使用说明
- 存取的数据结构仅支持非嵌套结构，即一个数据类对应一张数据库表或者一个sheet页；
- 类字段命名需采用小驼峰格式（如： startTime）且使用ExcelProperty注解进行标注，数据库字段命名需采用蛇形格式（如：start_time）;
- 数据库表必有字段：id-自增主键、task_id-一次仿真的唯一标识；
备注：使用过程中如有问题，可留言~