Skip to main content
image

Android Sqlite Database Example

Android Sqlite Database Example
Using Sqlite database in android, one can store the data in the application for its later use.
Here’s a handy guide to creating, updating and deleting data from SQLite database in android.

STEP 1 :
First of all create a new Android Application in android studio. Give it your desired name like
SQLite Example

Image

I am going to create a simple application which will be storing the favorite places from around the world.
User will be entering the place name and its country in one activity and adding it from there.
And one activity will be containing the List of places already added.

image

STEP 2 : DESIGNING
Now open your activity_main.xml [it is here res>layout> activity_main.xml] and add the following xml code to it, so that it will look like :

image1

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical"
    android:weightSum="7"
    android:background="#ffb6ffb0">

    <TextView
        android:layout_width="match_parent"
        android:layout_height="0dp"
        android:layout_weight="0.5"
        android:layout_margin="5dp"
        android:gravity="center_horizontal"
        android:text="PLACES TO VISIT : "
        android:textSize="25sp"
        android:textStyle="italic|normal" />

    <EditText
        android:id="@+id/edplaceid"
        android:layout_width="match_parent"
        android:layout_height="0dp"
        android:layout_weight="1"
        android:layout_margin="5dp"
        android:hint="PLACE ID" />

    <EditText
        android:id="@+id/edplace"
        android:layout_width="match_parent"
        android:layout_height="0dp"
        android:layout_weight="1"
        android:layout_margin="5dp"
        android:hint="PLACE NAME" />

    <EditText
        android:id="@+id/edcountry"
        android:layout_width="match_parent"
        android:layout_height="0dp"
        android:layout_weight="1"
        android:layout_margin="5dp"
        android:hint="COUNTRY" />

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="0dp"
        android:layout_weight="0.8"
        android:orientation="horizontal"
        android:weightSum="3">

        <Button
            android:id="@+id/btnadd"
            android:layout_width="0dp"
            android:layout_marginLeft="5dp"
            android:layout_marginRight="5dp"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:background="#ffff2f5d"
            android:textColor="#fff"
            android:textSize="20sp"
            android:text="ADD" />

        <Button
            android:id="@+id/btnupdate"
            android:layout_width="0dp"
            android:layout_marginLeft="5dp"
            android:layout_marginRight="5dp"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:background="#ffff2f5d"
            android:textColor="#fff"
            android:textSize="20sp"
            android:text="UPDATE" />

        <Button
            android:id="@+id/btndelete"
            android:layout_width="0dp"
            android:layout_marginLeft="5dp"
            android:layout_marginRight="5dp"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:background="#ffff2f5d"
            android:textColor="#fff"
            android:textSize="20sp"
            android:text="DELETE" />
    </LinearLayout>

    <Button
        android:id="@+id/btnview"
        android:layout_width="match_parent"
        android:layout_marginLeft="5dp"
        android:layout_marginRight="5dp"
        android:layout_marginTop="5dp"
        android:layout_height="0dp"
        android:layout_weight="0.8"
        android:background="#ff2b84ff"
        android:textColor="#fff"
        android:textSize="20sp"
        android:text="VIEW PLACES" />

    <TextView
        android:id="@+id/txtresulttext"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_marginLeft="5dp"
        android:layout_marginTop="15dp"
        android:gravity="left"
        android:text=""
        android:layout_weight="1"
        android:textStyle="italic|bold"
        android:textSize="20sp" />
</LinearLayout>

The code it pretting much self explanatory.

Now make an another xml file in the same folder and name it placeslist.xml and add following code to it :

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical"
    android:weightSum="10">

    <ListView
        android:id="@+id/placeslist"
        android:layout_width="match_parent"
        android:layout_weight="9"
        android:layout_height="0dp"
        android:layout_alignParentLeft="true"></ListView>

    <TextView
        android:id="@+id/txtresulttext"
        android:layout_width="match_parent"
        android:layout_height="0dp"
        android:layout_marginLeft="5dp"
        android:layout_marginTop="5dp"
        android:gravity="left"
        android:text=""
        android:layout_weight="1"
        android:textStyle="italic|bold"
        android:textSize="13sp" />
