极速 csv文件导入Mysql

第一步：创建项目、导入包如图：
commons-lang3-3.8.1.jar
mysql-connector-java-8.0.26.jar
opencsv-5.5.2.jar

第二步：实现连接Mysql
package com.excel.utils;

import java.sql.Connection;
import java.sql.DriverManager;

public class DBUtils {
/**
* URL."
/
private static String URL = “jdbc:mysql://IP:端口/数据库名?characterEncoding=UTF-8&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai&useSSL=false”;
/*
* USERNAME.
/
private static String USERNAME = “用户名”;
/*
* PASSWORD.
*/
private static String PASSWORD = “密码”;

/**
 * conn.
 */
private static Connection conn;
/**
 * DRIVER_CLASS.
 */
private static String DRIVER_CLASS = "com.mysql.cj.jdbc.Driver";

/**
 * 根据配置文件，获取数据库连接
 * 
 * @param configFile
 * @return
 * @throws Exception
 */
public static Connection getConnection(final String configFile) throws Exception {
	if (conn == null) {
		Class.forName(DRIVER_CLASS);
		conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
		return conn;
	} else {
		return conn;
	}
}

/**
 * 根据配置文件，获取数据库连接
 * 
 * @param configFile
 * @return
 * @throws Exception
 */
public static Connection getConnection() throws Exception {
	if (conn == null) {
		Class.forName(DRIVER_CLASS);
		conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
		return conn;
	} else {
		return conn;
	}
}

}

第三步：实现导入数据库
package com.excel.utils;
import java.io.BufferedReader;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;
import java.util.List;

import com.opencsv.CSVReader;

public class ImportDataForMysql {

public static void main(String[] args) {
	String csvFilePath ="";
	String tablename = "";  
	csvFilePath = "2023-09-14.csv";
	tablename = "TaskA1_20230913";
	inputData(csvFilePath,tablename);
}

public static void inputData(String csvFilePath, String tableName) {
   // 替换为你的CSV文件路径
    try (Connection connection = getConn();
         BufferedReader br = new BufferedReader(new FileReader(csvFilePath))) {
        // 读取CSV文件第一行作为表头
        String headerLine = br.readLine();
        List<String> headers = Arrays.asList(headerLine.split(","));
        dropTable(connection, headers,tableName);
        // 建立表格并获取动态的列定义
        createTable(connection, headers,tableName);
        // 使用 OpenCSV 读取 CSV 文件数据
        CSVReader csvReader = new CSVReader(br);
        List<String[]> rows = csvReader.readAll();
        // 将STS数据插入到表中
        insertDataIntoTable(connection, headers, rows,tableName);

        System.out.println("数据导入完成！");

    } catch (Exception e) {
        e.printStackTrace();
    }
}

public static Connection getConn() throws Exception {
	Connection conn= DBUtils.getConnection();
	return conn;
}


private static void dropTable(Connection connection, List<String> headers,String table) throws SQLException {
	try {
		StringBuilder sb = new StringBuilder();
        sb.append("Drop TABLE "+table+";"); 
        try (Statement statement = connection.createStatement()) {
            statement.executeUpdate(sb.toString());
        }
	} catch (Exception e) {
		e.printStackTrace();
	}
    
}


public static String maxlenStr = "";
public static String findLongestString(String oldstring, String newString) {
	String longestString = "";
	int maxLength = oldstring.length();
	if (newString.length() > maxLength) {
		maxLength = newString.length();
		longestString = newString;
	}
	maxlenStr = longestString;
	return longestString;
}

private static void createTable(Connection connection, List<String> headers,String table) throws SQLException {
    StringBuilder sb = new StringBuilder();
    sb.append("CREATE TABLE "+table+ " (");
    for (String header : headers) {
        sb.append("`"+header+"`").append(" varchar(200),");
    }
    sb.deleteCharAt(sb.length() - 1);
    sb.append(")");
    System.out.println(sb.toString());

    try (Statement statement = connection.createStatement()) {
        statement.executeUpdate(sb.toString());
    }
}

private static void insertDataIntoTable(Connection connection, List<String> headers, List<String[]> data,String tableName) throws SQLException {
    StringBuilder sb = new StringBuilder();
    sb.append("INSERT INTO "+tableName+" (");
    for (String header : headers) {
        sb.append("`"+header+"`").append(",");
    }
    sb.deleteCharAt(sb.length() - 1);
    sb.append(") VALUES (");
    
    for (String header : headers) {
        sb.append("?").append(",");
    }
    sb.deleteCharAt(sb.length() - 1);

    String sql = sb.toString();
    sql += ")";
    System.out.println(sql);
    try (PreparedStatement statement = connection.prepareStatement(sql)) {
        for (String[] row : data) {
            for (int i = 0; i < row.length; i++) {
            	String col = row[i];
            	findLongestString(maxlenStr, col);
                statement.setString(i + 1, row[i]);
            }
            statement.executeUpdate();
        }
    }
    System.out.println(maxlenStr);
}

}

第四步：效果图