Android SQLite Database Tutorial

Getting Started with DhamenORM SQLite Android Model & DAO class generator

DhamenDAO Android SQLite models and Data Access Object (DAO) generator. DhamenDAO is very simple tool that will generate all the important database functions that are required by any relational database applications, create, read, update, and delete (CRUD). By generating all the classes needed to access SQLite database, the developers can spare their valuable time to do other important tasks in their application development.

This tool will not generate android basic and important java class models and the data access objects (DAO) as well as the Database Manager to communicate with the SQLite database. DhamenDAO will generate the CRUD function based on existed SQLite database, so you have to create the SQLite database using any SQLite database manager.

Steps to generate Android SQLite java classes are as follows:

Create your SQLite database using your favourite SQLite database manager.
Download and start DhamenDAO android SQLite java class generator.
Enter your database and package information.
Select the tables required to generate android java classes.
Copy generated files to your project.
Configure the database manager.
That is it. Just enjoy the.

( 1 ) Create your SQLite database using your favourite SQLite database manager.

I will be using the following tables to generate the Android SQLite Data Access Objects Classes

CREATE TABLE `Expense` (
	`_id`			INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	`TransName`		TEXT NOT NULL,
	`TransAmount`	REAL NOT NULL,
	`TransDate`		TEXT NOT NULL,
	`TransCategory`	INTEGER NOT NULL
);

CREATE TABLE `Expense` (

`_id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,

`TransName` TEXT NOT NULL,

`TransAmount` REAL NOT NULL,

`TransDate` TEXT NOT NULL,

`TransCategory` INTEGER NOT NULL

);

CREATE TABLE `Category` (
	`_id`			INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	`CategoryName`	TEXT NOT NULL
);

CREATE TABLE `Category` (

`_id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,

`CategoryName` TEXT NOT NULL

);

please watch this tutorial on how to create SQLite database.

( 2 ) Download and start DhamenDAO android SQLite java class generator.

Download the DhamenDAO android sqlite model and data access object generator.

( 3 ) Enter your database and package information.

Select the SQLite database file.
Package Name
Here enter your project package name, this can be found in your Android Manifest file or the gradle file.
Output Path
Output path is the folder where you want the generated classes to be stored. Be careful, all the files in this folder will be DELETED, you have to select a folder that is only dedicated to dhamenORM-generated classes or select empty folder.
Database Name
Database Name is the SQLite database file name that is stored in the Android file system.
Database Version
Number of the database starting at 1; if the database is older, onUpgrade method will be used to upgrade the database; if the database is newer, onDowngrade method will be used to downgrade the database. For more information about SQLiteOpenHelper read the documentation.
Datasource Class Prefix
Generated java classes prefix name. The class prefix will be apply to the Data Access Object only
Datasource Class Postfix
Generated java classes pstfix name. The class postfix will be apply to the Data Access Object only
Generate Db Manager Class
The Database Db Manager class is the only way to communicate between your application and the SQLite database. All the generated classes will be using the Db Manager class in order to insert, update, delete, read from the SQLite database, if you choose not to generate this class, then you have to provide your own Db manger with the same name as the generated Db Manager. (DbManager.java)
Click START button to move to the next step.

( 3 ) Select the tables you want to generate the Android java classes then click generate classes button.

( 4 ) Be careful, all the files in the output folder will be DELETED, you have to select a folder that is only dedicated to dhamenORM-generated classes or select empty folder.

( 5 ) Copy the generated classes to your project.

Here I created folder called DhamenORM and past all the generated classes.

( 6 ) Create new application class that extends the android application and initialize the DbManager class with the application Context as follow. You can also initialize the DbManger class in each activity you want to use the generated classes.

public class ExpenseApplication extends Application {
 
    private static Context context;
 
    @Override
    public void onCreate() {
        super.onCreate();
        context = getApplicationContext();
        DbManager.setConfig(context);
    }
 
    public static Context getContext() {
        return context;
    }
}

