Android SQLite Database

SQLite Database in Android. Learn how to create an offline database for android.

What is SQlite ?

Open Source database

Supports standard relational database features.

It  is embedded into every Android device.

If your app creates a database by default it is saved in the directory data/data/yourpackagename/databases/yourdatabasename.

Working with SQLiteOpenHelper and SQLiteDatabase.

SQLiteOpenHelper

To create and upgrade a database in your app, you need to create a subclass of  SQLiteOpenHelper which will provide you two abstract methods with a constructor where we need to specify the database version and database name.

onCreate()-This is where we need to write create table statements.

onUpgrade()-Called when we need to update the database schema.

SQLiteDatabase

Provides methods to insert(), update() , delete()  and even other methods like execSQL() which allows to execute SQL statements.

Working with ContentValues and Cursor.

ContentValues

It is predefined class specifically used for inserts and updates in database records. It accepts key/value pair where key represents the column name and value represents record of that column.

Cursor

It represents the result of a query.

Steps to create a database in Android

Step 1

activity_main.xml

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout 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:orientation="vertical"
    tools:context="eazy.mysqliteblog.MainActivity">

    <EditText
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:id="@+id/edcountryname"
        android:hint="Enter Country Name"/>
    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:id="@+id/addbtn"
        android:text="Add Country"></Button>

    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:id="@+id/displaybtn"
        android:text="Display All"></Button>

    <ListView
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:id="@+id/listcountry"></ListView>


</LinearLayout>

Step 2

MainActivity.java

package eazy.mysqliteblog;

import android.database.Cursor;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ListView;
import android.widget.Toast;

import java.util.ArrayList;

public class MainActivity extends AppCompatActivity {


    EditText edcountry;
    Button btnadd,displaybtn;

    ListView listView;
    ArrayList al=new ArrayList();
    MyDbHelper myDbHelper;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        myDbHelper=new MyDbHelper(MainActivity.this);
        edcountry= (EditText) findViewById(R.id.edcountryname);

        listView= (ListView) findViewById(R.id.listcountry);
        btnadd= (Button) findViewById(R.id.addbtn);
        displaybtn= (Button) findViewById(R.id.displaybtn);

        displaybtn.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {

                // call the getAllData method declared in MyDbHelper class its return type is cursor so we need to store
                // it in cursor
                Cursor c=myDbHelper.getAllData();

                //check whether the cursor has data or not

                if (c.getCount()>0) {

                    //go to the first row
                    if (c.moveToFirst()) {
                        do {
                            //fetch the first row data

                            String name = c.getString(1);
                            al.add(name);
                            // getString method takes column index as an arg we need to fetch countryname which is 1st column
                            // in database column index starts with a 0
                        } while (c.moveToNext());


                        listView.setAdapter(new ArrayAdapter(MainActivity.this, android.R.layout.simple_list_item_1, al));
                    }
                }





            }
        });
        btnadd.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {

                myDbHelper.insertData(edcountry.getText().toString());
                Toast.makeText(MainActivity.this, "Data Saved   ", Toast.LENGTH_SHORT).show();
            }
        });
    }
}

Step 3

MyDbHelper.java

package eazy.mysqliteblog;

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

/**
 * Created by Administrator on 06-06-2017.
 */

public class MyDbHelper extends SQLiteOpenHelper {
    public MyDbHelper(Context context) {

        super(context, "mydb", null, 1);
    }

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        // with the help of Sqlitedatabase object we just executed a normal create table statement
        sqLiteDatabase.execSQL("create table country(_id integer primary key autoincrement, name text)");

    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {


    }
    // to insert records in table you need to create a user defined function which will take arguments that needs
    // to be inserted in table
    public void insertData(String cname){

        //create Contentvalues to insert the records
        ContentValues contentValues=new ContentValues();

        //it has put method that accepts two params column name and the value
        contentValues.put("name",cname);

        //to write the above data in database create an object of sqlitedatabase and open the database in writable mode
        SQLiteDatabase db=this.getWritableDatabase();

        db.insert("country",null,contentValues);


    }

    //to fetch all inserted records create a user defined method with cursor as a return type
    public Cursor getAllData(){

        //to read the  data from  database create an object of sqlitedatabase and open the database in readable mode
        SQLiteDatabase db=this.getReadableDatabase();
        // intialize cursor with the query
        Cursor c=db.rawQuery("select * from country",null);

        return c;

    }


}

 

Final Output

 

[gallery ids="35,36" type="rectangular" link="file"]