SQLiteHelper

ProductHandler.java

public class BeaconProductHandler extends SQLiteHelper {

    public static final String TABLE_BEACON_PRODUCT = "BEACON_PRODUCT_TABLE";

    // beacon product table column names
    public static final String key_uid = "uid";
    public static final String key_uuid = "uuid";
    public static final String key_major_value = "major_value";
    public static final String key_minor_value = "minor_value";
    public static final String key_name = "name";
    public static final String key_image = "image";
    public static final String key_description = "description";
    public static final String key_state = "state";
    public static final String key_status = "status";
    public static final String key_entry_notification = "entry_notification";
    public static final String key_exit_notification = "exit_notification";
    public static final String key_audio_status = "audio_status";
    public static final String key_audio_file = "audio_file";
    public static final String key_password_status = "password_status";
    public static final String key_password = "password";
    public static final String key_battery = "battery";
    public static final String key_notification = "notification";
    public static final String key_is_owner = "is_owner";

    private Context mContext;

    public BeaconProductHandler(Context mContext) {
        super(mContext);
        // TODO Auto-generated constructor stub
        this.mContext = mContext;
    }

    // Add beacon product row.
    // NOTE: to remove special characters. mystring.replaceAll("[^\\w ,/]", "").replaceAll("'", "\'"));
    public synchronized long addBeaconProduct(String uuid, int major_value, int minor_value,
                                              String name, String image, String description,
                                              String state, int status, int entry_notification,
                                              int exit_notification, int audio_status,
                                              String audio_file, int password_status,
                                              String password, int battery,
                                              int notification, int is_owner) {

        ContentValues values = new ContentValues();
        values.put(key_uuid, uuid);
        values.put(key_major_value, major_value);
        values.put(key_minor_value, minor_value);
        values.put(key_name, name);
        values.put(key_image, image);
        values.put(key_description, description);
        values.put(key_state, state);
        values.put(key_status, status);
        values.put(key_entry_notification, entry_notification);
        values.put(key_exit_notification, exit_notification);
        values.put(key_audio_status, audio_status);
        values.put(key_audio_file, audio_file);
        values.put(key_password_status, password_status);
        values.put(key_password, password);
        values.put(key_battery, battery);
        values.put(key_notification, notification);
        values.put(key_is_owner, is_owner);

        SQLiteDatabase db = this.getWritableDatabase();
        long id = db.insert(TABLE_BEACON_PRODUCT, null, values);
        db.close(); // Closing database connection

        return id;
    }

    // pass -1 to this method in case of new possible major minor
    // otherwise pass beacon product id to get major minor from db.
    public synchronized BeaconProduct getBeaconMajorMinor(int beacon_product_id) {

        BeaconProduct beaconProduct = new BeaconProduct();

        String raw_query;
        if (beacon_product_id == -1) {
            final int min = 1;
            final int max = 65535;

            SQLiteDatabase db = this.getReadableDatabase();
            Cursor cursor;
            int random_major;
            int random_minor;
            do {
                Random r = new Random();
                random_major = r.nextInt(max - min + 1) + min;
                random_minor = r.nextInt(max - min + 1) + min;

                // check for random major minor exist in db.
                raw_query = "SELECT * FROM " + BeaconProductHandler.TABLE_BEACON_PRODUCT
                        + " WHERE "
                        + key_major_value + " = " + random_major + " AND "
                        + key_minor_value + " = " + random_minor + ";";
                cursor = db.rawQuery(raw_query, null);
            } while (cursor.moveToFirst());

            // store possible major minor.
            beaconProduct.major_value = random_major;
            beaconProduct.minor_value = random_minor;

            cursor.close();
            db.close();

        } else {
            raw_query = "SELECT * FROM " + BeaconProductHandler.TABLE_BEACON_PRODUCT + " WHERE "
                    + key_uid + " = " + beacon_product_id + ";";

            SQLiteDatabase db = this.getReadableDatabase();
            Cursor cursor = db.rawQuery(raw_query, null);

            if (cursor.moveToFirst()) {
                // get major minor from db.
                beaconProduct.major_value = cursor.getInt(cursor.getColumnIndex(BeaconProductHandler.key_major_value));
                beaconProduct.minor_value = cursor.getInt(cursor.getColumnIndex(BeaconProductHandler.key_minor_value));
            }

            cursor.close();
            db.close();
        }

        return beaconProduct;
    }

