SQLite Database Sample

DatabaseManager.java

public class DatabaseManager {
    private AtomicInteger mOpenCounter = new AtomicInteger();

    private static DatabaseManager instance;
    private static SQLiteOpenHelper mDatabaseHelper;
    private SQLiteDatabase mDatabase;

    public static synchronized void initializeInstance(SQLiteOpenHelper helper) {
        if (instance == null) {
            instance = new DatabaseManager();
            mDatabaseHelper = helper;
        }
    }

    public static synchronized DatabaseManager getInstance() {
        if (instance == null) {
            throw new IllegalStateException(DatabaseManager.class.getSimpleName() +
                    " is not initialized, call initializeInstance(..) method first.");
        }

        return instance;
    }

    public synchronized SQLiteDatabase openDatabase() {
        if (mOpenCounter.incrementAndGet() == 1) {
            // Opening new database
            mDatabase = mDatabaseHelper.getWritableDatabase();
        }
        return mDatabase;
    }

    public synchronized void closeDatabase() {
        if (mOpenCounter.decrementAndGet() == 0) {
            // Closing database
            mDatabase.close();

        }
    }
}

DBUtils.java

public class DBUtils {

    public static ArrayList<String> getColumns(SQLiteDatabase db, String tableName) {
        ArrayList<String> al = null;
        Cursor c = null;
        try {
            c = db.rawQuery("SELECT * FROM " + tableName + " LIMIT 1", null);
            if (c != null) {
                al = new ArrayList<>(Arrays.asList(c.getColumnNames()));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (c != null)
                c.close();
        }
        return al;
    }

    public static String join(ArrayList<String> al, String delim) {
        StringBuilder sb = new StringBuilder();
        int num = al.size();
        for (int i = 0; i < num; i++) {
            if (i != 0)
                sb.append(delim);
            sb.append(al.get(i));
        }
        return sb.toString();
    }
}

FavouriteHandler.java

public class FavouriteHandler {
    public static final String TABLE_FAVOURITE = "FAVOURITE_TABLE";

    public static final String KEY_ID = "_id";
    public static final String KEY_POST_ID = "post_id";
    public static final String KEY_META_KEY = "meta_key";
    public static final String KEY_LAT = "property_latitude";
    public static final String KEY_LONG = "property_longitude";
    public static final String KEY_TITLE = "post_title";
    public static final String KEY_CONTENT = "post_content";
    public static final String KEY_THUMBNAIL = "thumbnail";
    public static final String KEY_STATUS = "property_status";
    public static final String KEY_COUNTRY = "property_country";
    public static final String KEY_ADDRESS = "property_address";
    public static final String KEY_CATEGORY = "property_category";


    public void createTable(SQLiteDatabase db) {
        String CREATE_FAV_MEWS_TABLE = "CREATE TABLE IF NOT EXISTS "
                + TABLE_FAVOURITE + " ("
                + KEY_ID
                + " INTEGER PRIMARY KEY autoincrement,"
                + KEY_POST_ID + " TEXT,"
                + KEY_META_KEY + " TEXT,"
                + KEY_LAT + " TEXT,"
                + KEY_LONG + " TEXT,"
                + KEY_TITLE + " TEXT,"
                + KEY_CONTENT + " TEXT,"
                + KEY_THUMBNAIL + " TEXT,"
                + KEY_STATUS + " TEXT,"
                + KEY_COUNTRY + " TEXT,"
                + KEY_ADDRESS + " TEXT,"
                + KEY_CATEGORY + " TEXT" + ")";
        db.execSQL(CREATE_FAV_MEWS_TABLE);
    }

    public void upgradeTable(SQLiteDatabase db) {
        FavouriteHandler fh = new FavouriteHandler();
        fh.createTable(db);

        ArrayList<String> columns = DBUtils.getColumns(db, TABLE_FAVOURITE);
        String alterQuery = "ALTER TABLE " + TABLE_FAVOURITE + " RENAME TO temp_" + TABLE_FAVOURITE;
        db.execSQL(alterQuery);

        fh.createTable(db);
        columns.retainAll(DBUtils.getColumns(db, TABLE_FAVOURITE));
        String cols = join(columns, ",");
        db.execSQL(String.format("INSERT INTO %s (%s) SELECT %s FROM temp_%s",
                TABLE_FAVOURITE, cols, cols, TABLE_FAVOURITE));
        String dropQuery = "DROP TABLE temp_" + TABLE_FAVOURITE;
        db.execSQL(dropQuery);
    }

