java 将csv表格文件写入数据库 jdbc CSVReader（从小白到高手的实现）

wuxinhhh

已于 2024-03-14 16:53:36 修改

阅读量134

点赞数 1

文章标签： java 数据库开发语言

于 2024-03-14 16:17:24 首次发布

本文链接：https://blog.csdn.net/wuxinhhh/article/details/136714037

版权

将一个csv文件写入数据库

将一个文件夹下的所以csv文件循环写入数据库

将一个文件夹下的所以csv文件写入数据库（使用线程池 executor）

将一个csv文件写入数据库



import com.opencsv.CSVReader;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileReader;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class ExcelToDatabase {

    public static void main(String[] args) {
        String csvFilePath = "D:\\pythonProject1\\work\\工会.csv";

        try (CSVReader reader = new CSVReader(new InputStreamReader(new FileInputStream(csvFilePath), "gbk"));
             Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/rm?useUnicode=true&characterEncoding=UTF-8", "root", "123456")) {

            //Sheet sheet = workbook.getSheetAt(0); // Assuming data is in the first sheet
            reader.skip(1);

            String[] nextLine;
            while ((nextLine = reader.readNext()) != null) {
                //Row row = sheet.getRow(i);
                String name = nextLine[0];
                String email = nextLine[1];
                String department = nextLine[2];
                String Unnamed3 = nextLine[3];
                String Unnamed4 = nextLine[4];
                String Unnamed5 = nextLine[5];
                String phone = nextLine[6];
                String alias = nextLine[7];
                String status = nextLine[8];
                String work_type = nextLine[9];
                String used_space = nextLine[10];
                String allocated_space = nextLine[11];
                String sort_order = nextLine[12];
                String last_login_time = nextLine[12];
                String last_password_change_time = nextLine[14];
                String create_time = nextLine[15];

                // Continue to extract other columns as needed

                // Insert data into database
                String insertQuery = "INSERT INTO jtEmail (name, email, department,Unnamed3,Unnamed4,Unnamed5,phone, alias, status, work_type, used_space, allocated_space, sort_order, last_login_time, last_password_change_time, create_time) " +
                        "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?,?,?)";
                PreparedStatement preparedStatement = connection.prepareStatement(insertQuery);
                preparedStatement.setString(1, name);
                preparedStatement.setString(2, email);
                preparedStatement.setString(3, department);
                preparedStatement.setString(4, Unnamed3);
                preparedStatement.setString(5, Unnamed4);
                preparedStatement.setString(6, Unnamed5);
                preparedStatement.setString(7, phone);
                preparedStatement.setString(8, alias);
                preparedStatement.setString(9, status);
                preparedStatement.setString(10, work_type);
                preparedStatement.setString(11, used_space);
                preparedStatement.setString(12, allocated_space);
                preparedStatement.setString(13, sort_order);
                preparedStatement.setString(14, last_login_time);
                preparedStatement.setString(15, last_password_change_time);
                preparedStatement.setString(16, create_time);
                // Set other parameters
                preparedStatement.executeUpdate();
            }

            System.out.println("Data inserted into database successfully.");

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

将一个文件夹下的所以csv文件循环写入数据库



import com.opencsv.CSVReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class ExcelToDatabaseAll {

    public static void main(String[] args) {
        String directoryPath = "D:\\pythonProject1\\work\\";

        File directory = new File(directoryPath);
        File[] files = directory.listFiles((dir, name) -> name.toLowerCase().endsWith(".csv"));

        if (files != null) {
            for (File file : files) {
                try (CSVReader reader = new CSVReader(new InputStreamReader(new FileInputStream(file), "gbk"));
                     Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/rm?useUnicode=true&characterEncoding=UTF-8", "root", "123456")) {

                    String[] nextLine;
                    reader.skip(1);
                    while ((nextLine = reader.readNext()) != null) {
                        String name = nextLine[0];
                        String email = nextLine[1];
                        String department = nextLine[2];
                        String Unnamed3 = nextLine[3];
                        String Unnamed4 = nextLine[4];
                        String Unnamed5 = nextLine[5];
                        String phone = nextLine[6];
                        String alias = nextLine[7];
                        String status = nextLine[8];
                        String work_type = nextLine[9];
                        String used_space = nextLine[10];
                        String allocated_space = nextLine[11];
                        String sort_order = nextLine[12];
                        String last_login_time = nextLine[12];
                        String last_password_change_time = nextLine[14];
                        String create_time = nextLine[15];
                        // Continue to extract other columns as needed

                        // Insert data into database
                        String insertQuery = "INSERT INTO jtEmail (name, email, department,Unnamed3,Unnamed4,Unnamed5,phone, alias, status, work_type, used_space, allocated_space, sort_order, last_login_time, last_password_change_time, create_time) " +
                                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?,?,?)";
                        PreparedStatement preparedStatement = connection.prepareStatement(insertQuery);
                        preparedStatement.setString(1, name);
                        preparedStatement.setString(2, email);
                        preparedStatement.setString(3, department);
                        preparedStatement.setString(4, Unnamed3);
                        preparedStatement.setString(5, Unnamed4);
                        preparedStatement.setString(6, Unnamed5);
                        preparedStatement.setString(7, phone);
                        preparedStatement.setString(8, alias);
                        preparedStatement.setString(9, status);
                        preparedStatement.setString(10, work_type);
                        preparedStatement.setString(11, used_space);
                        preparedStatement.setString(12, allocated_space);
                        preparedStatement.setString(13, sort_order);
                        preparedStatement.setString(14, last_login_time);
                        preparedStatement.setString(15, last_password_change_time);
                        preparedStatement.setString(16, create_time);
                        // Set other parameters
                        preparedStatement.executeUpdate();
                    }

                    System.out.println("Data from file " + file.getName() + " inserted into database successfully.");

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

将一个文件夹下的所以csv文件写入数据库（使用线程池 executor）



import com.opencsv.CSVReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

public class ExcelToDatabaseAllThread {

    public static void main(String[] args) {
        String directoryPath = "D:\\pythonProject1\\work\\";
        //初始化线程池
        ExecutorService executor = Executors.newFixedThreadPool(10);

        File directory = new File(directoryPath);
        File[] files = directory.listFiles((dir, name) -> name.toLowerCase().endsWith(".csv"));

        if (files != null) {
            for (File file : files) {
                executor.submit(() -> { //提交任务到线程池
                    try (CSVReader reader = new CSVReader(new InputStreamReader(new FileInputStream(file), "gbk"));
                         Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/rm?useUnicode=true&characterEncoding=UTF-8", "root", "123456")) {

                        String[] nextLine;
                        reader.skip(1);
                        while ((nextLine = reader.readNext()) != null) {
                            String name = nextLine[0];
                            String email = nextLine[1];
                            String department = nextLine[2];
                            String Unnamed3 = nextLine[3];
                            String Unnamed4 = nextLine[4];
                            String Unnamed5 = nextLine[5];
                            String phone = nextLine[6];
                            String alias = nextLine[7];
                            String status = nextLine[8];
                            String work_type = nextLine[9];
                            String used_space = nextLine[10];
                            String allocated_space = nextLine[11];
                            String sort_order = nextLine[12];
                            String last_login_time = nextLine[12];
                            String last_password_change_time = nextLine[14];
                            String create_time = nextLine[15];
                            // Continue to extract other columns as needed

                            // Insert data into database
                            String insertQuery = "INSERT INTO jtEmail (name, email, department,Unnamed3,Unnamed4,Unnamed5,phone, alias, status, work_type, used_space, allocated_space, sort_order, last_login_time, last_password_change_time, create_time) " +
                                    "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?,?,?)";
                            PreparedStatement preparedStatement = connection.prepareStatement(insertQuery);
                            preparedStatement.setString(1, name);
                            preparedStatement.setString(2, email);
                            preparedStatement.setString(3, department);
                            preparedStatement.setString(4, Unnamed3);
                            preparedStatement.setString(5, Unnamed4);
                            preparedStatement.setString(6, Unnamed5);
                            preparedStatement.setString(7, phone);
                            preparedStatement.setString(8, alias);
                            preparedStatement.setString(9, status);
                            preparedStatement.setString(10, work_type);
                            preparedStatement.setString(11, used_space);
                            preparedStatement.setString(12, allocated_space);
                            preparedStatement.setString(13, sort_order);
                            preparedStatement.setString(14, last_login_time);
                            preparedStatement.setString(15, last_password_change_time);
                            preparedStatement.setString(16, create_time);
                            // Set other parameters
                            preparedStatement.executeUpdate();
                        }

                        System.out.println("Data from file " + file.getName() + " inserted into database successfully.");

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

        executor.shutdown();
    }
}