Ship an application with a database

Question

If your application requires a database and it comes with built in data  what is the best way to ship that application  Should I    Precreate the SQLite database and include it in the  apk  Include the SQL commands with the application and have it create the database and insert the data on first use    The drawbacks I see are    Possible SQLite version mismatches might cause problems and I currently don t know where the database should go and how to access it  It may take a really long time to create and populate the database on the device    Any suggestions  Pointers to the documentation regarding any issues would be greatly appreciated

User · Answer

Android already provides a version-aware approach of database management. This approach has been leveraged in the BARACUS framework for Android applications.

It enables you to manage the database along the entire version lifecycle of an app, beeing able to update the sqlite database from any prior version to the current one.

Also, it allows you to run hot-backups and hot-recovery of the SQLite.

I am not 100% sure, but a hot-recovery for a specific device may enable you to ship a prepared database in your app. But I am not sure about the database binary format which might be specific to certain devices, vendors or device generations.

Since the stuff is Apache License 2, feel free to reuse any part of the code, which can be found on github

EDIT :

If you only want to ship data, you might consider instantiating and persisting POJOs at the applications first start. BARACUS got a built-in support to this (Built-in key value store for configuration infos, e.g. "APP_FIRST_RUN" plus a after-context-bootstrap hook in order to run post-launch operations on the context). This enables you to have tight coupled data shipped with your app; in most cases this fitted to my use cases.

User · Answer

There are two options for creating and updating databases     One is to create a database externally  then place it in the assets folder of the project and then copy the entire database from there   This is much quicker if the database has a lot of tables and other components   Upgrades are triggered by changing the database version number in the res values strings xml file  Upgrades would then be accomplished by creating a new database externally  replacing the old database in the assets folder with the new database  saving the old database in internal storage under another name  copying the new database from the assets folder into internal storage  transferring all of the data from the old database  that was renamed earlier  into the new database and finally deleting the old database   You can create a database originally by using the SQLite Manager FireFox plugin to execute your creation sql statements   The other option is to create a database internally from a sql file   This is not as quick but the delay would probably be unnoticeable to the users if the database has only a few tables  Upgrades are triggered by changing the database version number in the res values strings xml file   Upgrades would then be accomplished by processing an upgrade sql file   The data in the database will remain unchanged except when its container is removed  for example dropping a table   The example below demonstrates how to use either method   Here is a sample create database sql file   It is to be placed in the assets folder of the project for the internal method or copied into the  Execute SQL  of SQLite Manager to create the database for the external method    NOTE  Notice the comment about the table required by Android    --Android requires a table named  android metadata  with a  locale  column CREATE TABLE  android metadata    locale  TEXT DEFAULT  en US    INSERT INTO  android metadata  VALUES   en US     CREATE TABLE  kitchen table   CREATE TABLE  coffee table   CREATE TABLE  pool table   CREATE TABLE  dining room table   CREATE TABLE  card table      Here is a sample update database sql file   It is to be placed in the assets folder of the project for the internal method or copied into the  Execute SQL  of SQLite Manager to create the database for the external method    NOTE  Notice that all three types of  SQL comments will be ignored by the sql parser that is included in this example    --CREATE TABLE  kitchen table    This is one type of comment in sql   It is ignored by parseSql        CREATE TABLE  coffee table   This is a second type of comment in sql   It is ignored by parseSql        CREATE TABLE  pool table    This is a third type of comment in sql   It is ignored by parseSql       CREATE TABLE  dining room table   This is a second type of comment in sql   It is ignored by parseSql       CREATE TABLE  card table   This is a third type of comment in sql   It is ignored by parseSql     --DROP TABLE  picnic table   Uncomment this if picnic table was previously created and now is being replaced  CREATE TABLE  picnic table    plates  TEXT   INSERT INTO  picnic table  VALUES   paper      Here is an entry to add to the  res values strings xml file for the database version number    lt item type  string  name  databaseVersion  format  integer  gt 1 lt  item gt    Here is an activity that accesses the database and then uses it    Note  You might want to run the database code in a separate thread if it uses a lot of resources    package android example   import android app Activity  import android database sqlite SQLiteDatabase  import android os Bundle           author Danny Remington - MacroSolve                Activity for demonstrating how to use a sqlite database      public class Database extends Activity            Called when the activity is first created           Override     public void onCreate Bundle savedInstanceState            super onCreate savedInstanceState           setContentView R layout main           DatabaseHelper myDbHelper          SQLiteDatabase myDb   null           myDbHelper   new DatabaseHelper this                         Database must be initialized before it can be used  This will ensure            that the database exists and is the current version                       myDbHelper initializeDataBase              try                  A reference to the database can be obtained after initialization              myDb   myDbHelper getWritableDatabase                                  Place code to use database here                             catch  Exception ex                ex printStackTrace               finally               try                   myDbHelper close                  catch  Exception ex                    ex printStackTrace                  finally                   myDb close                                       Here is the database helper class where the database is created or updated if necessary    NOTE  Android requires that you create a class that extends SQLiteOpenHelper in order to work with a Sqlite database    package android example   import java io FileOutputStream  import java io IOException  import java io InputStream  import java io OutputStream   import android content Context  import android database sqlite SQLiteDatabase  import android database sqlite SQLiteOpenHelper           author Danny Remington - MacroSolve                Helper class for sqlite database      public class DatabaseHelper extends SQLiteOpenHelper                  The Android s default system path of the application database in internal        storage  The package of the application is part of the path of the        directory              private static String DB DIR     data data android example databases        private static String DB NAME    database sqlite       private static String DB PATH   DB DIR   DB NAME      private static String OLD DB PATH   DB DIR    old     DB NAME       private final Context myContext       private boolean createDatabase   false      private boolean upgradeDatabase   false                  Constructor Takes and keeps a reference of the passed context in order to        access to the application assets and resources                  param context             public DatabaseHelper Context context            super context  DB NAME  null  context getResources   getInteger                  R string databaseVersion            myContext   context             Get the path of the database that is based on the context          DB PATH   myContext getDatabasePath DB NAME  getAbsolutePath                          Upgrade the database in internal storage if it exists but is not current          Create a new empty database in internal storage if it does not exist              public void initializeDataBase                           Creates or updates the database in internal storage if it is needed            before opening the database  In all cases opening the database copies            the database in internal storage to the cache                      getWritableDatabase             if  createDatabase                                  If the database is created by the copy method  then the creation                code needs to go here  This method consists of copying the new                database from assets into internal storage and then caching it                              try                                         Write over the empty data that was created in internal                    storage with the one in assets and then cache it                                      copyDataBase                  catch  IOException e                    throw new Error  Error copying database                            else if  upgradeDatabase                                  If the database is upgraded by the copy and reload method  then                the upgrade code needs to go here  This method consists of                renaming the old database in internal storage  create an empty                new database in internal storage  copying the database from                assets to the new database in internal storage  caching the new                database from internal storage  loading the data from the old                database into the new database in the cache and then deleting the                old database from internal storage                              try                   FileHelper copyFile DB PATH  OLD DB PATH                   copyDataBase                    SQLiteDatabase old db   SQLiteDatabase openDatabase OLD DB PATH  null  SQLiteDatabase OPEN READWRITE                   SQLiteDatabase new db   SQLiteDatabase openDatabase DB PATH null  SQLiteDatabase OPEN READWRITE                                         Add code to load data into the new database from the old                    database and then delete the old database from internal                    storage after all data has been transferred                                    catch  IOException e                    throw new Error  Error copying database                                                   Copies your database from your local assets-folder to the just created        empty database in the system folder  from where it can be accessed and        handled  This is done by transfering bytestream                private void copyDataBase   throws IOException                         Close SQLiteOpenHelper so it will commit the created empty database            to internal storage                      close                           Open the database in the assets folder as the input stream                      InputStream myInput   myContext getAssets   open DB NAME                          Open the empty db in interal storage as the output stream                      OutputStream myOutput   new FileOutputStream DB PATH                          Copy over the empty db in internal storage with the database in the            assets folder                      FileHelper copyFile myInput  myOutput                          Access the copied database so SQLiteHelper will cache it and mark it            as created                      getWritableDatabase   close                         This is where the creation of tables and the initial population of the        tables should happen  if a database is being created from scratch instead        of being copied from the application package assets  Copying a database        from the application package assets to internal storage inside this        method will result in a corrupted database          lt P gt         NOTE  This method is normally only called when a database has not already        been created  When the database has been copied  then this method is        called the first time a reference to the database is retrieved after the        database is copied since the database last cached by SQLiteOpenHelper is        different than the database in internal storage               Override     public void onCreate SQLiteDatabase db                          Signal that a new database needs to be copied  The copy process must            be performed after the database in the cache has been closed causing            it to be committed to internal storage  Otherwise the database in            internal storage will not have the same creation timestamp as the one            in the cache causing the database in internal storage to be marked as            corrupted                      createDatabase   true                         This will create by reading a sql file and executing the commands in            it                             try                  InputStream is   myContext getResources   getAssets   open                  create database sql                                  String   statements   FileHelper parseSqlFile is                                 for  String statement   statements                   db execSQL statement                                     catch  Exception ex                   ex printStackTrace                                           Called only if version number was changed and the database has already        been created  Copying a database from the application package assets to        the internal data system inside this method will result in a corrupted        database in the internal data system               Override     public void onUpgrade SQLiteDatabase db  int oldVersion  int newVersion                          Signal that the database needs to be upgraded for the copy method of            creation  The copy process must be performed after the database has            been opened or the database will be corrupted                      upgradeDatabase   true                         Code to update the database via execution of sql statements goes            here                                     This will upgrade by reading a sql file and executing the commands in            it                         try              InputStream is   myContext getResources   getAssets   open              upgrade database sql                          String   statements   FileHelper parseSqlFile is                         for  String statement   statements               db execSQL statement                             catch  Exception ex               ex printStackTrace                                       Called everytime the database is opened by getReadableDatabase or        getWritableDatabase  This is called after onCreate or onUpgrade is        called               Override     public void onOpen SQLiteDatabase db            super onOpen db                        Add your public helper methods to access and get content from the        database  You could return cursors by doing         return myDataBase query        so it d be easy to you to create adapters        for your views               Here s the FileHelper class that contains methods for byte stream copying files and parsing sql files   package android example   import java io BufferedReader  import java io File  import java io FileInputStream  import java io FileOutputStream  import java io FileReader  import java io IOException  import java io InputStream  import java io InputStreamReader  import java io OutputStream  import java io Reader  import java nio channels FileChannel           author Danny Remington - MacroSolve                Helper class for common tasks using files          public class FileHelper                  Creates the specified  lt i gt  lt b gt toFile lt  b gt  lt  i gt  that is a byte for byte a copy        of  lt i gt  lt b gt fromFile lt  b gt  lt  i gt   If  lt i gt  lt b gt toFile lt  b gt  lt  i gt  already existed  then        it will be replaced with a copy of  lt i gt  lt b gt fromFile lt  b gt  lt  i gt   The name and        path of  lt i gt  lt b gt toFile lt  b gt  lt  i gt  will be that of  lt i gt  lt b gt toFile lt  b gt  lt  i gt   Both         lt i gt  lt b gt fromFile lt  b gt  lt  i gt  and  lt i gt  lt b gt toFile lt  b gt  lt  i gt  will be closed by this        operation                  param fromFile                   - InputStream for the file to copy from          param toFile                   - InputStream for the file to copy to              public static void copyFile InputStream fromFile  OutputStream toFile  throws IOException              transfer bytes from the inputfile to the outputfile         byte   buffer   new byte 1024           int length           try               while   length   fromFile read buffer    gt  0                    toFile write buffer  0  length                                      Close the streams         finally               try                   if  toFile    null                        try                           toFile flush                          finally                           toFile close                                                      finally                   if  fromFile    null                        fromFile close                                                                    Creates the specified  lt i gt  lt b gt toFile lt  b gt  lt  i gt  that is a byte for byte a copy        of  lt i gt  lt b gt fromFile lt  b gt  lt  i gt   If  lt i gt  lt b gt toFile lt  b gt  lt  i gt  already existed  then        it will be replaced with a copy of  lt i gt  lt b gt fromFile lt  b gt  lt  i gt   The name and        path of  lt i gt  lt b gt toFile lt  b gt  lt  i gt  will be that of  lt i gt  lt b gt toFile lt  b gt  lt  i gt   Both         lt i gt  lt b gt fromFile lt  b gt  lt  i gt  and  lt i gt  lt b gt toFile lt  b gt  lt  i gt  will be closed by this        operation                  param fromFile                   - String specifying the path of the file to copy from          param toFile                   - String specifying the path of the file to copy to              public static void copyFile String fromFile  String toFile  throws IOException           copyFile new FileInputStream fromFile   new FileOutputStream toFile                          Creates the specified  lt i gt  lt b gt toFile lt  b gt  lt  i gt  that is a byte for byte a copy        of  lt i gt  lt b gt fromFile lt  b gt  lt  i gt   If  lt i gt  lt b gt toFile lt  b gt  lt  i gt  already existed  then        it will be replaced with a copy of  lt i gt  lt b gt fromFile lt  b gt  lt  i gt   The name and        path of  lt i gt  lt b gt toFile lt  b gt  lt  i gt  will be that of  lt i gt  lt b gt toFile lt  b gt  lt  i gt   Both         lt i gt  lt b gt fromFile lt  b gt  lt  i gt  and  lt i gt  lt b gt toFile lt  b gt  lt  i gt  will be closed by this        operation                  param fromFile                   - File for the file to copy from          param toFile                   - File for the file to copy to              public static void copyFile File fromFile  File toFile  throws IOException           copyFile new FileInputStream fromFile   new FileOutputStream toFile                          Creates the specified  lt i gt  lt b gt toFile lt  b gt  lt  i gt  that is a byte for byte a copy        of  lt i gt  lt b gt fromFile lt  b gt  lt  i gt   If  lt i gt  lt b gt toFile lt  b gt  lt  i gt  already existed  then        it will be replaced with a copy of  lt i gt  lt b gt fromFile lt  b gt  lt  i gt   The name and        path of  lt i gt  lt b gt toFile lt  b gt  lt  i gt  will be that of  lt i gt  lt b gt toFile lt  b gt  lt  i gt   Both         lt i gt  lt b gt fromFile lt  b gt  lt  i gt  and  lt i gt  lt b gt toFile lt  b gt  lt  i gt  will be closed by this        operation                  param fromFile                   - FileInputStream for the file to copy from          param toFile                   - FileInputStream for the file to copy to              public static void copyFile FileInputStream fromFile  FileOutputStream toFile  throws IOException           FileChannel fromChannel   fromFile getChannel            FileChannel toChannel   toFile getChannel             try               fromChannel transferTo 0  fromChannel size    toChannel             finally               try                   if  fromChannel    null                        fromChannel close                                    finally                   if  toChannel    null                        toChannel close                                                                    Parses a file containing sql statements into a String array that contains        only the sql statements  Comments and white spaces in the file are not        parsed into the String array  Note the file must not contained malformed        comments and all sql statements must end with a semi-colon     in order        for the file to be parsed correctly  The sql statements in the String        array will not end with a semi-colon                      param sqlFile                   - String containing the path for the file that contains sql                   statements                  return String array containing the sql statements              public static String   parseSqlFile String sqlFile  throws IOException           return parseSqlFile new BufferedReader new FileReader sqlFile                           Parses a file containing sql statements into a String array that contains        only the sql statements  Comments and white spaces in the file are not        parsed into the String array  Note the file must not contained malformed        comments and all sql statements must end with a semi-colon     in order        for the file to be parsed correctly  The sql statements in the String        array will not end with a semi-colon                      param sqlFile                   - InputStream for the file that contains sql statements                  return String array containing the sql statements              public static String   parseSqlFile InputStream sqlFile  throws IOException           return parseSqlFile new BufferedReader new InputStreamReader sqlFile                           Parses a file containing sql statements into a String array that contains        only the sql statements  Comments and white spaces in the file are not        parsed into the String array  Note the file must not contained malformed        comments and all sql statements must end with a semi-colon     in order        for the file to be parsed correctly  The sql statements in the String        array will not end with a semi-colon                      param sqlFile                   - Reader for the file that contains sql statements                  return String array containing the sql statements              public static String   parseSqlFile Reader sqlFile  throws IOException           return parseSqlFile new BufferedReader sqlFile                          Parses a file containing sql statements into a String array that contains        only the sql statements  Comments and white spaces in the file are not        parsed into the String array  Note the file must not contained malformed        comments and all sql statements must end with a semi-colon     in order        for the file to be parsed correctly  The sql statements in the String        array will not end with a semi-colon                      param sqlFile                   - BufferedReader for the file that contains sql statements                  return String array containing the sql statements              public static String   parseSqlFile BufferedReader sqlFile  throws IOException           String line          StringBuilder sql   new StringBuilder            String multiLineComment   null           while   line   sqlFile readLine       null                line   line trim                    Check for start of multi-line comment             if  multiLineComment    null                       Check for first multi-line comment type                 if  line startsWith                              if   line endsWith                                 multiLineComment                                                  Check for second multi-line comment type                   else if  line startsWith                             if   line endsWith                                 multiLineComment                                             Append line if line is not empty or a single line comment                   else if   line startsWith  --    amp  amp   line equals                            sql append line                        Check for matching end comment               else if  multiLineComment equals                          if  line endsWith                              multiLineComment   null                                   Check for matching end comment               else if  multiLineComment equals                         if  line endsWith                             multiLineComment   null                                                      sqlFile close             return sql toString   split

