Updating Database in Background task (Android)

Question

My app makes use of SQLiteDatabase to save two arraylists to separate tables.

I have noticed that since implementing the database, whenever it updates the database (involving dropping the tables, recreating them, then populating them with the arraylists) the app briefly freezes and I get the following message in logcat: "I/Choreographer: Skipped 236 frames! The application may be doing too much work on its main thread."

To confirm it was the updating, I removed the code used to update the database. Upon doing that, I no longer got the warning message, and my app didn't freeze.

This is the code inside my custom DB helper, which extends SQLiteOpenHelper, that is used to update the table:

 public void insertData(ArrayList<SavedWifiHotspot> hotspots, ArrayList<MarkerOptions> markers) {
    Log.d("insert LocationsDB", "Data inserted");
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues hotspotValues = new ContentValues();
    ContentValues markerValues = new ContentValues();
    for(SavedWifiHotspot hotspot : hotspots) {
        hotspotValues.put("Ssid", hotspot.getSsid());
        hotspotValues.put("Password", hotspot.getPassword());
        hotspotValues.put("LocationName", hotspot.getHotspotLoc());
        hotspotValues.put("Lat", hotspot.getLatitude());
        hotspotValues.put("Lng", hotspot.getLongitude());
        db.insert(HOTSPOT_TABLE_NAME, null, hotspotValues);
    }
    for(MarkerOptions marker : markers) {
        markerValues.put("LocationName", marker.getTitle());
        markerValues.put("Lat", marker.getPosition().latitude);
        markerValues.put("Lng", marker.getPosition().longitude);
        db.insert(LOCATION_TABLE_NAME, null, markerValues);
    }
}

And this is the code used to clear the tables before they are updated:

public void clearData() {
    Log.d("clear LocationsDB", "Tables cleared");
    SQLiteDatabase db=this.getWritableDatabase();

    String dropHSTable = "DROP TABLE IF EXISTS "
            + HOTSPOT_TABLE_NAME + ";";

    String dropLocTable = "DROP TABLE IF EXISTS "
            + LOCATION_TABLE_NAME + ";";

    db.execSQL(dropHSTable);
    db.execSQL(dropLocTable);

    createTables(db);
}

How should I go about updating my database in the background? I've read about threads, should I use a thread to do this?

Edit: This is the error, in reference to my comment.

FATAL EXCEPTION: AsyncTask #5
Process: com1032.cw2.fm00232.fm00232_assignment2, PID: 8830
java.lang.RuntimeException: An error occured while executing doInBackground()
  at android.os.AsyncTask$3.done(AsyncTask.java:300)
  at java.util.concurrent.FutureTask.finishCompletion(FutureTask.java:355)
  at java.util.concurrent.FutureTask.setException(FutureTask.java:222)
  at java.util.concurrent.FutureTask.run(FutureTask.java:242)
  at android.os.AsyncTask$SerialExecutor$1.run(AsyncTask.java:231)
  at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1112)
  at java.util.concurrent.ThreadPoolExe
  at java.lang.Thread.run(Thread.java:818)cutor$Worker.run(ThreadPoolExecutor.java:587)
Caused by: java.util.ConcurrentModificationException
  at java.util.ArrayList$ArrayListIterator.next(ArrayList.java:573)
  at com1032.cw2.fm00232.fm00232_assignment2.LocationsDB$3.doInBackground(LocationsDB.java:124)
  at at com1032.cw2.fm00232.fm00232_assignment2.LocationsDB$3.doInBackground(LocationsDB.java:119)
  at android.os.AsyncTask$2.call(AsyncTask.java:288)
  at java.util.concurrent.FutureTask.run(FutureTask.java:237)
  at android.os.AsyncTask$SerialExecutor$1.run(AsyncTask.java:231)
  at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1112)
  at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:587)
  at java.lang.Thread.run(Thread.java:818)

For reference, line 124 is:

