SQLite Database

SQLite Database: 

SQLite is an Open Source Database which is embedded into Android. SQLite supports standard relational database features like SQL syntax, transactions and prepared statements. In addition it requires only little memory at runtime (approx. 250 KByte).

SQLite supports the data types TEXT (similar to String in Java), INTEGER (similar to long in Java) andREAL (similar to double in Java). All other types must be converted into one of these fields before saving them in the database. SQLite itself does not validate if the types written to the columns are actually of the defined type, e.g. you can write an integer into a string column and vice versa. Using an SQLite database in Android does not require any database setup or administration.You only have to define the SQL statements for creating and updating the database. Afterwards the database is automatically managed for you by the Android platform.

Advantages of using database are like one doesn’t need to read entire file, load it in memory for getting some small chuck of data, also from developer perspective retrieve data from database would be easier.

SQLiteOpenHelper

To create and upgrade a database in your Android application you usually subclassSQLiteOpenHelper. In the constructor of your subclass you call the super() method ofSQLiteOpenHelper, specifying the database name and the current database version.In this class you need to override the onCreate() and onUpgrade() methods. onCreate() is called by the framework, if the database does not exists. onUpgrade() is called, if the database version is increased in your application code. This method allows you to update the database schema. Both methods receive an SQLiteDatabase object as parameter which represents the database.

SQLiteOpenHelper provides the methods  getReadableDatabase()  and getWriteableDatabase()  to get access to an SQLiteDatabase object; either in read or write mode. The database tables should use the identifier _id for the primary key of the table. Several Android functions rely on this standard.

It is best practice to create a separate class per table. This class defines static  onCreate()  and onUpdate() methods. These methods are called in the corresponding methods ofSQLiteOpenHelper. This way your implementation of SQLiteOpenHelper will stay readable, even if you have several tables.

SQLiteDatabase

SQLiteDatabase is the base class for working with a SQLite database in Android and provides methods to open, query, update and close the database.More specifically  SQLiteDatabase provides the insert(), update() and delete() methods.In addition it provides the execSQL() method, which allows to execute an SQL statement directly. The object ContentValues allows to define key/values. The "key" represents the table column identifier and the "value" represents the content for the table record in this column. ContentValues can be used for inserts and updates of database entries.

Queries can be created via the rawQuery() and query() methods or via theSQLiteQueryBuilder class . rawQuery() directly accepts an SQL select statement as input. query() provides a structured interface for specifying the SQL query. SQLiteQueryBuilder is a convenience class that helps to build SQL queries.

Cursor

A query returns a Cursor object. A Cursor represents the result of a query and basically points to one row of the query result. This way Android can buffer the query results efficiently; as it does not have to load all data into memory.To get the number of elements of the resulting query use the getCount() method.

To move between individual data rows, you can use the  moveToFirst()  and  moveToNext() methods. The isAfterLast() method allows to check if the end of the query result has been reached. Cursor provides typed get*() methods, e.g.  getLong(columnIndex),getString(columnIndex) to access the column data for the current position of the result. The "columnIndex" is the number of the column you are accessing.

Cursor also provides the getColumnIndexOrThrow(String) method which allows to get the column index for a column name of the table. A Cursor needs to be closed with the close() method call.

Let’s see the programetic example, so that we can understand very well.

Here we Create 2 Button, One is for Insert and another is for Fetching the values.

Main.xml

<?xml version="1.0" encoding="utf-8"?>

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"

    android:layout_width="fill_parent"

    android:layout_height="fill_parent"

    android:orientation="vertical" >

    <TextView

        android:layout_width="fill_parent"

        android:layout_height="wrap_content"

        android:text="Database Example"

        android:gravity="center"/>

    <RelativeLayout

        android:id="@+id/relativeLayout1"

        android:layout_width="match_parent"

        android:layout_height="wrap_content" >

        <Button

            android:id="@+id/button1"

            android:layout_width="wrap_content"

            android:layout_height="wrap_content"

            android:layout_alignParentTop="true"

            android:layout_centerHorizontal="true"

            android:text="Insert" />

    </RelativeLayout>

    <RelativeLayout

        android:id="@+id/relativeLayout2"

        android:layout_width="match_parent"

        android:layout_height="wrap_content" >

        <Button

            android:id="@+id/button2"

            android:layout_width="wrap_content"

            android:layout_height="wrap_content"

            android:layout_alignParentTop="true"

            android:layout_centerHorizontal="true"

            android:text="View" />

    </RelativeLayout>

