- Details
- Written by Nam Ha Minh
- Last Updated on 12 December 2023   |   Print Email
Importing data from Excel to database is a common task of a software program. So in this post, I will share with you a sample Java program that reads data from an Excel file and inserts that data to a MySQL database.Suppose that we have an Excel file that stores information about students enrolled in courses like this:
As you can see, this Excel file has only one sheet and the student information is stored in 3 columns: Student Name (text), Enrolled (date time) and Progress (number). So let’s create a corresponding table in the database with the following structure:
You can run the following MySQL script to create this table:
CREATE TABLE `students` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(128) NOT NULL,
`enrolled` timestamp NOT NULL,
`progress` int(11) NOT NULL,
PRIMARY KEY (`id`)
);
Note that the data type of the
enrolled column is timestamp so it can hold both date and time values.To
read the Excel file from Java, we can use the
Apache POI library. Suppose that your project uses Maven, so add the following dependency to the
pom.xml file:
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.0</version>
</dependency>
And to insert data to MySQL database from Java, we need to use a dependency for MySQL JDBC driver:
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.46</version>
<scope>runtime</scope>
</dependency>
And below is the full code of the sample program that reads data from the Excel file and insert that data to MySQL database:
package net.codejava;
import java.io.*;
import java.sql.*;
import java.util.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
/**
* Sample Java program that imports data from an Excel file to MySQL database.
*
* @author Nam Ha Minh - https://www.codejava.net
*
*/
public class Excel2DatabaseTest {
public static void main(String[] args) {
String jdbcURL = "jdbc:mysql://localhost:3306/sales";
String username = "user";
String password = "password";
String excelFilePath = "Students.xlsx";
int batchSize = 20;
Connection connection = null;
try {
long start = System.currentTimeMillis();
FileInputStream inputStream = new FileInputStream(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 students (name, enrolled, progress) 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:
Date enrollDate = nextCell.getDateCellValue();
statement.setTimestamp(2, new Timestamp(enrollDate.getTime()));
case 2:
int progress = (int) nextCell.getNumericCellValue();
statement.setInt(3, progress);
}
}
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();
}
}
}
As you can see, we use
JDBC batch update feature to efficiently insert a large number of rows into the database, with batch size of 20 – which means it will group maximum 20 SQL statements in each batch sent to the server for processing.We also use
JDBC transaction to make sure that either all rows inserted or none. Finally, we measure the time it takes to complete the reading and inserting of data.Run this program and you would see the output somehow like this:
Import done in 3462 ms
To verify, run SQL Select statement in MySQL Workbench and you would see:
That’s how to write Java code that imports data from an Excel file and insert into database. For your reference, I attached the sample project in the Attachments section below. You can also get the sample code on GitHub
here.
Related Tutorials:
Other Java Coding Tutorials:
About the Author:
Nam Ha Minh is certified Java programmer (SCJP and SCWCD). He started programming with Java in the time of Java 1.4 and has been falling in love with Java since then. Make friend with him on
Facebook and watch
his Java videos you YouTube.