package com.kcim.service.impl;

import cn.hutool.core.date.DatePattern;
import cn.hutool.core.date.DateTime;
import cn.hutool.core.date.DateUtil;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.kcim.common.constants.Constant;
import com.kcim.common.constants.NumberConstant;
import com.kcim.common.exception.CostException;
import com.kcim.common.file.MinioConfig;
import com.kcim.common.file.MinioFileUtil;
import com.kcim.common.util.DateUtils;
import com.kcim.common.util.PageUtils;
import com.kcim.common.util.UserContext;
import com.kcim.dao.model.KpiComputeImport;
import com.kcim.dao.repository.KpiComputeImportRepository;
import com.kcim.service.CenterService;
import com.kcim.service.KpiComputeImportService;
import com.kcim.vo.CommonTitleVo;
import com.kcim.vo.DictDataVo;
import com.kcim.vo.SheetImportResultVO;
import com.kcim.vo.UserInfoVO;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.io.FileUtils;
import org.apache.ibatis.jdbc.SqlRunner;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.nio.file.Files;
import java.nio.file.StandardCopyOption;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @program: CostAccount
 * @description:
 * @author: Wang.YS
 * @create: 2024-06-03 13:58
 **/
@Service("kpiComputeImportService")
@Slf4j
public class KpiComputeImportServiceImpl implements KpiComputeImportService {
    private final KpiComputeImportRepository repository;

    private final MinioConfig minioConfig;

    private final MinioFileUtil minioFileUtil;

    private final CenterService centerService;

    @Value("${spring.datasource.driver-class-name}")
    private String driver;
    @Value("${spring.datasource.url}")
    private String url;
    @Value("${spring.datasource.username}")
    private String username;
    @Value("${spring.datasource.password}")
    private String password;

    public KpiComputeImportServiceImpl(KpiComputeImportRepository repository, MinioConfig minioConfig, MinioFileUtil minioFileUtil, CenterService centerService) {
        this.repository = repository;
        this.minioConfig = minioConfig;
        this.minioFileUtil = minioFileUtil;
        this.centerService = centerService;
    }


    /**
     * 查询导入列表
     *
     * @param current     当前页
     * @param pageSize    页容量
     * @param computeDate 核算年月
     * @param tableName   表名
     * @return 导入列表
     */
    @Override
    public Object getList(Integer current, Integer pageSize, String computeDate, String tableName) {
        List<UserInfoVO> centerEmployee = centerService.getCenterUserInfo(null);
        Map<Long, String> userName = centerEmployee.stream().collect(Collectors.toMap(UserInfoVO::getId, UserInfoVO::getName, (a, b) -> b));
        DictDataVo dict = centerService.getDict(Constant.SPECIAL_SHEET_MAP);
        if (Objects.isNull(dict)) {
            throw new CostException("未找到特殊项目导入字典");
        }
        List<DictDataVo> dataVoList = dict.getDataVoList();
        if (CollectionUtils.isEmpty(dataVoList)) {
            throw new CostException("未找到特殊项目导入字典");
        }
        Map<String, String> dictionaryCodeNameMap = dataVoList.stream().collect(Collectors.toMap(DictDataVo::getCode, DictDataVo::getName, (a, b) -> b));
        Page<KpiComputeImport> page = repository.getPage(current, pageSize, computeDate, tableName);
        List<KpiComputeImport> list = page.getRecords();
        if (CollectionUtils.isEmpty(list)) {
            return new PageUtils(new ArrayList<>(), NumberConstant.ZERO, pageSize, current);
        }
        for (KpiComputeImport computeImport : list) {
            computeImport.setCreateUserName(userName.get(Long.valueOf(computeImport.getCreateUser())));
            computeImport.setTableNameDisplay(dictionaryCodeNameMap.get(computeImport.getTableName()));
        }
        return new PageUtils(list, Math.toIntExact(page.getTotal()), pageSize, current);
    }

