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

  • 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??