java - 在 arraylist 中添加元素的 SQL 查询

Question

我已经写了一个方法 stub ，到目前为止，我在 PreparedStatement 中有这个 SQL 查询:

PreparedStatement ps = connection.prepareStatement("SELECT id,itemId,userId, sum(I.price) as totalAmount FROM dbo.Orders o 
join dbo.Items I on o.itemId = I.id join dbo.Users u on o.UserId = u.Id where o.id = ?");
            ps.setInt(1, id);

要求: 检索给定订单 ID 的不同 Items 值并将它们添加到 arrayList。

到目前为止，这是我的方法:

    public ArrayList<Orders> GetOrders(Connection connection) throws Exception
            {
                ArrayList<Orders> feedData = new ArrayList<Orders>();
            ArrayList<Integer> itemsId = new ArrayList<Integer>();
//How do I get items in this arraylist since there could be multiple items for a given order ?
            try
        {
            PreparedStatement ps = connection.prepareStatement("SELECT id,itemId, userId, sum(I.price) as totalAmount FROM dbo.Orders o join dbo.Items I on o.itemId = I.id join dbo.Users u on o.UserId = u.Id where o.id = ? group by o.id");
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            while(rs.next())
            {
                Orders o = new Orders();
                o.setId(rs.getInt("id"));
             //how do I add elements itemid's in itemsId array list here.?
                while(){

                }
                o.setItem(itemsId);
                o.setUserId(rs.getInt("price"));
                o.setTotalAmount(rs.getInt("totalAmount"));
                feedData.add(o);
            }
            return feedData;
        }
        catch(Exception e)
        {
            throw e;
        }
    }

编辑:

数据库模式:

create table Users
(
Id int not null primary Key identity(1,1) constraint fk_UserId_Id references Orders(UserId),
name nvarchar(50),
phone nvarchar(10)
);

create table Items
(
Id int not null primary Key identity(1,1) constraint fk_item_Id references Orders(ItemId),
name nvarchar(50),
Price int
);

create table Orders
(
id int not null primary Key identity(1,1),
ItemId int,
UserId int,
);

Answer 1

//这是你要找的吗？

PreparedStatement ps = connection.prepareStatement("
SELECT o.id,o.itemId,userId, sum(I.price) as totalAmount 
FROM dbo.Orders o 
INNER JOIN dbo.Items I on o.itemId = I.id 
INNER JOIN dbo.Users u on o.UserId = u.Id 
WHERE o.id = ?
GROUP BY o.id,o.itemId,u.userId");
 ps.setInt(1, id);