SQL query to add elements to arraylist
I wrote a stub method where I have this SQL queru in PreparedStatement:
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);
Requirements: to get different values of elements for a given order Id and add them to the list of arrays.
This is what my method looks like:
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 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;
}
}
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,
);
+3
source to share
2 answers
// Is this what you are looking for?
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);
0
source to share
Basically you need to go through all the returned records, even if you only want to load one order object.
This is a quick layout I did, so hold on to criticism, yes it could be implemented better.
public ArrayList<Orders> GetOrders( Connection connection ) throws Exception {
ArrayList<Orders> feedData = new ArrayList<Orders>();
int id = 0; // your Order ID
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();
ArrayList<Integer> itemsId = new ArrayList<Integer>();
Orders o = new Orders();
boolean populateOrder = true;
while( rs.next() ) {
itemsId.add( rs.getInt( "itemId" ) );
if( populateOrder ) {
o.setId( rs.getInt( "id" ) );
o.setUserId( rs.getInt( "price" ) );
o.setTotalAmount( rs.getInt( "totalAmount" ) );
feedData.add( o );
populateOrder = false; // make sure this If executes only once
}
}
o.setItem( itemsId );
return feedData;
} catch( Exception e ) {
throw e;
}
}
The above example has one big limitation and this only works for single orders (one order ID). If you want to upload more than one order, the code must be changed.
0
source to share