Cacher is the code snippet organizer for pro developers

//package com.example.ormtest;

import java.lang.reflect.Field;
import java.util.List;
import java.util.Locale;

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

public class DatabaseHelper {
	private static final String SQLITEDEBUG = "dbLog";
	private boolean log;
	private String[] sql;
	private SQLiteDatabase sqlite;

	/**
	 * For faster create sqlite db in android
	 * @param context
	 * @param dbname
	 * @param factory
	 * @param version
	 * @param log	log sql query
	 * @param obj	list of model object
	 */
	public DatabaseHelper(Context context, String dbname,
			CursorFactory factory, int version, boolean log, List<Object> obj) {
		this.log = log;
		sql = parseObject(obj);
		
		sqliteHelper helper = new sqliteHelper(context, dbname, factory, version);
		sqlite = helper.getWritableDatabase();
	}

	private String[] parseObject(List<Object> objects) {
		String sql_create = "";
		String sql_upgrade = "";
		for (Object object : objects) {
			String tb_name = object.getClass().getSimpleName()
					.toLowerCase(Locale.US);

			String sqlField = "";
			Field[] fields = object.getClass().getDeclaredFields();
			for (Field field : fields) {
				sqlField += field.getName() + " "
						+ typeColumn(field.getType().getSimpleName()) + ",";
			}
			sqlField = sqlField.endsWith(",") ? sqlField.substring(0,
					sqlField.lastIndexOf(",")) : sqlField;
			String result = "CREATE TABLE " + tb_name + " ("
					+ "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
					+ sqlField + ");";

			sql_upgrade += "DROP TABLE IF EXISTS " + tb_name + ";";
			sql_create += result;
		}
		return new String[] { sql_create, sql_upgrade };
	}

	private String typeColumn(String type) {
		String selectType = null;
		if (type.equalsIgnoreCase("String"))
			selectType = "TEXT";
		if (type.equalsIgnoreCase("int"))
			selectType = "INTEGER";
		if (type.equalsIgnoreCase("boolean"))
			selectType = "BLOB";
		if (type.equalsIgnoreCase("float"))
			selectType = "REAL";
		return selectType;
	}
	
	class sqliteHelper extends SQLiteOpenHelper{

		public sqliteHelper(Context context, String name,
				CursorFactory factory, int version) {
			super(context, name, factory, version);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			for (String string : sql[0].split(";")) {
				db.execSQL(string);
			}
			
			if (log) {
				Log.d(SQLITEDEBUG, "onCreate DB");
				Log.d(SQLITEDEBUG, sql[0]);
			}
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			for (String string : sql[1].split(";")) {
				db.execSQL(string);
			}
			onCreate(db);
			if (log) {
				Log.d(SQLITEDEBUG, "onUpgrade DB");
				Log.d(SQLITEDEBUG, sql[1]);
			}
		}
	}
}

package com.healtheworld.nicovideoplayer.helper.database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.MatrixCursor;
import android.database.MergeCursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.support.v7.app.AlertDialog;
import android.util.Base64;
import android.util.Log;
import android.view.View;

import com.healtheworld.nicovideoplayer.R;
import com.healtheworld.nicovideoplayer.config.Setting;
import com.healtheworld.nicovideoplayer.custom.recycleview.RecycleAdapter;

import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by YuHung on 5/29/2015.
 */
public class DatabaseUtils {
    private static final int RESULT_NOT_INIT = 0;
    private static final int RESULT_SUCCESS = 1;
    private static final int RESULT_CONFLIC = 2;
    private static Context mContext;
    private static SQLiteDatabase sqlite;

    public static void initial(Context context) {
        mContext = context;
        SqliteHelper helper = new SqliteHelper(context, Setting.getDatabaseName(), null, Setting.getDatabaseVerson());
        // make sure close any cursor
        helper.close();
        sqlite = helper.getWritableDatabase();
    }

    /**
     * clear & add new account to local database
     *
     * @param email
     * @param password
     * @param name     account anme
     * @param avatar   avatar image url
     * @return {@link #RESULT_SUCCESS}, {@link #RESULT_NOT_INIT}
     */
    public static final int cleanAndAddAccount(String email, String password, String name, String avatar) {
        int result = RESULT_NOT_INIT;
        if (sqlite != null) {
            // delete all fist
            deleteAllAccountSaved();

            ContentValues cv = new ContentValues();
            try {
                cv.put("email", encrypt(email));
                cv.put("password", encrypt(password));
                cv.put("name", encrypt(name));
                cv.put("avatar", encrypt(avatar));
                sqlite.insert(Setting.getTableAccountName(), null, cv);
            } catch (UnsupportedEncodingException e) {
                e.printStackTrace();
            }

            result = RESULT_SUCCESS;
        } else {
            Log.e(Setting.getLogTag(), "you have to call DatabaseUtils.initial()");
        }

        return result;
    }

