Android SQLite Tutorial

In this tutorial you will develop a simple to-do-list native app using SQLite for android.

Download the required software packages

Download and install Android Studio and Android SDK.

Android Studio + SDK – http://developer.android.com/sdk/index.html

Setting up your development environment

Open your Android Studio and choose Start a new Android Studio project.

Start a new Android Studio project

Start a new Android Studio project

Enter your custom Application name, Company Domain and select Project location. Click Next.

Configure your new project.

Configure your new project.

Select Phone and Tablet. Make sure API 15 selected. Click Next.

Configure your new project.

Configure your new project.

Select Empty Activity and click Next.

Add an activity to Mobile.

Add an activity to Mobile.

Click Finish.

Customize the activity.

Customize the activity.

Create a new java class. Right click on my.intellij.androidsqlite package > New >Activity > Java Class.

Create a new java class.

Create a new java class.

Name it as Task and click Ok.

Create New Class

Create New Class

Then replace with this code in Task.java. Set your Task class with all getter and setter methods to maintain single task as an object.

package my.intellij.androidsqlite;

public class Task {

    //private variables
    int taskId;
    String name;
    String description;

    // Empty constructor
    public Task(){

    }
    // constructor
    public Task(int taskId, String name, String description){
        this.taskId = taskId;
        this.name = name;
        this.description = description;
    }

    // constructor
    public Task(String name, String description){
        this.name = name;
        this.description = description;
    }
    // getting taskId
    public int getTaskId(){
        return this.taskId;
    }

    // setting taskId
    public void setID(int taskId){
        this.taskId = taskId;
    }

    // getting name
    public String getName(){
        return this.name;
    }

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

    // getting description
    public String getDescription(){
        return this.description;
    }

    // setting description
    public void setDescription(String description){
        this.description = description;
    }
}

Create a new java class. Right click on my.intellij.androidsqlite package > New >Activity > Java Class.

Create a new java class.

Create a new java class.

Name it as DatabaseHandler and click Ok.

Create New Class

Create New Class

Write DatabaseHandler class to handle all database CRUD(Create, Read, Update and Delete) operations.

package my.intellij.androidsqlite;

import android.content.ContentValues;
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.List;

public class DatabaseHandler extends SQLiteOpenHelper {
    // All Static variables
    // Database Version
    private static final int DATABASE_VERSION = 1;

    // Database Name
    private static final String DATABASE_NAME = "tasksManager";

    // Tasks table name
    private static final String TABLE_TASKS = "tasks";

    // Tasks Table Columns names
    private static final String KEY_TASK_ID = "taskId";
    private static final String KEY_NAME = "name";
    private static final String KEY_DESCRIPTION = "description";

    public DatabaseHandler(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    // Creating Tables
    @Override
    public void onCreate(SQLiteDatabase db) {
        String CREATE_TASKS_TABLE = "CREATE TABLE " + TABLE_TASKS + "("
                + KEY_TASK_ID + " INTEGER PRIMARY KEY," + KEY_NAME + " TEXT,"
                + KEY_DESCRIPTION + " TEXT" + ")";
        db.execSQL(CREATE_TASKS_TABLE);
    }

    // Upgrading database
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Drop older table if existed
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_TASKS);

        // Create tables again
        onCreate(db);
    }

    /**
     * All CRUD(Create, Read, Update, Delete) Operations
     */

    // Adding new task
    void addTask(Task task) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_NAME, task.getName()); // Task Name
        values.put(KEY_DESCRIPTION, task.getDescription()); // Task Description

        // Inserting Row
        db.insert(TABLE_TASKS, null, values);
        db.close(); // Closing database connection
    }

    // Getting single task
    Task getTask(int taskId) {
        SQLiteDatabase db = this.getReadableDatabase();

        Cursor cursor = db.query(TABLE_TASKS, new String[] { KEY_TASK_ID,
                        KEY_NAME, KEY_DESCRIPTION }, KEY_TASK_ID + "=?",
                new String[] { String.valueOf(taskId) }, null, null, null, null);
        if (cursor != null)
            cursor.moveToFirst();

        Task task = new Task(Integer.parseInt(cursor.getString(0)),
                cursor.getString(1), cursor.getString(2));
        // return task
        return task;
    }

    // Getting All Tasks
    public List<Task> getAllTasks() {
        List<Task> taskList = new ArrayList<Task>();
        // Select All Query
        String selectQuery = "SELECT  * FROM " + TABLE_TASKS;

        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);

        // looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
                Task task = new Task();
                task.setID(Integer.parseInt(cursor.getString(0)));
                task.setName(cursor.getString(1));
                task.setDescription(cursor.getString(2));
                // Adding task to list
                taskList.add(task);
            } while (cursor.moveToNext());
        }

        // return task list
        return taskList;
    }

    // Updating single task
    public int updateTask(Task task) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_NAME, task.getName());
        values.put(KEY_DESCRIPTION, task.getDescription());

        // updating row
        return db.update(TABLE_TASKS, values, KEY_TASK_ID + " = ?",
                new String[] { String.valueOf(task.getTaskId()) });
    }

    // Deleting single task
    public void deleteTask(Task task) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE_TASKS, KEY_TASK_ID + " = ?",
                new String[] { String.valueOf(task.getTaskId()) });
        db.close();
    }


    // Getting tasks Count
    public int getTasksCount() {
        String countQuery = "SELECT  * FROM " + TABLE_TASKS;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);
        cursor.close();

        // return count
        return cursor.getCount();
    }
}

Open MainActivity and replace with this code.

package my.intellij.androidsqlite;

import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.util.Log;

import java.util.List;

public class MainActivity extends AppCompatActivity {

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

        DatabaseHandler db = new DatabaseHandler(this);

        /**
         * CRUD Operations
         * */
        // Inserting Tasks
        Log.d("Insert: ", "Inserting ..");
        db.addTask(new Task("Breakfast", "Nasi Lemak"));
        db.addTask(new Task("Lunch", "Nasi Ayam"));
        db.addTask(new Task("Dinner", "MCD"));

        // Reading all tasks
        Log.d("Reading: ", "Reading all tasks..");
        List<Task> tasks = db.getAllTasks();

        for (Task cn : tasks) {
            String log = "TaskId: "+cn.getTaskId()+" ,Name: " + cn.getName() + " ,Description: " + cn.getDescription();
            // Writing Tasks to log
            Log.d("Name: ", log);
        }
    }
}

Build and run the app. The output will be show up like below.

Logcat output.

Logcat output.