public class ExpenseApplication extends Application {

private static Context context;

@Override

public void onCreate() {

super.onCreate();

context = getApplicationContext();

DbManager.setConfig(context);

}

public static Context getContext() {

return context;

}

( 7 ) update your project manifest file and add the new application name that you created.

( 8 ) Finally using the generated classes as follows.

Insert Record.

// get the current date
long nowLong = System.currentTimeMillis()/1000;
int nowInt = (int)nowLong;
 
// declare Expense object
Expenseitems expenseItem = new Expenseitems();
     
// Set the values
expenseItem.setExpname(inputExpenseName.getText().toString());
expenseItem.setExpamount(Double.valueOf(inputExpenseAmount.getText().toString()));
expenseItem.setCompleted(1);
expenseItem.setCreatedon(nowInt);
     
// Call the Expense DAO to insert the record
ExpenseitemsDao.insertRecord(expenseItem);

// get the current date

long nowLong = System.currentTimeMillis()/1000;

int nowInt = (int)nowLong;

// declare Expense object

Expenseitems expenseItem = new Expenseitems();

// Set the values

expenseItem.setExpname(inputExpenseName.getText().toString());

expenseItem.setExpamount(Double.valueOf(inputExpenseAmount.getText().toString()));

expenseItem.setCompleted(1);

expenseItem.setCreatedon(nowInt);

// Call the Expense DAO to insert the record

ExpenseitemsDao.insertRecord(expenseItem);

Update Record.

int expenseId = getIntent().getIntExtra("EXPENSE_ID",0);
long nowLong = System.currentTimeMillis()/1000;
int nowInt = (int)nowLong;
 
// Load the record by id
Expenseitems expenseItem = ExpenseitemsDao.LoadRecordById(expenseId);
 
// do the update and save the record
expenseItem.setExpname(inputExpenseName.getText().toString());
expenseItem.setExpamount(Double.valueOf(inputExpenseAmount.getText().toString()));
expenseItem.setCreatedon(nowInt);
     
// Update the record
ExpenseitemsDao.updateRecord(expenseItem);

int expenseId = getIntent().getIntExtra("EXPENSE_ID",0);

long nowLong = System.currentTimeMillis()/1000;

int nowInt = (int)nowLong;

// Load the record by id

Expenseitems expenseItem = ExpenseitemsDao.LoadRecordById(expenseId);

// do the update and save the record

expenseItem.setExpname(inputExpenseName.getText().toString());

expenseItem.setExpamount(Double.valueOf(inputExpenseAmount.getText().toString()));

expenseItem.setCreatedon(nowInt);

// Update the record

ExpenseitemsDao.updateRecord(expenseItem);

And here is the complete classes that is generated by DhamenORM tool, the Android SQLite Data Access Objest classes

Here is the DbShema.java class which will have all the table and columns and the database information.

package com.dhamen.hadrecyclerview.DhamenDAO;

import android.provider.BaseColumns;

public class DbSchema {
    private static final String TAG = "DbSchema";

    public static final String DATABASE_NAME = "DBExpense.db";
    public static final int DATABASE_VERSION = 1;
    public static final String SORT_ASC = " ASC";
    public static final String SORT_DESC = " DESC";
    public static final String[] ORDERS = {SORT_ASC,SORT_DESC};
    public static final int OFF = 0;
    public static final int ON = 1;

    public static final class Table_Expense implements BaseColumns  { 
        // Table Name
        public static final String TABLE_NAME = "Expense";

        // Table Columns
        public static final String COL__ID = "_id";
        public static final String COL_TRANSNAME = "TransName";
        public static final String COL_TRANSAMOUNT = "TransAmount";
        public static final String COL_TRANSDATE = "TransDate";
        public static final String COL_TRANSCATEGORY = "TransCategory";

        // Create Table Statement
        public static final String CREATE_TABLE = "CREATE TABLE IF NOT EXISTS Expense ( " + 
            COL__ID + " INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,  " + 
            COL_TRANSNAME + " TEXT NOT NULL," + 
            COL_TRANSAMOUNT + " REAL NOT NULL," + 
            COL_TRANSDATE + " TEXT NOT NULL," + 
            COL_TRANSCATEGORY + " INTEGER NOT NULL );";

        // Drop table statement
        public static final String DROP_TABLE = "DROP TABLE IF EXISTS Expense;";

        // Columns list array
        public static final String[] allColumns = {
            COL__ID,
            COL_TRANSNAME,
            COL_TRANSAMOUNT,
            COL_TRANSDATE,
            COL_TRANSCATEGORY };
    }

    public static final class Table_Category implements BaseColumns  { 
        // Table Name
        public static final String TABLE_NAME = "Category";

        // Table Columns
        public static final String COL__ID = "_id";
        public static final String COL_CATEGORYNAME = "CategoryName";

        // Create Table Statement
        public static final String CREATE_TABLE = "CREATE TABLE IF NOT EXISTS Category ( " + 
            COL__ID + " INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,  " + 
            COL_CATEGORYNAME + " TEXT NOT NULL );";

        // Drop table statement
        public static final String DROP_TABLE = "DROP TABLE IF EXISTS Category;";