    /**
     * @return get account saved in database
     */
    public static final AccountObject getAccountSaved() {
        AccountObject obj = null;
        if (sqlite != null) {
            Cursor cursor = sqlite.query(Setting.getTableAccountName(), null, null, null, null, null, null);
            if (cursor != null && cursor.moveToFirst()) {
                // add data to object && decrypt data
                obj = new AccountObject();
                obj.setEmail(decrypt(cursor.getString(cursor.getColumnIndex("email"))));
                obj.setPassword(decrypt(cursor.getString(cursor.getColumnIndex("password"))));
                obj.setName(decrypt(cursor.getString(cursor.getColumnIndex("name"))));
                obj.setAvatar(decrypt(cursor.getString(cursor.getColumnIndex("avatar"))));

                cursor.close();
            }
        } else {
            Log.e(Setting.getLogTag(), "you have to call DatabaseUtils.initial()");
        }
        return obj;
    }

    /**
     * delete account which saved before
     *
     * @return {@link #RESULT_SUCCESS}, {@link #RESULT_NOT_INIT}
     */
    public static final int deleteAllAccountSaved() {
        int result = RESULT_NOT_INIT;
        if (sqlite != null) {
            sqlite.delete(Setting.getTableAccountName(), null, null);
            result = RESULT_SUCCESS;
        } else {
            Log.e(Setting.getLogTag(), "you have to call DatabaseUtils.initial()");
        }

        return result;
    }

    /**
     * add fav container object to database
     *
     * @param name name of container
     * @return id inserted if success, {@link #RESULT_NOT_INIT}, {@link #RESULT_CONFLIC}
     */
    public static int addFavContainer(String name) {
        int result = RESULT_NOT_INIT;
        if (sqlite != null) {
            // check fav container before add
            Cursor cursor = sqlite.query(Setting.getTableFavContainer(), null, "name='" + name + "'", null, null, null, null);
            if (cursor != null && cursor.getCount() > 0) {
                result = RESULT_CONFLIC;
                Log.e(Setting.getLogTag(), "fav container name must be unique.");
            } else {
                ContentValues cv = new ContentValues();
                cv.put("name", name);
                result = (int) sqlite.insert(Setting.getTableFavContainer(), null, cv);
            }

            if (cursor != null) cursor.close();
        } else {
            Log.e(Setting.getLogTag(), "you have to call DatabaseUtils.initial()");
        }
        return result;
    }

    /**
     * delete fav container by id
     *
     * @param id fav container's id
     * @return {@link #RESULT_SUCCESS}, {@link #RESULT_NOT_INIT}
     */
    public static int deleteFavContainer(int id) {
        int result = RESULT_NOT_INIT;
        if (sqlite != null) {
            sqlite.delete(Setting.getTableFavContainer(), "id=" + id, null);

            // delete item which stay in container
            Cursor cursor = sqlite.query(Setting.getTableFavItem(), null, "favContainer=" + id, null, null, null, null);
            if (cursor != null && cursor.moveToFirst()) {
                while (!cursor.isAfterLast()) {
                    // delete all fav item found
                    deleteFavItem(cursor.getInt(cursor.getColumnIndex("id")));
                    cursor.moveToNext();
                }
                cursor.close();
            }
            result = RESULT_SUCCESS;
        } else {
            Log.e(Setting.getLogTag(), "you have to call DatabaseUtils.initial()");
        }

        return result;
    }

    /**
     * @return get all fav container cursor
     * @see com.healtheworld.nicovideoplayer.custom.recycleview.Holder#generatePopupMenu(View, int, Context, AlertDialog.Builder, RecycleAdapter)
     */
    public static Cursor getFavContainer() {
        Cursor cursor = null;
        if (sqlite != null) {
            cursor = sqlite.query(Setting.getTableFavContainer(), null, null, null, null, null, null);

            // add new fav container item ...
            MatrixCursor extras = new MatrixCursor(cursor.getColumnNames());
            extras.addRow(new String[]{"-1", mContext.getString(R.string.add_new_fav_container_item)});
            cursor = new MergeCursor(new Cursor[]{extras, cursor});
        } else {
            Log.e(Setting.getLogTag(), "you have to call DatabaseUtils.initial()");
        }
        return cursor;
    }

    /**
     * @param favContainerID ID of fav container
     * @return number item inside container which have param "favContainerID"
     */
    public static int countItemInFavContainer(int favContainerID) {
        int count = -1;
        if (sqlite != null) {
            Cursor cursor = sqlite.query(Setting.getTableFavItem(), null, "favContainer=?", new String[]{String.valueOf(favContainerID)}, null, null, null);
            count = cursor.getCount();
        } else {
            Log.e(Setting.getLogTag(), "you have to call DatabaseUtils.initial()");
        }
        return count;
    }

