Android with SQLite Tutorial

0 0
Read Time:7 Minute, 20 Second

Android SQLite Setup

SQLite is like a digital filing cabinet for your Android app. It helps store, organize, and find information easily. It’s already inside your Android phone, so you don’t need to set it up separately.

Think of SQLiteOpenHelper as your helper to manage this filing cabinet. It helps create, update, and access the drawers (tables) where your information is stored.

For instance, imagine you want to keep track of names and ages. SQLite lets you put this info into the cabinet and later retrieve it whenever you need. This example will show how to save this data and then display it in the log, kind of like a report.

Once you’re comfortable with how SQLite works, you can learn to show this info in different parts of your app, like in a dropdown menu (spinner) or a list for users to see.

Example of android SQLite database

File: Contact.java

package example.codedixa.com.sqlitetutorial;  
  
public class Contact {  
    int _id;  
    String _name;  
    String _phone_number;  
    public Contact(){   }  
    public Contact(int id, String name, String _phone_number){  
        this._id = id;  
        this._name = name;  
        this._phone_number = _phone_number;  
    }  
  
    public Contact(String name, String _phone_number){  
        this._name = name;  
        this._phone_number = _phone_number;  
    }  
    public int getID(){  
        return this._id;  
    }  
  
    public void setID(int id){  
        this._id = id;  
    }  
  
    public String getName(){  
        return this._name;  
    }  
  
    public void setName(String name){  
        this._name = name;  
    }  
  
    public String getPhoneNumber(){  
        return this._phone_number;  
    }  
  
    public void setPhoneNumber(String phone_number){  
        this._phone_number = phone_number;  
    }  
} 

File: Contact.kt

package example.codedixa.com.sqlitetutorial

class Contact {
    var id: Int = 0
    var name: String = ""
    var phoneNumber: String = ""

    constructor()

    constructor(id: Int, name: String, phoneNumber: String) {
        this.id = id
        this.name = name
        this.phoneNumber = phoneNumber
    }

    constructor(name: String, phoneNumber: String) {
        this.name = name
        this.phoneNumber = phoneNumber
    }
}

File: DatabaseHandler.java