User · Answer

In November 2017 Google released the Room Persistence Library  From the documentation   The Room persistence library provides an abstraction layer over SQLite to allow fluent database access while harnessing the full power of SQLite  The library helps you create a cache of your app s data on a device that s running your app  This cache  which serves as your app s single source of truth  allows users to view a consistent copy of the key information within your app  regardless of whether users have an internet connection   The Room database has a callback when the database is first created or opened  You can use the create callback to populate your database  Room databaseBuilder context applicationContext          DataDatabase  class java   quot Sample db quot              prepopulate the database after onCreate was called          addCallback object   Callback                 override fun onCreate db  SupportSQLiteDatabase                    super onCreate db                     moving to a new thread                 ioThread                       getInstance context  dataDao                                            insert PREPOPULATE DATA                                                      build    Code from this blog post

User · Answer

If you are using ROOM there is a pretty straight forward path already https   developer android com training data-storage room prepopulate in the official documentation  Here s how it is in action to repopulate your database from an asset file  Room databaseBuilder appContext  AppDatabase class   quot Sample db quot        createFromAsset  quot database myapp db quot        build    or from a file  Room databaseBuilder appContext  AppDatabase class   quot Sample db quot        createFromFile File  quot mypath quot         build    and if you are not using Room  I strongly recommend you do

User · Answer

I wrote a library to simplify this process   dataBase   new DataBase Builder context   myDb              setAssetsPath       default  databases            setDatabaseErrorHandler              setCursorFactory              setUpgradeCallback             setVersion       default 1 build      It will create a dataBase from assets databases myDb db file  In addition  you will get all those functionality    Load database from file Synchronized access to the database Using sqlite-android by requery  Android specific distribution of the latest versions of SQLite    Clone it from github

User · Answer

