Android SQLite Basics: creating and using a database, and working with sqlite

SQLite DB Example | Android Developer Tutorial

There are 4 ways of storing data on the android platform:

• 1.    Preferences
• 2.    SQLite Database
• 3.    Files
• 4.    Network

A word about each of them here and then I will move on to an example that shows how to work with SQLite DB that comes along with the android platform.

Preferences – 

Basically used for storing user preferences for a single application or across applications for a mobile. This is typically name-value pairs accessible to the context.

Databases – 

Android supports creating of databases based on SQLite db. Each database is private to the applications that creates it 

Files –

Files can be directly stored on the mobile or on to an extended storage medium. By default other applications cannot access it.

Network – 

Data can be stored and retrieved from the network too depending on the availability.

If an application wants to store and retrieve data for its own use, without having to share the data across applications, it can access the SQLite DB directly.

In this example, we will do the following:

1.    Create a database (typically a one time activity)

2.    Create a table (typically a one time activity)

3.    Insert values into the table

4.    Retrieve the values from the table

5.    Display the retrieved values as a List view

6.    Delete all the records from the table before closing the connection to the database
   For Total Project Click Here : SqliteListView
Final Out Put :

SqliteListView OutPut

Step 1: Create a database:

            sdb=this.openOrCreateDatabase(DB_NAME, MODE_PRIVATE, null);

     This opens a database defined in the constant DB_NAME , if it already exists. Else it creates a database and opens it. The second parameter is operating mode : MODE_PRIVATE meaning it is accessible to only this context. The other modes are and MODE_WORLD_WRITABLE. MODE_WORLD_READABLE

       Step 2: Create a Table:

                            sdb.execSQL("CREATE TABLE IF NOT EXISTS "+TABLE_Name+"(fname varchar,lname varchar,
                      country varchar, age int(3));");

       Step 3: Insert values into the table:
             sdb.execSQL("insert into "+TABLE_Name+" values('Sravan','voleti','Android Developer',24)");

        Step 4: Retrieve values 

Cursor c=sdb.rawQuery(" select * from "+TABLE_Name, null)
      if (c != null ) {

            if  (c.moveToFirst()) {

                  do {

String firstName = c.getString(c.getColumnIndex("FirstName"));

                  int age = c.getInt(c.getColumnIndex("Age"));

                  results.add("" + firstName + ",Age: " + age);

                  }while (c.moveToNext());

            } 

       }

       Step 5: Display the values as a list 

            

       this.setListAdapter(new ArrayAdapter<String>(this, android.R.layout.simple_list_item_1,list));

      The statement displays it as a list as the class extends a ListActivity.

     Step 6: Delete the values from the table in the finally part of the try block

finally {

            if (sdb != null) 

                  sampleDB.execSQL("DELETE FROM " + TABLE_NAME);

                  sampleDB.close();

        }

       It is as simple as this to work with the SQLite DB even in android. No different from a desktop application. However, there are various overloaded methods of query() provided by the SQLIteDatabase class which can be more optimally used instead of execSQL.




       Api Explanations :
      ---------------------------



/*
ArrayList :
---------- 
It is an implementation of List, backed by an array. All optional operations including adding, removing, and replacing elements 
are supported. All elements are permitted, including null.
ListActivity :
-------------
It is hosts a ListView object that can be bound to different data sources, typically either an array or a Cursor holding query results.


android.database.Cursor :
------------------------ 
This interface provides random read-write access to the result set returned by a database query. Cursor implementations are not required to
be synchronized so code using a Cursor from multiple threads should perform its own synchronization when using the Cursor.


android.database.sqlite.SQLiteDatabase :
---------------------------------------- 
Exposes methods to manage a SQLite database.
SQLiteDatabase has methods to create, delete, execute SQL commands, and perform other common database management tasks.


 android.database.sqlite.SQLiteException :
 -----------------------------------------
SQLite exception that indicates there was an error with SQL parsing or execution.

android.os.Bundle :
------------------
      A mapping from String values to various Parcelable types.


android.util.Log :
------------------
API for sending log output.
Generally, use the Log.v() Log.d() Log.i() Log.w() and Log.e() methods.


android.widget.ArrayAdapter :
-----------------------------
A ListAdapter that manages a ListView backed by an array of arbitrary objects. By default this class expects that the 
provided resource id references a single TextView.


 */