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
  2. This blog is the general information for the feature. You got a good work for these blog.We have a developing our creative content of this mind.Thank you for this blog. This for very interesting and useful.

    rpa Training in Chennai

    rpa Training in bangalore

    rpa Training in pune

    blueprism Training in Chennai

    blueprism Training in bangalore

    blueprism Training in pune

    iot-training-in-chennai

    ReplyDelete
  3. Outstanding blog post, I have marked your site so ideally I’ll see much more on this subject in the foreseeable future.
    java training in omr | oracle training in chennai

    java training in annanagar | java training in chennai

    ReplyDelete
  4. Well done! Pleasant post! This truly helps me to discover the solutions for my inquiry. Trusting, that you will keep posting articles having heaps of valuable data. You're the best! 
    python training in OMR
    python training in tambaram
    python training in annanagar

    ReplyDelete
  5. Your good knowledge and kindness in playing with all the pieces were very useful. I don’t know what I would have done if I had not encountered such a step like this.
    Devops training in velachery
    Devops training in annanagar
    Devops training in tambaram
    DevOps online Training

    ReplyDelete
  6. I would really like to read some personal experiences like the way, you've explained through the above article. I'm glad for your achievements and would probably like to see much more in the near future. Thanks for share.
    Blueprism training in Pune

    Blueprism training in Chennai

    ReplyDelete
  7. Whoa! I’m enjoying the template/theme of this website. It’s simple, yet effective. A lot of times it’s very hard to get that “perfect balance” between superb usability and visual appeal. I must say you’ve done a very good job with this.

    Selenium Interview Questions and Answers

    Best Selenium Training in Chennai | Selenium Training Institute in Chennai | Besant Technologies

    Selenium Training in Bangalore | Best Selenium Training in Bangalore

    ReplyDelete
  8. Hmm, it seems like your site ate my first comment (it was extremely long) so I guess I’ll just sum it up what I had written and say, I’m thoroughly enjoying your blog. I as well as an aspiring blog writer, but I’m still new to the whole thing. Do you have any recommendations for newbie blog writers? I’d appreciate it.

    AWS Interview Questions And Answers

    AWS Training in Pune | Best Amazon Web Services Training in Pune

    Amazon Web Services Training in Pune | Best AWS Training in Pune

    AWS Online Training | Online AWS Certification Course - Gangboard

    ReplyDelete

Post a Comment