How can I insert an image into my database?

Question

So, I have a database that stores recipes. Right now, the Recipe table just holds the data like name, type, etc. But, since I want to show a picture of each recipe in the app, I need to somehow store the images into the database but I don't know how.

Heres what I have so far

public class DBHandler extends SQLiteOpenHelper{

//    private static class DatabaseHelper extends SQLiteOpenHelper {

        public static final String COLUMN_ROWID = "_id";
        public static final String COLUMN_NAME = "name";
        public static final String COLUMN_TYPE = "type";
        public static final String COLUMN_INGRED = "ingred";

        private static final String TAG = "DBHandler";
        //private DatabaseHelper mDbHelper;
        //private SQLiteDatabase mDb;

        private static final String DATABASE_NAME = "Recipes";
        private static final String SQLITE_TABLE = "TABLE_RECIPES";
        private static final int DATABASE_VERSION = 1;

        //private final Context mCtx;
    public DBHandler(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }
/*        DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }*/

        @Override
        public void onCreate(SQLiteDatabase db) {
            String DATABASE_CREATE =
                    "CREATE TABLE if not exists " + SQLITE_TABLE + " (" +
                            COLUMN_ROWID + " integer PRIMARY KEY autoincrement," +
                            COLUMN_NAME + "," +
                            COLUMN_TYPE + "," +
                            COLUMN_INGRED + ")";

            Log.w(TAG, DATABASE_CREATE);
            db.execSQL(DATABASE_CREATE);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                    + newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS " + SQLITE_TABLE);
            onCreate(db);
        }

/*    public DBHandler(Context ctx) {
        this.mCtx = ctx;
    }*/
/*
    public DBHandler open() throws SQLException {
        mDbHelper = new DatabaseHelper(mCtx);
        mDb = mDbHelper.getWritableDatabase();
        return this;
    }*/

/*    public void close() {
        if (mDbHelper != null) {
            mDbHelper.close();
        }
    }*/

    public void createRecipe(String name,
                              String type, String ingred) {

        ContentValues initialValues = new ContentValues();
        //initialValues.put(COLUMN_CODE, code);
        initialValues.put(COLUMN_NAME, name);
        initialValues.put(COLUMN_TYPE, type);
        initialValues.put(COLUMN_INGRED, ingred);

        SQLiteDatabase db = this.getWritableDatabase();
        db.insert(SQLITE_TABLE, null, initialValues);
        db.close();
    }

    public boolean deleteAllRecipes() {
        int doneDelete = 0;

        SQLiteDatabase mDb = this.getWritableDatabase();
        doneDelete = mDb.delete(SQLITE_TABLE, null , null);
        Log.w(TAG, Integer.toString(doneDelete));
        return doneDelete > 0;

    }

    public Cursor fetchRecipesByName(String inputText) throws SQLException {
        SQLiteDatabase mDb = this.getWritableDatabase();
        Log.w(TAG, inputText);
        Cursor mCursor = null;
        if (inputText == null  ||  inputText.length () == 0)  {
            mCursor = mDb.query(SQLITE_TABLE, new String[] {COLUMN_ROWID,
                            COLUMN_NAME, COLUMN_TYPE, COLUMN_INGRED},
                    null, null, null, null, null);

        }
        else {

            mCursor = mDb.query(true, SQLITE_TABLE, new String[] {COLUMN_ROWID,
                            COLUMN_NAME, COLUMN_TYPE, COLUMN_INGRED},
                    COLUMN_NAME + " like '%" + inputText + "%'" + " or " +
                            COLUMN_TYPE + " like '%" + inputText + "%'" + " or " +
                            COLUMN_INGRED + " like '%" + inputText + "%'",
                    null, null, null, null, null);
        }
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;

    }


    public Cursor fetchAllRecipes() {
        SQLiteDatabase mDb = this.getWritableDatabase();

        Cursor mCursor = mDb.query(SQLITE_TABLE, new String[] {COLUMN_ROWID,
                        COLUMN_NAME, COLUMN_TYPE, COLUMN_INGRED},
                null, null, null, null, null);

        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;
    }

    public void insertSomeRecipes() {

        createRecipe("Blackened Salmon","Dinner","Salmon");
    }

    public boolean deleteRecipe(String name){
        SQLiteDatabase mDb = this.getWritableDatabase();
        boolean result = false;

        String sql_query = "SELECT * FROM " + SQLITE_TABLE + " WHERE " + COLUMN_NAME + " = \"" + name + "\"";

        Cursor myCursor = mDb.rawQuery(sql_query, null);
        Recipes myRecipe = new Recipes();

        if (myCursor.moveToFirst()){
            myRecipe.setID(myCursor.getInt(0));
            mDb.delete(SQLITE_TABLE, COLUMN_ROWID + " = ?", new String[]{String.valueOf(myRecipe.getID())});
            myCursor.close();
            result = true;
        }

        mDb.close();
        return result;

    }

}