        // Columns list array
        public static final String[] allColumns = {
            COL__ID,
            COL_CATEGORYNAME };
    }

}

package com.dhamen.hadrecyclerview.DhamenDAO;

import android.provider.BaseColumns;

public class DbSchema {

private static final String TAG = "DbSchema";

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

public static final int DATABASE_VERSION = 1;

public static final String SORT_ASC = " ASC";

public static final String SORT_DESC = " DESC";

public static final String[] ORDERS = {SORT_ASC,SORT_DESC};

public static final int OFF = 0;

public static final int ON = 1;

public static final class Table_Expense implements BaseColumns {

// Table Name

public static final String TABLE_NAME = "Expense";

// Table Columns

public static final String COL__ID = "_id";

public static final String COL_TRANSNAME = "TransName";

public static final String COL_TRANSAMOUNT = "TransAmount";

public static final String COL_TRANSDATE = "TransDate";

public static final String COL_TRANSCATEGORY = "TransCategory";

// Create Table Statement

public static final String CREATE_TABLE = "CREATE TABLE IF NOT EXISTS Expense ( " +

COL__ID + " INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, " +

COL_TRANSNAME + " TEXT NOT NULL," +

COL_TRANSAMOUNT + " REAL NOT NULL," +

COL_TRANSDATE + " TEXT NOT NULL," +

COL_TRANSCATEGORY + " INTEGER NOT NULL );";

// Drop table statement

public static final String DROP_TABLE = "DROP TABLE IF EXISTS Expense;";

// Columns list array

public static final String[] allColumns = {

COL__ID,

COL_TRANSNAME,

COL_TRANSAMOUNT,

COL_TRANSDATE,

COL_TRANSCATEGORY };

}

public static final class Table_Category implements BaseColumns {

// Table Name

public static final String TABLE_NAME = "Category";

// Table Columns

public static final String COL__ID = "_id";

public static final String COL_CATEGORYNAME = "CategoryName";

// Create Table Statement

public static final String CREATE_TABLE = "CREATE TABLE IF NOT EXISTS Category ( " +

COL__ID + " INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, " +

COL_CATEGORYNAME + " TEXT NOT NULL );";

// Drop table statement

public static final String DROP_TABLE = "DROP TABLE IF EXISTS Category;";

// Columns list array

public static final String[] allColumns = {

COL__ID,

COL_CATEGORYNAME };

}

Here is the DbManager.java class that will communicate between your android application and the SQLite database.

package com.dhamen.hadrecyclerview.DhamenDAO;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DbManager {
    private static final String TAG = "DBMANAGER";

    private static DbManager sInstance;

    private static final String LOG_TAG = "DbManager";
    private static long errorId = 0;

    private Context mCtx;
    private DbHelper mDbHelper;
    private SQLiteDatabase mDb;


    public static DbManager setConfig(Context context) {
        if (sInstance==null){
            sInstance = new DbManager(context);}
        return sInstance;
    }


    protected static DbManager getsInstance() {
        return sInstance;
    }


    private DbManager(Context ctx) {
        this.mCtx = ctx;
    }

    protected DbManager() {}

    public DbManager open() {
        mDbHelper = new DbHelper(this);
        mDb = mDbHelper.getWritableDatabase();
        return this;
    }

    public void close() {
        mDbHelper.close();
    }

    public SQLiteDatabase getDatabase() {
        mDbHelper = new DbHelper(this);
        mDb = mDbHelper.getWritableDatabase();
        return mDb;
    }

    private static class DbHelper extends SQLiteOpenHelper {
        private static DbHelper sInstance;

        private static final String LOG_TAG = "DbHelper";
        private DbManager mDbManager;

        public static DbHelper getInstance(DbManager manager) {
            if (sInstance == null) {
                sInstance = new DbHelper(manager);
            }
            return sInstance;
        }


        private DbHelper(DbManager dbmanger) {
            super(dbmanger.mCtx, DbSchema.DATABASE_NAME, null, DbSchema.DATABASE_VERSION);
            mDbManager = dbmanger;
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL(DbSchema.Table_Expense.CREATE_TABLE);
            db.execSQL(DbSchema.Table_Category.CREATE_TABLE);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            db.execSQL(DbSchema.Table_Expense.DROP_TABLE);
            db.execSQL(DbSchema.Table_Category.DROP_TABLE);
            this.onCreate(db);
        }

    }
}

package com.dhamen.hadrecyclerview.DhamenDAO;

import android.content.Context;

import android.database.sqlite.SQLiteDatabase;

import android.database.sqlite.SQLiteOpenHelper;

import android.util.Log;

public class DbManager {

private static final String TAG = "DBMANAGER";

private static DbManager sInstance;

private static final String LOG_TAG = "DbManager";

private static long errorId = 0;

private Context mCtx;

private DbHelper mDbHelper;

private SQLiteDatabase mDb;

public static DbManager setConfig(Context context) {

if (sInstance==null){

sInstance = new DbManager(context);}

return sInstance;

}

protected static DbManager getsInstance() {

return sInstance;

}

private DbManager(Context ctx) {

this.mCtx = ctx;

}

protected DbManager() {}

public DbManager open() {

mDbHelper = new DbHelper(this);

mDb = mDbHelper.getWritableDatabase();

return this;

}

public void close() {

mDbHelper.close();

}

public SQLiteDatabase getDatabase() {

mDbHelper = new DbHelper(this);

mDb = mDbHelper.getWritableDatabase();

return mDb;

}

private static class DbHelper extends SQLiteOpenHelper {

private static DbHelper sInstance;

private static final String LOG_TAG = "DbHelper";

private DbManager mDbManager;

public static DbHelper getInstance(DbManager manager) {

if (sInstance == null) {

sInstance = new DbHelper(manager);

}

return sInstance;

}

private DbHelper(DbManager dbmanger) {

super(dbmanger.mCtx, DbSchema.DATABASE_NAME, null, DbSchema.DATABASE_VERSION);

mDbManager = dbmanger;

}

@Override

public void onCreate(SQLiteDatabase db) {

db.execSQL(DbSchema.Table_Expense.CREATE_TABLE);

db.execSQL(DbSchema.Table_Category.CREATE_TABLE);

}

@Override

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

db.execSQL(DbSchema.Table_Expense.DROP_TABLE);

db.execSQL(DbSchema.Table_Category.DROP_TABLE);

this.onCreate(db);

}

Expense model for the expense table Expense.java

package com.dhamen.hadrecyclerview.DhamenDAO;

import android.os.Bundle;
import java.util.Date;

public class Expense {

    public static final String COL__ID = "_id";
    public static final String COL_TRANSNAME = "TransName";
    public static final String COL_TRANSAMOUNT = "TransAmount";
    public static final String COL_TRANSDATE = "TransDate";
    public static final String COL_TRANSCATEGORY = "TransCategory";

