Android Mileage App.3

Mileage Query

---

I have made a little progress on the Android app. I added code to calculate the most recent mileage. To do so I used the .rawQuery(), method of the SQLite Database Helper class.

In order to calculate the mileage I need to retrieve three values: the current mileage reading, the previous mileage reading and the current gallons. I also need to make sure that the values belong to the current vehicle.

To do this I used a subquery.

SELECT Miles, Gallons 
from Mileage 
where _id = 
(Select max(_id) from Mileage and Vehicle_id= vehicle)

The "vehicle" in the code is a variable that gets the vehicle_Id from the EditText control. The subquery is one of the reasons I used the .rawQuery() method. It would not be possible in the .query() method. Here is the code for the first query:

Cursor cursor=helper
                     .getReadableDatabase()
                     .rawQuery
("SELECT Miles, Gallons from Mileage where _id = (Select max(_id) from Mileage where Vehicle_id=" 
+ vehicle 
+ ")"
,null);

Once I have the cursor, I move it to the first record and extract the values:

if(cursor.moveToFirst())
{
     endMiles =   Double.parseDouble(cursor.getString(0));
     gallons = Double.parseDouble(cursor.getString(1));
 }

Here is the whole method. The method is added to the the click listener for the main activity button.

private void calculateMPG(){
                //this method calculates the mpg from the last two mileage entries for the vehicle
                //it could use some refactoring
                double endMiles=0;
                double gallons=0;
                double beginMiles=0;
                int vehicle = Integer.parseInt(carID.getText().toString());
                //calculate mileage if at least two values
                Cursor cursor=helper
                        .getReadableDatabase()
                        .rawQuery("SELECT Miles, Gallons from Mileage where _id = (Select max(_id) from Mileage where Vehicle_id=" + vehicle + ")",
                        null);


                if(cursor.moveToFirst())
                {
                    endMiles = Double.parseDouble(cursor.getString(0));
                    gallons = Double.parseDouble(cursor.getString(1));
                }

                cursor=helper.getReadableDatabase().rawQuery("SELECT Miles from Mileage where _id = (Select max(_id)-1 from Mileage where Vehicle_id=" + vehicle + ")", null);

                if(cursor.moveToFirst())
                {
                    beginMiles = Double.parseDouble(cursor.getString(0));

                }


                double mpg = (endMiles-beginMiles)/gallons;
                mpgResult.setText(Double.toString(mpg));
            }

Here is a picture of the app working. I need to work on the layout but the mileage calculation works

In the next part, I am going to create the activity the summarizes all the database data.