Home > Tutorials > SQLite on Android – Part I

SQLite on Android – Part I

This is gonna be a very very long tutorials about SQLite on Android; I will guide you how to use and apply to make one simple application, like this:



The application is simple, create a database, insert some records, during load screen, all records will be queried and displayed to a list view like the image above.

A – Requirements

– You probably need to know something before to get straight forward into this tutorial:

1. Some Android basic operation: like handling list view, custom view display, layout & user-interface presentation…which are all mentioned in my previous posts. Make sure you have known or read about it, you MUST understand the mechanism how it works. If not, try to re-read, re-code and re-think until you understand and make it your own.

2. Know how to work around with SQLite, just a bit basic like: creating database, delete database, creating tables, deleting tables, select/insert/update/delete … Use SQLite version 3, because it is implemented on Android.

That’s all you need before getting started!

B – Data Modeling: On the Idea

Since the app we’re going to create is very simple, the modeling is absolutely simple.

Data Modeling - On the Idea

Data Modeling - On the Idea

We shall need to create a SQLite database named ‘user_db‘ which holds one single table ‘tbl_info‘ to contain user information.

C – Revised on SQLite Query Expressions

Ok! A bit practice on SQLite query expression that we will use:

1. to create table ‘tbl_info

CREATE TABLE tbl_info (
    name text NOT NULL,
    age integer NOT NULL,
    city text NOT NULL

2. to delete table ‘tbl_info


3. to insert a record into table ‘tbl_info

INSERT INTO tbl_info(name, age, city) VALUES ("Pete Houston", 29, "Seoul, South Korea");

4. to update a record into table ‘tbl_info
Eg: to update age from 29 to 32 for user having ‘_id = 1’:

UPDATE tbl_info SET age=32 WHERE _id=1;

5. to delete a record from table ‘tbl_info

DELETE FROM tbl_info WHERE _id=1;

6. to delete all records

DELETE * FROM tbl_info;

Well, that might be enough for now!
Have you understand a bit about SQLite upto now?

D – The Couple Handler

Generally, in a using-SQLite Android application, you need to design two classes in such a purposes: one class for handling database physically (like creating database files..), and the other is for logical handling (like execute SQL query expressions…).

1. The one for handling database physically is always inherited from class ‘SQLiteOpenHelper‘ (android.database.sqlite)

A helper class to manage database creation and version management.

You create a subclass implementing onCreate(SQLiteDatabase), onUpgrade(SQLiteDatabase, int, int) and optionally onOpen(SQLiteDatabase), and this class takes care of opening the database if it exists, creating it if it does not, and upgrading it as necessary. Transactions are used to make sure the database is always in a sensible state.

This class makes it easy for ContentProvider implementations to defer opening and upgrading the database until first use, to avoid blocking application startup with long-running database upgrades.

That’s what is said in Android documentation.

Therefore, when you inherit your class from SQLiteOpenHelper, you need to override the two provided methods.

	public void onCreate(SQLiteDatabase db) {

	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

– ‘onCreate‘ will be called when database is created.
– ‘onUpdate’ will be called when database version is changed, in which the old database file is deleted and the new one is created instead.

(That’s the reason why I called this ‘handling database physcally‘ if you ever wonder!)

– In common, this class name has suffix ‘Helper‘ to determine inheritance from SQLiteOpenHelper.

2. The other class provides the mechanism to handling database logically: like insert records, query records, delete records…Commonly, the class name has suffix ‘Adapter‘ to determine its job, just similar ListAdapter, BaseAdapter…or whatever adapter which provide such mechanisms for handling data logically.


It’s quite long for a tutorial, so I break up here.

Wait for my next part



Pete Houston


Categories: Tutorials Tags: , , ,
  1. August 7, 2014 at 9:53 pm

    Fantastic goods from you, man. I have understand your stuff prior to and you’re just too magnificent.
    I really like what you have got right here, certainly like what you’re saying and the way in which you assert it.

    You are making it entertaining and you continue to care for
    to stay it smart. I can’t wait to read far more from you.
    That is really a terrific site.

  2. June 26, 2012 at 1:50 am

    please tell me how to implements this tutorial with search filter like http://android-helper.blogspot.com/2011/07/android-search-in-custom-listview.html thank you.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: