java 一个表导入到三个数据库

可以使用 JDBC 技术将一个表导入到三个数据库中。首先，需要在 Java 代码中连接到三个数据库，然后使用 SQL 语句将表中的数据插入到每个数据库中。具体实现可以参考 JDBC 的相关文档和示例代码。

相关问题

java批量导入excel到数据库

要批量导入Excel到数据库，可以使用Java中的Apache POI和JDBC技术。以下是一个简单的示例代码：

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

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

public class ExcelToDatabase {

    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/mydatabase";
        String username = "root";
        String password = "password";

        try (Connection conn = DriverManager.getConnection(url, username, password)) {
            String sql = "INSERT INTO mytable (column1, column2, column3) VALUES (?, ?, ?)";
            PreparedStatement statement = conn.prepareStatement(sql);

            FileInputStream inputStream = new FileInputStream("data.xlsx");
            XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
            Sheet sheet = workbook.getSheetAt(0);

            for (Row row : sheet) {
                Cell cell1 = row.getCell(0);
                Cell cell2 = row.getCell(1);
                Cell cell3 = row.getCell(2);

                statement.setString(1, cell1.getStringCellValue());
                statement.setInt(2, (int) cell2.getNumericCellValue());
                statement.setDouble(3, cell3.getNumericCellValue());
                statement.addBatch();
            }

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

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

在这个示例中，我们首先建立了一个JDBC连接，然后使用Apache POI从Excel文件中读取数据。我们将每一行数据插入到数据库表中，使用PreparedStatement对象来执行SQL语句。最后，我们使用executeBatch()方法来批量执行插入操作。

请注意，这个示例假设Excel文件中的第一列是字符串，第二列是整数，第三列是双精度浮点数。您需要根据实际情况进行适当的更改。

用java代码编写一个 将Excel文件导入到mysql数据库的代码

### 回答1：
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.Iterator;

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

public class ExcelToMysql {

public static void main(String[] args) {

String jdbcUrl = "jdbc:mysql://localhost:3306/test_db";
String username = "root";
String password = "password";

String excelFilePath = "data.xlsx";

int batchSize = 20;

Connection connection = null;

try {

long start = System.currentTimeMillis();

FileInputStream inputStream = new FileInputStream(new File(excelFilePath));

Workbook workbook = new XSSFWorkbook(inputStream);

Sheet firstSheet = workbook.getSheetAt(0);
Iterator<Row> rowIterator = firstSheet.iterator();

connection = DriverManager.getConnection(jdbcUrl, username, password);
connection.setAutoCommit(false);

String sql = "INSERT INTO users (name, email, country) VALUES (?, ?, ?)";
PreparedStatement statement = connection.prepareStatement(sql);

int count = 0;

rowIterator.next(); // skip the header row

while (rowIterator.hasNext()) {
Row nextRow = rowIterator.next();
Iterator<Cell> cellIterator = nextRow.cellIterator();

while (cellIterator.hasNext()) {
Cell nextCell = cellIterator.next();

int columnIndex = nextCell.getColumnIndex();

switch (columnIndex) {
case 0:
String name = nextCell.getStringCellValue();
statement.setString(1, name);
break;
case 1:
String email = nextCell.getStringCellValue();
statement.setString(2, email);
break;
case 2:
String country = nextCell.getStringCellValue();
statement.setString(3, country);
break;
}

}

statement.addBatch();

if (count % batchSize == 0) {
statement.executeBatch();
}

}

workbook.close();

// execute the remaining queries
statement.executeBatch();

connection.commit();
connection.close();

long end = System.currentTimeMillis();
System.out.printf("Import done in %d ms\n", (end - start));

} catch (IOException ex1) {
System.out.println("Error reading file");
ex1.printStackTrace();
} catch (SQLException ex2) {
System.out.println("Database error");
ex2.printStackTrace();
}

}

}

### 回答2：
import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Statement;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelToMysql {
public static void main(String[] args) {
String jdbcURL = "jdbc:mysql://localhost:3306/database_name";
String username = "username";
String password = "password";

String excelFilePath = "path_to_excel_file";
int batchSize = 20;

Connection connection = null;

try {
FileInputStream inputStream = new FileInputStream(new File(excelFilePath));
Workbook workbook = new XSSFWorkbook(inputStream);

Sheet firstSheet = workbook.getSheetAt(0);
Iterator<Row> rowIterator = firstSheet.iterator();

connection = DriverManager.getConnection(jdbcURL, username, password);
connection.setAutoCommit(false);

String sql = "INSERT INTO table_name (column1, column2, column3) VALUES (?, ?, ?)";
PreparedStatement statement = connection.prepareStatement(sql);

int count = 0;

rowIterator.next(); // Skip header row

while (rowIterator.hasNext()) {
Row nextRow = rowIterator.next();
Iterator<Cell> cellIterator = nextRow.cellIterator();

while (cellIterator.hasNext()) {
Cell nextCell = cellIterator.next();

int columnIndex = nextCell.getColumnIndex();

switch (columnIndex) {
case 0:
String column1Value = nextCell.getStringCellValue();
statement.setString(1, column1Value);
break;
case 1:
String column2Value = nextCell.getStringCellValue();
statement.setString(2, column2Value);
break;
case 2:
double column3Value = nextCell.getNumericCellValue();
statement.setDouble(3, column3Value);
break;
}
}

statement.addBatch();

if (count % batchSize == 0) {
statement.executeBatch();
}
}

workbook.close();

statement.executeBatch();

connection.commit();
connection.close();

System.out.println("Data imported successfully.");
} catch (Exception e) {
e.printStackTrace();
}
}
}

### 回答3：
导入Excel文件到MySQL数据库的代码可以使用Java中的Apache POI和JDBC来实现。下面是一个示例代码：

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

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

public class ExcelToMySQLImporter {

    public static void main(String[] args) {
        String excelFilePath = "your_excel_file_path.xlsx";
        String dbUrl = "jdbc:mysql://localhost:3306/your_database";
        String username = "your_username";
        String password = "your_password";

        try (Connection connection = DriverManager.getConnection(dbUrl, username, password);
             FileInputStream fileInputStream = new FileInputStream(excelFilePath);
             Workbook workbook = new XSSFWorkbook(fileInputStream)) {

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

            for (Row row : sheet) {
                int id = (int)row.getCell(0).getNumericCellValue(); // 获取第一列数据
                String name = row.getCell(1).getStringCellValue(); // 获取第二列数据
                int age = (int)row.getCell(2).getNumericCellValue(); // 获取第三列数据

                // 创建插入数据的SQL语句
                String insertQuery = "INSERT INTO your_table (id, name, age) VALUES (?, ?, ?)";

                PreparedStatement preparedStatement = connection.prepareStatement(insertQuery);
                preparedStatement.setInt(1, id);
                preparedStatement.setString(2, name);
                preparedStatement.setInt(3, age);

                preparedStatement.executeUpdate();
            }

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

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

请注意替换`your_excel_file_path.xlsx`为实际Excel文件的路径，`your_database`为要导入的数据库的名称，`your_table`为要导入的表的名称，`your_username`和`your_password`为数据库的用户名和密码。代码将依次读取工作表的每一行，并将数据插入到MySQL数据库中。