How to import excel data to SQLite DB using java — Devstringx Technologies
2 min readOct 12, 2021
What does it cover?
- Creating and closing database connection (JDBC)
- Creating dynamic schema
- Creating table and deleting table values
- Reading data from excel
- Importing excel data to DB
Prerequisites:
SQLite, Java, Database
How does it work?
- To execute the SQL queries using java, we need to establish a connection with DB and then create a statement to execute the queries. Use prepare statement to execute statement multiple times.
- To create schema, we are generating total number of maximum columns having values from excel.
- Using Apache POI to Read excel data and importing data in batch using prepared statement methods.
- Closing workbook and database connection
Code :
// Assign variablesConnection 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 databaseconnection = DriverManager.getConnection(url);connection.setAutoCommit(false);
// Opening WorksheetFileInputStream inputStream = new FileInputStream(excelFilePath);Workbook workbook = new XSSFWorkbook(inputStream);Sheet firstSheet = workbook.getSheetAt(0); // first sheet
// Generate maximum number of column in sheetint 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 sheetwhile (cellIterator.hasNext()) {Cell nextCell = cellIterator.next();int col1 = nextCell.getColumnIndex();for (int row = 1; row <= totalNoOfRows; row++) {if (col1 >= max)max = col1;}}}
// creating schemaArrayList 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 statementString 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 statementString insert = "INSERT INTO " + dbName + schema + "VALUES" + values + ";";statement = connection.prepareStatement(insert);
// Read excel dataIterator<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 methodsstatement.addBatch();statement.executeBatch();statement.clearBatch();}
// Closing workbook and database connectionworkbook.close();connection.commit();connection.close();
Originally published at https://www.devstringx.com on Oct 07, 2021.