【仿真建模-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("");
    }

    @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-一次仿真的唯一标识;

备注:使用过程中如有问题,可留言~

上一篇:stm32学习:(寄存器3)系统架构


下一篇:MD5/AES/RSA 替换为国密 SM3/SM4/SM2 实现前后端交互