I'm a noob in programming android apps. When using SQLite Database I got following problem:
Logcat tells me: "no such table: table1", when I run both methods. Where is my mistake?
I use an existing database, placed in assets/databases folder.
MainActivity
public void onClickSearch(View view) {
dataBaseHelper = new DataBaseHelper(MainActivity.this);
try {
String post = dataBaseHelper.getPost("table1","user");
Toast.makeText(this,String.format("%s",post),Toast.LENGTH_SHORT).show();
}catch (Exception e){
Toast.makeText(this,"Error",Toast.LENGTH_SHORT).show();
}
}
public void onClickRows(View view) {
dataBaseHelper = new DataBaseHelper(MainActivity.this);
try {
long count = dataBaseHelper.numberOfRows("table1");
String message = String.format("%s",count);
Toast.makeText(MainActivity.this,message,Toast.LENGTH_LONG).show();
}catch (Exception e){
Toast.makeText(this,"Error",Toast.LENGTH_SHORT).show();
}
}
DataBaseHelper
public class DataBaseHelper extends SQLiteOpenHelper {
public DataBaseHelper(@Nullable Context context) {
super(context, "database.db", null, 1);
}
public String getPost(String table, String nameIn){
String queryString = "SELECT post FROM "+ table +" WHERE name = "+nameIn;
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery(queryString,null);
StringBuffer buffer = new StringBuffer();
while(cursor.moveToNext()){
String post = cursor.getString(0);
buffer.append(""+post);
}
cursor.close();
db.close();
return buffer.toString();
}
public long numberOfRows(String table){
String queryString = "SELECT * FROM " + table;
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery(queryString,null);
long count = cursor.getCount();
cursor.close();
db.close();
return count;
}
}
When I export the .db file from Device File Explorer, the file is empty (just an android_manifest table).
Where is my file from the assets folder?
Please help.
Where is my file from the assets folder?
By the look of it, it is sitting untouched in the package. The database needs to be copied and unpacked from the package to a location from which it can be used.
The empty database with just the android_manifest table is indicative of a database being created, as getReadableDatabase (or getWriteableDatabase) will attempt to do.
Fixing the issue
In short what is typically done is that when attempting to access the database a check is made to see if the database exists, if it does not then the database is copied and unpacked from the package.
The following is a modified version of your DataBaseHelper class that incorporates the above. I suggest that you read the comments (it is assumed that the onCreate and onUpgrade methods existed and were omitted from the code you posted).
NOTE Once you have changed the code you need to delete the database that has been created. Uninstalling the App before rerunning will delete the existing database.
NOTE you say:-
I use an existing database, placed in assets/databases folder.
The code below works when the existing database is in the assets folder rather than the assets/databases folder. You should move or copy the database to the assets folder.
public class DataBaseHelper extends SQLiteOpenHelper {
//######## ADDED (more convenient to code in one place)
static String DATABASE_NAME = "database.db";
public DataBaseHelper(@Nullable Context context) {
super(context, DATABASE_NAME, null, 1);
String databasePath = context.getDatabasePath(DATABASE_NAME).getPath();
if (!doesDatabaseExist(databasePath)) {
copyDatabaseFromAssets(context,databasePath,DATABASE_NAME);
}
}
@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
}
@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
}
//######## ADDED
/*
Check to see if the database exists.
If it does then return, otherwise check to see
if the directories in the path exist, if they do
return, otherwise make the missing directories.
*/
private boolean doesDatabaseExist(String databasepath) {
if (new File(databasepath).exists()) return true;
if (new File(databasepath).getParentFile().exists()) return false;
new File(databasepath).getParentFile().mkdirs();
return false;
}
//######## ADDED
/*
Copy the database from the assets folder
*/
private void copyDatabaseFromAssets(Context context, String databasepath, String assetfilename) {
int bSize = 4096, bytes = 0;
byte[] buffer = new byte[bSize];
try {
InputStream asset = context.getAssets().open(assetfilename);
FileOutputStream database = new FileOutputStream(new File(databasepath));
while((bytes = asset.read(buffer)) > 0) {
database.write(buffer,0,bytes);
}
database.flush();
database.close();
asset.close();
} catch (IOException e) {
e.printStackTrace();
throw new RuntimeException("Error copy Asset File " + assetfilename + " to " + databasepath);
}
}
public String getPost(String table, String nameIn){
String queryString = "SELECT post FROM "+ table +" WHERE name = "+nameIn;
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery(queryString,null);
StringBuffer buffer = new StringBuffer();
while(cursor.moveToNext()){
String post = cursor.getString(0);
buffer.append(""+post);
}
cursor.close();
db.close();
return buffer.toString();
}
public long numberOfRows(String table){
String queryString = "SELECT * FROM " + table;
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery(queryString,null);
long count = cursor.getCount();
cursor.close();
db.close();
return count;
}
}