Suggestion: Instead of storing the image itself in your database, save it to file and then store the filename/path in your database instead. Otherwise you're either looking at working with BLOB's, or base64-encoding your data on storage/retrieval if you want to store it as text. — aroth, Apr 29 '15 at 04:04
Take a look here: http://stackoverflow.com/questions/7331310/how-to-store-image-as-blob-in-sqlite-how-to-retrieve-it — Samurai, Apr 29 '15 at 04:10

score 1 · Answer 1 · answered Apr 29 '15 at 04:07

Well for storing images you can try this

If you want to save images which are not coming from server and are stored in drawable/mipmap folder just store their id like

initialValues.put(COLUMN_IMAGE, R.mipmap.demp);
And if they are coming from server or api call just save their url and load them using any library such as picaso or something.

I tried the same in one of my app it worked.

score 0 · Answer 2 · answered Apr 29 '15 at 04:12

Convert your image into base64 encoded string and then insert that string into database:

Bitmap bm = BitmapFactory.decodeFile("<path to your image>");
ByteArrayOutputStream baos = new ByteArrayOutputStream();  
bm.compress(Bitmap.CompressFormat.JPEG, 100, baos);  
byte[] b = baos.toByteArray(); 
String encodedString = Base64.encodeToString(b, Base64.DEFAULT);

When your fetch the string then convert it like this:

byte[] b= Base64.decode(encodedString , Base64.DEFAULT);
Bitmap decodedByte = BitmapFactory.decodeByteArray(b,0,b.length);

score 0 · Answer 3 · answered Apr 29 '15 at 04:19

This is a method to store your bitmap image into database, you need to assign a type of column as Blob for image in database table

protected long saveBitmap(SQLiteDatabase database, Bitmap bmp) {
        Log.d("ImageDatabase", "::  Method Called");
        int bytes = bmp.getByteCount();
        ByteBuffer buffer = ByteBuffer.allocate(bytes);
        bmp.copyPixelsToBuffer(buffer);
        byte[] array = buffer.array();

        Log.d("ImageDatabase", "::  ByteArray Created");
        ContentValues cv = new ContentValues();
        cv.put("img", bytes);
        Log.d("ImageDatabase", "::  Before Insert");
        long rs = database.insert("images", null, cv);
        Log.d("ImageDatabase", "::  After Insert");
        return rs;
    }

score 0 · Accepted Answer · answered Apr 29 '15 at 04:45

The simplest technique you can follow is to store your Image path in the database table as a String and then you can get it back whenever you need that image and decode it using BitmapFactory.decodeFile. Storing Base64String is fine but the string will have too large so, if you will see the path of image it is comparatively a shorter string

For Example:

define another parameter in your createRecipe() method named String imagePath like:

public void createRecipe(String name,String type, String ingred,String imgPath){}

When you make a call to this function you will pass the image path what ever you have like:

createRecipe(name,type,ingred,path_of_image)

To get ImagePath you can do something like that:

String destination = new File(Environment.getExternalStorageDirectory(), name_of_your_image+ ".jpg");
FileInputStream in = new FileInputStream(destination);
String photoPath = destination.getAbsolutePath();

Finally when you call your fetchRecipesByName() or fetchAllRecipes() method the Cursor will now have your path stored against each entry you can get this path as a String and pass it to the below function you will get Bitmap in return.

private Bitmap getBitmapFromPath(String path)
{     
    BitmapFactory.Options btmapOptions = new BitmapFactory.Options();

    btmapOptions.inSampleSize = 2;

    Bitmap bm = BitmapFactory.decodeFile(path, btmapOptions);

    return bm;
}

Note: You should also take care if you are storing path of image and it could be deleted from gallery so in return you cannot have any bitmap against this path so you should manage if image dosen't exist then use an alternative image.

Thanks so much for making this so simple. This seems like a much better idea. But, where exactly would the getBitmapFromPath function go? — , Apr 30 '15 at 01:36
It will be called when you make a call of your function `fetchRecipesByName()` or `fetchAllRecipes()` method, so fetch image path from DB via cursor and pass that path to this `getImageFromPath()` function it will return you bitmap — Zubair Ahmed, Apr 30 '15 at 04:02

How can I insert an image into my database?

4 Answers4