Tuesday, August 2, 2011

Database Connection in Android

package com.comp1.android.database;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseConnectionAPI extends SQLiteOpenHelper{

//The Android's default system path of your application database.
private final static String DB_PATH = "/data/data/com.comp1.android.database/databases/";

private final static String DB_NAME = "fruitsmoothies.sqlite";

private final Context myContext;

private static SQLiteDatabase db;

/**
* Constructor
* Takes and keeps a reference of the passed context in order to access to the application assets and resources.
* @param context
*/
public DatabaseConnectionAPI(Context context)
{

super(context, DB_NAME, null, 1);
this.myContext = context;
}

/**
* Creates a empty database on the system and rewrites it with your own database.
* */
public void createDataBase() throws IOException
{

boolean dbExist = checkDataBase();

if(dbExist)
{
//do nothing - database already exist
}
else
{

//By calling this method and empty database will be created into the default system path
//of your application so we are gonna be able to overwrite that database with our database.
this.getReadableDatabase();

try
{
copyDataBase();
}
catch (IOException e)
{

throw new Error("Error copying database");
}
}
}

/**
* Check if the database already exist to avoid re-copying the file each time you open the application.
* @return true if it exists, false if it doesn't
*/
private boolean checkDataBase(){

SQLiteDatabase checkDB = null;
try{
String myPath = DB_PATH + DB_NAME;
checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);

}catch(SQLiteException e){
//database does't exist yet.
}
if(checkDB != null){
checkDB.close();
}
return checkDB != null ? true : false;
}

/**
* Copies your database from your local assets-folder to the just created empty database in the
* system folder, from where it can be accessed and handled.
* This is done by transfering bytestream.
* */
private void copyDataBase() throws IOException{

//Open your local db as the input stream
InputStream myInput = myContext.getAssets().open(DB_NAME);

// Path to the just created empty db
String outFileName = DB_PATH + DB_NAME;

//Open the empty db as the output stream
OutputStream myOutput = new FileOutputStream(outFileName);

//transfer bytes from the inputfile to the outputfile
byte[] buffer = new byte[2048];
int length;
while ((length = myInput.read(buffer))>0){
myOutput.write(buffer, 0, length);
}

//Close the streams
myOutput.flush();
myOutput.close();
myInput.close();

}

public void openDataBase() throws SQLException{
try
{
db.close();
}
catch(Exception e)
{
System.out.println("no database connected to close");
}
//Open the database
String myPath = DB_PATH + DB_NAME;
db = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);

}

@Override
public synchronized void close() {
if(db != null)
db.close();
super.close();
}

@Override
public void onCreate(SQLiteDatabase db) {

}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

}
/**
* Use this function to set the value of a particular column
*
* @param columnName The column name whose value is to be changed
* @param newColumnValue The value to be replaced in the column
* @param whereColumnName The column name to be compared with the where clause
* @param whereColumnValue The value to be compared in the where clause
*/
void onUpdateSet(String columnName, String newColumnValue, String[] whereColumnName, String[] whereColumnValue){
String expanded_ColumnNames = new String(whereColumnName[0]);
String expanded_ColumnValues = new String(whereColumnValue[0]);
for(int i=1;i {
expanded_ColumnNames = expanded_ColumnNames+","+whereColumnName[i];
expanded_ColumnValues = expanded_ColumnValues+","+whereColumnValue[i];
}
try
{
openDataBase();
db.execSQL("update recipe set \""+columnName+"\" = \""+newColumnValue+"\" where \""+expanded_ColumnNames+"\" = \""+expanded_ColumnValues+"\"");
}
catch(Exception e)
{
System.out.println("Update couldnt complete "+e);
}

}

/**
* Query the given table, returning a Cursor over the result set.
*
* @param table The table name to compile the query against.
* @param columns A list of which columns to return. Passing null will return all columns,
* which is discouraged to prevent reading data from storage that isn't going to be used.
* @param selection A filter declaring which rows to return, formatted
* as an SQL WHERE clause (excluding the WHERE itself). Passing null
* will return all rows for the given table.
* @param selectionArgs You may include ?s in selection, which will be
* replaced by the values from selectionArgs, in order that they appear
* in the selection. The values will be bound as Strings.
* @param groupBy A filter declaring how to group rows, formatted as an
* SQL GROUP BY clause (excluding the GROUP BY itself). Passing null
* will cause the rows to not be grouped.
* @param having A filter declare which row groups to include in the
* cursor, if row grouping is being used, formatted as an SQL HAVING
* clause (excluding the HAVING itself). Passing null will cause all
* row groups to be included, and is required when row grouping is
* not being used.
* @param orderBy How to order the rows, formatted as an SQL
* ORDER BY clause (excluding the ORDER BY itself). Passing
* null will use the default sort order, which may be unordered.
* @return A Cursor object, which is positioned before the first entry
*/
Cursor onQueryGetCursor(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy)
{
Cursor query = null;
try
{
openDataBase();
query = db.query(table, columns, selection, selectionArgs, groupBy, having, orderBy);
System.out.println("@@@@@ Query is :"+query);
}
catch(Exception e)
{
System.out.println("Query couldnt complete "+e);
}
return query;
}
/**
* Use this method to search a particular String in the provided field.
*
*
* @param columns The array of columns to be returned
* @param table The table name
* @param whereColumn The where clause specifying a particular columns
* @param keyword The keyword which is to be searched
*
* @return The cursor containing the result of the query
*/
Cursor onSearchGetCursor(String[] columns, String table, String[] whereColumn, String keyword)
{
String expColumns = new String(columns[0]);
Cursor rawquery=null;
for(int i=1;i expColumns = expColumns+","+columns[i];
try
{
openDataBase();
rawquery = db.rawQuery("SELECT "+expColumns+" from "+table+" where "+whereColumn[0]+" like \"%"+keyword+"%\" or "+whereColumn[1]+" like \"%"+keyword+"%\" or "+whereColumn[2]+" like \"%"+keyword+"%\"", null);
}
catch(Exception e)
{
System.out.println("Raw Query couldnt complete "+e);
}
return rawquery;
}
}

7 comments:

Android app development said...

This is one of the significant and good post.I like your blog tips.Nice to read about your post.
Android app developers

CapDroid said...

Thanks, Android App Development

Najib Puthawala said...

This is very excellent database code and very useful for my applcation......:)

CapDroid said...

Thanks, Najib Puthawala

Najib Puthawala said...

it.s....my pleasure...!!!!!

NihaR Chalishazar said...

thanks to share it.. nice blog..

CapDroid said...

Thanks dear....