阅读量:4
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-一次仿真的唯一标识；
备注：使用过程中如有问题，可留言~
