Android SQLite Tutorial With Example In Android Studio Step By Step

android sqlite

Hello, developers. Welcome to Android SQLite Tutorial with Example.

This Android SQLite tutorial will cover the simple operation of SQLite databases like insert and display data.

What is SQLite

SQLite is an in build database for every android device. In build means that you do not need to have any hosted server to store the database like MySQL. SQLite database is stored in android device(mobile and tablet) itself.

Because, it occupies very less memory space, SQLite works faster than other databases.

SQLite follows all the SQL standards and syntaxes. You need to write queries as per the SQL rules to insert, fetch, update or delete the data in SQLite.

Sometimes you want to give user an offline features, hence you need to store information in the SQLite Database.

Generally, data is stored in the text format in sqlite especially when the data is about user information.

You need to create tables and columns as per the general structure of any database.

After that, you can insert the data in terms of the rows.

SQLiteOpenHelper

SQLiteOpenHelper class is responsible for the management of database creation and version control management.

This class takes care of opening the database if it exists, creating it if it does not, and upgrading it as necessary. Transactions are used to make sure the database is always in a sensible state.

Following are the methods of this class.

1. close()

This method is used to close any particular sqlite database which is open.

You can use this method as below.

SQLiteOpenHelper openHelper;
openHelper.close();

2. getDatabaseName()

To get the name of the currently opened database, this method is useful.

It will return the name in string format.

String dbName = openHelper.getDatabaseName();

3. getReadableDatabase()

Create and/or open a database. This method will return an object of SQLiteDatabase class.

When the storage disk has no more space or when the database needs to be opened for Read-Only purpose, this method is used.

It will throw an SQLiteException if the database can not be opened by the system.

SQLiteDatabase db = openHelper.getReadableDatabase();

4. getWritableDatabase()

Create and/or open a database that will be used for reading and writing.

Once the database is opened successfully, it is cached by the system so that you can call this method anytime when you need o write new data into the database (Make sure to call close() when you no longer need the database.)

When user have not granted proper permissions or storage is full, this method may throw SQLiteException.

SQLiteDatabase db = openHelper.getWritableDatabase();

5. onConfigure(SQLiteDatabase db)

This method will be called when the database connection is being configured, to enable features such as write-ahead logging or foreign key support.

This method should modify the database, except to make some changes regarding the database connection configuration.

public void onConfigure (SQLiteDatabase db)

6. onCreate()

When the database is created for the very first time, this method is called.

Creation of tables and initial population of those tables are done at this time only.

public abstract void onCreate (SQLiteDatabase db)

7. onDowngrade()

To downgrade the database version, this need to be called. When calling this method, the requested version of the database must be older than the current version.

public void onDowngrade (SQLiteDatabase db, 
                int oldVersion, 
                int newVersion)

8. onOpen()

when database is opened, this method will be called.

This method is always called after the database connection is configured and after the database schema has been created, upgraded or downgraded as necessary.

public void onOpen (SQLiteDatabase db)

9. onUpgrade()

When you want to upgrade the database structure, you should use this method.

Use this method when you need to drop any existing table, to update any table, to add a new table or anything else which is responsible to upgrade the schema of the database.

public abstract void onUpgrade (SQLiteDatabase db, 
                int oldVersion, 
                int newVersion)

10. setIdleConnectionTimeout()

It defines the number of milliseconds up to which the database is allowed to be in the idle state.

Once the the database remains idle than specified time, it is closed and removed from the pool.

public void setIdleConnectionTimeout (long idleConnectionTimeoutMs)

11. setLookasideConfig()

Generally, this method needs to be called from the constructor of the subclass, before opening the database because lookaside memory configuration can only be changed when no connection is using it.

SQLite default settings will be used, if this method isn’t called. Use below line to disable lookaside

setLookasideConfig(0,0)

12. setOpenParams()

Sets configuration parameters that are used for opening SQLiteDatabase class.

It will throw an IllegalStateException if the database is already open.

SQLiteDatabase

This class is used to manage various task relating to database using it’s many in built methods.

SQLiteDatabase has methods to create, delete, execute SQL commands, and perform other common database management tasks.

If you are going to create more than one database in the single application, then make sure that names of the databases are unique.

Database names are not required to be unique if they are not created withing single application.

Below are the methods of this class.

1. beginTransaction()

It begins a transaction in EXCLUSIVE mode.

2. beginTransactionNonExclusive()

It Begins a transaction in IMMEDIATE mode.

3. compileStatement()

Compiles an SQL statement into a reusable pre-compiled statement object.

Use this method as below

compileStatement(String sql)