I modified the class and the answers to the question and wrote a class that allows updating the database via DB VERSION   public class DatabaseHelper extends SQLiteOpenHelper       private static String DB NAME    info db       private static String DB PATH           private static final int DB VERSION   1       private SQLiteDatabase mDataBase      private final Context mContext      private boolean mNeedUpdate   false       public DatabaseHelper Context context            super context  DB NAME  null  DB VERSION           if  android os Build VERSION SDK INT  gt   17              DB PATH   context getApplicationInfo   dataDir     databases            else             DB PATH     data data     context getPackageName       databases            this mContext   context           copyDataBase             this getReadableDatabase               public void updateDataBase   throws IOException           if  mNeedUpdate                File dbFile   new File DB PATH   DB NAME               if  dbFile exists                    dbFile delete                 copyDataBase                 mNeedUpdate   false                       private boolean checkDataBase             File dbFile   new File DB PATH   DB NAME           return dbFile exists               private void copyDataBase             if   checkDataBase                  this getReadableDatabase                this close                try                   copyDBFile                  catch  IOException mIOException                    throw new Error  ErrorCopyingDataBase                                       private void copyDBFile   throws IOException           InputStream mInput   mContext getAssets   open DB NAME             InputStream mInput   mContext getResources   openRawResource R raw info           OutputStream mOutput   new FileOutputStream DB PATH   DB NAME           byte   mBuffer   new byte 1024           int mLength          while   mLength   mInput read mBuffer    gt  0              mOutput write mBuffer  0  mLength           mOutput flush            mOutput close            mInput close               public boolean openDataBase   throws SQLException           mDataBase   SQLiteDatabase openDatabase DB PATH   DB NAME  null  SQLiteDatabase CREATE IF NECESSARY           return mDataBase    null              Override     public synchronized void close             if  mDataBase    null              mDataBase close            super close                Override     public void onCreate SQLiteDatabase db                 Override     public void onUpgrade SQLiteDatabase db  int oldVersion  int newVersion            if  newVersion  gt  oldVersion              mNeedUpdate   true            Using a class   In the activity class  declare variables   private DatabaseHelper mDBHelper  private SQLiteDatabase mDb    In the onCreate method  write the following code   mDBHelper   new DatabaseHelper this    try       mDBHelper updateDataBase      catch  IOException mIOException        throw new Error  UnableToUpdateDatabase       try       mDb   mDBHelper getWritableDatabase      catch  SQLException mSQLException        throw mSQLException      If you add a database file to the folder res raw then use the following modification of the class   public class DatabaseHelper extends SQLiteOpenHelper       private static String DB NAME    info db       private static String DB PATH           private static final int DB VERSION   1       private SQLiteDatabase mDataBase      private final Context mContext      private boolean mNeedUpdate   false       public DatabaseHelper Context context            super context  DB NAME  null  DB VERSION           if  android os Build VERSION SDK INT  gt   17              DB PATH   context getApplicationInfo   dataDir     databases            else             DB PATH     data data     context getPackageName       databases            this mContext   context           copyDataBase             this getReadableDatabase               public void updateDataBase   throws IOException           if  mNeedUpdate                File dbFile   new File DB PATH   DB NAME               if  dbFile exists                    dbFile delete                 copyDataBase                 mNeedUpdate   false                       private boolean checkDataBase             File dbFile   new File DB PATH   DB NAME           return dbFile exists               private void copyDataBase             if   checkDataBase                  this getReadableDatabase                this close                try                   copyDBFile                  catch  IOException mIOException                    throw new Error  ErrorCopyingDataBase                                       private void copyDBFile   throws IOException             InputStream mInput   mContext getAssets   open DB NAME           InputStream mInput   mContext getResources   openRawResource R raw info           OutputStream mOutput   new FileOutputStream DB PATH   DB NAME           byte   mBuffer   new byte 1024           int mLength          while   mLength   mInput read mBuffer    gt  0              mOutput write mBuffer  0  mLength           mOutput flush            mOutput close            mInput close               public boolean openDataBase   throws SQLException           mDataBase   SQLiteDatabase openDatabase DB PATH   DB NAME  null  SQLiteDatabase CREATE IF NECESSARY           return mDataBase    null              Override     public synchronized void close             if  mDataBase    null              mDataBase close            super close                Override     public void onCreate SQLiteDatabase db                 Override     public void onUpgrade SQLiteDatabase db  int oldVersion  int newVersion            if  newVersion  gt  oldVersion              mNeedUpdate   true            http   blog harrix org article 6784

User · Answer

The SQLiteAssetHelper library makes this task really simple   It s easy to add as a gradle dependency  but a Jar is also available for Ant Eclipse   and together with the documentation it can be found at  https   github com jgilfelt android-sqlite-asset-helper  Note  This project is no longer maintained as stated on above Github link   As explained in documentation    Add the dependency to your module s gradle build file   dependencies       compile  com readystatesoftware sqliteasset sqliteassethelper       Copy the database into the assets directory  in a subdirectory called assets databases  For instance  assets databases my database db   Optionally  you may compress the database in a zip file such as assets databases my database zip  This isn t needed  since the APK is compressed as a whole already   Create a class  for example   public class MyDatabase extends SQLiteAssetHelper        private static final String DATABASE NAME    my database db       private static final int DATABASE VERSION   1       public MyDatabase Context context            super context  DATABASE NAME  null  DATABASE VERSION

User · Answer

Currently there is no way to precreate an SQLite database to ship with your apk   The best you can do is save the appropriate SQL as a resource and run them from your application   Yes  this leads to duplication of data  same information exists as a resrouce and as a database  but there is no other way right now   The only mitigating factor is the apk file is compressed   My experience is 908KB compresses to less than 268KB   The thread below has the best discussion solution I have found with good sample code   http   groups google com group android-developers msg 9f455ae93a1cf152  I stored my CREATE statement as a string resource to be read with Context getString   and ran it with SQLiteDatabse execSQL     I stored the data for my inserts in res raw inserts sql  I created the sql file  7000  lines    Using the technique from the link above I entered a loop  read the file line by line and concactenated the data onto  INSERT INTO tbl VALUE   and did another SQLiteDatabase execSQL     No sense in saving 7000  INSERT INTO tbl VALUE  s when they can just be concactenated on     It takes about twenty seconds on the emulator  I do not know how long this would take on a real phone  but it only happens once  when the user first starts the application

User · Answer

Shipping the database inside the apk and then copying it to  data data     will double the size of the database  1 in apk  1 in data data       and will increase the apk size  of course   So your database should not be too big

User · Answer

Shipping the database inside the apk and then copying it to  data data     will double the size of the database  1 in apk  1 in data data       and will increase the apk size  of course   So your database should not be too big

User · Answer

If the required data is not too large  limits I don  t know  would depend on a lot of things   you might also download the data  in XML  JSON  whatever  from a website webapp  AFter receiving  execute the SQL statements using the received data creating your tables and inserting the data    If your mobile app contains lots of data  it might be easier later on to update the data in the installed apps with more accurate data or changes

User · Answer

Android already provides a version-aware approach of database management. This approach has been leveraged in the BARACUS framework for Android applications.

It enables you to manage the database along the entire version lifecycle of an app, beeing able to update the sqlite database from any prior version to the current one.

Also, it allows you to run hot-backups and hot-recovery of the SQLite.

I am not 100% sure, but a hot-recovery for a specific device may enable you to ship a prepared database in your app. But I am not sure about the database binary format which might be specific to certain devices, vendors or device generations.

Since the stuff is Apache License 2, feel free to reuse any part of the code, which can be found on github

EDIT :

If you only want to ship data, you might consider instantiating and persisting POJOs at the applications first start. BARACUS got a built-in support to this (Built-in key value store for configuration infos, e.g. "APP_FIRST_RUN" plus a after-context-bootstrap hook in order to run post-launch operations on the context). This enables you to have tight coupled data shipped with your app; in most cases this fitted to my use cases.

User · Answer

Finally I did it   I have used this link help Using your own SQLite database in Android applications  but had to change it a little bit    If you have many packages you should put the master package name here   private static String DB PATH    data data masterPakageName databases   I changed the method which copies the database from local folder to emulator folder  It had some problem when that folder didn t exist  So first of all  it should check the path  and if it s not there  it should create the folder  In the previous code  the copyDatabase method was never called when the database didn t exist and the checkDataBase method caused exception  so I changed the code a little bit  If your database does not have a file extension  don t use the file name with one    it works nice for me   i hope it whould be usefull for u too      package farhangsarasIntroduction    import java io File  import java io FileOutputStream  import java io IOException  import java io InputStream  import java io OutputStream  import java util ArrayList  import java util HashMap   import android content Context  import android database Cursor   import android database sqlite SQLiteDatabase  import android database sqlite SQLiteException  import android database sqlite SQLiteOpenHelper   import android util Log        public class DataBaseHelper extends SQLiteOpenHelper         The Android s default system path of your application database      private static String DB PATH    data data com example sample databases        private static String DB NAME    farhangsaraDb        private SQLiteDatabase myDataBase       private final Context myContext                   Constructor         Takes and keeps a reference of the passed context in order to access to the application assets and resources           param context              public DataBaseHelper Context context             super context  DB NAME  null  1               this myContext   context                             Creates a empty database on the system and rewrites it with your own database                 public void createDataBase              boolean dbExist          try                 dbExist   checkDataBase                catch  SQLiteException e                 e printStackTrace                throw new Error  database dose not exist                        if dbExist             do nothing - database already exist          else               try                    copyDataBase                    catch  IOException e                     e printStackTrace                    throw new Error  Error copying database                         By calling this method and empty database will be created into the default system path       of your application so we are gonna be able to overwrite that database with our database          this getReadableDatabase                                    Check if the database already exist to avoid re-copying the file each time you open the application           return true if it exists  false if it doesn t              private boolean checkDataBase         SQLiteDatabase checkDB   null       try          String myPath   DB PATH       DB NAME           checkDB   SQLiteDatabase openDatabase myPath  null  SQLiteDatabase OPEN READONLY        catch SQLiteException e          database does t exist yet          throw new Error  database does t exist yet                 if checkDB    null        checkDB close                return checkDB    null   true   false                         Copies your database from your local assets-folder to the just created empty database in the         system folder  from where it can be accessed and handled          This is done by transfering bytestream                 private void copyDataBase   throws IOException                   copyDataBase                  Open your local db as the input stream             InputStream myInput   myContext getAssets   open DB NAME                   Path to the just created empty db             String outFileName   DB PATH       DB NAME              File databaseFile   new File  DB PATH                   check if databases folder exists  if not create one and its subfolders             if   databaseFile exists                     databaseFile mkdir                                 Open the empty db as the output stream             OutputStream myOutput   new FileOutputStream outFileName                  transfer bytes from the inputfile to the outputfile             byte   buffer   new byte 1024               int length              while   length   myInput read buffer   gt 0               myOutput write buffer  0  length                                Close the streams             myOutput flush                myOutput close                myInput close                     Override     public synchronized void close              if myDataBase    null          myDataBase close             super close                 Override     public void onCreate SQLiteDatabase db                   Override     public void onUpgrade SQLiteDatabase db  int oldVersion  int newVersion                 you to create adapters for your views

User · Answer

From what I ve seen you should be be shipping a database that already has the tables setup and data  However if you want  and depending on the type of application you have  you can allow  upgrade database option   Then what you do is download the latest sqlite version  get the latest Insert Create statements of a textfile hosted online  execute the statements and do a data transfer from the old db to the new one

User · Answer