    private Integer m_id;
    private String mTransName;
    private Double mTransAmount;
    private String mTransDate;
    private Integer mTransCategory;

    public Expense() {
    }

    public Expense(Integer _id, String TransName, Double TransAmount, String TransDate, Integer TransCategory) {
        this.m_id = _id;
        this.mTransName = TransName;
        this.mTransAmount = TransAmount;
        this.mTransDate = TransDate;
        this.mTransCategory = TransCategory;
    }

    public Integer get_id() {
        return m_id;
    }

    public void set_id(Integer _id) {
        this.m_id = _id;
    }

    public String getTransName() {
        return mTransName;
    }

    public void setTransName(String TransName) {
        this.mTransName = TransName;
    }

    public Double getTransAmount() {
        return mTransAmount;
    }

    public void setTransAmount(Double TransAmount) {
        this.mTransAmount = TransAmount;
    }

    public String getTransDate() {
        return mTransDate;
    }

    public void setTransDate(String TransDate) {
        this.mTransDate = TransDate;
    }

    public Integer getTransCategory() {
        return mTransCategory;
    }

    public void setTransCategory(Integer TransCategory) {
        this.mTransCategory = TransCategory;
    }


    public Bundle toBundle() { 
        Bundle b = new Bundle();
        b.putInt(COL__ID, this.m_id);
        b.putString(COL_TRANSNAME, this.mTransName);
        b.putDouble(COL_TRANSAMOUNT, this.mTransAmount);
        b.putString(COL_TRANSDATE, this.mTransDate);
        b.putInt(COL_TRANSCATEGORY, this.mTransCategory);
        return b;
    }

