CODEWITHSUNDEEP
javamysqljdbcjbossjndi
user2727195
user2727195

Reputation: 7330

Chain MySQL statements under JBoss

EDIT: the linked answer doesn't use LAST_INSERT_ID, still looking for answer.

I'm using JBoss and getting connections via DataSource (JNDI).

How can I chain and execute multiple SQL statements in one go, where the second statement depends on the output of the first (LAST_INSERT_ID())

preparedStatement = connection.prepareStatement("INSERT INTO product(name) VALUES(?); INSERT INTO brand_product(brand_id, product_id) VALUES(?, LAST_INSERT_ID())", Statement.RETURN_GENERATED_KEYS);
preparedStatement.setString(1, name);
preparedStatement.setInt(2, brandId);

if(preparedStatement.executeUpdate() != 0) {
    try (ResultSet generatedKeys = preparedStatement.getGeneratedKeys()) {
        if(generatedKeys.next()) {
            product = new Product(generatedKeys.getInt(1), name);
        }
    }
};

Please note: I've generated keys in use as well, the second table is not generating any keys since it's a join table.

Upvotes: 1

Views: 110

Answers (1)

user2727195
user2727195

Reputation: 7330

  1. Configure JBoss to allow multiple Queries in admin interface. Click disable first before adding properties. (under DataSources).

    propertyName: allowMultiQueries property value: true

  2. Generated keys will not have an issue since the second query doesn't have Auto increment primary keys.

Upvotes: 1

Related Questions