for(MarkerOptions marker: markers[0]) {

And line 119 is:

new AsyncTask<ArrayList<MarkerOptions>, Void, Void>() {

Edit2: Fixed the above problem. My app was invoking the insert data method using empty lists. So I've added .empty check before the insert data method.

Do you mean the code used by the Activity class to access the database? — Isengrim, May 19 '16 at 18:22

Cory Roy · Accepted Answer · 2016-05-19T16:52:18.847

An async task sounds like a good idea.

public void insertData(ArrayList<SavedWifiHotspot> hotspots, ArrayList<MarkerOptions> markers) {
Log.d("insert LocationsDB", "Data inserted");

new AsyncTask<ArrayList<SavedWifiHotspot>, Void, Void>() {

        @Override
        protected Void doInBackground(ArrayList<SavedWifiHotspot>... hotspots) {
            ContentValues hotspotValues = new ContentValues();
            for(SavedWifiHotspot hotspot : hotspots[0]) {
                hotspotValues.put("Ssid", hotspot.getSsid());
                hotspotValues.put("Password", hotspot.getPassword());
                hotspotValues.put("LocationName", hotspot.getHotspotLoc());
                hotspotValues.put("Lat", hotspot.getLatitude());
                hotspotValues.put("Lng", hotspot.getLongitude());
                db.insert(HOTSPOT_TABLE_NAME, null, hotspotValues);
            }

        }
    }.execute(hotspots);

new AsyncTask<ArrayList<MarkerOptions>, Void, Void>() {

        @Override
        protected Void doInBackground(ArrayList<MarkerOptions>... options) {
            ContentValues hotspotValues = new ContentValues();
            for(MarkerOptions marker: options[0]) {
                markerValues.put("LocationName", marker.getTitle());
                markerValues.put("Lat", marker.getPosition().latitude);
                markerValues.put("Lng", marker.getPosition().longitude);
                db.insert(LOCATION_TABLE_NAME, null, markerValues);
            }

        }
    }.execute(options);
}


public void clearData() {
    Log.d("clear LocationsDB", "Tables cleared");
    new AsyncTask<Void, Void, Void>() {
        @Override
        protected Void doInBackground(Void... params) {
            SQLiteDatabase db=this.getWritableDatabase();

           String dropHSTable = "DROP TABLE IF EXISTS "
               + HOTSPOT_TABLE_NAME + ";";

           String dropLocTable = "DROP TABLE IF EXISTS "
               + LOCATION_TABLE_NAME + ";";

           db.execSQL(dropHSTable);
           db.execSQL(dropLocTable);
           createTables(db);
        }
   }.execute();


}

Is this something fairly easy to learn how to use? Basically this app I'm making is for coursework, so I'm a little limited for time. — Isengrim, May 19 '16 at 16:36
It's pretty much done for you. If it crashes post the stacktrace and I can help you sort it out. — Cory Roy, May 19 '16 at 16:51
Seems to work just fine. I am having a problem with closing my database. Usually I close the databases when the activity pauses, but this now seems to cause insertData to try and access a closed database, even if I check us db.isOpen in an if statement before invoking insertData. Also this warning is showing over the doInBackground methods "Unchecked generics array creation for varargs parameter". — Isengrim, May 19 '16 at 18:45
Are you opening a reference to the db in the `onResume` method? — Cory Roy, May 19 '16 at 19:46
Yeah, in my onResume I have `if(!db.isOpen())` to check if the db is open, and if not then it executes `db = locDB.getReadableDatabase();`. — Isengrim, May 19 '16 at 19:50
Look at this http://stackoverflow.com/questions/10677781/how-to-open-close-sqlite-db-in-android-properly for how to deal with opening and closing the database. You should be using a helper class. — Cory Roy, May 19 '16 at 19:55

score 0 · Answer 2 · answered Jun 28 '22 at 10:16

you are doing this task on MainThread which freezes the UI. this is the reason for getting I/Choreographer: Skipped 236 frames! The application may be doing too much work on its main thread. warning.

doing something on a background thread might be the best solution.

First solution: using Executors to make a new thread:

create a class like this:

source

public class AppExecutors {

    private final Executor mDiskIO;

    private final Executor mNetworkIO;

    private final Executor mMainThread;

    private AppExecutors(Executor diskIO, Executor networkIO, Executor mainThread) {
        this.mDiskIO = diskIO;
        this.mNetworkIO = networkIO;
        this.mMainThread = mainThread;
    }

    public AppExecutors() {
        this(Executors.newSingleThreadExecutor(), Executors.newFixedThreadPool(3),
                new MainThreadExecutor());
    }

    public Executor diskIO() {
        return mDiskIO;
    }

    public Executor networkIO() {
        return mNetworkIO;
    }

    public Executor mainThread() {
        return mMainThread;
    }

    private static class MainThreadExecutor implements Executor {
        private Handler mainThreadHandler = new Handler(Looper.getMainLooper());

        @Override
        public void execute(@NonNull Runnable command) {
            mainThreadHandler.post(command);
        }
    }
}

this class has three usable methods that might come in handy:

diskIO is the best usecase for working with database, as it has only ONE thread, so calling multiple calls to the database won't cause problem and all the jobs assigned to the database will be done sequentially.

you can use it like this:

AppExecutors.getInstance().mDiskIO().execute {
    someIntensiveAndtimeConsumingTask()
}

keep in mind that you need to switch back to mainThread if you want to deal with UI components:

AppExecutors.getInstance().mDiskIO().execute {

String textViewText = getSomeStringThatTakesTime()

AppExecutors.getInstance().mMainThread().execute {
    awesomeTextView.setText(textViewText)
    }
}

also notice that you can use AppExecutors.getInstance().mNetworkIO() to handle network requests. the difference is that mDiskIO uses a newSingleThreadExecutor which instantiates a single thread. but the mNetworkIO uses a pool of threads that enables you to handle multiple requests at the same time.

second solutions: Kotlin Coroutines getting started guide

lifeCycleScope.launch(Dispatchers.IO) {

    val dataString = someIntensiveTaskOnDatabase()
    //like what we did on the first solution, we need to switch to mainThread to do modifications on UI elements
    withContext(Dispatchers.Main) {
    textView.text = dataString
    }
}

Updating Database in Background task (Android)

2 Answers2