SQLite Database Android Example

SQLite is an Open Source database. SQLite supports standard relational database features like SQL syntax, transactions and prepared statements. The database requires limited memory at runtime (approx. 250 KByte) which makes it a good candidate from being embedded into other runtimes.SQLiteDatabase has methods to create, delete, execute SQL commands, and perform other common database management tasks. SQLiteDatabase is the base class for working with a SQLite database in Android and provides methods to open, query, update and close the database.

1. MainActivity.java

package com.tutorialsee;

import android.os.Bundle;
import android.app.Activity;
import android.database.Cursor;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;

public class MainActivity extends Activity {
static DBAdapter db;

@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
      
final Button b1=(Button) findViewById(R.id.button1);
final Button b2=(Button) findViewById(R.id.button2);
final Button b3=(Button) findViewById(R.id.button3);
final Button b4=(Button) findViewById(R.id.button4);
final Button b5=(Button) findViewById(R.id.button5);
final EditText et1=(EditText)findViewById(R.id.editText1);
final EditText et2=(EditText)findViewById(R.id.editText2);
final EditText et3=(EditText)findViewById(R.id.editText3);

  db = new DBAdapter(MainActivity.this);

//---Insert Contact---
b1.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
db.open();
db.insertContact(et2.getText().toString(),et3.getText().toString());
db.close();
Toast.makeText(getBaseContext(), "Inserted",Toast.LENGTH_SHORT).show();
}
});

//---Select All contacts---
b2.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
db.open();
Cursor c = db.getAllContacts();
if (c.moveToFirst())
{
do {
DisplayContact(c);
} while (c.moveToNext());
}
db.close();
}

private void DisplayContact(Cursor c)
{
Toast.makeText(getBaseContext(),"id: " + c.getString(0) + "\n" +"Name: " + c.getString(1) + "\n" +
"Email: " + c.getString(2),
Toast.LENGTH_LONG).show();
}
});

//---Select a contact---   
b3.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
db.open();
Cursor c = db.getContact(Integer.parseInt(et1.getText().toString()));
if (c.moveToFirst())       
DisplayContact(c);
else
Toast.makeText(getBaseContext(), "No contact found", Toast.LENGTH_LONG).show();
db.close();
}

private void DisplayContact(Cursor c) {
Toast.makeText(getBaseContext(),"id: " + c.getString(0) + "\n" +"Name: " + c.getString(1) + "\n" +
"Email: " + c.getString(2),
Toast.LENGTH_LONG).show();
}
}) ;

//---updates a contact---      
b4.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
db.open();
if (db.updateContact
(Integer.parseInt(et1.getText().toString()), et2.getText().toString(), et3.getText().toString()))
Toast.makeText(getBaseContext(), "Update successful.", Toast.LENGTH_LONG).show();
else
Toast.makeText(getBaseContext(), "Update failed.", Toast.LENGTH_LONG).show();
db.close();
}
});
//---delete a contact--- 
b5.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
db.open();
db.deleteContact(Integer.parseInt(et1.getText().toString()));
Toast.makeText(getBaseContext(), "Delete successful.", Toast.LENGTH_LONG).show();
db.close();
}
});
}
}

2. DBAdapter.java

package com.tutorialsee;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBAdapter {
public static final String KEY_ROWID = "_id";
public static final String KEY_NAME = "name";
public static final String KEY_EMAIL = "email";
private static final String TAG = "DBAdapter";
private static final String DATABASE_NAME = "MyDB";
private static final String DATABASE_TABLE = "contacts";
private static final int DATABASE_VERSION = 1;
private static final String DATABASE_CREATE ="create table contacts (_id integer primary key autoincrement, "
+ "name text not null, email text not null);";
private final Context context;
private DatabaseHelper DBHelper;
private SQLiteDatabase db;

public DBAdapter(Context ctx)
{
this.context = ctx;
DBHelper = new DatabaseHelper(context);
}

private static class DatabaseHelper extends SQLiteOpenHelper
{
DatabaseHelper(Context context)
{
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}

@Override
public void onCreate(SQLiteDatabase db)
{
try {
db.execSQL(DATABASE_CREATE);
} catch (SQLException e) {
e.printStackTrace();
}
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
{
Log.w(TAG, "Upgrading database from version " + oldVersion + " to " + newVersion + ", which will destroy all old data");
db.execSQL("DROP TABLE IF EXISTS contacts");
onCreate(db);
}
}

//---opens the database---
public DBAdapter open() throws SQLException
{
db = DBHelper.getWritableDatabase();
return this;
}

//---closes the database---
public void close()
{
DBHelper.close();
}

//---insert a contact into the database---
public long insertContact(String name, String email)
{
ContentValues initialValues = new ContentValues();
initialValues.put(KEY_NAME, name);
initialValues.put(KEY_EMAIL, email);
return db.insert(DATABASE_TABLE, null, initialValues);
}

//---deletes a particular contact---
public boolean deleteContact(long rowId)
{
return db.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowId, null) > 0;
}

//---retrieves all the contacts---
public Cursor getAllContacts()
{
return db.query(DATABASE_TABLE, new String[] {KEY_ROWID, KEY_NAME,KEY_EMAIL},
null, null, null, null, null);
}

//---retrieves a particular contact---
public Cursor getContact(long rowId) throws SQLException
{
Cursor mCursor =db.query(true, DATABASE_TABLE, new String[] {KEY_ROWID,
KEY_NAME, KEY_EMAIL}, KEY_ROWID + "=" + rowId, null,
null, null, null, null);
if (mCursor != null) {
    mCursor.moveToFirst();
}
return mCursor;
}

//---updates a contact---
public boolean updateContact(long rowId, String name, String email)
{
ContentValues args = new ContentValues();
args.put(KEY_NAME, name);
args.put(KEY_EMAIL, email);
return db.update(DATABASE_TABLE, args, KEY_ROWID + "=" + rowId, null) > 0;
}
}

