SQLite Database used in Android Application Development

SQLite is a relational database management system (RDBMS). SQLite database used in Android Application Development. It is well regarded. It is Open source, Lightweight. It uses a Single-tier database architecture. SQLite is an Open Source Database that is embedded into Android. SQLite even supports standard relational database features like SQL syntax, transactions, and prepared statements.

SQLite database supports three types of data types as Text, Integer and Real. You might be familiar with text and integer data type as it is a basic data type, almost uses in every language. Real is one of the important data types supported by the SQLite database used in Android Application Development.

If you want to develop your own Android App, get our Full Android Application Development Tutorial FREE.

All other types must be converted into one of these fields before saving them in the database. SQLite is available on every Android device. 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. Afterward, the database is automatically managed for you by the Android platform.

Packages for SQLite Database

The package android. SQLite database contains all general classes for working with databases.

Package android.database.sqlite contains the SQLite specific classes.

1. SQLiteOpenHelper

To create and upgrade a database in your Android application you usually create a subclass of SQLiteOpenHelper. In this class, you need to override the onCreate() and onUpgrade() methods.

onCreate() is called by the framework if the database does not exist.

onUpgrade() is called if the database version is increased in your application code. This method allows you to update the database schema.

These two receive an SQLiteDatabase object as their argument. SQLiteOpenHelper uses the methods getReadableDatabase() and getWriteableDatabase() to get access to an SQLiteDatabase object. either in read or write mode.

It is good to create a separate class per table. This class defines static onCreate() and onUpdate() methods.

2. SQLiteDatabase

SQLiteDatabase is the other base class used for working with the SQLite database in Android and provides methods to open the database, query means to select, update and close the database. In addition, it provides the execSQL() method, which allows executing an SQL statement directly. Queries can be created via the rawQuery() and query() methods or via the SQLiteQueryBuilder class.

query() provides a structured interface for specifying the SQL query.

query() Example:

The following gives an example of a query() call.

return database.query(TABLE Name, new String[] { KEY_ROWID, KEY_CATEGORY, KEY_SUMMARY, KEY_DESCRIPTION }, null, null, null, null, null);

The method query() has the following parameters.

Table 1. Parameters of the query() method

	Comment
String dbName	The table name to compile the query against.
int[] columnNames	A list of which table columns to return. Passing “null” will return all columns.
String whereClause	Where-clause, i.e. filter for the selection of data, null will select all data.
String[] selectionArgs	You may include ?s in the “whereClause””. These placeholders will get replaced by the values from the `selectionArgs` array.
String[] groupBy	A filter declaring how to group rows, null will cause the rows to not be grouped.
String[] having	Filter for the groups, null means no filter.
String[] orderBy	Table columns will be used to order the data, null means no order.

If a condition is not required you can pass null, e.g. for the group by clause.

The “whereClause” is specified without the word “where”, for example, a “where” statement might look like: “id=19 and summary=Title”.

3. Cursor

A query that returns a Cursor object.Cursor is similar to the ResulSet object in java and the DataReader object in c#.net or VB.net. A Cursor represents the result of a query and basically points to the first row of the result returned by the query. 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.

Cursor provides typed getDatatype() methods, e.g. getInteger(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.

A Cursor needs to be closed with the close() method call.

For Example:

package com.org.net;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;</p>
<p align="justify">public class contact extends SQLiteOpenHelper {</p>

<p align="justify">            
  public contact(Context context, String name, CursorFactory factory,
    int version) {
      super(context, name, factory, version);
      // TODO Auto-generated constructor stub
    }
</p>

<p align="justify">
  @Override
  public void onCreate(SQLiteDatabase arg0) {
    // TODO Auto-generated method stub
    String cr="Create table contact(id integer primary key,name text,number text)";
    arg0.execSQL(cr);
  }
</p>

<p align="justify">
  @Override
  public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
    // TODO Auto-generated method stub
    String cr="DROP TABLE IF EXISTS contact";
    arg0.execSQL(cr);
    onCreate(arg0);
  }
</p>

public long insert(ContentValues val)
{
  long rowid=0;
  SQLiteDatabase db=getWritableDatabase();

  db.insert("contact",null,val);
  return rowid;
}

public  Cursor getall()
{
  Cursor cursor=null;
  SQLiteDatabase db=getReadableDatabase();

  cursor=db.query("contact",null,null,null,null,null,null);

  return cursor;
}

<p align="justify">}</p>

<p align="justify"><strong>Activity File</strong></p>
 
<pre class="lang:default decode:true " >
package com.org.net;
import android.app.Activity;
import android.content.ContentValues;
import android.database.Cursor;
import android.os.Bundle;
import android.util.Log;
import android.widget.Toast;
</p>

<p align="justify">
  public class MydatabaseActivity extends Activity {
  /** Called when the activity is first created. */

  @Override
  public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.main);
    contact con=new contact(this, "ContactDB",null,1);</p>
    ContentValues content=new ContentValues();
    content.put("name", "Gaurao");
    content.put("no","777777777777");
    long rowid=con.insert(content);
    Log.d("Insert",String.valueOf(rowid));

    content.clear();
    content.put("name", "Rupesh");
    content.put("no","77777777777");
    rowid=con.insert(content);
    Log.d("Insert",String.valueOf(rowid));

    Cursor cr=con.getall();
    int i=0;

    while(cr.moveToNext())
    {
      Log.d(String.valueOf(i),"Name="+cr.getString(1));
    }

    cr.close();
    Toast.makeText(this,"Gaurao",1000).show();
  }
}

Full android web application Tutorial:-