java - SQL查询在java中使用arraylist创建arraylist

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 中。到目前为止，我的方法如下所示:但嵌套子查询对我来说看起来是多余的。有更好的方法吗？所以，基本上，我试图将数据库中的值获取到数组列表中。按照表顺序，每个 userId 可以有多个项目，因此我尝试创建 arraylist 的 arraylist ，其中内部 araylist 包含每个 userId 的所有项目，外部 arraylist 包含 userid 和相应的字段。

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 ?
                public ArrayList<Orders> GetOrders(Connection connection, int id) throws Exception
            {
                ArrayList<Orders> feedData = new ArrayList<Orders>();
                ArrayList<Integer> itemsId = new ArrayList<Integer>();
                try
                {
                    PreparedStatement ps = connection.prepareStatement("SELECT o.id, o.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);
                    ResultSet rs = ps.executeQuery();
                    while(rs.next())
                    {
                        Orders o = new Orders();
                        o.setId(rs.getInt("id"));
                        ps = connection.prepareStatement("SELECT itemId FROM dbo.Orders o where o.id = ?");
                        ps.setInt(1, id);
                        ResultSet rs2 = ps.executeQuery();
                        while(rs2.next()){
                        itemsId.add(rs.getInt("itemId"));
                        }
                        o.setItem(itemsId);
                        o.setUserId(rs.getInt("price"));
                        o.setTotalAmount(rs.getInt("totalAmount"));
                        feedData.add(o);
                    }
                    return feedData;
                }
                catch(Exception e)
                {
                    throw e;
                }
            }


    }



Edit:

Database schema:

    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

根据我对您的要求的理解，给定订单 ID，您需要获取商品列表及其详细信息。以下 SQL 足以获取项目列表:

SELECT Id, name, Price from Items where id in (select ItemId from Orders where id = ?)

接下来的事情是；您想要返回包含项目详细信息的 ArrayList 的 ArrayList，而是可以考虑使用 Items 类对象来存储项目详细信息元素并归还。例如，您可以像这样简化您的需求:

public ArrayList<Items> getItems(Connection connection, int orderId) throws Exception
{
    ArrayList<Items> itemsList = new ArrayList<Items>();
    try
    {
        String query = "SELECT Id, name, Price from Items where id in "
                + "(select ItemId from Orders where id = ?)";
        PreparedStatement ps = connection.prepareStatement(query);
        ps.setInt(1, orderId);
        ResultSet rs = ps.executeQuery();
        while(rs.next())
        {
            int itemId = rs.getInt(1);
            String itemName = rs.getString(2);
            double itemPrice = rs.getDouble(3);
            Items item = new Items(itemId, itemName, itemPrice);
            itemsList.add(item);
        }
    }
    catch(Exception e)
    {
        e.printStackTrace();
    }
    return itemsList;
}

以下可能是您可以存储和检索项目详细信息的 Items 类:

public class Items {
    private int itemId;
    private String itemName;
    private double itemPrice;
    public Items(int itemId, String itemName, double itemPrice){
        this.itemId = itemId;
        this.itemName = itemName;
        this.itemPrice = itemPrice;
    }
    public int getItemId() {
        return itemId;
    }
    public void setItemId(int itemId) {
        this.itemId = itemId;
    }
    public String getItemName() {
        return itemName;
    }
    public void setItemName(String itemName) {
        this.itemName = itemName;
    }
    public double getItemPrice() {
        return itemPrice;
    }
    public void setItemPrice(double itemPrice) {
        this.itemPrice = itemPrice;
    }
}