SQLite subqueries

SDK Version: 

Recently we worked with big databases under android, and during that I met query optimization problems when using subqueries.

From my previous experiences with MySQL I thought, the database engine optimization is always the best, and there is no way, that multiple queries from program code is better, but I ended running multiple selects in a loop in this case for the best result.

Handling old data in new app versions

SDK Version: 

When you release a newer version of your application, on update the android system identifies from the package name and version number, that the user has an older version installed. During updating not all data of the previous version is overwritten, databases, preferences, previously downloaded/created files on sdcard or internal storage remain unchanged. If there are any incompatibility with the old version of these, is must be taken care of.

First of all, files on the external storage can be lost or corrupted due to many other reasons, so version incompatibility is just one more reason to check those files before using them.
However the internal storage is theoretically not modified by external sources, if there are version incompatibilities, you have to check the state of this files too.

Database transactions

SDK Version: 

In android it is highly important to use transactions when working with databases.

First, in android database operations - especially writing - are very slow. Batching them into transactions will make them much faster.

Second, the database remains consistent under any circumstances. The database system makes sure to all the operations in a transaction take effect, or on error, rollback all of them.

If you are used to other platforms like PHP+MySQL where the code usually runs on a powerful server, witch is not likely to stop execution "unexpectedly", you can be surprised how much it affects the performance in android.
The android system can kill apps/threads/activities and so interrupt database usage, the battery can discharge or can be removed etc.

The implementation is very simple, using 3 methods in the SQLiteDatabase class:


How to have a default database

SDK Version: 

If you want to include a database with initial data in your apk, you have to insert the database file into the projects assets folder, then programmatically check if the database, and if it does not exists copy the one from the assets.

You will need a function to check if the database exists, fox example:

  1. private static final String DATABASE_NAME = "testdatabase";
  2. private static final String DB_PATH = "/data/data/"+mContext.getPackageName()+"/databases/";
  4. private boolean isDataBaseExist() {
  5.         File dbFile = new File(DB_PATH+DATABASE_NAME);
  6.         return dbFile.exists();
  7. }

If the check shows that the the database does not exist, a function like this will copy it:

  1. private void copyDataBase() throws IOException {
  2.         // Open your local db as the input stream
  3.         InputStream myInput = mContext.getAssets().open("databases/"+DATABASE_NAME);
  4.         // Path to the just created empty db
  5.         String outFileName = DB_PATH + DATABASE_NAME;

Connecting to MySQL database

SDK Version: 
The most spread method to connect to a remote MySQL database from an android device, is to put some kind of service into the middle. Since MySQL is usually used together with PHP, the easiest and most obvious way to write a PHP script to manage the database and run this script using HTTP protocol from the android system. mysql logo

We can code the data in JSON format, between Android and PHP with the easy to use built in JSON functions in both languages.

I present some sample code, which selects data from a database depending on a given condition and creates a log message on the android side with the received data.

Lets suppose that we have a MySQL database named PeopleData, and a table int created, with the following SQL:

  1. CREATE TABLE `people` (

Store images/files in database

SDK Version: 

Hoever the practice is to store them normally and save the access route (Uri), sometimes it can be handy to store files/images completely in database.
In sqlite database there are only a few data types, so its easy to choose: files can be stored in a text as a ByteArray.

Lets see a sample code, where we download an image from the Internet then store it in the local database:

  1. //where we want to download it from
  2. URL url = new URL(IMAGE_URL);  //
  3. //open the connection
  4. URLConnection ucon = url.openConnection();
  5. //buffer the download
  6. InputStream is = ucon.getInputStream();
  7. ByteArrayBuffer baf = new ByteArrayBuffer(128);
  8. //get the bytes one by one
  9. int current = 0;
  10. while ((current = != -1) {
  11.         baf.append((byte) current);
  12. }
  14. //store the data as a ByteArray
  15. //db is a SQLiteDatabase object
  16. ContentValues dataToInsert = new ContentValues();                          

Debugging database

SDK Version: 

When I first tried to manage an sqlite database on an android device I was not sure about where I fail in it. Can I even insert the records into the database, or I fail only to read the data from it? So I started to search for possibilities to debug the database lifecycle.

The system stores databases in the /data/data/package_name/databases folder by default.

In a command line using the adb (Android Debug Bridge - found in the android sdk tools library) you can access the databases on a running emulator like below:

  1. adb -s emulator-5554 shell
  3. sqlite3 /data/data/package_name/databases/database_name

After this you can type normal SQL commands to test the content. For example:

  1. SELECT * FROM table_name;

This will list the table content (in an ugly format), or say that it does not exists.

Data Storage tutorial, basic samples are included

SDK Version: 

    Data Storage Methods

  • Preferences
  • Preferences is a lightweight mechanism to store and retrieve key-value pairs of primitive data types.
  • Files
  • You can store your data in files on your mobile phone, or in a removable storage medium.
  • Databases
  • Android Api supports SQLite databases. All databases, SQLite and others, are stored on the device in /data/data/package_name/databases.
  • Network
  • You can also use the Internet to store and receive data, whether it's an SQLite database, or just a simple textfile.

Syndicate content