How to Make and Use Sqlite Database in Android

//First  To Make DBHandler Class in Your Project

public class DBHandler extends SQLiteOpenHelper {
    private static final int DB_VERSION = 1;
    private static final String DB_NAME = "Test.db";
    //Table Name
    private static final String TABLE_NAME_CATEGORY = "maincategory";
    private static final String TABLE_NAME_SUB_CATEGORY = "subcategory";

    //Variable
    private static final String KEY_ID = "id";
    private static final String KEY_CLIENT_ID = "client_id";
    private static final String KEY_CATEGORY_ID = "category_id";
    private static final String KEY_CATEGORY_NAME = "category_name";
    private static final String KEY_CATEGORY_IMAGE = "category_image";
    private static final String KEY_SUB_CATEGORY_ID = "sub_category_id";
    private static final String KEY_SUB_CATEGORY_NAME = "sub_category_name";
    private static final String KEY_SUB_CATEGORY_DETAILS = "sub_category_detail";
    private static final String KEY_SUB_CATEGORY_TYPE = "sub_category_type";
    private static final String KEY_SUB_CATEGORY_IMAGE = "sub_category_image";
    private static final String KEY_SUB_CATEGORY_MENUTEST = "sub_category_menutest";
    private static final String KEY_SUB_CATEGORY_PRICE = "sub_category_price";
    private static final String KEY_SUB_CATEGORY_TASTE = "sub_category_menu_taste";
    private static final String KEY_SUB_CATEGORY_FOOD_TYPE = "sub_category_food_type";
    private static final String KEY_SUB_CATEGORY_ADDON = "sub_category_addon";
    private static final String KEY_TABLE_CATEGORY_ID = "table_category_id";
    private static final String KEY_TABLE_SUB_CATEGORY_ID = "table_subcategory_id";
    private static final String KEY_TABLE_NO_PERSON = "table_no_person";
    private static final String KEY_TABLE_NAME = "table_name";
    private static final String KEY_ITEM_PER_DAY = "item_day";
    private static final String KEY_ORDER_BY = "order_by";
    private static final String KEY_ITEM_NAME = "item_name";
    private static final String KEY_ITEM_TYPE = "item_type";
    private static final String KEY_ITEM_TASTE = "item_taste";
    private static final String KEY_ITEM_QTY = "item_qty";
    private static final String KEY_ITEM_DIET = "item_diet";
    private static final String KEY_ITEM_PRICE = "item_price";
    private static final String KEY_TOTAL_PRICE = "total_price";
    private static final String KEY_ITEM_ADDITIONAL_NOTE = "item_note";
    private static final String KEY_ORDER_STATUS = "order_status";
 
    String QUERY;

    String CREATE_TABLE_CATEGORY = "CREATE TABLE " + TABLE_NAME_CATEGORY + " (" + KEY_ID + " INTEGER PRIMARY KEY,"
            + KEY_CATEGORY_ID + " VARCHAR," + KEY_CATEGORY_NAME + " VARCHAR," + KEY_CATEGORY_IMAGE + " BOLB)";

    String CREATE_TABLE_SUB_CATEGORY = "CREATE TABLE " + TABLE_NAME_SUB_CATEGORY + " (" + KEY_ID + " INTEGER PRIMARY KEY,"
            + KEY_CATEGORY_ID + " VARCHAR," + KEY_SUB_CATEGORY_ID + " VARCHAR," + KEY_SUB_CATEGORY_NAME + " VARCHAR," +
            KEY_SUB_CATEGORY_TYPE + " VARCHAR," + KEY_SUB_CATEGORY_IMAGE + " BOLB," + KEY_SUB_CATEGORY_MENUTEST + " VARCHAR,"
            + KEY_SUB_CATEGORY_PRICE + " VARCHAR," + KEY_SUB_CATEGORY_DETAILS + " VARCHAR," + KEY_SUB_CATEGORY_TASTE + " VARCHAR,"
            + KEY_SUB_CATEGORY_FOOD_TYPE + " VARCHAR," + KEY_SUB_CATEGORY_ADDON + " VARCHAR)";

    String DROP_TABLE_CATEGORY = "DROP TABLE IF EXISTS " + CREATE_TABLE_CATEGORY;
    String DROP_TABLE_SUB_CATEGORY = "DROP TABLE IF EXISTS " + CREATE_TABLE_SUB_CATEGORY;