3. activity_main.xml

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:background="#25AE90"
    android:orientation="vertical" >
   
    <TextView
         android:layout_width="match_parent"
        android:layout_height="150px"
        android:text="SQLite"
        android:textColor="@android:color/white"
        android:gravity="center"
        android:textSize="40sp"
       
        />

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="match_parent"
        android:background="#25AE90"
        android:gravity="center"
        android:orientation="vertical" >

        <EditText
            android:id="@+id/editText1"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:layout_marginLeft="10dp"
            android:layout_marginRight="10dp"
            android:background="@android:color/white"
            android:ems="4"
            android:hint="Id"
            android:inputType="number"
            android:padding="8dp"
            android:textColor="#25AE90"
            android:textColorHint="#25AE90" >

            <requestFocus />
        </EditText>

        <EditText
            android:id="@+id/editText2"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:layout_marginLeft="10dp"
            android:layout_marginRight="10dp"
            android:layout_marginTop="5dp"
            android:background="@android:color/white"
            android:ems="8"
            android:hint="Name"
            android:inputType="textPersonName"
            android:padding="8dp"
            android:textColor="#25AE90"
            android:textColorHint="#25AE90" >
        </EditText>

        <EditText
            android:id="@+id/editText3"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:layout_marginLeft="10dp"
            android:layout_marginRight="10dp"
            android:layout_marginTop="5dp"
            android:background="@android:color/white"
            android:ems="10"
            android:hint="Email"
            android:inputType="textEmailAddress"
            android:padding="8dp"
            android:textColor="#25AE90"
            android:textColorHint="#25AE90" />
       
     

        <LinearLayout
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:layout_marginLeft="10dp"
            android:layout_marginRight="10dp"
            android:layout_marginTop="60dp"
            android:background="#25AE90"
            android:gravity="center"
           
            android:orientation="horizontal" >

            <Button
                android:id="@+id/button1"
                android:layout_width="match_parent"
                android:layout_height="38dp"
                android:layout_marginRight="1dp"
                android:layout_weight="1"
                android:background="@android:color/white"
                android:text="Insert"
                android:textColor="#25AE90" />

            <Button
                android:id="@+id/button2"
                android:layout_width="match_parent"
                android:layout_height="38dp"
                android:layout_marginLeft="1dp"
                android:layout_weight="1"
                android:background="@android:color/white"
                android:text="Select All"
                android:textColor="#25AE90" />
        </LinearLayout>

        <LinearLayout
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:layout_marginLeft="10dp"
            android:layout_marginRight="10dp"
            android:background="#25AE90"
            android:gravity="center"
            android:orientation="horizontal" >

            <Button
                android:id="@+id/button3"
                android:layout_width="match_parent"
                android:layout_height="38dp"
                android:layout_marginRight="1dp"
                android:layout_marginTop="2dp"
                android:layout_weight="1"
                android:background="@android:color/white"
                android:text="Select"
                android:textColor="#25AE90" />

            <Button
                android:id="@+id/button4"
                android:layout_width="match_parent"
                android:layout_height="38dp"
                android:layout_marginLeft="1dp"
                android:layout_marginTop="2dp"
                android:layout_weight="1"
                android:background="@android:color/white"
                android:text="Update"
                android:textColor="#25AE90" />
        </LinearLayout>

        <Button
            android:id="@+id/button5"
            android:layout_width="match_parent"
            android:layout_height="38dp"
            android:layout_marginLeft="10dp"
            android:layout_marginRight="10dp"
            android:layout_marginTop="2dp"
            android:background="@android:color/white"
            android:text="Delete"
            android:textColor="#25AE90" />
      
    </LinearLayout>

</LinearLayout>

4. activity_dbadapter.xml

<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    tools:context="${relativePackage}.${activityClass}" >

    <TextView
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="@string/hello_world" />

</RelativeLayout>