Java读取文件(Excel、CSV、TXT)中的数据并导入数据库(MySQL、Oracle)

Java 实现读取文件（Excel、CSV、TXT）中的数据并导入MySQL和Oracle数据库。

引入依赖

在pom.xml文件中添加以下依赖，用于处理Excel文件（Apache POI）、CSV文件（OpenCSV），以及与MySQL和Oracle的数据库连接。

<dependencies>
    <!-- MySQL JDBC Driver -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.32</version>
    </dependency>

    <!-- Oracle JDBC Driver -->
    <dependency>
        <groupId>com.oracle.database.jdbc</groupId>
        <artifactId>ojdbc8</artifactId>
        <version>19.8.0.0</version>
    </dependency>

    <!-- Apache POI for Excel files -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>5.2.3</version>
    </dependency>

    <!-- OpenCSV for CSV files -->
    <dependency>
        <groupId>com.opencsv</groupId>
        <artifactId>opencsv</artifactId>
        <version>5.7.1</version>
    </dependency>
</dependencies>

连接数据库

创建一个工具类来管理数据库连接

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

public class DatabaseConnection {
    private static final String MYSQL_URL = "jdbc:mysql://localhost:3306/database";
    private static final String ORACLE_URL = "jdbc:oracle:thin:@localhost:1521:xe";
    private static final String MYSQL_USER = "username";
    private static final String MYSQL_PASSWORD = "password";
    private static final String ORACLE_USER = "username";
    private static final String ORACLE_PASSWORD = "password";

    public static Connection getMySQLConnection() throws SQLException {
        return DriverManager.getConnection(MYSQL_URL, MYSQL_USER, MYSQL_PASSWORD);
    }

    public static Connection getOracleConnection() throws SQLException {
        return DriverManager.getConnection(ORACLE_URL, ORACLE_USER, ORACLE_PASSWORD);
    }
}

解析文件并生成表结构

读取Excel、CSV、和TXT文件，并根据表头生成SQL表结构。

1. Excel 文件读取

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.Statement;

public class ExcelReader {

    public static void readExcel(String filePath, Connection connection) throws Exception {
        FileInputStream fis = new FileInputStream(filePath);
        Workbook workbook = new XSSFWorkbook(fis);
        Sheet sheet = workbook.getSheetAt(0);
        Row headerRow = sheet.getRow(0);

        // 表名为文件名（去掉扩展名）
        String tableName = filePath.substring(filePath.lastIndexOf("/") + 1, filePath.lastIndexOf("."));
        StringBuilder createTableQuery = new StringBuilder("CREATE TABLE " + tableName + " (");

        for (Cell cell : headerRow) {
            String columnName = cell.getStringCellValue();
            createTableQuery.append(columnName).append(" VARCHAR(255),");
        }

        // 去掉最后的逗号
        createTableQuery.setLength(createTableQuery.length() - 1);
        createTableQuery.append(");");

        // 创建表
        Statement stmt = connection.createStatement();
        stmt.execute(createTableQuery.toString());

        // 插入数据
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            StringBuilder insertQuery = new StringBuilder("INSERT INTO " + tableName + " VALUES (");

            for (Cell cell : row) {
                insertQuery.append("'").append(cell.toString()).append("',");
            }

            insertQuery.setLength(insertQuery.length() - 1);
            insertQuery.append(");");

            stmt.execute(insertQuery.toString());
        }

        workbook.close();
        fis.close();
    }
}

2. CSV 文件读取

import com.opencsv.CSVReader;

import java.io.FileReader;
import java.sql.Connection;
import java.sql.Statement;

public class CSVReaderUtil {

    public static void readCSV(String filePath, Connection connection) throws Exception {
        CSVReader csvReader = new CSVReader(new FileReader(filePath));
        String[] header = csvReader.readNext();

        // 表名为文件名（去掉扩展名）
        String tableName = filePath.substring(filePath.lastIndexOf("/") + 1, filePath.lastIndexOf("."));
        StringBuilder createTableQuery = new StringBuilder("CREATE TABLE " + tableName + " (");

        for (String column : header) {
            createTableQuery.append(column).append(" VARCHAR(255),");
        }

        createTableQuery.setLength(createTableQuery.length() - 1);
        createTableQuery.append(");");

        Statement stmt = connection.createStatement();
        stmt.execute(createTableQuery.toString());

        String[] row;
        while ((row = csvReader.readNext()) != null) {
            StringBuilder insertQuery = new StringBuilder("INSERT INTO " + tableName + " VALUES (");

            for (String cell : row) {
                insertQuery.append("'").append(cell).append("',");
            }

            insertQuery.setLength(insertQuery.length() - 1);
            insertQuery.append(");");

            stmt.execute(insertQuery.toString());
        }

        csvReader.close();
    }
}

3. TXT 文件读取

import java.io.BufferedReader;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.Statement;

public class TXTReader {

    public static void readTXT(String filePath, Connection connection) throws Exception {
        BufferedReader br = new BufferedReader(new FileReader(filePath));
        String headerLine = br.readLine();

        // 表名为文件名（去掉扩展名）
        String tableName = filePath.substring(filePath.lastIndexOf("/") + 1, filePath.lastIndexOf("."));
        String[] headers = headerLine.split("\t");

        StringBuilder createTableQuery = new StringBuilder("CREATE TABLE " + tableName + " (");

        for (String header : headers) {
            createTableQuery.append(header).append(" VARCHAR(255),");
        }

        createTableQuery.setLength(createTableQuery.length() - 1);
        createTableQuery.append(");");

        Statement stmt = connection.createStatement();
        stmt.execute(createTableQuery.toString());

        String line;
        while ((line = br.readLine()) != null) {
            String[] data = line.split("\t");
            StringBuilder insertQuery = new StringBuilder("INSERT INTO " + tableName + " VALUES (");

            for (String value : data) {
                insertQuery.append("'").append(value).append("',");
            }

            insertQuery.setLength(insertQuery.length() - 1);
            insertQuery.append(");");

            stmt.execute(insertQuery.toString());
        }

        br.close();
    }
}

使用

创建一个主类，调用不同文件类型的读取方法。

import java.sql.Connection;

public class ImportData {

    public static void main(String[] args) {
        try {
            Connection mysqlConnection = DatabaseConnection.getMySQLConnection();
            Connection oracleConnection = DatabaseConnection.getOracleConnection();

            // 读取Excel文件并导入MySQL
            ExcelReader.readExcel("data.xlsx", mysqlConnection);

            // 读取CSV文件并导入Oracle
            CSVReaderUtil.readCSV("data.csv", oracleConnection);

            // 读取TXT文件并导入MySQL
            TXTReader.readTXT("data.txt", mysqlConnection);

            System.out.println("Data imported successfully!");

            mysqlConnection.close();
            oracleConnection.close();

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