How to Store Image as Blob in Sqlite & How to Retrieve It

how to store Image as blob in Sqlite & how to retrieve it?

Here the code i used for my app

This code will take a image from url and convert is to a byte array

byte[] logoImage = getLogoImage(IMAGEURL);

private byte[] getLogoImage(String url){
     try {
             URL imageUrl = new URL(url);
             URLConnection ucon = imageUrl.openConnection();

             InputStream is = ucon.getInputStream();
             BufferedInputStream bis = new BufferedInputStream(is);

             ByteArrayBuffer baf = new ByteArrayBuffer(500);
             int current = 0;
             while ((current = bis.read()) != -1) {
                  baf.append((byte) current);
             }

             return baf.toByteArray();
     } catch (Exception e) {
          Log.d("ImageManager", "Error: " + e.toString());
     }
     return null;
}

To save the image to db i used this code.

 public void insertUser(){
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        String delSql = "DELETE FROM ACCOUNTS";
        SQLiteStatement delStmt = db.compileStatement(delSql);
        delStmt.execute();

        String sql = "INSERT INTO ACCOUNTS (account_id,account_name,account_image) VALUES(?,?,?)";
        SQLiteStatement insertStmt = db.compileStatement(sql);
        insertStmt.clearBindings();
        insertStmt.bindString(1, Integer.toString(this.accId));
        insertStmt.bindString(2,this.accName);
        insertStmt.bindBlob(3, this.accImage);
        insertStmt.executeInsert();
        db.close();
}

To retrieve the image back this is code i used.

public Account getCurrentAccount() {
    SQLiteDatabase db = dbHelper.getWritableDatabase();
    String sql = "SELECT * FROM ACCOUNTS";
    Cursor cursor = db.rawQuery(sql, new String[] {});

    if(cursor.moveToFirst()){
        this.accId  = cursor.getInt(0);
        this.accName = cursor.getString(1);
        this.accImage = cursor.getBlob(2);
    }
    if (cursor != null && !cursor.isClosed()) {
        cursor.close();
    }
    db.close();
    if(cursor.getCount() == 0){
        return null;
    } else {
        return this;
    }
}

Finally to load this image to a imageview

logoImage.setImageBitmap(BitmapFactory.decodeByteArray( currentAccount.accImage, 
        0,currentAccount.accImage.length));

How i can save an Image in Sqlite database as blob?

SQLite is meant to contain around ~400KB data. storing images inside a SQLite Db would'nt be nice. The image path method written above is better.

You will be better off using a image loading library as Picasso, Universal Image Loader etc if it suits your needs. This way you can just persist the image url. The image is cached by the library.

How to display a BLOB image stored in my SQLite database?

Now that you have the BLOB you can use io.BytesIO. I will create an example to demonstrate, like:

from PIL import Image, ImageTk
from io import BytesIO

def show(data):
    img_byte = BytesIO(data)
    img = ImageTk.PhotoImage(Image.open(img_byte))
    Label(root,image=img).pack()
    root.image = img # Keep a reference

So now you can query the database and fetch the value:

def fetch():
    c = con.cursor()
    id = 1 # Any id
    c.execute('SELECT photo FROM test where id=?',(id,))
    data = c.fetchall()[0][0] # Get the blob data
    show(data) # Call the function with the passes data

This will show the image in a label in the root window for the entered id.

Android: Store large images in SQLite as BLOB efficiently without decreasing their quality

Even if you manage to store a large image in the DB you won't be able to retrieve it unfortunately. Db cursor size limit.

---

Related Topics