    public Expense(Bundle b) {
        if (b != null) {
            this.m_id = b.getInt(COL__ID);
            this.mTransName = b.getString(COL_TRANSNAME);
            this.mTransAmount = b.getDouble(COL_TRANSAMOUNT);
            this.mTransDate = b.getString(COL_TRANSDATE);
            this.mTransCategory = b.getInt(COL_TRANSCATEGORY);
        }
    }

}

package com.dhamen.hadrecyclerview.DhamenDAO;

import android.os.Bundle;

import java.util.Date;

public class Expense {

public static final String COL__ID = "_id";

public static final String COL_TRANSNAME = "TransName";

public static final String COL_TRANSAMOUNT = "TransAmount";

public static final String COL_TRANSDATE = "TransDate";

public static final String COL_TRANSCATEGORY = "TransCategory";

private Integer m_id;

private String mTransName;

private Double mTransAmount;

private String mTransDate;

private Integer mTransCategory;

public Expense() {

}

public Expense(Integer _id, String TransName, Double TransAmount, String TransDate, Integer TransCategory) {

this.m_id = _id;

this.mTransName = TransName;

this.mTransAmount = TransAmount;

this.mTransDate = TransDate;

this.mTransCategory = TransCategory;

}

public Integer get_id() {

return m_id;

}

public void set_id(Integer _id) {

this.m_id = _id;

}

public String getTransName() {

return mTransName;

}

public void setTransName(String TransName) {

this.mTransName = TransName;

}

public Double getTransAmount() {

return mTransAmount;

}

public void setTransAmount(Double TransAmount) {

this.mTransAmount = TransAmount;

}

public String getTransDate() {

return mTransDate;

}

public void setTransDate(String TransDate) {

this.mTransDate = TransDate;

}

public Integer getTransCategory() {

return mTransCategory;

}

public void setTransCategory(Integer TransCategory) {

this.mTransCategory = TransCategory;

}

public Bundle toBundle() {

Bundle b = new Bundle();

b.putInt(COL__ID, this.m_id);

b.putString(COL_TRANSNAME, this.mTransName);

b.putDouble(COL_TRANSAMOUNT, this.mTransAmount);

b.putString(COL_TRANSDATE, this.mTransDate);

b.putInt(COL_TRANSCATEGORY, this.mTransCategory);

return b;

}

public Expense(Bundle b) {

if (b != null) {

this.m_id = b.getInt(COL__ID);

this.mTransName = b.getString(COL_TRANSNAME);

this.mTransAmount = b.getDouble(COL_TRANSAMOUNT);

this.mTransDate = b.getString(COL_TRANSDATE);

this.mTransCategory = b.getInt(COL_TRANSCATEGORY);

}

ExpenseDao.java

package com.dhamen.hadrecyclerview.DhamenDAO;

import java.util.ArrayList;
import java.util.List;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

public class ExpenseDao extends DbManager {
    private static final String TAG = "ExpenseDao";

    protected static SQLiteDatabase database;
    protected static DbManager mDbManager;
    protected static  String[] allColumns = DbSchema.Table_Expense.allColumns;


    protected ExpenseDao() {
    }

    protected static void database_open() throws SQLException {
        mDbManager = DbManager.getsInstance();
        database = mDbManager.getDatabase();
    }

    protected static void database_close() {
        mDbManager = DbManager.getsInstance();
        mDbManager.close();
    }

    public static Expense loadRecordById(int m_id)  { 
        database_open();
        Cursor cursor = database.query(DbSchema.Table_Expense.TABLE_NAME,allColumns,  "_id = ?" , new String[] { String.valueOf(m_id) } , null, null, null,null);

        if (cursor != null)
            cursor.moveToFirst();

        Expense expense = new Expense();
        expense = cursorToExpense(cursor);

        cursor.close();
        database_close();

        return expense;
    }

    public static ArrayList<Expense> loadAllRecords() {
        ArrayList<Expense> expenseList = new ArrayList<Expense>();
        database_open();

        Cursor cursor = database.query(
                DbSchema.Table_Expense.TABLE_NAME,
                allColumns,
                null,
                null,
                null,
                null,
                null);

        cursor.moveToFirst();
        while (!cursor.isAfterLast()) {
            Expense expense = cursorToExpense(cursor);
            expenseList.add(expense);
            cursor.moveToNext();
        }
        cursor.close();
        database_close();
        return expenseList;
    }

    // Please always use the typed column names (Table_Expense) when passing arguments.
    // Example: Table_Expense.Column_Name
    public static ArrayList<Expense> loadAllRecords(String selection, String[] selectionArgs, String groupBy, String having, String orderBy) {
        ArrayList<Expense> expenseList = new ArrayList<Expense>();
        database_open();

        if(TextUtils.isEmpty(selection)){
            selection = null;
            SelectionArgs = null;}
        }

        Cursor cursor = database.query(
                DbSchema.Table_Expense.TABLE_NAME,
                allColumns,
                selection==null ? null : selection,
                SelectionArgs==null ? null : SelectionArgs,
                groupBy==null ? null : groupBy,
                having==null ? null : having,
                orderBy==null ? null : orderBy);

        cursor.moveToFirst();
        while (!cursor.isAfterLast()) {
            Expense expense = cursorToExpense(cursor);
            expenseList.add(expense);
            cursor.moveToNext();
        }
        cursor.close();
        database_close();
        return expenseList;
    }

    public static long insertRecord(Expense expense) {
        ContentValues values = new ContentValues();
        values = getExpenseValues(expense);
        database_open();
        long insertId = database.insert(DbSchema.Table_Expense.TABLE_NAME , null, values);
        database_close();
        return insertId;
    }

    public static int updateRecord(Expense expense) { 
        ContentValues values = new ContentValues();
        values = getExpenseValues(expense);
        database_open();
        String[] where = new String[] { String.valueOf(expense.get_id()) }; 
        int updatedId = database.update(DbSchema.Table_Expense.TABLE_NAME , values, DbSchema.Table_Expense.COL__ID + " = ? ",where );
        database_close();
        return updatedId;
    }

    public static int deleteRecord(Expense expense) { 
        database_open();
        String[] where = new String[] { String.valueOf(expense.get_id()) }; 
        int deletedCount = database.delete(DbSchema.Table_Expense.TABLE_NAME , DbSchema.Table_Expense.COL__ID + " = ? ",where );
        database_close();
        return deletedCount;
    }

    public static int deleteRecord(String id) {
        database_open();
        String[] where = new String[] { id }; 
        int deletedCount = database.delete(DbSchema.Table_Expense.TABLE_NAME , DbSchema.Table_Expense.COL__ID + " = ? ",where );
        database_close();
        return deletedCount;
    }

    protected static ContentValues getExpenseValues(Expense expense) {
        ContentValues values = new ContentValues();

        values.put(DbSchema.Table_Expense.COL__ID, expense.get_id());
        values.put(DbSchema.Table_Expense.COL_TRANSNAME, expense.getTransName());
        values.put(DbSchema.Table_Expense.COL_TRANSAMOUNT, expense.getTransAmount());
        values.put(DbSchema.Table_Expense.COL_TRANSDATE, expense.getTransDate());
        values.put(DbSchema.Table_Expense.COL_TRANSCATEGORY, expense.getTransCategory());

        return values;
    }

    protected static Expense cursorToExpense(Cursor cursor)  {
        Expense expense = new Expense();

        expense.set_id(cursor.getInt(cursor.getColumnIndex(DbSchema.Table_Expense.COL__ID)));
        expense.setTransName(cursor.getString(cursor.getColumnIndex(DbSchema.Table_Expense.COL_TRANSNAME)));
        expense.setTransAmount(cursor.getDouble(cursor.getColumnIndex(DbSchema.Table_Expense.COL_TRANSAMOUNT)));
        expense.setTransDate(cursor.getString(cursor.getColumnIndex(DbSchema.Table_Expense.COL_TRANSDATE)));
        expense.setTransCategory(cursor.getInt(cursor.getColumnIndex(DbSchema.Table_Expense.COL_TRANSCATEGORY)));

        return expense;
    }

    

}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

package com.dhamen.hadrecyclerview.DhamenDAO;

import java.util.ArrayList;

import java.util.List;

import android.content.ContentValues;

import android.content.Context;

import android.database.Cursor;

import android.database.SQLException;

import android.database.sqlite.SQLiteDatabase;

public class ExpenseDao extends DbManager {

private static final String TAG = "ExpenseDao";

protected static SQLiteDatabase database;

protected static DbManager mDbManager;

protected static String[] allColumns = DbSchema.Table_Expense.allColumns;

protected ExpenseDao() {

}

protected static void database_open() throws SQLException {

mDbManager = DbManager.getsInstance();

database = mDbManager.getDatabase();

}

protected static void database_close() {

mDbManager = DbManager.getsInstance();

mDbManager.close();

}

public static Expense loadRecordById(int m_id) {

database_open();

Cursor cursor = database.query(DbSchema.Table_Expense.TABLE_NAME,allColumns, "_id = ?" , new String[] { String.valueOf(m_id) } , null, null, null,null);

if (cursor != null)

cursor.moveToFirst();

Expense expense = new Expense();

expense = cursorToExpense(cursor);

cursor.close();

database_close();

return expense;

}

public static ArrayList<Expense> loadAllRecords() {

ArrayList<Expense> expenseList = new ArrayList<Expense>();

database_open();

Cursor cursor = database.query(

DbSchema.Table_Expense.TABLE_NAME,

allColumns,

null,

null);

cursor.moveToFirst();

while (!cursor.isAfterLast()) {

Expense expense = cursorToExpense(cursor);

expenseList.add(expense);

cursor.moveToNext();

}

cursor.close();

database_close();

return expenseList;

}

// Please always use the typed column names (Table_Expense) when passing arguments.

// Example: Table_Expense.Column_Name

public static ArrayList<Expense> loadAllRecords(String selection, String[] selectionArgs, String groupBy, String having, String orderBy) {

ArrayList<Expense> expenseList = new ArrayList<Expense>();

database_open();

if(TextUtils.isEmpty(selection)){

selection = null;

SelectionArgs = null;}

}

Cursor cursor = database.query(

DbSchema.Table_Expense.TABLE_NAME,

allColumns,

selection==null ? null : selection,

SelectionArgs==null ? null : SelectionArgs,

groupBy==null ? null : groupBy,

having==null ? null : having,

orderBy==null ? null : orderBy);

cursor.moveToFirst();

while (!cursor.isAfterLast()) {

Expense expense = cursorToExpense(cursor);

expenseList.add(expense);

cursor.moveToNext();

}

cursor.close();

database_close();

return expenseList;

}

public static long insertRecord(Expense expense) {

ContentValues values = new ContentValues();

values = getExpenseValues(expense);

database_open();

long insertId = database.insert(DbSchema.Table_Expense.TABLE_NAME , null, values);

database_close();

return insertId;

}

public static int updateRecord(Expense expense) {

ContentValues values = new ContentValues();

values = getExpenseValues(expense);

database_open();

String[] where = new String[] { String.valueOf(expense.get_id()) };

int updatedId = database.update(DbSchema.Table_Expense.TABLE_NAME , values, DbSchema.Table_Expense.COL__ID + " = ? ",where );

database_close();

return updatedId;

}

public static int deleteRecord(Expense expense) {

database_open();

String[] where = new String[] { String.valueOf(expense.get_id()) };

int deletedCount = database.delete(DbSchema.Table_Expense.TABLE_NAME , DbSchema.Table_Expense.COL__ID + " = ? ",where );

database_close();

return deletedCount;

}

public static int deleteRecord(String id) {

database_open();

String[] where = new String[] { id };

int deletedCount = database.delete(DbSchema.Table_Expense.TABLE_NAME , DbSchema.Table_Expense.COL__ID + " = ? ",where );

database_close();

return deletedCount;

}

protected static ContentValues getExpenseValues(Expense expense) {

ContentValues values = new ContentValues();

values.put(DbSchema.Table_Expense.COL__ID, expense.get_id());

values.put(DbSchema.Table_Expense.COL_TRANSNAME, expense.getTransName());

values.put(DbSchema.Table_Expense.COL_TRANSAMOUNT, expense.getTransAmount());

values.put(DbSchema.Table_Expense.COL_TRANSDATE, expense.getTransDate());

values.put(DbSchema.Table_Expense.COL_TRANSCATEGORY, expense.getTransCategory());

return values;

}

protected static Expense cursorToExpense(Cursor cursor) {

Expense expense = new Expense();

expense.set_id(cursor.getInt(cursor.getColumnIndex(DbSchema.Table_Expense.COL__ID)));

expense.setTransName(cursor.getString(cursor.getColumnIndex(DbSchema.Table_Expense.COL_TRANSNAME)));

expense.setTransAmount(cursor.getDouble(cursor.getColumnIndex(DbSchema.Table_Expense.COL_TRANSAMOUNT)));

expense.setTransDate(cursor.getString(cursor.getColumnIndex(DbSchema.Table_Expense.COL_TRANSDATE)));

expense.setTransCategory(cursor.getInt(cursor.getColumnIndex(DbSchema.Table_Expense.COL_TRANSCATEGORY)));

return expense;

}

Category.java

package com.dhamen.hadrecyclerview.DhamenDAO;

import android.os.Bundle;
import java.util.Date;

public class Category {