I m using ORMLite and below code worked for me  public class DatabaseProvider extends OrmLiteSqliteOpenHelper       private static final String DatabaseName    DatabaseName       private static final int DatabaseVersion   1      private final Context ProvidedContext       public DatabaseProvider Context context            super context  DatabaseName  null  DatabaseVersion           this ProvidedContext  context          SharedPreferences preferences   PreferenceManager getDefaultSharedPreferences context           boolean databaseCopied   preferences getBoolean  DatabaseCopied   false           if  databaseCopied                  Do Nothing           else               CopyDatabase                SharedPreferences Editor editor   preferences edit                editor putBoolean  DatabaseCopied   true               editor commit                         private String DatabasePath             return   data data     ProvidedContext getPackageName       databases               private void CopyDatabase             try               CopyDatabaseInternal              catch  IOException e                e printStackTrace                         private File ExtractAssetsZip String zipFileName            InputStream inputStream          ZipInputStream zipInputStream          File tempFolder          do               tempFolder   null              tempFolder   new File ProvidedContext getCacheDir       extracted-    System currentTimeMillis                     while  tempFolder exists              tempFolder mkdirs             try               String filename              inputStream   ProvidedContext getAssets   open zipFileName               zipInputStream   new ZipInputStream new BufferedInputStream inputStream                ZipEntry zipEntry              byte   buffer   new byte 1024               int count               while   zipEntry   zipInputStream getNextEntry       null                    filename   zipEntry getName                    if  zipEntry isDirectory                          File fmd   new File tempFolder getAbsolutePath           filename                       fmd mkdirs                        continue                                     FileOutputStream fileOutputStream   new FileOutputStream tempFolder getAbsolutePath           filename                   while   count   zipInputStream read buffer      -1                        fileOutputStream write buffer  0  count                                      fileOutputStream close                    zipInputStream closeEntry                               zipInputStream close              catch  IOException e                e printStackTrace                return null                     return tempFolder             private void CopyDatabaseInternal   throws IOException            File extractedPath   ExtractAssetsZip DatabaseName     zip            String databaseFile               for  File innerFile   extractedPath listFiles                  databaseFile   innerFile getAbsolutePath                break                    if  databaseFile    null    databaseFile length     0               throw new RuntimeException  databaseFile is empty             InputStream inputStream   new FileInputStream databaseFile            String outFileName   DatabasePath     DatabaseName           File destinationPath   new File DatabasePath             if   destinationPath exists                destinationPath mkdirs             File destinationFile   new File outFileName           if   destinationFile exists                destinationFile createNewFile             OutputStream myOutput   new FileOutputStream outFileName            byte   buffer   new byte 1024           int length          while   length   inputStream read buffer    gt  0                myOutput write buffer  0  length                      myOutput flush            myOutput close            inputStream close                Override     public void onCreate SQLiteDatabase sqLiteDatabase  ConnectionSource connectionSource                Override     public void onUpgrade SQLiteDatabase sqLiteDatabase  ConnectionSource connectionSource  int fromVersion  int toVersion               Please note  The code extracts database file from a zip file in assets

User · Answer

My solution neither uses any third-party library nor forces you to call custom methods on SQLiteOpenHelper subclass to initialize the database on creation  It also takes care of database upgrades as well  All that needs to be done is to subclass SQLiteOpenHelper   Prerequisite    The database that you wish to ship with the app  It should contain a 1x1 table named android metadata with an attribute locale having the value en US in addition to the tables unique to your app    Subclassing SQLiteOpenHelper    Subclass SQLiteOpenHelper  Create a private method within the SQLiteOpenHelper subclass  This method contains the logic to copy database contents from the database file in the  assets  folder to the database created in the application package context  Override onCreate  onUpgrade and onOpen methods of SQLiteOpenHelper    Enough said  Here goes the SQLiteOpenHelper subclass   public class PlanDetailsSQLiteOpenHelper extends SQLiteOpenHelper       private static final String TAG    SQLiteOpenHelper        private final Context context      private static final int DATABASE VERSION   1      private static final String DATABASE NAME    my custom db        private boolean createDb   false  upgradeDb   false       public PlanDetailsSQLiteOpenHelper Context context            super context  DATABASE NAME  null  DATABASE VERSION           this context   context                        Copy packaged database from assets folder to the database created in the        application package context                  param db                   The target database in the application package context              private void copyDatabaseFromAssets SQLiteDatabase db            Log i TAG   copyDatabase            InputStream myInput   null          OutputStream myOutput   null          try                  Open db packaged as asset as the input stream             myInput   context getAssets   open  path to shipped db file                    Open the db in the application package context              myOutput   new FileOutputStream db getPath                     Transfer db file contents              byte   buffer   new byte 1024               int length              while   length   myInput read buffer    gt  0                    myOutput write buffer  0  length                             myOutput flush                    Set the version of the copied database to the current                version              SQLiteDatabase copiedDb   context openOrCreateDatabase                  DATABASE NAME  0  null               copiedDb execSQL  PRAGMA user version       DATABASE VERSION               copiedDb close               catch  IOException e                e printStackTrace                throw new Error TAG     Error copying database              finally                  Close the streams             try                   if  myOutput    null                        myOutput close                                      if  myInput    null                        myInput close                                    catch  IOException e                    e printStackTrace                    throw new Error TAG     Error closing streams                                        Override     public void onCreate SQLiteDatabase db            Log i TAG   onCreate db            createDb   true              Override     public void onUpgrade SQLiteDatabase db  int oldVersion  int newVersion            Log i TAG   onUpgrade db            upgradeDb   true              Override     public void onOpen SQLiteDatabase db            Log i TAG   onOpen db            if  createDb      The db in the application package                context is being created                 So copy the contents from the db                file packaged in the assets                folder              createDb   false              copyDatabaseFromAssets db                      if  upgradeDb      The db in the application package                context is being upgraded from a lower to a higher version              upgradeDb   false                 Your db upgrade logic here                      Finally  to get a database connection  just call getReadableDatabase   or getWritableDatabase   on the SQLiteOpenHelper subclass and it will take care of creating a db  copying db contents from the specified file in the  assets  folder  if the database does not exist   In short  you can use the SQLiteOpenHelper subclass to access the db shipped in the assets folder  just as you would use for a database that is initialized using SQL queries in the onCreate   method

User · Answer

Shipping the app with a database file  in Android Studio 3 0 Shipping the app with a database file is a good idea for me  The advantage is that you don t need to do a complex initialization  which sometimes costs lots of time  if your data set is huge  Step 1  Prepare database file Have your database file ready  It can be either a  db file or a  sqlite file  If you use a  sqlite file  all you need to do is to change file extension names  The steps are the same  In this example  I prepared a file called testDB db  It has one table and some sample data in it like this  Step 2  Import the file into your project Create the assets folder if you haven t had one  Then copy and paste the database file into this folder  Step 3  Copy the file to the app s data folder You need to copy the database file to the app s data folder in order to do further interaction with it  This is a one time action  initialization  to copy the database file  If you call this code multiple times  the database file in data folder will be overwritten by the one in assets folder  This overwrite process is useful when you want to update the database in future during the app update  Note that during app update  this database file will not be changed in the app s data folder  Only uninstall will delete it  The database file needs to be copied to  databases folder  Open Device File Explorer  Enter data data  lt YourAppName gt   location  This is the app s default data folder mentioned above  And by default  the database file will be place in another folder called databases under this directory  Now  the copy file process is pretty much like the what Java is doing  Use the following code to do the copy paste  This is the initiation code  It can also be used to update by overwriting  the database file in future    get context by calling  quot this quot  in activity or getActivity   in fragment   call this if API level is lower than 17  String appDataPath    quot  data data  quot    context getPackageName      quot  databases  quot  String appDataPath   context getApplicationInfo   dataDir   File dbFolder   new File appDataPath    quot  databases quot     Make sure the  databases folder exists dbFolder mkdir     This can be called multiple times   File dbFilePath   new File appDataPath    quot  databases testDB db quot     try       InputStream inputStream   context getAssets   open  quot testDB db quot        OutputStream outputStream   new FileOutputStream dbFilePath       byte   buffer   new byte 1024       int length      while   length   inputStream read buffer   gt 0                outputStream write buffer  0  length             outputStream flush        outputStream close        inputStream close      catch  IOException e         handle    Then refresh the folder to verify the copy process  Step 4  Create database open helper Create a subclass for SQLiteOpenHelper  with connect  close  path  etc  I named it DatabaseOpenHelper import android content Context  import android database SQLException  import android database sqlite SQLiteDatabase  import android database sqlite SQLiteOpenHelper   public class DatabaseOpenHelper extends SQLiteOpenHelper       public static final String DB NAME    quot testDB db quot       public static final String DB SUB PATH    quot  databases  quot    DB NAME      private static String APP DATA PATH    quot  quot       private SQLiteDatabase dataBase      private final Context context       public DatabaseOpenHelper Context context           super context  DB NAME  null  1           APP DATA PATH   context getApplicationInfo   dataDir          this context   context             public boolean openDataBase   throws SQLException          String mPath   APP DATA PATH   DB SUB PATH            Note that this method assumes that the db file is already copied in place         dataBase   SQLiteDatabase openDatabase mPath  null  SQLiteDatabase OPEN READWRITE           return dataBase    null              Override     public synchronized void close            if dataBase    null   dataBase close             super close                Override     public void onCreate SQLiteDatabase db                Override     public void onUpgrade SQLiteDatabase db  int oldVersion  int newVersion             Step 5  Create top level class to interact with the database This will be the class that read  amp  write your database file  Also there is a sample query to print out the value in the database  import android content Context  import android database Cursor  import android database SQLException  import android database sqlite SQLiteDatabase  import android util Log   public class Database       private final Context context      private SQLiteDatabase database      private DatabaseOpenHelper dbHelper       public Database Context context           this context   context          dbHelper   new DatabaseOpenHelper context              public Database open   throws SQLException               dbHelper openDataBase            dbHelper close            database   dbHelper getReadableDatabase            return this             public void close                 dbHelper close               public void test            try              String query   quot SELECT value FROM test1 quot               Cursor cursor   database rawQuery query  null               if  cursor moveToFirst                     do                      String value   cursor getString 0                       Log d  quot db quot   value                    while  cursor moveToNext                               cursor close              catch  SQLException e                  handle                    Step 6  Test running Test the code by running the following lines of codes  Database db   new Database context   db open    db test    db close     Hit the run button and cheer

User · Answer

