Example of android SQLite database

Let's see the simple example of android sqlite database.

File: Contact.java

1. package com.example.sqlite;  
2. public class Contact {  
3.    int _id;  
4.     String _name;  
5.     String _phone_number;  
6.      public Contact(){   }  
7.     public Contact(int id, String name, String _phone_number){  
8.         this._id = id;  
9.         this._name = name;  
10.         this._phone_number = _phone_number;  
11.     }  
12.    
13.     public Contact(String name, String _phone_number){  
14.         this._name = name;  
15.         this._phone_number = _phone_number;  
16.     }  
17.     public int getID(){  
18.         return this._id;  
19.     }  
20.   
21.     public void setID(int id){  
22.         this._id = id;  
23.     }  
24.   
25.     public String getName(){  
26.         return this._name;  
27.     }  
28.   
29.     public void setName(String name){  
30.         this._name = name;  
31.     }  
32.   
33.     public String getPhoneNumber(){  
34.         return this._phone_number;  
35.     }  
36.    
37.     public void setPhoneNumber(String phone_number){  
38.         this._phone_number = phone_number;  
39.     }  
40. }  

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

File: DatabaseHandler.java

Now, let's create the database handler class that extends SQLiteOpenHelper class and provides the implementation of its methods.

1. package com.example.sqlite;  
2. import java.util.ArrayList;  
3. import java.util.List;  
4.    
5. import android.content.ContentValues;  
6. import android.content.Context;  
7. import android.database.Cursor;  
8. import android.database.sqlite.SQLiteDatabase;  
9. import android.database.sqlite.SQLiteOpenHelper;  
10.    
11. public class DatabaseHandler extends SQLiteOpenHelper {  
12.    private static final int DATABASE_VERSION = 1;  
13.    private static final String DATABASE_NAME = "contactsManager";  
14.     private static final String TABLE_CONTACTS = "contacts";  
15.      private static final String KEY_ID = "id";  
16.     private static final String KEY_NAME = "name";  
17.     private static final String KEY_PH_NO = "phone_number";  
18.    
19.     public DatabaseHandler(Context context) {  
20.         super(context, DATABASE_NAME, null, DATABASE_VERSION);  
21.         //3rd argument to be passed is CursorFactory instance  
22.     }  
23.    
24.     // Creating Tables  
25.     @Override  
26.     public void onCreate(SQLiteDatabase db) {  
27.         String CREATE_CONTACTS_TABLE = "CREATE TABLE " + TABLE_CONTACTS + "("  
28.                 + KEY_ID + " INTEGER PRIMARY KEY," + KEY_NAME + " TEXT,"  
29.                 + KEY_PH_NO + " TEXT" + ")";  
30.         db.execSQL(CREATE_CONTACTS_TABLE);  
31.     }  
32.    
33.     // Upgrading database  
34.     @Override  
35.     public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {  
36.         // Drop older table if existed  
37.         db.execSQL("DROP TABLE IF EXISTS " + TABLE_CONTACTS);  
38.    
39.         // Create tables again  
40.         onCreate(db);  
41.     }  
42.    
43.      // code to add the new contact  
44.      void addContact(Contact contact) {  
45.         SQLiteDatabase db = this.getWritableDatabase();  
46.    
47.         ContentValues values = new ContentValues();  
48.         values.put(KEY_NAME, contact.getName()); // Contact Name  
49.         values.put(KEY_PH_NO, contact.getPhoneNumber()); // Contact Phone  
50.    
51.         // Inserting Row  
52.         db.insert(TABLE_CONTACTS, null, values);  
53.         //2nd argument is String containing nullColumnHack  
54.         db.close(); // Closing database connection  
55.     }  
56.    
57.     // code to get the single contact  
58.     Contact getContact(int id) {  
59.         SQLiteDatabase db = this.getReadableDatabase();  
60.    
61.         Cursor cursor = db.query(TABLE_CONTACTS, new String[] { KEY_ID,  
62.                 KEY_NAME, KEY_PH_NO }, KEY_ID + "=?",  
63.                 new String[] { String.valueOf(id) }, null, null, null, null);  
64.         if (cursor != null)  
65.             cursor.moveToFirst();  
66.    
67.         Contact contact = new Contact(Integer.parseInt(cursor.getString(0)),  
68.                 cursor.getString(1), cursor.getString(2));  
69.         // return contact  
70.         return contact;  
71.     }  
72.    
73.     // code to get all contacts in a list view  
74.     public List<Contact> getAllContacts() {  
75.         List<Contact> contactList = new ArrayList<Contact>();  
76.         // Select All Query  
77.         String selectQuery = "SELECT  * FROM " + TABLE_CONTACTS;  
78.    
79.         SQLiteDatabase db = this.getWritableDatabase();  
80.         Cursor cursor = db.rawQuery(selectQuery, null);  
81.    
82.         // looping through all rows and adding to list  
83.         if (cursor.moveToFirst()) {  
84.             do {  
85.                 Contact contact = new Contact();  
86.                 contact.setID(Integer.parseInt(cursor.getString(0)));  
87.                 contact.setName(cursor.getString(1));  
88.                 contact.setPhoneNumber(cursor.getString(2));  
89.                 // Adding contact to list  
90.                 contactList.add(contact);  
91.             } while (cursor.moveToNext());  
92.         }  
93.    
94.         // return contact list  
95.         return contactList;  
96.     }  
97.    
98.     // code to update the single contact  
99.     public int updateContact(Contact contact) {  
100.         SQLiteDatabase db = this.getWritableDatabase();  
101.    
102.         ContentValues values = new ContentValues();  
103.         values.put(KEY_NAME, contact.getName());  
104.         values.put(KEY_PH_NO, contact.getPhoneNumber());  
105.    
106.         // updating row  
107.         return db.update(TABLE_CONTACTS, values, KEY_ID + " = ?",  
108.                 new String[] { String.valueOf(contact.getID()) });  
109.     }  
110.    
111.     // Deleting single contact  
112.     public void deleteContact(Contact contact) {  
113.         SQLiteDatabase db = this.getWritableDatabase();  
114.         db.delete(TABLE_CONTACTS, KEY_ID + " = ?",  
115.                 new String[] { String.valueOf(contact.getID()) });  
116.         db.close();  
117.     }  
118.    
119.     // Getting contacts Count  
120.     public int getContactsCount() {  
121.         String countQuery = "SELECT  * FROM " + TABLE_CONTACTS;  
122.         SQLiteDatabase db = this.getReadableDatabase();  
123.         Cursor cursor = db.rawQuery(countQuery, null);  
124.         cursor.close();  
125.    
126.         // return count  
127.         return cursor.getCount();  
128.     }  
129.    
130. }  

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