Pass sql statement as a parameter in string the format.

4. create()

When you want to create a new database, use this method.

It will also dedicate memory to the database.

create(SQLiteDatabase.CursorFactory factory)

5. delete()

This method is used to delete specific row from the database.

Use this method as below

delete(String table, String whereClause, String[] whereArgs)

Here, first parameter indicates the table from which row is needed to delete.

6. deleteDatabase()

This methods deletes a database with it’s journal file and other various files that are created by the database engine.

deleteDatabase(File file)

7. enableWriteAheadLogging()

In big enterprise applications, there are many threads running parallely, so if all these threads want to execute sql queries on the same database simultaneously, this method enables this feature.

8. disableWriteAheadLogging()

Feature of executing multiple sql queries (Create, retrieve , update, delete) on the same database simultaneously can be disabled with the use of this method.

9. getMaximumSize()

It returns a maximum number of memory size which can be allotted to a single database.

10. getPath()

It gives a path to the database file.

11. getVersion()

Current version of the database will be returned by this method.

12. getPageSize()

Returns the current database page size, in bytes.

13. isOpen()

This method returns a boolean value. If the current database is open then it will return true otherwise false.

14. isReadOnly()

If the database is opened as read only mode, then this method will return true.

15. needUpgrade(int newVersion)

Boolean vale will be returned by this method. It returns true if the new version code is greater than the current version code of the database.

Download Source Code For Android SQLite Tutorial

[sociallocker] Download Source Code [/sociallocker]

Follow all the below steps to create a simple sqlite database example.

Step 1: Create a new project in Android Studio.

Create a new standalone and fresh new android studio project.Make sure you have select empty activity while creating new project.

Step 2: Creating DatabaseHelper class

Create a new class named “DatabaseHelper” and all below source code

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

/**
 * Created by Parsania Hardik on 11/01/2016.
 */
public class DatabaseHelper extends SQLiteOpenHelper {

    public static String DATABASE_NAME = "student_database";
    private static final int DATABASE_VERSION = 1;
    private static final String TABLE_STUDENTS = "students";
    private static final String KEY_ID = "id";
    private static final String KEY_FIRSTNAME = "name";

    /*CREATE TABLE students ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, phone_number TEXT......);*/
   
    private static final String CREATE_TABLE_STUDENTS = "CREATE TABLE "
            + TABLE_STUDENTS + "(" + KEY_ID
            + " INTEGER PRIMARY KEY AUTOINCREMENT," + KEY_FIRSTNAME + " TEXT );";
   
    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);

        Log.d("table", CREATE_TABLE_STUDENTS);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
       db.execSQL(CREATE_TABLE_STUDENTS);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS '" + TABLE_STUDENTS + "'");
        onCreate(db);
    }

    public long addStudentDetail(String student) {
        SQLiteDatabase db = this.getWritableDatabase();
        // Creating content values
        ContentValues values = new ContentValues();
        values.put(KEY_FIRSTNAME, student);
       // insert row in students table
        long insert = db.insert(TABLE_STUDENTS, null, values);

        return insert;
    }

    public ArrayList<String> getAllStudentsList() {
        ArrayList<String> studentsArrayList = new ArrayList<String>();
        String name="";
        String selectQuery = "SELECT  * FROM " + TABLE_STUDENTS;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor c = db.rawQuery(selectQuery, null);
        // looping through all rows and adding to list
        if (c.moveToFirst()) {
            do {
                name = c.getString(c.getColumnIndex(KEY_FIRSTNAME));
               // adding to Students list
                studentsArrayList.add(name);
            } while (c.moveToNext());
            Log.d("array", studentsArrayList.toString());
        }
        return studentsArrayList;
    }
}

Step 3: Description of DatabaseHelper

Database name, Database version, table name and column names of tables are written as below.

    public static String DATABASE_NAME = "student_database";
    private static final int DATABASE_VERSION = 1;
    private static final String TABLE_STUDENTS = "students";
    private static final String KEY_ID = "id";
    private static final String KEY_FIRSTNAME = "name";

The value of the DATABASE_VERSION variable describes the version of the database. It’s value is 1 at the first time when you create an app.

Understanding the most important part of the SQLite Database

See the below source code

 private static final String CREATE_TABLE_STUDENTS = "CREATE TABLE "
            + TABLE_STUDENTS + "(" + KEY_ID
            + " INTEGER PRIMARY KEY AUTOINCREMENT," + KEY_FIRSTNAME + " TEXT );";

Above is the query to create a new table named “students”.

We need to write this type of query for each table we want to create.

In onCreate() method, we have written create statement for the table.

