I'm developing an inventory management system and I'm working on the final part of allowing users to update orders. This update requires items to be added or removed from the order. I'm working on adding the part now. I've tested the MySQL query that works in 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));
In Java, I know that I need to create an Item object based on the ID passed in above. This is 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; }
This is the 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; }
I saw something similar here but not quite sure how to adapt it. I know I'm about to drop a price because I'm considering using the ItemDAO.read(Long id)
method in the ItemDAO class, but I don't know how to set the properties of the Item
object using that method.
New to the DAO model and JDBC so if there is already an answer I can't find it so I would be grateful if I could point me in the right direction.
edit:
This is the ItemDAO read()
method and the modelFromResults()
method:
@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); }
solution:
OrderDAO:
Order Controller:
It has full functionality to add items and delete items, as well as delete any order with a quantity of 0 items.