【仿真建模-anylogic】数据源组件

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("");}@Overridepublic <T> void store(String taskId, String containerName, List<T> items, Class<T> clazz) {EasyExcelUtil.write(path + taskId, "data.xlsx", containerName, items, clazz);}@Overridepublic <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("");}@Overridepublic 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);}}@Overridepublic <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;@Overridepublic 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();}}}@Overridepublic <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-一次仿真的唯一标识；

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