Daniel Cisek
Daniel Cisek

Reputation: 931

Many delete queries in single JDBC query

I'm having a problem with in my class with generating delete command:

    private String generateDeleteCommand() {
    StringBuilder deleteCommand = new StringBuilder();
    for (ForeignKey fk : exportedForeignKeys) {
        deleteCommand.append("DELETE FROM ").append(fk.foreignTableName)
                .append(" WHERE ").append(fk.foreignColumnName)
                .append("=:").append(fk.primaryColumnName).append(";\n");
    }
    deleteCommand.append("DELETE FROM ").append(tableName)
            .append(" WHERE ");
    for (String key : primaryKeys.keySet()) {
        deleteCommand.append(key).append("=:").append(key).append(" AND ");
    }
    deleteCommand
            .delete(deleteCommand.length() - 5, deleteCommand.length());
    deleteCommand.append(";");
    System.out.println(deleteCommand);
    return deleteCommand.toString();
}

The query I get is valid when using in phpmyadmin - but when I'll try to use it with jdbc executeUpdate() i get MySQLSyntaxError, i.e. for table "trasy" with two exported foreign keys query looks like:

DELETE FROM kursy WHERE ID_TRASY=19;
DELETE FROM przystanki WHERE ID_TRASY=19;
DELETE FROM trasy WHERE ID_TRASY=19;

Exception:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your    SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELETE FROM przystanki WHERE ID_TRASY=19;
DELETE FROM trasy WHERE ID_TRASY=19' at line 2

It doesn't matter whether there is \n between the queries or no.

Upvotes: 2

Views: 1426

Answers (2)

user177800
user177800

Reputation:

Use the .addBatch() method on Statement and add each DELETE query separately and .executeBatch() them as a batch.

If using transactions, you can test the counts returned by each statement, and if anything is wrong, you can .rollback() the entire batch.

I have an open source project that shows exactly how to do this.

SQL Construction Kit on GitHub, there is an AbstractDatabase.java class that has an .executeBatch() method that you can copy and use yourself with very little modification. It even has the code for testing each command and doing the commit/rollback.

Upvotes: 5

snajahi
snajahi

Reputation: 910

jdbc executes one statement at a time, so your statements, even if are multiple seperated by \n, are in fact executed as one instruction hence the error from mysql.

Upvotes: 1

Related Questions