execute multiple inserts with PreparedStatement - Java

Question

I am working on a project using Java + Connector/J + MySQL. I need to execute the following query using one PreparedStatement. Is it possible?

INSERT INTO FILES (FILENAME, FILESIZE, HASH) VALUES (a,b,c)
ON DUPLICATE KEY UPDATE FILEID=LAST_INSERT_ID(FILEID);
INSERT INTO DISTRIBUTES (PEERID, FILEID) VALUES (1,LAST_INSERT_ID());

When I try to execute this, I get an error. I know how to make batch executions of the same INSERT statement, but I can't make it run using multiple inserts in one statement.

The code in java to execute the above, is this:

        query = "INSERT INTO FILES (FILENAME, FILESIZE, HASHROOT, HASHLIST) VALUES (?,?,?,?)\n" +
                "ON DUPLICATE KEY UPDATE FILEID=LAST_INSERT_ID(FILEID);\n" +
                "INSERT INTO DISTRIBUTES (PEERID, FILEID) VALUES (" + peerID + ",LAST_INSERT_ID());";
        pstmt = null;

        try
        {
            pstmt = conn.prepareStatement(query);

            stmt.setString(1, files[i].getFileName());
            pstmt.setLong(2, files[i].getFileSize());
            pstmt.setString(3, /* Get some data here */);
            pstmt.setBytes(4, /* Get some data here */);
            pstmt.addBatch();
            pstmt.executeBatch();
        }
        catch (Exception ex)
        {
            // Error handling
        }
        finally
        {
            // Close resources
        }

EDIT: The error I get in the catch block, is saying "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 'INSERT INTO DISTRIBUTES (PEERID, FILEID) VALUES (16,LAST_INSERT_ID())' at line 3"

And what **is** the error you get? And don't swallow exceptions in the catch block. At least print them out! — , Mar 03 '12 at 17:08
Have you tried to execute these two statements separetely without ";" and "\n"? I'm not familiar with Connector/J, but these semicolon-separated rows doesn't seem good. — Gergely Bacso, Mar 03 '12 at 17:23
The '\n' is only for debugging purposes. The semicolon character is required by MySql, for executing queries in a batch. The statement is correct, I've tested it, through writing the query in MySQL Workbench. The problem is I think when trying to execute as a PreparedStatement — Bobos, Mar 03 '12 at 17:31
You could use `PreparedStatement#addBatch()` http://stackoverflow.com/a/4355097/384351 — Zheileman, Mar 03 '12 at 17:38

score 0 · Answer 1 · answered Mar 03 '12 at 17:34

You can only execute one statement with single preparedStatement. You have to split the sql into two separate insert into ... sequences.

Also

INSERT INTO DISTRIBUTES (PEERID, FILEID) VALUES (" + peerID + ",LAST_INSERT_ID());";

does not look like using a preparedStatment - it has hardcoded peerID instead of placeholder ?

execute multiple inserts with PreparedStatement - Java

1 Answers1

Linked