我需要使用 MySQL 数据库中的对象 id 创建一个对象

Question

我正在开发库存管理系统，我正在研究允许用户更新订单的最后一部分。此更新需要在订单中添加或删除商品。我现在正在研究添加部分。我已经测试过可以在 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);
    }

Answer 1

解决方案：

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 的任何订单。