    /**
     * @return
     */
    @Override
    public Object getTableList() {
        DictDataVo dict = centerService.getDict(Constant.SPECIAL_SHEET_MAP);
        if (Objects.isNull(dict)) {
            throw new CostException("未找到特殊项目导入字典");
        }
        List<DictDataVo> dataVoList = dict.getDataVoList();
        if (CollectionUtils.isEmpty(dataVoList)) {
            throw new CostException("未找到特殊项目导入字典");
        }
        Map<String, String> dictionaryCodeNameMap = dataVoList.stream().collect(Collectors.toMap(DictDataVo::getCode, DictDataVo::getName, (a, b) -> b));
        List<CommonTitleVo> list = new ArrayList<>();
        if (!CollectionUtils.isEmpty(dictionaryCodeNameMap)) {
            list = dictionaryCodeNameMap.keySet().stream().map(s -> new CommonTitleVo(s, dictionaryCodeNameMap.get(s))).collect(Collectors.toList());
        }
        return list;
    }

    /**
     * 导入数据
     *
     * @param computeDate 核算年月
     * @param tableName   表名
     * @param file        导入文件
     */
    @Override
    @Transactional(rollbackFor = Throwable.class, propagation = Propagation.REQUIRED)
    public void importData(String computeDate, String tableName, MultipartFile file) {
        //excel 数据导入
        String fileName = file.getOriginalFilename();
        //导入文件上传
        String uploadFileUrl = uploadFile(file, fileName);
        //导入记录添加
        KpiComputeImport kpiComputeImport = new KpiComputeImport();
        kpiComputeImport.setComputeDate(computeDate);
        kpiComputeImport.setImportStatus(NumberConstant.ZERO);
        kpiComputeImport.setCreateTime(new Date());
        kpiComputeImport.setCreateUser(String.valueOf(UserContext.getCurrentUser().getId()));
        kpiComputeImport.setUrl(uploadFileUrl);
        kpiComputeImport.setTableName(tableName);
        kpiComputeImport.setFileName(fileName);
        kpiComputeImport.setHospId(UserContext.getCurrentLoginHospId());
        kpiComputeImport.setDelFlag(NumberConstant.ZERO);
        repository.save(kpiComputeImport);
        insertExcelData(kpiComputeImport.getId(), tableName, file, fileName, computeDate);
    }

    private void insertExcelData(Integer id, String tableName, MultipartFile file, String fileName, String computeDate) {
        try {
            assert fileName != null;
            File file1 = new File(fileName);
            FileUtils.copyInputStreamToFile(file.getInputStream(), file1);
            String excelSql = readExcelSql(file1, tableName, id, computeDate);
            SqlRunner sqlRunner = new SqlRunner(getConnection());
            sqlRunner.insert(excelSql);

            //更新新增人和时间
            String updateSql = "update " + "`" + tableName + "`" + "set" +
                    "`create_user` = '" + UserContext.getCurrentUser().getId() + "' , `create_time` = '" + DateUtils.formatDate2String(new Date()) +
                    "' where `compute_date` = '" + computeDate + "' and `hosp_id` = " + UserContext.getCurrentLoginHospId() +
                    ";";
            sqlRunner.run(updateSql);
        } catch (IOException | SQLException | ClassNotFoundException e) {
            throw new RuntimeException(e);
        }


    }

