How to Import Excel Data to SQLite DB Using Java | Devstringx

Import Excel Data to SQLite DB Using Java

How to Import Excel Data to SQLite DB Using Java | Devstringx

Prerequisites:

SQLite, Java, Database

What it covers?

  1. Creating and closing database connection (JDBC)
  2. Creating dynamic schema
  3. Creating a table and deleting table values
  4. Reading data from excel
  5. Importing excel data to DB

How does it work?

  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 a 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;

PreparedStatementstatement = 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

FileInputStreaminputStream = newFileInputStream(excelFilePath);

Workbook workbook = newXSSFWorkbook(inputStream);

Sheet firstSheet = workbook.getSheetAt(0);     // first sheet

//Generate maximum number of column in sheet

intmax=0;

Iterator<Row>rowIterator = firstSheet.iterator();

while (rowIterator.hasNext()) {

Row nextRow = rowIterator.next();

Iterator<Cell>cellIterator = nextRow.cellIterator();

inttotalNoOfRows = firstSheet.getLastRowNum(); // To get the number of rows present in sheet

while (cellIterator.hasNext()) {

Cell nextCell = cellIterator.next();

intcol1 = nextCell.getColumnIndex();

for (introw = 1; row<= totalNoOfRows; row++) {

if (col1>= max)

max = col1;

}

}

}

// creating schema

ArrayListsch = newArrayList();

ArrayListval = newArrayList();

for (intfirst = 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 createstatement

String table = “CREATE TABLE ” + dbName + schema + “;”;

Statement stmt = connection.createStatement();

stmt.executeUpdate(table);

“Read Also:- How to Prevent SQL Injection | SQL Injection Vulnerability

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()) {

intcellCount = 0;

Row nextRow = rowIterator.next();

ArrayList<String>data = newArrayList<String>();

data.clear();

Iterator<Cell>cellIterator = nextRow.cellIterator();

while (cellIterator.hasNext()) {

Cell nextCell = cellIterator.next();

intcol = nextCell.getColumnIndex();

if (nextCell.getCellType() == CellType.STRING) {

data.add(cellCount, nextCell.getStringCellValue());

}

elseif (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>

Share this post