@Override
    public void onCreate(SQLiteDatabase db) {
       db.execSQL(CREATE_TABLE_STUDENTS);
}

If you want to make more than one table, then put above type of coding line for each table.

  • In onUpgrade() method, sqlite will deop already exist tables and then it will recreate all the tables.
  • onCreate() method is called within onUpgrade() method to create tables.
 @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS '" + TABLE_STUDENTS + "'");
        onCreate(db);
}

In following scenarios, you need to upgrade DATABASE_VERSION number in increment order.

  1. When you add, update or delete any column of any table in the whole database.
  2. If you update any column name of any table.
  3. After you add, update or delete any table.
  4. Update the table name and increase DATABASE_VERSION.
  • onUpgrade() method is called every time When you update the DATABASE_VERSION in the increment order.
  • addStudentDetail() method will add the name to the database.
  • getAllStudentsList() method will fetch all the values of names from database.

Step 5: Updating activity_main.xml

Put following code in activity_main.xml

<?xml version="1.0" encoding="utf-8"?>
<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:orientation="vertical"
    android:background="@color/colorAccent"
    tools:context="com.exampledemo.parsaniahardik.simplesqlitedemonuts.MainActivity">

    <TextView
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:gravity="center"
        android:textColor="#fff"
        android:textSize="20sp"
        android:layout_marginTop="10dp"
        android:text="Enter Name to store in SQLite" />

    <EditText
        android:layout_width="match_parent"
        android:layout_height="40dp"
        android:id="@+id/etname"
        android:background="#fff"
        android:layout_marginTop="20dp"
        android:layout_marginLeft="20dp"
        android:hint="Enter Name"/>

    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:id="@+id/btnstore"
        android:layout_marginLeft="20dp"
        android:layout_marginTop="10dp"
        android:text="Store"/>
    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:id="@+id/btnget"
        android:layout_marginLeft="20dp"
        android:layout_marginTop="10dp"
        android:text="Get All Names from SQLite"/>

    <TextView
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:id="@+id/tvnames"
        android:layout_marginLeft="10dp"
        android:textColor="#fff"
        android:textSize="20sp"
        android:layout_marginTop="10dp"
        android:text="Enter Name to store in SQLite" />

</LinearLayout>

One edittext, one textview and two buttons are defined in above code.

User will use edittext to enter the name of the student.

Step 6: Updating MainActivity.java

Add following  in MainActivity.java

import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;
import android.widget.Toast;
import java.util.ArrayList;

public class MainActivity extends AppCompatActivity {

    private Button btnStore, btnGetall;
    private EditText etname;
    private DatabaseHelper databaseHelper;
    private TextView tvnames;
    private ArrayList<String> arrayList;

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

        databaseHelper = new DatabaseHelper(this);
        tvnames = (TextView) findViewById(R.id.tvnames);

        btnStore = (Button) findViewById(R.id.btnstore);
        btnGetall = (Button) findViewById(R.id.btnget);
        etname = (EditText) findViewById(R.id.etname);

        btnStore.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                databaseHelper.addStudentDetail(etname.getText().toString());
                etname.setText("");
                Toast.makeText(MainActivity.this, "Stored Successfully!", Toast.LENGTH_SHORT).show();
            }
        });
        btnGetall.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                arrayList = databaseHelper.getAllStudentsList();
                tvnames.setText("");
                for (int i = 0; i < arrayList.size(); i++){
                     tvnames.setText(tvnames.getText().toString()+", "+arrayList.get(i));
                }
            }
        });
    }
}

When the user clicks on store button, the entered text is stored in the database as below.

 btnStore.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                databaseHelper.addStudentDetail(etname.getText().toString());
                etname.setText("");
                Toast.makeText(MainActivity.this, "Stored Successfully!", Toast.LENGTH_SHORT).show();
            }
 });

addStudentDeatils method is called here, which is present in the DatabaseHelper class.

This method defines the sql query to add the name in the sqlite database under the column “name”.

When the user clicks on the getall button, all the names are fetched from the database.

 btnGetall.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                arrayList = databaseHelper.getAllStudentsList();
                tvnames.setText("");
                for (int i = 0; i < arrayList.size(); i++){
                     tvnames.setText(tvnames.getText().toString()+", "+arrayList.get(i));
                }
            }
});

getAllStudentsList method gives us all the names of the students in the arraylist.

Above method is also present in the DatabaseHelper class.

Output

First, check the output of SQLite In Android Studio example, then we will develop it.

So that is all for SQLite in Android example.

Feel free to comment your queries and reviews in the comment section. Thank you šŸ™‚