    public static final String COL__ID = "_id";
    public static final String COL_CATEGORYNAME = "CategoryName";

    private Integer m_id;
    private String mCategoryName;

    public Category() {
    }

    public Category(Integer _id, String CategoryName) {
        this.m_id = _id;
        this.mCategoryName = CategoryName;
    }

    public Integer get_id() {
        return m_id;
    }

    public void set_id(Integer _id) {
        this.m_id = _id;
    }

    public String getCategoryName() {
        return mCategoryName;
    }

    public void setCategoryName(String CategoryName) {
        this.mCategoryName = CategoryName;
    }


    public Bundle toBundle() { 
        Bundle b = new Bundle();
        b.putInt(COL__ID, this.m_id);
        b.putString(COL_CATEGORYNAME, this.mCategoryName);
        return b;
    }

    public Category(Bundle b) {
        if (b != null) {
            this.m_id = b.getInt(COL__ID);
            this.mCategoryName = b.getString(COL_CATEGORYNAME);
        }
    }

}

package com.dhamen.hadrecyclerview.DhamenDAO;

import android.os.Bundle;

import java.util.Date;

public class Category {

public static final String COL__ID = "_id";

public static final String COL_CATEGORYNAME = "CategoryName";

private Integer m_id;

private String mCategoryName;

public Category() {

}

public Category(Integer _id, String CategoryName) {

this.m_id = _id;

this.mCategoryName = CategoryName;

}

public Integer get_id() {

return m_id;

}

public void set_id(Integer _id) {

this.m_id = _id;

}

public String getCategoryName() {

return mCategoryName;

}

public void setCategoryName(String CategoryName) {

this.mCategoryName = CategoryName;

}

public Bundle toBundle() {

Bundle b = new Bundle();

b.putInt(COL__ID, this.m_id);

b.putString(COL_CATEGORYNAME, this.mCategoryName);

return b;

}

public Category(Bundle b) {

if (b != null) {

this.m_id = b.getInt(COL__ID);

this.mCategoryName = b.getString(COL_CATEGORYNAME);

}

CategoryDao.java

package com.dhamen.hadrecyclerview.DhamenDAO;

import java.util.ArrayList;
import java.util.List;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

public class CategoryDao extends DbManager {
    private static final String TAG = "CategoryDao";

    protected static SQLiteDatabase database;
    protected static DbManager mDbManager;
    protected static  String[] allColumns = DbSchema.Table_Category.allColumns;


    protected CategoryDao() {
    }

    protected static void database_open() throws SQLException {
        mDbManager = DbManager.getsInstance();
        database = mDbManager.getDatabase();
    }

    protected static void database_close() {
        mDbManager = DbManager.getsInstance();
        mDbManager.close();
    }

    public static Category loadRecordById(int m_id)  { 
        database_open();
        Cursor cursor = database.query(DbSchema.Table_Category.TABLE_NAME,allColumns,  "_id = ?" , new String[] { String.valueOf(m_id) } , null, null, null,null);

        if (cursor != null)
            cursor.moveToFirst();

        Category category = new Category();
        category = cursorToCategory(cursor);

        cursor.close();
        database_close();

        return category;
    }

    public static ArrayList<Category> loadAllRecords() {
        ArrayList<Category> categoryList = new ArrayList<Category>();
        database_open();

        Cursor cursor = database.query(
                DbSchema.Table_Category.TABLE_NAME,
                allColumns,
                null,
                null,
                null,
                null,
                null);

        cursor.moveToFirst();
        while (!cursor.isAfterLast()) {
            Category category = cursorToCategory(cursor);
            categoryList.add(category);
            cursor.moveToNext();
        }
        cursor.close();
        database_close();
        return categoryList;
    }

    // Please always use the typed column names (Table_Category) when passing arguments.
    // Example: Table_Category.Column_Name
    public static ArrayList<Category> loadAllRecords(String selection, String[] selectionArgs, String groupBy, String having, String orderBy) {
        ArrayList<Category> categoryList = new ArrayList<Category>();
        database_open();

        if(TextUtils.isEmpty(selection)){
            selection = null;
            SelectionArgs = null;}
        }

        Cursor cursor = database.query(
                DbSchema.Table_Category.TABLE_NAME,
                allColumns,
                selection==null ? null : selection,
                SelectionArgs==null ? null : SelectionArgs,
                groupBy==null ? null : groupBy,
                having==null ? null : having,
                orderBy==null ? null : orderBy);

        cursor.moveToFirst();
        while (!cursor.isAfterLast()) {
            Category category = cursorToCategory(cursor);
            categoryList.add(category);
            cursor.moveToNext();
        }
        cursor.close();
        database_close();
        return categoryList;
    }

    public static long insertRecord(Category category) {
        ContentValues values = new ContentValues();
        values = getCategoryValues(category);
        database_open();
        long insertId = database.insert(DbSchema.Table_Category.TABLE_NAME , null, values);
        database_close();
        return insertId;
    }

    public static int updateRecord(Category category) { 
        ContentValues values = new ContentValues();
        values = getCategoryValues(category);
        database_open();
        String[] where = new String[] { String.valueOf(category.get_id()) }; 
        int updatedId = database.update(DbSchema.Table_Category.TABLE_NAME , values, DbSchema.Table_Category.COL__ID + " = ? ",where );
        database_close();
        return updatedId;
    }

    public static int deleteRecord(Category category) { 
        database_open();
        String[] where = new String[] { String.valueOf(category.get_id()) }; 
        int deletedCount = database.delete(DbSchema.Table_Category.TABLE_NAME , DbSchema.Table_Category.COL__ID + " = ? ",where );
        database_close();
        return deletedCount;
    }

    public static int deleteRecord(String id) {
        database_open();
        String[] where = new String[] { id }; 
        int deletedCount = database.delete(DbSchema.Table_Category.TABLE_NAME , DbSchema.Table_Category.COL__ID + " = ? ",where );
        database_close();
        return deletedCount;
    }

    protected static ContentValues getCategoryValues(Category category) {
        ContentValues values = new ContentValues();

        values.put(DbSchema.Table_Category.COL__ID, category.get_id());
        values.put(DbSchema.Table_Category.COL_CATEGORYNAME, category.getCategoryName());

        return values;
    }

    protected static Category cursorToCategory(Cursor cursor)  {
        Category category = new Category();

        category.set_id(cursor.getInt(cursor.getColumnIndex(DbSchema.Table_Category.COL__ID)));
        category.setCategoryName(cursor.getString(cursor.getColumnIndex(DbSchema.Table_Category.COL_CATEGORYNAME)));

        return category;
    }

    

}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

package com.dhamen.hadrecyclerview.DhamenDAO;

import java.util.ArrayList;

import java.util.List;

import android.content.ContentValues;

import android.content.Context;

import android.database.Cursor;

import android.database.SQLException;

import android.database.sqlite.SQLiteDatabase;

public class CategoryDao extends DbManager {

private static final String TAG = "CategoryDao";

protected static SQLiteDatabase database;

protected static DbManager mDbManager;

protected static String[] allColumns = DbSchema.Table_Category.allColumns;

protected CategoryDao() {

}

protected static void database_open() throws SQLException {

mDbManager = DbManager.getsInstance();

database = mDbManager.getDatabase();

}

protected static void database_close() {

mDbManager = DbManager.getsInstance();

mDbManager.close();

}

public static Category loadRecordById(int m_id) {

database_open();

Cursor cursor = database.query(DbSchema.Table_Category.TABLE_NAME,allColumns, "_id = ?" , new String[] { String.valueOf(m_id) } , null, null, null,null);

if (cursor != null)

cursor.moveToFirst();

Category category = new Category();

category = cursorToCategory(cursor);

cursor.close();

database_close();

return category;

}

public static ArrayList<Category> loadAllRecords() {

ArrayList<Category> categoryList = new ArrayList<Category>();

database_open();

Cursor cursor = database.query(

DbSchema.Table_Category.TABLE_NAME,

allColumns,

null,

null);

cursor.moveToFirst();

while (!cursor.isAfterLast()) {

Category category = cursorToCategory(cursor);

categoryList.add(category);

cursor.moveToNext();

}

cursor.close();

database_close();

return categoryList;

}

// Please always use the typed column names (Table_Category) when passing arguments.

// Example: Table_Category.Column_Name

public static ArrayList<Category> loadAllRecords(String selection, String[] selectionArgs, String groupBy, String having, String orderBy) {

ArrayList<Category> categoryList = new ArrayList<Category>();

database_open();

if(TextUtils.isEmpty(selection)){

selection = null;

SelectionArgs = null;}

}

Cursor cursor = database.query(

DbSchema.Table_Category.TABLE_NAME,

allColumns,

selection==null ? null : selection,

SelectionArgs==null ? null : SelectionArgs,

groupBy==null ? null : groupBy,

having==null ? null : having,

orderBy==null ? null : orderBy);

cursor.moveToFirst();

while (!cursor.isAfterLast()) {

Category category = cursorToCategory(cursor);

categoryList.add(category);

cursor.moveToNext();

}

cursor.close();

database_close();

return categoryList;

}

public static long insertRecord(Category category) {

ContentValues values = new ContentValues();

values = getCategoryValues(category);

database_open();

long insertId = database.insert(DbSchema.Table_Category.TABLE_NAME , null, values);

database_close();

return insertId;

}

public static int updateRecord(Category category) {

ContentValues values = new ContentValues();

values = getCategoryValues(category);

database_open();

String[] where = new String[] { String.valueOf(category.get_id()) };

int updatedId = database.update(DbSchema.Table_Category.TABLE_NAME , values, DbSchema.Table_Category.COL__ID + " = ? ",where );

database_close();

return updatedId;

}

public static int deleteRecord(Category category) {

database_open();

String[] where = new String[] { String.valueOf(category.get_id()) };

int deletedCount = database.delete(DbSchema.Table_Category.TABLE_NAME , DbSchema.Table_Category.COL__ID + " = ? ",where );

database_close();

return deletedCount;

}

public static int deleteRecord(String id) {

database_open();

String[] where = new String[] { id };

int deletedCount = database.delete(DbSchema.Table_Category.TABLE_NAME , DbSchema.Table_Category.COL__ID + " = ? ",where );

database_close();

return deletedCount;

}

protected static ContentValues getCategoryValues(Category category) {

ContentValues values = new ContentValues();

values.put(DbSchema.Table_Category.COL__ID, category.get_id());

values.put(DbSchema.Table_Category.COL_CATEGORYNAME, category.getCategoryName());

return values;

}

protected static Category cursorToCategory(Cursor cursor) {

Category category = new Category();

category.set_id(cursor.getInt(cursor.getColumnIndex(DbSchema.Table_Category.COL__ID)));

category.setCategoryName(cursor.getString(cursor.getColumnIndex(DbSchema.Table_Category.COL_CATEGORYNAME)));

return category;

}

I Hope you enjoy with this tool and make use of it.

Thank you.