    public synchronized ArrayList<BeaconProduct> getAllBeaconProduct() {

        ArrayList<BeaconProduct> arrBeaconProducts = new ArrayList<BeaconProduct>();

        String raw_query = "SELECT * FROM " + BeaconProductHandler.TABLE_BEACON_PRODUCT + ";";

        SQLiteDatabase db = this.getReadableDatabase();
        Cursor c = db.rawQuery(raw_query, null);

        if (c.moveToFirst()) {
            do {
                BeaconProduct beaconProduct = new BeaconProduct();

                beaconProduct.uid = c.getInt(c.getColumnIndex(key_uid));
                beaconProduct.uuid = c.getString(c.getColumnIndex(key_uuid));
                beaconProduct.major_value = c.getInt(c
                        .getColumnIndex(key_major_value));
                beaconProduct.minor_value = c.getInt(c
                        .getColumnIndex(key_minor_value));
                beaconProduct.name = c.getString(c
                        .getColumnIndex(key_name));
                beaconProduct.image = c.getString(c
                        .getColumnIndex(key_image));
                beaconProduct.description = c.getString(c
                        .getColumnIndex(key_description));
                beaconProduct.status = c.getInt(c
                        .getColumnIndex(key_status));
                beaconProduct.entry_notification = c.getInt(c
                        .getColumnIndex(key_entry_notification));
                beaconProduct.exit_notification = c.getInt(c
                        .getColumnIndex(key_exit_notification));
                beaconProduct.audio_status = c.getInt(c
                        .getColumnIndex(key_audio_status));
                beaconProduct.audio_file = c.getString(c
                        .getColumnIndex(key_audio_file));
                beaconProduct.password_status = c.getInt(c
                        .getColumnIndex(key_password_status));
                beaconProduct.password = c.getString(c
                        .getColumnIndex(key_password));
                beaconProduct.battery = c.getInt(c
                        .getColumnIndex(key_battery));
                beaconProduct.notification = c.getInt(c
                        .getColumnIndex(key_notification));
                beaconProduct.is_owner = c.getInt(c
                        .getColumnIndex(key_is_owner));

                arrBeaconProducts.add(beaconProduct);
            } while (c.moveToNext());
        }

        c.close();
        db.close();

        return arrBeaconProducts;
    }

    public synchronized BeaconProduct getBeaconProductById(int product_id) {

        BeaconProduct beaconProduct = new BeaconProduct();

        String raw_query = "SELECT * FROM " + BeaconProductHandler.TABLE_BEACON_PRODUCT + " WHERE " + key_uid + " = " + product_id + ";";

        SQLiteDatabase db = this.getReadableDatabase();
        Cursor c = db.rawQuery(raw_query, null);

        if (c.moveToFirst()) {
            beaconProduct.uid = c.getInt(c.getColumnIndex(key_uid));
            beaconProduct.uuid = c.getString(c.getColumnIndex(key_uuid));
            beaconProduct.major_value = c.getInt(c
                    .getColumnIndex(key_major_value));
            beaconProduct.minor_value = c.getInt(c
                    .getColumnIndex(key_minor_value));
            beaconProduct.name = c.getString(c
                    .getColumnIndex(key_name));
            beaconProduct.image = c.getString(c
                    .getColumnIndex(key_image));
            beaconProduct.description = c.getString(c
                    .getColumnIndex(key_description));
            beaconProduct.state = c.getString(c
                    .getColumnIndex(key_state));
            beaconProduct.status = c.getInt(c
                    .getColumnIndex(key_status));
            beaconProduct.entry_notification = c.getInt(c
                    .getColumnIndex(key_entry_notification));
            beaconProduct.exit_notification = c.getInt(c
                    .getColumnIndex(key_exit_notification));
            beaconProduct.audio_status = c.getInt(c
                    .getColumnIndex(key_audio_status));
            beaconProduct.audio_file = c.getString(c
                    .getColumnIndex(key_audio_file));
            beaconProduct.password_status = c.getInt(c
                    .getColumnIndex(key_password_status));
            beaconProduct.password = c.getString(c
                    .getColumnIndex(key_password));
            beaconProduct.battery = c.getInt(c
                    .getColumnIndex(key_battery));
            beaconProduct.notification = c.getInt(c
                    .getColumnIndex(key_notification));
            beaconProduct.is_owner = c.getInt(c
                    .getColumnIndex(key_is_owner));
        }

        c.close();
        db.close();

        return beaconProduct;
    }

