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为数据类信息      void store(String taskId, String containerName, List items, Class clazz);       List query(String taskId, String containerName, Class 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  void store(String taskId, String containerName, List items, Class clazz) {         EasyExcelUtil.write(path + taskId, "data.xlsx", containerName, items, clazz);     }      @Override     public  List query(String taskId, String containerName, Class clazz) {         List result = new ArrayList<>();         Path directoryPath = Paths.get(path + taskId);          try (Stream 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  void store(String taskId, String containerName, List items, Class clazz) {         int batchSize = 1000;         int fileNum = (items.size() + batchSize - 1) / batchSize;         for (int index = 0; index < fileNum; index++) {             List subList = items.subList(index * batchSize, Math.min((index + 1) * batchSize, items.size()));             EasyExcelUtil.write(path + taskId, "data" + index + ".xlsx", containerName, subList, clazz);         }     }      @Override     public  List query(String taskId, String containerName, Class 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  void store(String taskId, String containerName, List items, Class clazz) {         Field[] fields = clazz.getDeclaredFields();         Map 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 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 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  List query(String taskId, String containerName, Class clazz) {         List result = new ArrayList<>();         Field[] fields = clazz.getDeclaredFields();         Map 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 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  Map getColumToFieldMap(Field[] fields, Class clazz) {         Map 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-一次仿真的唯一标识；

备注：使用过程中如有问题，可留言~