Handling SQLite Database from Android Programatically

Hi guys, first of all let me wish you all “A very Happy New Year”, i was working on a android project from couple of week ends so thought of putting it on my blog so that it can help some one and save his time.Android provides several ways to store user and application data. SQLite is one way of the way to store user data. SQLite is very light weight database which comes with Android OS. In this tutorial I’ll be discussing how to write classes to handle all SQLite operations.

Note: please follow the comments in the code carefully, this article is pretty simple
please find the below is the structure for the table.

AcctNo, Acct Holder Name, Acct Type, Bank Name, Created Date, amount

in order to hold the data between calls we can create the same old POJO classes, below is the OpeningBalance bean

/**
 * 
 */
package com.spark.android.pocketpassbook.model;

/**
 * @author Sony
 * 
 */
public class OpeningBalanceModel {

	private String acctNo;
	private String bankName;
	private String acctHdrName;
	private String acctType;
	private String openingDate;
	private String amount;

	/**
	 * @return the acctNo
	 */
	public String getAcctNo() {
		return acctNo;
	}

	/**
	 * @param acctNo
	 *            the acctNo to set
	 */
	public void setAcctNo(String acctNo) {
		this.acctNo = acctNo;
	}

	/**
	 * @return the bankName
	 */
	public String getBankName() {
		return bankName;
	}

	/**
	 * @param bankName
	 *            the bankName to set
	 */
	public void setBankName(String bankName) {
		this.bankName = bankName;
	}

	/**
	 * @return the acctHdrName
	 */
	public String getAcctHdrName() {
		return acctHdrName;
	}

	/**
	 * @param acctHdrName
	 *            the acctHdrName to set
	 */
	public void setAcctHdrName(String acctHdrName) {
		this.acctHdrName = acctHdrName;
	}

	/**
	 * @return the acctType
	 */
	public String getAcctType() {
		return acctType;
	}

	/**
	 * @param acctType
	 *            the acctType to set
	 */
	public void setAcctType(String acctType) {
		this.acctType = acctType;
	}

	/**
	 * @return the openingDate
	 */
	public String getOpeningDate() {
		return openingDate;
	}

	/**
	 * @param openingDate
	 *            the openingDate to set
	 */
	public void setOpeningDate(String openingDate) {
		this.openingDate = openingDate;
	}

	/**
	 * @return the amount
	 */
	public String getAmount() {
		return amount;
	}

	/**
	 * @param amount
	 *            the amount to set
	 */
	public void setAmount(String amount) {
		this.amount = amount;
	}

}

once we are done with the bean class, now its time to write the database class, which can directly handle the SQLite db. now in order to handle all database CRUD(Create, Read, Update and Delete) operations we need to write our own class which will extend from a super class SQLiteOpenHelper

/**
 * 
 */
package com.spark.android.pocketpassbook.dbhandlers;

import java.util.ArrayList;
import java.util.List;

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

import com.spark.android.pocketpassbook.model.OpeningBalanceModel;

/**
 * @author Sony
 * 
 */
public class DatabaseHandler extends SQLiteOpenHelper {

	// All Static variables
	// Database Version
	private static final int DATABASE_VERSION = 1;

	// Database Name
	private static final String DATABASE_NAME = "pocket_passbook";

	// opening_balance table name
	private static final String TABLE_OPENING_BALANCE = "opening_balance";

	// opening_balance table Columns names
	private static final String KEY_ID = "id";
	private static final String BANK_NAME = "bank_name";
	private static final String ACCT_HDR_NAME = "aact_hdr_name";
	private static final String ACCT_NUM = "aact_num";
	private static final String ACCT_TYPE = "aact_type";
	private static final String OPENING_DATE = "opening_date";
	private static final String AMOUNT = "amount";

	public DatabaseHandler(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	//onCreate method will be called for the first time in the application is deployed 
	@Override
	public void onCreate(SQLiteDatabase sqLiteDatabase) {

		String CREATE_OPENING_BAL_TABLE = "CREATE TABLE IF NOT EXISTS "
				+ TABLE_OPENING_BALANCE + "(" + KEY_ID
				+ " INTEGER PRIMARY KEY AUTOINCREMENT," + BANK_NAME + " TEXT,"
				+ ACCT_HDR_NAME + " TEXT," + ACCT_NUM + " TEXT," + ACCT_TYPE
				+ " TEXT," + OPENING_DATE + " TEXT" + ")";

		sqLiteDatabase.execSQL(CREATE_OPENING_BAL_TABLE);
	}

	// onUpgrade is called once there is a change in structure of the table
 	@Override
	public void onUpgrade(SQLiteDatabase sqLiteDatabase, int arg1, int arg2) {

		// Drop older table if existed
		sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + TABLE_OPENING_BALANCE);

		// Create tables again
		onCreate(sqLiteDatabase);
	}

