August 8, 2011 at 16:53Correct work with the database in Android
Greetings to all droiders in these difficult times for us.
Honestly, this hype about patents, wars, etc., hesitated, but this article will not discuss this.
I was not going to write an article on this topic, because everywhere everything is full about working with the database in Android and everything seems to be simple, but it’s very tired of receiving reports about errors, errors specific and related to the database.
Therefore, I will consider a couple of points that I have encountered in practice, to warn people who have only to deal with this, and then I wait for your comments on the topic of solving these problems, after which I will make changes to the post and we will make an excellent tutorial that will be an example of work with SQLite on Android, not only for beginners, but also for those who are already familiar with the basics and have written simple applications.
Ways to work with the database
There are three ways to work with data in the database that immediately rush to your mind:
1) You create an empty database structure. The user works with the application (creates notes, deletes them) and the database is filled. An example is NotePad in developer.android.com demos or on your droid device.
2) You already have a ready-made database filled with data that needs to be distributed with the application, or parse the data from the file into assets.
3) Receive data from the network, as necessary.
If there is any other one or two ways, then I will gladly supplement this list with your help.
All the main tutorials are designed just for the first case. You write a request to create a database structure and execute this request in the onCreate () method of the SQLiteOpenHelper class, for example like this:
Like that. A more complete version of the class and other components can be found at the link at the bottom of the article.
Additionally, you can override the onOpen (), getReadableDatabase () / getWritableDatabase () methods, but usually the above methods and data retrieval methods are enough.
Next, we create an instance of this class in our application when it is launched and execute requests, that is, the problem part has been passed. Why is it problematic? Because when a user downloads applications from the market, he does not think about your database and anything can happen. Let's say the network is gone or another process has started, or you wrote error-sensitive code.
By the way, there is one more point worth paying attention to. An instance variable of our class can be created and stored in the Application object and accessed as necessary, but you must remember to call the close () method, since constant connection to the database is a heavy resource. In addition, there may be collisions when working with the database from multiple threads.
But there is another way, for example, to create our object as necessary to access the database. I think this is a matter of preference, but which also needs to be discussed.
And now the most important thing. What if we needed to use an existing database with data in the application?
A little googling, you will immediately stumble upon such a “wonderful article” - www.reigndesign.com/blog/using-your-own-sqlite-database-in-android-applicationsin which, as it seems, there is the right panacea. But it was not there. It also has several errors.
Here they are:
1) In the createDataBase () method, the line:
SQLiteDatabase dbRead = getReadableDatabase ();
and then the code ... contains crash applications on the Desire NTS, because we get a database for reading (it is created), but it does not close.
Add dbRead.close () below the line and the fix is ready, but the point is debatable.
Here's what the dock says about the getReadableDatabase () method:
Create and / or open a database. This will be the same object returned by getWritableDatabase () unless some problem, such as a full disk, requires the database to be opened read-only. In that case, a read-only database object will be returned. If the problem is fixed, a future call to getWritableDatabase () may succeed, in which case the read-only database object will be closed and the read / write object will be returned in the future.
Like getWritableDatabase (), this method may take a long time to return, so you should not call it from the application main thread, including from ContentProvider.onCreate ().
So. This method should not be called in the main thread of the application. Otherwise, everything is clear.
2) Error: No such table android_metadata.The author of the post got out by creating this table in advance in the database. I don’t know how much this is the correct way, but this table is created in each sqlite-bd system and contains the current locale.
3) Error: Unable to open database file. There are many opinions, different opinions that you can read from the links below.
stackoverflow.com/questions/3563728/random-exception-android-database-sqlite-sqliteexception-unable-to-open-database
groups.google.com/group/android-developers/browse_thread/thread/a0959c4059359d6f
code.google.com/ p / android / issues / detail? id = 949
stackoverflow.com/questions/4937934/unable-to-open-database-file-on-device-htc-desire
androidblogger.blogspot.com/2011/02/instable-android- and-unable-to-open.html
It is possible that the problems are related to the fact that one thread blocks the database and the second cannot access it, there may be a problem in access rights to the application (it was noted that more often database problems appear on NTS phones on those models that cannot be ruined , although not only on them, for example on Acer tablets), but be that as it may, these problems exist.
I am inclined to the option that the problem is in threads, not in vain because we are not recommended to call methods to create a database in the main thread.
Perhaps the solution to this will be the following (option 2 is being considered). Using the first option to work with the database, fill it with data after creation, for example:
This approach still needs to be tested in practice, but since this post is aimed at developing the right collective decision on this topic, comments and samples on this topic are only welcome.
The moral of the story is this: if you find some good piece of code for your solution, then check it, do not be lazy before copying it into your project.
Conclusion
As a whole, this post shows (regarding method No. 2) how to do it, but also contains a couple of interesting thoughts.
The getReadableDatabase () method can be redefined for example like this:
An excellent tutorial on this topic here is www.vogella.de/articles/AndroidSQLite/article.html
By the way: following the practice of the platform itself, the primary key field should be called "_id".
Write your practitioners in a comment. We will make this post better for everyone, and maybe the world will become a little kinder.
UPD Just tested my approach. Everything works in the emulator, but be careful.
The data.txt file is in assets like this:
Zametka # 1
Zametka # 2
Zametka # 3
Zametka # 4
And the application class:
I note that this class is used only to demonstrate and verify what happens when the getReadableDatabase () / getWritableDatabase () methods are called and the database is created. In real projects, the code needs to be adapted.
In addition, the android_metadata plate appeared in the database (without my participation), so the above error has been resolved.
I hope someone comes in handy.
Curious additions No. 1 (from Kalobok habrayuzer )
So far I have completely abandoned SQLiteOpenHelper - it turned out that it is impossible to create a base on an SD card in it. Theoretically, what it returns should be used as a path to the database. In practice, SQLiteOpenHelper sometimes uses it, and sometimes bypasses it - it depends on whether we open the database for reading or writing, whether it already exists, etc. SQLiteOpenHelper.getWritableDatabase calls Context.openOrCreateDatabase, which in turn uses Context.validateFilePath to get the full path to the file. There we use the private method Context.getDatabasesDir, which cannot be redefined - we arrived. The base will be created in the standard directory.
But if we called SQLiteOpenHelper.getReadableDatabase, first it will try to call the same getWritableDatabase. But if this does not work out, it will bypass Context.openOrCreateDatabase - it will call Context.getDatabasePath itself (here we can correct it) and it will open the necessary database. This method would suit us if it had always been used. But alas. :(
In general, the idea with this helper was good, and the implementation - with the left foot from a hangover.
Honestly, this hype about patents, wars, etc., hesitated, but this article will not discuss this.
I was not going to write an article on this topic, because everywhere everything is full about working with the database in Android and everything seems to be simple, but it’s very tired of receiving reports about errors, errors specific and related to the database.
Therefore, I will consider a couple of points that I have encountered in practice, to warn people who have only to deal with this, and then I wait for your comments on the topic of solving these problems, after which I will make changes to the post and we will make an excellent tutorial that will be an example of work with SQLite on Android, not only for beginners, but also for those who are already familiar with the basics and have written simple applications.
Ways to work with the database
There are three ways to work with data in the database that immediately rush to your mind:
1) You create an empty database structure. The user works with the application (creates notes, deletes them) and the database is filled. An example is NotePad in developer.android.com demos or on your droid device.
2) You already have a ready-made database filled with data that needs to be distributed with the application, or parse the data from the file into assets.
3) Receive data from the network, as necessary.
If there is any other one or two ways, then I will gladly supplement this list with your help.
All the main tutorials are designed just for the first case. You write a request to create a database structure and execute this request in the onCreate () method of the SQLiteOpenHelper class, for example like this:
class MyDBHelper extends SQLiteOpenHelper {
final String CREATE_TABLE = "CREATE TABLE myTable(...)";
final String DB_NAME = "mySuperDB.db";
Context mContext;
public MyDBHelper(Context context, int dbVer){
super(context, DB_NAME, null, dbVer);
mContext = context;
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(CREATE_TABLE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
//проверяете какая версия сейчас и делаете апдейт
db.execSQL("DROP TABLE IF EXISTS tableName");
onCreate(db);
}
...какой-то код
}
Like that. A more complete version of the class and other components can be found at the link at the bottom of the article.
Additionally, you can override the onOpen (), getReadableDatabase () / getWritableDatabase () methods, but usually the above methods and data retrieval methods are enough.
Next, we create an instance of this class in our application when it is launched and execute requests, that is, the problem part has been passed. Why is it problematic? Because when a user downloads applications from the market, he does not think about your database and anything can happen. Let's say the network is gone or another process has started, or you wrote error-sensitive code.
By the way, there is one more point worth paying attention to. An instance variable of our class can be created and stored in the Application object and accessed as necessary, but you must remember to call the close () method, since constant connection to the database is a heavy resource. In addition, there may be collisions when working with the database from multiple threads.
But there is another way, for example, to create our object as necessary to access the database. I think this is a matter of preference, but which also needs to be discussed.
And now the most important thing. What if we needed to use an existing database with data in the application?
A little googling, you will immediately stumble upon such a “wonderful article” - www.reigndesign.com/blog/using-your-own-sqlite-database-in-android-applicationsin which, as it seems, there is the right panacea. But it was not there. It also has several errors.
Here they are:
1) In the createDataBase () method, the line:
SQLiteDatabase dbRead = getReadableDatabase ();
and then the code ... contains crash applications on the Desire NTS, because we get a database for reading (it is created), but it does not close.
Add dbRead.close () below the line and the fix is ready, but the point is debatable.
Here's what the dock says about the getReadableDatabase () method:
Create and / or open a database. This will be the same object returned by getWritableDatabase () unless some problem, such as a full disk, requires the database to be opened read-only. In that case, a read-only database object will be returned. If the problem is fixed, a future call to getWritableDatabase () may succeed, in which case the read-only database object will be closed and the read / write object will be returned in the future.
Like getWritableDatabase (), this method may take a long time to return, so you should not call it from the application main thread, including from ContentProvider.onCreate ().
So. This method should not be called in the main thread of the application. Otherwise, everything is clear.
2) Error: No such table android_metadata.The author of the post got out by creating this table in advance in the database. I don’t know how much this is the correct way, but this table is created in each sqlite-bd system and contains the current locale.
3) Error: Unable to open database file. There are many opinions, different opinions that you can read from the links below.
stackoverflow.com/questions/3563728/random-exception-android-database-sqlite-sqliteexception-unable-to-open-database
groups.google.com/group/android-developers/browse_thread/thread/a0959c4059359d6f
code.google.com/ p / android / issues / detail? id = 949
stackoverflow.com/questions/4937934/unable-to-open-database-file-on-device-htc-desire
androidblogger.blogspot.com/2011/02/instable-android- and-unable-to-open.html
It is possible that the problems are related to the fact that one thread blocks the database and the second cannot access it, there may be a problem in access rights to the application (it was noted that more often database problems appear on NTS phones on those models that cannot be ruined , although not only on them, for example on Acer tablets), but be that as it may, these problems exist.
I am inclined to the option that the problem is in threads, not in vain because we are not recommended to call methods to create a database in the main thread.
Perhaps the solution to this will be the following (option 2 is being considered). Using the first option to work with the database, fill it with data after creation, for example:
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(CREATE_TABLE);
fillData(db);
}
private void fillData(SQLiteDatabase db) {
//разбираем файл data.xml лежащий например в assets
//и вставляем данные в базу
//либо читаем лежащие там же sql-скрипты и выполняем с помощью все того же db.execSQL() или аналогично
}
This approach still needs to be tested in practice, but since this post is aimed at developing the right collective decision on this topic, comments and samples on this topic are only welcome.
The moral of the story is this: if you find some good piece of code for your solution, then check it, do not be lazy before copying it into your project.
Conclusion
As a whole, this post shows (regarding method No. 2) how to do it, but also contains a couple of interesting thoughts.
The getReadableDatabase () method can be redefined for example like this:
@Override
public synchronized SQLiteDatabase getReadableDatabase() {
//Log.d(Constants.DEBUG_TAG, "getReadableDatabase() called");
SQLiteDatabase db;
try {
db = super.getReadableDatabase();
}
catch (SQLiteException e) {
Log.d(Constants.DEBUG_TAG, e.getMessage());
File dbFile = myContext.getDatabasePath(DB_NAME);
Log.d(Constants.DEBUG_TAG,"db path="+dbFile.getAbsolutePath());
//db = SQLiteDatabase.openDatabase(/*DB_PATH + DB_NAME*/ dbFile.getAbsolutePath(), null, SQLiteDatabase.NO_LOCALIZED_COLLATORS);
db = SQLiteDatabase.openOrCreateDatabase(dbFile.getAbsolutePath(), null);
}
return db;
}
An excellent tutorial on this topic here is www.vogella.de/articles/AndroidSQLite/article.html
By the way: following the practice of the platform itself, the primary key field should be called "_id".
Write your practitioners in a comment. We will make this post better for everyone, and maybe the world will become a little kinder.
UPD Just tested my approach. Everything works in the emulator, but be careful.
public class DBHelper extends SQLiteOpenHelper {
final static int DB_VER = 1;
final static String DB_NAME = "todo.db";
final String TABLE_NAME = "todo";
final String CREATE_TABLE = "CREATE TABLE "+TABLE_NAME+
"( _id INTEGER PRIMARY KEY , "+
" todo TEXT)";
final String DROP_TABLE = "DROP TABLE IF EXISTS "+TABLE_NAME;
final String DATA_FILE_NAME = "data.txt";
Context mContext;
public DBHelper(Context context) {
super(context, DB_NAME, null, DB_VER);
Log.d(Constants.DEBUG_TAG,"constructor called");
mContext = context;
}
@Override
public void onCreate(SQLiteDatabase db) {
Log.d(Constants.DEBUG_TAG,"onCreate() called");
db.execSQL(CREATE_TABLE);
fillData(db);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL(DROP_TABLE);
onCreate(db);
}
private ArrayList getData() {
InputStream stream = null;
ArrayList list = new ArrayList();
try {
stream = mContext.getAssets().open(DATA_FILE_NAME);
}
catch (IOException e) {
Log.d(Constants.DEBUG_TAG,e.getMessage());
}
DataInputStream dataStream = new DataInputStream(stream);
String data = "";
try {
while( (data=dataStream.readLine()) != null ) {
list.add(data);
}
}
catch (IOException e) {
e.printStackTrace();
}
return list;
}
private void fillData(SQLiteDatabase db){
ArrayList data = getData();
for(String dt:data) Log.d(Constants.DEBUG_TAG,"item="+dt);
if( db != null ){
ContentValues values;
for(String dat:data){
values = new ContentValues();
values.put("todo", dat);
db.insert(TABLE_NAME, null, values);
}
}
else {
Log.d(Constants.DEBUG_TAG,"db null");
}
}
}
The data.txt file is in assets like this:
Zametka # 1
Zametka # 2
Zametka # 3
Zametka # 4
And the application class:
public class TODOApplication extends Application {
private DBHelper mDbHelper;
@Override
public void onCreate(){
super.onCreate();
mDbHelper = new DBHelper(getApplicationContext());
mDbHelper.getWritableDatabase();
}
@Override
public void onLowMemory() {
super.onLowMemory();
mDbHelper.close();
}
@Override
public void onTerminate(){
super.onTerminate();
mDbHelper.close();
}
}
I note that this class is used only to demonstrate and verify what happens when the getReadableDatabase () / getWritableDatabase () methods are called and the database is created. In real projects, the code needs to be adapted.
In addition, the android_metadata plate appeared in the database (without my participation), so the above error has been resolved.
I hope someone comes in handy.
Curious additions No. 1 (from Kalobok habrayuzer )
So far I have completely abandoned SQLiteOpenHelper - it turned out that it is impossible to create a base on an SD card in it. Theoretically, what it returns should be used as a path to the database. In practice, SQLiteOpenHelper sometimes uses it, and sometimes bypasses it - it depends on whether we open the database for reading or writing, whether it already exists, etc. SQLiteOpenHelper.getWritableDatabase calls Context.openOrCreateDatabase, which in turn uses Context.validateFilePath to get the full path to the file. There we use the private method Context.getDatabasesDir, which cannot be redefined - we arrived. The base will be created in the standard directory.
But if we called SQLiteOpenHelper.getReadableDatabase, first it will try to call the same getWritableDatabase. But if this does not work out, it will bypass Context.openOrCreateDatabase - it will call Context.getDatabasePath itself (here we can correct it) and it will open the necessary database. This method would suit us if it had always been used. But alas. :(
In general, the idea with this helper was good, and the implementation - with the left foot from a hangover.