Ich muss ein Objekt mit einer Objekt-ID aus einer MySQL-Datenbank erstellen

Question

Ich entwickle ein Bestandsverwaltungssystem und arbeite am letzten Teil, der es Benutzern ermöglicht, Bestellungen zu aktualisieren. Für dieses Update müssen Artikel zur Bestellung hinzugefügt oder daraus entfernt werden. Ich arbeite gerade daran, das Teil hinzuzufügen. Ich habe eine MySQL-Abfrage getestet, die in Workbench funktioniert:

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));

In Java weiß ich, dass ich ein Item-Objekt basierend auf der oben übergebenen ID erstellen muss. Das ist 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;
    }

Das ist der Controller:

@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;
    }

Ich habe hier etwas Ähnliches gesehen, bin mir aber nicht ganz sicher, wie ich es anpassen soll. Ich wusste, dass ich gleich einen Preis herabsetzen würde, weil ich überlegte, die Methode unter Verwendung der Eigenschaften des ItemDAO.read(Long id) 方法，但我不知道如何设置 Item-Objekts in der ItemDAO-Klasse zu verwenden.

Neu im DAO-Modell und JDBC. Wenn es also bereits eine Antwort gibt, kann ich sie nicht finden. Ich wäre dankbar, wenn ich mich in die richtige Richtung weisen könnte.

Bearbeiten: Dies ist die ItemDAO read() 方法和 modelFromResults()-Methode:

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