	public void deleteTable(String param) {

		SQLiteDatabase sqLiteDatabase = this.getWritableDatabase();
		if (param.equalsIgnoreCase("delete")) {
			// DELETE DATA FROM table if existed
			sqLiteDatabase.execSQL("DELETE FROM " + TABLE_OPENING_BALANCE);
			// Create tables again
			onCreate(sqLiteDatabase);
		} else if (param.equalsIgnoreCase("drop")) {
			// Drop older table if existed
			sqLiteDatabase.execSQL("DROP TABLE IF EXISTS "
					+ TABLE_OPENING_BALANCE);
			// Create tables again
			onCreate(sqLiteDatabase);
		}

	}

	// Adding new opening balance data
	public boolean addOpeningBalance(OpeningBalanceModel openingBalanceModel) {
		boolean status;
		try {
			SQLiteDatabase db = this.getWritableDatabase();

			ContentValues values = new ContentValues();
			values.put(BANK_NAME, openingBalanceModel.getBankName());
			values.put(ACCT_HDR_NAME, openingBalanceModel.getAcctHdrName());
			values.put(ACCT_NUM, openingBalanceModel.getAcctNo());
			values.put(ACCT_TYPE, openingBalanceModel.getAcctType());
			values.put(OPENING_DATE, openingBalanceModel.getOpeningDate());
			//values.put(AMOUNT, openingBalanceModel.getAmount());

			// Inserting Row
			db.insert(TABLE_OPENING_BALANCE, null, values);
			db.close(); // Closing database connection
			status = true;
		} catch (Exception e) {
			status = false;
			e.printStackTrace();
		}
		return status;
	}

	// Getting All opening balance records
	public List<OpeningBalanceModel> getAllOpeningBalance() {
		List<OpeningBalanceModel> OpeningBalanceModelList = new ArrayList<OpeningBalanceModel>();
		// Select All Query
		String selectQuery = "SELECT  * FROM " + TABLE_OPENING_BALANCE;

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				OpeningBalanceModel openingBalanceModel = new OpeningBalanceModel();
				openingBalanceModel.setBankName(cursor.getString(1));
				openingBalanceModel.setAcctHdrName(cursor.getString(2));
				openingBalanceModel.setAcctNo(cursor.getString(3));
				openingBalanceModel.setAcctType(cursor.getString(4));
				openingBalanceModel.setOpeningDate(cursor.getString(5));

				// Adding contact to list
				OpeningBalanceModelList.add(openingBalanceModel);
			} while (cursor.moveToNext());
		}

		// return contact list
		return OpeningBalanceModelList;
	}

	// Getting All opening balance records
	public List<OpeningBalanceModel> getNameAndAcctType(String acctNo) {
		List<OpeningBalanceModel> OpeningBalanceModelList = new ArrayList<OpeningBalanceModel>();
		// Select All Query
		String selectQuery = "SELECT  * FROM " + TABLE_OPENING_BALANCE
				+ " WHERE " + ACCT_NUM + "=" + acctNo;

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				OpeningBalanceModel openingBalanceModel = new OpeningBalanceModel();
				openingBalanceModel.setBankName(cursor.getString(1));
				openingBalanceModel.setAcctHdrName(cursor.getString(2));
				openingBalanceModel.setAcctNo(cursor.getString(3));
				openingBalanceModel.setAcctType(cursor.getString(4));
				openingBalanceModel.setOpeningDate(cursor.getString(5));

				// Adding contact to list
				OpeningBalanceModelList.add(openingBalanceModel);
			} while (cursor.moveToNext());
		}

		// return contact list
		return OpeningBalanceModelList;
	}

}

in order to run this we should have a main activity and call the DatabaseHandler object from the main activity.
as shown below.


package com.spark.android.pocketpassbook.testing;

import java.util.List;
 
import android.app.Activity;
import android.os.Bundle;
import android.util.Log;
import android.widget.TextView;
 
public class AndroidSQLiteTutorialActivity extends Activity {
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);
         
        DatabaseHandler db = new DatabaseHandler(this);
         
        final OpeningBalanceModel openingBalanceModel = new OpeningBalanceModel();
        // all the values i got it from front end after the form gets filled. 
        // if not we can just hardcode and test it.

        final EditText bankName = (EditText) findViewById(R.id.bankName);
	final EditText acctHdrName = (EditText) findViewById(R.id.acctHdrName);
	final EditText acctNo = (EditText) findViewById(R.id.acctNo);
	final EditText acctType = (EditText) findViewById(R.id.acctType);
	final EditText openingDate = (EditText) findViewById(R.id.openingDate);

        // set the values to model object
        openingBalanceModel.setBankName(bankName.getText()
							.toString().trim());
	openingBalanceModel.setAcctHdrName(acctHdrName.getText()
							.toString().trim());
	openingBalanceModel.setAcctNo(acctNo.getText().toString()
							.trim());
	openingBalanceModel.setAcctType(acctType.getText()
							.toString().trim());
	openingBalanceModel.setOpeningDate(openingDate.getText()
							.toString().trim());
        
        // calling the database handler object by passing the model object to it
        boolean status = openingBalanceDatabaseHandler.addOpeningBalance(openingBalanceModel);
    }
    }
}

Lets meet in my next tutorial (Android Text To Speech)
Happy Coding in Android 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s