    public synchronized int updateBeaconProduct(int beacon_product_id, String uuid, int major_value, int minor_value,
                                                String name, String image, String description,
                                                String state, int status, int entry_notification,
                                                int exit_notification, int audio_status,
                                                String audio_file, int password_status,
                                                String password, int battery,
                                                int notification, int is_owner) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(key_uuid, uuid);
        values.put(key_major_value, major_value);
        values.put(key_minor_value, minor_value);
        values.put(key_name, name);
        values.put(key_image, image);
        values.put(key_description, description);
        values.put(key_state, state);
        values.put(key_status, status);
        values.put(key_entry_notification, entry_notification);
        values.put(key_exit_notification, exit_notification);
        values.put(key_audio_status, audio_status);
        values.put(key_audio_file, audio_file);
        values.put(key_password_status, password_status);
        values.put(key_password, password);
        values.put(key_battery, battery);
        values.put(key_notification, notification);
        values.put(key_is_owner, is_owner);

        // updating row
        int res = db.update(TABLE_BEACON_PRODUCT, values, key_uid + " = ?",
                new String[]{String.valueOf(beacon_product_id)});
        db.close();

        return res;
    }

    public synchronized int updateBeaconProduct(BeaconProduct beaconProduct) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(key_uuid, beaconProduct.uuid);
        values.put(key_major_value, beaconProduct.major_value);
        values.put(key_minor_value, beaconProduct.minor_value);
        values.put(key_name, beaconProduct.name);
        values.put(key_image, beaconProduct.image);
        values.put(key_description, beaconProduct.description);
        values.put(key_state, beaconProduct.state);
        values.put(key_status, beaconProduct.status);
        values.put(key_entry_notification, beaconProduct.entry_notification);
        values.put(key_exit_notification, beaconProduct.exit_notification);
        values.put(key_audio_status, beaconProduct.audio_status);
        values.put(key_audio_file, beaconProduct.audio_file);
        values.put(key_password_status, beaconProduct.password_status);
        values.put(key_password, beaconProduct.password);
        values.put(key_battery, beaconProduct.battery);
        values.put(key_notification, beaconProduct.notification);
        values.put(key_is_owner, beaconProduct.is_owner);

        // updating row
        int res = db.update(TABLE_BEACON_PRODUCT, values, key_uid + " = ?",
                new String[]{String.valueOf(beaconProduct.uid)});
        db.close();

        return res;
    }

    public synchronized int deleteBeaconProduct(int beacon_product_id) {

        SQLiteDatabase db = this.getWritableDatabase();

        int res = db.delete(TABLE_BEACON_PRODUCT, key_uid + " = ?",
                new String[]{String.valueOf(beacon_product_id)});
        db.close();

        return res;
    }

    public synchronized DBResponse getBeaconByMajorMinor(int beacon_major, int beacon_minor) {


        String raw_query = "SELECT * FROM " + BeaconProductHandler.TABLE_BEACON_PRODUCT
                + " WHERE "
                + key_major_value + " = " + beacon_major + " AND "
                + key_minor_value + " = " + beacon_minor + ";";

        SQLiteDatabase db = this.getReadableDatabase();
        Cursor c = db.rawQuery(raw_query, null);
        DBResponse dbResponse = new DBResponse();
        if (c.moveToFirst()) {

            BeaconProduct beaconProduct = new BeaconProduct();
            beaconProduct.uid = c.getInt(c.getColumnIndex(key_uid));
            beaconProduct.uuid = c.getString(c.getColumnIndex(key_uuid));
            beaconProduct.major_value = c.getInt(c
                    .getColumnIndex(key_major_value));
            beaconProduct.minor_value = c.getInt(c
                    .getColumnIndex(key_minor_value));
            beaconProduct.name = c.getString(c
                    .getColumnIndex(key_name));
            beaconProduct.image = c.getString(c
                    .getColumnIndex(key_image));
            beaconProduct.description = c.getString(c
                    .getColumnIndex(key_description));
            beaconProduct.status = c.getInt(c
                    .getColumnIndex(key_status));
            beaconProduct.entry_notification = c.getInt(c
                    .getColumnIndex(key_entry_notification));
            beaconProduct.exit_notification = c.getInt(c
                    .getColumnIndex(key_exit_notification));
            beaconProduct.audio_status = c.getInt(c
                    .getColumnIndex(key_audio_status));
            beaconProduct.audio_file = c.getString(c
                    .getColumnIndex(key_audio_file));
            beaconProduct.password_status = c.getInt(c
                    .getColumnIndex(key_password_status));
            beaconProduct.password = c.getString(c
                    .getColumnIndex(key_password));
            beaconProduct.battery = c.getInt(c
                    .getColumnIndex(key_battery));
            beaconProduct.notification = c.getInt(c
                    .getColumnIndex(key_notification));
            beaconProduct.state = c.getString(c
                    .getColumnIndex(key_state));
            beaconProduct.is_owner = c.getInt(c.
                    getColumnIndex(key_is_owner));

            dbResponse.error = false;
            dbResponse.o = beaconProduct;
            Log.e("beacon", beaconProduct.toString());
        } else {
            dbResponse.error = true;
            dbResponse.o = "No beacon product found in beacon product table.";
            Log.e("beacon", "No beacon product found in beacon product table.");
        }

        c.close();
        db.close();

        return dbResponse;
    }

    public synchronized int updateNotificationForAll(Utils.OnOff onOff) {
        int notification;
        switch (onOff) {
            case ON:
                notification = 1;
                break;
            case OFF:
                notification = 0;
                break;
            default:
                notification = 1;
                break;
        }

        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(key_notification, notification);

        // updating row
        int res = db.update(TABLE_BEACON_PRODUCT, values, null, null);
        db.close();

        return res;
    }

    public synchronized boolean isAllNotificationOn() {
        boolean isAllNotificationOn = true;

        String raw_query = "SELECT * FROM " + BeaconProductHandler.TABLE_BEACON_PRODUCT + ";";

        SQLiteDatabase db = this.getReadableDatabase();
        Cursor c = db.rawQuery(raw_query, null);

        if (c.moveToFirst()) {
            do {
                if (c.getInt(c.getColumnIndex(key_notification)) == 0) {
                    isAllNotificationOn = false;
                    break;
                }
            } while (c.moveToNext());
        }

        c.close();
        db.close();

        return isAllNotificationOn;
    }

    public synchronized boolean isAllNotificationOff() {
        boolean isAllNotificationOff = true;

        String raw_query = "SELECT * FROM " + BeaconProductHandler.TABLE_BEACON_PRODUCT + ";";

        SQLiteDatabase db = this.getReadableDatabase();
        Cursor c = db.rawQuery(raw_query, null);

        if (c.moveToFirst()) {
            do {
                if (c.getInt(c.getColumnIndex(key_notification)) == 1) {
                    isAllNotificationOff = false;
                    break;
                }
            } while (c.moveToNext());
        }

        c.close();
        db.close();

        return isAllNotificationOff;
    }

    public synchronized boolean isNameAlreadyExist(String beacon_name) {
        boolean isAlreadyExist = false;

        String raw_query = "SELECT * FROM " + BeaconProductHandler.TABLE_BEACON_PRODUCT
                + " WHERE "
                + key_name + " = '" + beacon_name + "';";

        SQLiteDatabase db = this.getReadableDatabase();
        Cursor c = db.rawQuery(raw_query, null);

        if (c.moveToFirst()) {
            isAlreadyExist = true;
        }

        c.close();
        db.close();

        return isAlreadyExist;
    }

    public synchronized boolean isMajorMinorAlreadyExists(int major_value, int minor_value) {
        boolean isMajorMinorExists = false;

        String raw_query = "SELECT * FROM " + BeaconProductHandler.TABLE_BEACON_PRODUCT
                + " WHERE "
                + key_major_value + " = " + major_value + " AND "
                + key_minor_value + " = " + minor_value + ";";

        SQLiteDatabase db = this.getReadableDatabase();
        Cursor c = db.rawQuery(raw_query, null);
        if (c.moveToFirst()) {
            isMajorMinorExists = true;
        }
        c.close();
        db.close();

        return isMajorMinorExists;
    }

}

