SQL query to create arraylist with in arraylist in java

Question

I have written a method stub where in I have this SQL query in PreparedStatement so far:

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

Requirement: to retrieve different values of Items for given order Id and add them to an arrayList.Here is what my method looks like so far: But having nested subquery looks redundant to me. Is there a better way to do it? So, basically, I am trying to get values from database into an arraylist. In table order, there can be multiple items per userId , so I am trying to create an arraylist of arraylist , where inner araylist contains all items per userId and outer arraylist contains userid and corresponding fields.

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

Actually yours question is not getting correct..

But for order id and its item purpose you can use this method

For database

Customer id need to be linked with order id and order would be contain 3 to 4 items so for that you should create diffrent table for item, use orderid for the each item also and give column for orderlinenumber buy that you can retrieve orderitems in invoice

customerId OrderId OrderDate

115 101 15jun2014

item.table

oderId OrderLineNumber itemName qty price

101 1 mouse 5 15.26 101 2 Keyborad 3 40.36 101 3 computer 2 1425.23

Answer 2

Based on my understanding of your requirement, given a order id you need to get the list of items & their details. Following SQL will be sufficient to get the list of items:

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

Next thing is; you want to return the ArrayList of ArrayList which contains the item details, instead you can think of using a Items class object to store the details of Items and return them. For example, you can simplify your requirement like this:

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

And following could be your Items class which you can store and retrieve item details:

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