Friday, November 9, 2012

How to store and retrieve the datas from existing SQLiteDB in Android

Hi everyone, here we are going to learn how to insert and fetch the data's from existing DB.

Place your existing DB in mnt/sdcard/YourFolder /Your.db
Here my DB name is XXX.db and the folder I have used is DBFolder.
This class is for DB.

public class SqliteAdapter extends Activity {

 private static final String TAG = "DatabaseHelper";
 public static final String DATABASE_FILE_PATH = "/sdcard/DBFolder";
 public static final String DATABASE_NAME = "XXX.db";
 private Context context;
 private SQLiteHelper sqLiteHelper;
 private static SQLiteDatabase database;
 static String myPath = DATABASE_FILE_PATH + File.separator + DATABASE_NAME;

 public SqliteAdapter(Context pcontext) {

  try {
   database = SQLiteDatabase.openDatabase(myPath, null,
     SQLiteDatabase.OPEN_READWRITE);
   context = pcontext;
  }
  catch (SQLiteException ex) {
   Log.e(TAG, "error -- " + ex.getMessage(), ex);
  } finally {
  }

 }

 public SqliteAdapter openToRead() throws android.database.SQLException {

  Log.i(TAG, "Open DB");
  sqLiteHelper = new SQLiteHelper(context, DATABASE_NAME, null, 1);
  database = SQLiteDatabase.openDatabase(myPath, null,
    SQLiteDatabase.OPEN_READWRITE);
  return this;

 }

 public SqliteAdapter openToWrite() throws android.database.SQLException {

  Log.i(TAG, "Open DB");
  sqLiteHelper = new SQLiteHelper(context, DATABASE_NAME, null, 1);
  database = SQLiteDatabase.openDatabase(myPath, null,
    SQLiteDatabase.OPEN_READWRITE);
  return this;

 }

 public void close() {

  sqLiteHelper.close();

 }


 public Cursor executeSQLQuery(String sql, String[] selectionArgs) {

  Log.i(TAG, "Open DB");
  sqLiteHelper = new SQLiteHelper(context, DATABASE_NAME, null, 1);
  database = SQLiteDatabase.openDatabase(myPath, null,
    SQLiteDatabase.OPEN_READWRITE);
  Cursor cursor = database.rawQuery(sql, selectionArgs);
  Log.i("DataBaseHelper", "executeSQLQuery cursor " + cursor.getCount());
  database.close();
  return cursor;

 }

 public class SQLiteHelper extends SQLiteOpenHelper {

  public SQLiteHelper(Context context, String name,
    CursorFactory factory, int version) {
   super(context, name, factory, version);

  }

  @Override
  public void onCreate(SQLiteDatabase db) {

   // TODO Auto-generated method stub
   db.execSQL(DATABASE_NAME);

  }

  @Override
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
   // TODO Auto-generated method stub
  }
 }
}

Access the DB class through your main class/Activity

public static final String DATABASE_FILE_PATH = "/sdcard/DBFolder";

public static final String DATABASE_NAME = "XXX.db";

static String myPath = DATABASE_FILE_PATH + File.separator + DATABASE_NAME;

SqliteAdapter mySQLiteAdapter;   //object of DB class

SQLiteHelper sqLiteHelper;



For Inserting the data's in Existing DB:

 
SQLiteDatabase db = SQLiteDatabase.openDatabase(myPath,null, SQLiteDatabase.OPEN_READWRITE); 
String sqlQuery = "insert into tableA (bundle,subject) values (a,b)";

db.execSQL(sqlQuery ); 



To Select/Retrieve data's from DB:

mySQLiteAdapter = new SqliteAdapter(this);

mySQLiteAdapter.openToRead();

mySQLiteAdapter.openToWrite();


Cursor cursor = mySQLiteAdapter.executeSQLQuery(
"select COUNT(*) as Value from tableA where TRIM(UPPER(bundle)) =TRIM(UPPER('"
+ value.trim() + "'))", null);



To Update the Data's:

SQLiteDatabase db=SQLiteDatabase .openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);

String updateSql="UPDATE table_B SET logged_in_status=0";

db.execSQL(updateSql);




 

2 comments: