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");
}
}
2 Answers
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.
where is your loop. try this
connect = DriverManager
.getConnection("jdbc:mysql://126.32.3.20/fulltext_ltat?"
+ "user=root&password=root&rewriteBatchedStatements=true");