    private String uploadFile(MultipartFile file, String fileName) {
        DateTime date = DateUtil.date();
        int month = DateUtil.month(date) + 1;
        int year = DateUtil.year(date);
        int day = DateUtil.dayOfMonth(date);
        Long hospId = UserContext.getCurrentLoginHospId();
        String fileUrl = "";
        try {
            String format = DateUtil.format(date, DatePattern.PURE_DATETIME_PATTERN);
            String originalFilename = format + fileName;
            String dataDirectory = minioConfig.getBucketName();
            String uploadFileName = "upload" + "/" + hospId + "/" + year + "/" + month + "/" + day + "/" + originalFilename;
            InputStream inputStream = file.getInputStream();
            minioFileUtil.putObject(dataDirectory, uploadFileName, inputStream);
            fileUrl = minioFileUtil.getObjectUrl(dataDirectory, uploadFileName);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        return fileUrl;
    }

    /**
     * 作废导入数据
     *
     * @param id 记录id
     */
    @Override
    @Transactional(rollbackFor = Throwable.class, propagation = Propagation.REQUIRED)
    public void removeImport(Integer id) {
        KpiComputeImport kpiComputeImport = repository.getById(id);
        if (Objects.nonNull(kpiComputeImport)) {
            //更新表信息
//            String computeDate = kpiComputeImport.getComputeDate();
            String tableName = kpiComputeImport.getTableName();
            Long hospId = kpiComputeImport.getHospId();
            // TODO: 2023/7/24  导入的数据可能不是这个月的，用记录id 进行删除，有问题再看
            StringBuilder delSql = new StringBuilder().append("update " + "`").append(tableName).append("`").append("set")
                    .append("`del_flag` = 1 , `update_user` = '").append(UserContext.getCurrentUser().getId())
                    .append("' , `update_time` = '").append(DateUtils.formatDate2String(new Date())).append("' where  `hosp_id` = ").append(hospId)
                    .append(" and `import_id` = ").append(id).append(";");
            try {
                log.info("执行的sql语句：" + delSql);
                SqlRunner sqlRunner = new SqlRunner(getConnection());
                sqlRunner.run(delSql.toString());
            } catch (ClassNotFoundException | SQLException e) {
                throw new RuntimeException("删除导入数据错误" + e);
            }
            //更新 记录表信息
            kpiComputeImport.setImportStatus(NumberConstant.ONE);
            kpiComputeImport.setUpdateUser(String.valueOf(UserContext.getCurrentUser().getId()));
            kpiComputeImport.setUpdateTime(new Date());
            repository.updateById(kpiComputeImport);
        }
    }


    @Override
    @Transactional(rollbackFor = Throwable.class, propagation = Propagation.REQUIRED)
    public List<SheetImportResultVO> importMultipleSheets(String computeDate, MultipartFile file) {
        List<CommonTitleVo> tableList = (List<CommonTitleVo>) getTableList();
        String fileName = file.getOriginalFilename();
        String uploadFileUrl = uploadFile(file, fileName);
        List<SheetImportResultVO> sheetResults = new ArrayList<>();
        try {
            // 使用系统临时目录显式创建临时文件
            File tempDir = new File(System.getProperty("java.io.tmpdir"));
            File tempFile = new File(tempDir, "excel-" + UUID.randomUUID() + ".tmp");

            // 确保文件存在并正确关闭流
            try (InputStream inputStream = file.getInputStream()) {
                Files.copy(inputStream, tempFile.toPath(), StandardCopyOption.REPLACE_EXISTING);
            }

            try (Workbook workbook = WorkbookFactory.create(tempFile)) {
                for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                    Sheet sheet = workbook.getSheetAt(i);
                    String sheetName = sheet.getSheetName();

                    SheetImportResultVO resultItem = new SheetImportResultVO();
                    resultItem.setSheetName(sheetName);
                    resultItem.setSheetIndex(i);

                    Optional<CommonTitleVo> mapping = tableList.stream()
                            .filter(t -> t.getName().equals(sheetName))
                            .findFirst();

                    if (!mapping.isPresent()) {
                        log.warn("未找到sheet页 '{%s}' 对应的数据表配置，跳过导入", sheetName);
                        resultItem.setSuccess(false);
                        resultItem.setErrorMessage("未找到对应的数据库表配置");
                        sheetResults.add(resultItem);
                        continue;
                    }

                    String tableName = mapping.get().getCode();
                    resultItem.setTableName(tableName);

                    try {
                        KpiComputeImport kpiComputeImport = new KpiComputeImport();
                        kpiComputeImport.setComputeDate(computeDate);
                        kpiComputeImport.setImportStatus(NumberConstant.ZERO);
                        kpiComputeImport.setCreateTime(new Date());
                        kpiComputeImport.setCreateUser(String.valueOf(UserContext.getCurrentUser().getId()));
                        kpiComputeImport.setUrl(uploadFileUrl);
                        kpiComputeImport.setTableName(tableName);
                        kpiComputeImport.setFileName(String.format("%s_%s", fileName, sheetName));
                        kpiComputeImport.setHospId(UserContext.getCurrentLoginHospId());
                        kpiComputeImport.setDelFlag(NumberConstant.ZERO);

                        repository.save(kpiComputeImport);

                        insertExcelDataForSheet(kpiComputeImport.getId(), tableName, file, fileName, computeDate, sheet);

                        resultItem.setSuccess(true);
                        resultItem.setImportId(kpiComputeImport.getId());
                        resultItem.setImportTime(kpiComputeImport.getCreateTime());

                    } catch (Exception e) {
                        log.error(String.format("sheet页 '%s' 导入失败: %s", sheetName, e.getMessage()));
                        resultItem.setSuccess(false);
                        resultItem.setErrorMessage("导入失败: " + e.getMessage());
                    }
                    sheetResults.add(resultItem);
                }
            } finally {
                // 删除临时文件
                if (tempFile.exists()) {
                    tempFile.delete();
                }
            }
        } catch (Exception e) {
            throw new RuntimeException("导入发生异常: " + e.getMessage(), e);
        }
        return sheetResults;
    }


    @SneakyThrows
    private void insertExcelDataForSheet(Integer id, String tableName, MultipartFile file, String fileName, String computeDate, Sheet sheet) {
        // 将MultipartFile转换为InputStream
        InputStream inputStream = file.getInputStream();

        // 这里假设有一个方法可以从给定的InputStream和sheet对象中读取SQL语句
        List<String> excelSql = readExcelSqlFromSheet(inputStream, sheet, tableName, id, computeDate);

        // 执行SQL插入操作
        SqlRunner sqlRunner = new SqlRunner(getConnection());
        for (String sql : excelSql) {
            sqlRunner.insert(sql);
        }

        // 更新新增人和时间
        String updateSql = "UPDATE `" + tableName + "` SET " +
                "`create_user` = '" + UserContext.getCurrentUser().getId() + "', " +
                "`create_time` = '" + DateUtils.formatDate2String(new Date()) + "' " +
                "WHERE `import_id` = '" + id + "';";
        sqlRunner.run(updateSql);
    }

    private static final int BATCH_SIZE = 500; // 每批次插入的记录数

    private List<String> readExcelSqlFromSheet(InputStream inputStream, Sheet sheet, String tableName, Integer id, String computeDate) throws IOException {
        List<String> sqlList = new ArrayList<>();
        boolean firstRow = true;
        Row headerRow = null;
        DataFormatter dataFormatter = new DataFormatter();
        int rowCount = 0;
        boolean hasComputeDateField = false;

        // 第一次遍历获取字段信息并判断是否有 compute_date 字段
        for (Row row : sheet) {
            if (rowCount == 0) {
                headerRow = row;
                // 检查字段中是否包含 compute_date
                for (int i = 0; i < headerRow.getLastCellNum(); i++) {
                    Cell headerCell = headerRow.getCell(i);
                    if (headerCell != null && "compute_date".equals(headerCell.getStringCellValue())) {
                        hasComputeDateField = true;
                        break;
                    }
                }
                rowCount++;
                continue;
            }
            break; // 只需要读取第一行判断字段
        }

        // 第二次遍历处理数据行
        StringBuilder currentBatchValues = new StringBuilder();
        int batchCount = 0;
        // 新增变量用于记录实际数据行数
        int actualRowCount = 0;

        for (Row row : sheet) {
            if (actualRowCount < 2) {
                actualRowCount++;
                continue; // 跳过前两行（标题行 + 说明行）
            }

            StringBuilder valueSb = new StringBuilder();
            valueSb.append("(");

            for (int i = 0; i < row.getLastCellNum(); i++) {
                if (i > 0) {
                    valueSb.append(", ");
                }
                Cell cell = row.getCell(i);
                if (cell == null) {
                    valueSb.append("NULL");
                    continue;
                }
                cell.setCellType(CellType.STRING);
                valueSb.append("'").append(cell.getStringCellValue().replace("'", "''")).append("'");
            }

            // 添加 import_id 和 hosp_id
            valueSb.append(", ").append(id)
                    .append(", ").append(UserContext.getCurrentLoginHospId());

            // 如果字段中没有 compute_date，才手动添加
            if (!hasComputeDateField) {
                valueSb.append(", '").append(computeDate).append("'");
            }

            valueSb.append(")");

            if (batchCount == 0) {
                // 构建字段名部分
                currentBatchValues.setLength(0); // 清空之前的缓存
                currentBatchValues.append("INSERT INTO `").append(tableName).append("` (");

                for (int i = 0; i < headerRow.getLastCellNum(); i++) {
                    if (i > 0) {
                        currentBatchValues.append(", ");
                    }
                    Cell headerCell = headerRow.getCell(i);
                    currentBatchValues.append("`").append(headerCell.getStringCellValue()).append("`");
                }

                currentBatchValues.append(", `import_id`, `hosp_id`");
                if (!hasComputeDateField) {
                    currentBatchValues.append(", `compute_date`");
                }
                currentBatchValues.append(") VALUES ");
            } else {
                currentBatchValues.append(", ");
            }

            currentBatchValues.append(valueSb.toString());
            batchCount++;

            // 如果达到批次大小，保存当前批次并重置
            if (batchCount >= BATCH_SIZE) {
                sqlList.add(currentBatchValues.toString());
                batchCount = 0;
            }

            actualRowCount++; // 使用独立变量追踪实际行数
        }

        // 添加最后一批未满的数据
        if (batchCount > 0) {
            sqlList.add(currentBatchValues.toString());
        }

        return sqlList;
    }


