Android sq lite database tutorial

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 1/110 BEGINNER BY RAVI TAMADA - NOVEMBER 27, 2011 - 0 COMMENTS 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. 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).      Android SQLite Database Tutorial

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 2/110 Contacts Table Structure 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){

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 3/110 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;

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 4/110 } // 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.,

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 5/110 public class DatabaseHandler extends SQLiteOpenHelper { // All Static variables // Database Version private static nal int DATABASE_VERSION = 1; // Database Name private static nal String DATABASE_NAME = "contactsManager"; // Contacts table name private static nal String TABLE_CONTACTS = "contacts"; // Contacts Table Columns names private static nal String KEY_ID = "id"; private static nal String KEY_NAME = "name"; private static nal 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

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 6/110 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) {}

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 7/110 ⇒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();

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 8/110 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));

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 9/110 // 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

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 10/110 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;

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 11/110 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 nal int DATABASE_VERSION = 1; // Database Name private static nal String DATABASE_NAME = "contactsManager"; // Contacts table name private static nal String TABLE_CONTACTS = "contacts"; // Contacts Table Columns names private static nal String KEY_ID = "id"; private static nal String KEY_NAME = "name"; private static nal 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) {

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 12/110 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 }

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 13/110 // 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

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 14/110 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;

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 15/110 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

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 16/110 * */ // 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.getPhoneNumb // 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.

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 17/110

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 18/110 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 Tamada Ravi is hardcore Android programmer and Android programming has been his passion since he compiled his rst hello-world program. Solving real problems of Android developers through tutorials has always been interesting part for him. DATABASE SQLITE RELATED POSTS

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 19/110 Android Working with ButterKnife ViewBinding Library

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 20/110 Android Working with WebView – Building a Simple In-App Browser Android Floating Widget like Facebook Chat Head 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 dif cult 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

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 21/110 http://android-helper4u.blogspot.com/2013/03/d-databse-and-spinner-tutorial.html Guest Could I use this database to store dynamic elds 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 ? Mohammad Alhobayyeb 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 Asif Hasan 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 david molina Did you make sure that you are creating that column in your table and is the exact name(spelling,

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 22/110 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 rst 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 rst 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

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 23/110 A guest what is path of DB? where can i found my SQLite le 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 le: 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’).

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 24/110 [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 dif culty to do this Tonyoh87 xed, 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

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 25/110 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 le 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 le 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.

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 26/110 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 speci ed 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………..

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 27/110 Zach Best tutorial for this I have seen, thanks! Sandeep Saini how to update the table with modi ed 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 le ryl Solution by Ravi in previous comments // setting name in textview TextView txtName = (TextView) ndViewById(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..

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 28/110 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.AndroidSQLiteTutori alActivity}: 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

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 29/110 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 rst. Thanks. volume_8091 And how can we save the table in a different le 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 x it? ASH

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 30/110 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 ne … rst 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 Clari cation 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);

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 31/110 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 in nite loop! but for people who have the same problem here is my way too x 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);

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 32/110 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;

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 33/110 //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?

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 34/110 id=izasoft.kurumsal.uygulama maybe you can use Xml Source an alternative… Thanks your tutorial. carterson2 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 Ravi Tamada 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 ll 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 modi ed 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.

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 35/110 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?

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 36/110 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 speci c contact. Tobi Load the data you want to update in an ArrayList of String then pass it to along with a userName ( any uniquely identi ed attribute ) to update a particular contact. Let say, you want to update user name, rst 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();

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 37/110 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 le? All examples I have seen create the database on the y. Jakub Pomykała

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 38/110 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”);

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 39/110 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 db le = new File(myPath); //checkdb = SQLiteDatabase.openDatabase(myPath,null,SQLiteDatabase.OPEN_READWRITE); checkdb = db le.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);

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 40/110 // Path to the just created empty db String out lename = 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 input le to output le byte[] buffer = new byte[1024]; int length; while ((length = myinput.read(buffer))>0) { myoutput.write(buffer,0,length); } //Close the streams myoutput. ush(); 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(); } }

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 41/110 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? David E. Barrera This is kinda tricky… ListView uses a layout to ll 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…

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 42/110 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 in nite 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

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 43/110 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 rst 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:

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 44/110 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 De nition 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.

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 45/110 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 le 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 in nite 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 rst it will executed rst ) 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.

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 46/110 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 le 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 le. An attempt to insert new data that would cause the database le 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 le manually,

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 47/110 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. Tan Woon How You can have a look on this http://instinctcoder.com/how-to-browse-android-emulator-sqlitedatabase/ David E. Barrera database is saved in internal memory… Hasan Rahman Sawan Hi, I am getting this error: [2014-01-07 09:49:56 – Dex Loader] Unable to execute dex: java.nio.BufferOver owException. 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.BufferOver owException. 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[]?

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 48/110 AyemMadScientist Oh hey, im the OP. Already found the solution, and if you guys interested have a look: http://dj- android.blogspot.in/2012/10/android-show-data-from-sqlite-db-into.html 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

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 49/110 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. George Imerlishvili // 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

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 50/110 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: . .

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 51/110 . int count = cursor.getCount(); cursor.close(); // return count return count; Shazwan I can’t nd the database le. 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 bene cial, I’d sure hate to lose a database with 100 contacts in it just because I decided to add a eld for a 2nd phone number. Is there an easy way to upgrade a table to add a eld without wiping the previous data? Mehmet Yildiz if we use existing database and wants to upgrade database what should write in upgrade methods

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 52/110 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 nd 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 nd the .db le??

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 53/110 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 rst 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 . Cnu Federer Hi, thanks for neat tutorial. I’m unable to see the created db les 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);

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 54/110 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 Milad Nozari Thanks, good tutorial. But may I ask why are you using: Integer.parseInt(cursor.getString(0));

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 55/110 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 de nition 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,

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 56/110 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 unde ned 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 Asad Ali Jogi 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?

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 57/110 How can I do that? I have make a class getter Setter and databasehandler which extends SQLiteOpenHelper? Jongsik Very useful article! Thank you Ravi Tamada 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?

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 58/110 Anas Nice article, very useful! NewtoAndroid The type java.lang.Object cannot be resolved. It is indirectly referenced from required .class les – The type java.lang.String cannot be resolved. It is indirectly referenced from required .class les Above error in DatabaseHandler.java le 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

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 59/110 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 ne.. 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

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 60/110 never Forget to close the Cursor in method getAllContact! Russelius Ernestius some methods did not Close database or the Cursor… Jason Flaherty 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 Jakub Šerých Thanks for perfect tutorial. I have two questions: 1) How to prepare the SQLlite database and and ll 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 Ravi Tamada 1. For the rst 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.

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 61/110 2. For exporting the db to SDCard, you can try below link http://stackover ow.com/questions/6540906/android-simple-export-and-import-of-sqlite-database Jakub Šerých Thanks! It seems perfect. Exporting/importing also solves the rst question, as I can create db and ll 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 ne. Guest

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 62/110 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 swhp wow thanks ravi, it’s very simple but useful tutorial Benz Narankz Hey Ravi Tamada, I have a database app that stores users’ names, phone numbers and email addresses, it works ne 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. abhishek garg numberFromDatabase = “111-333-222-4”; String uri = “tel:” + numberFromDatabase.trim() ; Intent intent = new Intent(Intent.ACTION_CALL);

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 63/110 intent.setData(Uri.parse(uri)); startActivity(intent); name you did very well ……… Appkart In Hi Ravi , Some modi cation 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)) ??? Ravi Tamada Yeah, my mistake

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 64/110 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 ?

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 65/110 Chetan if I want to store data in sqlite le for permenate storage of data then how i connect to that le where i store that le SQLITE le Gaurav when i write statement that give me the error List contacts = mDb.getAllStudentList(); The method getAllStudentList() is unde ned 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 eld 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 rst created. */ ArrayList<HashMap> contactList; private static nal String TAG_NAME = “name”; @Override public void onCreate(Bundle savedInstanceState) {

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 66/110 super.onCreate(savedInstanceState); setContentView(R.layout.main); ListView test = (ListView) ndViewById(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);

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 67/110 } 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 con rmation email from you. BLAH Can someone give me the example of deleting the user?? Abhay `this.getReadableDatabase(); makes the app to crash Ravi Tamada Check the LogCat for errors. Edward Lim Just wanted to give a huge shoutout to you and your work, Its all thanks to you that I nally 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!!!

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 68/110 https://play.google.com/store/apps/details?id=com.workoutlog.elcsgen.thesimpleworkoutlog Midhun 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.

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 69/110 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! Ravi Tamada Yeah. Your code is correct. 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 gure 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

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 70/110 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. rstapp.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. rstapp.sqliteexample;

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 71/110 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 nal int DATABASE_VERSION = 1; public static nal String DATABASE_NAME = “products.db”; public static nal String TABLE_PRODUCTS = “products”; public static nal String COLUMN_ID = “_id”; public static nal 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); }

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 72/110 //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”;

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 73/110 } while (cursor.moveToNext()); } db.close(); return dbString; } } Here is my MainActivity.java code package com. rstapp.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) ndViewById(R.id.buckysInput); buckysText = (TextView) ndViewById(R.id.buckysText); dbHandler = new MyDBHandler(this, null ,null ,1);

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 74/110 printDatabase(); } /*@Override public boolean onCreateOptionsMenu(Menu menu) { // In ate the menu; this adds items to the action bar if it is present. getMenuIn ater().in ate(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 Simpli ableIfStatement 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){

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 75/110 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 elds 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;

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 76/110 import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; import android.util.Log; public class MySQLiteHelper extends SQLiteOpenHelper{ public static nal String TABLE_DATA = “data”; public static nal String COLUMN_ID = “_id”; public static nal String COLUMN_ROUTENAME = “routeName”; public static nal String COLUMN_START_LATITUDE = “start_latitude”; public static nal String COLUMN_START_LONGITUDE = “start_longitude”; public static nal String COLUMN_STOP_LATITUDE = “stop_latitude”; public static nal String COLUMN_STOP_LONGITUDE = “stop_longitude”; public static nal String COLUMN_COLORFLAG = “color”; private static nal String DATABASE_NAME = “data.db”; private static nal int DATABASE_VERSION = 1; // Database creation sql statement private static nal String DATABASE_CREATE = “create table ” + TABLE_DATA + “(” + COLUMN_ID + ” integer primary key autoincrement, ” + COLUMN_ROUTENAME + ” text not null,”

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 77/110 + 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 rst 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);

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 78/110 onCreate(db); } } Arpit Thanks for the tutorial ravi sir but i wanna a know how to add .sql le in to the our SQLite database Please help me suggest any link to do that and sorry for my english Nirav Kalola Very nice tutorial. thanks for sharing Ravi Tamada 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 Ravi Tamada 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 .

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 79/110 Ravi Tamada 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 rst 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, Nirav Kalola Very nice tutorial Shreedhar090 Thanks a lot man… Ravi Tamada You are welcome 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

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 80/110 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 Luth M Nabil Thanks for the Sample Graham Washbrook Thank you. Any reason why db.close() is not called in some of the CRUD operations? Ravi Tamada Yeah, please add it before return. Also I suggest you create a singleton class to initiate the sqlite. Marcus Silva Thank You Soooo much !!! Great tutorial, well explained ! KC Raju Vysyaraju it’s great and simple. Thank you orence cosmas This is awesome! Thanks Amit Jayant Thanks a lot! ‫ارﺣﻢ‬ i have error in DatabaseHandler ? why? FaisalHyder As salam o alaikum. Error, where? which error? Be speci c brother..

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 81/110 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 le or pdf le? 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();

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 82/110 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? Ravi Tamada 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

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 83/110 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 ll something in that. Hitesh Danidhariya make an empty constructor Anto Navis 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 ) ndViewById(R.id.listView); List contacts = db.getAllContacts(); ArrayAdapter adapter = new ArrayAdapter(this,android.R.layout.simple_list_item_1,contacts);

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 84/110 list.setAdapter(adapter) //this would work ospfkpoes Its showing the list in hexadecimal values.. cant gure 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 StackOver ow. I know we have to split the arraylist and insert it one by one. BUT, how do i store it in the table ? Ravi Tamada 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”,

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 85/110 “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” ] Burak Cakir Hi Suhail, It’s late but it might work for someone else. You should store your ArrayList as Json string. Then when you need it, you can convert JSON string into Java objects by using GSON easily. Suhail Parvez I have posted my solution here . (http://stackover ow.com/questions/39899007/insert-arraylist-in-sqlite-database? noredirect=1#comment67084738_39899007) 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.

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 86/110 Leo My good sir might I ask where is the xml le or how can i connect the database and xml le 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 xed 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();

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 87/110 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 Ravi Tamada Whats the code you are trying? Keshav Tiwari @ravi8x:disqus Sir I have the same prob. Its my rst 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 retro t??? 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..

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 88/110 { //rest of the code….. int count=cursor.getCount(); cursor.close(); return count; } Ravi Tamada Hi Chris Thanks for pointing that out. Try reading Realm Database has it has more advantages over SQLite. http://www.androidhive.info/2016/05/android-working-with-realm-database-replacing-sqlite-core- data/ Chris Ryce Can anyone tell me how to use this updateContact() function. And what integer value it is returning? Ravi Tamada Hi Chris The updateContact() takes Contact object which you fetch using getContact() method. Modify the values of contact object and pass it to updateContact(). Have a look at Realm Database. It’s much easier. http://www.androidhive.info/2016/05/android-working-with-realm-database-replacing-sqlite-core- data/ Chris Ryce Thanks for your reply Ravi Tamada Chris Ryce I have one more question. I have successfully applied the deleteContact() function but when I further

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 89/110 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..

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 90/110 Ravi Tamada You are welcome Aditya Singh you are awesome Ravi Tamada 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();

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 91/110 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?

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 92/110 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 nd 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. Ravi Tamada Hi Shahzib I have another article that explains making the urls public. Check the below http://www.androidhive.info/2015/03/android-hosting-php-mysql-restful-services-to-digitalocean/ Once you have good picture about connecting to php server, make sure that you are going through the below article too. http://www.androidhive.info/2014/01/how-to-create-rest-api-for-android-app-using-php-slim-and- mysql-day-12-2/ 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 nd 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 . Ravi Tamada 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/.

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 93/110 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 de ne the SQLite and a DAO database to work with the Contact. — ContactsDataSource.java @BindDataSource(dao = {DaoContacts.class}, leName = “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

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 94/110 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);

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 95/110 // 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 le 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 Ravi Tamada I am glad you liked it Simon T what parameter do i pass to method deleteContact()

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 96/110 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 Ravi Tamada Sama sama 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/38519a4a5f06b2c9ad991cadde40f7a1377ca8dd256411586 452b6ba9f2aeed7.png Ravi Tamada 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. Ravi Tamada

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 97/110 ContentProviders are messy and dif cult when you have multiple tables / foreign keys. Have you tried using Realm? http://www.androidhive.info/2016/05/android-working-with-realm-database-replacing-sqlite-core- data/ 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!

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 98/110 syed shahid How i update a speci c 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) ndViewById(R.id.b_login); USERNAME = (EditText) ndViewById(R.id.user_name); USERPASS = (EditText) ndViewById(R.id.user_pass); Login.setOnClickListener(new OnClickListener() { @Override public void onClick(View arg0) {

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 99/110 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(); nish(); } else { Toast.makeText(getApplicationContext(), “Login Failed—- “, Toast.LENGTH_LONG).show(); nish(); } } 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;

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 100/110 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); nish(); } else { Toast.makeText(getApplicationContext(), “Login Failed—- “, Toast.LENGTH_LONG).show(); nish(); } } 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;

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 101/110 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_ lter”); Bundle B = new Bundle(); B.putString(“user_name”, NAME); i.putExtras(B); startActivity(i); nish(); } else { Toast.makeText(getApplicationContext(), “Login Failed—- “, Toast.LENGTH_LONG).show(); nish(); } //Intent i = new Intent(“delete_ lter”); //startActivity(i); } } }); } } //Registration REG.setOnClickListener(new OnClickListener() { @Override public void onClick(View v) {

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 102/110 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(); nish(); } } }); //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);

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 103/110 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(); nish(); } 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”); }

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 104/110 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();

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 105/110 values.put(TableData.TableInfo.USER_NAME, new_user_name); SQ.update(TableData.TableInfo.TABLE_NAME, values, selection, args); } Kinjal Makwana how to get database le ? i see the /data/ folder blank Elbert you must root the phone to be able to acccess system les 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

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 106/110 return c; } annie you didnt db.close() for some functions such as getAllContacts Ravi Tamada 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 rst 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 nd the database les. 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 lepath Ravi Tamada Correct. Sachin Gupta

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 107/110 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. Alexander Bernat So easy, that’s what i wanted to see in my simple app! Thanks a lot! Ravi Tamada You are welcome 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 ? Ravi Tamada 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 ^-^

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 108/110 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 eld cannot be referenced from a static context” SSKWEB Android thanks a lot… your code is crystal clear Ravi Tamada Cheers! SEARCH HERE Type and hit enter... CATEGORIES App (18) Beginner (44) Cloud Connectivity (51) Database (12) Firebase (8) Material Design (16) 

10/16/2017 Android SQLite Database Tutorial https://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ 109/110 Android SQLite Database Tutorial - 1,746,816 views How to connect Android with PHP, MySQL - 1,697,874 views Android JSON Parsing Tutorial - 1,590,887 views Android Push Noti cations using Firebase Cloud Messaging FCM & PHP - 1,519,161 views Android Sliding Menu using Navigation Drawer - 1,451,918 views Android Login and Registration with PHP, MySQL and SQLite - 1,289,256 views Android Custom ListView with Image and Text - 1,129,922 views Android GPS, Location Manager Tutorial - 960,240 views Android Tab Layout with Swipeable Views - 838,427 views Android Expandable List View Tutorial - 778,016 views UI & UX (33) WE’RE SOCIAL POPULAR ANDROID TUTORIALS

Android sq lite database tutorial

More Related Content

What's hot

Similar to Android sq lite database tutorial

More from maamir farooq

Recently uploaded

Android sq lite database tutorial