In this Android Example we will learn how to create sqllite database manipulation class. How to open SQLite Database in Syncronize way to Insert, Update, Delete database records.
I am taking an example of storing user data in SQLite database. I am using a table called User to store user data. This table contains three columns id (INT), name (TEXT), email(TEXT).
UserData class with getter and setter methods to create single userdata as an object.
package com.androidexample.model;
public class UserData {
//private variables
int _id;
String _name;
String _email;
// Empty constructor
public UserData(){
}
// constructor
public UserData(int id, String name, String email){
this._id = id;
this._name = name;
this._email = email;
}
// constructor
public UserData(String name, String email){
this._name = name;
this._email = email;
}
// 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 email
public String getEmail(){
return this._email;
}
// setting email
public void setEmail(String email){
this._email = email;
}
/* (non-Javadoc)
* @see java.lang.Object#toString()
*/
@Override
public String toString() {
return "UserInfo [name=" + _name + ", email=" + _email + "]";
}
}
1. Create class DBAdapter inside file DBAdapter.java.
2. Create an inner class in DBAdapter and extend it with SQLiteOpenHelper class.
3. I have taken Inner class DataBaseHelper and extends SQLiteOpenHelper class.
4. After extending DBAdapter class from SQLiteOpenHelper We need to override two methods onCreate() and onUpgrage().
onCreate() : This is called when database is created.
onUpgrade() : This method is called when database version is changed (Upgraded like modifying the table structure, adding constraints to database etc.). see this line in DBAdapter class
// increase by one in case of upgrade database
public static final int DATABASE_VERSION = 1;
/****** Open database for insert,update,delete in syncronized manner *******/
private static synchronized SQLiteDatabase open() throws SQLException {
return DBHelper.getWritableDatabase();
}
DBAdapter.java file
public class DBAdapter {
/******** if debug is set true then it will show all Logcat message *******/
public static final boolean DEBUG = true;
/******************** Logcat TAG ************/
public static final String LOG_TAG = "DBAdapter";
/******************** Table Fields ************/
public static final String KEY_ID = "_id";
public static final String KEY_USER_NAME = "user_name";
public static final String KEY_USER_EMAIL = "user_email";
/******************** Database Name ************/
public static final String DATABASE_NAME = "DB_sqllite";
/**** Database Version (Increase one if want to also upgrade your database) ***/
public static final int DATABASE_VERSION = 1;// started at 1
/** Table names */
public static final String USER_TABLE = "tbl_user";
/******* Set all table with comma seperated like USER_TABLE,ABC_TABLE *******/
private static final String[ ] ALL_TABLES = { USER_TABLE };
/** Create table syntax */
private static final String USER_CREATE = "create table tbl_user
( _id integer primary key autoincrement,
user_name text not null,
user_email text not null
);";
/******************** Used to open database in syncronized way ************/
private static DataBaseHelper DBHelper = null;
protected DBAdapter() {
}
/*********** Initialize database *************/
public static void init(Context context) {
if (DBHelper == null) {
if (DEBUG)
Log.i("DBAdapter", context.toString());
DBHelper = new DataBaseHelper(context);
}
}
/********** Main Database creation INNER class ********/
private static class DataBaseHelper extends SQLiteOpenHelper {
public DataBaseHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
if (DEBUG)
Log.i(LOG_TAG, "new create");
try {
db.execSQL(USER_CREATE);
} catch (Exception exception) {
if (DEBUG)
Log.i(LOG_TAG, "Exception onCreate() exception");
}
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
if (DEBUG)
Log.w(LOG_TAG, "Upgrading database from version" + oldVersion
+ "to" + newVersion + "...");
for (String table : ALL_TABLES) {
db.execSQL("DROP TABLE IF EXISTS " + table);
}
onCreate(db);
}
} // Inner class closed
/***** Open database for insert,update,delete in syncronized manner *****/
private static synchronized SQLiteDatabase open() throws SQLException {
return DBHelper.getWritableDatabase();
}
/****************** General functions*******************/
/********** Escape string for single quotes (Insert,Update) *******/
private static String sqlEscapeString(String aString) {
String aReturn = "";
if (null != aString) {
//aReturn = aString.replace(", );
aReturn = DatabaseUtils.sqlEscapeString(aString);
// Remove the enclosing single quotes ...
aReturn = aReturn.substring(1, aReturn.length() - 1);
}
return aReturn;
}
/********* UnEscape string for single quotes (show data) *******/
private static String sqlUnEscapeString(String aString) {
String aReturn = ";
if (null != aString) {
aReturn = aString.replace(, ");
}
return aReturn;
}
/********************************************************************/
}
INSERT OPERATION :
public static void addUserData(UserData uData) {
// Open database for Read / Write
final SQLiteDatabase db = open();
String name = sqlEscapeString(uData.getName());
String email = sqlEscapeString(uData.getEmail());
ContentValues cVal = new ContentValues();
cVal.put(KEY_USER_NAME, name);
cVal.put(KEY_USER_EMAIL, email);
// Insert user values in database
db.insert(USER_TABLE, null, cVal);
db.close(); // Closing database connection
}
UPDATE OPERATION
// Updating single data
public static int updateUserData(UserData data) {
final SQLiteDatabase db = open();
ContentValues values = new ContentValues();
values.put(KEY_USER_NAME, data.getName());
values.put(KEY_USER_EMAIL, data.getEmail());
// updating row
return db.update(USER_TABLE, values, KEY_ID + " = ?",
new String[] { String.valueOf(data.getID()) });
}
GET RECORDS
// Getting single contact
public static UserData getUserData(int id) {
// Open database for Read / Write
final SQLiteDatabase db = open();
Cursor cursor = db.query(USER_TABLE, new String[] { KEY_ID,
KEY_USER_NAME, KEY_USER_EMAIL }, KEY_ID + "=?",
new String[] { String.valueOf(id) }, null, null, null, null);
if (cursor != null)
cursor.moveToFirst();
UserData data = new UserData(Integer.parseInt(cursor.getString(0)),
cursor.getString(1), cursor.getString(2));
// return user data
return data;
}
// Getting All User data
public static List<UserData> getAllUserData() {
List<UserData> contactList = new ArrayList<UserData>();
// Select All Query
String selectQuery = "SELECT * FROM " + USER_TABLE;
// Open database for Read / Write
final SQLiteDatabase db = open();
Cursor cursor = db.rawQuery ( selectQuery, null );
// looping through all rows and adding to list
if (cursor.moveToFirst()) {
do {
UserData data = new UserData();
data.setID(Integer.parseInt(cursor.getString(0)));
data.setName(cursor.getString(1));
data.setEmail(cursor.getString(2));
// Adding contact to list
contactList.add(data);
} while (cursor.moveToNext());
}
// return user list
return contactList;
}
GET NUMBER OF RECORDS
public static int getUserDataCount() {
final SQLiteDatabase db = open();
String countQuery = "SELECT * FROM " + USER_TABLE;
Cursor cursor = db.rawQuery(countQuery, null);
cursor.close();
// return count
return cursor.getCount();
}
DELETE RECORD
// Deleting single contact
public static void deleteUserData(UserData data) {
final SQLiteDatabase db = open();
db.delete(USER_TABLE, KEY_ID + " = ?",
new String[] { String.valueOf(data.getID()) });
db.close();
}
public class DBAdapter {
/******* if debug is set true then it will show all Logcat message ***/
public static final boolean DEBUG = true;
/********** Logcat TAG ************/
public static final String LOG_TAG = "DBAdapter";
/************ Table Fields ************/
public static final String KEY_ID = "_id";
public static final String KEY_USER_NAME = "user_name";
public static final String KEY_USER_EMAIL = "user_email";
/************* Database Name ************/
public static final String DATABASE_NAME = "DB_sqllite";
/**** Database Version (Increase one if want to also upgrade your database) ****/
public static final int DATABASE_VERSION = 1;// started at 1
/** Table names */
public static final String USER_TABLE = "tbl_user";
/**** Set all table with comma seperated like USER_TABLE,ABC_TABLE ******/
private static final String[ ] ALL_TABLES = { USER_TABLE };
/** Create table syntax */
private static final String USER_CREATE = "create table tbl_user
( _id integer primary key autoincrement,
user_name text not null,
user_email text not null
);";
/********* Used to open database in syncronized way *********/
private static DataBaseHelper DBHelper = null;
protected DBAdapter() {
}
/********** Initialize database *********/
public static void init(Context context) {
if (DBHelper == null) {
if (DEBUG)
Log.i("DBAdapter", context.toString());
DBHelper = new DataBaseHelper(context);
}
}
/********** Main Database creation INNER class ********/
private static class DataBaseHelper extends SQLiteOpenHelper {
public DataBaseHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
if (DEBUG)
Log.i(LOG_TAG, "new create");
try {
db.execSQL(USER_CREATE);
} catch (Exception exception) {
if (DEBUG)
Log.i(LOG_TAG, "Exception onCreate() exception");
}
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
if (DEBUG)
Log.w(LOG_TAG, "Upgrading database from version" + oldVersion
+ "to" + newVersion + "...");
for (String table : ALL_TABLES) {
db.execSQL("DROP TABLE IF EXISTS " + table);
}
onCreate(db);
}
} // Inner class closed
/***** Open database for insert,update,delete in syncronized manner ****/
private static synchronized SQLiteDatabase open() throws SQLException {
return DBHelper.getWritableDatabase();
}
/************* General functions*************/
/*********** Escape string for single quotes (Insert,Update) ********/
private static String sqlEscapeString(String aString) {
String aReturn = "";
if (null != aString) {
//aReturn = aString.replace(", );
aReturn = DatabaseUtils.sqlEscapeString(aString);
// Remove the enclosing single quotes ...
aReturn = aReturn.substring(1, aReturn.length() - 1);
}
return aReturn;
}
/********** UnEscape string for single quotes (show data) ************/
private static String sqlUnEscapeString(String aString) {
String aReturn = ";
if (null != aString) {
aReturn = aString.replace(, ");
}
return aReturn;
}
/********* User data functons *********/
public static void addUserData(UserData uData) {
// Open database for Read / Write
final SQLiteDatabase db = open();
String name = sqlEscapeString(uData.getName());
String email = sqlEscapeString(uData.getEmail());
ContentValues cVal = new ContentValues();
cVal.put(KEY_USER_NAME, name);
cVal.put(KEY_USER_EMAIL, email);
// Insert user values in database
db.insert(USER_TABLE, null, cVal);
db.close(); // Closing database connection
}
// Updating single data
public static int updateUserData(UserData data) {
final SQLiteDatabase db = open();
ContentValues values = new ContentValues();
values.put(KEY_USER_NAME, data.getName());
values.put(KEY_USER_EMAIL, data.getEmail());
// updating row
return db.update(USER_TABLE, values, KEY_ID + " = ?",
new String[] { String.valueOf(data.getID()) });
}
// Getting single contact
public static UserData getUserData(int id) {
// Open database for Read / Write
final SQLiteDatabase db = open();
Cursor cursor = db.query(USER_TABLE, new String[] { KEY_ID,
KEY_USER_NAME, KEY_USER_EMAIL }, KEY_ID + "=?",
new String[] { String.valueOf(id) }, null, null, null, null);
if (cursor != null)
cursor.moveToFirst();
UserData data = new UserData(Integer.parseInt(cursor.getString(0)),
cursor.getString(1), cursor.getString(2));
// return user data
return data;
}
// Getting All User data
public static List<UserData> getAllUserData() {
List<UserData> contactList = new ArrayList<UserData>();
// Select All Query
String selectQuery = "SELECT * FROM " + USER_TABLE;
// Open database for Read / Write
final SQLiteDatabase db = open();
Cursor cursor = db.rawQuery ( selectQuery, null );
// looping through all rows and adding to list
if (cursor.moveToFirst()) {
do {
UserData data = new UserData();
data.setID(Integer.parseInt(cursor.getString(0)));
data.setName(cursor.getString(1));
data.setEmail(cursor.getString(2));
// Adding contact to list
contactList.add(data);
} while (cursor.moveToNext());
}
// return user list
return contactList;
}
// Deleting single contact
public static void deleteUserData(UserData data) {
final SQLiteDatabase db = open();
db.delete(USER_TABLE, KEY_ID + " = ?",
new String[] { String.valueOf(data.getID()) });
db.close();
}
// Getting dataCount
public static int getUserDataCount() {
final SQLiteDatabase db = open();
String countQuery = "SELECT * FROM " + USER_TABLE;
Cursor cursor = db.rawQuery(countQuery, null);
cursor.close();
// return count
return cursor.getCount();
}
} // CLASS CLOSED
Initialize database class and create DataBaseHelper Object.
DBAdapter.init(this);
public class AndroidSqliteExample extends Activity {
@Override
protected void onCreate(Bundle savedInstanceState) {
/******************* Intialize Database *************/
DBAdapter.init(this);
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main_sqlite_example);
// Inserting Contacts
Log.d("Insert: ", "Inserting ..");
DBAdapter.addUserData(new UserData("Ravi", "9100000000"));
DBAdapter.addUserData(new UserData("Srinivas", "9199999999"));
DBAdapter.addUserData(new UserData("Tommy", "9522222222"));
DBAdapter.addUserData(new UserData("Karthik", "9533333333"));
// Reading all contacts
Log.d("Reading: ", "Reading all contacts..");
List<UserData> data = DBAdapter.getAllUserData();
for (UserData dt : data) {
String log = "Id: "+dt.getID()+" ,Name: " + dt.getName() + " ,Phone: " + dt.getEmail();
// Writing Contacts to log
Log.d("Name: ", log);
}
}
}
OUTPUT
Facebook Integration - Android Example Android SharedPreferences Basics Play Video File - Android Example Upload File To Server - Android Example Incomming SMS Broadcast Receiver - Android Example