File: MainActivity.java

1. package com.example.sqlite;  
2.   
3. import java.util.List;  
4.   
5. import android.os.Bundle;  
6. import android.app.Activity;  
7. import android.util.Log;  
8. import android.view.Menu;  
9.   
10. public class MainActivity extends Activity {  
11.   
12.     @Override  
13.     protected void onCreate(Bundle savedInstanceState) {  
14.         super.onCreate(savedInstanceState);  
15.         setContentView(R.layout.activity_main);  
16.           
17.         DatabaseHandler db = new DatabaseHandler(this);  
18.            
19.          // Inserting Contacts  
20.         Log.d("Insert: ", "Inserting ..");  
21.         db.addContact(new Contact("Ravi", "9100000000"));  
22.         db.addContact(new Contact("Srinivas", "9199999999"));  
23.         db.addContact(new Contact("Tommy", "9522222222"));  
24.         db.addContact(new Contact("Karthik", "9533333333"));  
25.    
26.         // Reading all contacts  
27.         Log.d("Reading: ", "Reading all contacts..");  
28.         List<Contact> contacts = db.getAllContacts();         
29.    
30.         for (Contact cn : contacts) {  
31.          String log = "Id: "+cn.getID()+" ,Name: " + cn.getName() + " ,Phone: " +   
32.             cn.getPhoneNumber();  
33.         // Writing Contacts to log  
34.         Log.d("Name: ", log);  
35.     }  
36.     }  
37.   
38.     @Override  
39.     public boolean onCreateOptionsMenu(Menu menu) {  
40.         // Inflate the menu; this adds items to the action bar if it is present.  
41.         getMenuInflater().inflate(R.menu.activity_main, menu);  
42.         return true;  
43.     }  
44.   
45. }  

package com.example.sqlite; import java.util.List; import android.os.Bundle; import android.app.Activity; import android.util.Log; import android.view.Menu; public class MainActivity extends Activity { @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); DatabaseHandler db = new DatabaseHandler(this); // Inserting Contacts Log.d("Insert: ", "Inserting .."); db.addContact(new Contact("Ravi", "9100000000")); db.addContact(new Contact("Srinivas", "9199999999")); db.addContact(new Contact("Tommy", "9522222222")); db.addContact(new Contact("Karthik", "9533333333")); // Reading all contacts Log.d("Reading: ", "Reading all contacts.."); List<contact> contacts = db.getAllContacts(); for (Contact cn : contacts) { String log = "Id: "+cn.getID()+" ,Name: " + cn.getName() + " ,Phone: " + cn.getPhoneNumber(); // Writing Contacts to log Log.d("Name: ", log); } } @Override public boolean onCreateOptionsMenu(Menu menu) { // Inflate the menu; this adds items to the action bar if it is present. getMenuInflater().inflate(R.menu.activity_main, menu); return true; } }

---

download this sqlite example

Output:

Open Logcat and see the output. It is the basic example of android sqlite without any GUI.
For GUI application with android SQLite, visit next page.

---

Output: