Java - executebatch is not working when inserting records to db

Question

I am trying to insert records into my table in MYSQL after extracting the words from a file and stored them in a hashset.

I tried using executeBatch() to insert into my db after getting 500 records but when the execution finished, I checked my table and there's no record inserted at all.

Note: When I use ExecuteUpdate() then the records will show in my table. But not ExecuteBatch() since I want to insert by batch, not one by one. May I know what did I do wrong?

Code:

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

            // Result set get the result of the SQL query

            int i=0;
            // This will load the MySQL driver, each DB has its own driver
            Class.forName("com.mysql.jdbc.Driver");
            // Setup the connection with the DB
            connect = DriverManager
                    .getConnection("jdbc:mysql://126.32.3.20/fulltext_ltat?"
                            + "user=root&password=root");

            // Statements allow to issue SQL queries to the database
          //  statement = connect.createStatement();
            System.out.print("Connected");
            // Result set get the result of the SQL query

            preparedStatement = connect
                    .prepareStatement("insert IGNORE into  fulltext_ltat.indextable values (default,?, ?) ");

            preparedStatement.setString(  1, path);
            preparedStatement.setString(2, word);
            preparedStatement.addBatch();
            i++;
           // preparedStatement.executeUpdate();

            if(i%500==0){
                preparedStatement.executeBatch();

            }




         preparedStatement.close();



            //  writeResultSet(resultSet);
        } catch (Exception e) {
            throw e;
        } finally {
            close();
        }

    }

This is my loop to call that method(words is just an array that contains the words which is going to be inserted to the table):

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

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

                }

My main method:

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");

            }
        }

Answer 1

Your pattern for doing batch updates is off. You should be opening the connection and preparing the statement only once. Then, iterate multiple times, binding parameters, and add that statement to the batch.

// define a collection of paths and words somewhere
List<String> paths = new ArrayList<>();
List<String> words = new ArrayList<>();

try {
    // presumably you only want to insert so many records
    int LIMIT = 10000;
    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 < LIMIT; ++i) {
        preparedStatement.setString(1, paths.get(i));
        preparedStatement.setString(2, word.get(i));
        preparedStatement.addBatch();
        if (i % 500 == 0) {
            preparedStatement.executeBatch();
        }
    }

    // execute remaining batches
    preparedStatement.executeBatch();
}
catch (SQLException e) {
    e.printStackTrace();
}
finally {
    try {
        preparedStatement.close();
        connect.close();
    }
    catch (SQLException e) {
        e.printStackTrace();
    }
}

One key change I made here is to add logic for when you should stop doing inserts. Currently, your code looks to have an infinite loop, which means it would run forever. This is probably not what you were intending to do.

Answer 2

where is your loop. try this

 connect = DriverManager
                .getConnection("jdbc:mysql://126.32.3.20/fulltext_ltat?"
                        + "user=root&password=root&rewriteBatchedStatements=true");