Java- Cannot insert batch of 1000 to table

Question

Let's say I have a program where I am inserting records into MYSQL table in ddatabase in Java.

Instead of inserting row by row, I insert by a batch of 1000 records. Using ExecuteBatch method, it doesn't seem to work as it still inserts row by row.

Code(only the snippet):

 public void readDataBase(String path,String word) throws Exception {
        try {


            Class.forName("com.mysql.jdbc.Driver");

            connect = DriverManager
                    .getConnection("jdbc:mysql://126.32.3.20/fulltext_ltat?"
                            + "user=root&password=root");
   String sql="insert IGNORE into  fulltext_ltat.indextable values (default,?, ?) ";

            preparedStatement = connect.prepareStatement(sql);

        for(int i=0;i<1000;i++) {
            preparedStatement.setString(1, path);
            preparedStatement.setString(2, word);
            preparedStatement.addBatch();



            if (i % 1000 == 0) {

                preparedStatement.executeBatch();
                System.out.print("Add Thousand");
            }

        }










        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                preparedStatement.close();
                connect.close();
            }
            catch (SQLException e) {
                e.printStackTrace();
            }

        }

    }

Code: Main method calling the above

public static void main(String[] args) throws Exception {

    StopWatch stopwatch = new StopWatch();
    stopwatch.start();


    File folder = new File("D:\\PDF1");
    File[] listOfFiles = folder.listFiles();

    for (File file : listOfFiles) {
        if (file.isFile()) {
            HashSet<String> uniqueWords = new HashSet<>();
            String path = "D:\\PDF1\\" + file.getName();
            try (PDDocument document = PDDocument.load(new File(path))) {

                if (!document.isEncrypted()) {

                    PDFTextStripper tStripper = new PDFTextStripper();
                    String pdfFileInText = tStripper.getText(document);
                    String lines[] = pdfFileInText.split("\\r?\\n");
                    for (String line : lines) {
                        String[] words = line.split(" ");

                        for (String word : words) {
                            uniqueWords.add(word)
                            ;

                        }

                    }
                    // System.out.println(uniqueWords);

                }
            } catch (IOException e) {
                System.err.println("Exception while trying to read pdf document - " + e);
            }
            Object[] words = uniqueWords.toArray();



            MysqlAccessIndex connection = new MysqlAccessIndex();

            for(int i = 1 ; i <= words.length - 1 ; i++ ) {

                connection.readDataBase(path, words[i].toString());

            }

            System.out.println("Completed");

        }
    }

The moment I run the program, the if statement is always executing rather than checking if there are 1000 records then only execute to insert to db.

Am I doing anything wrong?

Answer 1

In the configuration property url add: allowMultiQueries=true

Answer 2

i % 1000 == 0 is true when i==0, so you only execute the batch in the first iteration of the loop.

You should execute the batch after the loop:

    for (int i=0;i<1000;i++) {
        preparedStatement.setString(1, path);
        preparedStatement.setString(2, word);
        preparedStatement.addBatch();
    }
    preparedStatement.executeBatch();
    System.out.print("Add Thousand");

Now, if you had 10000 records, and you wanted to execute batch insert every 1000, you could write:

    for (int i=0;i<10000;i++) {
        preparedStatement.setString(1, path);
        preparedStatement.setString(2, word);
        preparedStatement.addBatch();
        if ((i + 1) % 1000 == 0) {
            preparedStatement.executeBatch();
            System.out.print("Add Thousand");
        }
    }

EDIT: In order not to insert the same word multiple times to the table, pass an array to your method:

Change

        for(int i = 1 ; i <= words.length - 1 ; i++ ) {
            connection.readDataBase(path, words[i].toString());
        }

to

        connection.readDataBase(path, words);

and

public void readDataBase(String path,String word) throws Exception {

to

public void readDataBase(String path,String[] words) throws Exception {

and finally the batch insert loop would become:

    for (int i=0;i<words.length;i++) {
        preparedStatement.setString(1, path);
        preparedStatement.setString(2, words[i]);
        preparedStatement.addBatch();
        if ((i + 1) % 1000 == 0) {
            preparedStatement.executeBatch();
            System.out.print("Add Thousand");
        }
    }
    if (words.length % 1000 > 0) {
        preparedStatement.executeBatch();
        System.out.print("Add Remaining");
    }