</LinearLayout>

Now we create a new class MyDatabase. This class I will create for creating Database, inserting into database & updating the database. Now I create a inner class which will create the database and it will extends from SQLiteOpenHelper. When You extending from SQLiteOpenHelper , you need to add unimplemented method and constructor.

Coding will look like this:

MyDatabase.java

import android.content.ContentValues;

import android.content.Context;

import android.database.Cursor;

import android.database.sqlite.SQLiteDatabase;

import android.database.sqlite.SQLiteDatabase.CursorFactory;

import android.database.sqlite.SQLiteOpenHelper;

public class MyDatabase

{

      public static final String MY_DB = "DataBase";

      Context myCon;

      SQLiteDatabase sdb;

      MyHelper mh;

     

      public MyDatabase(Context con)

      {

            myCon = con;

            mh = new MyHelper(myCon, MY_DB, null, 1);

      }

     

      //Inserting the Data

      public void insertData()

      {

            sdb = mh.getWritableDatabase();

            ContentValues cv = new ContentValues();

            cv.put("name", "Sam");

            cv.put("salary", 2000);

            sdb.insert("Data", null, cv);

      }

     

      //Fetching the Data

      public Cursor getData()

      {

            sdb = mh.getReadableDatabase();

            Cursor c = sdb.query("Data", null, null, null, null, null, null);

            return c;

      }

     

      //Inner Helper Class

      public class MyHelper extends SQLiteOpenHelper

      {

            public MyHelper(Context context, String name, CursorFactory factory,

                        int version) {

                  super(context, name, factory, version);

                  // TODO Auto-generated constructor stub

            }

            @Override

            public void onCreate(SQLiteDatabase db)

            {

                  //Here Creating the Table

                  db.execSQL("create table Data(_id integer primary key, name text, salary integer);");

            }

            @Override

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

                  // TODO Auto-generated method stub

                 

            }

           

      }

}

My Main Java file is like that:

DatabaseSampleActivity.java

import android.app.Activity;

import android.database.Cursor;

import android.os.Bundle;

import android.view.View;

import android.view.View.OnClickListener;

import android.widget.Button;

import android.widget.Toast;

public class DatabaseSampleActivity extends Activity

{

      MyDatabase db = new MyDatabase(this);

     

    public void onCreate(Bundle savedInstanceState) {

        super.onCreate(savedInstanceState);

        setContentView(R.layout.main);

       

        Button insert = (Button)findViewById(R.id.button1);

        Button view = (Button)findViewById(R.id.button2);

       

        insert.setOnClickListener(new OnClickListener() {

                 

                  @Override

                  public void onClick(View v)

                  {

                        db.insertData();

                        Toast.makeText(getApplicationContext(), "Data Inserted", 0).show();

                  }

            });

       

        view.setOnClickListener(new OnClickListener() {

                 

                  @Override

                  public void onClick(View v)

                  {

                        Cursor c = db.getData();

                        if(c!=null)

                        {

                              while(c.moveToNext())

                              {

                                    String name = c.getString(1);

                                    int sal = c.getInt(2);

                                    Toast.makeText(getApplicationContext(), "Name: "+name+" Salary: " +sal, 0).show();

                              }

                        }

                  }

            });

    }

    @Override

    protected void onDestroy()

    {

      super.onDestroy();

    }

}

Output:

When you click on Insert Button, data will insert into the database.

After Inserting into the database, you need to see the Values.  Click on the View Button, so that you can see.