I wrote a library to simplify this process   dataBase   new DataBase Builder context   myDb              setAssetsPath       default  databases            setDatabaseErrorHandler              setCursorFactory              setUpgradeCallback             setVersion       default 1 build      It will create a dataBase from assets databases myDb db file  In addition  you will get all those functionality    Load database from file Synchronized access to the database Using sqlite-android by requery  Android specific distribution of the latest versions of SQLite    Clone it from github

User · Answer

My solution neither uses any third-party library nor forces you to call custom methods on SQLiteOpenHelper subclass to initialize the database on creation  It also takes care of database upgrades as well  All that needs to be done is to subclass SQLiteOpenHelper   Prerequisite    The database that you wish to ship with the app  It should contain a 1x1 table named android metadata with an attribute locale having the value en US in addition to the tables unique to your app    Subclassing SQLiteOpenHelper    Subclass SQLiteOpenHelper  Create a private method within the SQLiteOpenHelper subclass  This method contains the logic to copy database contents from the database file in the  assets  folder to the database created in the application package context  Override onCreate  onUpgrade and onOpen methods of SQLiteOpenHelper    Enough said  Here goes the SQLiteOpenHelper subclass   public class PlanDetailsSQLiteOpenHelper extends SQLiteOpenHelper       private static final String TAG    SQLiteOpenHelper        private final Context context      private static final int DATABASE VERSION   1      private static final String DATABASE NAME    my custom db        private boolean createDb   false  upgradeDb   false       public PlanDetailsSQLiteOpenHelper Context context            super context  DATABASE NAME  null  DATABASE VERSION           this context   context                        Copy packaged database from assets folder to the database created in the        application package context                  param db                   The target database in the application package context              private void copyDatabaseFromAssets SQLiteDatabase db            Log i TAG   copyDatabase            InputStream myInput   null          OutputStream myOutput   null          try                  Open db packaged as asset as the input stream             myInput   context getAssets   open  path to shipped db file                    Open the db in the application package context              myOutput   new FileOutputStream db getPath                     Transfer db file contents              byte   buffer   new byte 1024               int length              while   length   myInput read buffer    gt  0                    myOutput write buffer  0  length                             myOutput flush                    Set the version of the copied database to the current                version              SQLiteDatabase copiedDb   context openOrCreateDatabase                  DATABASE NAME  0  null               copiedDb execSQL  PRAGMA user version       DATABASE VERSION               copiedDb close               catch  IOException e                e printStackTrace                throw new Error TAG     Error copying database              finally                  Close the streams             try                   if  myOutput    null                        myOutput close                                      if  myInput    null                        myInput close                                    catch  IOException e                    e printStackTrace                    throw new Error TAG     Error closing streams                                        Override     public void onCreate SQLiteDatabase db            Log i TAG   onCreate db            createDb   true              Override     public void onUpgrade SQLiteDatabase db  int oldVersion  int newVersion            Log i TAG   onUpgrade db            upgradeDb   true              Override     public void onOpen SQLiteDatabase db            Log i TAG   onOpen db            if  createDb      The db in the application package                context is being created                 So copy the contents from the db                file packaged in the assets                folder              createDb   false              copyDatabaseFromAssets db                      if  upgradeDb      The db in the application package                context is being upgraded from a lower to a higher version              upgradeDb   false                 Your db upgrade logic here                      Finally  to get a database connection  just call getReadableDatabase   or getWritableDatabase   on the SQLiteOpenHelper subclass and it will take care of creating a db  copying db contents from the specified file in the  assets  folder  if the database does not exist   In short  you can use the SQLiteOpenHelper subclass to access the db shipped in the assets folder  just as you would use for a database that is initialized using SQL queries in the onCreate   method

User · Answer

Currently there is no way to precreate an SQLite database to ship with your apk   The best you can do is save the appropriate SQL as a resource and run them from your application   Yes  this leads to duplication of data  same information exists as a resrouce and as a database  but there is no other way right now   The only mitigating factor is the apk file is compressed   My experience is 908KB compresses to less than 268KB   The thread below has the best discussion solution I have found with good sample code   http   groups google com group android-developers msg 9f455ae93a1cf152  I stored my CREATE statement as a string resource to be read with Context getString   and ran it with SQLiteDatabse execSQL     I stored the data for my inserts in res raw inserts sql  I created the sql file  7000  lines    Using the technique from the link above I entered a loop  read the file line by line and concactenated the data onto  INSERT INTO tbl VALUE   and did another SQLiteDatabase execSQL     No sense in saving 7000  INSERT INTO tbl VALUE  s when they can just be concactenated on     It takes about twenty seconds on the emulator  I do not know how long this would take on a real phone  but it only happens once  when the user first starts the application

User · Answer

If you are using ROOM there is a pretty straight forward path already https   developer android com training data-storage room prepopulate in the official documentation  Here s how it is in action to repopulate your database from an asset file  Room databaseBuilder appContext  AppDatabase class   quot Sample db quot        createFromAsset  quot database myapp db quot        build    or from a file  Room databaseBuilder appContext  AppDatabase class   quot Sample db quot        createFromFile File  quot mypath quot         build    and if you are not using Room  I strongly recommend you do

User · Answer

If the required data is not too large  limits I don  t know  would depend on a lot of things   you might also download the data  in XML  JSON  whatever  from a website webapp  AFter receiving  execute the SQL statements using the received data creating your tables and inserting the data    If your mobile app contains lots of data  it might be easier later on to update the data in the installed apps with more accurate data or changes

User · Answer

Finally I did it   I have used this link help Using your own SQLite database in Android applications  but had to change it a little bit    If you have many packages you should put the master package name here   private static String DB PATH    data data masterPakageName databases   I changed the method which copies the database from local folder to emulator folder  It had some problem when that folder didn t exist  So first of all  it should check the path  and if it s not there  it should create the folder  In the previous code  the copyDatabase method was never called when the database didn t exist and the checkDataBase method caused exception  so I changed the code a little bit  If your database does not have a file extension  don t use the file name with one    it works nice for me   i hope it whould be usefull for u too      package farhangsarasIntroduction    import java io File  import java io FileOutputStream  import java io IOException  import java io InputStream  import java io OutputStream  import java util ArrayList  import java util HashMap   import android content Context  import android database Cursor   import android database sqlite SQLiteDatabase  import android database sqlite SQLiteException  import android database sqlite SQLiteOpenHelper   import android util Log        public class DataBaseHelper extends SQLiteOpenHelper         The Android s default system path of your application database      private static String DB PATH    data data com example sample databases        private static String DB NAME    farhangsaraDb        private SQLiteDatabase myDataBase       private final Context myContext                   Constructor         Takes and keeps a reference of the passed context in order to access to the application assets and resources           param context              public DataBaseHelper Context context             super context  DB NAME  null  1               this myContext   context                             Creates a empty database on the system and rewrites it with your own database                 public void createDataBase              boolean dbExist          try                 dbExist   checkDataBase                catch  SQLiteException e                 e printStackTrace                throw new Error  database dose not exist                        if dbExist             do nothing - database already exist          else               try                    copyDataBase                    catch  IOException e                     e printStackTrace                    throw new Error  Error copying database                         By calling this method and empty database will be created into the default system path       of your application so we are gonna be able to overwrite that database with our database          this getReadableDatabase                                    Check if the database already exist to avoid re-copying the file each time you open the application           return true if it exists  false if it doesn t              private boolean checkDataBase         SQLiteDatabase checkDB   null       try          String myPath   DB PATH       DB NAME           checkDB   SQLiteDatabase openDatabase myPath  null  SQLiteDatabase OPEN READONLY        catch SQLiteException e          database does t exist yet          throw new Error  database does t exist yet                 if checkDB    null        checkDB close                return checkDB    null   true   false                         Copies your database from your local assets-folder to the just created empty database in the         system folder  from where it can be accessed and handled          This is done by transfering bytestream                 private void copyDataBase   throws IOException                   copyDataBase                  Open your local db as the input stream             InputStream myInput   myContext getAssets   open DB NAME                   Path to the just created empty db             String outFileName   DB PATH       DB NAME              File databaseFile   new File  DB PATH                   check if databases folder exists  if not create one and its subfolders             if   databaseFile exists                     databaseFile mkdir                                 Open the empty db as the output stream             OutputStream myOutput   new FileOutputStream outFileName                  transfer bytes from the inputfile to the outputfile             byte   buffer   new byte 1024               int length              while   length   myInput read buffer   gt 0               myOutput write buffer  0  length                                Close the streams             myOutput flush                myOutput close                myInput close                     Override     public synchronized void close              if myDataBase    null          myDataBase close             super close                 Override     public void onCreate SQLiteDatabase db                   Override     public void onUpgrade SQLiteDatabase db  int oldVersion  int newVersion                 you to create adapters for your views

User · Answer

