Database Manipulations in Android

Share

The following code snippets heps you to manipulate database operations in Android.

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class UserDatabase{
   
    //Database Related Constants
    public static final String KEY_ROWID = "id";
    public static final String KEY_NAME = "name";
    public static final String KEY_PASSWD = "password";
   
    private static final String DATABASE_NAME = "UserDB";
    private static final String DATABASE_TABLE = "usertable";
    private static final int DATABASE_VERSION = 1;

    long id;
    Cursor c;

    private static final String DATABASE_CREATE =
        "create table "+DATABASE_TABLE+" (id integer primary key autoincrement, "
         +KEY_NAME+" text not null,"+KEY_PASSWD+" text not null);";

    private  final Context context;
    private DatabaseHelper DBHelper;
    private SQLiteDatabase sqlitedatabase;

    public UserDatabase(Context ctext){
        context = ctext;       
    }
   
    //Used to check and insert username and password in android's sqlite table
    public void insertUserDetails(String username, String password){
        try {
            boolean isuserexists = false;
            //check whether the username has an entry already
            isuserexists = checkUserExists(username);
            DBHelper = new DatabaseHelper(context);
            sqlitedatabase = DBHelper.getWritableDatabase();
           
               //insert the username and password in tables
            if(isuserexists == false)   
                insertRow(username, password);
           
            sqlitedatabase.close();
           
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
   
   
    //Function used to check whether a entry with same username available  
    public boolean checkUserExists(String username){
        boolean userexists = false;
        try {
            DBHelper = new DatabaseHelper(context);
            sqlitedatabase = DBHelper.getWritableDatabase();
           
            Cursor c = sqlitedatabase.rawQuery("select * from "+DATABASE_TABLE+" where "+KEY_NAME+"='"+username+"'", null);

            if (c.moveToFirst())
            {
                 do {
                     int index = c.getColumnIndex(KEY_NAME);
                      String name = c.getString(index);
                      if(username.equalsIgnoreCase(name)){
                          userexists = true;
                          sqlitedatabase.close();
                          return userexists;
                      }
                 } while (c.moveToNext());
            }
        } catch (Exception e) {
            e.printStackTrace();
        }  
        sqlitedatabase.close();
        return userexists;
    }
   
    //Function used to update the password for the specified username
    public void update_table(String name, String password){       
        try {
            DBHelper = new DatabaseHelper(context);
            sqlitedatabase = DBHelper.getWritableDatabase();           
            sqlitedatabase.execSQL("update "+DATABASE_TABLE+" set "+KEY_PASSWD+"='"+password+"' where "+KEY_NAME+"='"+name+"'");
            sqlitedatabase.close();
           
        } catch (Exception e) {
            e.printStackTrace();
        }  
    }
   
   
    //Database Helper Class
    private static class DatabaseHelper extends SQLiteOpenHelper
    {
        DatabaseHelper(Context context)
        {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        public void onCreate(SQLiteDatabase db)
        {
            db.execSQL(DATABASE_CREATE);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
        {           
            db.execSQL("DROP TABLE IF EXISTS titles");
            onCreate(db);
        }
    }

    public long insertRow(String name, String password)
    {
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_NAME, name);
        initialValues.put(KEY_PASSWD, password);
        return sqlitedatabase.insert(DATABASE_TABLE, null, initialValues);
    }

    public Cursor getAllRows()
    {
        return sqlitedatabase.query(DATABASE_TABLE, new String[] {
                        KEY_ROWID, KEY_NAME, KEY_PASSWD},
                null, null, null, null, null);
    }

}

About The Author

Katharnavas is the Web, Mobile Application Developer and also a blogger at Katharnavas Designer's Shine. Follow Katharnavas on Twitter for updates, development.