How to import excel data to SQLite DB using java
Prerequisites:
SQLite, Java, Database
What it covers?
1. Creating and closing database connection (JDBC)
2. Creating dynamic schema
3. Creating table and deleting table values
4. Reading data from excel
5. Importing excel data to DB
How it works?
1. To execute the SQL queries using java, we need to establish a connection with DB and then create a statement to execute the queries. Uses prepare statement to execute statement multiple times.
2. To create schema, we are generating total number of maximum columns having values from excel.
3. Using Apache POI to Read excel data and importing data in batch using prepared statement methods.
4. Closing workbook and database connection
<CODE>
// Assign variables
Connection connection = null;
PreparedStatement statement = null;
Connection connection = null;
String dbName = "<DB Name>";
String excelFilePath = "<file path>";
String url = "jdbc:sqlite:" + <"dbPath"> + dbName+ ".db";
// create a connection to the database
connection = DriverManager.getConnection(url);
connection.setAutoCommit(false);
// Opening Worksheet
FileInputStream inputStream = new FileInputStream(excelFilePath);
Workbook workbook = new XSSFWorkbook(inputStream);
Sheet firstSheet = workbook.getSheetAt(0); // first sheet
// Generate maximum number of column in sheet
int max=0;
Iterator<Row> rowIterator = firstSheet.iterator();
while (rowIterator.hasNext()) {
Row nextRow = rowIterator.next();
Iterator<Cell> cellIterator = nextRow.cellIterator();
int totalNoOfRows = firstSheet.getLastRowNum(); // To get the number of rows present in sheet
while (cellIterator.hasNext()) {
Cell nextCell = cellIterator.next();
int col1 = nextCell.getColumnIndex();
for (int row = 1; row <= totalNoOfRows; row++) {
if (col1 >= max)
max = col1;
}
}
}
// creating schema
ArrayList sch = new ArrayList();
ArrayList val = new ArrayList();
for (int first = 1; first <= max + 1; first++) {
sch.add("’" + first + "’");
val.add("?");
}
String schema = sch.toString().replace("[", "(").replace("]", ")").trim();
String values = val.toString().replace("[", "(").replace("]", ")").trim();
// execute queries using create statement
String table = "CREATE TABLE " + dbName + schema + ";";
Statement stmt = connection.createStatement();
stmt.executeUpdate(table);
String delete = "DELETE FROM " + dbName + ";";
Statement stmt = connection.createStatement();
stmt.executeUpdate(delete);
// execute query using prepare statement
String insert = "INSERT INTO " + dbName + schema + "VALUES" + values + ";";
statement = connection.prepareStatement(insert);
// Read excel data
Iterator<Row> rowIterator = firstSheet.iterator();
while (rowIterator.hasNext()) {
int cellCount = 0;
Row nextRow = rowIterator.next();
ArrayList<String> data = new ArrayList<String>();
data.clear();
Iterator<Cell> cellIterator = nextRow.cellIterator();
while (cellIterator.hasNext()) {
Cell nextCell = cellIterator.next();
int col = nextCell.getColumnIndex();
if (nextCell.getCellType() == CellType.STRING) {
data.add(cellCount, nextCell.getStringCellValue());
}
else if (nextCell.getCellType() == CellType.NUMERIC) {
data.add(cellCount, NumberToTextConverter.toText(nextCell.getNumericCellValue()));
}
statement.setString(col + 1, data.get(cellCount).toString());
cellCount += 1;
}
// Import data in batch using prepared statement methods
statement.addBatch();
statement.executeBatch();
statement.clearBatch();
}
// Closing workbook and database connection
workbook.close();
connection.commit();
connection.close();
</CODE>
Happy Testing!