I modified the class and the answers to the question and wrote a class that allows updating the database via DB VERSION   public class DatabaseHelper extends SQLiteOpenHelper       private static String DB NAME    info db       private static String DB PATH           private static final int DB VERSION   1       private SQLiteDatabase mDataBase      private final Context mContext      private boolean mNeedUpdate   false       public DatabaseHelper Context context            super context  DB NAME  null  DB VERSION           if  android os Build VERSION SDK INT  gt   17              DB PATH   context getApplicationInfo   dataDir     databases            else             DB PATH     data data     context getPackageName       databases            this mContext   context           copyDataBase             this getReadableDatabase               public void updateDataBase   throws IOException           if  mNeedUpdate                File dbFile   new File DB PATH   DB NAME               if  dbFile exists                    dbFile delete                 copyDataBase                 mNeedUpdate   false                       private boolean checkDataBase             File dbFile   new File DB PATH   DB NAME           return dbFile exists               private void copyDataBase             if   checkDataBase                  this getReadableDatabase                this close                try                   copyDBFile                  catch  IOException mIOException                    throw new Error  ErrorCopyingDataBase                                       private void copyDBFile   throws IOException           InputStream mInput   mContext getAssets   open DB NAME             InputStream mInput   mContext getResources   openRawResource R raw info           OutputStream mOutput   new FileOutputStream DB PATH   DB NAME           byte   mBuffer   new byte 1024           int mLength          while   mLength   mInput read mBuffer    gt  0              mOutput write mBuffer  0  mLength           mOutput flush            mOutput close            mInput close               public boolean openDataBase   throws SQLException           mDataBase   SQLiteDatabase openDatabase DB PATH   DB NAME  null  SQLiteDatabase CREATE IF NECESSARY           return mDataBase    null              Override     public synchronized void close             if  mDataBase    null              mDataBase close            super close                Override     public void onCreate SQLiteDatabase db                 Override     public void onUpgrade SQLiteDatabase db  int oldVersion  int newVersion            if  newVersion  gt  oldVersion              mNeedUpdate   true            Using a class   In the activity class  declare variables   private DatabaseHelper mDBHelper  private SQLiteDatabase mDb    In the onCreate method  write the following code   mDBHelper   new DatabaseHelper this    try       mDBHelper updateDataBase      catch  IOException mIOException        throw new Error  UnableToUpdateDatabase       try       mDb   mDBHelper getWritableDatabase      catch  SQLException mSQLException        throw mSQLException      If you add a database file to the folder res raw then use the following modification of the class   public class DatabaseHelper extends SQLiteOpenHelper       private static String DB NAME    info db       private static String DB PATH           private static final int DB VERSION   1       private SQLiteDatabase mDataBase      private final Context mContext      private boolean mNeedUpdate   false       public DatabaseHelper Context context            super context  DB NAME  null  DB VERSION           if  android os Build VERSION SDK INT  gt   17              DB PATH   context getApplicationInfo   dataDir     databases            else             DB PATH     data data     context getPackageName       databases            this mContext   context           copyDataBase             this getReadableDatabase               public void updateDataBase   throws IOException           if  mNeedUpdate                File dbFile   new File DB PATH   DB NAME               if  dbFile exists                    dbFile delete                 copyDataBase                 mNeedUpdate   false                       private boolean checkDataBase             File dbFile   new File DB PATH   DB NAME           return dbFile exists               private void copyDataBase             if   checkDataBase                  this getReadableDatabase                this close                try                   copyDBFile                  catch  IOException mIOException                    throw new Error  ErrorCopyingDataBase                                       private void copyDBFile   throws IOException             InputStream mInput   mContext getAssets   open DB NAME           InputStream mInput   mContext getResources   openRawResource R raw info           OutputStream mOutput   new FileOutputStream DB PATH   DB NAME           byte   mBuffer   new byte 1024           int mLength          while   mLength   mInput read mBuffer    gt  0              mOutput write mBuffer  0  mLength           mOutput flush            mOutput close            mInput close               public boolean openDataBase   throws SQLException           mDataBase   SQLiteDatabase openDatabase DB PATH   DB NAME  null  SQLiteDatabase CREATE IF NECESSARY           return mDataBase    null              Override     public synchronized void close             if  mDataBase    null              mDataBase close            super close                Override     public void onCreate SQLiteDatabase db                 Override     public void onUpgrade SQLiteDatabase db  int oldVersion  int newVersion            if  newVersion  gt  oldVersion              mNeedUpdate   true            http   blog harrix org article 6784

User · Answer

From what I ve seen you should be be shipping a database that already has the tables setup and data  However if you want  and depending on the type of application you have  you can allow  upgrade database option   Then what you do is download the latest sqlite version  get the latest Insert Create statements of a textfile hosted online  execute the statements and do a data transfer from the old db to the new one

User · Answer

