Java and Transactions

[Kryptix]

I run a little game and when it loops through saving the players progress it wipes their inventory with the following query:

 

databaseConnection.updateQuery("DELETE FROM `rscd_invitems` WHERE `user` = '" + save.getUsernameHash() + "'");

 

It then checks to see if they have any items in their inventory, and if so, loops through extending the query:

 

if (save.getInventory().size() > 0) {

query = "INSERT INTO `rscd_invitems` (`user`, `id`, `amount`, `wielded`, `slot`) VALUES ";

int slot = 0;

for (InvItem item : save.getInventory().getItems()) {

query += "('" + save.getUsernameHash() + "', '" + item.getID() + "', '" + item.getAmount() + "', '" + (item.isWielded() ? 1 : 0) + "', '" + (slot++) + "'), ";

}

databaseConnection.updateQuery(query.substring(0, query.length() - 2));

}

 

This is absolutely fine except if the server crashes half way through the process it leaves them with a wiped inventory.

 

Today someone told me about transactions but I'm not entirely sure how to use them. I thought I could do something like this:

 

START TRANSACTION; DELETE FROM `rscd_invitems` WHERE `user` = '918'; INSERT INTO `rscd_invitems` (`user`, `id`, `amount`, `wielded`, `slot`) VALUES ('918', '1', '1', '0', '0'); COMMIT;

 

The problem is, with Java you need to do it differently as you can't put multiple queries on one line. Does anyone know how to do this?

 

I appreciate that it's kind of a Java question but someone here might know. I've seen a few examples but don't really understand them, all the examples I've found are quite different from what I'm trying to do.

[jskywalker]

I found an example which might help

http://java.exampleshow.com/sourcecode/Inserting_BLOB_Values_with_setBlob_Method

 

You do know that MySQL only works with transactions is you use InnoDB-tables?