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

User · Answer

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

User · Answer

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 lt User gt  getAll        Query  SELECT   FROM user WHERE uid IN   userIds      List lt User gt  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

User · Answer

Use a Thread or AsyncTask for long-running operations  50ms    Test your app to see where that is  Most operations  probably  don t require a thread  because most operations  probably  only involve a few rows  Use a thread for bulk operations  Share one SQLiteDatabase instance for each DB on disk between threads and implement a counting system to keep track of open connections    Are there any best practices for these scenarios   Share a static field between all your classes  I used to keep a singleton around for that and other things that need to be shared  A counting scheme  generally using AtomicInteger  also should be used to make sure you never close the database early or leave it open   My solution   The old version I wrote is available at https   github com Taeluf dev tree main archived databasemanager and is not maintained  If you want to understand my solution  look at the code and read my notes  My notes are usually pretty helpful   copy paste the code into a new file named DatabaseManager   or download it from github  extend DatabaseManager and implement onCreate and onUpgrade like you normally would  You can create multiple subclasses of the one DatabaseManager class in order to have different databases on disk  Instantiate your subclass and call getDb   to use the SQLiteDatabase class  Call close   for each subclass you instantiated  The code to copy paste  import android content Context  import android database sqlite SQLiteDatabase   import java util concurrent ConcurrentHashMap       Extend this class and use it as an SQLiteOpenHelper class       DO NOT distribute  sell  or present this code as your own      for any distributing selling  or whatever  see the info at the link below       Distribution  attribution  legal stuff     See https   github com JakarCo databasemanager        If you ever need help with this code  contact me at support androidsqlitelibrary com  or support jakar co          Do not sell this  but use it as much as you want  There are no implied or express warranties with this code         This is a simple database manager class which makes threading synchronization super easy        Extend this class and use it like an SQLiteOpenHelper  but use it as follows      Instantiate this class once in each thread that uses the database       Make sure to call   link  close    on every opened instance of this class     If it is closed  then call   link  open    before using again         Call   link  getDb    to get an instance of the underlying SQLiteDatabse class  which is synchronized        I also implement this system  well  it s very similar  in my  lt a href  quot http   androidslitelibrary com quot  gt Android SQLite Libray lt  a gt  at http   androidslitelibrary com            abstract public class DatabaseManager               See SQLiteOpenHelper documentation            abstract public void onCreate SQLiteDatabase db          See SQLiteOpenHelper documentation             abstract public void onUpgrade SQLiteDatabase db  int oldVersion  int newVersion          Optional                       public void onOpen SQLiteDatabase db           Optional                      public void onDowngrade SQLiteDatabase db  int oldVersion  int newVersion            Optional                     public void onConfigure SQLiteDatabase db               The SQLiteOpenHelper class is not actually used by your application                     static private class DBSQLiteOpenHelper extends SQLiteOpenHelper            DatabaseManager databaseManager          private AtomicInteger counter   new AtomicInteger 0            public DBSQLiteOpenHelper Context context  String name  int version  DatabaseManager databaseManager                super context  name  null  version               this databaseManager   databaseManager                     public void addConnection                counter incrementAndGet                      public void removeConnection                counter decrementAndGet                      public int getCounter                 return counter get                       Override         public void onCreate SQLiteDatabase db                databaseManager onCreate db                       Override         public void onUpgrade SQLiteDatabase db  int oldVersion  int newVersion                databaseManager onUpgrade db  oldVersion  newVersion                       Override         public void onOpen SQLiteDatabase db                databaseManager onOpen db                       Override         public void onDowngrade SQLiteDatabase db  int oldVersion  int newVersion                databaseManager onDowngrade db  oldVersion  newVersion                       Override         public void onConfigure SQLiteDatabase db                databaseManager onConfigure db                        private static final ConcurrentHashMap lt String DBSQLiteOpenHelper gt  dbMap   new ConcurrentHashMap lt String  DBSQLiteOpenHelper gt          private static final Object lockObject   new Object          private DBSQLiteOpenHelper sqLiteOpenHelper      private SQLiteDatabase db      private Context context           Instantiate a new DB Helper           lt br gt  SQLiteOpenHelpers are statically cached so they  and their internally cached SQLiteDatabases  will be reused for concurrency                param context Any   link android content Context  belonging to your package          param name The database name  This may be anything you like  Adding a file extension is not required and any file extension you would like to use is fine          param version the database version              public DatabaseManager Context context  String name  int version            String dbPath   context getApplicationContext   getDatabasePath name  getAbsolutePath            synchronized  lockObject                sqLiteOpenHelper   dbMap get dbPath               if  sqLiteOpenHelper  null                    sqLiteOpenHelper   new DBSQLiteOpenHelper context  name  version  this                   dbMap put dbPath sqLiteOpenHelper                               SQLiteOpenHelper class caches the SQLiteDatabase  so this will be the same SQLiteDatabase object every time             db   sqLiteOpenHelper getWritableDatabase                      this context   context getApplicationContext                 Get the writable SQLiteDatabase             public SQLiteDatabase getDb            return db                 Check if the underlying SQLiteDatabase is open                return whether the DB is open or not             public boolean isOpen            return  db  null amp  amp db isOpen                     Lowers the DB counter by 1 for any   link DatabaseManager s referencing the same DB on disk          lt br   gt If the new counter is 0  then the database will be closed           lt br   gt  lt br   gt This needs to be called before application exit          lt br   gt If the counter is 0  then the underlying SQLiteDatabase is  lt b gt null lt  b gt  until another DatabaseManager is instantiated or you call   link  open                   return true if the underlying   link android database sqlite SQLiteDatabase  is closed  counter is 0   and false otherwise  counter  gt  0              public boolean close            sqLiteOpenHelper removeConnection            if  sqLiteOpenHelper getCounter    0               synchronized  lockObject                   if  db inTransaction   db endTransaction                    if  db isOpen   db close                    db   null                            return true                    return false                Increments the internal db counter by one and opens the db if needed                  public void open            sqLiteOpenHelper addConnection            if  db  null   db isOpen                     synchronized  lockObject                       db   sqLiteOpenHelper getWritableDatabase

