Thread: Query database to retrieve a column for all menu Items in an ArrayList

Having trouble figuring out the code in order to find the price column for all items in a ArrayList

so far i have:

private double calculateSubtotal()
{
 
   try
      {
 
          myConnection = DriverManager.getConnection(DATABASE_URL);
          myStatement = myConnection.createStatement();
          myResultSet = myStatement.executeQuery("SELECT price FROM menu");
      }
      return 0;

instance variable code below for ArrayList

private ArrayList billItems = new ArrayList();

I am not sure if i am going about this the right way or not? if additional info is needed please ask

How is the ArrayList related to the data base? The ArrayList contains menu items which correspond to a specific column of a data base? If so, query the data base by the menu name or the place in the column, but you'll need to describe the relationship before a better answer can be given.

Originally Posted by GregBrannon

How is the ArrayList related to the data base? The ArrayList contains menu items which correspond to a specific column of a data base? If so, query the data base by the menu name or the place in the column, but you'll need to describe the relationship before a better answer can be given.

This is the rest of the question: This method should then calculate the total price of all the items in the ArrayList and return this value as a double.

Probably doesn't help?

If that's your question, it helps a lot. To sum the contents of the ArrayList, iterate it, parse the contents to numbers that can be added, and add them as you iterate. You will likely find assistance in the ArrayList and Integer or Double documentation. Come back when you have more specific questions about how to do what I've described.

Originally Posted by GregBrannon

If that's your question, it helps a lot. To sum the contents of the ArrayList, iterate it, parse the contents to numbers that can be added, and add them as you iterate. You will likely find assistance in the ArrayList and Integer or Double documentation. Come back when you have more specific questions about how to do what I've described.

This is what i have done:

private double calculateSubtotal()
   {
      double total = 0;
      String[] items = billItems;
 
      try
      {
          for(int i = 0; i < items.length; i++ )
        {
          myResultSet = myStatement.executeQuery("SELECT price FROM " +
                  "Menu WHERE name = '" +(String)items[i] + "'");
        }
          if(myResultSet.next() == true)
          {
              total += myResultSet.getDouble("Price");
          } // end of for loop
      catch ( SQLException sqlException )
      {
         sqlException.printStackTrace();
         System.exit( 1 );
      } // end catch
      }  
 
   } // end method calculateSubtotal

And how's that working for you? Is the following statement giving the desired result?

String[] items = billItems;

Why are you trying to do that? What type of objects exist (if any) in the ArrayList billItems? If you can answer the last, we can work to iterate those objects and add them together.

Originally Posted by GregBrannon

And how's that working for you? Is the following statement giving the desired result?

String[] items = billItems;

Why are you trying to do that? What type of objects exist (if any) in the ArrayList billItems? If you can answer the last, we can work to iterate those objects and add them together.

Its alright, i have it working now, thanks