    /**
     * add fav item to database
     *
     * @param title        video name
     * @param author       uploader
     * @param uploadDate   upload date
     * @param views        view count
     * @param thumb        link video thumbnail
     * @param duration     video duration (hh:mm:ss)
     * @param favContainer id of container which contain fav item
     * @return {@link #RESULT_SUCCESS}, {@link #RESULT_NOT_INIT}
     */
    public static int addFavItem(String videoId, String title, String author, String uploadDate, String views, String thumb, String duration, int favContainer) {
        int result = RESULT_NOT_INIT;
        if (sqlite != null) {
            ContentValues cv = new ContentValues();
            cv.put("videoId", videoId);
            cv.put("title", title);
            cv.put("author", author);
            cv.put("uploadDate", uploadDate);
            cv.put("views", views);
            cv.put("thumb", thumb);
            cv.put("duration", duration);
            cv.put("favContainer", favContainer);

            sqlite.insert(Setting.getTableFavItem(), null, cv);
            result = RESULT_SUCCESS;
        } else {
            Log.e(Setting.getLogTag(), "you have to call DatabaseUtils.initial()");
        }
        return result;
    }

    /**
     * delete fav item by id
     *
     * @param id fav item's id
     * @return {@link #RESULT_SUCCESS}, {@link #RESULT_NOT_INIT}
     */
    public static int deleteFavItem(int id) {
        int result = RESULT_NOT_INIT;
        if (sqlite != null) {
            sqlite.delete(Setting.getTableFavItem(), "id=" + id, null);
            result = RESULT_SUCCESS;
        } else {
            Log.e(Setting.getLogTag(), "you have to call DatabaseUtils.initial()");
        }
        return result;
    }

    /**
     * check video is inserted in same fav container
     *
     * @param videoId        video id
     * @param favContainerId fav container id
     * @return true: if exist
     * @see com.healtheworld.nicovideoplayer.custom.recycleview.RecycleAdapter#addFavItem(int, int)
     */
    public static boolean isExistItemInFavContainer(String videoId, int favContainerId) {
        Cursor cursor = null;
        if (sqlite != null) {
            cursor = sqlite.query(Setting.getTableFavItem(), null, "videoId=? and favContainer=?", new String[]{videoId, String.valueOf(favContainerId)}, null, null, null);
        } else {
            Log.e(Setting.getLogTag(), "you have to call DatabaseUtils.initial()");
        }
        boolean result = cursor == null ? false : cursor.getCount() > 0;
        cursor.close();
        return result;
    }

    public static List<String> getAllFavItems() {
        List<String> list = null;
        if (sqlite != null) {
            Cursor cursor = sqlite.query(Setting.getTableFavItem(), null, null, null, null, null, null);
            if (cursor != null && cursor.moveToFirst()) {
                list = new ArrayList<>();
                while (!cursor.isAfterLast()) {
                    list.add(cursor.getString(cursor.getColumnIndex("videoId")));
                    cursor.moveToNext();
                }
                cursor.close();
            }
        } else {
            Log.e(Setting.getLogTag(), "you have to call DatabaseUtils.initial()");
        }
        return list;
    }

    /**
     * encrypt data before save to db
     *
     * @param str string to encrypt
     * @return
     * @throws UnsupportedEncodingException
     */
    public static final String encrypt(String str) throws UnsupportedEncodingException {
        return str != null ? new String(Base64.encode((str + Setting.getDefaultKey()).getBytes("utf-8"), Base64.NO_PADDING)) : null;
    }

    /**
     * decrypt data when use
     *
     * @param encrypted
     * @return
     */
    public static final String decrypt(String encrypted) {
        String result = null;
        if (encrypted != null) {
            String temp = new String(Base64.decode(encrypted, Base64.NO_PADDING));
            if (temp.length() > Setting.getDefaultKey().length()) {
                result = temp.substring(0, temp.length() - Setting.getDefaultKey().length());
            }
        }
        return result;
    }

    private static class SqliteHelper extends SQLiteOpenHelper {
        public SqliteHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
            super(context, name, factory, version);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            // create account table
            db.execSQL("CREATE TABLE " + Setting.getTableAccountName() + " (_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, email TEXT NOT NULL, password TEXT NOT NULL, name TEXT NOT NULL,avatar TEXT NOT NULL)");

            // create fav container
            db.execSQL("CREATE TABLE " + Setting.getTableFavContainer() + " (_id INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL, name TEXT  UNIQUE NOT NULL)");

            // create fav item
            db.execSQL("CREATE TABLE " + Setting.getTableFavItem() + " (_id INTEGER  PRIMARY KEY NOT NULL, videoId TEXT  NOT NULL, title TEXT  NOT NULL, author TEXT  NOT NULL, uploadDate TEXT  NOT NULL, views TEXT  NOT NULL, thumb TEXT  NOT NULL, duration TEXT  NOT NULL, favContainer INTEGER NOT NULL)");
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            // delete account table
            db.execSQL("DROP TABLE IF EXISTS " + Setting.getTableAccountName());

            // delete fav container table
            db.execSQL("DROP TABLE IF EXISTS " + Setting.getTableFavContainer());

            // delete fav item table
            db.execSQL("DROP TABLE IF EXISTS " + Setting.getTableFavItem());
            onCreate(db);
        }
    }
}