title: SQLite Database query and AsyncTask
body: <p>Hi Anddroid Experts!!!</p>
  
  <p>I am currently writing an app that have SQLite database and MPcharts. The idea is that I created a SQLite database with a few database tables and pulling data out of database and create MP charts. I used a similar method in different app and it works fine. In this case, the query is little more complicated and the app stopped with no data showing on my charts. After investigating online, I feel like that AsyncTask would help me on this issue. I am not sure how to apply AsyncTask to my case. Basically, link the Arraylist xDataMonthCategory() and Arraylist yDataPieChartCategoryMonth() to the Piechart and have the SQLite query runs in background using AsyncTask. I tested my query and I know the data is there.   Please help me. Many many thanks!!!</p>
  
  <p>Here is MP Piechart Java Class:</p>
  
  <pre><code>        import android.graphics.Color;
      import android.os.Bundle;
      import android.support.design.widget.FloatingActionButton;
      import android.support.design.widget.Snackbar;
      import android.support.v7.app.AppCompatActivity;
      import android.support.v7.widget.Toolbar;
      import android.view.View;
  
      import com.github.mikephil.charting.charts.PieChart;
      import com.github.mikephil.charting.components.Legend;
      import com.github.mikephil.charting.data.Entry;
      import com.github.mikephil.charting.data.PieData;
      import com.github.mikephil.charting.data.PieDataSet;
      import com.github.mikephil.charting.formatter.PercentFormatter;
  
      import java.util.ArrayList;
  
  public class MTDPieCharts extends AppCompatActivity {
  
  @Override
  protected void onCreate(Bundle savedInstanceState) {
      super.onCreate(savedInstanceState);
      setContentView(R.layout.activity_mtdpie_charts);
      Toolbar toolbar = (Toolbar) findViewById(R.id.toolbar);
      setSupportActionBar(toolbar);
  
      FloatingActionButton fab = (FloatingActionButton) findViewById(R.id.fab);
      fab.setOnClickListener(new View.OnClickListener() {
          @Override
          public void onClick(View view) {
              Snackbar.make(view, "Replace with your own action", Snackbar.LENGTH_LONG)
                      .setAction("Action", null).show();
          }
      });
      MTDPieChartCategory();
      //MTDPieChartItems();
  }
  
  //MTD Category PieChart
  public void MTDPieChartCategory() {
  
      DataBaseHelper databasehelper = new DataBaseHelper(MTDPieCharts.this);
      PieChart MTDcategoryChart = new PieChart(this);
      MTDcategoryChart = (PieChart) findViewById(R.id.MTDPieChartCategory);
      //get yVals and datasets of MTD Category chart
      ArrayList&lt;Entry&gt; yValsCategoryAMT = new ArrayList&lt;Entry&gt;();
      for(int i = 0; i &lt; databasehelper.yDataPieChartCategoryMonth().size(); i++)
          yValsCategoryAMT.add(new Entry(databasehelper.yDataPieChartCategoryMonth().get(i), i));
  
      //get xVals of MTD Category chart
      ArrayList&lt;String&gt; xValsCategory = new ArrayList&lt;String&gt;();
      for(int i = 0; i &lt; databasehelper.xDataMonthCategory().size(); i++)
          xValsCategory.add(databasehelper.xDataMonthCategory().get(i));
  
      PieDataSet MTDCategorySet = new PieDataSet(yValsCategoryAMT, "");
  
      //set yvals attribute
      MTDCategorySet.setSliceSpace(3);
      MTDCategorySet.setSelectionShift(5);
  
      //add many colors
      MTDCategorySet.setColors(new int[]{R.color.BlueA100, R.color.Cyan500, R.color.colorAccent, R.color.DeepPurpleA200, R.color.BlueA100, R.color.Teal200, R.color.Pink200, R.color.Pink500, R.color.purple},this);
  
      // configue pie chart
      MTDcategoryChart.setUsePercentValues(true);
      MTDcategoryChart.setRotationEnabled(true);
      MTDcategoryChart.setRotationAngle(0);
      MTDcategoryChart.setDescriptionPosition(350, 550);
      MTDcategoryChart.setDrawHoleEnabled(true);
      MTDcategoryChart.setHoleColor(Color.TRANSPARENT);
      MTDcategoryChart.setHoleRadius(13);
      MTDcategoryChart.setTransparentCircleRadius(18);
      MTDcategoryChart.getLegend().setPosition(Legend.LegendPosition.RIGHT_OF_CHART);
      //MTDcategoryChart.getLegend().setXEntrySpace(3);
      // MTDcategoryChart.getLegend().setYEntrySpace(3);
      MTDcategoryChart.getLegend().setTextColor(Color.BLUE);
      MTDcategoryChart.setDescription("Category");
      MTDcategoryChart.setDescriptionTextSize(13);
      MTDcategoryChart.setDescriptionColor(Color.BLUE);
  
      //get category chart data
      PieData MTDCategoryPieData = new PieData(xValsCategory, MTDCategorySet);
      MTDCategoryPieData.setValueFormatter(new PercentFormatter());
      MTDCategoryPieData.setValueTextSize(11);
      MTDCategoryPieData.setValueTextColor(Color.BLUE);
      MTDcategoryChart.setData(MTDCategoryPieData);
  
  
      //refresh data
      MTDcategoryChart.invalidate();
  
  }
  </code></pre>
  
  <p>Database helper class:</p>
  
  <pre><code>    import android.content.ContentValues;
      import android.content.Context;
      import android.database.Cursor;
      import android.database.SQLException;
      import android.database.sqlite.SQLiteDatabase;
      import android.database.sqlite.SQLiteOpenHelper;
      import android.util.Log;
  
      import java.text.SimpleDateFormat;
      import java.util.ArrayList;
      import java.util.Date;
  
        //create category table
  public static final String Create_Table_Category="create table if not exists "+
          TableData.TableInfo.DataBase_Table_Category+  "(" +
          TableData.TableInfo.COL_C1_CategoryID+" integer primary key autoincrement,"+
          TableData.TableInfo.COL_C2_Description+" text not null);";
  
          //create table spending
          public static final String Create_Table_Spending="create table if not exists "+
          TableData.TableInfo.DataBase_Table_Spending+" ("+TableData.TableInfo.COL_S1_SpendingID+" integer primary key autoincrement,"+
          TableData.TableInfo.COL_S2_Spending_DT+" Datetime not null,"+
          TableData.TableInfo.COL_S3_ItemID+" integer not null,"+
          TableData.TableInfo.COL_S4_AMT+" Float not null," +
          "FOREIGN key ("+TableData.TableInfo.COL_S3_ItemID+") REFERENCES " +TableData.TableInfo.DataBase_Table_Items+" ("+TableData.TableInfo.COL_I1_ItemID+") ON DELETE CASCADE);";
  
  // create table Item
  
  public static final String Create_Table_Items="Create table if not exists " +
          TableData.TableInfo.DataBase_Table_Items+" ("+
          TableData.TableInfo.COL_I1_ItemID+" integer primary key autoincrement,"+
          TableData.TableInfo.COL_I2_Description+" Text not null,"+
          TableData.TableInfo.COL_I3_CategoryID+" integer not null,"+
  
          "FOREIGN key ("+TableData.TableInfo.COL_I3_CategoryID+") REFERENCES " +TableData.TableInfo.DataBase_Table_Category+ " ("+TableData.TableInfo.COL_C1_CategoryID+") ON DELETE CASCADE);";
     // create table Users
  public static final String Created_Table_Users="create table if not exists "+
         TableData.TableInfo.DataBase_Table_Users+ " ("+ TableData.TableInfo.COL_U1_UserID+" integer primary key autoincrement ,"+
         TableData.TableInfo.COL_U2_UserName+" text not null ,"+ TableData.TableInfo.COL_U3_Password+" Text not null, "+
         TableData.TableInfo.COL_U4_Created_DT+" Datetime not null);";
  
  public static final String Drop_table_Category="drop table if exists "+TableData.TableInfo.DataBase_Table_Category;
  public static final String Drop_table_Items="drop table if exists "+TableData.TableInfo.DataBase_Table_Items;
  public static final String Drop_table_Spending="drop table if exists "+TableData.TableInfo.DataBase_Table_Spending;
  public static final String Drop_table_Users="drop table if exists "+ TableData.TableInfo.DataBase_Table_Users;
  
  SQLiteDatabase db;
  // declare variables
  public static final String YearEndPieChartMonth="YearEndPieChartMonth";
  public static final YearEndPieChartMonthValues=  String                    "YearEndPieChartMonthValues";
  public static final String MonthPieChartCategory="MonthPieChartCategory";
  public static final String MonthPieCHartCategoryValues="MonthPieCHartCategoryValues";
  public static final String MonthPieChartItems="MonthPieChartItems";
  public static final String MonthPiechartItemsValues="MonthPiechartItemsValues";
  public static final String YearEndPieChartCategory="YearEndPieChartCategory";
  public static final String YearEndCategoryValues="YearEndCategoryValues";
  public static final String YearEndPieChartItems="YearEndPieChartItems";
  public static final String YearEndItemsValues="YearEndItemsValues";
  
  public DataBaseHelper(Context context) {
      super(context, TableData.TableInfo.DataBase_Name, null, TableData.TableInfo.DataBase_version);
  }
  
  
  
  @Override
  public void onCreate(SQLiteDatabase db) {
      //create Category table
      db.execSQL(Create_Table_Category);
      Log.d("Database operation:", "Category table created");
      //insert data into Category table
      String sql_category="INSERT INTO " +       TableData.TableInfo.DataBase_Table_Category+" ("+     TableData.TableInfo.COL_C2_Description+" ) 
    VALUES('Housing'),('Utilities'),('Household'),('Groceries'),('Living'),('Transportation'),('HealthCare'),('Personal'),('EatingOut'),('Entertainments'),('Children'),('DebtPayments'),('Savings')";
      db.execSQL(sql_category);
      Log.d("Category table:","Data inserted");
  
      //create Items table
      db.execSQL(Create_Table_Items);
      Log.d("Datbase operation:", "Item table created ");
      // insert data into Item table
      String sql_Items="insert into "+ TableData.TableInfo.DataBase_Table_Items+ " ("+ TableData.TableInfo.COL_I2_Description+" ,"+ TableData.TableInfo.COL_I3_CategoryID+" )  values('Rent',1),('Mortgage',1),('StrataFees',1),('PropertyTaxes',1),('HouseInsurance',1),('Phone',2),('Cell',2),('CableTVInternet',2),('GAS',2),('Hybro',2), ('Security',2),('Electricity',2),('Water',2),('StorageLocker',3),('Decor',3),('Gardening',3),('Garbage',3),('MiscItems',3),('Food',4),('BabyNeeds',4),('Cleaning',4),('Toiletries',4),('Kitchenwares',4),('PersonalCare',5),('BankFees',5), ('DryCleaning',5),('PetCosts',5),('Fuel',6),('AutoInsurance',6),('Transit',6),('Parking',6),('Taxi',6),('MedicalPremiums',7),('LifeInsurance',7),('Medication',7),('EyeCare',7),('Dental',7),('WellnessCosts',7),('Tobacco',8),('Alcohol',8),('BooksCDs',8),('Donations',8),('Subscriptions',8),('Meals',9),('Snacks',9),('TakeOut',9),('CoffeeWater',9),('SportsEquipmentFees',10),('Movies',10),('Hobbies',10),('Gaming',10),('FitnessMemberships',10),('DayCare',11),('LessonsActivities',11),('Allowance',11),('SchoolSuppliesFees',11),('Babysitting',11),('Loans',12),('CreditCards',12),('Leases',12),('SupportPayments',12),('GovernmentDebts',12),('EmergencyFund',13),('RRSP',13),('RESPs',13),('TravelVacations',13),('CarRepairs',13),('CLothing',13), ('Gifts',13),('AssistingFamilyFriends',13)";
      db.execSQL(sql_Items);
      Log.d("Items","Data inserted");
      //create spending table
      db.execSQL(Create_Table_Spending);
      Log.d("Database operation:", "Spending table created ");
      db.execSQL(Created_Table_Users);
  }
  
  @Override
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
      db.execSQL(Drop_table_Category);
      db.execSQL(Drop_table_Items);
      db.execSQL(Drop_table_Spending);
      db.execSQL(Drop_table_Users);
      onCreate(db);
  
  }
  public DataBaseHelper open() throws SQLException {
      db = this.getWritableDatabase();
      return this;
  }
  
  public void close() {
      db.close();
  }
  
  public SQLiteDatabase getDatabaseInstance() {
      return db;
  }
  
  public String getTime(){
      SimpleDateFormat dateFormat=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
      Date date=new Date();
  
      return dateFormat.format(date);
  }
  
  
   //current month pie chart category
  public ArrayList&lt;String&gt; xDataMonthCategory(){
      ArrayList&lt;String&gt; xnewDataCategory=new ArrayList&lt;String&gt;();
      SQLiteDatabase db=this.getReadableDatabase();
      String sql="select distinct c."+ TableData.TableInfo.COL_C2_Description+" as MonthPieChartCategory from " + TableData.TableInfo.DataBase_Table_Spending+" s join "+ TableData.TableInfo.DataBase_Table_Items+" i on i."+ TableData.TableInfo.COL_I1_ItemID+"=s."+ TableData.TableInfo.COL_S3_ItemID+" join "+ TableData.TableInfo.DataBase_Table_Category+" c on c."+ TableData.TableInfo.COL_C1_CategoryID+"=i."+ TableData.TableInfo.COL_I3_CategoryID+" where strftime('%Y',"+ TableData.TableInfo.COL_S2_Spending_DT+")=strftime('%Y',date('now')) and strftime('%m',"+ TableData.TableInfo.COL_S2_Spending_DT+")=strftime('%m',date('now')) group by  c."+ TableData.TableInfo.COL_C2_Description+" order by  c."+ TableData.TableInfo.COL_C2_Description+" desc";
      Cursor cursor=db.rawQuery(sql,null);
      for(cursor.moveToFirst();!cursor.isAfterLast();cursor.moveToLast()) {
          xnewDataCategory.add(cursor.getString(cursor.getColumnIndex(MonthPieChartCategory)));
      }
      cursor.close();
      return xnewDataCategory;
  
  }
  //current month pie chart category y values
  public ArrayList&lt;Float&gt; yDataPieChartCategoryMonth(){
      ArrayList&lt;Float&gt; ynewDataPieCHartCategoryMonth=new ArrayList&lt;Float&gt;();
      SQLiteDatabase db=this.getReadableDatabase();
      String sql="select sum("+ TableData.TableInfo.COL_S4_AMT+") MonthPieCHartCategoryValues,c."+ TableData.TableInfo.COL_C2_Description+" Category from "+ TableData.TableInfo.DataBase_Table_Spending+" s join "+ TableData.TableInfo.DataBase_Table_Items+" i on i."+ TableData.TableInfo.COL_I1_ItemID+"=s."+ TableData.TableInfo.COL_S3_ItemID+ " join "+ TableData.TableInfo.DataBase_Table_Category+" c on c."+ TableData.TableInfo.COL_C1_CategoryID+"=i."+ TableData.TableInfo.COL_I3_CategoryID + " where strftime('%Y',"+ TableData.TableInfo.COL_S2_Spending_DT+")=strftime('%Y',Date('now')) and strftime('%m',"+ TableData.TableInfo.COL_S2_Spending_DT+")=strftime('%m',date('now')) group by c."+ TableData.TableInfo.COL_C2_Description+" order by Category  desc";
      Cursor cursor=db.rawQuery(sql,null);
      for(cursor.moveToFirst();!cursor.isAfterLast();cursor.moveToLast()){
          ynewDataPieCHartCategoryMonth.add(cursor.getFloat(cursor.getColumnIndex(MonthPieCHartCategoryValues)));
      }
      cursor.close();
      return ynewDataPieCHartCategoryMonth;
  
  }
  </code></pre>