我正在開發庫存管理系統，我正在研究允許用戶更新訂單的最後一部分。此更新需要在訂單中新增或刪除商品。我現在正在研究添加部分。我已經測試過可以在 Workbench 中執行的 MySQL 查詢：
INSERT INTO order_items (item_quantity, fk_item_id, fk_order_id) VALUES (1, (SELECT item_id FROM items WHERE item_id= 1), (SELECT order_id FROM orders WHERE order_id=2));
在 Java 中，我知道需要根據上面傳入的 ID 建立一個 Item 物件。 這是 OrderDAO：
public Order addItem(Order order) {
ItemDAO itemDao = new ItemDAO();
try (Connection connection = DBUtils.getInstance().getConnection();
PreparedStatement statement = connection.prepareStatement("INSERT INTO order_items (item_quantity, fk_item_id, fk_order_id) VALUES (?, (SELECT item_id FROM items WHERE item_id = ?), (SELECT order_id FROM orders WHERE order_id = ?));");) {
statement.setInt(1, order.getItemQuantity());
statement.setLong(2, order.getItemId());
itemDao.read(order.getItemId());
statement.setLong(3, order.getOrderId());
statement.executeUpdate();
System.out.println(order);
return read(order.getOrderId());
} catch (Exception e) {
LOGGER.debug(e);
LOGGER.error(e.getMessage());
}
return null;
}
這是控制器：
@Override
public Order update() {
LOGGER.info("Please enter the id of the order you would like to update");
Long id = utils.getLong();
// LOGGER.info("Would you like to add or delete an item from an order");
LOGGER.info("Please enter the ID of the item you wish to add");
Long itemId = utils.getLong();
LOGGER.info("Please enter the quantity of the item to add");
int quantity = utils.getInt();
Item item = new Item(itemId);
System.out.println(item);
Order order = orderDAO.addItem(new Order(item, quantity, id));
LOGGER.info("Order Updated\n");
return order;
}
我在這裡看到了類似的東西，但不太確定如何適應它。我知道我即將降價，因為我考慮使用 ItemDAO 類別中的 ItemDAO.read(Long id) 方法，但我不知道如何設定 Item 物件的屬性使用該方法。
剛接觸 DAO 模型和 JDBC，所以如果已經有答案，我找不到它，所以如果我能找到正確的方向，我將不勝感激。
編輯：
這是 ItemDAO read() 方法和 modelFromResults() 方法：
@Override
public Item read(Long id) {
try (Connection connection = DBUtils.getInstance().getConnection();
PreparedStatement statement = connection.prepareStatement("SELECT * FROM items WHERE item_id = ?");) {
statement.setLong(1, id);
try (ResultSet resultSet = statement.executeQuery();) {
resultSet.next();
return modelFromResultSet(resultSet);
}
} catch (Exception e) {
LOGGER.debug(e);
LOGGER.error(e.getMessage());
}
return null;
@Override
public Item modelFromResultSet(ResultSet resultSet) throws SQLException {
Long itemID = resultSet.getLong("item_id");
String itemName = resultSet.getString("item_name");
double itemCost = resultSet.getDouble("item_cost");
return new Item(itemID, itemName, itemCost);
}
解決方案：
OrderDAO：
public Order orderItemsFromResultSet(ResultSet rs) throws SQLException { Long orderId = rs.getLong("order_items_id"); Long itemId = rs.getLong("item_id"); String itemName = rs.getString("item_name"); double itemCost = rs.getDouble("item_cost"); Item item = new Item(itemId, itemName, itemCost); Order order = new Order(item, orderId); return order; } @Override public Order read(Long id) { try (Connection connection = DBUtils.getInstance().getConnection(); PreparedStatement statement = connection.prepareStatement("SELECT * FROM order_items LEFT OUTER JOIN items ON items.item_id = order_items.fk_item_id WHERE fk_order_id = ?;");) { statement.setLong(1, id); try (ResultSet resultSet = statement.executeQuery();) { resultSet.next(); return orderItemsFromResultSet(resultSet); } } catch (Exception e) { LOGGER.debug(e); LOGGER.error(e.getMessage()); } return null; } public Order addItem(Order order) { try (Connection connection = DBUtils.getInstance().getConnection(); PreparedStatement statement = connection.prepareStatement("INSERT INTO order_items (item_quantity, fk_item_id, fk_order_id) VALUES (?, (SELECT item_id FROM items WHERE item_id = ?), (SELECT order_id FROM orders WHERE order_id = ?));");) { statement.setInt(1, order.getItemQuantity()); statement.setLong(2, order.getItemId()); statement.setLong(3, order.getOrderId()); statement.executeUpdate(); return read(order.getOrderId()); } catch (Exception e) { LOGGER.debug(e); LOGGER.error(e.getMessage()); } return null; } public Order removeItem(Order order) { try (Connection connection = DBUtils.getInstance().getConnection(); PreparedStatement statementOne = connection.prepareStatement("UPDATE order_items SET item_quantity = item_quantity - 1 WHERE item_quantity >= 0 && fk_item_id = ? && fk_order_id = ?"); PreparedStatement statementTwo = connection.prepareStatement("DELETE FROM order_items WHERE item_quantity = 0");) { statementOne.setLong(1, order.getItemId()); statementOne.setLong(2, order.getOrderId()); statementOne.executeUpdate(); statementTwo.executeUpdate(); } catch (Exception e) { LOGGER.debug(e); LOGGER.error(e.getMessage()); } return null; }訂單控制器：
@Override public Order update() { LOGGER.info("Please enter the id of the order you would like to update"); Long id = utils.getLong(); LOGGER.info("Would you like to add or delete an item from an order"); String addOrDelete = utils.getString(); addOrDelete = addOrDelete.toLowerCase(); if (addOrDelete.equals("add")) { LOGGER.info("Please enter the ID of the item you wish to add"); Long itemId = utils.getLong(); LOGGER.info("Please enter the quantity of the item to add"); int quantity = utils.getInt(); Item item = new Item(itemId); ItemDAO itemDao = new ItemDAO(); item = itemDao.read(item.getItemID()); Order order = orderDAO.addItem(new Order(item.getItemID(), quantity, id)); LOGGER.info("Order Updated\n"); return order; } else if (addOrDelete.equals("delete")) { LOGGER.info("Please enter the id of the item you wish to remove"); Long itemId = utils.getLong(); Order order = new Order(); order.setOrderId(id); order.setItemId(itemId); orderDAO.removeItem(order); LOGGER.info("Order Updated\n"); return order; } return null; }它具有添加商品和刪除商品的完整功能，以及刪除商品數量為 0 的任何訂單。