Android provides several ways to store user and app data. SQLite is one way of storing user data. SQLite is a very light weight database which comes with Android OS. In this tutorial I’ll be discussing how to write classes to handle all SQLite operations.

Download Code

In this tutorial I am taking an example of storing user contacts in SQLite database. I am using a table called Contacts to store user contacts. This table contains three columns id (INT), name (TEXT), phone_number(TEXT).

Contacts Table Structure

Sqlite contacts table

Writing Contact Class

Before you go further you need to write your Contact class with all getter and setter methods to maintain single contact as an object.

package com.androidhive.androidsqlite; 

public class Contact {
	
	//private variables
	int _id;
	String _name;
	String _phone_number;
	
	// Empty constructor
	public Contact(){
		
	}
	// constructor
	public Contact(int id, String name, String _phone_number){
		this._id = id;
		this._name = name;
		this._phone_number = _phone_number;
	}
	
	// constructor
	public Contact(String name, String _phone_number){
		this._name = name;
		this._phone_number = _phone_number;
	}
	// getting ID
	public int getID(){
		return this._id;
	}
	
	// setting id
	public void setID(int id){
		this._id = id;
	}
	
	// getting name
	public String getName(){
		return this._name;
	}
	
	// setting name
	public void setName(String name){
		this._name = name;
	}
	
	// getting phone number
	public String getPhoneNumber(){
		return this._phone_number;
	}
	
	// setting phone number
	public void setPhoneNumber(String phone_number){
		this._phone_number = phone_number;
	}
}

Writing SQLite Database Handler Class

  We need to write our own class to handle all database CRUD(Create, Read, Update and Delete) operations.

1. Create a new project by going to File ⇒ New Android Project.
2. Once the project is created, create a new class in your project src directory and name it as DatabaseHandler.java ( Right Click on src/package ⇒ New ⇒ Class)
3. Now extend your DatabaseHandler.java class from SQLiteOpenHelper.

public class DatabaseHandler extends SQLiteOpenHelper {

4. After extending your class from SQLiteOpenHelper you need to override two methods onCreate() and onUpgrage()
onCreate() – These is where we need to write create table statements. This is called when database is created.
onUpgrade() – This method is called when database is upgraded like modifying the table structure, adding constraints to database etc.,

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 = "contactsManager";

	// Contacts table name
	private static final String TABLE_CONTACTS = "contacts";

	// Contacts Table Columns names
	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);
	}

	// 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);
	}

⇒All CRUD Operations (Create, Read, Update and Delete)

Now we need to write methods for handling all database read and write operations. Here we are implementing following methods for our contacts table.

// Adding new contact
public void addContact(Contact contact) {}

// Getting single contact
public Contact getContact(int id) {}

// Getting All Contacts
public List<Contact> getAllContacts() {}

// Getting contacts Count
public int getContactsCount() {}
// Updating single contact
public int updateContact(Contact contact) {}

// Deleting single contact
public void deleteContact(Contact contact) {}

⇒Inserting new Record

The addContact() method accepts Contact object as parameter. We need to build ContentValues parameters using Contact object. Once we inserted data in database we need to close the database connection.

        // Adding new contact
	public 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 Number

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

⇒Reading Row(s)

The following method getContact() will read single contact row. It accepts id as parameter and will return the matched row from the database.

        // Getting single contact
	public 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;
	}

getAllContacts() will return all contacts from database in array list format of Contact class type. You need to write a for loop to go through each contact.

        // Getting All Contacts
	 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;
	}

getContactsCount() will return total number of contacts in SQLite database.

// 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();
	}

⇒Updating Record

updateContact() will update single contact in database. This method accepts Contact class object as parameter.

        // Updating 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 Record

deleteContact() will delete single contact from database.

        // 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();
	}

Complete DatabaseHandler.java Code:

package com.androidhive.androidsqlite;

import java.util.ArrayList;
import java.util.List;

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

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 = "contactsManager";

	// Contacts table name
	private static final String TABLE_CONTACTS = "contacts";

	// Contacts Table Columns names
	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);
	}

	// 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);
	}

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

	// Adding 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);
		db.close(); // Closing database connection
	}

	// Getting 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;
	}
	
	// Getting All Contacts
	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;
	}

	// Updating 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();
	}

}

Usage:

package com.androidhive.androidsqlite;

import java.util.List;

import android.app.Activity;
import android.os.Bundle;
import android.util.Log;
import android.widget.TextView;

public class AndroidSQLiteTutorialActivity extends Activity {
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);
        
        DatabaseHandler db = new DatabaseHandler(this);
        
        /**
         * CRUD Operations
         * */
        // 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);
	}
    }
}

Android Log Cat Report:

I am writing output to Log report. You can see your log report by going to Windows ⇒ Show View ⇒ Other.. ⇒ Android ⇒ Log Cat.

Android Log Cat
Android Log Cat
Android Output log report

What’s Next?

If you feel comfortable with SQLite database, check out Android SQLite Database with Multiple Tables which explains how to handle SQLite when your app needs more than one table.