package example.codedixa.com.sqlitetutorial;  
  
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 {  
    private static final int DATABASE_VERSION = 1;  
    private static final String DATABASE_NAME = "contactsManager";  
    private static final String TABLE_CONTACTS = "contacts";  
    private static final String KEY_ID = "id";  
    private static final String KEY_NAME = "name";  
    private static final String KEY_PH_NO = "phone_number";  
  
    public DatabaseHandler(Context context) {  
        super(context, DATABASE_NAME, null, DATABASE_VERSION);  
        //3rd argument to be passed is CursorFactory instance  
    }  
  
    // Creating Tables  
    @Override  
    public void onCreate(SQLiteDatabase db) {  
        String CREATE_CONTACTS_TABLE = "CREATE TABLE " + TABLE_CONTACTS + "("  
                + KEY_ID + " INTEGER PRIMARY KEY," + KEY_NAME + " TEXT,"  
                + KEY_PH_NO + " TEXT" + ")";  
        db.execSQL(CREATE_CONTACTS_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_CONTACTS);  
  
        // Create tables again  
        onCreate(db);  
    }  
  
    // code to add the new contact  
    void addContact(Contact contact) {  
        SQLiteDatabase db = this.getWritableDatabase();  
  
        ContentValues values = new ContentValues();  
        values.put(KEY_NAME, contact.getName()); // Contact Name  
        values.put(KEY_PH_NO, contact.getPhoneNumber()); // Contact Phone  
  
        // Inserting Row  
        db.insert(TABLE_CONTACTS, null, values);  
        //2nd argument is String containing nullColumnHack  
        db.close(); // Closing database connection  
    }  
  
    // code to get the single contact  
    Contact getContact(int id) {  
        SQLiteDatabase db = this.getReadableDatabase();  
  
        Cursor cursor = db.query(TABLE_CONTACTS, new String[] { KEY_ID,  
                        KEY_NAME, KEY_PH_NO }, KEY_ID + "=?",  
                new String[] { String.valueOf(id) }, null, null, null, null);  
        if (cursor != null)  
            cursor.moveToFirst();  
  
        Contact contact = new Contact(Integer.parseInt(cursor.getString(0)),  
                cursor.getString(1), cursor.getString(2));  
        // return contact  
        return contact;  
    }  
  
    // code to get all contacts in a list view  
    public List<Contact> getAllContacts() {  
        List<Contact> contactList = new ArrayList<Contact>();  
        // Select All Query  
        String selectQuery = "SELECT  * FROM " + TABLE_CONTACTS;  
  
        SQLiteDatabase db = this.getWritableDatabase();  
        Cursor cursor = db.rawQuery(selectQuery, null);  
  
        // looping through all rows and adding to list  
        if (cursor.moveToFirst()) {  
            do {  
                Contact contact = new Contact();  
                contact.setID(Integer.parseInt(cursor.getString(0)));  
                contact.setName(cursor.getString(1));  
                contact.setPhoneNumber(cursor.getString(2));  
                // Adding contact to list  
                contactList.add(contact);  
            } while (cursor.moveToNext());  
        }  
  
        // return contact list  
        return contactList;  
    }  
  
    // code to update the single contact  
    public int updateContact(Contact contact) {  
        SQLiteDatabase db = this.getWritableDatabase();  
  
        ContentValues values = new ContentValues();  
        values.put(KEY_NAME, contact.getName());  
        values.put(KEY_PH_NO, contact.getPhoneNumber());  
  
        // updating row  
        return db.update(TABLE_CONTACTS, values, KEY_ID + " = ?",  
                new String[] { String.valueOf(contact.getID()) });  
    }  
  
    // Deleting single contact  
    public void deleteContact(Contact contact) {  
        SQLiteDatabase db = this.getWritableDatabase();  
        db.delete(TABLE_CONTACTS, KEY_ID + " = ?",  
                new String[] { String.valueOf(contact.getID()) });  
        db.close();  
    }  
  
    // Getting contacts Count  
    public int getContactsCount() {  
        String countQuery = "SELECT  * FROM " + TABLE_CONTACTS;  
        SQLiteDatabase db = this.getReadableDatabase();  
        Cursor cursor = db.rawQuery(countQuery, null);  
        cursor.close();  
  
        // return count  
        return cursor.getCount();  
    }  
  
}  

File: DatabaseHandler.kt

package example.codedixa.com.sqlitetutorial

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

class DatabaseHandler(context: Context) :
    SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {

    companion object {
        private const val DATABASE_VERSION = 1
        private const val DATABASE_NAME = "contactsManager"
        private const val TABLE_CONTACTS = "contacts"
        private const val KEY_ID = "id"
        private const val KEY_NAME = "name"
        private const val KEY_PH_NO = "phone_number"
    }

    override fun onCreate(db: SQLiteDatabase) {
        val CREATE_CONTACTS_TABLE = "CREATE TABLE $TABLE_CONTACTS(" +
                "$KEY_ID INTEGER PRIMARY KEY," +
                "$KEY_NAME TEXT," +
                "$KEY_PH_NO TEXT" +
                ")"
        db.execSQL(CREATE_CONTACTS_TABLE)
    }

    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        db.execSQL("DROP TABLE IF EXISTS $TABLE_CONTACTS")
        onCreate(db)
    }

    fun addContact(contact: Contact) {
        val db = this.writableDatabase
        val values = ContentValues()
        values.put(KEY_NAME, contact.name) // Contact Name
        values.put(KEY_PH_NO, contact.phoneNumber) // Contact Phone

        db.insert(TABLE_CONTACTS, null, values)
        db.close()
    }

    fun getContact(id: Int): Contact {
        val db = this.readableDatabase
        val cursor = db.query(
            TABLE_CONTACTS, arrayOf(KEY_ID, KEY_NAME, KEY_PH_NO),
            "$KEY_ID=?", arrayOf(id.toString()), null, null, null, null
        )
        cursor?.moveToFirst()

        val contact = Contact(
            cursor.getInt(0),
            cursor.getString(1),
            cursor.getString(2)
        )
        cursor.close()
        return contact
    }

    fun getAllContacts(): List<Contact> {
        val contactList: MutableList<Contact> = ArrayList()
        val selectQuery = "SELECT  * FROM $TABLE_CONTACTS"
        val db = this.writableDatabase
        val cursor: Cursor = db.rawQuery(selectQuery, null)

        if (cursor.moveToFirst()) {
            do {
                val contact = Contact()
                contact.id = cursor.getInt(0)
                contact.name = cursor.getString(1)
                contact.phoneNumber = cursor.getString(2)
                contactList.add(contact)
            } while (cursor.moveToNext())
        }
        cursor.close()
        return contactList
    }

    fun updateContact(contact: Contact): Int {
        val db = this.writableDatabase
        val values = ContentValues()
        values.put(KEY_NAME, contact.name)
        values.put(KEY_PH_NO, contact.phoneNumber)
        return db.update(
            TABLE_CONTACTS, values, "$KEY_ID = ?",
            arrayOf(contact.id.toString())
        )
    }

    fun deleteContact(contact: Contact) {
        val db = this.writableDatabase
        db.delete(TABLE_CONTACTS, "$KEY_ID = ?", arrayOf(contact.id.toString()))
        db.close()
    }

    fun getContactsCount(): Int {
        val countQuery = "SELECT  * FROM $TABLE_CONTACTS"
        val db = this.readableDatabase
        val cursor: Cursor = db.rawQuery(countQuery, null)
        val count = cursor.count
        cursor.close()
        return count
    }
}

