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

Question

我正在开发一个库存管理系统，我正在研究允许用户更新订单的最后一部分。此更新需要从订单中添加或删除项目。我现在正在做加法部分。我有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;
}

这里是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 Updatedn");
return order;
}

我在这里看到了类似的东西，但不太确定如何适应它。我知道我只是在拐角处从便士下降，因为我想使用ItemDAO.read(Long id)方法我在ItemDAO类，但我不知道如何使用该方法设置Item对象的属性。

刚开始使用DAO模型和JDBC，所以如果已经有一个答案，我找不到它，所以如果我得到正确的方向，我会非常感激。

编辑:这是ItemDAOread()方法和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;
}

OrderController:

@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 Updatedn");
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 Updatedn");
return order;
}
return null;
}

这具有添加物品和删除物品的完整功能，删除物品数量达到0的任何订单。