User · Answer

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

User · Answer

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  lt  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  lt  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

User · Answer

Concurrent Database Access  Same article on my blog I like formatting more   I wrote small article which describe how to make access to your android database thread safe     Assuming you have your own SQLiteOpenHelper   public class DatabaseHelper extends SQLiteOpenHelper           Now you want to write data to database in separate threads       Thread 1  Context context   getApplicationContext     DatabaseHelper helper   new DatabaseHelper context    SQLiteDatabase database   helper getWritableDatabase     database insert        database close         Thread 2  Context context   getApplicationContext     DatabaseHelper helper   new DatabaseHelper context    SQLiteDatabase database   helper getWritableDatabase     database insert        database close      You will get following message in your logcat and one of your changes will not be written   android database sqlite SQLiteDatabaseLockedException  database is locked  code 5    This is happening because every time you create new SQLiteOpenHelper object you are actually making new database connection  If you try to write to the database from actual distinct connections at the same time  one will fail   from answer above   To use database with multiple threads we need to make sure we are using one database connection   Let   s make singleton class Database Manager which will hold and return single SQLiteOpenHelper object   public class DatabaseManager        private static DatabaseManager instance      private static SQLiteOpenHelper mDatabaseHelper       public static synchronized void initializeInstance SQLiteOpenHelper helper            if  instance    null                instance   new DatabaseManager                mDatabaseHelper   helper                       public static synchronized DatabaseManager getInstance             if  instance    null                throw new IllegalStateException DatabaseManager class getSimpleName                           is not initialized  call initialize     method first                        return instance             public SQLiteDatabase getDatabase             return new mDatabaseHelper getWritableDatabase               Updated code which write data to database in separate threads will look like this       In your application class  DatabaseManager initializeInstance new MySQLiteOpenHelper         Thread 1  DatabaseManager manager   DatabaseManager getInstance     SQLiteDatabase database   manager getDatabase    database insert        database close         Thread 2  DatabaseManager manager   DatabaseManager getInstance     SQLiteDatabase database   manager getDatabase    database insert        database close      This will bring you another crash   java lang IllegalStateException  attempt to re-open an already-closed object  SQLiteDatabase   Since we are using only one database connection  method getDatabase   return same instance of SQLiteDatabase object for Thread1 and Thread2  What is happening  Thread1 may close database  while Thread2 is still using it  That   s why we have IllegalStateException crash   We need to make sure no-one is using database and only then close it  Some folks on stackoveflow recommended to never close your SQLiteDatabase  This will result in following logcat message   Leak found Caused by  java lang IllegalStateException  SQLiteDatabase created and never closed   Working sample  public class DatabaseManager        private int mOpenCounter       private static DatabaseManager instance      private static SQLiteOpenHelper mDatabaseHelper      private SQLiteDatabase mDatabase       public static synchronized void initializeInstance SQLiteOpenHelper helper            if  instance    null                instance   new DatabaseManager                mDatabaseHelper   helper                       public static synchronized DatabaseManager getInstance             if  instance    null                throw new IllegalStateException DatabaseManager class getSimpleName                           is not initialized  call initializeInstance     method first                        return instance             public synchronized SQLiteDatabase openDatabase             mOpenCounter            if mOpenCounter    1                   Opening new database             mDatabase   mDatabaseHelper getWritableDatabase                      return mDatabase             public synchronized void closeDatabase             mOpenCounter--          if mOpenCounter    0                   Closing database             mDatabase close                          Use it as follows   SQLiteDatabase database   DatabaseManager getInstance   openDatabase    database insert          database close    Don t close it directly  DatabaseManager getInstance   closeDatabase       correct way   Every time you need database you should call openDatabase   method of DatabaseManager class  Inside this method  we have a counter  which indicate how many times database is opened  If it equals to one  it means we need to create new database connection  if not  database connection is already created    The same happens in closeDatabase   method  Every time we call this method  counter is decreased  whenever it goes to zero  we are closing database connection     Now you should be able to use your database and be sure it s thread safe