There are two options for creating and updating databases     One is to create a database externally  then place it in the assets folder of the project and then copy the entire database from there   This is much quicker if the database has a lot of tables and other components   Upgrades are triggered by changing the database version number in the res values strings xml file  Upgrades would then be accomplished by creating a new database externally  replacing the old database in the assets folder with the new database  saving the old database in internal storage under another name  copying the new database from the assets folder into internal storage  transferring all of the data from the old database  that was renamed earlier  into the new database and finally deleting the old database   You can create a database originally by using the SQLite Manager FireFox plugin to execute your creation sql statements   The other option is to create a database internally from a sql file   This is not as quick but the delay would probably be unnoticeable to the users if the database has only a few tables  Upgrades are triggered by changing the database version number in the res values strings xml file   Upgrades would then be accomplished by processing an upgrade sql file   The data in the database will remain unchanged except when its container is removed  for example dropping a table   The example below demonstrates how to use either method   Here is a sample create database sql file   It is to be placed in the assets folder of the project for the internal method or copied into the  Execute SQL  of SQLite Manager to create the database for the external method    NOTE  Notice the comment about the table required by Android    --Android requires a table named  android metadata  with a  locale  column CREATE TABLE  android metadata    locale  TEXT DEFAULT  en US    INSERT INTO  android metadata  VALUES   en US     CREATE TABLE  kitchen table   CREATE TABLE  coffee table   CREATE TABLE  pool table   CREATE TABLE  dining room table   CREATE TABLE  card table      Here is a sample update database sql file   It is to be placed in the assets folder of the project for the internal method or copied into the  Execute SQL  of SQLite Manager to create the database for the external method    NOTE  Notice that all three types of  SQL comments will be ignored by the sql parser that is included in this example    --CREATE TABLE  kitchen table    This is one type of comment in sql   It is ignored by parseSql        CREATE TABLE  coffee table   This is a second type of comment in sql   It is ignored by parseSql        CREATE TABLE  pool table    This is a third type of comment in sql   It is ignored by parseSql       CREATE TABLE  dining room table   This is a second type of comment in sql   It is ignored by parseSql       CREATE TABLE  card table   This is a third type of comment in sql   It is ignored by parseSql     --DROP TABLE  picnic table   Uncomment this if picnic table was previously created and now is being replaced  CREATE TABLE  picnic table    plates  TEXT   INSERT INTO  picnic table  VALUES   paper      Here is an entry to add to the  res values strings xml file for the database version number    lt item type  string  name  databaseVersion  format  integer  gt 1 lt  item gt    Here is an activity that accesses the database and then uses it    Note  You might want to run the database code in a separate thread if it uses a lot of resources    package android example   import android app Activity  import android database sqlite SQLiteDatabase  import android os Bundle           author Danny Remington - MacroSolve                Activity for demonstrating how to use a sqlite database      public class Database extends Activity            Called when the activity is first created           Override     public void onCreate Bundle savedInstanceState            super onCreate savedInstanceState           setContentView R layout main           DatabaseHelper myDbHelper          SQLiteDatabase myDb   null           myDbHelper   new DatabaseHelper this                         Database must be initialized before it can be used  This will ensure            that the database exists and is the current version                       myDbHelper initializeDataBase              try                  A reference to the database can be obtained after initialization              myDb   myDbHelper getWritableDatabase                                  Place code to use database here                             catch  Exception ex                ex printStackTrace               finally               try                   myDbHelper close                  catch  Exception ex                    ex printStackTrace                  finally                   myDb close                                       Here is the database helper class where the database is created or updated if necessary    NOTE  Android requires that you create a class that extends SQLiteOpenHelper in order to work with a Sqlite database    package android example   import java io FileOutputStream  import java io IOException  import java io InputStream  import java io OutputStream   import android content Context  import android database sqlite SQLiteDatabase  import android database sqlite SQLiteOpenHelper           author Danny Remington - MacroSolve                Helper class for sqlite database      public class DatabaseHelper extends SQLiteOpenHelper                  The Android s default system path of the application database in internal        storage  The package of the application is part of the path of the        directory              private static String DB DIR     data data android example databases        private static String DB NAME    database sqlite       private static String DB PATH   DB DIR   DB NAME      private static String OLD DB PATH   DB DIR    old     DB NAME       private final Context myContext       private boolean createDatabase   false      private boolean upgradeDatabase   false                  Constructor Takes and keeps a reference of the passed context in order to        access to the application assets and resources                  param context             public DatabaseHelper Context context            super context  DB NAME  null  context getResources   getInteger                  R string databaseVersion            myContext   context             Get the path of the database that is based on the context          DB PATH   myContext getDatabasePath DB NAME  getAbsolutePath                          Upgrade the database in internal storage if it exists but is not current          Create a new empty database in internal storage if it does not exist              public void initializeDataBase                           Creates or updates the database in internal storage if it is needed            before opening the database  In all cases opening the database copies            the database in internal storage to the cache                      getWritableDatabase             if  createDatabase                                  If the database is created by the copy method  then the creation                code needs to go here  This method consists of copying the new                database from assets into internal storage and then caching it                              try                                         Write over the empty data that was created in internal                    storage with the one in assets and then cache it                                      copyDataBase                  catch  IOException e                    throw new Error  Error copying database                            else if  upgradeDatabase                                  If the database is upgraded by the copy and reload method  then                the upgrade code needs to go here  This method consists of                renaming the old database in internal storage  create an empty                new database in internal storage  copying the database from                assets to the new database in internal storage  caching the new                database from internal storage  loading the data from the old                database into the new database in the cache and then deleting the                old database from internal storage                              try                   FileHelper copyFile DB PATH  OLD DB PATH                   copyDataBase                    SQLiteDatabase old db   SQLiteDatabase openDatabase OLD DB PATH  null  SQLiteDatabase OPEN READWRITE                   SQLiteDatabase new db   SQLiteDatabase openDatabase DB PATH null  SQLiteDatabase OPEN READWRITE                                         Add code to load data into the new database from the old                    database and then delete the old database from internal                    storage after all data has been transferred                                    catch  IOException e                    throw new Error  Error copying database                                                   Copies your database from your local assets-folder to the just created        empty database in the system folder  from where it can be accessed and        handled  This is done by transfering bytestream                private void copyDataBase   throws IOException                         Close SQLiteOpenHelper so it will commit the created empty database            to internal storage                      close                           Open the database in the assets folder as the input stream                      InputStream myInput   myContext getAssets   open DB NAME                          Open the empty db in interal storage as the output stream                      OutputStream myOutput   new FileOutputStream DB PATH                          Copy over the empty db in internal storage with the database in the            assets folder                      FileHelper copyFile myInput  myOutput                          Access the copied database so SQLiteHelper will cache it and mark it            as created                      getWritableDatabase   close                         This is where the creation of tables and the initial population of the        tables should happen  if a database is being created from scratch instead        of being copied from the application package assets  Copying a database        from the application package assets to internal storage inside this        method will result in a corrupted database          lt P gt         NOTE  This method is normally only called when a database has not already        been created  When the database has been copied  then this method is        called the first time a reference to the database is retrieved after the        database is copied since the database last cached by SQLiteOpenHelper is        different than the database in internal storage               Override     public void onCreate SQLiteDatabase db                          Signal that a new database needs to be copied  The copy process must            be performed after the database in the cache has been closed causing            it to be committed to internal storage  Otherwise the database in            internal storage will not have the same creation timestamp as the one            in the cache causing the database in internal storage to be marked as            corrupted                      createDatabase   true                         This will create by reading a sql file and executing the commands in            it                             try                  InputStream is   myContext getResources   getAssets   open                  create database sql                                  String   statements   FileHelper parseSqlFile is                                 for  String statement   statements                   db execSQL statement                                     catch  Exception ex                   ex printStackTrace                                           Called only if version number was changed and the database has already        been created  Copying a database from the application package assets to        the internal data system inside this method will result in a corrupted        database in the internal data system               Override     public void onUpgrade SQLiteDatabase db  int oldVersion  int newVersion                          Signal that the database needs to be upgraded for the copy method of            creation  The copy process must be performed after the database has            been opened or the database will be corrupted                      upgradeDatabase   true                         Code to update the database via execution of sql statements goes            here                                     This will upgrade by reading a sql file and executing the commands in            it                         try              InputStream is   myContext getResources   getAssets   open              upgrade database sql                          String   statements   FileHelper parseSqlFile is                         for  String statement   statements               db execSQL statement                             catch  Exception ex               ex printStackTrace                                       Called everytime the database is opened by getReadableDatabase or        getWritableDatabase  This is called after onCreate or onUpgrade is        called               Override     public void onOpen SQLiteDatabase db            super onOpen db                        Add your public helper methods to access and get content from the        database  You could return cursors by doing         return myDataBase query        so it d be easy to you to create adapters        for your views               Here s the FileHelper class that contains methods for byte stream copying files and parsing sql files   package android example   import java io BufferedReader  import java io File  import java io FileInputStream  import java io FileOutputStream  import java io FileReader  import java io IOException  import java io InputStream  import java io InputStreamReader  import java io OutputStream  import java io Reader  import java nio channels FileChannel           author Danny Remington - MacroSolve                Helper class for common tasks using files          public class FileHelper                  Creates the specified  lt i gt  lt b gt toFile lt  b gt  lt  i gt  that is a byte for byte a copy        of  lt i gt  lt b gt fromFile lt  b gt  lt  i gt   If  lt i gt  lt b gt toFile lt  b gt  lt  i gt  already existed  then        it will be replaced with a copy of  lt i gt  lt b gt fromFile lt  b gt  lt  i gt   The name and        path of  lt i gt  lt b gt toFile lt  b gt  lt  i gt  will be that of  lt i gt  lt b gt toFile lt  b gt  lt  i gt   Both         lt i gt  lt b gt fromFile lt  b gt  lt  i gt  and  lt i gt  lt b gt toFile lt  b gt  lt  i gt  will be closed by this        operation                  param fromFile                   - InputStream for the file to copy from          param toFile                   - InputStream for the file to copy to              public static void copyFile InputStream fromFile  OutputStream toFile  throws IOException              transfer bytes from the inputfile to the outputfile         byte   buffer   new byte 1024           int length           try               while   length   fromFile read buffer    gt  0                    toFile write buffer  0  length                                      Close the streams         finally               try                   if  toFile    null                        try                           toFile flush                          finally                           toFile close                                                      finally                   if  fromFile    null                        fromFile close                                                                    Creates the specified  lt i gt  lt b gt toFile lt  b gt  lt  i gt  that is a byte for byte a copy        of  lt i gt  lt b gt fromFile lt  b gt  lt  i gt   If  lt i gt  lt b gt toFile lt  b gt  lt  i gt  already existed  then        it will be replaced with a copy of  lt i gt  lt b gt fromFile lt  b gt  lt  i gt   The name and        path of  lt i gt  lt b gt toFile lt  b gt  lt  i gt  will be that of  lt i gt  lt b gt toFile lt  b gt  lt  i gt   Both         lt i gt  lt b gt fromFile lt  b gt  lt  i gt  and  lt i gt  lt b gt toFile lt  b gt  lt  i gt  will be closed by this        operation                  param fromFile                   - String specifying the path of the file to copy from          param toFile                   - String specifying the path of the file to copy to              public static void copyFile String fromFile  String toFile  throws IOException           copyFile new FileInputStream fromFile   new FileOutputStream toFile                          Creates the specified  lt i gt  lt b gt toFile lt  b gt  lt  i gt  that is a byte for byte a copy        of  lt i gt  lt b gt fromFile lt  b gt  lt  i gt   If  lt i gt  lt b gt toFile lt  b gt  lt  i gt  already existed  then        it will be replaced with a copy of  lt i gt  lt b gt fromFile lt  b gt  lt  i gt   The name and        path of  lt i gt  lt b gt toFile lt  b gt  lt  i gt  will be that of  lt i gt  lt b gt toFile lt  b gt  lt  i gt   Both         lt i gt  lt b gt fromFile lt  b gt  lt  i gt  and  lt i gt  lt b gt toFile lt  b gt  lt  i gt  will be closed by this        operation                  param fromFile                   - File for the file to copy from          param toFile                   - File for the file to copy to              public static void copyFile File fromFile  File toFile  throws IOException           copyFile new FileInputStream fromFile   new FileOutputStream toFile                          Creates the specified  lt i gt  lt b gt toFile lt  b gt  lt  i gt  that is a byte for byte a copy        of  lt i gt  lt b gt fromFile lt  b gt  lt  i gt   If  lt i gt  lt b gt toFile lt  b gt  lt  i gt  already existed  then        it will be replaced with a copy of  lt i gt  lt b gt fromFile lt  b gt  lt  i gt   The name and        path of  lt i gt  lt b gt toFile lt  b gt  lt  i gt  will be that of  lt i gt  lt b gt toFile lt  b gt  lt  i gt   Both         lt i gt  lt b gt fromFile lt  b gt  lt  i gt  and  lt i gt  lt b gt toFile lt  b gt  lt  i gt  will be closed by this        operation                  param fromFile                   - FileInputStream for the file to copy from          param toFile                   - FileInputStream for the file to copy to              public static void copyFile FileInputStream fromFile  FileOutputStream toFile  throws IOException           FileChannel fromChannel   fromFile getChannel            FileChannel toChannel   toFile getChannel             try               fromChannel transferTo 0  fromChannel size    toChannel             finally               try                   if  fromChannel    null                        fromChannel close                                    finally                   if  toChannel    null                        toChannel close                                                                    Parses a file containing sql statements into a String array that contains        only the sql statements  Comments and white spaces in the file are not        parsed into the String array  Note the file must not contained malformed        comments and all sql statements must end with a semi-colon     in order        for the file to be parsed correctly  The sql statements in the String        array will not end with a semi-colon                      param sqlFile                   - String containing the path for the file that contains sql                   statements                  return String array containing the sql statements              public static String   parseSqlFile String sqlFile  throws IOException           return parseSqlFile new BufferedReader new FileReader sqlFile                           Parses a file containing sql statements into a String array that contains        only the sql statements  Comments and white spaces in the file are not        parsed into the String array  Note the file must not contained malformed        comments and all sql statements must end with a semi-colon     in order        for the file to be parsed correctly  The sql statements in the String        array will not end with a semi-colon                      param sqlFile                   - InputStream for the file that contains sql statements                  return String array containing the sql statements              public static String   parseSqlFile InputStream sqlFile  throws IOException           return parseSqlFile new BufferedReader new InputStreamReader sqlFile                           Parses a file containing sql statements into a String array that contains        only the sql statements  Comments and white spaces in the file are not        parsed into the String array  Note the file must not contained malformed        comments and all sql statements must end with a semi-colon     in order        for the file to be parsed correctly  The sql statements in the String        array will not end with a semi-colon                      param sqlFile                   - Reader for the file that contains sql statements                  return String array containing the sql statements              public static String   parseSqlFile Reader sqlFile  throws IOException           return parseSqlFile new BufferedReader sqlFile                          Parses a file containing sql statements into a String array that contains        only the sql statements  Comments and white spaces in the file are not        parsed into the String array  Note the file must not contained malformed        comments and all sql statements must end with a semi-colon     in order        for the file to be parsed correctly  The sql statements in the String        array will not end with a semi-colon                      param sqlFile                   - BufferedReader for the file that contains sql statements                  return String array containing the sql statements              public static String   parseSqlFile BufferedReader sqlFile  throws IOException           String line          StringBuilder sql   new StringBuilder            String multiLineComment   null           while   line   sqlFile readLine       null                line   line trim                    Check for start of multi-line comment             if  multiLineComment    null                       Check for first multi-line comment type                 if  line startsWith                              if   line endsWith                                 multiLineComment                                                  Check for second multi-line comment type                   else if  line startsWith                             if   line endsWith                                 multiLineComment                                             Append line if line is not empty or a single line comment                   else if   line startsWith  --    amp  amp   line equals                            sql append line                        Check for matching end comment               else if  multiLineComment equals                          if  line endsWith                              multiLineComment   null                                   Check for matching end comment               else if  multiLineComment equals                         if  line endsWith                             multiLineComment   null                                                      sqlFile close             return sql toString   split

User · Answer

