Saya perlu mencipta objek menggunakan id objek daripada pangkalan data MySQL

Question

Saya sedang membangunkan sistem pengurusan inventori dan saya sedang mengusahakan bahagian terakhir untuk membenarkan pengguna mengemas kini pesanan. Kemas kini ini memerlukan item ditambah atau dialih keluar daripada pesanan. Saya sedang berusaha untuk menambah bahagian itu sekarang. Saya telah menguji pertanyaan MySQL yang berfungsi dalam Workbench:

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

Di Java, saya tahu bahawa saya perlu mencipta objek Item berdasarkan ID yang diluluskan di atas. Ini 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;
    }

Ini adalah pengawal:

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

Saya melihat sesuatu yang serupa di sini tetapi tidak pasti cara menyesuaikannya. Saya tahu saya akan menandakan kerana saya mempertimbangkan untuk menggunakan kaedah menggunakan sifat objek ItemDAO.read(Long id) 方法，但我不知道如何设置 Item dalam kelas ItemDAO.

Baru kepada model DAO dan JDBC jadi jika sudah ada jawapan saya tidak dapat mencarinya jadi saya akan berterima kasih jika saya dapat menunjukkan saya ke arah yang betul.

Sunting: Ini adalah kaedah 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

Penyelesaian:

TempahanDAO:

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

Pengawal Pesanan:

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

Ia mempunyai fungsi penuh untuk menambah item dan mengalih keluar item, serta memadam sebarang pesanan dengan 0 kuantiti item.