Android Sqlite


Download Android Sqlite sample

Android comes in with built in SQLite database, which is an opensource relational database that can be used to perform database operations on android devices such as storing, manipulating or retrieving persistent data from the database.

In this tutorial, we are going to discuss following topics:

  1. SQLiteOpenHelper class
  2. Insert, Update, Delete and Query operation
  3. Custom ListView design
  4. Populating ListView using CursorAdapter

Table structure


Table structure of given sample is given below:
Table name : customer
Field Type Key
_id Integer PK
firstName text
lastName text

I used _id as primary key because when you use CursorAdapter, by default, it looks _id as primary key.

The corresponding Customer class for above table structure is as follows:

Customer.java

I Written a healper class named ReturnMessage to deliver a message whether an operation was successful or failed.

ReturnMessage.java

SQLiteOpenHelper class


The android.database.sqlite.SQLiteOpenHelper class is used for database creation and version management. To perform any database operation, you have to write a class that extends SQLiteOpenHelper class and implement onCreate() and onUpgrade() methods of SQLiteOpenHelper class.

Write a class named SqliteHelper and extends android.database.sqlite.SQLiteOpenHelper class. Now we need to write a constructor that call super constructor and need to provide implementation of onCreate() and onUpgrade() methods.

SqliteHelper.java

Now let's discuss each method one by one:

SqliteHelper(Context context) : this is constructor of class that calls super class constructor from its body.

onCreate(SQLiteDatabase sqLiteDatabase) : This method is called only once in its life time. So, in this method we perform all table creation task

onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) : This method is called when database version upgrade. Suppose in an live app our database has 5 tables, now we want to add a new table and want to alter few columns of old table.

For that, first we need to upgrade database version, that will force system to call onUpgrade method. Now, within onUpgrade method we will write create query to add a new table and alter query to alter the columns of old table.

getCustomerDao : For clear visibility I prefered to write a separate class CustomerDao that is responsible for handling all customer related operation.

getObjectFromCursor : This is utility method which accept cursor and class as parameter. It is responsible for processing cursor and return corresponding object.

CustomerDao class


This class is responsible for handling all customer related operations like create customer table, insert, update, delete and query customers.

CustomerDao.java

Create table in Sqlite - Create customer table


createCustomerTable method of CustomerDao class is responsible for creating customer table. It is called from onCreate method of SqlHelper class only once in its life time. COL_ID, COL_FIRST_NAME, COL_LAST_NAME and TABLE_NAME are constant declared in CustomerDao class.

SQLiteDatabase insert - Add customer


addCustomer method of CustomerDao is responsible for adding a customer and return an object of ReturnMessage so that caller can check whether operation was successful or not.

ContentValues

ContentValues is a name value pair, that is used to insert or update values into database tables. ContentValues object need to be passed to insert() and update() method of SQLiteDataBase objects.

Here we are creating ContentValues object from Customer object and passing it to insert method of SQLiteDataBase objects.

SQLiteDatabase update - Update customer


updateCustomer method of CustomerDao is responsible for updating a customer. Source of updateCustomer is given below:

SQLiteDatabase delete - Delete customer


deleteCustomer method of CustomerDao is used to delete a customer row from the customer table. Source of deleteCustomer method is given below:

SQLiteDatabase query - Query a customer


getCustomers method of CustomerDao is responsible for querying the customers based on customer object passed as parameter and return Cursor object.

Cursor

Cursor is a class which represents a 2 dimensional table of any database. When you try to retrieve some data using SELECT statement, then the database will first create a Cursor object and return its reference to you.

The pointer of this returned reference is pointing to the 0th location which is otherwise called as before first location of the Cursor, so when you want to retrive data from the cursor, you have to first move to the first record so we have to use moveToFirst

Populating a ListView with a CursorAdapter


There are 3 easy step to populate a ListView with CursorAdapter:

  1. Define the row of ListView, how you want to display rows of list
  2. Write your Custom Adapter class by extending CursorAdapter
  3. Initialize the custom CursorAdapter object, and set that object as adapter of ListView.

Create a layout resource xml and define how row of listView will look like:

This is how customer row looks like:

scaleType center

CursorAdapter

Now we create a class named CustomerCursorAdapter that extends CursorAdapter and override bindView method of cursor adapter. Source of CustomerCursorAdapter class is given below:

Set cursor adapter object as adapter of listView

Now, last step is to populate a ListView from cursor is given below:

Android Sqlite example


We have already discussed all major points that was required to develop a Sqlite sample which perform insert, update, delete and query operation, and populate a Custom ListView using CursorAdapter.

So, lets develop a demo that demonstrate all the discussed points.

Android Sqlite sample

Step 1: Create a new project in Android Studio and name it Android Sqlite.

Step 2: Open res -> values -> strings.xml and add following string resource

strings.xml

Step 3: Open res -> layout -> activity_main.xml and add following code

activity_main.xml

Step 4: Open res -> layout -> customer_layout.xml and add following code

customer_layout.xml

Step 5: Create a class named Customer.java and replace its code with below given code

Customer.java

Step 6: Create a class named CustomerCursorAdapter.java and replace its code with below given code

CustomerCursorAdapter.java

Step 7: Create a class named CustomerDao.java and replace its code with below given code

CustomerDao.java

Step 8: Create a class named SqliteHelper.java and replace its code with below given code

SqliteHelper.java

Step 9: Add below given code to your MainActivity.java

MainActivity.java

Step 10: Create a class ReturnMessage.java and replace its code with below given code

ReturnMessage.java

Step 11: Clean and run the project to see the result.


Share & Like

Tutorial topics

About ApnaTutorials.com

ApnaTutorials.com is for Java, J2ee, Html5 and android developers. All tutorials are well explained and are easy to understand. We have tested every tutorials on our developement environment before posting it in public domain.