How to insert and retrieve image from sqlite database?

Question

I am working on a android application in which the data will be fetched from sqlite, here I am able to insert and retrieve string values but now I want to insert and retrieve the image? Can any one help me with this.

This is my DBAdapter class in which I am creating db, columns and inserting data in it.

public class DBAdapter {

    public static final String KEY_ROWID = "_id";
    public static final String Product = "product";
    public static final String Price = "price";
    public static final String InStock = "instock";
    public static final String OutOfStock = "outofstock";
    //public static final String imgs = "img";
    private static final String TAG = "CountriesDbAdapter";
    private DatabaseHelper mDbHelper;
    private SQLiteDatabase mDb;

    private static final String DATABASE_NAME = "World";
    private static final String SQLITE_TABLE = "Country";
    private static final int DATABASE_VERSION = 1;

    private final Context mCtx;

    private static final String DATABASE_CREATE =
            "CREATE TABLE if not exists " + SQLITE_TABLE + " (" +
                    KEY_ROWID + " integer PRIMARY KEY autoincrement," +
                    Product + "," +
                    Price + "," +
                    InStock + "," +
                    OutOfStock + "," +
                    " UNIQUE (" + Product +"));";

    private static class DatabaseHelper extends SQLiteOpenHelper {

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


        @Override
        public void onCreate(SQLiteDatabase db) {
            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 DBAdapter(Context ctx) {
        this.mCtx = ctx;
    }

    public DBAdapter open() throws SQLException {
        mDbHelper = new DatabaseHelper(mCtx);
        mDb = mDbHelper.getWritableDatabase();
        return this;

    }

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

    public long createCountry(String product, String price,
                              String instock, String outofstock) {

        ContentValues initialValues = new ContentValues();
        initialValues.put(Product, product);
        initialValues.put(Price, price);
        initialValues.put(InStock, instock);
        initialValues.put(OutOfStock, outofstock);
//        initialValues.put(imgs,img);
        return mDb.insert(SQLITE_TABLE, null, initialValues);
    }

    public boolean deleteAllCountries() {

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

    }

   /* public Cursor fetchCountriesByName(String inputText) throws SQLException {
        Log.w(TAG, inputText);
        Cursor mCursor = null;
        if (inputText == null  ||  inputText.length () == 0)  {
            mCursor = mDb.query(SQLITE_TABLE, new String[] {KEY_ROWID,
                            Product, Price, InStock, OutOfStock},
                    null, null, null, null, null);

        }
        else {
            mCursor = mDb.query(true, SQLITE_TABLE, new String[] {KEY_ROWID,
                            Product, Price, InStock, OutOfStock},
                    Product + " like '%" + inputText + "%'", null,
                    null, null, null, null);
        }
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;

    }*/

    public Cursor fetchAllCountries() {

        Cursor mCursor = mDb.query(SQLITE_TABLE, new String[] {KEY_ROWID,
                        Product, Price, InStock, OutOfStock},
                null, null, null, null, null);

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

    public void insertSomeCountries() {

        createCountry("NOKIA X201","10,000","InStock","OutOfStock");
        createCountry("SAMSUNG Galaxy Note","30,000","OutOfStock","InStock");
        createCountry("IPHONE 6S+","65,000","InStock","OutOfStock");
        createCountry("MOTO G2","12,999","InStock","OutOfStock");
        createCountry("IBALL ANDY", "11,000", "OutOfStock", "InStock");
        createCountry("MOTO G4", "13,999", "InStock", "OutOfStock");
        createCountry("SONY XPERIA", "8,000", "OutOfStock", "InStock");

    }

}

score 0 · Answer 1 · answered Jun 22 '16 at 09:22

Try this,

Insert image as an byte array in database

public void addImage( String name, byte[] image) throws SQLiteException{
    SQLiteDatabase database = this.getWritableDatabase();
    ContentValues cv = new  ContentValues();
    cv.put(KEY_NAME,    name);
    cv.put(KEY_IMAGE,   image);
    database.insert( DB_TABLE, null, cv );
}

To retrieve image

 byte[] image = cursor.getBlob(1);

Note:

Before inserting into database, you need to convert your Bitmap image into byte array first then apply it using database query.
When retrieving from database, you certainly have a byte array of image, what you need to do is to convert byte array back to original image. So, you have to make use of BitmapFactory to decode

score 0 · Answer 2 · edited May 23 '17 at 12:08

0

My suggestion is that you avoid storing images in database. It is much faster to store image on disk, and write a path to it in database.

But if you still want to write images to database, you can find a solution here: writing image to db and here writting image to db2

You must use BLOB (binary large object) to store your image in your sqlite database.

edited May 23 '17 at 12:08

Community

1
1

answered Jun 22 '16 at 09:22

vanste25

1,754
14
39

score 0 · Answer 3 · answered Jun 22 '16 at 09:26

Convert your Bitmap image into byteArray using

Convert Bitmap to ByteArray.

Bitmap bitmap = BitmapFactory.decodeResource(getResources(),R.drawable.myImage);
ByteArrayOutputStream opstream = new ByteArrayOutputStream();
bitmap.compress(Bitmap.CompressFormat.PNG, 100, opstream);
byte[] bytArray = opstream.toByteArray();

and then store it into database.

then receive byteArray using,

byte[] imageArray = cursor.getBlob(YOUR_COLUMN_POSITION);

after that Convert byte[] to Bitmap using,

Convert ByteArray to Bitmap.

Bitmap bitmap = BitmapFactory.decodeByteArray(bytArray, 0, bytArray.length);
ImageView img = (ImageView) findViewById(R.id.imgv1);
img.setImageBitmap(bitmap);

This might helps you.

How to insert and retrieve image from sqlite database?

3 Answers3