</LinearLayout>

This xml will make up the design of the second class which will be displaying details from the database.
Now add one last xml file in the same folder and give it the name rows.xml
And edit it as following :

<?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="wrap_content"
    android:orientation="vertical" >

    <LinearLayout
        android:id="@+id/lvh"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal"
        android:scrollbars="horizontal"
        android:weightSum="4" >

        <TextView
            android:id="@+id/txtplaceid"
            android:layout_width="0dp"
            android:layout_height="wrap_content"
            android:layout_weight="0.6"
            android:gravity="center"
            android:text="1"
            android:background="#ffb6ffb0"
            android:textColor="#000"
            android:textSize="20sp" />

        <TextView
            android:id="@+id/txtplacename"
            android:layout_width="0dp"
            android:layout_height="wrap_content"
            android:layout_weight="1.7"
            android:gravity="center"
            android:text="Barcelona"
            android:background="#ffc5ffe3"
            android:textColor="#000"
            android:textSize="20sp" />

        <TextView
            android:id="@+id/txtcountry"
            android:layout_width="0dp"
            android:layout_height="wrap_content"
            android:layout_weight="1.7"
            android:gravity="center"
            android:text="Spain"
            android:background="#ffd3f5ff"
            android:textColor="#000"
            android:textSize="20sp" />


    </LinearLayout>

</LinearLayout>

This is will the list row of the listview.

STEP 3 : THE CODE
Create a new class [.java] file and name it as DBController.java
This file will be used to make our database.

Open it and add the code as follows :
First declare following :

public class DBController extends SQLiteOpenHelper {
    private static final String tablename = "places";  // tablename
    private static final String place = "place";  // column name
    private static final String id = "ID";  // auto generated ID column
    private static final String country = "country"; // column name
    private static final String databasename = "placesinfo"; // Dtabasename
    private static final int versioncode = 1; //versioncode of the database

    public DBController(Context context) {
        super(context, databasename, null, versioncode);

    }

In the onCreate method, add the following code to create the database while running the app for the very first time :

 
@Override
    public void onCreate(SQLiteDatabase database) {
        String query;
        query = "CREATE TABLE IF NOT EXISTS " + tablename + "(" + id + " integer primary key, " + place + " text, " + country + " text)";
        database.execSQL(query);
    }

In the onUpgrade method :

@Override
    public void onUpgrade(SQLiteDatabase database, int version_old,
                          int current_version) {
        String query;
        query = "DROP TABLE IF EXISTS " + tablename;
        database.execSQL(query);
        onCreate(database);
    }

To get the data from the database I am using ArrayList which will fetch all the data present in the table.

public ArrayList<HashMap<String, String>> getAllPlace() {
        ArrayList<HashMap<String, String>> wordList;
        wordList = new ArrayList<HashMap<String, String>>();
        String selectQuery = "SELECT  * FROM " + tablename;
        SQLiteDatabase database = this.getWritableDatabase();
        Cursor cursor = database.rawQuery(selectQuery, null);
        if (cursor.moveToFirst()) {
            do {

                HashMap<String, String> map = new HashMap<String, String>();
                map.put("id", cursor.getString(0));
                map.put("place", cursor.getString(1));
                map.put("country", cursor.getString(2));

                wordList.add(map);
            } while (cursor.moveToNext());
        }

        // return contact list
        return wordList;
    }

Here’s the complete code of this class

package parallelcodes.sqliteexample;

/**
 * Created by abc on 20-May-15.
 */


import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import java.util.ArrayList;
import java.util.HashMap;

public class DBController extends SQLiteOpenHelper {
    private static final String tablename = "places";  // tablename
    private static final String place = "place";  // column name
    private static final String id = "ID";  // auto generated ID column
    private static final String country = "country"; // column name
    private static final String databasename = "placesinfo"; // Dtabasename
    private static final int versioncode = 1; //versioncode of the database

    public DBController(Context context) {
        super(context, databasename, null, versioncode);

    }

