如何使用Java代码批量导入excel数据到数据库
时间: 2023-05-19 14:05:18 浏览: 127
您可以使用Apache POI库来读取Excel文件中的数据,然后使用JDBC连接到数据库并将数据插入到数据库中。以下是一个简单的示例代码:
```java
import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
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 ExcelToDatabase {
public static void main(String[] args) {
String jdbcUrl = "jdbc:mysql://localhost:3306/mydatabase";
String username = "myusername";
String password = "mypassword";
String excelFilePath = "data.xlsx";
try (Connection connection = DriverManager.getConnection(jdbcUrl, username, password)) {
FileInputStream inputStream = new FileInputStream(new File(excelFilePath));
Workbook workbook = new XSSFWorkbook(inputStream);
Sheet sheet = workbook.getSheetAt(0);
String sql = "INSERT INTO mytable (column1, column2, column3) VALUES (?, ?, ?)";
PreparedStatement statement = connection.prepareStatement(sql);
int rowNumber = 0;
for (Row row : sheet) {
if (rowNumber == 0) {
rowNumber++;
continue;
}
Cell cell1 = row.getCell(0);
String column1 = cell1.getStringCellValue();
Cell cell2 = row.getCell(1);
String column2 = cell2.getStringCellValue();
Cell cell3 = row.getCell(2);
double column3 = cell3.getNumericCellValue();
statement.setString(1, column1);
statement.setString(2, column2);
statement.setDouble(3, column3);
statement.addBatch();
if (rowNumber % 1000 == 0) {
statement.executeBatch();
}
}
workbook.close();
statement.executeBatch();
System.out.println("Data imported successfully.");
} catch (SQLException e) {
System.out.println("Database error:");
e.printStackTrace();
} catch (Exception e) {
System.out.println("Error:");
e.printStackTrace();
}
}
}
```
请注意,此代码仅适用于Excel文件的第一个工作表,并且假定数据库中已经存在名为“mytable”的表,该表具有三个列:column1,column2和column3。您需要根据自己的需求进行修改。