Mysql 2 insert queries

Question

I am trying to execute 2 queries.

First one should insert data (especially "product") or update in case the db already has a row with such title.

Second one should insert new category for product which was inserted\updated from 1st query and ignore any inserts, if table already has such product with such category

Here is my code :

 conn = DatabaseConnection.getConnection();
 stmt = conn.createStatement();
 conn.setAutoCommit(false);

 String updateSQL = "INSERT INTO product (title, price, `status`) " +
                "VALUES(?, ?, ?)" +
                "ON DUPLICATE KEY UPDATE price = ?, `status` = ?;"

 PreparedStatement preparedStatement = conn.prepareStatement(updateSQL);
 preparedStatement.setString(1, product.getTitle());
 preparedStatement.setBigDecimal(2, product.getPrice());
 preparedStatement.setInt(3, product.getStatus().ordinal());
 preparedStatement.executeUpdate();

 updateSQL = "INSERT IGNORE INTO product_categories (product_id, category_id) " +
             "VALUES (last_insert_id(), ?);";
 preparedStatement = conn.prepareStatement(updateSQL);
 preparedStatement.setLong(1, categoryId);
 preparedStatement.executeUpdate();
 conn.commit();

So, the problem is that I use last_insert_id() which means that i will use incorrect row in 2nd query if 1st query just updated the data.

So, I would like to know how could I synchronize these 2 queries.

yeap, i just forgot to copy that. I can't clearly understand, what is "fetch" "[See this question for details]" is just a string, it's not a link — quento, Feb 22 '16 at 19:22
(Sorry - forgot to paste the link) Fetch the last_insert_id from your first query, and only execute the second query if it exists. [See this question for details](http://stackoverflow.com/questions/5513180/java-preparedstatement-retrieving-last-inserted-id) — Kenney, Feb 22 '16 at 19:28

score 1 · Accepted Answer · edited May 23 '17 at 10:28

Since you don't have access to last_insert_id() in the second query, you'll have to fetch it as in the answers for this question.

Here's an example:

...
preparedStatement.executeUpdate();    // this is the first query

ResultSet rs = preparedStatement.getGeneratedKeys();
if ( rs.next() )
{
    long last_insert_id = rs.getLong(1);

    updateSQL = "INSERT IGNORE INTO product_categories (product_id, category_id) " +
            "VALUES (?, ?);";
    preparedStatement = conn.prepareStatement(updateSQL);
    preparedStatement.setLong(1, last_insert_id);
    preparedStatement.setLong(2, categoryId);
    preparedStatement.executeUpdate();
}
conn.commit();

If the first query didn't result in an INSERT, then there isn't enough information to add the product to the product_category, in which case this is skipped all together. This does assume that the product is already in the category. If you're not sure about that, and want to execute the second query regardless, you could query for the product_id:

SELECT id FROM product WHERE title = ?

and then use that id instead of the last_insert_id variable, or, you could change the second query and use title as a key (although I'd stick with an id):

INSERT IGNORE INTO product_categories (product_id, category_id) 
VALUES (SELECT id FROM product WHERE title = ?), ?)

Mysql 2 insert queries

1 Answers1