What you do is copy the pre-existing databases as developed generally with an SQlite management tool (e.g. DB Browser for SQLite, Navicat etc), to the assets folder (must be assets/databases folder if using SQLiteAssetHelper (see readme)).
- Note you may need to create the folder(s)
- Note it is best to copy the file outside of Android Studio, as Android Studio may attempt to open the file, which can cause confusion.
- Note make sure that the database has been saved (I'd suggest closing the tool and re-opening it to check).
The databases need to be copied from the assets folder (assets/database folder) to a suitable location (SQLiteAssetHelper is one way) but only when the database doesn't exist. Although SQLiteAssetHelper is frequently recommended, it unfortunately doesn't appear to work for Android 9+ (Pie). As such you may wish to have a look at the Entire DBHelper the above code was extracted from, in this answer
This answer is a step by step guide to using SQLiteAssetHelper [Reading a database from the assets folder a database from the assets folder). Steps 1-3 can be followed (except creating/copying to the databases folder) if not using SQLiteAssetHelper
You have to change the DBNAME accordingly and this expects the database file to be in the assets folder rather than assets/databases. The DBNAME will be the file name with the extension if the file has one.
- The code is relatively lengthy as it includes logging for debugging purposes. You likely wish to remove the debugging prior to deploying the App.
The assets will be part of the APK (size restrictions permitting).
If you use SQLiteAssetHelper you need one per database. The Database Helper as per the link could be used multiple times are be adapted for multiple databases.
Example
The following example copies 3 pre-existing databases namely mydb001, mydb002 and mydb003. The first 2 are in the assets folder, the 3rd has been placed into the assets/databases folder (for demonstration).
If the databases need to be copied they will be. If they have been previously copied they aren't re-copied.
There are two classes used OpenAssetDBHelper.java and MainActivity.java. The former being the cllass that does all the work. Note that it has pretty extensive logging and is therefore quite lengthy. Of note is that it can handle mutliple Database Helpers up to 10 but this can be changed by changing public static final int MAXIMUM_HELPERS = 10;.
This has been checked on an emulated Android Pie device.
OpenAssetDBHelper.java
:-
public class OpenAssetDBHelper extends SQLiteOpenHelper {
private static final String LOGTAG = "OADB-HLPR";
public static final int MAXIMUM_HELPERS = 10;
private String mDBPath, mAssetPath;
private static OpenAssetDBHelper mInstance[] = new OpenAssetDBHelper[MAXIMUM_HELPERS];
private SQLiteDatabase mDB;
/**
* OpenAssetDBHelper Class that will copy a predefined database
* from the assets folder and then open it is the database;
*
* The copy will only be done if the database does not exist.
*
* Note this code is intended to be used for development and/or
* experimentation, hence the extensive logging.
*/
/**
* get an OpenAssetDBHelper instance as a singleton;
* Note! caters for up to 10 OpenAssetDBHelpers for up to 10 databases
* as specified by the helper_index
*
* @param helper_index Index to this instance/database
* (0-MAXIMUM_HELPERS less 1)
* @param context Context for the database
* @param database Database name (i.e. file name)
* @param dbversion Database version (user_version)
* @param assetfile Name of the asset file to be copied to the database
* @param asset_sub_directories String Array of the sub-directories where
* the asset file is located;
* MUST be in order
* @return The resultant OpenAssetDBHelper
*/
public static synchronized OpenAssetDBHelper getInstance(
int helper_index,
Context context,
String database,
int dbversion,
String assetfile,
String[] asset_sub_directories) {
// Checck that helper_index is within bounds
if (helper_index > (MAXIMUM_HELPERS -1)) {
throw new RuntimeException(
"Helper Index greater than " +
MAXIMUM_HELPERS
);
}
if (helper_index < 0) {
throw new RuntimeException(
"Helper Index cannot be negative, must be 0-" +
MAXIMUM_HELPERS
);
}
// Create the respective OpenAssetDBHelper instance
if(mInstance[helper_index] == null) {
mInstance[helper_index] = new OpenAssetDBHelper(context,
database,
dbversion,
assetfile,
asset_sub_directories);
}
return mInstance[helper_index];
}
/**
* Construct an OpenAssetDBHelper instance;
* Note! can only be called within class
*
* @param context the context to be used
* @param database the database name (equates to filename)
* @param dbversion the databaae version (user_version)
* @param assetfile The name of the asset file i.e. the pre-defined db
* @param directories The hierarchy of directories within the assets folder
* where the asset file is located
* (null or zero elements = in the assets folder)
*/
private OpenAssetDBHelper(Context context,
String database,
int dbversion,
String assetfile,
String[] directories) {
super(context, database, null, dbversion);
Log.d(LOGTAG,"OpenAssetDBHelper being constructed.");
mDBPath = context.getDatabasePath(database).getPath();
if (assetfile == null || assetfile.length() < 1) {
assetfile = database;
}
mAssetPath = buildAssetPath(directories,assetfile);
if (!ifDatabaseExists(mDBPath)) {
Log.d(LOGTAG,"Database " + database + " not found at " + mDBPath + " so attempting to copy from assets.");
if (copyDatabaseFromAssets(context,mDBPath, database, mAssetPath)) {
Log.d(LOGTAG, "Successfully Copied Database from Assets.");
} else {
throw new RuntimeException("No Database Available.");
}
}
// Force Database open and store it
this.mDB = this.getWritableDatabase();
//logDatabaseTableInformation(mDB);
Log.d(LOGTAG,"OpenAssetDBHelper constructed.");
}
/**
* onCreate - This is where you would create tables;
* Typically this is where you would alter the structure of the database;
* Note that this is called once for the lifetime of the database.
* @param db The SQLitedatabase
*/
@Override
public void onCreate(SQLiteDatabase db) {
Log.d(LOGTAG,new Object(){}.getClass().getEnclosingMethod().getName() + " initiated.");
// As Database is copied from assets nothing to do in onCreate!
Log.d(LOGTAG,new Object(){}.getClass().getEnclosingMethod().getName() + " completed.");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
Log.d(LOGTAG,new Object(){}.getClass().getEnclosingMethod().getName() + " initiated.");
// Nothing to do as it's early days in the Database's lifetime.
Log.d(LOGTAG,new Object(){}.getClass().getEnclosingMethod().getName() + " completed.");
}
/**
* Check to see if the Database exists,
* if it doesn't exists then check to see if
* the database directory exists,
* if the directory(ies) does(do) not exist then make the directory(ies);
*
*
* @param dbpath The path to the database
* @return true if the database exists, else false
*/
private boolean ifDatabaseExists(String dbpath) {
Log.d(LOGTAG,new Object(){}.getClass().getEnclosingMethod().getName() + " initiated.");
File db = new File(dbpath);
if(db.exists()) return true;
File dir = new File(db.getParent());
if (!dir.exists()) {
dir.mkdirs();
}
return false;
}
/**
* Copy the Database from the assets folder
* @param context
* @param dbpath
* @return
*/
private boolean copyDatabaseFromAssets(Context context,String dbpath, String dbname, String asset) {
String thisclass = new Object(){}.getClass().getEnclosingMethod().getName();
Log.d(LOGTAG,thisclass + " initiated");
InputStream assetsdb;
OutputStream database;
File db = new File(dbpath);
int filesize;
// Get the asset file
try {
Log.d(LOGTAG,thisclass + " attempting to find asset " + asset);
assetsdb = context.getAssets().open(asset);
filesize = assetsdb.available();
Log.d(LOGTAG,thisclass + " asset " + asset +
" located successfully with a size of " +
Integer.toString(filesize)
);
} catch (IOException e) {
Log.d(LOGTAG,thisclass + " Did not locate asset " + asset);
e.printStackTrace();
return false;
}
// Read the first 16 bytes from the asset file
byte[] dbcheck = new byte[16];
try {
assetsdb.read(dbcheck,0,16);
} catch (IOException e) {
Log.d(LOGTAG, thisclass + " failed trying to read 16 bytes to check for a valid database. ");
e.printStackTrace();
return false;
}
// Check that the asset file is an SQLite database
String chkdb = new String(dbcheck);
if(!chkdb.equals("SQLite format 3\u0000")) {
Log.d(LOGTAG,thisclass + " asset " +
asset +
" is not a valid SQLite Database File (found " +
chkdb +
" at bytes 1-16 instead of SQLite format 3)");
try {
assetsdb.close();
} catch (IOException e) {
// Not worth doing anything
}
return false;
}
// Close the asset file
try {
assetsdb.close();
} catch (IOException e) {
Log.d(LOGTAG,thisclass +
" failed to close assets file after checking for a valid database."
);
return false;
}
// Re-open the asset file
try {
assetsdb = context.getAssets().open(asset);
filesize = assetsdb.available();
} catch (IOException e) {
Log.d(LOGTAG, thisclass +
" failed trying to re-open asset " +
asset +
" after checking for a valid database."
);
e.printStackTrace();
return false;
}
// Read the entire asset file into a buffer
Log.d(LOGTAG, thisclass +
" copying asset database " +
dbname +
" into buffer of size " +
filesize
);
byte[] buffer = new byte[filesize];
// Close the asset file
try {
assetsdb.read(buffer);
Log.d(LOGTAG,thisclass +
" closing asset database " + dbname
);
assetsdb.close();
} catch (IOException e) {
Log.d(LOGTAG, thisclass +
" failed while copying asset database " +
dbname +
" (or closing asset database)."
);
e.printStackTrace();
return false;
}
// Open the new database file
try {
Log.d(LOGTAG,thisclass + " attempting to open new database file " + dbpath);
database = new FileOutputStream(dbpath);
} catch (IOException e) {
Log.d(LOGTAG, thisclass + " failed to open new database file.");
e.printStackTrace();
return false;
}
// Write the new database file
try {
Log.d(LOGTAG, thisclass + " writing new database file " + dbpath);
database.write(buffer);
} catch (IOException e) {
Log.d(LOGTAG, thisclass + " failed while writing new database file " + dbpath);
e.printStackTrace();
return false;
}
// Flush the new database file
try {
Log.d(LOGTAG, thisclass + " flushing new database file " + dbpath);
database.flush();
} catch (IOException e) {
Log.d(LOGTAG, thisclass + " failed while flushing new database file " + dbpath);
e.printStackTrace();
return false;
}
// Close the new database file
try {
Log.d(LOGTAG, thisclass + " closing new database file " + dbpath);
database.close();
} catch (IOException e) {
Log.d(LOGTAG, thisclass + " failed while closing new database file " + dbpath);
e.printStackTrace();
return false;
}
Log.d(LOGTAG,new Object(){}.getClass().getEnclosingMethod().getName() + " completed.");
return true;
}
/**
* Log Database table Information
*/
private void logDatabaseTableInformation(SQLiteDatabase db) {
Log.d(LOGTAG,new Object(){}.getClass().getEnclosingMethod().getName() + " initiated.");
String mastertable = "sqlite_master";
String typecolumn = "type";
String namecolumn = "name";
String sqlcolumn = "sql";
String[] args = new String[]{"table","android_metadata"};
Cursor csr = db.query(mastertable,
null,
typecolumn + "=? AND " + namecolumn + "!=?",
args,
null,null,null
);
while (csr.moveToNext()) {
Log.d(LOGTAG,"Database contains Table " +
csr.getString(csr.getColumnIndex(namecolumn)) +
" created by SQL " +
csr.getString(csr.getColumnIndex(sqlcolumn))
);
logTableInformation(db, csr.getString(csr.getColumnIndex(namecolumn)));
}
csr.close();
Log.d(LOGTAG,new Object(){}.getClass().getEnclosingMethod().getName() + " completed.");
}
/**
* Write Table information, Table name, Column Count,
* Row Count and Column Names to the Log
* @param table Name of the table to be reported on
*/
private void logTableInformation(SQLiteDatabase db, String table) {
Cursor csr = db.query(table,
null,
null,
null,
null,
null,
null
);
Log.d(LOGTAG,"Table is " +
table +
" Column Count = " +
Integer.toString(csr.getColumnCount()) +
" Row Count = " +
Long.toString(DatabaseUtils.queryNumEntries(mDB,table))
);
StringBuilder columns_as_string = new StringBuilder();
for (String s: csr.getColumnNames()) {
columns_as_string.append(s).append(" ");
}
Log.d(LOGTAG, "\tColumns are :- " + columns_as_string);
csr.close();
}
/**
* Build the sub-path to the asset, according to the directories specified
*
* @param directories directories underneath the assets folder where
* the asset files is located, null or empty
* array if file is located directly in the
* assets folder;
* directories must be specified in the order
* in which they appear in the path.
* @param filename The filename of the asset
* @return The fill sub-path to the asset
*/
private String buildAssetPath(String[] directories, String filename) {
StringBuilder sb = new StringBuilder();
final String SEPERATOR = "/";
if (directories != null && directories.length > 0) {
for (String s: directories) {
sb.append(s);
if (!s.substring(s.length()-1,s.length()).equals(SEPERATOR)) {
sb.append(SEPERATOR);
}
}
sb.append(filename);
return sb.toString();
} else {
return filename;
}
}
public static OpenAssetDBHelper getHelperInstance(int instance) {
if (instance < mInstance.length && instance >= 0) {
return mInstance[instance];
}
else return mInstance[0];
}
}
- The code could be extensively reduced, it is lengthy as it's intended to assist with development and understanding.
MainActivity.java
:-
public class MainActivity extends AppCompatActivity {
public static final int DBVERSION = 1;
public static final String DBNAME1 = "mydb001"; // in assets
public static final int DB1_INDEX = 0;
public static final String DBNAME2 = "mydb002"; // in assets
public static final int DB2_INDEX = 1;
public static final String DBNAME3 = "mydb003"; //in assets/databases
public static final int DB3_INDEX = 2;
OpenAssetDBHelper[] mDBHlpr = new OpenAssetDBHelper[3];
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
// Example 1 - Database file located in the assets folder and
// database name same as file name
mDBHlpr[0] = OpenAssetDBHelper.getInstance(DB1_INDEX,
this,
DBNAME1,
DBVERSION,
DBNAME1,
null //<<<<<<<<<< null = db file (pre-existing db) located in the assets folder
);
// mydb002
mDBHlpr[1] = OpenAssetDBHelper.getInstance(DB2_INDEX,
this,
DBNAME2,
DBVERSION,
"mydb002",
null
);
// mydb003 in the assets/database folder
mDBHlpr[2] = OpenAssetDBHelper.getInstance(DB3_INDEX,
this,
DBNAME3,
DBVERSION,
"mydb003",
new String[]{"databases"} //<<<<<<<<<<< array of sub-folders under assets (order matters)
);
// Do something with each Database
for (OpenAssetDBHelper o:mDBHlpr) {
logDBSchemaInfo(o.getWritableDatabase());
}
}
//Dump/Log SQLitemaster
private void logDBSchemaInfo(SQLiteDatabase db) {
DatabaseUtils.dumpCursor(
db.query(
"sqlite_master",null,null,null,null,null,null
)
);
}
}
Result
The log, for the first run (in this case), includes :-
2019-04-29 15:41:56.329 D/OADB-HLPR: ifDatabaseExists initiated.
2019-04-29 15:41:56.330 D/OADB-HLPR: Database mydb001 not found at /data/user/0/mjt.openmultipleassetdataabses/databases/mydb001 so attempting to copy from assets.
2019-04-29 15:41:56.331 D/OADB-HLPR: copyDatabaseFromAssets initiated
2019-04-29 15:41:56.331 D/OADB-HLPR: copyDatabaseFromAssets attempting to find asset mydb001
2019-04-29 15:41:56.331 D/OADB-HLPR: copyDatabaseFromAssets asset mydb001 located successfully with a size of 77824
2019-04-29 15:41:56.331 D/OADB-HLPR: copyDatabaseFromAssets copying asset database mydb001 into buffer of size 77824
2019-04-29 15:41:56.332 D/OADB-HLPR: copyDatabaseFromAssets closing asset database mydb001
2019-04-29 15:41:56.332 D/OADB-HLPR: copyDatabaseFromAssets attempting to open new database file /data/user/0/mjt.openmultipleassetdataabses/databases/mydb001
2019-04-29 15:41:56.332 D/OADB-HLPR: copyDatabaseFromAssets writing new database file /data/user/0/mjt.openmultipleassetdataabses/databases/mydb001
2019-04-29 15:41:56.332 D/OADB-HLPR: copyDatabaseFromAssets flushing new database file /data/user/0/mjt.openmultipleassetdataabses/databases/mydb001
2019-04-29 15:41:56.332 D/OADB-HLPR: copyDatabaseFromAssets closing new database file /data/user/0/mjt.openmultipleassetdataabses/databases/mydb001
2019-04-29 15:41:56.333 D/OADB-HLPR: copyDatabaseFromAssets completed.
2019-04-29 15:41:56.333 D/OADB-HLPR: Successfully Copied Database from Assets.
2019-04-29 15:41:56.353 D/OADB-HLPR: OpenAssetDBHelper constructed.
2019-04-29 15:41:56.353 D/OADB-HLPR: OpenAssetDBHelper being constructed.
2019-04-29 15:41:56.354 D/OADB-HLPR: ifDatabaseExists initiated.
2019-04-29 15:41:56.354 D/OADB-HLPR: Database mydb002 not found at /data/user/0/mjt.openmultipleassetdataabses/databases/mydb002 so attempting to copy from assets.
2019-04-29 15:41:56.354 D/OADB-HLPR: copyDatabaseFromAssets initiated
2019-04-29 15:41:56.354 D/OADB-HLPR: copyDatabaseFromAssets attempting to find asset mydb002
2019-04-29 15:41:56.354 D/OADB-HLPR: copyDatabaseFromAssets asset mydb002 located successfully with a size of 77824
2019-04-29 15:41:56.354 D/OADB-HLPR: copyDatabaseFromAssets copying asset database mydb002 into buffer of size 77824
2019-04-29 15:41:56.355 D/OADB-HLPR: copyDatabaseFromAssets closing asset database mydb002
2019-04-29 15:41:56.355 D/OADB-HLPR: copyDatabaseFromAssets attempting to open new database file /data/user/0/mjt.openmultipleassetdataabses/databases/mydb002
2019-04-29 15:41:56.355 D/OADB-HLPR: copyDatabaseFromAssets writing new database file /data/user/0/mjt.openmultipleassetdataabses/databases/mydb002
2019-04-29 15:41:56.355 D/OADB-HLPR: copyDatabaseFromAssets flushing new database file /data/user/0/mjt.openmultipleassetdataabses/databases/mydb002
2019-04-29 15:41:56.355 D/OADB-HLPR: copyDatabaseFromAssets closing new database file /data/user/0/mjt.openmultipleassetdataabses/databases/mydb002
2019-04-29 15:41:56.355 D/OADB-HLPR: copyDatabaseFromAssets completed.
2019-04-29 15:41:56.356 D/OADB-HLPR: Successfully Copied Database from Assets.
2019-04-29 15:41:56.375 D/OADB-HLPR: OpenAssetDBHelper constructed.
2019-04-29 15:41:56.375 D/OADB-HLPR: OpenAssetDBHelper being constructed.
2019-04-29 15:41:56.375 D/OADB-HLPR: ifDatabaseExists initiated.
2019-04-29 15:41:56.375 D/OADB-HLPR: Database mydb003 not found at /data/user/0/mjt.openmultipleassetdataabses/databases/mydb003 so attempting to copy from assets.
2019-04-29 15:41:56.375 D/OADB-HLPR: copyDatabaseFromAssets initiated
2019-04-29 15:41:56.375 D/OADB-HLPR: copyDatabaseFromAssets attempting to find asset databases/mydb003
2019-04-29 15:41:56.376 D/OADB-HLPR: copyDatabaseFromAssets asset databases/mydb003 located successfully with a size of 77824
2019-04-29 15:41:56.376 D/OADB-HLPR: copyDatabaseFromAssets copying asset database mydb003 into buffer of size 77824
2019-04-29 15:41:56.376 D/OADB-HLPR: copyDatabaseFromAssets closing asset database mydb003
2019-04-29 15:41:56.376 D/OADB-HLPR: copyDatabaseFromAssets attempting to open new database file /data/user/0/mjt.openmultipleassetdataabses/databases/mydb003
2019-04-29 15:41:56.377 D/OADB-HLPR: copyDatabaseFromAssets writing new database file /data/user/0/mjt.openmultipleassetdataabses/databases/mydb003
2019-04-29 15:41:56.377 D/OADB-HLPR: copyDatabaseFromAssets flushing new database file /data/user/0/mjt.openmultipleassetdataabses/databases/mydb003
2019-04-29 15:41:56.377 D/OADB-HLPR: copyDatabaseFromAssets closing new database file /data/user/0/mjt.openmultipleassetdataabses/databases/mydb003
2019-04-29 15:41:56.377 D/OADB-HLPR: copyDatabaseFromAssets completed.
2019-04-29 15:41:56.377 D/OADB-HLPR: Successfully Copied Database from Assets.
2019-04-29 15:41:56.394 D/OADB-HLPR: OpenAssetDBHelper constructed.
Subsequent Run
2019-04-29 16:05:29.905 D/OADB-HLPR: OpenAssetDBHelper being constructed.
2019-04-29 16:05:29.905 D/OADB-HLPR: ifDatabaseExists initiated.
2019-04-29 16:05:29.910 D/OADB-HLPR: OpenAssetDBHelper constructed.
2019-04-29 16:05:29.910 D/OADB-HLPR: OpenAssetDBHelper being constructed.
2019-04-29 16:05:29.910 D/OADB-HLPR: ifDatabaseExists initiated.
2019-04-29 16:05:29.914 D/OADB-HLPR: OpenAssetDBHelper constructed.
2019-04-29 16:05:29.914 D/OADB-HLPR: OpenAssetDBHelper being constructed.
2019-04-29 16:05:29.915 D/OADB-HLPR: ifDatabaseExists initiated.
2019-04-29 16:05:29.918 D/OADB-HLPR: OpenAssetDBHelper constructed.