    /**
     * 复原导入数据
     *
     * @param id 记录id
     */
    @Override
    @Transactional(rollbackFor = Throwable.class, propagation = Propagation.REQUIRED)
    public void recoverImport(Integer id) {
        KpiComputeImport kpiComputeImport = repository.getById(id);
        if (Objects.nonNull(kpiComputeImport)) {
            //更新表信息
            String computeDate = kpiComputeImport.getComputeDate();
            String tableName = kpiComputeImport.getTableName();
            Long hospId = kpiComputeImport.getHospId();
            StringBuilder delSql = new StringBuilder().append("update " + "`").append(tableName).append("`").append("set")
                    .append("`del_flag` = 0 , `update_user` = '").append(UserContext.getCurrentUser().getId())
                    .append("' , `update_time` = '").append(DateUtils.formatDate2String(new Date())).append("' where `hosp_id` = ").append(hospId)
                    .append(" and `import_id` = ").append(id).append(";");
            try {
                log.info("执行的sql语句：" + delSql);
                SqlRunner sqlRunner = new SqlRunner(getConnection());
                sqlRunner.run(delSql.toString());
            } catch (ClassNotFoundException | SQLException e) {
                throw new RuntimeException("复原数据错误" + e);
            }
            //更新 记录表信息
            kpiComputeImport.setImportStatus(NumberConstant.ZERO);
            kpiComputeImport.setUpdateUser(String.valueOf(UserContext.getCurrentUser().getId()));
            kpiComputeImport.setUpdateTime(new Date());
            repository.updateById(kpiComputeImport);
        }
    }