I m using ORMLite and below code worked for me  public class DatabaseProvider extends OrmLiteSqliteOpenHelper       private static final String DatabaseName    DatabaseName       private static final int DatabaseVersion   1      private final Context ProvidedContext       public DatabaseProvider Context context            super context  DatabaseName  null  DatabaseVersion           this ProvidedContext  context          SharedPreferences preferences   PreferenceManager getDefaultSharedPreferences context           boolean databaseCopied   preferences getBoolean  DatabaseCopied   false           if  databaseCopied                  Do Nothing           else               CopyDatabase                SharedPreferences Editor editor   preferences edit                editor putBoolean  DatabaseCopied   true               editor commit                         private String DatabasePath             return   data data     ProvidedContext getPackageName       databases               private void CopyDatabase             try               CopyDatabaseInternal              catch  IOException e                e printStackTrace                         private File ExtractAssetsZip String zipFileName            InputStream inputStream          ZipInputStream zipInputStream          File tempFolder          do               tempFolder   null              tempFolder   new File ProvidedContext getCacheDir       extracted-    System currentTimeMillis                     while  tempFolder exists              tempFolder mkdirs             try               String filename              inputStream   ProvidedContext getAssets   open zipFileName               zipInputStream   new ZipInputStream new BufferedInputStream inputStream                ZipEntry zipEntry              byte   buffer   new byte 1024               int count               while   zipEntry   zipInputStream getNextEntry       null                    filename   zipEntry getName                    if  zipEntry isDirectory                          File fmd   new File tempFolder getAbsolutePath           filename                       fmd mkdirs                        continue                                     FileOutputStream fileOutputStream   new FileOutputStream tempFolder getAbsolutePath           filename                   while   count   zipInputStream read buffer      -1                        fileOutputStream write buffer  0  count                                      fileOutputStream close                    zipInputStream closeEntry                               zipInputStream close              catch  IOException e                e printStackTrace                return null                     return tempFolder             private void CopyDatabaseInternal   throws IOException            File extractedPath   ExtractAssetsZip DatabaseName     zip            String databaseFile               for  File innerFile   extractedPath listFiles                  databaseFile   innerFile getAbsolutePath                break                    if  databaseFile    null    databaseFile length     0               throw new RuntimeException  databaseFile is empty             InputStream inputStream   new FileInputStream databaseFile            String outFileName   DatabasePath     DatabaseName           File destinationPath   new File DatabasePath             if   destinationPath exists                destinationPath mkdirs             File destinationFile   new File outFileName           if   destinationFile exists                destinationFile createNewFile             OutputStream myOutput   new FileOutputStream outFileName            byte   buffer   new byte 1024           int length          while   length   inputStream read buffer    gt  0                myOutput write buffer  0  length                      myOutput flush            myOutput close            inputStream close                Override     public void onCreate SQLiteDatabase sqLiteDatabase  ConnectionSource connectionSource                Override     public void onUpgrade SQLiteDatabase sqLiteDatabase  ConnectionSource connectionSource  int fromVersion  int toVersion               Please note  The code extracts database file from a zip file in assets

User · Answer

The SQLiteAssetHelper library makes this task really simple   It s easy to add as a gradle dependency  but a Jar is also available for Ant Eclipse   and together with the documentation it can be found at  https   github com jgilfelt android-sqlite-asset-helper  Note  This project is no longer maintained as stated on above Github link   As explained in documentation    Add the dependency to your module s gradle build file   dependencies       compile  com readystatesoftware sqliteasset sqliteassethelper       Copy the database into the assets directory  in a subdirectory called assets databases  For instance  assets databases my database db   Optionally  you may compress the database in a zip file such as assets databases my database zip  This isn t needed  since the APK is compressed as a whole already   Create a class  for example   public class MyDatabase extends SQLiteAssetHelper        private static final String DATABASE NAME    my database db       private static final int DATABASE VERSION   1       public MyDatabase Context context            super context  DATABASE NAME  null  DATABASE VERSION

User · Answer

Currently there is no way to precreate an SQLite database to ship with your apk   The best you can do is save the appropriate SQL as a resource and run them from your application   Yes  this leads to duplication of data  same information exists as a resrouce and as a database  but there is no other way right now   The only mitigating factor is the apk file is compressed   My experience is 908KB compresses to less than 268KB   The thread below has the best discussion solution I have found with good sample code   http   groups google com group android-developers msg 9f455ae93a1cf152  I stored my CREATE statement as a string resource to be read with Context getString   and ran it with SQLiteDatabse execSQL     I stored the data for my inserts in res raw inserts sql  I created the sql file  7000  lines    Using the technique from the link above I entered a loop  read the file line by line and concactenated the data onto  INSERT INTO tbl VALUE   and did another SQLiteDatabase execSQL     No sense in saving 7000  INSERT INTO tbl VALUE  s when they can just be concactenated on     It takes about twenty seconds on the emulator  I do not know how long this would take on a real phone  but it only happens once  when the user first starts the application

User · Answer

From what I ve seen you should be be shipping a database that already has the tables setup and data  However if you want  and depending on the type of application you have  you can allow  upgrade database option   Then what you do is download the latest sqlite version  get the latest Insert Create statements of a textfile hosted online  execute the statements and do a data transfer from the old db to the new one

User · Answer

Currently there is no way to precreate an SQLite database to ship with your apk   The best you can do is save the appropriate SQL as a resource and run them from your application   Yes  this leads to duplication of data  same information exists as a resrouce and as a database  but there is no other way right now   The only mitigating factor is the apk file is compressed   My experience is 908KB compresses to less than 268KB   The thread below has the best discussion solution I have found with good sample code   http   groups google com group android-developers msg 9f455ae93a1cf152  I stored my CREATE statement as a string resource to be read with Context getString   and ran it with SQLiteDatabse execSQL     I stored the data for my inserts in res raw inserts sql  I created the sql file  7000  lines    Using the technique from the link above I entered a loop  read the file line by line and concactenated the data onto  INSERT INTO tbl VALUE   and did another SQLiteDatabase execSQL     No sense in saving 7000  INSERT INTO tbl VALUE  s when they can just be concactenated on     It takes about twenty seconds on the emulator  I do not know how long this would take on a real phone  but it only happens once  when the user first starts the application

User · Answer

In November 2017 Google released the Room Persistence Library  From the documentation   The Room persistence library provides an abstraction layer over SQLite to allow fluent database access while harnessing the full power of SQLite  The library helps you create a cache of your app s data on a device that s running your app  This cache  which serves as your app s single source of truth  allows users to view a consistent copy of the key information within your app  regardless of whether users have an internet connection   The Room database has a callback when the database is first created or opened  You can use the create callback to populate your database  Room databaseBuilder context applicationContext          DataDatabase  class java   quot Sample db quot              prepopulate the database after onCreate was called          addCallback object   Callback                 override fun onCreate db  SupportSQLiteDatabase                    super onCreate db                     moving to a new thread                 ioThread                       getInstance context  dataDao                                            insert PREPOPULATE DATA                                                      build    Code from this blog post

User · Answer

From what I ve seen you should be be shipping a database that already has the tables setup and data  However if you want  and depending on the type of application you have  you can allow  upgrade database option   Then what you do is download the latest sqlite version  get the latest Insert Create statements of a textfile hosted online  execute the statements and do a data transfer from the old db to the new one

User · Answer

Shipping the app with a database file  in Android Studio 3 0 Shipping the app with a database file is a good idea for me  The advantage is that you don t need to do a complex initialization  which sometimes costs lots of time  if your data set is huge  Step 1  Prepare database file Have your database file ready  It can be either a  db file or a  sqlite file  If you use a  sqlite file  all you need to do is to change file extension names  The steps are the same  In this example  I prepared a file called testDB db  It has one table and some sample data in it like this  Step 2  Import the file into your project Create the assets folder if you haven t had one  Then copy and paste the database file into this folder  Step 3  Copy the file to the app s data folder You need to copy the database file to the app s data folder in order to do further interaction with it  This is a one time action  initialization  to copy the database file  If you call this code multiple times  the database file in data folder will be overwritten by the one in assets folder  This overwrite process is useful when you want to update the database in future during the app update  Note that during app update  this database file will not be changed in the app s data folder  Only uninstall will delete it  The database file needs to be copied to  databases folder  Open Device File Explorer  Enter data data  lt YourAppName gt   location  This is the app s default data folder mentioned above  And by default  the database file will be place in another folder called databases under this directory  Now  the copy file process is pretty much like the what Java is doing  Use the following code to do the copy paste  This is the initiation code  It can also be used to update by overwriting  the database file in future    get context by calling  quot this quot  in activity or getActivity   in fragment   call this if API level is lower than 17  String appDataPath    quot  data data  quot    context getPackageName      quot  databases  quot  String appDataPath   context getApplicationInfo   dataDir   File dbFolder   new File appDataPath    quot  databases quot     Make sure the  databases folder exists dbFolder mkdir     This can be called multiple times   File dbFilePath   new File appDataPath    quot  databases testDB db quot     try       InputStream inputStream   context getAssets   open  quot testDB db quot        OutputStream outputStream   new FileOutputStream dbFilePath       byte   buffer   new byte 1024       int length      while   length   inputStream read buffer   gt 0                outputStream write buffer  0  length             outputStream flush        outputStream close        inputStream close      catch  IOException e         handle    Then refresh the folder to verify the copy process  Step 4  Create database open helper Create a subclass for SQLiteOpenHelper  with connect  close  path  etc  I named it DatabaseOpenHelper import android content Context  import android database SQLException  import android database sqlite SQLiteDatabase  import android database sqlite SQLiteOpenHelper   public class DatabaseOpenHelper extends SQLiteOpenHelper       public static final String DB NAME    quot testDB db quot       public static final String DB SUB PATH    quot  databases  quot    DB NAME      private static String APP DATA PATH    quot  quot       private SQLiteDatabase dataBase      private final Context context       public DatabaseOpenHelper Context context           super context  DB NAME  null  1           APP DATA PATH   context getApplicationInfo   dataDir          this context   context             public boolean openDataBase   throws SQLException          String mPath   APP DATA PATH   DB SUB PATH            Note that this method assumes that the db file is already copied in place         dataBase   SQLiteDatabase openDatabase mPath  null  SQLiteDatabase OPEN READWRITE           return dataBase    null              Override     public synchronized void close            if dataBase    null   dataBase close             super close                Override     public void onCreate SQLiteDatabase db                Override     public void onUpgrade SQLiteDatabase db  int oldVersion  int newVersion             Step 5  Create top level class to interact with the database This will be the class that read  amp  write your database file  Also there is a sample query to print out the value in the database  import android content Context  import android database Cursor  import android database SQLException  import android database sqlite SQLiteDatabase  import android util Log   public class Database       private final Context context      private SQLiteDatabase database      private DatabaseOpenHelper dbHelper       public Database Context context           this context   context          dbHelper   new DatabaseOpenHelper context              public Database open   throws SQLException               dbHelper openDataBase            dbHelper close            database   dbHelper getReadableDatabase            return this             public void close                 dbHelper close               public void test            try              String query   quot SELECT value FROM test1 quot               Cursor cursor   database rawQuery query  null               if  cursor moveToFirst                     do                      String value   cursor getString 0                       Log d  quot db quot   value                    while  cursor moveToNext                               cursor close              catch  SQLException e                  handle                    Step 6  Test running Test the code by running the following lines of codes  Database db   new Database context   db open    db test    db close     Hit the run button and cheer

[android] Ship an application with a database

Examples related to android

Examples related to android-sqlite

Examples related to android-database