A Guide to Mobile and Web Technology(LAMP)

Posts tagged ‘db’

Database Manipulations in Android

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

}
Follow

Get every new post delivered to your Inbox.

Join 38 other followers