package com.ruoyi.system.test;


import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.*;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.regex.Matcher;
import java.util.regex.Pattern;


import static com.ruoyi.common.utils.StringUtils.convertToCamelCase;


/**
 * @author hanzihang
 * @date 2023/7/20 2:34 PM
 */
public class ljj {


    public static String extractSubstring(String input, String identifier) {
        int startIndex = input.indexOf(identifier);
        if (startIndex == -1) {
            return null; // 如果找不到指定的标识符，则返回空
        }

        int endIndex = input.indexOf(" ", startIndex);
        if (endIndex == -1) {
            endIndex = input.length(); // 如果找不到空格，则截取到字符串末尾
        }
        String sqlColumn = input.substring(startIndex + identifier.length() + 1, endIndex);
        System.out.println(sqlColumn);

        return convertToCamelCase(sqlColumn);

    }

    private static final Pattern TPATTERN = Pattern.compile("[A-Z0-9]");

    private static String teseDemo(String str) {
        Matcher matcher = TPATTERN.matcher(str);
        StringBuffer sb = new StringBuffer();
        while (matcher.find()) {
            matcher.appendReplacement(sb, "_" + matcher.group(0).toLowerCase());
        }
        matcher.appendTail(sb);
        return sb.toString();
    }

    public static void main(String[] args) {


        try {
            FileInputStream file = new FileInputStream(new File("data.xlsx"));

            // 创建一个工作簿对象
            Workbook workbook = new XSSFWorkbook(file);

            // 获取第一个sheet
            Sheet sheet = workbook.getSheetAt(0);

            // 配置数据库连接
            String url = "jdbc:mysql://localhost:3306/mydatabase";
            String username = "root";
            String password = "password";

            // 建立数据库连接
            Connection connection = DriverManager.getConnection(url, username, password);

            // 准备插入数据的SQL语句
            String insertQuery = "INSERT INTO mytable (column1, column2) VALUES (?, ?)";

            // 创建预处理语句对象
            PreparedStatement preparedStatement = connection.prepareStatement(insertQuery);

            // 迭代每一行并插入数据
            for (Row row : sheet) {
                // 读取每一列数据
                Cell cell1 = row.getCell(0);
                Cell cell2 = row.getCell(1);

                // 设置参数并执行插入操作
                preparedStatement.setString(1, cell1.getStringCellValue());
                preparedStatement.setString(2, cell2.getStringCellValue());
                preparedStatement.executeUpdate();
            }

            // 关闭预处理语句、数据库连接和文件流
            preparedStatement.close();
            connection.close();
            workbook.close();
            file.close();
        } catch (IOException | SQLException e) {
            e.printStackTrace();
        }
    }

}