Ravi is hardcore Android programmer and Android programming has been his passion since he compiled his first hello-world program. Solving real problems of Android developers through tutorials has always been interesting part for him.
  • Pingback: Android SQLite Database Tutorial | AndroidHive | Tutorials, Games, Apps, Tips | Android Development for all | Scoop.it()

  • Pingback: Android SQLite Database Tutorial | Database | Syngu()

  • Pingback: Get acces to a login system, Android | PHP Developer Resource()

  • Pingback: Android | Pearltrees()

  • Pingback: Threading()

  • Arise Rayamangalam

    A problem with this type of method is if you want to add data say 100 nos then your code look shabby and it will be very difficult tot enter the data. You can use another method by which we can enter the data using a Database Manager and later attach it to the Application. A simple tutorial with source code is given in the following link
    http://android-helper4u.blogspot.com/2013/03/d-databse-and-spinner-tutorial.html

  • Guest

    Could I use this database to store dynamic fields such as latitude and longitude (for my app) which gets refreshed every 10 sec. Is there a way to retrieve this data on a web server ?

  • Your tutorial is simple and clear.

    I tried Vogella’s tutorial about SQLite but I get lost with it, although he is a good tutor in other parts, but you beated him in SQLite part. 🙂

    Thank you

    • Nirmal

      you r right ,,he will give Some half half code,,so we confuse that data is going where

      • madV

        just awsome man… 🙂

  • anjali

    good.

  • anjali

    it is good. but i have one doubt. how to creae multiple tables

  • Good example But how to add value like Email adress?

  • Giannis

    Hi i get an error: table contacts has no column named phone, i run it in API level 15 i am really stack and need ASAP help..please if anyone know asnwer me here or in my email: gveron@gmail.com

    • Did you make sure that you are creating that column in your table and is the exact name(spelling, capitals, etc) I run into a similar problem but mine was resolved by adding onCreate before manipulating the data, that way I make sure that if for any reason the table does not exists it will be crated first and I will not get any errors.

    • Garry Hickey

      having same problem, says the column doesnt exist, why?

  • Akheloes

    Just a benign thought : isn’t that the whole purpose of a database ?

  • me

    Can you give an example in Usage of how updateContact works

  • Noah

    Nice! However you can simplify the somewhat awkward

    if (cursor.moveToFirst()) { do … while (cursor.moveToNext()); }

    with a much cleaner

    while (cursor.moveToNext()) { … }

    Since the query / rawQuery moves the cursor before the first entry.

  • Shahil Modan

    Nice Tutorial i am new in android its help me a lot ….thank you !!!!!

  • kushi

    helloo sir,

    it has helped me very much thank you.but i do have got a problem please resolve my problem..

    we r getting a o/p in logcat but i want to display data in listview that what ever the data stored in sqlite database? please help me
    thanks in advance

  • A guest

    what is path of DB? where can i found my SQLite file here?

    • MAJK

      Yeah, what is the path of database? Please answer as soon as possible.

      BTW. Very useful tutorial !

  • Tonyoh87

    Hey great tutorial.

    I imported the project but I get the following errors:

    [2013-05-05 17:34:18 – DemoActivity] Application package ‘AndroidManifest.xml’ must have a minimum of 2 segments.

    [2013-05-05 17:37:58 – Database] Error in an XML file: aborting build.

    [2013-05-05 18:18:19 – Database] W/ResourceType( 8832): Bad XML block: header size 92 or total size 0 is larger than data size 0

    [2013-05-05 18:18:19 – Database] C:UsersTonyoh87workspaceDatabasereslayoutactivity_main.xml:6: error: Error: No resource found that matches the given name (at ‘text’ with value ‘@string/hello_world’).

    [2013-05-05 18:18:19 – Database] C:UsersTonyoh87workspaceDatabaseresmenuactivity_main.xml:2: error: Error: No resource found that matches the given name (at ‘title’ with value ‘@string/menu_settings’).

    [2013-05-05 18:19:28 – Database] W/ResourceType( 3340): Bad XML block: header size 119 or total size 0 is larger than data size 0

    [2013-05-05 18:19:28 – Database] C:UsersTonyoh87workspaceDatabasereslayoutactivity_main.xml:6: error: Error: No resource found that matches the given name (at ‘text’ with value ‘@string/hello_world’).

    [2013-05-05 18:19:28 – Database] C:UsersTonyoh87workspaceDatabaseresmenuactivity_main.xml:2: error: Error: No resource found that matches the given name (at ‘title’ with value ‘@string/menu_settings’).

    [2013-05-05 18:29:12 – AndroidSQLiteTutorial] Android requires compiler compliance level 5.0 or 6.0. Found ‘1.7’ instead. Please use Android Tools > Fix Project Properties.

    [2013-05-05 18:30:24 – AndroidSQLiteTutorial] Android requires compiler compliance level 5.0 or 6.0. Found ‘1.7’ instead. Please use Android Tools > Fix Project Properties.

    [2013-05-05 18:31:51 – AndroidSQLiteTutorial] Android requires compiler compliance level 5.0 or 6.0. Found ‘1.7’ instead. Please use Android Tools > Fix Project Properties.

    [2013-05-05 18:34:04 – AndroidSQLiteTutorial] Android requires compiler compliance level 5.0 or 6.0. Found ‘1.7’ instead. Please use Android Tools > Fix Project Properties.

    [2013-05-05 18:38:10 – AndroidSQLiteTutorial] Android requires compiler compliance level 5.0 or 6.0. Found ‘1.7’ instead. Please use Android Tools > Fix Project Properties.

    Can you help me about this ?

    • nida

      how to use “getAllContacts()” to display all user contacts in listview i m facing difficulty to do this

  • Tonyoh87

    fixed, please ignore my request 🙂

  • Tonyoh87

    stupid question but how do we use the database ? I’m looking to use it for making a language application

  • S

    how value in id Column are generated and inserted

  • Deepu S.A

    This is what a tutorial should be. I have nothing more to say. just perfect!.

  • Ali Fattahi

    Hello

    Thank you for your useful post , i have a question aboud sqlite

    if I already have a sqlite database file how can I use in my project ?

    Best Regards
    Ali

  • Guest

    a small correction : onUpgrage() shoudl be onUpgrade()

  • Shan

    Great post ! A small correction : onUpgrage() shoudl be onUpgrade()

    • vsdf

      Shoudl Should be Should

      • Guest

        Thanks for pointing that out too! 🙂

      • Shan

        Thanks for pointing that out too! It should be ‘should’ not ‘Should’ 🙂

  • yasith

    This helped me 🙂 Thanks for writing the post.

  • Tejas

    does any body know about maximum data base file size in(1.2 mb ) Assets folder.
    will it cause any problem. What if it increases more than 1 mb. I know how to implement this. but is there Any disadvantages of using it.

  • Sanjeev

    Expecting content provider tutorial as good as this one soon sir…

  • anil

    Thanks for this tutorial ……………..

  • anil

    plz tell me How to display data from sqlite in textview.

  • majid

    thanks for sample and good exmple.

  • Allen

    Ravi,

    Your getContact(int id) method will crash if there are no rows for the specified id. The returned Cursor will never be null, but it could be empty. Cursor.moveToFirst() returns false if the Cursor is empty, so the if condition should be:

    if (Cursor.moveToFirst()) {}

    instead of:

    if (cursor != null) {}

    Thanks for the tutorial, Ravi!

  • Milana

    Hi Ravi,

    can you please help me on coding of how to tag a photo which is in image view

  • Sanjay Mangroliya

    Very Nice Example………..

  • Zach

    Best tutorial for this I have seen, thanks!

  • Sandeep Saini

    how to update the table with modified values…..????
    and where to pass the ID…..??

  • Sadegh ghanbari

    Nice example ,thank you.
    http://www.AndroidSoftware.ir

  • Ryl

    Hello
    I am new with Android development..may i Know exactly how to write the output to textview ?

    • PrakashK Gowin

      Use Edittext attribute in your .xml file

      • ryl

        Solution by Ravi in previous comments
        // setting name in textview

        TextView txtName = (TextView) findViewById(R.id.txtName);

        // display name
        txtName.setText(person.getName());

        Do you knw how to share this contact database detail in another activity?

  • ryl

    Hi Ravi, I would like to ask how can i share the contact database details in another activities?

  • PrakashK Gowin

    Hi Ravi, Could You plz update us with some Apache Cordova (Phone Gap)Examples..

  • Saurabh Singh

    nice example of data base–
    but when i am doing some changes. on this code……..
    like giving data from EditText
    I am heaving a meney problem
    …………………….

  • polash

    Nice Tutorial……….. 🙂

  • adnan

    hi, good tutorial but i have got a problem. i am just adding and reading data but when i run it event log doesn’t show something and emulator shows “Closed un expectedly” (i made some xml that why m telling about emulator).

  • Moorthy

    hi ravi.

    nice tutorial,

    wondering how to get Id of the record. am getting error

  • Thiago Borges

    It doesn’t work for me. I’m getting:

    java.lang.RuntimeException: Unable to start activity ComponentInfo{com.androidhive.androidsqlite/com.androidhive.androidsqlite.AndroidSQLiteTutorialActivity}: java.lang.IllegalStateException: attempt to re-open an already-closed object: SQLiteDatabase: /data/data/com.androidhive.androidsqlite/databases/contactsManager

  • android fan

    good tutorial… 🙂

  • Akhil

    Hi…Can we use on Database Handler class for more than one table

  • Joseph

    Nice tutorial, Only have a question: Where’s saved the SqLite db ? I want to take out for processing

  • syang

    How are the “ids” generated?

  • Rowan

    Fantastic tutorial. Worked perfectly.

  • Dan

    Is it possible to view the tables in the database?

    • Jake

      yes it is. Download SQLite plug-in from Mozilla Firefox browser click tools>there you get the SQLite Manager. But download first. Thanks.

  • volume_8091

    And how can we save the table in a different file so that the user could save changes ?

  • raju

    BRO Hats off for ur work, really its superb……..
    the coding snippets and the way u explain the concepts in such an easy way and to the point its really admirable…………….

  • madV

    Awesome tut man.. 🙂

  • Nilima Nandagavali

    Thanks for this…..
    Good Example!!!! :-))))

    • ASH

      i got this error in handler lass .how we can fix it?

  • ASH

    Multiple markers at this line
    – Contact cannot be resolved to a type
    – Contact cannot be resolved to a type
    – No enclosing instance of type contact is accessible. Must qualify the allocation with an enclosing instance
    of type contact (e.g. x.new A() where x is an instance of contact).

    HOW WE CAN OVERCOME THIS ERROR

  • Govind Rao

    Hi Sir,

    i have one table like Team (Team_ID primary key and Team_Name UNIQUE)
    when i start the my application i will work fine … first time … and i inserted value like {1, ‘TeamA’} {2,’TeamB’} the i close my application then .. again i try to insert same values … i am getting like”Error insertionting Team_Name = teamB”.
    please tell me the how cai solve the problem.

    • PrakashK Gowin

      You cannot insert the same values again, because you have created the column Team_ID as primary. So You need to give some other values for further insertion

  • arash ataafarin

    Just For More Clarification for newbie programmers,you should copy android_login_api folder in c:/wamp/www or c:/xamp/htdoc
    And if you use port 8080 to access xamp,wamp you should change links to :
    loginURL = “http://10.0.2.2:8080/android_login_api/”;
    registerURL = “http://10.0.2.2:8080/android_login_api/”;

  • Parth Sharma

    I did this:

    Contact contact = db.getContact(i.getExtras().getInt(“id”));

    db.deleteContact(contact);

    But still its not deleting anything but raising nullpointerexception(npe)

  • gds

    Thanks man…. was hitting my head in the walls to get a hand on Android databases ….. this was the best tutorial of all that came up on searching.

    Thanks again
    gds

  • yogesh

    Its nice tutorial.

    Using this i can i add and delete records in db but not able to update records.

    So please reply for the same as early as possible for updating record using this tutorial.

    Thanks in advance.

  • Techy

    Its worth read article. Another blog also posted the same Topic with breaking everything in parts. Getting started with DB in android

  • Lez-J

    Hello my friend! when I try to execute your getAllContacts() method I have an outOfMemory exception due to an infinite loop! but for people who have the same problem here is my way too fix it:

    public ArrayList getListContacts(SQLiteDatabase db){

    ArrayList listeContacts = new ArrayList();

    String query = “SELECT * FROM ” + TABLE_CONTACT;

    //SQLiteDatabase db = this.getWritableDatabase();

    Cursor cursor = db.rawQuery(query, null);

    cursor.moveToFirst();

    for(int i=0;i<cursor.getCount();i++){

    Contact contact = new Contact();

    contact.setID(Integer.parseInt(cursor.getString(0)));

    banque.setName(cursor.getString(1));

    banque.setPhoneNumber(cursor.getString(2));

    listeContacts.add(contact);

    cursor.moveToNext();

    }

    cursor.close();

    return listeContacts;

    }

  • Lez-J

    sory for the previous post it’s about a project that I’m doing. but according with the object of this tutorial

    and sorry for my bad english I’m a french speaker
    public ArrayList getListContacts(SQLiteDatabase db){

    ArrayList listeContacts = new ArrayList();

    String query = “SELECT * FROM ” + TABLE_CONTACT;

    //SQLiteDatabase db = this.getWritableDatabase();

    Cursor cursor = db.rawQuery(query, null);

    cursor.moveToFirst();

    for(int i=0;i<cursor.getCount();i++){

    Contact contact = new Contact();

    contact.setID(Integer.parseInt(cursor.getString(0)));

    contact.setName(cursor.getString(1));

    contact.setPhoneNumber(cursor.getString(2));

    listeContacts.add(contact);

    cursor.moveToNext();

    }
    cursor.close();

    return listeContacts;

    }

  • radha

    i need login activity with sqlite and sessions..can you please provide me link or any information

  • Amjad Mansour

    thank you very much working

  • ilksen

    I wanted to use this sample in my project. https://play.google.com/store/apps/details?id=izasoft.kurumsal.uygulama maybe you can use Xml Source an alternative… Thanks your tutorial.

  • Q: how big a table can you put in an Android cellphone? I have a 10GB table, and I am weary of starting down this road, if queries will take minutes instead of seconds.. Thanks for posting. Any advice appreciated!

    Jim Pruett
    Wikispeedia.org

    • If you have larger database, you should not keep it in the device. You have to maintain a server and make API calls from device and get the data.

    • Raja

      if your data 10gb put data on server side not android phone

    • Sasuke Uchia

      How did you make your table this big anyway. What it contains. There is no way you can create a table that big, if you are not inserting special data type like Image, audio, Video…etc.
      Anyway even if you will able to put it in your mobile phone. Do you know how much time it will take to search and load the data. Even i think a computer may start hanging.
      Anyway Good luck with that……(Why i suppose to care)

  • Harry May

    I like this totorial, you explain it cleary and simple, thanks !!!

  • Hhayf

    Hello.If someone could help me would be great.I installed this program,no errors,everything runs normal,but after I fill in a form and press “add user” nothing happens.Can someone help me point the problem?

    • A Friend from hiddle leaf

      Well, you can’t just copy and use this class. You have to modified it according to your need.
      Give me all the details about your layout what you want to save i will post a custom class for you.

  • Itachi Uchia

    Nice Tutorials

  • Sasuke Uchia

    Am i suppose to care. I am an avenger and i will avenge my clan

  • NarutoUzumaki of hidden leaf

    Don’t you think you should use readable database to read all the contacts.
    In getAllContacts

    SQLiteDatabase db = this.getWritableDatabase();

  • NarutoUzumaki of hidden leaf

    And what about the update query what will it update

  • Evgeniy Safronov

    // Empty constructor
    // setting id
    etc.
    Good tutorial but absolutely useless comments.

    • We are Titans….

      You have disrespected me, Now you will face the wrath of titans

    • Itachi Uchia

      Amaterasu – Burn in hell

    • Evgeniy Safronov

      Wh.. why? You don’t agree that this is a good tutorial? 0_o

      • Tobi

        Because Tobi is a good boy.

  • Yan Philippe

    Hi, I don’t knowhow to simply delete a contact, can please someone help me?

    • NarutoUzumaki of hidden leaf

      //tablename is your tablename from which you want to delete
      //KEY_ID is the column name
      //keyid is the value
      //Example you want to delete KEY_ID 20. (And remember you can also use any column you

      //want in place of KEY_ID).
      //Then keyid is 20 for your query

      SQLiteDatabase db = getWritableDatabase();
      String deleteQuery = “DELETE FROM ” + tablename +” WHERE “+KEY_ID+” =?”;
      db.execSQL(deleteQuery, new String[]{String.valueOf(keyId)});
      db.close();

      • suraj

        its not working and also what about updating?

        • NarutoUzumaki of hidden leaf

          Working perfectly in my app.
          By working what do you mean – giving Error, Exception, or wrong result.
          In wrong result case i think you might entering wrong KEY_ID. KEY_ID is unique, and used to delete a specific contact.

        • Tobi

          Load the data you want to update in an ArrayList of String then pass it to along with a userName ( any uniquely identified attribute ) to update a particular contact.
          Let say, you want to update user name, first name, middle name, last name, email id,mobile number, sex, and date of birth –
          Use following code –
          public int updateUser(String userName, ArrayList dataArray)
          {
          SQLiteDatabase db = this.getWritableDatabase();

          ContentValues values=new ContentValues();

          String newUserName = dataArray.get(0);

          values.put(KEY_USER_NAME, dataArray.get(0));

          values.put(KEY_FIRST_NAME, dataArray.get(1));

          values.put(KEY_MIDDLE_NAME, dataArray.get(2));

          values.put(KEY_LAST_NAME, dataArray.get(3));

          values.put(KEY_EMAIL_ID, dataArray.get(4));

          values.put(KEY_MOBILE_NUMBER, dataArray.get(5));

          values.put(KEY_SEX, dataArray.get(6));

          values.put(KEY_DATE_OF_BIRTH, dataArray.get(7));

          int index = db.update(TABLE_NAME, values , KEY_USER_NAME+”= ?”,new String[] {String.valueOf(userName)} );

          db.close();
          return index;

          }

  • amit

    its xml

  • Jon

    I am writing an app that queries an already created database (read only).
    How do I connect to the database via its file?
    All examples I have seen create the database on the fly.

    • Have you found the solution? I’ve the same problem 🙂

      • Tobi

        Hi there

        Solution Founded

        My Boy Itachi found it.

        • jit

          so please tell us that solution..we want to access a already created database for readonly purpose..example-to make a contacts app,which show results on inserting names in searchbox..

          • Tobi

            Try this code :

            public class DataBaseHelper extends SQLiteOpenHelper {
            private Context mycontext;

            //private String DB_PATH = mycontext.getApplicationContext().getPackageName()+”/databases/”;
            private static String DB_NAME = “(datbasename).sqlite”;//the extension may be .sqlite or .db
            public SQLiteDatabase myDataBase;
            /*private String DB_PATH = “/data/data/”
            + mycontext.getApplicationContext().getPackageName()
            + “/databases/”;*/

            public DataBaseHelper(Context context) throws IOException {
            super(context,DB_NAME,null,1);
            this.mycontext=context;
            boolean dbexist = checkdatabase();
            if (dbexist) {
            //System.out.println(“Database exists”);
            opendatabase();
            } else {
            System.out.println(“Database doesn’t exist”);
            createdatabase();
            }
            }

            public void createdatabase() throws IOException {
            boolean dbexist = checkdatabase();
            if(dbexist) {
            //System.out.println(” Database exists.”);
            } else {
            this.getReadableDatabase();
            try {
            copydatabase();
            } catch(IOException e) {
            throw new Error(“Error copying database”);
            }
            }
            }

            private boolean checkdatabase() {
            //SQLiteDatabase checkdb = null;
            boolean checkdb = false;
            try {
            String myPath = DB_PATH + DB_NAME;
            File dbfile = new File(myPath);
            //checkdb = SQLiteDatabase.openDatabase(myPath,null,SQLiteDatabase.OPEN_READWRITE);
            checkdb = dbfile.exists();
            } catch(SQLiteException e) {
            System.out.println(“Database doesn’t exist”);
            }
            return checkdb;
            }

            private void copydatabase() throws IOException {
            //Open your local db as the input stream
            InputStream myinput = mycontext.getAssets().open(DB_NAME);

            // Path to the just created empty db
            String outfilename = DB_PATH + DB_NAME;

            //Open the empty db as the output stream
            OutputStream myoutput = new FileOutputStream(“/data/data/(packagename)/databases /(datbasename).sqlite”);

            // transfer byte to inputfile to outputfile
            byte[] buffer = new byte[1024];
            int length;
            while ((length = myinput.read(buffer))>0) {
            myoutput.write(buffer,0,length);
            }

            //Close the streams
            myoutput.flush();
            myoutput.close();
            myinput.close();
            }

            public void opendatabase() throws SQLException {
            //Open the database
            String mypath = DB_PATH + DB_NAME;
            myDataBase = SQLiteDatabase.openDatabase(mypath, null, SQLiteDatabase.OPEN_READWRITE);
            }

            public synchronized void close() {
            if(myDataBase != null) {
            myDataBase.close();
            }
            super.close();
            }

            }

    • Itachi Uchia

      There are several ways, Simplest one is :-
      Use “IF NOT EXISTS”

      @Override
      public void onCreate(SQLiteDatabase db) {
      String CREATE_TABLE = “CREATE TABLE IF NOT EXISTS “+TABLE_NAME+”(” + all your columns + “); ” ;

      db.execSQL(CREATE_TABLE);
      }

      If you still having troubles then let me know;

      • jit

        we want to access a already created database for readonly purpose..example-to make a contacts app,which show results on inserting names in searchbox..

    • Jon

      This is the solution, more complicated than I thought

      http://www.vogella.com/articles/AndroidSQLite/article.html

  • mich

    BEST TUTORIAL ! THANKS SO MUCH! 😀

  • André Kunde

    Nice!
    How can I use this List on a ListView component?

    • This is kinda tricky… ListView uses a layout to fill the list, hence you need an adapter… if using android.R.layout.simple_list_item_1 it accepts String and from (using the Contact example) the Contact you need to extract a String (Name probably) and load it into an ArrayList and this ArrayList load it to the Adapter and this Adapter set it to the ListView…

  • jose

    Great Tutorial, but can you tell me how can i show it in a ListView , and add images (icons)…
    ThankYou

    • Itachi Uchia

      First you need to learn about ListView – ( Use google to search, Open results, Prefer what suits you best ) .
      Second load the data from database into an ArrayList of type according to your need (Preferable String) (1d, 2d .. . depends on your need )
      Now load that data into a class and create objects, load that objects into an arrayList
      Then use this new ArrayList to populate the ListView.

      Finally – It is a big topic can’t be taught in a single comment.
      Search this topic in google.
      If you don’t know how to search in google then follow these links.

      1. developer.android.com/guide/topics/ui/layout/listview.html
      2. http://www.vogella.com/articles/AndroidListView/article.html
      3. http://www.mkyong.com/android/android-listview-example/
      4. http://www.youtube.com/watch?v=gaOsl2TtMHs

      If you are a noob then watch youtube video in 4th link. It shows the simplest way to create ListView with icons.

  • Vaibhav Luthra

    I downloaded this example and used on my phone however when I go to log cat for checking results it keeps running and I am unable to see any data..please suggest

    • Akamaru of Hidden Leaf

      What do you want to say exactly ?
      From your problem it looks like a infinite loop problem.

  • Can Gökdere

    Hello, this is nice tutorial but there is a thing I do not understand. When to close or not close the database and cursor? In this tutorial it seems random. Also, what happens if you close/do not close

    • Shikamaru of Hidden Leaf

      This is a nice question to ask,
      I would like to answer it as simple as possible

      First, When we need to open a database :-

      The answer is that – Whenever you want to access anything ( Tables, Views, Indexes . . . )
      from a database you have to open it, in other words you have to open the connection between your program code and database.

      You can do it easily by using SQLiteDatabase in Android like this,
      SQLiteDatabase db = this.getWritableDatabase(); //To write;
      SQLiteDatabase db = this.getReadableDatabase(); //To Read

      Any of those statements will open the default database for your app that is set by you in the constructor of your database class.

      So, if you want to access a database you have to open it ( In other words you have to open the connection b/w your app and database. This is obvious that first you have to create the connection. )

      Second, Why you have to close a Database connection.

      Remember that you must always close the Database connection if it is no longer needed for better performance of your app. If a connection is opened then it will use system resources which will reduce your app’s performance.

      In Android You will get an Exception if you forget to do that, However if you want multiple connections to a database through your app then you have to specify it clearly. In C# you do this by using MultipleActiveResultSets=True;
      If you forget to do that and you are accessing database concurrently ( In other words multiple connections are opened for some purpose say – You have to read from one table and write to another table in same database ) then you will get an Exception.

      At last,
      you should always:

      1) Open connections as late as possible

      2) Close connections as soon as possible

      The connection itself is returned to the connection pool. Connections
      are a limited resource. Any new connection you establish that has exactly the same connection string will be able to reuse the connection from the pool.

      Cursor are used to execute database queries in Android.

      Formal Definition is – cursor is a interface provides random read-write access to the result set returned by a database query.
      Cursor implementations are not required to be synchronized so code using a Cursor from multiple threads should perform its own synchronization when using the Cursor.

      It is recommended to close all cursors when you are not using they anymore.
      If you keep it opened, you will cause a memory leak on your application.

      • MMJQ

        Bump, as the question is a good one and wasn’t answered. In the example in this post, the author calls db.close() on insert and update operations but not on query retrieval operations. I’m particularly curious about this as I’m trying very similar operations and frequently getting database locked exceptions.

  • krunal

    hello guy,
    i develop application which have bulk inset record in database.
    i have two question…
    1) when record are inserted at time i can not read record, my query got in the queue, it is possible that access data and insert data at time…?
    2) what is the limit of sqlite data base..size…?

    • Itachi Uchia

      1.

      Simple answer for your question is – “IMPOSSIBLE”

      Even if you getting success to do so – Means you are getting wrong results.

      I think you should know the basics of Database –

      Why database is better then file handling and other data storage methods.

      Simple answer –

      You can’t perform W-W, W-R, R-W operations simultaneously.

      ( W – write, R- read )

      However you can execute infinite R-R operations at a same time.

      Just think about the online Banking system or Railway reservation system.

      In which there is a special feature of database is used which is Transaction.

      It follows ACID.

      which is Atomicity, Consistency, Isolation, Durability.

      Atomicity – Either complete or not at all.

      Consistency – After each transaction system will go from one consistent state to another consistent state.

      Isolation – Every transaction will executed in isolation of each other.

      ( Means if write query come first it will executed first ) There is just no way to give both write and read operation at the same time. Even there is a difference of nano second System will detect it. However if you got success to do so . db simply reject it or execute the operation which has higher priority.

      Durability – System must durable in time.

      –Maybe it is very broad than a simple database but it may be help you to understand.–

      2.

      An SQLite database file is organized as pages. The size of each
      page is a power of 2 between 512 and SQLITE_MAX_PAGE_SIZE. The default
      value for SQLITE_MAX_PAGE_SIZE is 32768.

      The SQLITE_MAX_PAGE_COUNT parameter, which is normally set to
      1073741823, is the maximum number of pages allowed in a single database
      file. An attempt to insert new data that would cause the database file
      to grow larger than this will return SQLITE_FULL.

      So we have 32768 * 1073741823, which is 35,184,372,056,064 (35 trillion bytes)!

      You can modify SQLITE_MAX_PAGE_COUNT or SQLITE_MAX_PAGE_SIZE
      in the source, but this of course will require a custom build of SQLite
      for your application. As far as I’m aware, there’s no way to set a
      limit programmatically other than at compile time (but I’d be happy to
      be proven wrong).

      • gdguradio@gmail.com

        please help me on this canyou show me how to solve this …..

        ” Contact getContact(int id) ”
        how to use this function please give sample on how to do it like i want to get names only so i want to check the column name only and get all the names that are john any sample on this?and how to use other functions as well..

  • Sandeep Pareek

    hi
    I have a question, Where did this database got created? on sdcard? or on phone memory? I wanna access that db file manually,

    just to add, can I create a folder in SD card with some name as “contact db folder” and save that db there in that folder on android?

    Thanks for the best tute on SQL basics.

  • Hasan Rahman Sawan

    Hi, I am getting this error:

    [2014-01-07 09:49:56 – Dex Loader] Unable to execute dex: java.nio.BufferOverflowException. Check the Eclipse log for stack trace.

    [2014-01-07 09:49:56 – AndroidSQLiteTutorial] Conversion to Dalvik format failed: Unable to execute dex: java.nio.BufferOverflowException. Check the Eclipse log for stack trace.

    [2014-01-07 09:53:09 – AndroidSQLiteTutorial] Dx

    trouble writing output: already prepared

    Please help. Thanks.

  • ZaidiSoft

    Thank you much for a very good and useful tutorial. Quick question though. Is it possible to have add contact and display all contacts in one activity. What I mean is, to have edit text and add new user button as well as list view of all contacts on the same UI.

  • AyemMadScientist

    Thanks for the tutorial. Btw any idea how can i load the data from database into gridView, or how can i pass the data to String[]?

  • wild974

    I am looking for some help on how to use this tutorial to load what i have in the database to a listView. Have been searching for days on how to do this with no luck. If someone could point me in the right direction. I can post my code if need be.

    • hồng tươi nguyễn

      1. you read all columns of database by function getAllContect as above
      2.you need a custom listview by adapter , then you show your data in listview.

  • glenn

    Under AndroidSQLiteTutorialActivity,

    What does for (Contact cn : contacts){ does?
    1.Dont understand what is cn and where it comes from.
    2. What does : means?

    • Android@143

      Its a new looping mechanism; it recommend on working with multiple objects.
      Here,
      ‘cn’ is an object of Contact class.
      contacts is the collection of Contact class objects.
      This loop execute the total Contact objects present in the collection; and the checking is not needed.

      Only the Contact class objects are looped.

      • gdguradio@gmail.com

        ” Contact getContact(int id) ”
        how to use this function please give sample on how to do it like i want to get names only so i want to check the column name only and get all the names that are john any sample on this?

  • broskie

    So how would I add the database to a listView in another activity. I would like to click on a button that will take you to another activity and display the name and phone number of each contact in a listView with an onClick listener for each. Thanks for the help.

    • hồng tươi nguyễn

      in activity you want to show databse :
      1. you read all columns of database by function getAllContect as above
      2.you need a custom listview by adapter , then you show your data in listview.

  • george

    Hello. I need someone
    to develop a simple app. will use java , php and sql. please reply to george.celsie@gmail.com
    if you can help me. I will share the
    credits :). Thanks

  • Nasif Ahmed

    Can you please tell me why you use(_) _id, _name, _number insted of id, name, number?

    • Karim

      I think it’s a coding style, to put _ before the name of local variables declared in a class.

  • // 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();

    }

    This function throws exception:

    java.lang.IllegalStateException: attempt to re-open an already-closed object: SQLiteQuery: SELECT * FROM contacts

    You must get count before cursor.close();

    This is correct way:

    // Getting contacts Count

    public int getContactsCount() {

    String countQuery = “SELECT * FROM ” + TABLE_CONTACTS;

    SQLiteDatabase db = this.getReadableDatabase();

    Cursor cursor = db.rawQuery(countQuery, null);

    int count = cursor.getCount();

    cursor.close();

    return count;

    }

    • Joseph David

      Try this:
      .
      .
      .
      int count = cursor.getCount();

      cursor.close();

      // return count
      return count;

  • Shazwan

    I can’t find the database file. where does it saved?

    • Bhaskar

      Windows -> Show View -> Other.. -> Android -> File Explorer
      Data->Data->com.xxx.xxxx(Your package Name) ->Databases

  • LaLaRargh

    I’m just wondering, but how would you call the updateContact method in your activity class? I’m presuming it must be something like:

    contacts = db.updateContacts(); ?

    something along the lines of

    contacts = db.updateContacts(new Contact(); ?

    Thanks for any help.

  • Brian K. Trotter

    I notice that the onUpgrade() function wipes the previous database and starts over. While I could see some applications where that would be beneficial, I’d sure hate to lose a database with 100 contacts in it just because I decided to add a field for a 2nd phone number. Is there an easy way to upgrade a table to add a field without wiping the previous data?

  • if we use existing database and wants to upgrade database what should write in upgrade methods

  • Bharat Jiyani

    God Bless You 🙂

  • Matthew N

    Thanks alot for a great tutorial! extremely helpful for a somewhat tricky topic for new android developers!

  • Anshuman

    Thanks for the codes and the idea. One question I had in mind was where to write this code?? Do I create a separate package for the database or should I write the code in MainActivity.java itself?? Please help
    Thank you

  • Shima Shirazi

    Hi,
    Thank you for Tutorial.
    I would be very thankful if you answer my question:
    Where can I see my database application in (android) phone?
    I rooted the phone, but I can not find folder such as /data/data/my_app/databade

    • Arsal

      data->data->see your package name-> databases->

  • Kamolporn Sanamlao

    I would like to thanks, this tutorial is helpful. 🙂

  • Hendrik

    Hi, in the addContact method, can someone explain why only KEY_NAME and KEY_PH_NO are being added? Does KEY_ID not need to be added? Thanks

    • Noman

      because it is auto increment

  • Chrisantics

    hello! how do i want to view the database in sqLite browser? I can’t seem to find the .db file??

  • Shima Shirazi

    Somebody answer my question, plz

  • Also Ravi

    Never before have I gone through a tutorial like this and had it work on the first time. Ravi Tamada you are the man!

  • Abdullah Ben Nakhi

    How do you store images in the SQLite database ?

  • aref chegini

    Hello,
    Thank you for Tutorial.

    how do you store group data in the sqllite database and when im create a program in eclips and im install in the mobail database is void .

  • Hi,
    thanks for neat tutorial.
    I’m unable to see the created db files physically in my device. (/data/data//)
    do you have any idea why this happens?

  • Trevor L.

    Thanks, your tutorials are much appreciated.

  • Zahidul Islam

    good tutorial but one thing..data reinserted while run the tutorial again. i.e four rows inserted as 20 rows when run the tutorial 5 times. plz solve.

    • Faisal

      this is not a problem ~

  • Jagdeep

    Cursor cursor = db.rawQuery(countQuery, null);
    cursor.close();

    // return count
    return cursor.getCount();

    Error here cannot access the cursor after closing it better put the count in some another variable and return that variable.

    • ramesh bs

      You should not close the cursor after u query, cursor is the one which hold data & other objects. So if u close, it wont give u count. Once all cursor operations is completed. close it

  • gdguradio@gmail.com

    ” Contact getContact(int id) ”
    how to use this function please give sample on how to do it like i want to get names only so i want to check the column name only and get all the names that are john any sample on this?because when i use it , it throw an exception like this
    ” throw new CursorIndexOutOfBoundsException(mPos, getCount());”
    and how to use
    List contacts = db.getAllContacts();

    without using this code

    for (Contact cn : contacts) {
    String log = “Id: “+cn.getID()+” ,Name: ” + cn.getName() + ” ,Phone: ” + cn.getPhoneNumber();
    // Writing Contacts to log
    Log.d(“Name: “, log);

    like if i want to use if to compare if i get same value from the name? like if i want to get all name of john from the list

  • Thanks, good tutorial. But may I ask why are you using:

    Integer.parseInt(cursor.getString(0));

    instead of:

    cursor.getInt(0);

    Thanks

    • Manoj

      it has store the data in string format

      • Manoj

        Check this one…db.addContact(new Contact(“Ravi”, “9100000000”));

        its storing data in string format…not stored in
        db.addContact(new Contact(“Ravi”, 9100000000));

  • piter09100

    Thank you, great tutorial 🙂

    But I dont understand how you set IDs. My app gives ID=0 to every save and I dont know why??

    • David Doyle

      The definition for the I’d column using ‘Integer primary key’ should automatically increment on an insert. If that is not working try ‘integer primary key auto increment not null’.

      • piter09100

        Thanks, it works now, I’ve changed …+ KEY_ID + ” INTEGER PRIMARY KEY,”.. to …+ “KEY_ID INTEGER PRIMARY KEY,”…

        • Sarthak Majithia

          You need to change “INTEGER PRIMARY KEY” to “INTEGER PRIMARY KEY AUTO INCREMENT NOT NULL”

  • Mike

    Hi,

    For the AndroidSQLiteTutorialActivity class, i am facing an error where the code

    DatabaseHandler db = new DatabaseHandler(this);

    gives me an error saying: The constructor DatabaseHandler(SQLiteSubmit) is undefined

    Does anyone know how to solve it? Should the class extend SQLiteOpenHelper instead of Activity?

    • Sushreekant Mishra

      Try DatabaseHandler db = new DatabaseHandler(AndroidSQLiteTutorialActivity.this);

      • Mike

        Hi thanks for the help!

        I am new to android development and databases. Can i check whether data SQLite database is only accessible to 1 user/device?

        For example, if there is a Contacts database, and many users all over the world want to access this database to retrieve information, what kind of database should be used?

        • David Doyle

          This only allows you to store in a db accessible on the device to one application. To use a shared db, accessible from multiple devices, you’d need to call a web service (though you still might want to store a local copy of any information for caching purposes).

  • santosh

    how do i show this data in tablerow

  • how to save data from soap to sqlite database in android

    I am getting GetVehicles method of SOAP WSDL from SOAP web services and call that GetVehicles result in TextView when clicking on a Button event.

    when I run program , I want to store that result shown in TextView in Sqlite database?

    How can I do that?

    I have make a class getter Setter and databasehandler which extends SQLiteOpenHelper?

  • Jongsik

    Very useful article! Thank you 😀

    • You are welcome 🙂

      • Umar Ashraf

        Plz tel me if I want to show the output in an Amulator in the form of Listview, plz send me the code…

  • junka

    Thank you very much for the tutorial. It worked very well except for the getContactsCount() function. It was giving me an error saying that I was trying to read a value from a closed object. I changed the code to:

    public int getContactsCount() {
    String countQuery = “SELECT * FROM ” + TABLE_CONTACTS;
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.rawQuery(countQuery, null);
    int count = cursor.getCount(); //added line here
    cursor.close();

    return count;
    }

    After that everything was working smoothly. Thanks again.

    • Jomel

      how to use that getContactCount

  • tok

    Is there any possibility to add ArrayList as a type of row?

  • Anas

    Nice article, very useful!

  • NewtoAndroid

    The type java.lang.Object cannot be resolved. It is indirectly referenced from
    required .class files
    – The type java.lang.String cannot be resolved. It is indirectly referenced from
    required .class files

    Above error in DatabaseHandler.java file

  • NewtoAndroid

    for — package com.androidhive.androidsqlite;

  • Midomed

    what if I wanted to create a query to search for a particular id or a name?

  • Nika KirkitaZe

    Hi. Thank you for this tutorial, it is really helpful.

    Unfortunately my app gives ID = 0 to every time, i tried (INTEGER PRIMARY KEY),(‘_id’ INTEGER PRIMARY KEY AUTOINCREMENT), (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL),

    But i am getting same result always. what can i do?

  • FDM.Pro

    Why use static variables in contact class?

    • FDM.Pro

      Sorry in Handler not in contact.

  • Dilberius Bič Božji

    Does anyone know how to display database in textview or in anything like database? I would like to make a highscore but do not know the code to display strings in textview pls answer at dilberius@gmail.com

  • mukesh

    at logcat here elements add one by one when connection is generate………….
    THE PROBLEM HERE

    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());
    }

    REDUCE IT……..

    THANKS WITH BEST REGARDS

  • baus

    where is the the ” ( ” in the CREATE_CONTACTS_TABLE-String?

  • Agilitis

    Hello everyone! I’m facing a pretty irritating problem… My app starts on my mobile but it shuts down without any errors, any ideas?

  • Maryea

    hyee ….
    i am facing database upgradation problem.. using a pre populated sqlite database… it works fine.. bt when i try to update it by inserting values in existing table using sqlite browser.. it never upgrades… and shows no such record..
    Any idea for solution???

  • Guest

    never Forget to close the Cursor in method getAllContact! 😉

  • Russelius Ernestius

    some methods did not Close database or the Cursor… 😉

  • Hi, How do I create a method to check for existing records? I don’t want to add duplicates for example. Thanks!

    • Kuev

      +1👍 I was going to ask the same question… Anyone can help?

  • Ahmed Sobhy

    realy am very happy now i can say that i may anderstand sqlite database android thanks

  • Syed Ahmed Ali

    Awesome tutorial… got an idea on SQLite database

  • Thanks for perfect tutorial. I have two questions:
    1) How to prepare the SQLlite database and and fill it with the data before it is used in android app? All the tutorials I have red are creating the empty database directly in the dbHelper onCreate, but I already have the initial data, so I need to open the fully functional database in the app and not to create the empty one.

    2) How to dump the state of the database inside the phone onto the SD card and import the data back to database when needed. It would be very usefull in the case of database upgrade.

    Thanks for any info
    Jakub

    • 1. For the first question, you can insert the data in onCreate method of sqlite class or you can insert the data from the starting point of your app.

      2. For exporting the db to SDCard, you can try below link

      http://stackoverflow.com/questions/6540906/android-simple-export-and-import-of-sqlite-database

      • Thanks! It seems perfect. Exporting/importing also solves the first question, as I can create db and fill it with data in SQLite Manager (FireFox plugin), pack it into *.apk and than import it in database onCreate method.

        Thx
        Jakub

        • June

          Hi Jakub,
          Please have a look on the question I just asked to Ravi on the same context as of yours. May be you’ll also be able to help.

      • June

        Hi Ravi,
        Is it possible to have a ready-made database instead of populating one while running the app? I mean you just answred @jakuberch:disqus asking him to populate the data either of the two ways i.e. inserting in onCreate or inserting from the starting point. I want the database ready to use after installing the app only. How to achieve that? And how can I update the database from server in such case?

  • Duke

    Hi, Thanks for the Nice Tutorial.
    This line is throwing error.

    ” contact.setID(Integer.parseInt(cursor.getString(0))); ”
    Kindly advise.
    Also, getContactsCount() was throwing exceptions, due to closed cursor, so i removed cursor.close(). now its working fine.

  • Guest

    Thanks bro, its works

  • Guest

    Hi,
    Thanks alot for the tutorial. I have done all this.
    Can I know how to connect this sql database with eclipse?
    Can anyone help me please?

  • dr_ervina

    Thanks a lot ravi for your very simple useful code

  • wow thanks ravi, it’s very simple but useful tutorial

  • Hey Ravi Tamada,

    I have a database app that stores users’ names, phone numbers and email addresses, it works fine but I wanted it to do some extra task. I created an itemOnClickListener to handle clicking on saved contacts. Clicking on the listItems produces a popup menu with 3 options;

    1. Call selected person

    2. SMS this person

    3. Send email

    I created the pop up menu but now I want it to be in such a way that when I click on “Call selected person” option, it should get the respective phone number from database and call it. Please help.

    • numberFromDatabase = “111-333-222-4”;

      String uri = “tel:” + numberFromDatabase.trim() ;
      Intent intent = new Intent(Intent.ACTION_CALL);
      intent.setData(Uri.parse(uri));
      startActivity(intent);

  • name

    you did very well ………

  • Appkart In

    Hi Ravi , Some modification is needed in your code

    1.You should also add id of contact in ContentValues in addContact().

    2.Cursor can be null so that check null for whole code not only cursor.moveToFirst();

    if (cursor != null) {
    cursor.moveToFirst();
    Contact contact = new Contact(Integer.parseInt(cursor.getString(0)),
    cursor.getString(1), cursor.getString(2));

    }

  • swagat

    I did the same code, but app is throwing error- Unfortunately stopped. What is the reason??

  • bharat

    hey can u help? How to connect an externally created sqlite database in android studio??

  • Hüseyin Mesecan

    in getContactsCount: you write curser.close(); and then return curser.getCount(); why?

  • wathmal

    why can’t we use cursor.getInt(0) instead of Integer.parseInt(cursor.getString(0)) ???

  • rootbee

    hi ravi.. i m going to build one my own app so could you please me.. bcz i m in confusion. if possible mail me in here rootbee2014@gmail.com

  • Twelve

    Useful tutorial, thx !

  • gourav

    How to insert Paragraph in database and retrive it as a paragraph???

  • David Bu

    Hi i am using ur code and by the way its the cleanest code ive seen ive watched your videos and glad to no your from a c sharp background your code matches entity framework basically if only their was an orm like that do you have an example of how to populate a listview using ur DBHandler class that is the point I am stuck on at the min using the GetAlLContacts I am wanting to populate a listview with it

  • Tejwinder

    you saved my day 🙂

    thanks Ravi

  • WanderFilho

    Thank you very much for the explanation.
    I’ve been looking for something like this on the web and you made it very simple and incisive.

  • TheFiddle47

    What is the data type to add the photo of the contact? Its Byte[]?

    • yash

      you can store images with BLOB..!!

      • bilgee

        is BLOB is byte array ?

  • Chetan

    if I want to store data in sqlite file for permenate storage of data then how i connect to that file where i store that file SQLITE file

  • Gaurav

    when i write statement that give me the error

    List contacts = mDb.getAllStudentList();

    The method getAllStudentList() is undefined for the type SQLiteDatabase how to i solve

    • PrakashK Gowin

      Write this getAllStudentList() method in your DbHandler Class with public access.

      • Gaurav meghanathi

        actually i am trying to fetch data into sqlite-database in a list-view but this tutorial not much helpful..

  • mahboob gh

    how to update my contact database whenever android local contacts changed . I need to store contacts in database but I need to update it when a new contact is deleted or edited or inserted and update local contact when in my app a field changed,please help me.thank you

  • Gaurav meghanathi

    how to set string log to listview and display that object

    • Rúben Diogo

      I had the same question and I used a simple adapter. Check my code:

      public class AndroidSQLiteTutorialActivity extends Activity {
      /** Called when the activity is first created. */
      ArrayList<HashMap> contactList;

      private static final String TAG_NAME = “name”;
      @Override
      public void onCreate(Bundle savedInstanceState) {
      super.onCreate(savedInstanceState);
      setContentView(R.layout.main);

      ListView test = (ListView) findViewById(R.id.listView1);
      DatabaseHandler db = new DatabaseHandler(this);

      /**
      * CRUD Operations
      * */
      // 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 contacts = db.getAllContacts();

      contactList = new ArrayList < HashMap > ();

      for (Contact cn : contacts) {
      String log = “Id: “+cn.getID()+” ,Name: ” + cn.getName() + ” ,Phone: ” + cn.getPhoneNumber();
      // Writing Contacts to log
      Log.d(“Name: “, log);

      String name = “” + cn.getName();

      HashMap contact = new HashMap ();

      // adding each child node to HashMap key => value
      contact.put(TAG_NAME, name);

      contactList.add(contact);
      }

      SimpleAdapter adapter = new SimpleAdapter(this, contactList, android.R.layout.simple_list_item_1, new String[] { TAG_NAME }, new int[] { android.R.id.text1 });
      test.setAdapter(adapter);

      }

      }

  • vnshetty

    Hi , why we need SQLiteDatabase db = this.getWritableDatabase(); in getAllContacts() function?,where we are only fetching the data from db. Cant we use SQLiteDatabase db = this.getReadableDatabase(); ?

  • shivashish

    thanks a lot sir for your simple and extremely helpful code.

  • Basha

    Your tutorials are awesome yesterday I subscribed in your site. But still i didn’t get any confirmation email from you.

  • BLAH

    Can someone give me the example of deleting the user??

  • Abhay

    `this.getReadableDatabase(); makes the app to crash

  • Edward Lim

    Just wanted to give a huge shoutout to you and your work, Its all thanks to you that I finally have my app on the app store 😀 Although its just something simple I created, i created it because I found a need for it. Check it out if you guys are interested, again huge thanks to your work!!!

    https://play.google.com/store/apps/details?id=com.workoutlog.elcsgen.thesimpleworkoutlog

  • There is an error in your getContactsCount() function. The cursor object is closed before calling cursor.getCount().

  • JAMES

    The database name has to end with .db

  • Sanket Prabhu

    In future, can ORMLite/Realm takeover the SQlite? As a developer which is best as considering functionality?

  • Usman Ishrat

    Well elaborated… could be more helpful if you explain the parameters of the queries .

    • 8Farhan Shaikh

      if u understood program plz help ..i posted above my query plz help me out

  • Henrique Rosa

    How can i exclude the table, not the rows, but the intire table?

  • 8Farhan Shaikh

    i have try to do above program but i stuck at main class ……. when initiate() the DatabaseHandler like this DatabaseHandler db=new DatabaseHandler(this); in mainActivity class it says to impement arguments … but when i sae in your mainActivity there is no argument expect(this)

    please help me soon

  • Gergely Bindics

    Hi!

    Thank you for this tutorial, it was very useful.

    There was one problem with it if I am correct.

    Calling getContactsCount() method, my app crashed.

    The problem was that you closed cursor and then tried to call its getCount() method.

    So I stored cursor,getCount() int a variable, closed the cursor and then returned the variable.

    Cheers!

  • masoud seraj

    nice tutorial!

  • Sunil Singh

    very nice for beginners

  • Shindou Takuto

    where is code for update and delete contact? and i want to list it in list view.. thanks

    • Tim

      @shindoutakuto:disqus not to be rude, but you should read up on how android components work together to form an application. The code for updating and deleting is in the DatabaseHandler class, its up to you to try to figure out how to put it together. The author shows a good example of how to use it in the activity, just look by the “CRUD operations” comment. Great Job @ravi8x:disqus.

  • Homen

    Nice tutorial

  • Rhiedzal Brilliant Marz

    How showing database in layout?? please

  • AK

    I am making a small app which will just add and delete products but It is not able to print the database

    I want to print the database but it is not entering in the while loop which is made to move the cursor to access the product name

    Here is my code for products.java class

    package com.firstapp.sqliteexample;

    public class Products {
    private int _id;
    private String _productname;

    public Products(String productname) {
    this._productname = productname;
    }

    public void set_id(int _id) {
    this._id = _id;
    }

    public void set_productname(String _productname) {
    this._productname = _productname;
    }

    public int get_id() {
    return _id;
    }

    public String get_productname() {
    return _productname;
    }
    }

    Here is my code for MyDBHandler.java

    package com.firstapp.sqliteexample;

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

    public class MyDBHandler extends SQLiteOpenHelper{

    private static final int DATABASE_VERSION = 1;
    public static final String DATABASE_NAME = “products.db”;
    public static final String TABLE_PRODUCTS = “products”;
    public static final String COLUMN_ID = “_id”;
    public static final String COLUMN_PRODUCTNAME = “productname”;

    public MyDBHandler(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
    super(context, name, factory, version);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
    String query = “CREATE TABLE ” + TABLE_PRODUCTS + “(” +
    COLUMN_ID + ” INTEGER PRIMARY KEY AUTOINCREMENT, ” +
    COLUMN_PRODUCTNAME + ” TEXT ” +
    “);”;
    db.execSQL(query);

    }

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

    //Add a new row to the database
    public void addProduct(Products product){
    ContentValues values = new ContentValues();
    values.put(COLUMN_PRODUCTNAME,product.get_productname());
    SQLiteDatabase db = getWritableDatabase();
    db.insert(TABLE_PRODUCTS, null, values);
    Log.i(“database:”, String.valueOf(values));
    db.close();
    }

    //Delete a product from the database
    public void deleteProduct(String productname){
    SQLiteDatabase db = getWritableDatabase();
    db.execSQL(“DELETE FROM ” + TABLE_PRODUCTS + ” WHERE ” + COLUMN_PRODUCTNAME + ” =” ” + productname + ” “;”);
    Log.i(“database:”, “yo”);
    }

    //Print out the the database as a string
    public String databasetoString(){
    String dbString=””;

    String query = “SELECT * FROM ” + TABLE_PRODUCTS;

    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.rawQuery(query, null);
    Log.i(“database:”, “print”);
    // looping through all rows and adding to list
    if (cursor.moveToFirst()) {
    Log.i(“database:”, “inif”);
    do {
    Log.i(“database:”, “inloop”);
    dbString += cursor.getString(cursor.getColumnIndex(“productname”));
    dbString += “n”;

    } while (cursor.moveToNext());
    }

    db.close();
    return dbString;
    }

    }

    Here is my MainActivity.java code

    package com.firstapp.sqliteexample;

    import android.support.v7.app.AppCompatActivity;
    import android.os.Bundle;
    import android.util.Log;
    import android.view.View;
    import android.widget.TextView;
    import android.widget.EditText;/*
    import android.view.Menu;
    import android.view.MenuItem;*/

    public class MainActivity extends AppCompatActivity {

    EditText buckysInput;
    TextView buckysText;
    MyDBHandler dbHandler;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);
    buckysInput = (EditText)findViewById(R.id.buckysInput);
    buckysText = (TextView)findViewById(R.id.buckysText);
    dbHandler = new MyDBHandler(this, null ,null ,1);
    printDatabase();
    }

    /*@Override
    public boolean onCreateOptionsMenu(Menu menu) {
    // Inflate the menu; this adds items to the action bar if it is present.
    getMenuInflater().inflate(R.menu.menu_main, menu);
    return true;
    }

    @Override
    public boolean onOptionsItemSelected(MenuItem item) {
    // Handle action bar item clicks here. The action bar will
    // automatically handle clicks on the Home/Up button, so long
    // as you specify a parent activity in AndroidManifest.xml.
    int id = item.getItemId();

    //noinspection SimplifiableIfStatement
    if (id == R.id.action_settings) {
    return true;
    }

    return super.onOptionsItemSelected(item);
    }
    */
    //Add a product to the database

    public void addButtonClick(View view){
    Products products = new Products(buckysInput.getText().toString());
    dbHandler.addProduct(products);
    printDatabase();
    }

    //Delete items
    public void deleteButtonClick(View view){
    String inputText = buckysInput.getText().toString();
    dbHandler.deleteProduct(inputText);
    printDatabase();
    }

    public void printDatabase(){
    Log.i(“db”, “entering database”);
    String dbString = dbHandler.databasetoString();
    buckysText.setText(dbString);
    buckysInput.setText(“”);
    Log.i(“db”, “exiting database”);
    Log.i(“db”, String.valueOf(dbHandler));
    }
    }

    XML code

  • Sathish Kumar

    how to save the datas from the server to sqlite database

  • roopa

    hi. iam new to sqlite.. and i need code from you sir.how to develop a program for name,phno, city,country fields give from the key board and store data in sqlite and display in another activity using list view.

  • roopa

    sorry stored data display from another activity.

  • joejava

    //MySQLiteHelper

    package com.egci392.qz0428;

    import android.content.Context;

    import android.database.sqlite.SQLiteDatabase;

    import android.database.sqlite.SQLiteOpenHelper;

    import android.util.Log;

    public class MySQLiteHelper extends SQLiteOpenHelper{

    public static final String TABLE_DATA = “data”;

    public static final String COLUMN_ID = “_id”;

    public static final String COLUMN_ROUTENAME = “routeName”;

    public static final String COLUMN_START_LATITUDE = “start_latitude”;

    public static final String COLUMN_START_LONGITUDE = “start_longitude”;

    public static final String COLUMN_STOP_LATITUDE = “stop_latitude”;

    public static final String COLUMN_STOP_LONGITUDE = “stop_longitude”;

    public static final String COLUMN_COLORFLAG = “color”;

    private static final String DATABASE_NAME = “data.db”;

    private static final int DATABASE_VERSION = 1;

    // Database creation sql statement

    private static final String DATABASE_CREATE = “create table ”

    + TABLE_DATA + “(” + COLUMN_ID + ” integer primary key autoincrement, ”

    + COLUMN_ROUTENAME + ” text not null,”

    + COLUMN_START_LATITUDE + ” real not null,”

    + COLUMN_START_LONGITUDE + ” real not null,”

    + COLUMN_STOP_LATITUDE + ” real not null,”

    + COLUMN_STOP_LONGITUDE + ” real not null,”

    + COLUMN_COLORFLAG + ” text not null);”;

    public MySQLiteHelper(Context context) {

    super(context,DATABASE_NAME,null,DATABASE_VERSION);

    }

    @Override

    public void onCreate(SQLiteDatabase database) {

    database.execSQL(DATABASE_CREATE);

    } //when the program runs will runs this command first and table will be created

    @Override

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

    Log.w(MySQLiteHelper.class.getName(),

    “Upgrading database from version ” + oldVersion + ” to ”

    + newVersion + “, which will destroy all old data”);

    db.execSQL(“DROP TABLE IF EXISTS ” + TABLE_DATA);

    onCreate(db);

    }

    }

  • Arpit

    Thanks for the tutorial ravi sir but i wanna a know how to add .sql file in to the our SQLite database Please help me suggest any link to do that and sorry for my english

  • Very nice tutorial. thanks for sharing

    • You are welcome 🙂

      • Elashry

        how can match a new reading data with saved sqlite database

  • Kamaro Lambert

    Man I love your tutorials they are very simple to understand and codes are very clean.

    • Goodness Adewale

      “the codes are very clean” – very true

      • 🙂

        • Lucas Ofend

          Please sir, i have an issue with the SQLite Database Restore button in c#.
          Can you guide me please on how to restore an SQLite database in c#??
          i’ll be very gratefull .

  • hezo

    thank you ! it very useful .

    • You are welcome!

      • Lucas Ofend

        Please sir, i have an issue with the SQLite Database Restore button in c#.
        Can you guide me please on how to restore an SQLite database in c#??
        i’ll be very gratefull

  • DroidTalker

    Hi,

    @ravi8x:disqus thank u for first of all.

    i need a question:
    In your opinion,
    how do i have to choose database for any android application?
    SQlite or PHP Mysql or else?
    What i need to suggest?

    Thank u very very much
    Regards,

  • Very nice tutorial

  • Shreedhar090

    Thanks a lot man…

  • Ahmad Muzzammil

    how can i make an app that function read database not create, edit or even delete it? so there is a search bar them if i insert an ID the result is detailview of the ID. Thank you

  • Ravi Godara

    Thanks a lot for the code ….
    A superb post for image upload & retrieve is also at
    http://godara4ravi.blogspot.in/2016/03/select-image-from-gallery-upload.html

  • Luthfi M Nabil

    Thanks for the Sample 🙂

  • Thank you. Any reason why db.close() is not called in some of the CRUD operations?

    • Yeah, please add it before return. Also I suggest you create a singleton class to initiate the sqlite.

  • Thank You Soooo much !!!
    Great tutorial, well explained !

  • KC Raju Vysyaraju

    it’s great and simple.
    Thank you

  • florence cosmas

    This is awesome! Thanks

  • Thanks a lot!

  • ارحم

    i have error in DatabaseHandler ? why?

    • FaisalHyder

      As salam o alaikum.
      Error, where? which error? Be specific brother..

  • Gian Espinosa

    Thanks!

  • rogue_shadow

    Could you show how to use the update function? Thank you

  • AjayB

    I see you have used db.close() at only at one place and cursor.close() at another place. Will that not lead to issue of open db connections? Anyway I am looking for code that uses synchronized DB connection to ensure that open collection do not hang the mobile device. DO you know of any good example?

  • Ser

    Hi folks, I have a question. Why did we create three constructor at Contacts.java?

    • Dravit Lochan Gupta

      Not very sure, but maybe because if the developer puts id as auto incremental. empty constructor if the default constructor malfunctions or something like that.

  • raj

    it is ok. but how can we add a doc file or pdf file?

  • Neeraj Pant

    how can add contact add in emergency list class from contact list in phone

  • Jan

    Thanks a lot for this amazing tutorial!

    cursor.close();
    return cursor.getCount();

    Should be

    int count = cursor.getCount();
    cursor.close();
    return count;

    Because now it throws an java.lang.IllegalStateException: attempt to re-open an already-closed object when you call the getContactsCount() method.

  • Tushar

    Just Wow man … Loved it (y)

  • Muhammad Maqsood

    Tiger….!

  • Ahmed Aslam

    Assalam-o-Alaikum. #Ravi in getContact(). U used this.getReadableDatabase(); but in getAllContacts(). U used this.getWritableDatabase();. plz explain this. because both methods are reading data from data base. why this difference. and what is the reason of this?

    • It’s mistake. As we are not writing anything, the method should be readable db.

  • Dravit Lochan Gupta

    I have a doubt.!

    when an object of DatabaseHandler is made in AndroidSQLiteTutorialActivity, onCreate(), method of DatabaseHandler will be called. won’t it give an error if a table with same name is found in the system which was made at the time of previous execution of the application.

    • Dominic Claxton

      I’m pretty sure using the helper and this function it creates the database if it doesn’t exist and ignores this process if it already exists

      • Dravit Lochan Gupta

        Can you explain in detail?

    • Heba

      make sure that the constructor like this
      public DatabaseHandler(Context context) {
      super(context, DATABASE_NAME, null, DATABASE_VERSION);
      }

  • Sushant

    // In getting all contacts
    do{
    Contact contact = new Contact();
    Asking to pass the “int id, String name, String _phone_number “.
    What to write in this constructor?
    Please help.

    • vishwa

      Don’t write anything, Empty constructor will handle it.

      while adding the contact you should fill something in that.

    • Hitesh Danidhariya

      make an empty constructor

  • Great you articles are really useful for me , i am a beginner for developing android app. your website is so useful for me.

  • Adedara Klever Olanrewaju

    Ravi, how do I set the results to a list view

    • Jeffy

      //your listview
      ListView list;
      list = (ListView )findViewById(R.id.listView);

      List contacts = db.getAllContacts();

      ArrayAdapter adapter = new ArrayAdapter(this,android.R.layout.simple_list_item_1,contacts);
      list.setAdapter(adapter)

      //this would work

      • ospfkpoes

        Its showing the list in hexadecimal values.. cant figure out what to do!!plz Help!!

  • Suhail Parvez

    Hey Ravi , how do we store an Arraylist into the database table ?
    Didn’t get much help in StackOverflow.
    I know we have to split the arraylist and insert it one by one.
    BUT, how do i store it in the table ?

    • Can you give me the sample data in your ArrayList?

      • Suhail Parvez

        Thie is the JSON coming in
        “staff”: [
        “John Doe”,
        “2”,
        “a”
        ],
        or
        “departments”: [
        “Waste Management”,
        “Medical Gases”,
        “Hospital Wards”,
        “Waste Compound”,
        “BIOCHEMISTRY CHEMICALS”,
        “Biochemistry Lab”,
        “Catering and Household”,
        “CDU”,
        “Clinical Engineering”,
        “Derrycourt Cleaning Services”,
        “EMBU Lab”,
        “Emergency”,
        “Haematology/ Blood transfusion”,
        “Histology”,
        “Histology Dyes”,
        “Household Services”,
        “IMMRL”,
        “Industrial Gases”,
        “Medical Gases”,
        “Metabolic”,
        “Microbiology”,
        “Mortuary”,
        “Neurology”,
        “Newborn Screening Lab”,
        “Pharmacy”,
        “Technical Services”
        ]

  • wazaa20003

    Ravi, how to select query using List?

  • Shiven Singh

    Amazing tutorial..Really appreciate the effort you put in to make all these sweet tutorials
    Thankyou from the whole community.

  • Leo

    My good sir might I ask where is the xml file or how can i connect the database and xml file

  • ajaxkm

    xml?

    • Kebaa Marush

      remember is this example you don’t use any view but rather work with the Logcat,

  • George Zhao

    Hi, thanks for your sharing. It helps me a lot.
    But I have a question, I do not understand why you do not add the id into the value in the method addContact().

  • shashank tiwari

    Sir again needed your help i wanted to drop my sqlite tables but not able to call onupgrade method .is there any alternate solution to dropping my sqlite tables and then recreating them.

  • Burak Cakir

    Great tutorial Ravi, thanks.

    But there is one mistake that you try to re-open the cursor which is already closed in getContactsCount() method.

    it should be fixed like this :

    public int getContactsCount() {
    String countQuery = “SELECT * FROM ” + TABLE_CONTACTS;
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.rawQuery(countQuery, null);
    int count = cursor.getCount();

    cursor.close();
    db.close();

    return count ;
    }

  • Shameem Ahsan

    hi sir if u dont mind give me ur number , need help

  • james

    How do you do it with getContactsById? sorry I’m new to this.

  • John Kimson Magat

    Thanks for this great tutorial 😀

    but please someone help me with my problem
    how do i put the result in ListView? i already tried it but it only show the last record

    • Whats the code you are trying?

      • Keshav Tiwari

        @ravi8x:disqus Sir I have the same prob. Its my first time with databases. I have a custom object and an adapter. Should I edit my object as Contact ? Should I send you the code?

  • Sgsh

    How do I create the database in one class and then reference it from other classes?

  • Saathwik Nalige

    This tutorial is awesome!!! Really helpful for beginners… have you devoloped any project based on retrofit???
    and im getting value of phone number as name(name = ravi,phone_num = ravi) instead of the number

  • Chris Ryce

    Thanks for this amazing tutorial. I’m a beginner in android development and just by your simple steps of explaining this complex topic of maintaining SQL database in android, I’ve learnt so much.
    There is a minor correction in getContactsCount() function. There shouldn’t be cursor.close() line before the cursor.getCount(). So I think it should be like this, correct me if i’m wrong..
    {
    //rest of the code…..
    int count=cursor.getCount();
    cursor.close();
    return count;
    }

  • Chris Ryce

    Can anyone tell me how to use this updateContact() function. And what integer value it is returning?

  • Chris Ryce

    I have one more question. I have successfully applied the deleteContact() function but when I further viewed the database, there is no update in the KEY_ID like-
    ID NAME PHONE_NO
    1 Chris 15654654
    2 Andrew 11546586
    3 Will 54556585

    After deletion of “Andrew” record-
    ID NAME PHONE_NO
    1 Chris 15654654
    3 Will 54556585

    See, no change in the key_id like “Will” record should have id=2.
    Is this limitations of SQLite or I’m doing something wrong?

    • Marius Zimta

      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);
      }

      in this function key_id is declared as primary key(that means, that the key must be unique). if you delete your entry you are deleting also the unique key. in this example the primary key is autoincremented.

  • Peter

    put the internet permission outside permission tags

    • Peter Amit

      application tags

  • Yogesh

    Thank you very much for the code.. 🙂

  • Lor’themar Theron

    Thanks for your guide, but when i run app, its giving me error at three lanes

    1. SQLiteDatabase db = this.getWritableDatabase(); // Updating single contact
    2. SQLiteDatabase db = this.getWritableDatabase(); //Getting all contact
    3. db.execSQL(CREATE_CONTACTS_TABLE); // creating table
    What must i do?? When i tried others database examples, they r giving me same error on getWritableDatabase, db.execSQL methods.

  • Dimas Prasetio

    hey ravi

    i wanna ask about this code :

    public List getAllContacts() {
    List contactList = new ArrayList();
    // 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;
    }

    if adding new value to sqlite and show into arraylist, the previous data not clear.
    example :

    i insert db.addContact(new Contact(“1”, “1”));
    and the output will be :
    1,1

    and then i insert new one
    i insert db.addContact(new Contact(“2”, “2”));
    and the output will be :
    1,1
    2,2

    how to change output only showing 2,2 ?
    i try put fotolist.clear() before add but not working

  • whoisnva

    If I instantiate the database in the MainActivity and want to access the data inside of a different Fragment how do I achieve this to make a getAllContacts(); call?

    • whoisnva

      Do I have to pass JSON to SQLlite or can I pass arraylist object direct?

  • gtm

    i need to user search the data in android application using sqlite .only search option

  • Shahzaib Dahani

    Sir You are doing a great job for beginners. I dont find any good startup tutorial than yours.
    I have some questions that i have ask. i have implemented login registration app and i just wanna know.. I mean for now we are using Wamp server l and in code we gave our machine ip address and it wont run on my mobile or on any other mobile too so whats the procedure if i have my own server and it should run on all networks not only on my PC.

  • Abdul moiz

    Sir its great to see your tutorials but i will suggest you that if you can make a list of tutorials so that beginners can start from top to bottom step by step.. maybe beginners will find hard if they start implementing REST API tutorials so you know if you can make a list in which all your tutorials from beginners to advanced. I hope you can understand as i am just a beginner .

    • Yup, I have this in mind. I’ll keep them after few days. Website is getting new look.

  • Rughetto

    hello and thanks for your tutorials! I want to bring to your attention a library that I made for Android to work with persistence through SQLite, SharedPreference, JSON, XML, and other formats. Its name is Krypton and is located at the following URL: https://github.com/xcesco/kripton/.

    With my library, to achieve the same CRUD operations of your tutorial, I would have to write a class contact:

    — Contact.java
    @BindType
    public class Contact {
    //private variables
    long id;
    String name;
    String phoneNumber;
    // same of original class

    }

    And two interfaces to define the SQLite and a DAO database to work with the Contact.

    — ContactsDataSource.java
    @BindDataSource(dao = {DaoContacts.class}, fileName = “contacts.db”)
    public interface ContactsDataSource {
    }

    — DaoContacts.java
    @BindDao(Contact.class)
    public interface DaoContacts {

    // Adding new contact
    @BindSqlInsert
    void addContact(Contact contact);

    // Getting single contact
    @BindSqlSelect(where=”id=${id}”)
    Contact getContact(long id);

    // Getting All Contacts
    @BindSqlSelect
    List getAllContacts();

    // Getting contacts Count
    @BindSqlSelect(value=”count(*)”)
    int getContactsCount();

    // Updating single contact
    @BindSqlUpdate(where=”id=${contact.id}”)
    int updateContact(Contact contact);

    // Deleting single contact
    @BindSqlDelete(where=”id=${contact.id}”)
    void deleteContact(Contact contact);
    }

    So, to perform CRUD operations you can simply write:

    // init library
    KriptonLibrary.init(this);

    // open database and get dao
    BindContactsDataSource dataSource=BindContactsDataSource.open();
    DaoContactsImpl daoContacts = dataSource.getDaoContacts();

    Contact bean=new Contact();
    bean.setName(“name”);
    bean.setPhoneNumber(“123456”);

    // insert a contact
    daoContacts.addContact(bean);

    // update a contact by id
    bean.setPhoneNumber(“654321”);
    daoContacts.updateContact(bean);

    // select all contact
    daoContacts.getAllContacts();

    // select by id
    Contact result=daoContacts.getContact(bean.getId());

    // delete by id
    daoContacts.deleteContact(result);

    // close database
    dataSource.close();

  • Kiruthika Meena

    Sir,
    how to view the table information in list view..?? please explain me sir

  • Beck

    Sir, I have a question about your code to create database. Will each user who launches this program share the same database, or each user has his own SQLite database?

  • jaya

    hi ravi
    am highly impressed with this article or yours
    but want to ask u if we have any app which can actually get contact.db file from ur phone
    bcoz am trying to help my mom to get her contacts back from her old phn
    she cant do all this coding stuff

  • Hosein Mirian

    Dear Ravi. Thanks for your beautiful Tutorial

  • Simon T

    what parameter do i pass to method deleteContact()
    to remove contacts?

    • zheer

      an instant of Contact class but before that make sure you set all variables on the instant by setter method

  • Nurul Musaffa

    Sangat membantu Ravi, terimakasih dari Indonesia

  • Michael Nguyen

    i insert data in sqlite but listview not freshing?
    i’ using notifyDataSetChanged but not freshing?

    please help me.
    Thank you https://uploads.disquscdn.com/images/38519a4a5f06b2c9ad991cadde40f7a1377ca8dd256411586452b6ba9f2aeed7.png

    • notifyDataSetChanged() will work only when you change the data in the arraylist or the data associated with adapter. If you want to see the changes after inserting in SQLite, add the newly inserted items to arraylistsv and call notifyDataSetChanged.

      Also there might be another problem with this line of code
      arraylistsv = db.getListSVAL() as you will get a reference problem.

  • Joy

    Hi…Ravi,
    I would like to want an example of SQLiteDatabase using ContentProvider with more than one URL content.

  • nilisha

    Hello Ravi, I am just started learning android and I have just copied your code as it is to understand sqlite connection in android bt still it is not working in emulator. Can you please personally guide me for this application from the beginning.

    • shashi patil

      problem??

  • fromeroh2009

    Hi Ravi
    I have a problem!
    i dont see the /data/data/xxxxxxxxxxx/databases folder
    when i open the Android Monitor and click File Explorer Tab the data folder is empty???
    please advise
    i tried to do it by ADB but when i execute the “ls” command it says: not permission
    please advise

    • Jamie O’neill

      you can use Stetho to view local db’s and shared preferences.
      add compile ‘com.facebook.stetho:stetho:1.4.2’ to your gradle and Stetho.initializeWithDefaults(this); in the onCreate of your MainActivity.
      Once you run your app connected via usb you can go to “chrome://inspect/#devices” in the chrome browser to examine the database folders.
      More info can be found here http://facebook.github.io/stetho/

  • Raghavan

    hi sir how to add daily reports of point of sale systems in SQLiteDatabase in android plz guide me sir

  • lukas

    Thanks a lot for the great tutorial, really helped me!

  • syed shahid

    How i update a specific contact and override it i call update in MainActivity and Pas new value to update plz anyone can help me
    ContentValues contentValues = new ContentValues();
    contentValues.put(“name”,”Shiraz”);
    contentValues.put(“phone_number”,”30159008182″);
    such value we want to put as an update value

    • yusuf khan

      DatabaseHandler db = new DatabaseHandler(this);

      db.updateContact(new Contact(1, “Shiraz”, “30159008182”));

      1 is the id where you want to update. Revert if you have any doubts.

  • venkat

    //Login
    public class LoginActivity extends Activity {
    Button Login;
    EditText USERNAME, USERPASS;
    String username, userpass;
    Context CTX = this;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
    // TODO Auto-generated method stub
    super.onCreate(savedInstanceState);
    setContentView(R.layout.login_layout);
    Login = (Button) findViewById(R.id.b_login);
    USERNAME = (EditText) findViewById(R.id.user_name);
    USERPASS = (EditText) findViewById(R.id.user_pass);
    Login.setOnClickListener(new OnClickListener() {

    @Override
    public void onClick(View arg0) {
    Bundle b = getIntent().getExtras();
    int status = b.getInt(“status”);
    if (status == 1) {
    Toast.makeText(getApplicationContext(), “Please wait…”, Toast.LENGTH_LONG).show();
    username = USERNAME.getText().toString();
    userpass = USERPASS.getText().toString();
    DatabaseOperations DOP = new DatabaseOperations(CTX);
    Cursor CR = DOP.getInformation(DOP);
    CR.moveToFirst();
    boolean loginstatus = false;
    String NAME = “”;
    do {
    if (username.equals(CR.getString(0)) && (userpass.equals(CR.getString(1)))) {
    loginstatus = true;
    NAME = CR.getString(0);
    }

    } while (CR.moveToNext());
    if (loginstatus) {
    Toast.makeText(getApplicationContext(), “Login Success—-n Welcome ” + NAME, Toast.LENGTH_LONG).show();
    finish();
    } else {
    Toast.makeText(getApplicationContext(), “Login Failed—- “, Toast.LENGTH_LONG).show();
    finish();
    }

    } else if (status == 2) {
    Toast.makeText(getApplicationContext(), “Please wait…”, Toast.LENGTH_LONG).show();
    username = USERNAME.getText().toString();
    userpass = USERPASS.getText().toString();
    DatabaseOperations DOP = new DatabaseOperations(CTX);
    Cursor CR = DOP.getInformation(DOP);
    CR.moveToFirst();
    boolean loginstatus = false;
    String NAME = “”;
    do {
    if (username.equals(CR.getString(0)) && (userpass.equals(CR.getString(1)))) {
    loginstatus = true;
    NAME = CR.getString(0);
    }

    } while (CR.moveToNext());
    if (loginstatus) {
    Toast.makeText(getApplicationContext(), “Login Success—-n Welcome ” + NAME, Toast.LENGTH_LONG).show();
    Intent i = new Intent(LoginActivity.this, DeleteActivity.class);
    Bundle BN = new Bundle();
    BN.putString(“user_name”, NAME);
    BN.putString(“user_pass”, userpass);
    i.putExtras(BN);
    startActivity(i);
    finish();
    } else {
    Toast.makeText(getApplicationContext(), “Login Failed—- “, Toast.LENGTH_LONG).show();
    finish();
    }

    } else if (status == 3) {
    Toast.makeText(getApplicationContext(), “Please wait…”, Toast.LENGTH_LONG).show();
    username = USERNAME.getText().toString();
    userpass = USERPASS.getText().toString();
    DatabaseOperations DOP = new DatabaseOperations(CTX);
    Cursor CR = DOP.getInformation(DOP);
    CR.moveToFirst();
    boolean loginstatus = false;
    String NAME = “”;
    do {
    if (username.equals(CR.getString(0)) && (userpass.equals(CR.getString(1)))) {
    loginstatus = true;
    NAME = CR.getString(0);
    }

    } while (CR.moveToNext());
    if (loginstatus) {
    Toast.makeText(getApplicationContext(), “Login Success—-n Welcome ” + NAME, Toast.LENGTH_LONG).show();
    Intent i = new Intent(“delete_filter”);
    Bundle B = new Bundle();
    B.putString(“user_name”, NAME);
    i.putExtras(B);
    startActivity(i);

    finish();
    } else {
    Toast.makeText(getApplicationContext(), “Login Failed—- “, Toast.LENGTH_LONG).show();
    finish();
    }

    //Intent i = new Intent(“delete_filter”);
    //startActivity(i);
    }

    }
    });
    }

    }

    //Registration

    REG.setOnClickListener(new OnClickListener() {

    @Override
    public void onClick(View v) {
    user_name = USER_NAME.getText().toString();
    user_pass = USER_PASS.getText().toString();
    con_pass = CON_PASS.getText().toString();

    if(!(user_pass.equals(con_pass)))
    {
    Toast.makeText(getApplicationContext(),”Passwords are not matching”, Toast.LENGTH_LONG).show();
    USER_NAME.setText(“”);
    USER_PASS.setText(“”);
    CON_PASS.setText(“”);
    }
    else
    {
    DatabaseOperations DB = new DatabaseOperations(ctx);
    DB.putInformation(DB, user_name, user_pass);
    Toast.makeText(getApplicationContext(), “Registration success”, Toast.LENGTH_LONG).show();
    finish();

    }

    }
    });
    //Update

    b_update.setOnClickListener(new OnClickListener() {

    @Override
    public void onClick(View v) {
    user_name = username.getText().toString();
    user_pass = password.getText().toString();
    New_user_name = newuser.getText().toString();
    DOP = new DatabaseOperations(CTX);

    Cursor CR = DOP.getUserPass(DOP, user_name);
    CR.moveToFirst();
    boolean login_status = false;
    if (CR.getCount() > 0) {
    DOP.updateUserInfo(DOP, user_name, user_pass, New_user_name);
    Toast.makeText(getApplicationContext(), “Updation Success…..”, Toast.LENGTH_LONG).show();
    finish();

    } else {
    Toast.makeText(getApplicationContext(), “Invalid user…..Try later”, Toast.LENGTH_LONG).show();

    }
    }
    });

    //DO
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // TODO Auto-generated method stub
    // Drop older table if existed
    db.execSQL(“DROP TABLE IF EXISTS ” + TableData.TableInfo.TABLE_NAME);
    onCreate(db);// Create tables again

    }

    public void putInformation(DatabaseOperations dop, String name, String pass)

    {
    SQLiteDatabase SQ = dop.getWritableDatabase();
    ContentValues cv = new ContentValues();
    cv.put(TableData.TableInfo.USER_NAME, name);
    cv.put(TableData.TableInfo.USER_PASS, pass);
    long k = SQ.insert(TableData.TableInfo.TABLE_NAME, null, cv);
    Log.d(“Database operations”, “One raw inserted”);

    }

    public Cursor getInformation(DatabaseOperations dop) {
    SQLiteDatabase SQ = dop.getReadableDatabase();
    String[] coloumns = {TableData.TableInfo.USER_NAME, TableData.TableInfo.USER_PASS};
    Cursor CR = SQ.query(TableData.TableInfo.TABLE_NAME, coloumns, null, null, null, null, null);
    return CR;

    }

    public Cursor getUserPass(DatabaseOperations DOP, String user) {
    SQLiteDatabase SQ = DOP.getReadableDatabase();
    String selection = TableData.TableInfo.USER_NAME + ” LIKE ?”;
    String coloumns[] = {TableData.TableInfo.USER_PASS};
    String args[] = {user};
    Cursor CR = SQ.query(TableData.TableInfo.TABLE_NAME, coloumns, selection, args, null, null, null);
    return CR;

    }

    public void deleteUser(DatabaseOperations DOP, String user, String pass) {
    String selection = TableData.TableInfo.USER_NAME + ” LIKE ? AND ” + TableData.TableInfo.USER_PASS + ” LIKE ?”;
    //String coloumns[] = {TableData.TableInfo.USER_PASS};
    String args[] = {user, pass};
    SQLiteDatabase SQ = DOP.getWritableDatabase();
    SQ.delete(TableData.TableInfo.TABLE_NAME, selection, args);

    }

    public void updateUserInfo(DatabaseOperations DOP, String user_name, String user_pass, String new_user_name) {
    SQLiteDatabase SQ = DOP.getWritableDatabase();
    String selection = TableData.TableInfo.USER_NAME + ” LIKE ? AND ” + TableData.TableInfo.USER_PASS + ” LIKE ?”;
    String args[] = {user_name, user_pass};
    ContentValues values = new ContentValues();
    values.put(TableData.TableInfo.USER_NAME, new_user_name);
    SQ.update(TableData.TableInfo.TABLE_NAME, values, selection, args);

    }

  • Kinjal Makwana

    how to get database file ?
    i see the /data/ folder blank

    • Elbert

      you must root the phone to be able to acccess system files

      • Kinjal Makwana

        its already rooted

  • Karan galgat

    how can i call the DatabaseHandler methods from activities other than MainActivity class, if the object is made in MainActivity class ?
    Also there is an error in DatabaseHandler class, in getContactsCount method due to which app crashes.(cursor.close(); should not be written since you are using cursor.getCount(); to return value )

    • Karan galgat

      …??

      • moussa maanga

        the error that you close the cursor then call him try this code it work for me

        public int getContactsCount() {
        String countQuery = “SELECT * FROM ” + TABLE_CONTACTS;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);
        int c=cursor.getCount();
        cursor.close();

        // return count
        return c;
        }

  • annie

    you didnt db.close() for some functions such as getAllContacts

    • If it’s missing, please add it yourself.

  • Ram Venkatraman

    I could execute this program and it worked out. Can you help me where this SQLite table is stored and how do i view this ?
    Thanks in advance.

  • JU quite old

    Thank you for the first tutorial I understood and success follows

  • Nikita Gaba

    Thanks for the tutorial Ravi, could u please help with the location of Databases stored on phone, i looked inside /data/data/com.my.package/ directory , i could not find the database files.

  • Nikitha

    I would like to open database with sqlitedatabase.opendatabase. Do you have any example. How can we give path there.

  • Shadow Walker

    Hi Ravi, could u please help with how i store image in SQLITE and get that image in imageview

    • Inter Lock (Alluka)

      you don’t exactly store the image in the database, you just store its filepath

  • Sachin Gupta

    Hello Ravi, thanks for this Tutorial.
    what should i do if i want to to sort database in alpabetical order by using name.
    (i mean which query should i run when i am getting all contact)

    • BunnyFiscuit

      SELECT * FROM tableName ORDER BY name

  • sandypatel

    great tutorial Ravi , Really Helpful for me.

  • So easy, that’s what i wanted to see in my simple app! Thanks a lot!

  • Parimal Debbarma

    hello sir,It is very nice and clear tutorial.I have a question how to get multiple checkbox click value in place of phone number ? please help.

  • Praful Dhabekar

    contact.set_id(Integer.parseInt(cursor.getString(0)));

    I am getting number format exception here. can you please help me ?

  • hamza abusabra

    thx alot for this tutorial . i have a question can i make 2 table in the database ?

    • You can have any number of tables. Follow the below article to see working with multiple tables.
      https://www.androidhive.info/2013/09/android-sqlite-database-with-multiple-tables/

      • hamza abusabra

        many thx for you ^-^

      • Stajah Lee Hoeflich

        @ravi8x:disqus Thank you so much for these tutorials! I am a student and we are implementing an Android App for our Senior Design Project (it will be an Android mobile device application – bluetooth paired with an RFID tag reading device – that takes/maintains inventory). I have never created an Android App before (school mostly writes programs in C++, Python, etc and not a full application with UI, etc). These tutorials are so extremely helpful! Since the date on this is several years ago, will that impact the code or anything with the libraries as I move forward trying to work with my team to make our app?

  • Yury Oskin

    Hello, i have one question, can i have access to List contacts from another activity, besides intent?
    if contact is static, we have access from any activity, but we can not do this List contacts = db.getAllContacts() because “non static field cannot be referenced from a static context”

  • SSKWEB Android

    thanks a lot… your code is crystal clear

  • Tiago

    Error – reusing cursor after closing
    cursor.close();

    // return count
    return cursor.getCount();
    ————————————————–
    int count = cursor.getCount();
    cursor.close();

    // return count
    return count;

  • Nishant Lokhande

    is this database universal? if i have two users A & B, if A creates a new entry, will that be accessible or visible to user B?(i want it to be that way)
    my english is not good and I’m a noob, hope you understand what i said

    • It’s not Universal that means other apps can’t access your app database. But within the app you can any thing. A can access of B, but that depends how you implement the logic.

  • Vivek

    Great Tutorial! I have a question. What theme are you using for the editor ? I want to apply it to my Android Studio Editor. Please let me know.

    • I use Darcula theme. But this article was written in Eclipse.

  • Pari Gojariya

    Anyone about complete tutorial on SQLite because i’m new in it.
    If yes then please suggest me.I really need it.

  • Kaushik Reddi

    can you give information about this for xml i need to create a app for suggest me a interface please @ravi8x:disqus

    • LaLa

      i need that too

  • Tom M-W

    Perhaps I doing something wrong (I’ve only been doing this Android Studio/Java thing for 2 days), but I think the ‘getContactsCount’ function didn’t work for me. I think this is because it is trying to return with a method that doesn’t exist (it gets closed). I solved it by storing the cursor.getCount() result in an integer, closing the cursor and then returning the integer. Otherwise, it’s worked great, thank you for the great tutorials!

  • LALIT

    Nice tutorial… May you please make a tutorial on Google drive api. I want to fetch data from private drive.

  • Akashh

    How is ID field getting updated ?? like I can see you are not inserting ID or doing anything like auto-increment ?

    • The ID field is declared as INTEGER PRIMARY KEY which doesn’t need an AUTOINCREMENT.

      Refer this doc
      The AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. It is usually not needed.
      https://sqlite.org/autoinc.html

  • Ibrahim

    Nice and well organized tutorial. I m new to android development but I known more SQL. I was wondering if there is a way to directly use sql commands for certain actions instead of using methods. Eg “select count(*) from contacts” to get the total count. Thank u

    • I suggest Room database once you are good with this.