    @Override
    public void onCreate(SQLiteDatabase database) {
        String query;
        query = "CREATE TABLE IF NOT EXISTS " + tablename + "(" + id + " integer primary key, " + place + " text, " + country + " text)";
        database.execSQL(query);
    }

    @Override
    public void onUpgrade(SQLiteDatabase database, int version_old,
                          int current_version) {
        String query;
        query = "DROP TABLE IF EXISTS " + tablename;
        database.execSQL(query);
        onCreate(database);
    }

    public ArrayList<HashMap<String, String>> getAllPlace() {
        ArrayList<HashMap<String, String>> wordList;
        wordList = new ArrayList<HashMap<String, String>>();
        String selectQuery = "SELECT  * FROM " + tablename;
        SQLiteDatabase database = this.getWritableDatabase();
        Cursor cursor = database.rawQuery(selectQuery, null);
        if (cursor.moveToFirst()) {
            do {

                HashMap<String, String> map = new HashMap<String, String>();
                map.put("id", cursor.getString(0));
                map.put("place", cursor.getString(1));
                map.put("country", cursor.getString(2));

                wordList.add(map);
            } while (cursor.moveToNext());
        }

        // return contact list
        return wordList;
    }
}

Now create a new class (.java) file with name PlaceList.java. This class will be making the second activity which will be displaying the data in the listview.

Edit as following :
First declare the following variable.

DBController controller = new DBController(this);
    ListView ls;
    TextView infotext;

Now in the onCreate method initialize these variables :

@Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.placeslist);

        ls = (ListView) findViewById(R.id.placeslist);  // Listview
        infotext = (TextView) findViewById(R.id.txtresulttext);  // information label

Now to display the data in the listview add the following code to the onCreate method itself :

try {
            List<HashMap<String, String>> data = controller.getAllPlace(); 
            //Calling the same method (Arraylist) declared in the DBController activity
            if (data.size() != 0) {   // if data is present do this
                SimpleAdapter adapter = new SimpleAdapter(
                        PlacesList.this, data, R.layout.rows,
                        new String[]{"id", "place", "country"}, new int[]{
                        R.id.txtplaceid, R.id.txtplacename,
                        R.id.txtcountry});

                ls.setAdapter(adapter);
                String length = String.valueOf(data.size());
                infotext.setText(length + " places");
            } else {  // if no data is present give the message no data in database.
                infotext.setText("No data in database");
            }

        } catch (Exception ex) {  
        //only if runtime error occurred , error will be displayed instead of force closing app
            infotext.setText(ex.getMessage().toString());
        }

Here’s the full code of PlaceList.java file

package parallelcodes.sqliteexample;

import android.os.Bundle;
import android.support.v7.app.ActionBarActivity;
import android.view.Menu;
import android.view.MenuItem;
import android.widget.ListView;
import android.widget.SimpleAdapter;
import android.widget.TextView;

import java.util.HashMap;
import java.util.List;


public class PlacesList extends ActionBarActivity {
    DBController controller = new DBController(this);
    ListView ls;
    TextView infotext;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.placeslist);

        ls = (ListView) findViewById(R.id.placeslist);
        infotext = (TextView) findViewById(R.id.txtresulttext);

        try {
            List<HashMap<String, String>> data = controller.getAllPlace();
            if (data.size() != 0) {
                // Srno, RMCode, Fileno, Loc, FileDesc, TAGNos
                SimpleAdapter adapter = new SimpleAdapter(
                        PlacesList.this, data, R.layout.rows,
                        new String[]{"id", "place", "country"}, new int[]{
                        R.id.txtplaceid, R.id.txtplacename,
                        R.id.txtcountry});

                ls.setAdapter(adapter);
                String length = String.valueOf(data.size());
                infotext.setText(length + " places");
            } else {
                infotext.setText("No data in database");
            }

        } catch (Exception ex) {
            infotext.setText(ex.getMessage().toString());
        }
    }

    @Override
    public boolean onCreateOptionsMenu(Menu menu) {
        getMenuInflater().inflate(R.menu.menu_main, menu);
        return true;
    }

    @Override
    public boolean onOptionsItemSelected(MenuItem item) {
        int id = item.getItemId();

        if (id == R.id.action_settings) {
            return true;
        }

        return super.onOptionsItemSelected(item);
    }
}

