mardi 3 mai 2016

make sqlite queries run faster with java

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