Android SQLite Database

Hi all, I Struck ed up so many times with the database issues and finally got the solution for all those issues. I am posting those here. Hope it helps you.


public class DatabaseHandler extends SQLiteOpenHelper {



private static final int DATABASE_VERSION = 1;


// Database Name
private static final String DATABASE_NAME = "contactsManager";


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


// Contacts Table Columns names
private static final String KEY_ID = "id";
private static final String KEY_NAME = "name";
private static final String KEY_PH_NO = "phone_number";


public DatabaseHandler(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}


// Creating Tables
@Override
public void onCreate(SQLiteDatabase db) {
String CREATE_CONTACTS_TABLE = "CREATE TABLE " + TABLE_CONTACTS + "("
+ KEY_ID + " INTEGER PRIMARY KEY," + KEY_NAME + " TEXT,"
+ KEY_PH_NO + " TEXT" + ")";
db.execSQL(CREATE_CONTACTS_TABLE);
}


// Upgrading database
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// Drop older table if existed
db.execSQL("DROP TABLE IF EXISTS " + TABLE_CONTACTS);


// Create tables again
onCreate(db);
}


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


// Adding new contact
void addContact(Contact contact) {
SQLiteDatabase db = this.getWritableDatabase();


ContentValues values = new ContentValues();
values.put(KEY_NAME, contact.getName()); // Contact Name
values.put(KEY_PH_NO, contact.getPhoneNumber()); // Contact Phone


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


// Getting single contact
Contact getContact(int id) {
SQLiteDatabase db = this.getReadableDatabase();


Cursor cursor = db.query(TABLE_CONTACTS, new String[] { KEY_ID,
KEY_NAME, KEY_PH_NO }, KEY_ID + "=?",
new String[] { String.valueOf(id) }, null, null, null, null);
if (cursor != null)
cursor.moveToFirst();


Contact contact = new Contact(Integer.parseInt(cursor.getString(0)),
cursor.getString(1), cursor.getString(2));
// return contact
return contact;
}

// Getting All Contacts
public List<Contact> getAllContacts() {
List<Contact> contactList = new ArrayList<Contact>();
// Select All Query
String selectQuery = "SELECT  * FROM " + TABLE_CONTACTS;


SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(selectQuery, null);


// looping through all rows and adding to list
if (cursor.moveToFirst()) {
do {
Contact contact = new Contact();
contact.setID(Integer.parseInt(cursor.getString(0)));
contact.setName(cursor.getString(1));
contact.setPhoneNumber(cursor.getString(2));
// Adding contact to list
contactList.add(contact);
} while (cursor.moveToNext());
}


// return contact list
return contactList;
}


// Updating single contact
public int updateContact(Contact contact) {
SQLiteDatabase db = this.getWritableDatabase();


ContentValues values = new ContentValues();
values.put(KEY_NAME, contact.getName());
values.put(KEY_PH_NO, contact.getPhoneNumber());


// updating row
return db.update(TABLE_CONTACTS, values, KEY_ID + " = ?",
new String[] { String.valueOf(contact.getID()) });
}


// Deleting single contact
public void deleteContact(Contact contact) {
SQLiteDatabase db = this.getWritableDatabase();
db.delete(TABLE_CONTACTS, KEY_ID + " = ?",
new String[] { String.valueOf(contact.getID()) });
db.close();
}




// Getting contacts Count
public int getContactsCount() {
String countQuery = "SELECT  * FROM " + TABLE_CONTACTS;
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery(countQuery, null);
cursor.close();


// return count
return cursor.getCount();
}


}






Contact.java


public class Contact {

//private variables
int _id;
String _name;
String _phone_number;

// Empty constructor
public Contact(){

}
// constructor
public Contact(int id, String name, String _phone_number){
this._id = id;
this._name = name;
this._phone_number = _phone_number;
}

// constructor
public Contact(String name, String _phone_number){
this._name = name;
this._phone_number = _phone_number;
}
// getting ID
public int getID(){
return this._id;
}

// setting id
public void setID(int id){
this._id = id;
}

// getting name
public String getName(){
return this._name;
}

// setting name
public void setName(String name){
this._name = name;
}

// getting phone number
public String getPhoneNumber(){
return this._phone_number;
}

// setting phone number
public void setPhoneNumber(String phone_number){
this._phone_number = phone_number;
}
}


Here you found all the solutions for database issues. 


If you found any errors please let me know.
Thank you.

Comments

  1. need ifo on How to store , delete ,retrieve Images from Database??

    ReplyDelete
    Replies
    1. You need to store the Images in SD card and store the references for the images in SQLite. While retrieving the image you can get the reference from Database and get the Image from the SD card.

      Delete
    2. could you please provide .XML files also and screen shots for the xml's i would like to see how GUI was created please share entire project in zip file for please mail to vijay.maranganti@gmail.com
      I am creating an Android project for my MS studies i have little bit knowledge in coding so based on ur coding i'll develope my project please share it

      Thanks,

      Vijay.

      Delete

Post a Comment