I hope to use a pre-existing SQLite database with Android studio. One method I need involves counting the number of rows in a table.
My method:
public int numberOfRows(){
int numRows = 0;
String query = "SELECT COUNT(*) FROM " + TASK_TABLE_NAME;
Cursor res = getReadableDatabase().rawQuery(query, null);
if (res.getCount() > 0){
res.moveToFirst();
numRows = res.getInt(0);
}
res.close();
return numRows;
}
When I try to debug the above code, res.getCount() returns 1, but res.getInt(0) returns 0, even though I do get a COUNT(*) greater than zero when I run the query in SQLiteStudio.
So far I have manually added android_metadata in my database and changed the index for the table to _id, after reading this blog.
I have already tried using DatabaseUtil.queryNumEntries(), which also returns zero.
When I try this code:
String query = "SELECT * FROM " + TASK_TABLE_NAME;
Cursor res2 = getReadableDatabase().rawQuery(query, null);
Calling res2.getColumnCount() would return the column count correctly, which means the database and table exist. It leads me to believe that somehow not all the rows in the table are being read properly.
Looking at the blog it misses perhaps the most important aspect that of actually adding data to the pre-existing database.
that is when it says :
After renaming the id field of all your data tables to “_id” and adding the “android_metadata” table, your database it’s ready to be used in your Android application.
It has assumed that you have used a database that already has data. If you just follow the steps of the guide and create the tables as per the screenshot. The you will have no data and effectively there is no advantage to using the data-less pre-existing database to creating the tables in the onCreate method of the class that extends SQliteOpenHelper (aka the Database Helper) DataBaseHelper.java.
In fact there could well be a disadvantage as the locale (the android_metadata table) is set to en_us and will thus not use the devices locale.
So, assuming that you've only followed the guide. Then :-
Re-visit the database using the SQLite Management tool (e.g. DB Browser for SQLite, Navicat .......) and add the data.
Save the data.
Close and then re-open the SQLite Management tool (it's very easy when using DB Browser for SQLite to inadvertently not save data from my limited experience).
When you are confident that the tables contain data close the SQLite Management tool and then copy the file into the assets folder.
Either clear/delete the App's data or uninstall the App and then rerun the App.
The blog's code also has some issues :-
It is suggested that you consider the following code as a replacement :-
public class DataBaseHelper extends SQLiteOpenHelper {
//The Androids default system path of your application database.
//private static String DB_PATH = "/data/data/YOUR_PACKAGE/databases/"; //<<<<<<<<<< WARNING best to not hard code the path
private static String DB_NAME = "myDBName";
private SQLiteDatabase myDataBase;
private final Context myContext;
private File dbpath;
/**
* 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;
//<<<<<<<<<< Get the DB path without hard coding it >>>>>>>>>>
// better future proofing
// less chance for coding errors
dbpath = context.getDatabasePath(DB_NAME); //<<<<<<<<<< ADDED get the path without hard-coding it (more future-proof than hard coding)
if (!checkDataBase()) {
try {
copyDataBase();
} catch (IOException e) {
e.printStackTrace();
throw new Error("Error copying database");
}
}
myDataBase = this.getWritableDatabase(); //<<<<<<<<<< ADDED this will force the open of db when constructing instantiating the helper
}
/**
* Creates a empty database on the system and rewrites it with your own database.
* */
//<<<<<<<<<< REDUNDANT CODE COMMENTED OUT
/*
public void createDataBase() throws IOException {
boolean dbExist = checkDataBase();
if(dbExist){
//do nothing - database already exist
}else{
//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();
try {
copyDataBase();
} catch (IOException e) {
}
}
}
*/
/**
* 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(){
if (dbpath.exists()) return true; // If the database file exists the db exists
// potential issue with the above is that a non sqlite file would result in an corrupt db exception
// checking the first 16 bytes could be used BUT who would copy non sqlite db into asset folder????
//<<<<<<<<<< IMPORTANT >>>>>>>>>>
// Instead of creating a new database and then overwriting it using getReadableDatabase or getWritableDatabase
// which is used to get around the problem of the databases directory not existing the the ENOENT IOError
// the directory is checked to see if it exists and if not to create it
// for Android Pie + due to the default being WAL the creating of the -wal and -shm files then the
// over-writing of the data base results in SQLite determing that the -wal file and -shm file are
// not the ones for the database (copied), thus the SQLiteOpen deletes the copied database and
// creates a brand new empty database
// hence the use of the following :-
if (!new File(dbpath.getParent()).exists()) {
new File(dbpath.getParent()).mkdirs();
}
return false;
/* <<<<<<<<<< REDUNDANT CODE COMMENTED OUT >>>>>>>>>>
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.
}
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{
//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; //<<<<<<<<<< REDUNDANT CODE COMMENTED OUT
//Open the empty db as the output stream
OutputStream myOutput = new FileOutputStream(dbpath); //<<<<<<<<< ADDED
//transfer bytes from the inputfile to the outputfile
byte[] buffer = new byte[1024];
int length;
while ((length = myInput.read(buffer))>0){
myOutput.write(buffer, 0, length);
}
//Close the streams
myOutput.flush();
myOutput.close();
myInput.close();
}
public void openDataBase() throws SQLException {
//Open the database
//String myPath = DB_PATH + DB_NAME; //<<<<<<<<<< REDUNDANT CODE COMMENTED OUT
myDataBase = SQLiteDatabase.openDatabase(dbpath.getPath(), null, SQLiteDatabase.OPEN_READONLY);
}
@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) {
}
// 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.
}
This example is a comparison between two pre-existing databases both with the same structure one asset file myDBNameEmpty with no data(rows) the other, asset file myDBNameWithData, that has rows in the tables. Neither has the android_metadata table, the use of _id as the column name is also not used.
Using slightly amended version of the suggested DatabaseHelper (to allow database name to be passed) this example shows :-
The changes, for this demo, to the suggested Database Helper (other than the class declaration and filename) are all within this block of code (see comments):-
//public DataabseHelper(Context context) //<<<<<<<< changed for demo
public DataBaseHelperSpcl(Context context, String databasename) { //<<<<<<<<<<FOR DEMO
//super(context,DB_NAME,null,1); //<<<<<<<<< change for dem //<<<<<<<<<<< changed for demo
super(context, databasename, null, 1); //<<<<<<<<<< FOR DEMO
//<<<<<<<<<< Get the DB path without hard coding it >>>>>>>>>>
// better future proofing
// less chance for coding errors
DB_NAME = databasename; //<<<<<<<<<<FOR DEMO ONLY
this.myContext = context;
The activity itself used for testing this example was :-
public class MainActivity extends AppCompatActivity {
DataBaseHelperSpcl[] myDBHlprs = new DataBaseHelperSpcl[2];
ArrayList<String> tablenames = new ArrayList<>();
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
myDBHlprs[0] = new DataBaseHelperSpcl(this,"myDBNameEmpty");
myDBHlprs[1] = new DataBaseHelperSpcl(this,"myDBNameWithData");
for (DataBaseHelperSpcl dbhlpr: myDBHlprs) {
SQLiteDatabase db = dbhlpr.getWritableDatabase();
Log.d("DATABASE","Processing Database " + db.getPath());
Cursor csr = db.query("sqlite_master",null,null,null,null,null,null);
tablenames.clear();
while (csr.moveToNext()) {
Log.d("DBENITITY",
"Current Database includes the Entity " +
csr.getString(csr.getColumnIndex("name")) +
" of type " + csr.getString(csr.getColumnIndex("type"))
);
if (csr.getString(csr.getColumnIndex("type")).equals("table")) {
tablenames.add(csr.getString(csr.getColumnIndex("name")));
}
}
for (String tbl: tablenames) {
int du_rowcount = (int) DatabaseUtils.queryNumEntries(db,tbl);
csr = db.query(tbl,new String[]{"count(*)"},null,null,null,null,null);
int qry_rowcount =0;
if (csr.moveToFirst()) {
qry_rowcount = csr.getInt(0);
}
Log.d(
"COUNTS_"+tbl,
"\n\tFromDBUtils = " + String.valueOf(du_rowcount) +
"\n\tFromQuery = " + String.valueOf(qry_rowcount)
);
csr = db.query(tbl,null,null,null,null,null,null);
StringBuilder sb = new StringBuilder("For Table ")
.append(tbl)
.append(" the # of columns is ")
.append(String.valueOf(csr.getColumnCount()))
.append(" they are :-")
;
for (String col: csr.getColumnNames()) {
sb.append("\n\t").append(col);
}
Log.d("COLUMNINFO",sb.toString());
}
// no need for _ID column 2 way around
DatabaseUtils.dumpCursor(
csr = db.query(
tablenames.get(0),
new String[]{"rowid AS " + BaseColumns._ID,"not_id AS " + BaseColumns._ID},
null,null,null,null,null)
);
}
}
}
Running the above (from Android 10 emulated device) results in :-
for myDBNameEmpty :-
2019-05-05 15:09:24.696 D/DATABASE: Processing Database /data/user/0/soa.usingyourownsqlitedatabaseblog/databases/myDBNameEmpty
2019-05-05 15:09:24.697 D/DBENITITY: Current Database includes the Entity Categories of type table
2019-05-05 15:09:24.697 D/DBENITITY: Current Database includes the Entity Content of type table
2019-05-05 15:09:24.697 D/DBENITITY: Current Database includes the Entity android_metadata of type table
2019-05-05 15:09:24.698 D/COUNTS_Categories: FromDBUtils = 0
FromQuery = 0
2019-05-05 15:09:24.699 D/COLUMNINFO: For Table Categories the # of columns is 3 they are :-
not_id
CategoryLabel
Colour
2019-05-05 15:09:24.700 D/COUNTS_Content: FromDBUtils = 0
FromQuery = 0
2019-05-05 15:09:24.700 D/COLUMNINFO: For Table Content the # of columns is 5 they are :-
again_not_id
Text
Source
Category
VerseOrder
2019-05-05 15:09:24.701 D/COUNTS_android_metadata: FromDBUtils = 1
FromQuery = 1
2019-05-05 15:09:24.701 D/COLUMNINFO: For Table android_metadata the # of columns is 1 they are :-
locale
2019-05-05 15:09:24.702 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@2b0e2ba
2019-05-05 15:09:24.703 I/System.out: <<<<<
for myDBNameEmpty :-
2019-05-05 15:09:24.703 D/DATABASE: Processing Database /data/user/0/soa.usingyourownsqlitedatabaseblog/databases/myDBNameWithData
2019-05-05 15:09:24.706 D/DBENITITY: Current Database includes the Entity Categories of type table
2019-05-05 15:09:24.706 D/DBENITITY: Current Database includes the Entity Content of type table
2019-05-05 15:09:24.706 D/DBENITITY: Current Database includes the Entity android_metadata of type table
2019-05-05 15:09:24.707 D/COUNTS_Categories: FromDBUtils = 5
FromQuery = 5
2019-05-05 15:09:24.708 D/COLUMNINFO: For Table Categories the # of columns is 3 they are :-
not_id
CategoryLabel
Colour
2019-05-05 15:09:24.709 D/COUNTS_Content: FromDBUtils = 6
FromQuery = 6
2019-05-05 15:09:24.709 D/COLUMNINFO: For Table Content the # of columns is 5 they are :-
again_not_id
Text
Source
Category
VerseOrder
2019-05-05 15:09:24.744 D/COUNTS_android_metadata: FromDBUtils = 1
FromQuery = 1
2019-05-05 15:09:24.745 D/COLUMNINFO: For Table android_metadata the # of columns is 1 they are :-
locale
dynamically created _id columns
2019-05-05 15:09:24.745 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@41656b
2019-05-05 15:09:24.746 I/System.out: 0 {
2019-05-05 15:09:24.746 I/System.out: _id=1
2019-05-05 15:09:24.746 I/System.out: _id=1
2019-05-05 15:09:24.746 I/System.out: }
2019-05-05 15:09:24.746 I/System.out: 1 {
2019-05-05 15:09:24.746 I/System.out: _id=2
2019-05-05 15:09:24.746 I/System.out: _id=2
2019-05-05 15:09:24.746 I/System.out: }
2019-05-05 15:09:24.746 I/System.out: 2 {
2019-05-05 15:09:24.746 I/System.out: _id=3
2019-05-05 15:09:24.746 I/System.out: _id=3
2019-05-05 15:09:24.747 I/System.out: }
2019-05-05 15:09:24.747 I/System.out: 3 {
2019-05-05 15:09:24.747 I/System.out: _id=4
2019-05-05 15:09:24.747 I/System.out: _id=4
2019-05-05 15:09:24.747 I/System.out: }
2019-05-05 15:09:24.747 I/System.out: 4 {
2019-05-05 15:09:24.747 I/System.out: _id=5
2019-05-05 15:09:24.747 I/System.out: _id=5
2019-05-05 15:09:24.747 I/System.out: }
2019-05-05 15:09:24.747 I/System.out: <<<<<