SQLite

SQLite subqueries

SDK Version: 
M3

sqlite
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.

Database transactions

SDK Version: 
M3

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:

beginTransaction();
setTransactionSuccessful();
endTransaction();

Debugging database

SDK Version: 
M3

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
  2.  
  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: 
M3

    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.

NewsDroid RSS Reader

SDK Version: 
M3

In Hello Android's first tutorial we will walk through creating a simple RSS reader. This tutorial will show you how to parse XML files and use the SQLiteDatabase for storage.

Syndicate content