How to delete local SQLite database file in Android?

Question

I have a function that stores the data to the database, in MainActivity it gets called on each iteration

// Iterate through cats array and call a function to add to DB
for(int i = 0; i < cats.length(); i++) {
    try {
        JSONObject singleObject = cats.getJSONObject(i);
        dbHandler.insertData(singleObject.getInt("catid"), singleObject.getString("catname"), "category" );
    } catch (JSONException e) {
        e.printStackTrace();
    }
}
Log.d("length", String.valueOf(cats.length()));  

Log.d function shows the length of 17. It's the correct length. However, in my Database, in the first run, I immediately get duplicates for each data entry, i.e 34 rows in my db file, and on each run data entry increment by one. So on the next run, it's 68 rows in db file. Same with the second table, I call the same function but with different parameters. Below is my code for insertData function:

public class DBHandler extends SQLiteOpenHelper {

    // creating a constant variables for our database.
    // below variable is for our database name.
    private static final String DB_NAME = "allservices";

    // below int is our database version
    private static final int DB_VERSION = 1;

    // below variable is for our table name.
    private static final String TABLE_NAME1 = "category";
    private static final String TABLE_NAME2 = "services";

    // below variable is for our id column.
    private static final String ID_COL = "catid";

    // below variable is for our name column
    private static final String NAME_COL1 = "catname";
    private static final String NAME_COL2 = "servicename";

    // creating a constructor for our database handler.
    public DBHandler(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }

    // below method is for creating a database by running a sqlite query
    @Override
    public void onCreate(SQLiteDatabase db) {
        // on below line we are creating
        // an sqlite query and we are
        // setting our column names
        // along with their data types.
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME1);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME2);

        String query1 = "CREATE TABLE " + TABLE_NAME1 + " ("
                + ID_COL + " INTEGER, "
                + NAME_COL1 + " TEXT)";

        String query2 = "CREATE TABLE " + TABLE_NAME2 + " ("
                + ID_COL + " INTEGER, "
                + NAME_COL2 + " TEXT)";

        // at last we are calling a exec sql
        // method to execute above sql query
        db.execSQL(query1);
        db.execSQL(query2);
    }

    public void insertData(int id, String catname, String tableName) {

        // on below line we are creating a variable for
        // our sqlite database and calling writable method
        // as we are writing data in our database.
        SQLiteDatabase db = this.getWritableDatabase();

        // on below line we are creating a
        // variable for content values.
        ContentValues values = new ContentValues();

        // on below line we are passing all values
        // along with its key and value pair.
        if(tableName.equals(TABLE_NAME1)) {
            values.put(ID_COL, id);
            values.put(NAME_COL1, catname);
        } else if (tableName.equals(TABLE_NAME2)) {
            values.put(ID_COL, id);
            values.put(NAME_COL2, catname);
        }

        // after adding all values we are passing
        // content values to our table.
        if(tableName.equals(TABLE_NAME1)) {
            db.insert(TABLE_NAME1, null, values);
            values.clear();
        } else if (tableName.equals(TABLE_NAME2)) {
            db.insert(TABLE_NAME2, null, values);
            values.clear();
        }
        // at last we are closing our
        // database after adding database.
        db.close();
    }


    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // this method is called to check if the table exists already.
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME1);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME2);
        onCreate(db);
    }
}  

EDIT
As per the request, below I'm posting the full code for MainActivity.java

package com.example.servicecategory;

import androidx.appcompat.app.AppCompatActivity;

import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.widget.ExpandableListAdapter;
import android.widget.ExpandableListView;
import android.widget.TextView;
import android.widget.Toast;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

public class MainActivity extends AppCompatActivity {
    ExpandableListView expandableListView;
    ExpandableListAdapter expandableListAdapter;
    List<String> listTitles;
    HashMap<String, List<String>> listDetail;
    HashMap<String, List<String>>listDetails;

    private DBHandler dbHandler;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        dbHandler = new DBHandler(MainActivity.this);

        String str = LoaderHelper.parseFileToString(this, "response.json");
        Log.d("json", str);

        JSONObject jsonObj = null;
        try {
            jsonObj = new JSONObject(str);
        } catch (JSONException e) {
            e.printStackTrace();
        }
        // Getting JSONArray from the JSONObject
        JSONArray cats = null;
        try {
            cats = jsonObj.getJSONArray("cats");
        } catch (JSONException e) {
            e.printStackTrace();
        }
        // Getting JSONArray from the JSONObject
        JSONArray services = null;
        try {
            services = jsonObj.getJSONArray("services");
        } catch (JSONException e) {
            e.printStackTrace();
        }
        try {
            Log.d("json", String.valueOf(cats.getJSONObject(1)));
        } catch (JSONException e) {
            e.printStackTrace();
        }
        Log.d("json", String.valueOf(services));

        // Iterate through cats array and call a function to add to DB
        for(int i = 0; i < cats.length(); i++) {
            try {
                JSONObject singleObject = cats.getJSONObject(i);
                dbHandler.insertData(singleObject.getInt("catid"), singleObject.getString("catname"), "category" );
            } catch (JSONException e) {
                e.printStackTrace();
            }
        }
        Log.d("length", String.valueOf(cats.length()));
        // Iterate through services array and call a function to add to DB
        for(int i = 0; i < services.length(); i++) {
            try {
                JSONObject singleObject = services.getJSONObject(i);
                dbHandler.insertData(singleObject.getInt("catid"), singleObject.getString("servicename"), "services");
            } catch (JSONException e) {
                e.printStackTrace();
            }
        }

        expandableListView = findViewById(R.id.expendableList);
//        listDetail = ExpandableListData.getChildList();
//        listTitles = new ArrayList<>(listDetail.keySet());
        listTitles = dbHandler.readTitles();
        listDetails = dbHandler.readListDetail();
        Log.d("titles", String.valueOf(listTitles));
        Log.d("titles", String.valueOf(listDetails));
        expandableListAdapter = new CustomExpandableListAdapter(this, listTitles, listDetails);
        expandableListView.setAdapter(expandableListAdapter);

        expandableListView.setOnGroupExpandListener(new ExpandableListView.OnGroupExpandListener() {
            @Override
            public void onGroupExpand(int i) {
//                Toast.makeText(getApplicationContext(), listTitles.get(i) + " List Expanded.", Toast.LENGTH_SHORT).show();
            }
        });
        expandableListView.setOnGroupCollapseListener(new ExpandableListView.OnGroupCollapseListener() {
            @Override
            public void onGroupCollapse(int i) {
//                Toast.makeText(getApplicationContext(), listTitles.get(i) + "List Collapsed.", Toast.LENGTH_SHORT).show();
            }
        });
        expandableListView.setOnChildClickListener(new ExpandableListView.OnChildClickListener() {
            @Override
            public boolean onChildClick(ExpandableListView expandableListView, View view, int i, int i1, long l) {
//                Toast.makeText(getApplicationContext(), listTitles.get(i) + " -> " + listDetail.get(listTitles.get(i)).get(i1), Toast.LENGTH_SHORT).show();
                return false;
            }
        });
    }
}  

So the question remains, how can I DROP tables on each app run so that I recreate DB tables again and add the data?

Answer 1

Problem is you are putting your code in onCreate() so every time activity launched it will add one set of data. that's what you got duplicate data

solution

1. move the code into button click events inside
2. clear data before add the array list