User · Answer

Inserts  updates  deletes and reads are generally OK from multiple threads  but Brad s answer is not correct   You have to be careful with how you create your connections and use them   There are situations where your update calls will fail  even if your database doesn t get corrupted   The basic answer   The SqliteOpenHelper object holds on to one database connection   It appears to offer you a read and write connection  but it really doesn t   Call the read-only  and you ll get the write database connection regardless   So  one helper instance  one db connection   Even if you use it from multiple threads  one connection at a time   The SqliteDatabase object uses java locks to keep access serialized   So  if 100 threads have one db instance  calls to the actual on-disk database are serialized   So  one helper  one db connection  which is serialized in java code   One thread  1000 threads  if you use one helper instance shared between them  all of your db access code is serial   And life is good  ish    If you try to write to the database from actual distinct connections at the same time  one will fail   It will not wait till the first is done and then write   It will simply not write your change   Worse  if you don   t call the right version of insert update on the SQLiteDatabase  you won   t get an exception   You   ll just get a message in your LogCat  and that will be it   So  multiple threads   Use one helper   Period   If you KNOW only one thread will be writing  you MAY be able to use multiple connections  and your reads will be faster  but buyer beware   I haven t tested that much   Here s a blog post with far more detail and an example app    Android Sqlite Locking  Updated link 6 18 2012  Android-Database-Locking-Collisions-Example by touchlab on GitHub   Gray and I are actually wrapping up an ORM tool  based off of his Ormlite  that works natively with Android database implementations  and follows the safe creation calling structure I describe in the blog post   That should be out very soon   Take a look     In the meantime  there is a follow up blog post    Single SQLite connection   Also checkout the fork by 2point0 of the previously mentioned locking example    Android-Database-Locking-Collisions-Example by 2point0 on GitHub

User · Answer

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

User · Answer

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

User · Answer

The Database is very flexible with multi-threading  My apps hit their DBs from many different threads simultaneously and it does just fine  In some cases I have multiple processes hitting the DB simultaneously and that works fine too    Your async tasks - use the same connection when you can  but if you have to  its OK to access the DB from different tasks

[android] What are the best practices for SQLite on Android?

Examples related to android

Examples related to database

Examples related to sqlite