Insert

View

Search

Update

Delete

Contact.java
package com.androidhive.androidsqlite;
public class Contact {
//private variables
int _id;
String _name;
String _phone_number;
// Empty constructor
public Contact(){
}
// constructor
public Contact(int id, String name, String _phone_number){
this._id = id;
this._name = name;
this._phone_number = _phone_number;
}
// constructor
public Contact(String name, String _phone_number){
this._name = name;
this._phone_number = _phone_number;
}
// getting ID
public int getID(){
return this._id;
}
// setting id
public void setID(int id){
this._id = id;
}
// getting name
public String getName(){
return this._name;
}
// setting name
public void setName(String name){
this._name = name;
}
// getting phone number
public String getPhoneNumber(){
return this._phone_number;
}
// setting phone number
public void setPhoneNumber(String phone_number){
this._phone_number = phone_number;
}
}
DatabaseHandler.java
public class DatabaseHandler extends SQLiteOpenHelper {
public static class Schema_DB {
private static int DATABASE_VERSION = 5;
private static String DATABASE_NAME = "062";
}
public DatabaseHandler(Context context) {
super(context, Schema_DB.DATABASE_NAME, null, Schema_DB.DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(Schema_Contacts.QUERY_CREATE_TABLE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
if (newVersion > oldVersion) {
db.execSQL(Schema_Contacts.QUERY_DROP_TABLE);
db.execSQL(Schema_Contacts.QUERY_CREATE_TABLE);
}
}
public static class Schema_Contacts {
public static final String TABLE_NAME = "contacts";
public static final String COL1_ID = "_id";
public static final String COL2_NAME = "name";
public static final String COL3_PHONE_NUMBER = "phone_number";
public static final String COL4_EMAIL_ADDRESS = "email_address";
public static final String QUERY_CREATE_TABLE =String.format("CREATE TABLE \"%s\" (\n" +
"\"%s\" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,\n" +
"\"%s\" TEXT NOT NULL,\n" +
"\"%s\" TEXT,\n" +
"\"%s\" TEXT\n" +
");",TABLE_NAME,COL1_ID,COL2_NAME,COL3_PHONE_NUMBER,COL4_EMAIL_ADDRESS);
public static final String QUERY_DROP_TABLE =String.format("DROP TABLE IF EXISTS \"%s\";",TABLE_NAME);
}
}
Contacts.java
public class Contacts {
private DatabaseHandler dbHandler;
public Contacts(DatabaseHandler dbHandler) {
this.dbHandler = dbHandler;
}
public long addContact(Contact contact) {
SQLiteDatabase db = this.dbHandler.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(DatabaseHandler.Schema_Contacts.COL2_NAME, contact.getName());
values.put(DatabaseHandler.Schema_Contacts.COL3_PHONE_NUMBER, contact.getPhoneNumber());
values.put(DatabaseHandler.Schema_Contacts.COL4_EMAIL_ADDRESS, contact.getEmailAddress());
long inserted_id = db.insert(DatabaseHandler.Schema_Contacts.TABLE_NAME, null, values);
db.close();
return inserted_id;
}
public Cursor getContactCursor(int id) {
SQLiteDatabase db = this.dbHandler.getReadableDatabase();
String[] columns = {
DatabaseHandler.Schema_Contacts.COL1_ID,
DatabaseHandler.Schema_Contacts.COL2_NAME,
DatabaseHandler.Schema_Contacts.COL3_PHONE_NUMBER,
DatabaseHandler.Schema_Contacts.COL4_EMAIL_ADDRESS
};
String selection = DatabaseHandler.Schema_Contacts.COL1_ID + " = ?";
String[] selectionArgs = {String.valueOf(id)};
Cursor cursor = db.query(DatabaseHandler.Schema_Contacts.TABLE_NAME, columns,
selection,
selectionArgs, null, null, null, null);
return cursor;
}
public Contact getContact(int id) {
SQLiteDatabase db = this.dbHandler.getReadableDatabase();
String[] columns = {
DatabaseHandler.Schema_Contacts.COL1_ID,
DatabaseHandler.Schema_Contacts.COL2_NAME,
DatabaseHandler.Schema_Contacts.COL3_PHONE_NUMBER,
DatabaseHandler.Schema_Contacts.COL4_EMAIL_ADDRESS
};
String selection = DatabaseHandler.Schema_Contacts.COL1_ID + " = ?";
String[] selectionArgs = {String.valueOf(id)};
Cursor cursor = db.query(DatabaseHandler.Schema_Contacts.TABLE_NAME, columns,
selection,
selectionArgs, null, null, null, null);
if (cursor != null)
cursor.moveToFirst();
Contact contact = new Contact(cursor.getInt(cursor.getColumnIndex(DatabaseHandler.Schema_Contacts.COL1_ID)),
cursor.getString(cursor.getColumnIndex(DatabaseHandler.Schema_Contacts.COL2_NAME)),
cursor.getString(cursor.getColumnIndex(DatabaseHandler.Schema_Contacts.COL3_PHONE_NUMBER)),
cursor.getString(cursor.getColumnIndex(DatabaseHandler.Schema_Contacts.COL4_EMAIL_ADDRESS)));
cursor.close();
db.close();
return contact;
}
public List<Contact> getAllContacts() {
List<Contact> contactList = new ArrayList<Contact>();
String selectQuery = String.format("SELECT * FROM %s", DatabaseHandler.Schema_Contacts.TABLE_NAME);
SQLiteDatabase db = this.dbHandler.getWritableDatabase();
Cursor cursor = db.rawQuery(selectQuery, null);
if (cursor.moveToFirst()) {
do {
Contact contact = new Contact(cursor.getInt(cursor.getColumnIndex(DatabaseHandler.Schema_Contacts.COL1_ID)),
cursor.getString(cursor.getColumnIndex(DatabaseHandler.Schema_Contacts.COL2_NAME)),
cursor.getString(cursor.getColumnIndex(DatabaseHandler.Schema_Contacts.COL3_PHONE_NUMBER)),
cursor.getString(cursor.getColumnIndex(DatabaseHandler.Schema_Contacts.COL4_EMAIL_ADDRESS)));
contactList.add(contact);
} while (cursor.moveToNext());
}
cursor.close();
db.close();
return contactList;
}
public Cursor getAllContactsCursor() {
String selectQuery = String.format("SELECT * FROM %s", DatabaseHandler.Schema_Contacts.TABLE_NAME);
SQLiteDatabase db = this.dbHandler.getWritableDatabase();
Cursor cursor = db.rawQuery(selectQuery, null);
return cursor;
}
public List<Contact> getContactsByName(String name) {
List<Contact> contactList = new ArrayList<Contact>();
SQLiteDatabase db = this.dbHandler.getWritableDatabase();
String[] columns = {
DatabaseHandler.Schema_Contacts.COL1_ID,
DatabaseHandler.Schema_Contacts.COL2_NAME,
DatabaseHandler.Schema_Contacts.COL3_PHONE_NUMBER,
DatabaseHandler.Schema_Contacts.COL4_EMAIL_ADDRESS
};
String selection = DatabaseHandler.Schema_Contacts.COL2_NAME + " LIKE ?";
String[] selectionArgs = {"%" + name + "%"};
Cursor cursor = db.query(DatabaseHandler.Schema_Contacts.TABLE_NAME, columns, selection, selectionArgs, null, null, null);
if (cursor.moveToFirst()) {
do {
Contact contact = new Contact(cursor.getInt(cursor.getColumnIndex(DatabaseHandler.Schema_Contacts.COL1_ID)),
cursor.getString(cursor.getColumnIndex(DatabaseHandler.Schema_Contacts.COL2_NAME)),
cursor.getString(cursor.getColumnIndex(DatabaseHandler.Schema_Contacts.COL3_PHONE_NUMBER)),
cursor.getString(cursor.getColumnIndex(DatabaseHandler.Schema_Contacts.COL4_EMAIL_ADDRESS)));
contactList.add(contact);
} while (cursor.moveToNext());
}
cursor.close();
db.close();
return contactList;
}
public Cursor getContactsByNameCursor(String name) {
SQLiteDatabase db = this.dbHandler.getWritableDatabase();
String[] columns = {
DatabaseHandler.Schema_Contacts.COL1_ID,
DatabaseHandler.Schema_Contacts.COL2_NAME,
DatabaseHandler.Schema_Contacts.COL3_PHONE_NUMBER,
DatabaseHandler.Schema_Contacts.COL4_EMAIL_ADDRESS
};
String selection = DatabaseHandler.Schema_Contacts.COL2_NAME + " LIKE ?";
String[] selectionArgs = {name};
Cursor cursor = db.query(DatabaseHandler.Schema_Contacts.TABLE_NAME, columns, selection, selectionArgs, null, null, null);
return cursor;
}
public int getContactsCount() {
String countQuery = "SELECT * FROM " + DatabaseHandler.Schema_Contacts.TABLE_NAME;
SQLiteDatabase db = this.dbHandler.getReadableDatabase();
Cursor cursor = db.rawQuery(countQuery, null);
int count = cursor.getCount();
cursor.close();
db.close();
return count;
}
public int updateContact(Contact contact) {
SQLiteDatabase db = this.dbHandler.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(DatabaseHandler.Schema_Contacts.COL2_NAME, contact.getName());
values.put(DatabaseHandler.Schema_Contacts.COL3_PHONE_NUMBER, contact.getPhoneNumber());
values.put(DatabaseHandler.Schema_Contacts.COL4_EMAIL_ADDRESS, contact.getEmailAddress());
int rows_affected = db.update(DatabaseHandler.Schema_Contacts.TABLE_NAME, values,
DatabaseHandler.Schema_Contacts.COL1_ID + " = ?",
new String[]{String.valueOf(contact.getId())});
db.close();
return rows_affected;
}
public int deleteContact(Contact contact) {
SQLiteDatabase db = this.dbHandler.getWritableDatabase();
int rows_affected = db.delete(DatabaseHandler.Schema_Contacts.TABLE_NAME,
DatabaseHandler.Schema_Contacts.COL1_ID + " = ?",
new String[]{String.valueOf(contact.getId())});
db.close();
return rows_affected;
}
}
References
https://github.com/mhdr/AndroidSamples/tree/master/062
http://www.androidhive.info/2011/11/android-sqlite-database-tutorial/
https://www.youtube.com/watch?v=38DOncHIazs&t=1510s&list=PLshdtb5UWjSp0879mLeCsDQN6L73XBZTk&index=34
https://www.youtube.com/watch?v=ahE8bQRD4f0&t=3s&list=PLshdtb5UWjSp0879mLeCsDQN6L73XBZTk&index=35
https://www.youtube.com/watch?v=V4FqE83K1n0&list=PLshdtb5UWjSp0879mLeCsDQN6L73XBZTk&index=36
https://www.youtube.com/watch?v=Im6oY8QSVHU&list=PLshdtb5UWjSp0879mLeCsDQN6L73XBZTk&index=37
https://www.youtube.com/watch?v=HSTt_M4bpBY&list=PLshdtb5UWjSp0879mLeCsDQN6L73XBZTk&index=38
https://www.youtube.com/watch?v=g2x1lzBKB8M&list=PLshdtb5UWjSp0879mLeCsDQN6L73XBZTk&index=39