将两条SQL语句合并为一条，以便在满足某些条件时更新表中的行

Question

我正在开发一个使用JDBC更新库存和下订单的应用程序。

我正在存储产品，如果请求的数量小于存储的数量，我想更新产品，如果数量等于数据库中当前库存的数量，则我想从数据库中删除产品。

我使用了两种不同的语句，但我只想使用其中一种。例如，如果我想将订单添加到DB中，系统将请求的内容是名称和产品数量。产品数量将从DB上产品的总数量中减去。伪代码将是

IF product quantity - user quantity =0 THEN DELETE product FROM database
ELSE UPDATE product quantity TO product quantity-user quantity ON THE database
product quantity=quantity of the product in the database
user quantity=quantity requested by the user

我现在准备的声明是这两份

UPDATE products SET quantity=quantity-? WHERE product_name=?
DELETE FROM products WHERE product_name=?

如果可能的话，我想把它们合并为一个

Answer 1

在生产系统中，您会做这种事情。

正如你所说，为了一个订单，就这样做。

UPDATE products SET quantity=quantity-? WHERE product_name=?

然后，在夜间或每周的清理中，这样做可以清除没有剩余数量的行。

DELETE FROM products WHERE quantity = 0

当你想知道实际有哪些产品可用时，你可以进行

SELECT product_name, quantity FROM products WHERE quantity > 0

这里的概念是：数量为零的行是"不可见的"，即使它们没有被删除。

如果这是我的系统，我不会删除行。首先，当你有更多的产品库存时会发生什么？

Answer 2

一种方法是通过在连接URL中将MySQL配置属性allowMultiQueries设置为true来放松安全性。

然后您可以一起执行两个SQL语句：

String sql = "UPDATE products" +
" SET quantity = quantity - ?" +
" WHERE product_name = ?" +
" AND quantity >= ?" +
";" +
"DELETE FROM products" +
" WHERE product_name = ?" +
" AND quantity = 0";
try (PreparedStatement stmt = conn.prepareStatement(sql)) {
stmt.setInt(1, userQuantity);
stmt.setString(2, productName);
stmt.setInt(3, userQuantity);
stmt.setString(4, productName);
stmt.execute();
int updateCount = stmt.getUpdateCount();
if (updateCount == 0)
throw new IllegalStateException("Product not available: " + productName);
// if you need to know if product got sold out, do the following
stmt.getMoreResults();
int deleteCount = stmt.getUpdateCount();
boolean soldOut = (deleteCount != 0);
}