so I am working on a project in a part of the project we must read data from excel file and store it in database we use sqlite
with Java JDBC
but the data is big more that 25 thousands line as shown here
`{stm = conn.createStatement();
//Get the workbook instance for XLS file
HSSFWorkbook workbook = new HSSFWorkbook(file);
//Get first sheet from the workbook
HSSFSheet sheet = workbook.getSheetAt(0);
//Iterate through each rows from first sheet
Iterator<Row> rowIterator = sheet.iterator();
int hour=0;
Row row;
String collegename;
String coursename;
int courseSec;
int courseid;
String lecturer;
try{stm.executeUpdate("DROP TABLE temp_table;");}
catch(Exception e){}
stm.executeUpdate("CREATE TABLE \"temp_table\" (\n" +
"\"college_name\" TEXT,\n" +
"\"course_id\" INTEGER,\n" +
"\"course_sec\" INTEGER,\n" +
"\"course_name\" TEXT,\n" +
"\"lecturer\" TEXT\n" +
")\n" +
";");
while(rowIterator.hasNext()) {
row = rowIterator.next();
collegename=row.getCell(0).getStringCellValue();
courseid=(int)row.getCell(1).getNumericCellValue();
courseSec=(int)row.getCell(4).getNumericCellValue();
coursename=row.getCell(2).getStringCellValue();
lecturer = row.getCell(6).getStringCellValue();
stm.executeUpdate("INSERT INTO temp_table(\"college_name\" , \"course_id\" , \"course_sec\" , \"course_name\" , \"lecturer\") "
+ "VALUES(\"" + collegename + "\" ," + courseid + " , " + courseSec + " , \"" + coursename + "\",\"" + lecturer + "\");");
}
}`
and it takes over 30 mins but in navicat premium
I can import it in less than 5 seconds maximum
cant I make it faster?
Aucun commentaire:
Enregistrer un commentaire