SQLiteHelper.java

public class SQLiteHelper extends SQLiteOpenHelper {

    private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_NAME = "_my_database.db";

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

    @Override
    public void onCreate(SQLiteDatabase db) {

        // beacon product table statement
        String CREATE_SEND_MSG_TABLE = "CREATE TABLE "
                + BeaconProductHandler.TABLE_BEACON_PRODUCT + " ("
                + BeaconProductHandler.key_uid
                + " INTEGER PRIMARY KEY autoincrement,"
                + BeaconProductHandler.key_uuid + " TEXT,"
                + BeaconProductHandler.key_major_value + " INTEGER,"
                + BeaconProductHandler.key_minor_value + " INTEGER,"
                + BeaconProductHandler.key_name + " TEXT,"
                + BeaconProductHandler.key_image + " TEXT,"
                + BeaconProductHandler.key_description + " TEXT,"
                + BeaconProductHandler.key_state + " TEXT,"
                + BeaconProductHandler.key_status + " INTEGER,"
                + BeaconProductHandler.key_entry_notification + " INTEGER,"
                + BeaconProductHandler.key_exit_notification + " INTEGER,"
                + BeaconProductHandler.key_audio_status + " INTEGER,"
                + BeaconProductHandler.key_audio_file + " TEXT,"
                + BeaconProductHandler.key_password_status + " INTEGER,"
                + BeaconProductHandler.key_password + " TEXT,"
                + BeaconProductHandler.key_battery + " INTEGER,"
                + BeaconProductHandler.key_notification + " INTEGER,"
                + BeaconProductHandler.key_is_owner+ " INTEGER" +")";
        db.execSQL(CREATE_SEND_MSG_TABLE);

        // beacon log table
        String CREATE_BEACON_LOG_TABLE = "CREATE TABLE "
                + BeaconLogHandler.TABLE_BEACON_LOG + " ("
                + BeaconLogHandler.key_uid
                + " INTEGER PRIMARY KEY autoincrement,"
                + BeaconLogHandler.key_beacon_id + " INTEGER,"
                + BeaconLogHandler.key_beacon_name + " TEXT,"
                + BeaconLogHandler.key_state + " TEXT,"
                + BeaconLogHandler.key_latitude + " TEXT,"
                + BeaconLogHandler.key_longitude + " TEXT,"
                + BeaconLogHandler.key_datetime + " TEXT,"
                + BeaconLogHandler.key_major_value + " INTEGER,"
                + BeaconLogHandler.key_minor_value + " INTEGER" + ")";
        db.execSQL(CREATE_BEACON_LOG_TABLE);

        // beacon exit log table
        String CREATE_BEACON_EXIT_LOG_TABLE = "CREATE TABLE "
                + BeaconExitHandler.TABLE_BEACON_EXIT_LOG + " ("
                + BeaconExitHandler.key_uid
                + " INTEGER PRIMARY KEY autoincrement,"
                + BeaconExitHandler.key_beacon_id + " INTEGER,"
                + BeaconExitHandler.key_beacon_name + " TEXT,"
                + BeaconExitHandler.key_state + " TEXT,"
                + BeaconExitHandler.key_latitude + " TEXT,"
                + BeaconExitHandler.key_longitude + " TEXT,"
                + BeaconExitHandler.key_datetime + " TEXT,"
                + BeaconExitHandler.key_major_value + " INTEGER,"
                + BeaconExitHandler.key_minor_value + " INTEGER" + ")";
        db.execSQL(CREATE_BEACON_EXIT_LOG_TABLE);

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        try {
            String dropSql = "";
            dropSql = "DROP TABLE " + BeaconProductHandler.TABLE_BEACON_PRODUCT;
            db.execSQL(dropSql);

            dropSql = "DROP TABLE " + BeaconLogHandler.TABLE_BEACON_LOG;
            db.execSQL(dropSql);

            dropSql = "DROP TABLE " + BeaconExitHandler.TABLE_BEACON_EXIT_LOG;
            db.execSQL(dropSql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        onCreate(db);
    }

    public void DeleteAll() {
        SQLiteDatabase db = this.getWritableDatabase();
        try {
            String dropSql = "DROP TABLE " + BeaconProductHandler.TABLE_BEACON_PRODUCT;
            db.execSQL(dropSql);

            dropSql = "DROP TABLE " + BeaconLogHandler.TABLE_BEACON_LOG;
            db.execSQL(dropSql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        onCreate(db);
    }


}