java- How to set rollback function on inserting records to table?

Question

I have a program where I extract some records from a PDF file, then I proceed to insert those records into a table in MySQL.

One of my main concern is if there is an error under any circumstances during the inserting to table. Let's say if I am inserting 1000 records from a file into the table and halfway, something bad happens. So does it auto rollback or do I need to include a "Begin Transaction and Commit Transaction" statement?

If so, how do I initiate a rollback inside Java? I am thinking of writing a rollback function just to achieve this.

My code:

 public void index(String path) throws Exception {

     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(" ");
             String sql="insert IGNORE into test.indextable values (?,?)";
             // con.connect().setAutoCommit(false);
             preparedStatement = con.connect().prepareStatement(sql);
             int i=0;
             for (String word : words) {
                 // check if one or more special characters at end of string then remove OR
                 // check special characters in beginning of the string then remove
                // insert every word directly to table db
                word=word.replaceAll("([\\W]+$)|(^[\\W]+)", "");
                preparedStatement.setString(1, path);
                preparedStatement.setString(2, word);
                preparedStatement.addBatch();
                i++;
                if (i % 1000 == 0) {
                    preparedStatement.executeBatch();
                    System.out.print("Add Thousand");
                }  
            }
            if (i > 0) {
                preparedStatement.executeBatch();
                System.out.print("Add Remaining");
            }

        }

    }

    // con.connect().commit();
    preparedStatement.close();
    System.out.println("Successfully commited changes to the database!");
}

This function above will be called by another function to be executed and the try and catch exception is in the caller function.

My rollback function:

 // function to undo entries in inverted file on error indexing
 public void rollbackEntries() throws Exception {
     con.connect().rollback();
     System.out.println("Successfully rolled back changes from the database!");
 }

I appreciate any suggestions.

Answer 1

I don't know what library you are using, so I am just going to guess on exception names and types. If you look in the api you can check to see what exceptions are thrown by what functions.

private final static String INSERT_STATMENT = "insert IGNORE into test.indextable values (?,?)";

public void index(String path) { // Don't throw the exception, handle it.
     PDDocument document = null;
     try {
         document = PDDocument.load(new File(path));
     } catch (FileNotFoundException e) {
         System.err.println("Unable to find document \"" + path "\"!");
         return;
     }

     if (document == null || document.isEncrypted()) {
             System.err.println("Unable to read data from document \"" + path "\"!");
             return;
     }

     String[] lines = null;

     try {
         PDFTextStripper stripper = new PDFTextStripper(); 
         lines = stripper.getText(document).split("\\r?\\n");
     } catch (IOException e) {
         System.err.println("Could not read data from document \"" + path "\"! File may be corrupted!");
         return;
     }

     // You can add in extra checks just to test for other specific edge cases
     if (lines == null || lines.length < 2) {
         System.err.println("Only found 1 line in document \"" + path "\"! File may be corrupted!");
         return;
     }


     for (String line : lines) {
         PreparedStatement statement = con.connect().prepareStatement(INSERT_STATMENT );
             String[] words = line.split(" ");

             for (int index = 0, executeWait = 0; index < words.length; index++, executeWait++) {
                preparedStatement.setString(1, path);
                preparedStatement.setString(2, words[index].replaceAll("([\\W]+$)|(^[\\W]+)", ""));
                preparedStatement.addBatch();

                // Repeat this part again like before
                if (executeWait % 1000 == 0) {
                    for (int timeout = 0; true; timeout++) {
                        try {
                            preparedStatement.executeBatch();
                            System.out.print("Pushed " + (((executeWait - 1) % 1000) + 1) + " statements to database.");
                            break;
                        } catch (ConnectionLostException e) {
                            if (timeout >= 5) {
                                System.err.println("Unable to resolve issues! Exiting...");
                                return;
                            }
                            System.err.println("Lost connection to database! Fix attempt " + (timeout + 1) + ". (Timeout at 5)");
                            con.reconnect();
                        } catch (SqlWriteException error) {
                            System.err.println("Error while writing to database. Rolling back changes and retrying. Fix attempt " + (timeout + 1) + ". (Timeout at 5)");
                            rollbackEntries();
                            if (timeout >= 5) {
                                System.err.println("Unable to resolve issues! Exiting...");
                                return;
                            }
                        }
                    }
                }  
            }

        }

    try {
        preparedStatement.close();
    } catch (IOException e) {
        // Do nothing since it means it was already closed. 
        // Probably throws an exception to prevent people from calling this method twice.
    }
    System.out.println("Successfully committed all changes to the database!");
}

There are definitely a few more exceptions which you will need to account for which I didn't add.

---

Edit: Your specific issue can be found at this link