MainActivity.java

Now open the MainActivity.java file. This file will be used to do all the task of adding the place, updating it and deleting it. This file is the homepage of my application.
So let’s add the code to make it work.
Add the following code to it :

public class MainActivity extends ActionBarActivity {
    DBController controller = new DBController(this);  // the database class 
    Button add, view, update, delete;
    EditText placeid, place, country;
    TextView infotext;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        placeid = (EditText) findViewById(R.id.edplaceid);  // EditBox for Place ID
        place = (EditText) findViewById(R.id.edplace); // EditBox for Place Name
        country = (EditText) findViewById(R.id.edcountry); // EditBox for Place Country

        add = (Button) findViewById(R.id.btnadd); // Add button to add the place details
        update = (Button) findViewById(R.id.btnupdate); // Update button to update the place details
        delete = (Button) findViewById(R.id.btndelete); // Delete button to delete a place  from database
        view = (Button) findViewById(R.id.btnview);  // Button to view the places

        infotext = (TextView) findViewById(R.id.txtresulttext);  // information label or result label

Now add the onClickListener to the view button : It will start another activity (PlaceList.class)

view.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                Intent i = new Intent(MainActivity.this, PlacesList.class);
                startActivity(i);
            }
        });

Now add onClickListener to the add button to add the places to database :
It will first check if both place name and country name is entered or not

add.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                try {
                    if (place.getText().toString().trim().equals("") || country.getText().toString().trim().equals("")) {
                        infotext.setText("Please insert place name and country..");
                    } else {
                        controller = new DBController(getApplicationContext());
                        SQLiteDatabase db = controller.getWritableDatabase();
                        ContentValues cv = new ContentValues();
                        cv.put("place", place.getText().toString());
                        cv.put("country", country.getText().toString());
                        db.insert("places", null, cv);
                        db.close();
                        infotext.setText("Place added Successfully");
                    }
                } catch (Exception ex) {
                    infotext.setText(ex.getMessage().toString());
                }
            }
        });

For Update button listeners add :

update.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                try {
                    if ((place.getText().toString().trim().equals("") && country.getText().toString().trim().equals("")) || placeid.getText().toString().trim().equals("")) {
                        infotext.setText("Please insert values to update..");
                    } else {
                        controller = new DBController(getApplicationContext());
                        SQLiteDatabase db = controller.getWritableDatabase();
                        ContentValues cv = new ContentValues();
                        cv.put("place", place.getText().toString());
                        cv.put("country", country.getText().toString());

                        db.update("places", cv, "id=" + placeid.getText().toString(), null);

                        Toast.makeText(MainActivity.this,
                                "Updated successfully", Toast.LENGTH_SHORT)
                                .show();

                        infotext.setText("Updated Successfully");
                    }
                } catch (Exception ex) {
                    infotext.setText(ex.getMessage().toString());
                }
            }
        });

And lastly for the delete button listener add :

delete.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                try {
                    if (placeid.getText().toString().trim().equals("")) {
                        infotext.setText("Please insert place ID to delete..");
                    } else {
                        controller = new DBController(getApplicationContext());
                        SQLiteDatabase db = controller.getWritableDatabase();

                        db.delete("places", "id=" + placeid.getText().toString(), null);

                        Toast.makeText(MainActivity.this,
                                "deleted successfully", Toast.LENGTH_SHORT)
                                .show();
                        infotext.setText("Deleted Successfully");
                    }
                } catch (Exception ex) {
                    infotext.setText(ex.getMessage().toString());
                }
            }
        });

Here’s add complete code for your reference :

package parallelcodes.sqliteexample;

import android.content.ContentValues;
import android.content.Intent;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.support.v7.app.ActionBarActivity;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;
import android.widget.Toast;


