What are the best practices for SQLite on Android?

Question

What would be considered the best practices when executing queries on an SQLite database within an Android app?

Is it safe to run inserts, deletes and select queries from an AsyncTask's doInBackground? Or should I use the UI Thread? I suppose that database queries can be "heavy" and should not use the UI thread as it can lock up the app - resulting in an Application Not Responding (ANR).

If I have several AsyncTasks, should they share a connection or should they open a connection each?

Are there any best practices for these scenarios?

Answer 1

You can try to apply new architecture approach anounced at Google I/O 2017.

It also includes new ORM library called Room

It contains three main components: @Entity, @Dao and @Database

User.java

@Entity
public class User {
  @PrimaryKey
  private int uid;

  @ColumnInfo(name = "first_name")
  private String firstName;

  @ColumnInfo(name = "last_name")
  private String lastName;

  // Getters and setters are ignored for brevity,
  // but they're required for Room to work.
}

UserDao.java

@Dao
public interface UserDao {
  @Query("SELECT * FROM user")
  List<User> getAll();

  @Query("SELECT * FROM user WHERE uid IN (:userIds)")
  List<User> loadAllByIds(int[] userIds);

  @Query("SELECT * FROM user WHERE first_name LIKE :first AND "
       + "last_name LIKE :last LIMIT 1")
  User findByName(String first, String last);

  @Insert
  void insertAll(User... users);

  @Delete
  void delete(User user);
}

AppDatabase.java

@Database(entities = {User.class}, version = 1)
public abstract class AppDatabase extends RoomDatabase {
  public abstract UserDao userDao();
}

Answer 2

Dmytro's answer works fine for my case. I think it's better to declare the function as synchronized. at least for my case, it would invoke null pointer exception otherwise, e.g. getWritableDatabase not yet returned in one thread and openDatabse called in another thread meantime.

public synchronized SQLiteDatabase openDatabase() {
        if(mOpenCounter.incrementAndGet() == 1) {
            // Opening new database
            mDatabase = mDatabaseHelper.getWritableDatabase();
        }
        return mDatabase;
    }

Answer 3

after struggling with this for a couple of hours, I've found that you can only use one db helper object per db execution. For example,

for(int x = 0; x < someMaxValue; x++)
{
    db = new DBAdapter(this);
    try
    {

        db.addRow
        (
                NamesStringArray[i].toString(), 
                StartTimeStringArray[i].toString(),
                EndTimeStringArray[i].toString()
        );

    }
    catch (Exception e)
    {
        Log.e("Add Error", e.toString());
        e.printStackTrace();
    }
    db.close();
}

as apposed to:

db = new DBAdapter(this);
for(int x = 0; x < someMaxValue; x++)
{

    try
    {
        // ask the database manager to add a row given the two strings
        db.addRow
        (
                NamesStringArray[i].toString(), 
                StartTimeStringArray[i].toString(),
                EndTimeStringArray[i].toString()
        );

    }
    catch (Exception e)
    {
        Log.e("Add Error", e.toString());
        e.printStackTrace();
    }

}
db.close();

creating a new DBAdapter each time the loop iterates was the only way I could get my strings into a database through my helper class.

Answer 4

Having had some issues, I think I have understood why I have been going wrong.

I had written a database wrapper class which included a close() which called the helper close as a mirror of open() which called getWriteableDatabase and then have migrated to a ContentProvider. The model for ContentProvider does not use SQLiteDatabase.close() which I think is a big clue as the code does use getWriteableDatabase In some instances I was still doing direct access (screen validation queries in the main so I migrated to a getWriteableDatabase/rawQuery model.

I use a singleton and there is the slightly ominous comment in the close documentation

Close any open database object

(my bolding).

So I have had intermittent crashes where I use background threads to access the database and they run at the same time as foreground.

So I think close() forces the database to close regardless of any other threads holding references - so close() itself is not simply undoing the matching getWriteableDatabase but force closing any open requests. Most of the time this is not a problem as the code is single threading, but in multi-threaded cases there is always the chance of opening and closing out of sync.

Having read comments elsewhere that explains that the SqLiteDatabaseHelper code instance counts, then the only time you want a close is where you want the situation where you want to do a backup copy, and you want to force all connections to be closed and force SqLite to write away any cached stuff that might be loitering about - in other words stop all application database activity, close just in case the Helper has lost track, do any file level activity (backup/restore) then start all over again.

Although it sounds like a good idea to try and close in a controlled fashion, the reality is that Android reserves the right to trash your VM so any closing is reducing the risk of cached updates not being written, but it cannot be guaranteed if the device is stressed, and if you have correctly freed your cursors and references to databases (which should not be static members) then the helper will have closed the database anyway.

So my take is that the approach is:

Use getWriteableDatabase to open from a singleton wrapper. (I used a derived application class to provide the application context from a static to resolve the need for a context).

Never directly call close.

Never store the resultant database in any object that does not have an obvious scope and rely on reference counting to trigger an implicit close().

If doing file level handling, bring all database activity to a halt and then call close just in case there is a runaway thread on the assumption that you write proper transactions so the runaway thread will fail and the closed database will at least have proper transactions rather than potentially a file level copy of a partial transaction.

Answer 5

I know that the response is late, but the best way to execute sqlite queries in android is through a custom content provider. In that way the UI is decoupled with the database class(the class that extends the SQLiteOpenHelper class). Also the queries are executed in a background thread(Cursor Loader).

Answer 6

My understanding of SQLiteDatabase APIs is that in case you have a multi threaded application, you cannot afford to have more than a 1 SQLiteDatabase object pointing to a single database.

The object definitely can be created but the inserts/updates fail if different threads/processes (too) start using different SQLiteDatabase objects (like how we use in JDBC Connection).

The only solution here is to stick with 1 SQLiteDatabase objects and whenever a startTransaction() is used in more than 1 thread, Android manages the locking across different threads and allows only 1 thread at a time to have exclusive update access.

Also you can do "Reads" from the database and use the same SQLiteDatabase object in a different thread (while another thread writes) and there would never be database corruption i.e "read thread" wouldn't read the data from the database till the "write thread" commits the data although both use the same SQLiteDatabase object.

This is different from how connection object is in JDBC where if you pass around (use the same) the connection object between read and write threads then we would likely be printing uncommitted data too.

In my enterprise application, I try to use conditional checks so that the UI Thread never have to wait, while the BG thread holds the SQLiteDatabase object (exclusively). I try to predict UI Actions and defer BG thread from running for 'x' seconds. Also one can maintain PriorityQueue to manage handing out SQLiteDatabase Connection objects so that the UI Thread gets it first.