Android Sqlite Example

Sqlite is database which in embeded in android by default. It is  an opensource database which we can use to create,delete and edit data. For implement an sqlite database you need to have an SqliteOpenHelper class which will implement onCreate() and onUpgrade() methods.

The onCreate() method is called when the database is created for first time. And after the if you want to make some changes in your database structure like creating new table or altering existing table you need to make that changes in your onUpgrade() method.

Another class which we are using here is SqliteDatabase class which will contain the methods for insert,update and delete the values from the database. Here in this example i will show how insert,update,delete,values from an sqlite database which will contain a simple form ti insert name and email and to simply display that name in  a listview.

First you need to have a model class which will contain getters and setters for the data’s to be inserted or displayed. so create a file under src/your-package/PersonModel.java and add the following code.

PersonModel.java

public class PersonModel {
    String name, email;

    public PersonModel(String name, String email) {
        this.name = name;
        this.email = email;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }
}

Now you need to have an DbHelper class which will implement the methods of SqliteOpenHelper and make use of SqliteDatabase class, so create a file in the same location and use the below code

DbHelper.java

public class DBHelper extends SQLiteOpenHelper {

    public static final String DATABASE_NAME = "codesfor.db";
    public static final String TABLE_PERSON = "table_person";

    public static final String ID = "id";
    public static final String NAME = "name";
    public static final String EMAIL = "email";

    Context context;

    public DBHelper(Context context) {
        super(context, DATABASE_NAME, null, 1);
        this.context = context;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub

        db.execSQL(
                "create table table_person " +
                        "(id integer primary key,name text, email text)"
        );
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub

    }


    public boolean insert_person(PersonModel personModel) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put("name", personModel.getName());
        contentValues.put("email", personModel.getEmail());
        try{
            db.insert(TABLE_PERSON, null, contentValues);
        }catch (Exception e){

            Log.d("e",e.toString());
        }

        return true;
    }

    public ArrayList<String> GetAllNames() {
        ArrayList<String> persons = new ArrayList<String>();

        SQLiteDatabase db = this.getReadableDatabase();
        Cursor res = db.rawQuery("SELECT * FROM " + TABLE_PERSON , new String[]{});
        res.moveToFirst();

        while (res.isAfterLast() == false) {

            persons.add(res.getString(res.getColumnIndex(NAME)));
            res.moveToNext();
        }
        return persons;
    }



    public boolean DeletePerson(String email){

        SQLiteDatabase db = this.getReadableDatabase();
        return db.delete(TABLE_PERSON, EMAIL + "='" + email+"'", null) > 0;

    }


}

Now we just want to make use of those methods implemented in DbHelper with appropriate parameters. So am giving my activity code below which is using for both adding and displaying values from database.

Here is the code for my layout file , which will contain a listview for displaying data and a small form for inserting values into database

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:paddingBottom="@dimen/activity_vertical_margin"
    android:paddingLeft="@dimen/activity_horizontal_margin"
    android:paddingRight="@dimen/activity_horizontal_margin"
    android:paddingTop="@dimen/activity_vertical_margin"
    app:layout_behavior="@string/appbar_scrolling_view_behavior"
    tools:context="com.example.picker.AddPerson"
    tools:showIn="@layout/activity_add_person">

    <ListView
        android:id="@+id/lv_person"
        android:layout_width="match_parent"
        android:layout_height="match_parent"
        android:layout_alignParentTop="true"
        android:layout_above="@+id/ll_form"></ListView>

    <LinearLayout
        android:id="@+id/ll_form"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_alignParentBottom="true"
        android:orientation="vertical">

        <EditText
            android:id="@+id/ed_name"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:hint="Name" />

        <EditText
            android:id="@+id/ed_email"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:hint="email" />

        <Button
            android:id="@+id/btn_save"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:text="save"
            android:textColor="@color/white" />

    </LinearLayout>

</RelativeLayout>

And here is my class ,i named it as AddPerson.java ,since my functionality is to add persons data.

AddPerson.java

public class AddPerson extends AppCompatActivity {

    Toolbar toolbar;
    ListView lv_person;
    ArrayList<String> name_list;
    DBHelper dbHelper;
    ArrayAdapter<String> person_list_adapter;

    EditText ed_name,ed_email;
    Button btn_save;

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

        //method for initialisation
        init();

        btn_save.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {

                if(dbHelper.insert_person(new PersonModel(ed_name.getText().toString(),ed_email.getText().toString()))){

                    name_list.add(ed_name.getText().toString());
                    person_list_adapter.notifyDataSetChanged();
                    ed_name.setText("");
                    ed_email.setText("");
                }
            }
        });
    }

    private void init() {

        toolbar = (Toolbar) findViewById(R.id.toolbar);
        setSupportActionBar(toolbar);


        lv_person = (ListView) findViewById(R.id.lv_person);
        ed_email = (EditText) findViewById(R.id.ed_email);
        ed_name = (EditText) findViewById(R.id.ed_name);
        btn_save = (Button) findViewById(R.id.btn_save);

        //initialising database
        dbHelper = new DBHelper(this);

        //calling function which contains name list fetching from sqlite database
        name_list = dbHelper.GetAllNames();

        //initialising array adapter for listview
        person_list_adapter = new ArrayAdapter<String>(this,android.R.layout.simple_list_item_1,name_list);

        //setting adapter to listview
        lv_person.setAdapter(person_list_adapter);

    }
}

output:

android-sqlite-example
android-sqlite-example

In the above code the datas are displayed using ListView. If  you are a beginner and don’t know how to implement listview ,please go my another tutorial Android Listview Example which will explain how to implement listview in android.

About the author

Hi guys, i am the author of codesfor. I am a B.Tech graduate currently working as an App developer. Apart from job i am a blogger and a freelancer.

One Comment

Add a Comment

Your email address will not be published. Required fields are marked *