Java mysql prepared statement update not working

Question

This is my code:

ResultSet rs = statement.executeQuery("select * from page"); PreparedStatement updatepage = mySqlCon.prepareStatement("update enwiki.page set enwiki.page.Text = ? where page_id = ?"); int count = 0; while (rs.next()) { int id = rs.getInt("id"); String text = rs.getString("text"); if(text == null) text = ""; updatepage.setString(1, text); updatepage.setInt(2, id); if(count++ > 10000) { updatepage.executeUpdate(); updatepage.clearBatch(); count = 0; } } updatepage.executeUpdate();

The problem is after the line : updatepage.executeUpdate() is run, I check the database using workbench and I don't see any changes on that table.

Luiggi Mendoza · Accepted Answer · 2015-05-07 20:22:50Z

Your current code is only executing the update when the value of count is greater than 10000, and it executes a single update. Seems like you want/need to use a batch processing, so you have to add the statements into the batch on every iteration (something you're not doing) and execute all the statements in the batch inside your if (something you're not doing either).

The code will be like this:

while (rs.next()) { int id = rs.getInt("id"); String text = rs.getString("text"); if(text == null) text = ""; updatepage.setString(1, text); updatepage.setInt(2, id); //add the current statement to the batch updatepage.addBatch(); if(count++ > 10000) { //line below will only execute the current statement (useless) //updatepage.executeUpdate(); //line below will clear the batch statements (useless too) //updatepage.clearBatch(); updatepage.executeBatch(); count = 0; } } //updatepage.executeUpdate(); updatepage.executeBatch();

@Andi is your connection set in autocommit mode? If not, you will have to add Connection#commit after all your operations are executed.
Yes, I tried both ways, autocommit and normal, none of them worked.
Oh, I think it is working, it is taking time. I thought eclipse had crashed!
I suggest to print a message every few times (e.g. every 1000 statements set in batch) to check the progress of the batch statements (=

user21 · Accepted Answer · 2015-05-07 20:34:03Z

Can you give paste the whole code ? From the code what you have provided updatepage.addBatch();/ updatepage.executeBatch(); is missing. Also check if dbConnection.setAutoCommit(false);.

Collectives™ on Stack Overflow

Java mysql prepared statement update not working

2 Answers 2

4 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Related