File: MainActivity.java

package example.codedixa.com.sqlitetutorial;  
  
import androidx.appcompat.app.AppCompatActivity;
import android.os.Bundle;  
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);  
  
        // Inserting Contacts  
        Log.d("Insert: ", "Inserting ..");  
        db.addContact(new Contact("Ravi", "9100000000"));  
        db.addContact(new Contact("Srinivas", "9199999999"));  
        db.addContact(new Contact("Tommy", "9522222222"));  
        db.addContact(new Contact("Karthik", "9533333333"));  
  
        // Reading all contacts  
        Log.d("Reading: ", "Reading all contacts..");  
        List<Contact> contacts = db.getAllContacts();  
  
        for (Contact cn : contacts) {  
            String log = "Id: " + cn.getID() + " ,Name: " + cn.getName() + " ,Phone: " +  
                    cn.getPhoneNumber();  
            // Writing Contacts to log  
            Log.d("Name: ", log);  
        }  
    }  
}  

File: MainActivity.kt

package example.codedixa.com.sqlitetutorial

import android.os.Bundle
import android.util.Log
import androidx.appcompat.app.AppCompatActivity

class MainActivity : AppCompatActivity() {
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)
        val db = DatabaseHandler(this)

        // Inserting Contacts
        Log.d("Insert: ", "Inserting ..")
        db.addContact(Contact("Ravi", "9100000000"))
        db.addContact(Contact("Srinivas", "9199999999"))
        db.addContact(Contact("Tommy", "9522222222"))
        db.addContact(Contact("Karthik", "9533333333"))

        // Reading all contacts
        Log.d("Reading: ", "Reading all contacts..")
        val contacts: List<Contact> = db.getAllContacts()

        for (cn in contacts) {
            val log = "Id: " + cn.id + " ,Name: " + cn.name + " ,Phone: " +
                    cn.phoneNumber
            // Writing Contacts to log
            Log.d("Name: ", log)
        }
    }
}
Android SQLite by codedixa

How to view the data stored in sqlite in android studio?

Follow the following steps to view the database and its data stored in android sqlite:

  • Open File Explorer.
  • Go to data directory inside data directory.
  • Search for your application package name.
  • Inside your application package go to databases where you will found your database (contactsManager).
  • Save your database (contactsManager) anywhere you like.
  • Download any SqLite browser plugins or tool (in my case DB Browser for SQLite).
  • Launch DB Browser for SQLite and open your database (contactsManager).
  • Go to Browse Data -> select your table (contacts) you will see the data stored.
android sqlite
Happy
Happy
0 %
Sad
Sad
0 %
Excited
Excited
0 %
Sleepy
Sleepy
0 %
Angry
Angry
0 %
Surprise
Surprise
0 %
Verified by MonsterInsights