    public DBHandler(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_TABLE_CATEGORY);
        db.execSQL(CREATE_TABLE_SUB_CATEGORY);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL(DROP_TABLE_CATEGORY);
        db.execSQL(DROP_TABLE_SUB_CATEGORY);
        onCreate(db);
    }

    //To Add Category
    public void addCategory(CategoryConstant category) {
        SQLiteDatabase db = this.getWritableDatabase();
        try {
            ContentValues values = new ContentValues();
            values.put(KEY_CATEGORY_ID, category.getId());
            values.put(KEY_CATEGORY_NAME, category.getName());
            values.put(KEY_CATEGORY_IMAGE, category.getCatimage());
            db.insert(TABLE_NAME_CATEGORY, null, values);
            db.close();
        } catch (Exception e) {
            Log.e("problem", e + "");
        }
    }

    public void removeCategory() {
        SQLiteDatabase db = this.getWritableDatabase();
        db.execSQL("delete from " + TABLE_NAME_CATEGORY);
    }

    public ArrayList<CategoryConstant> getAllCategory() {
        SQLiteDatabase db = this.getReadableDatabase();
        ArrayList<CategoryConstant> categoryList = null;
        try {
            categoryList = new ArrayList<CategoryConstant>();
            String QUERY = "SELECT * FROM " + TABLE_NAME_CATEGORY;
            Cursor cursor = db.rawQuery(QUERY, null);
            if (!cursor.isLast()) {
                while (cursor.moveToNext()) {
                    CategoryConstant category = new CategoryConstant();
                    category.setID(cursor.getInt(0));
                    category.setId(cursor.getString(1));
                    category.setName(cursor.getString(2));
                    category.setCatimage(cursor.getString(3));
                    categoryList.add(category);
                }
            }
            db.close();
        } catch (Exception e) {
            Log.e("error", e + "");
        }
        return categoryList;
    }


    //To Add Sub Category
    public void addSubCategory(SubCategoryConstant category) {
        SQLiteDatabase db = this.getWritableDatabase();
        try {
            ContentValues values = new ContentValues();
            values.put(KEY_CATEGORY_ID, category.getCategoryId());
            values.put(KEY_SUB_CATEGORY_ID, category.getId());
            values.put(KEY_SUB_CATEGORY_NAME, category.getName());
            values.put(KEY_SUB_CATEGORY_TYPE, category.getVeg());
            values.put(KEY_SUB_CATEGORY_IMAGE, category.getMenuimage());
            values.put(KEY_SUB_CATEGORY_MENUTEST, category.getMenutest());
            values.put(KEY_SUB_CATEGORY_PRICE, category.getPrice());
            values.put(KEY_SUB_CATEGORY_DETAILS, category.getDetail());
            values.put(KEY_SUB_CATEGORY_TASTE, category.getMenutaste());
            values.put(KEY_SUB_CATEGORY_FOOD_TYPE, category.getFoodtype());
            values.put(KEY_SUB_CATEGORY_ADDON, category.getAddon());
            db.insert(TABLE_NAME_SUB_CATEGORY, null, values);
            db.close();
        } catch (Exception e) {
            Log.e("problem", e + "");
        }
    }

    public void removeSubCategory() {
        SQLiteDatabase db = this.getWritableDatabase();
        db.execSQL("delete from " + TABLE_NAME_SUB_CATEGORY);
    }

    public ArrayList<SubCategoryConstant> getAllSubCategory(String categoryid, String type) {
        String QUERY;
        SQLiteDatabase db = this.getReadableDatabase();
        ArrayList<SubCategoryConstant> categoryList = null;
        try {
            categoryList = new ArrayList<SubCategoryConstant>();
            if (type.equals("0")) {
                QUERY = "SELECT * FROM " + TABLE_NAME_SUB_CATEGORY + " WHERE " + KEY_CATEGORY_ID
                        + " = '" + categoryid + "'";
            } else {
                QUERY = "SELECT * FROM " + TABLE_NAME_SUB_CATEGORY + " WHERE " + KEY_CATEGORY_ID
                        + " = '" + categoryid + "' AND " + KEY_SUB_CATEGORY_TYPE + " = '" + type + "'";
            }
            Cursor cursor = db.rawQuery(QUERY, null);
            if (!cursor.isLast()) {
                while (cursor.moveToNext()) {
                    SubCategoryConstant category = new SubCategoryConstant();
                    category.setID(cursor.getInt(0));
                    category.setCategoryId(cursor.getString(1));
                    category.setId(cursor.getString(2));
                    category.setName(cursor.getString(3));
                    category.setVeg(cursor.getString(4));
                    category.setMenuimage(cursor.getString(5));
                    category.setMenutest(cursor.getString(6));
                    category.setPrice(cursor.getString(7));
                    category.setDetail(cursor.getString(8));
                    category.setMenutaste(cursor.getString(9));
                    category.setFoodtype(cursor.getString(10));
                    category.setAddon(cursor.getString(11));
                    categoryList.add(category);
                }
            }
            db.close();
        } catch (Exception e) {
            Log.e("error", e + "");
        }
        return categoryList;
    }

    public ArrayList<SubCategoryConstant> getSubCategoryPrice(String categoryid, String subcategoryid) {
        String QUERY;
        SQLiteDatabase db = this.getReadableDatabase();
        ArrayList<SubCategoryConstant> categoryList = null;
        try {
            categoryList = new ArrayList<SubCategoryConstant>();

            QUERY = "SELECT * FROM " + TABLE_NAME_SUB_CATEGORY + " WHERE " + KEY_CATEGORY_ID
                    + " = '" + categoryid + "' AND " + KEY_SUB_CATEGORY_ID + " = '" + subcategoryid + "'";
            Log.e("Query", QUERY);
            Cursor cursor = db.rawQuery(QUERY, null);
            if (!cursor.isLast()) {
                while (cursor.moveToNext()) {
                    SubCategoryConstant category = new SubCategoryConstant();
                    category.setID(cursor.getInt(0));
                    category.setCategoryId(cursor.getString(1));
                    category.setId(cursor.getString(2));
                    category.setName(cursor.getString(3));
                    category.setVeg(cursor.getString(4));
                    category.setMenuimage(cursor.getString(5));
                    category.setMenutest(cursor.getString(6));
                    category.setPrice(cursor.getString(7));
                    category.setDetail(cursor.getString(8));
                    category.setMenutaste(cursor.getString(9));
                    category.setFoodtype(cursor.getString(10));
                    category.setAddon(cursor.getString(11));
                    categoryList.add(category);
                }
            }
            db.close();
        } catch (Exception e) {
            Log.e("error", e + "");
        }
        return categoryList;
    }

    public void removeCartAllItem(String user_id) {
        //Open the database
        SQLiteDatabase database = this.getWritableDatabase();

        //Execute sql query to remove from database
        String sql = "DELETE FROM " + TABLE_NAME_ITEM_VALUE + " WHERE " + KEY_CLIENT_ID + " = '" + user_id + "' AND "
                + KEY_ORDER_STATUS + " = '" + "N" + "'";
        // Log.d("QUesry is",sql);
        //NOTE: When removing by String in SQL, value must be enclosed with ''
        database.execSQL(sql);

        //Close the database
        database.close();
    }

    public void updateItemValue(String user_id, String taste, String qty, String base_price, String diet, String note, String categoryId,
                                String tbl_cat_id, String tbl_sub_cate_id) {
        SQLiteDatabase db = this.getWritableDatabase();
        try {
            ContentValues values = new ContentValues();
            values.put(KEY_ITEM_TASTE, taste);
            values.put(KEY_ITEM_QTY, qty);
            values.put(KEY_ITEM_PRICE, base_price);
            values.put(KEY_ITEM_QTY, qty);
            values.put(KEY_ITEM_DIET, diet);
            values.put(KEY_ITEM_ADDITIONAL_NOTE, note);
            db.update(TABLE_NAME_ITEM_VALUE, values, KEY_CLIENT_ID + "='" + user_id + "' AND "
                    + KEY_SUB_CATEGORY_ID + "='" + categoryId + "' AND " + KEY_TABLE_CATEGORY_ID + " = '" + tbl_cat_id
                    + "' AND " + KEY_TABLE_SUB_CATEGORY_ID + " = '" + tbl_sub_cate_id + "'", null);
        } catch (Exception e) {
            Log.e("problem", e + "");
        } finally {
            if (db != null) {
                db.close();
            }
        }
    }
}