House Multi

Fedora - Blogger - Code Programming - Database - Design

SQLite: SQLite Database Manipulation Class

,


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.
 

Project Structure :

 
sqlite_data_manipulation_calss_project_sketch.png
 
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).

Structure of UserData table



SQLite Android Example

Create UserData Class

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 + "]";
        }
     
}

Create SQLite Database Handler Class

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() Method 

/****** 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;
    }
     
     
    /********************************************************************/
     
}

Create, Read, Update and Delete Operations

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();
}

Complete DBAdapter.java Class With All Manipulations:


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

USE OF  DBAdapter CLASS


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);
        }
       
    }
}


See Output Log Data

SQLite database creation androd example

SQLite database class

OUTPUT
SQLte database output in android


Ipad Wallpaper

Contact Me