Insert and Retrieve Image from SQLite Database Android Example

So many people are confuse which data type is best for insert large amount of data(like images) in to database. At that point of time in SQLite (BLOB) type is prefer and this will be store binary data nothing but a byte array data.(Converting our image to byte array and store into SQLite)

Here we See One Example which is Store Employee Details Like

Employee Name
Employee Photo
Employee Age

For Employee Name we use text datatype and for Employee Age integer datatype.But to Store Employee Photo which datatype ? at that time we use BLOB datatype.

insertEmpDetails() method which is taken Employee Class as parameter and it store all Employee information into Employees Table.
here we can see before inserting image into database,we should  be convert your Bitmap image to byte array for that we are using Utility class getBytes()method.

retriveEmpDetails() method which is retrieve Employee Details from Database and return Employee Object.
To get all Name,Photo,Age we use Corresponding getXXX() methods.
At the time of retriving photo we will get blob[] array of image.at that time we need to convert byte array to image.for that purpose we use getPhoto() method in Utility class which will convert our blob array into Bitmap image and retrun Bitmap image.

UI Layout(activity_main.xml)

This layout used to view 

Employee Name
Employee Photo
Employee Age

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    android:orientation="vertical" >

    <ImageView
        android:id="@+id/ivMain"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:scaleType="fitCenter" />

    <LinearLayout
        android:layout_width="fill_parent"
        android:layout_height="wrap_content" >

        <TextView
            android:id="@+id/text1"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="EMPLOYEE_NAME" />

        <TextView
            android:id="@+id/name"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_marginLeft="25dp"
            android:text="NAME" />
    </LinearLayout>

    <LinearLayout
        android:layout_width="fill_parent"
        android:layout_height="wrap_content" >

        <TextView
            android:id="@+id/text2"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="EMPLOYEE_PHOTO" />

        <ImageView
            android:id="@+id/photo"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_marginLeft="25dp" />
    </LinearLayout>

    <LinearLayout
        android:layout_width="fill_parent"
        android:layout_height="wrap_content" >

        <TextView
            android:id="@+id/text3"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="EMPLOYEE_AGE" />

        <TextView
            android:id="@+id/age"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_marginLeft="25dp"
            android:text="AGE" />
    </LinearLayout>

</LinearLayout>


Android Activity(InsertandRetriveBlobData.java)

package com.androidsurya.sqliteexample;

import android.app.Activity;
import android.graphics.BitmapFactory;
import android.os.Bundle;
import android.widget.ImageView;
import android.widget.TextView;

public class InsertandRetriveBlobData extends Activity {
private DBhelper DbHelper;

public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
DbHelper = new DBhelper(this);
Employee employee_One = new Employee(BitmapFactory.decodeResource(
getResources(), R.drawable.photo), "Surya", 25);
DbHelper.open();
DbHelper.insertEmpDetails(employee_One);
DbHelper.close();
employee_One = null;
DbHelper.open();
employee_One = DbHelper.retriveEmpDetails();
DbHelper.close();

TextView empname = (TextView) findViewById(R.id.name);
empname.setText(employee_One.getName());
ImageView empphoto = (ImageView) findViewById(R.id.photo);
empphoto.setImageBitmap(employee_One.getBitmap());
TextView empage = (TextView) findViewById(R.id.age);
empage.setText("" + employee_One.getAge());

}
}

DBhelper.java

This is database class here we declare

insertEmpDetails() method to insert employee details and
retriveEmpDetails() method to retrive employee details 

package com.androidsurya.sqliteexample;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DBhelper {
public static final String EMP_ID = "id";
public static final String EMP_NAME = "name";
public static final String EMP_AGE = "age";
public static final String EMP_PHOTO = "photo";

private DatabaseHelper mDbHelper;
private SQLiteDatabase mDb;

private static final String DATABASE_NAME = "EmployessDB.db";
private static final int DATABASE_VERSION = 1;

private static final String EMPLOYEES_TABLE = "Employees";

private static final String CREATE_EMPLOYEES_TABLE = "create table "
+ EMPLOYEES_TABLE + " (" + EMP_ID
+ " integer primary key autoincrement, " + EMP_PHOTO
+ " blob not null, " + EMP_NAME + " text not null unique, "
+ EMP_AGE + " integer );";

private final Context mCtx;

private static class DatabaseHelper extends SQLiteOpenHelper {
DatabaseHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}

public void onCreate(SQLiteDatabase db) {
db.execSQL(CREATE_EMPLOYEES_TABLE);
}

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS " + EMPLOYEES_TABLE);
onCreate(db);
}
}

public void Reset() {
mDbHelper.onUpgrade(this.mDb, 1, 1);
}

public DBhelper(Context ctx) {
mCtx = ctx;
mDbHelper = new DatabaseHelper(mCtx);
}

public DBhelper open() throws SQLException {
mDb = mDbHelper.getWritableDatabase();
return this;
}

public void close() {
mDbHelper.close();
}

public void insertEmpDetails(Employee employee) {
ContentValues cv = new ContentValues();
cv.put(EMP_PHOTO, Utility.getBytes(employee.getBitmap()));
cv.put(EMP_NAME, employee.getName());
cv.put(EMP_AGE, employee.getAge());
mDb.insert(EMPLOYEES_TABLE, null, cv);
}

public Employee retriveEmpDetails() throws SQLException {
Cursor cur = mDb.query(true, EMPLOYEES_TABLE, new String[] { EMP_PHOTO,
EMP_NAME, EMP_AGE }, null, null, null, null, null, null);
if (cur.moveToFirst()) {
byte[] blob = cur.getBlob(cur.getColumnIndex(EMP_PHOTO));
String name = cur.getString(cur.getColumnIndex(EMP_NAME));
int age = cur.getInt(cur.getColumnIndex(EMP_AGE));
cur.close();
return new Employee(Utility.getPhoto(blob), name, age);
}
cur.close();
return null;
}
}

Utility.java

Utility class is main role here
Before inserting image and retrive image from SQLite Database we use these below methods.
getBytes() Convert bitmap image to byte array and retrun byte[]
getPhoto() convert byte[] to bitmap and retrun bitmap image

package com.androidsurya.sqliteexample;

import java.io.ByteArrayOutputStream;

import android.graphics.Bitmap;
import android.graphics.Bitmap.CompressFormat;
import android.graphics.BitmapFactory;

public class Utility {
// convert from bitmap to byte array
public static byte[] getBytes(Bitmap bitmap) {
ByteArrayOutputStream stream = new ByteArrayOutputStream();
bitmap.compress(CompressFormat.PNG, 0, stream);
return stream.toByteArray();
}

// convert from byte array to bitmap
public static Bitmap getPhoto(byte[] image) {
return BitmapFactory.decodeByteArray(image, 0, image.length);
}
}

Register Android Activity in AndroidManifest file

 <activity
            android:name="com.androidsurya.sqliteexample.InsertandRetriveBlobData"
            android:label="@string/app_name" >

Output Screenshot 

Next Example using Sqlite Database

How to insert multiple images and retrieve multiple images from sqlite database example in Android.

http://androidsurya.blogspot.in/2014/01/multiple-images-insert-and-retrieve.html

Review/Feedback are always welcome