    public static String readExcelSql(File file, String tableName, Integer id, String computeDate) throws IOException {
        // 获取excel工作簿对象
        String sql = null;
        String fileName = file.getName();
        String substring = fileName.substring(fileName.lastIndexOf("."));
        if (".xls".equals(substring)) {
            HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(file));
            for (Sheet sheet : workbook) {
                List<String> headerList = new ArrayList<>();
                List<Map<String, Object>> dataList = new ArrayList<>();
                System.out.println(sheet.getSheetName());
                int i = 0;
                for (Row row : sheet) {
                    if (i == 1) {
                        i++;
                        continue;
                    }
                    if (i == 0) {
                        if (i == 0) {
                            for (Cell cell : row) {
                                headerList.add(cell.getStringCellValue());
                            }
//                            if(headerList.size() > 0) {
//                                String createTableSql = createTable(headerList, sheetName);
//                                System.out.println("生成的创建表语句："+ createTableSql);
//                            }
                        }
                    } else {
                        Map<String, Object> map = new HashMap<>();
                        int j = 0;
                        for (Cell cell : row) {
                            //设置单元格类型
                            cell.setCellType(CellType.STRING);
                            map.put(headerList.get(j), cell.getStringCellValue());
                            j++;
                        }

                        dataList.add(map);
                    }
                    i++;
                }
                //组装记录表和hospId
                setCommonData(id, dataList, computeDate);
                sql = insertFromMap(dataList, tableName);

            }

        } else if (".xlsx".equals(substring)) {
            XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(file));
            for (Sheet sheet : workbook) {
                List<String> headerList = new ArrayList<>();
                List<Map<String, Object>> dataList = new ArrayList<>();
                System.out.println(sheet.getSheetName());
                String sheetName = sheet.getSheetName();
                int i = 0;
                for (Row row : sheet) {
                    if (i == 1) {
                        i++;
                        continue;
                    }
                    if (i == 0) {
                        if (i == 0) {
                            for (Cell cell : row) {
                                headerList.add(cell.getStringCellValue());
                            }
//                            if(headerList.size() > 0) {
//                                String createTableSql = createTable(headerList, sheetName);
//                                System.out.println("生成的创建表语句："+ createTableSql);
//                            }
                        }
                    } else {
                        Map<String, Object> map = new HashMap<>();
                        int j = 0;
                        for (Cell cell : row) {
                            //设置单元格类型
                            cell.setCellType(CellType.STRING);
                            map.put(headerList.get(j), cell.getStringCellValue());
                            j++;
                        }
                        dataList.add(map);
                    }
                    i++;
                }
                setCommonData(id, dataList, computeDate);
                sql = insertFromMap(dataList, tableName);
            }
        }
        return sql;
    }

    private static void setCommonData(Integer id, List<Map<String, Object>> dataList, String computeDate) {
        if (!CollectionUtils.isEmpty(dataList)) {
            dataList.forEach(stringObjectMap -> {
                stringObjectMap.put("hosp_id", UserContext.getCurrentLoginHospId());
                //Excel字段里没有传核算年月的，用传入的核算年月
                if (!stringObjectMap.containsKey("compute_date")) {
                    stringObjectMap.put("compute_date", computeDate);
                }
                stringObjectMap.put("import_id", id);
            });
        }
    }

    /**
     * 生成创建表结构
     *
     * @param headerList 表头
     * @param sheetName  sheet名
     * @return
     */
    public static String createTable(List<String> headerList, String sheetName) {
        StringBuffer createTableSql = new StringBuffer();
        if (headerList.size() > 0) {
            createTableSql.append("SET NAMES utf8mb4;\n");
            createTableSql.append("SET FOREIGN_KEY_CHECKS = 0;\n");
            createTableSql.append("DROP TABLE IF EXISTS `" + sheetName + "`;\n");
            createTableSql.append("CREATE TABLE `" + sheetName + "`");
            createTableSql.append("(\n");
            createTableSql.append("`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',\n");
            int k = 0;
            for (String key : headerList) {
                createTableSql.append("`" + key + "` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,\n");
                k++;
                if (k == headerList.size()) {
                    createTableSql.append("PRIMARY KEY (`id`) USING BTREE)\n");
                    createTableSql.append("ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin COMMENT = '' ROW_FORMAT = Dynamic;\n");
                    createTableSql.append("SET FOREIGN_KEY_CHECKS = 1;");
                }
            }
        }
        return createTableSql.toString();
    }


    /**
     * map对象生成insert插入语句
     *
     * @param dataList
     * @param tableName
     */
    public static String insertFromMap(List<Map<String, Object>> dataList, String tableName) {
        String sql = null;
        if (dataList.size() > 0) {
            int i = 0;
            StringBuilder strKey = new StringBuilder();
            //插入sql语句
            StringBuilder insertSql = new StringBuilder().append("INSERT INTO " + "`").append(tableName).append("`");
            StringBuilder value = new StringBuilder();
            for (Map<String, Object> map : dataList) {
                //存入key的字符串数组
                //存入value的字符串数组
                ArrayList<Object> arrValue = new ArrayList<>();
                //拼接sql
                ArrayList<Object> arrKey = new ArrayList<>(map.keySet());
                for (String keys : map.keySet()) {
                    arrValue.add(map.get(keys));
                }
                if (i == 0) {
                    //遍历存的key字符串数组拼接sql
                    for (int j = 0; j < arrKey.size(); j++) {
                        strKey.append("`").append(arrKey.get(j)).append("`");
                        if (j != arrKey.size() - 1) {//拼上","最后一个不拼
                            strKey.append(",");
                        }
                    }
                }
                i++;
                StringBuilder strVal = new StringBuilder();
                //遍历存的value字符串数组拼接sql
                for (int j = 0; j < arrValue.size(); j++) {
                    if (null != arrValue.get(j) && !"".equals(arrValue.get(j))) {
                        strVal.append("'").append(arrValue.get(j)).append("'");//拼接单引号
                    } else if ("".equals(arrValue.get(j))) {
                        strVal.append((String) null);
                    } else {
                        strVal.append(arrValue.get(j));
                    }
                    if (j != arrValue.size() - 1) {//拼上","最后一个不拼
                        strVal.append(",");
                    }
                }
                String stringEntryVal = strVal.toString();
                value.append("(").append(stringEntryVal).append(")");
                if (i < dataList.size()) {
                    value.append(",");
                }
            }
            insertSql.append("(").append(strKey).append(")");
            insertSql.append(" VALUES ");
            insertSql.append(value).append(";");
            System.out.println("生成插入数据sql:" + insertSql.toString());
            sql = insertSql.toString();
        }
        return sql;
    }

    private Connection getConnection() throws ClassNotFoundException, SQLException {
        Class.forName(driver);
        return DriverManager.getConnection(url, username, password);
    }
}