public class MainActivity extends ActionBarActivity {
    DBController controller = new DBController(this);
    Button add, view, update, delete;
    EditText placeid, place, country;
    TextView infotext;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        placeid = (EditText) findViewById(R.id.edplaceid);
        place = (EditText) findViewById(R.id.edplace);
        country = (EditText) findViewById(R.id.edcountry);

        add = (Button) findViewById(R.id.btnadd);
        update = (Button) findViewById(R.id.btnupdate);
        delete = (Button) findViewById(R.id.btndelete);
        view = (Button) findViewById(R.id.btnview);

        infotext = (TextView) findViewById(R.id.txtresulttext);

        view.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                Intent i = new Intent(MainActivity.this, PlacesList.class);
                startActivity(i);
            }
        });
        add.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                try {
                    if (place.getText().toString().trim().equals("") || country.getText().toString().trim().equals("")) {
                        infotext.setText("Please insert place name and country..");
                    } else {
                        controller = new DBController(getApplicationContext());
                        SQLiteDatabase db = controller.getWritableDatabase();
                        ContentValues cv = new ContentValues();
                        cv.put("place", place.getText().toString());
                        cv.put("country", country.getText().toString());
                        db.insert("places", null, cv);
                        db.close();
                        infotext.setText("Place added Successfully");
                    }
                } catch (Exception ex) {
                    infotext.setText(ex.getMessage().toString());
                }
            }
        });

        update.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                try {
                    if ((place.getText().toString().trim().equals("") && country.getText().toString().trim().equals("")) || placeid.getText().toString().trim().equals("")) {
                        infotext.setText("Please insert values to update..");
                    } else {
                        controller = new DBController(getApplicationContext());
                        SQLiteDatabase db = controller.getWritableDatabase();
                        ContentValues cv = new ContentValues();
                        cv.put("place", place.getText().toString());
                        cv.put("country", country.getText().toString());

                        db.update("places", cv, "id=" + placeid.getText().toString(), null);

                        Toast.makeText(MainActivity.this,
                                "Updated successfully", Toast.LENGTH_SHORT)
                                .show();

                        infotext.setText("Updated Successfully");
                    }
                } catch (Exception ex) {
                    infotext.setText(ex.getMessage().toString());
                }
            }
        });

        delete.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                try {
                    if (placeid.getText().toString().trim().equals("")) {
                        infotext.setText("Please insert place ID to delete..");
                    } else {
                        controller = new DBController(getApplicationContext());
                        SQLiteDatabase db = controller.getWritableDatabase();

                        db.delete("places", "id=" + placeid.getText().toString(), null);

                        Toast.makeText(MainActivity.this,
                                "deleted successfully", Toast.LENGTH_SHORT)
                                .show();
                        infotext.setText("Deleted Successfully");
                    }
                } catch (Exception ex) {
                    infotext.setText(ex.getMessage().toString());
                }
            }
        });
    }


    @Override
    public boolean onCreateOptionsMenu(Menu menu) {
        getMenuInflater().inflate(R.menu.menu_main, menu);
        return true;
    }

    @Override
    public boolean onOptionsItemSelected(MenuItem item) {
        int id = item.getItemId();

        if (id == R.id.action_settings) {
            return true;
        }

        return super.onOptionsItemSelected(item);
    }
}

And the Manifest file
AndroidManifest.xml

<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
    package="parallelcodes.sqliteexample" >

    <application
        android:allowBackup="true"
        android:icon="@mipmap/ic_launcher"
        android:label="@string/app_name"
        android:theme="@style/AppTheme" >
        <activity
            android:name=".MainActivity"
            android:label="@string/app_name" >
            <intent-filter>
                <action android:name="android.intent.action.MAIN" />

                <category android:name="android.intent.category.LAUNCHER" />
            </intent-filter>
        </activity>
        <activity
            android:name=".PlacesList"
            android:label="@string/app_name"/>
    </application>
</manifest>

Now the application is ready you can run it.

I hope you enjoyed this post and do comment if you get any errors in this app. I will try to help you out.
If you like my posts please like and like my page on Facebook.