    public synchronized long addFav(Mews mews) {
        ContentValues values = new ContentValues();
        values.put(KEY_POST_ID, mews.post_id);
        values.put(KEY_META_KEY, mews.meta_key);
        values.put(KEY_LAT, mews.property_latitude);
        values.put(KEY_LONG, mews.property_longitude);
        values.put(KEY_TITLE, mews.post_title);
        values.put(KEY_CONTENT, mews.post_content);
        values.put(KEY_THUMBNAIL, mews.thumbnail);
        values.put(KEY_STATUS, mews.property_status);
        values.put(KEY_COUNTRY, mews.property_country);
        values.put(KEY_ADDRESS, mews.property_address);
        values.put(KEY_CATEGORY, mews.property_category);

        SQLiteDatabase db = DatabaseManager.getInstance().openDatabase();
        long id = db.insert(TABLE_FAVOURITE, null, values);
        DatabaseManager.getInstance().closeDatabase();

        return id;
    }

    public synchronized int unFav(Mews mews) {
        SQLiteDatabase db = DatabaseManager.getInstance().openDatabase();
        int res = db.delete(TABLE_FAVOURITE, KEY_POST_ID + " = ?",
                new String[]{String.valueOf(mews.post_id)});
        DatabaseManager.getInstance().closeDatabase();
        return res;
    }

    public synchronized ArrayList<Mews> getAllFavMews() {
        ArrayList<Mews> arrMews = new ArrayList<>();

        String raw_query = "SELECT * FROM " + TABLE_FAVOURITE + ";";

        SQLiteDatabase db = DatabaseManager.getInstance().openDatabase();
        Cursor c = db.rawQuery(raw_query, null);

        try {
            if (c.moveToFirst()) {
                do {
                    Mews mews = new Mews();
                    mews.distance = 0;
                    mews._id = c.getInt(c.getColumnIndex(KEY_ID));
                    mews.post_id = c.getString(c.getColumnIndex(KEY_POST_ID));
                    mews.meta_key = c.getString(c.getColumnIndex(KEY_META_KEY));
                    mews.property_latitude = c.getDouble(c.getColumnIndex(KEY_LAT));
                    mews.property_longitude = c.getDouble(c.getColumnIndex(KEY_LONG));
                    mews.post_title = c.getString(c.getColumnIndex(KEY_TITLE));
                    mews.post_content = c.getString(c.getColumnIndex(KEY_CONTENT));
                    mews.thumbnail = c.getString(c.getColumnIndex(KEY_THUMBNAIL));
                    mews.property_status = c.getString(c.getColumnIndex(KEY_STATUS));
                    mews.property_country = c.getString(c.getColumnIndex(KEY_COUNTRY));
                    mews.property_address = c.getString(c.getColumnIndex(KEY_ADDRESS));
                    mews.property_category = c.getString(c.getColumnIndex(KEY_CATEGORY));
                    arrMews.add(mews);
                } while (c.moveToNext());
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            c.close();
        }
        DatabaseManager.getInstance().closeDatabase();
        return arrMews;
    }

    public boolean isFav(Mews mews) {
        boolean isFav = false;
        String raw_query = "SELECT * FROM " + TABLE_FAVOURITE + " WHERE " + KEY_POST_ID + " = '" + mews.post_id + "'" + ";";

        SQLiteDatabase db = DatabaseManager.getInstance().openDatabase();
        Cursor c = db.rawQuery(raw_query, null);

        try {
            if (c.moveToFirst()) {
                isFav = true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            c.close();
        }
        DatabaseManager.getInstance().closeDatabase();
        return isFav;
    }
}

MyApplication.java

public class MyApplication extends Application {

    @Override
    public void onCreate() {
        super.onCreate();
        DatabaseManager.initializeInstance(new SQLiteHelper(this, BuildConfig.VERSION_CODE));
    }
}

SQLiteHelper.java

public class SQLiteHelper extends SQLiteOpenHelper {

    private static final String DATABASE_NAME = "_mews.db";

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

    @Override
    public void onCreate(SQLiteDatabase db) {
        FavouriteHandler fh = new FavouriteHandler();
        fh.createTable(db);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        FavouriteHandler favouriteHandler = new FavouriteHandler();
        favouriteHandler.upgradeTable(db);
    }

    public void DeleteAll() {
        SQLiteDatabase db = this.getWritableDatabase();
        try {
            String dropSql = "DROP TABLE " + FavouriteHandler.TABLE_FAVOURITE;
            db.execSQL(dropSql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        onCreate(db);
    }
}