How to perform CRUD Operations in Android SQLite with Blob

In this post, I will show you how to perform CRUD (Create,Read, Update and Delete) operation with Images from Gallery in SQLite which is embedded in Android devices.
Codes:
AndroidManifest.xml
Don't forget to add the following permission in your manifest file.

DBHelper.java
Open DBHelper.java and replace it with the following code.
package com.example.blob.helper;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
 
public class DBHelper extends SQLiteOpenHelper {

 static DBHelper dbhelper;
 static final String DATABASE_NAME = "IMAGE_EX";
 static final int DATABASE_VERSION = 1;
 public static final String IMAGE_TABLE="image_table";
 public static final String IMAGE_="image";
 public static final String IMAGE_NAME="image_name";
 public static final String IMAGE_ID="id";

 public static final String IMAGE_EX = "CREATE TABLE "+IMAGE_TABLE+" ("+IMAGE_ID + " INTEGER PRIMARY KEY,"+IMAGE_NAME+ " VARCHAR(55) DEFAULT NULL," + IMAGE_+" BLOB DEFAULT NULL);";

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

 @Override
 public void onCreate(SQLiteDatabase db) {

  db.execSQL(IMAGE_EX);

 }
  
 @Override
 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
  Log.w(DBHelper.class.getName(), "Upgrading database from version " + oldVersion + " to " + newVersion+ ". Old data will be destroyed");
  db.execSQL("DROP TABLE IF EXISTS"+ IMAGE_TABLE);
  }
  
 }

InsertHelper.java
Open InsertHelper.java and replace it with the following code.

package com.example.blob.helper;
 
import android.content.ContentValues;
import android.content.Context;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
 
public class InsertHelper {

 private Context context;
 private SQLiteDatabase mDb;
 private DBHelper dbHelper;

 public InsertHelper(Context context) {
  this.context = context;
 }

 public InsertHelper open() throws SQLException {
  dbHelper = new DBHelper(context);
  mDb = dbHelper.getWritableDatabase();
  return this;
 }

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

 public long insert_profile(byte[] byteImage, String data){

  ContentValues values = new ContentValues();
  values.put(DBHelper.IMAGE_, byteImage);
  values.put(DBHelper.IMAGE_NAME, data);

  Log.w("Position: ", "Inserted Values-->" + values);

  return mDb.insert(DBHelper.IMAGE_TABLE, null, values);

 }
}

UpdateHelper.java
Open UpdateHelper.java and replace it with the following code.

package com.example.blob.helper;
 
import android.content.ContentValues;
import android.content.Context;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
 
public class UpdateHelper {

 private Context context;
 private SQLiteDatabase mDb;
 private DBHelper dbHelper;

 public UpdateHelper(Context context) {
  this.context = context;
 }

 public UpdateHelper open() throws SQLException {
  dbHelper = new DBHelper(context);
  mDb = dbHelper.getWritableDatabase();
  return this;
 }

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

 public long update_profile(String id, byte[] byteImage, String data) {

  ContentValues Values = new ContentValues();
  Values.put(DBHelper.IMAGE_NAME,data);
  Values.put(DBHelper.IMAGE_,byteImage);
         return mDb.update(DBHelper.IMAGE_TABLE, Values, DBHelper.IMAGE_ID + "=" + id, null);

 }
}

DeleteHelper.java
Open DeleteHelper.java and replace it with the following code.

package com.example.blob.helper;
 
import android.content.Context;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
 
public class DeleteHelper {

 private Context context;
 private SQLiteDatabase mDb;
 private DBHelper dbHelper;

 public DeleteHelper(Context context) {
  this.context = context;
 }

 public DeleteHelper open() throws SQLException {
  dbHelper = new DBHelper(context);
  mDb = dbHelper.getWritableDatabase();
  return this;
 }

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

 public long delete_profile(String id) {
 
  return mDb.delete(DBHelper.IMAGE_TABLE, DBHelper.IMAGE_ID + "=" + id, null);

 }
}

activity_main.xml
Create activity_main.xml and replace it with the following code.

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
 xmlns:tools="http://schemas.android.com/tools"
 android:layout_width="match_parent"
 android:layout_height="match_parent"
 android:orientation="vertical"
 android:weightSum="5"
 tools:context=".MainActivity">

 <Button
  android:id="@+id/add"
  android:layout_width="fill_parent"
  android:layout_height="wrap_content"
  android:layout_weight="0.3"
  android:background="@android:color/holo_red_dark"
  android:text="@string/add_hint"
  android:textColor="@android:color/white"/>

 <ListView
  android:id="@+id/list"
  android:layout_width="wrap_content"
  android:layout_height="wrap_content"
  android:layout_weight="4.7"/>
 
</LinearLayout>

list_item_profile.xml
Create list_item_profile.xml and replace it with the following code.

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
 android:id="@+id/ll"
 android:layout_width="match_parent"
 android:layout_height="wrap_content"
 android:gravity="center"
 android:orientation="horizontal"
 android:padding="13dp">

 <ImageView
  android:id="@+id/profile_image"
  android:layout_width="75dp"
  android:layout_height="75dp"
  android:contentDescription="@string/app_name"
  android:scaleType="fitXY"
  android:src="@mipmap/ic_launcher"/>

 <TextView
  android:id="@+id/profile_name"
  android:layout_width="250dp"
  android:layout_height="match_parent"
  android:gravity="center"
  android:padding="5dp"
  android:text="@string/app_name"/>

</LinearLayout>

MainActivity.java
Open MainActivity.java and replace it with the following code.

package com.example.blob;
 
import android.content.Intent;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.util.Log;
import android.view.View;
import android.widget.AdapterView;
import android.widget.Button;
import android.widget.ListView;
import android.widget.Toast;
 
import com.example.blob.adapter.ProfileListAdapter;
import com.example.blob.helper.DBHelper;
import com.example.blob.helper.DeleteHelper;
 
import java.util.ArrayList;
 
public class MainActivity extends AppCompatActivity {

 Button btn_add;
 ListView listView;
 ArrayList image_name = new ArrayList<>();
 ArrayList image = new ArrayList<>();
 ArrayList image_id = new ArrayList<>();
 ProfileListAdapter adapter;
 Intent intent;
 DBHelper dbHelper;
 DeleteHelper del;
 SQLiteDatabase database;

 @Override
 protected void onCreate(Bundle savedInstanceState) {
  super.onCreate(savedInstanceState);
  setContentView(R.layout.activity_main);

  btn_add = (Button) findViewById(R.id.add);
  listView = (ListView) findViewById(R.id.list);

  // Clear the ArrayLists
  image_name.clear();
  image.clear();
  image_id.clear();

  display_data();

  // the helper class for DB creation operation
  dbHelper = new DBHelper(this);

  // the helper class for doing delete operation
  del = new DeleteHelper(this);

  btn_add.setOnClickListener(new View.OnClickListener() {
   @Override
   public void onClick(View v) {
    intent = new Intent(MainActivity.this, AddUpdateActivity.class);
    intent.putExtra("update", false);
    startActivity(intent);
    finish();
   }
  });

  // click event for updating the selected profile
  listView.setOnItemClickListener(new AdapterView.OnItemClickListener() {
   @Override
   public void onItemClick(AdapterView parent, View view, int position, long id) {
    intent = new Intent(MainActivity.this, AddUpdateActivity.class);
    intent.putExtra("id", image_id.get(position));
    intent.putExtra("image", image.get(position));
    intent.putExtra("name", image_name.get(position));
    intent.putExtra("update", true);
    startActivity(intent);
   }
  });

  // long click event for deleting the selected profile
  listView.setOnItemLongClickListener(new AdapterView.OnItemLongClickListener() {
   @Override
   public boolean onItemLongClick(AdapterView parent, View view, final int position, long id) {

    del.open();
    long ret = del.delete_profile(image_id.get(position));
    del.close();

    if(ret>0){
     Toast.makeText(getApplicationContext(), "Try Again!", Toast.LENGTH_SHORT).show();
    } else {
     Toast.makeText(getApplicationContext(), "Successfully Deleted!", Toast.LENGTH_SHORT).show();
 
     // default function to call the same class
     recreate();
    }
 
    return true;
   }
  });
 
 }

 public void display_data() {

  dbHelper = new DBHelper(this);
  database = dbHelper.getWritableDatabase();

  // Query to select all profiles
  String select_data = "SELECT * FROM " + DBHelper.IMAGE_TABLE;
 
  Cursor sCursor = database.rawQuery(select_data, null);
 
  if (sCursor.moveToFirst()) {
   do { 
image_id.add(sCursor.getString(sCursor.getColumnIndex(DBHelper.IMAGE_ID)));
     image_name.add(sCursor.getString(sCursor.getColumnIndex(DBHelper.IMAGE_NAME)));
     image.add(sCursor.getBlob(sCursor.getColumnIndex(DBHelper.IMAGE_)));

    Log.v("Response:", " " + image_name + " " + image);

   } while (sCursor.moveToNext());
  }
  sCursor.close();
  adapter = new ProfileListAdapter(MainActivity.this, image_id, image_name, image);
  listView.setAdapter(adapter);
 }
}

ProfileListAdapter.java
Open ProfileListAdapter.java and replace it with the following code.

package com.example.blob.adapter;
 
import android.app.Activity;
import android.content.Context;
import android.graphics.BitmapFactory;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.BaseAdapter;
import android.widget.ImageView;
import android.widget.TextView;
 
import com.example.blob.R;
 
import java.util.ArrayList;
 
public class ProfileListAdapter extends BaseAdapter {

 Context mContext;
 ArrayList image_id;
 ArrayList image_name;
 ArrayList image;
 LayoutInflater layoutInflater;
 ImageView profile;
 TextView name;
 byte[] bytes;

 public ProfileListAdapter(Context mContext, ArrayList image_id,        ArrayList image_name, ArrayList image) {
  this.mContext = mContext;
  this.image_id = image_id;
  this.image_name = image_name;
  this.image = image;
 }

 public int getCount() {
  return image_id.size();
 }

 public Object getItem(int position) {
  return position;
 }

 public long getItemId(int position) {
  return position;
 }

 @Override
 public View getView(int position, View convertView, ViewGroup parent) {

  layoutInflater = ((Activity) mContext).getLayoutInflater();
  convertView = layoutInflater.inflate(R.layout.list_item_profile, null);

  profile = (ImageView) convertView.findViewById(R.id.profile_image);
  name = (TextView) convertView.findViewById(R.id.profile_name);

  name.setText(image_name.get(position));
  bytes = image.get(position);

  // Decoding Bitmap from stored ByteArray
  profile.setImageBitmap(BitmapFactory.decodeByteArray(bytes, 0, bytes.length));

  return convertView;
 }
}

activity_dashboard.xml
Create activity_dashboard.xml and replace it with the following code.

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

 <ImageView
  android:id="@+id/imageView"
  android:layout_width="200dp"
  android:layout_height="200dp"
  android:layout_gravity="center_horizontal"
  android:contentDescription="@string/iv"
  android:src="@mipmap/ic_launcher" />

 <EditText
  android:id="@+id/editText"
  android:layout_width="match_parent"
  android:layout_height="wrap_content"
  android:layout_gravity="center_horizontal"
  android:layout_margin="10dp"
  android:hint="@string/name" />

 <Button
  android:id="@+id/button"
  android:layout_width="wrap_content"
  android:layout_height="wrap_content"
  android:layout_gravity="center_horizontal"
  android:text="Insert" />
</LinearLayout>

AddUpdateActivity.java
Open AddUpdateActivity.java and replace it with the following code.

package com.example.blob;
 
import android.content.Intent;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.graphics.Bitmap;
import android.graphics.BitmapFactory;
import android.net.Uri;
import android.os.Bundle;
import android.provider.MediaStore;
import android.support.v7.app.AppCompatActivity;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ImageView;
import android.widget.Toast;

import com.example.blob.helper.DBHelper;
import com.example.blob.helper.InsertHelper;
import com.example.blob.helper.UpdateHelper;
 
import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.IOException;
 
public class AddUpdateActivity extends AppCompatActivity {

 ImageView img1;
 EditText edt1;
 Button btn1;
 int SELECT_PICTURE = 1;
 SQLiteDatabase db;
 DBHelper mHelper;
 String selectedImagePath;
 byte[] byteImage = null;
 Intent intent;
 boolean isUpdate;
 String id, data;

 @Override
 protected void onCreate(Bundle savedInstanceState) {
  super.onCreate(savedInstanceState);
  setContentView(R.layout.activity_dashboard);

  // the helper class for DB creation operation
  mHelper = new DBHelper(this);
  img1 = (ImageView) findViewById(R.id.imageView);
  edt1 = (EditText) findViewById(R.id.editText);
  btn1 = (Button) findViewById(R.id.button);

  isUpdate = getIntent().getBooleanExtra("update", false);
  if (isUpdate) {
   id = getIntent().getStringExtra("id");
   byteImage = getIntent().getByteArrayExtra("image");
   data = getIntent().getStringExtra("name");

   // Decoding Bitmap from stored ByteArray from preview the stored image
   img1.setImageBitmap(BitmapFactory.decodeByteArray(byteImage, 0, byteImage.length));
   edt1.setText(data);
   btn1.setText("Update");
  }

  // Onclick event to select the image from gallery
  img1.setOnClickListener(new View.OnClickListener() {
   @Override
   public void onClick(View v) {
    Intent intent = new Intent(Intent.ACTION_PICK, android.provider.MediaStore.Images.Media.EXTERNAL_CONTENT_URI);
    intent.setType("image/*");
    startActivityForResult(Intent.createChooser(intent, "Select File"), SELECT_PICTURE);
   }
  });

  // Onclick event to do insert or update the data based on isUpdate
  btn1.setOnClickListener(new View.OnClickListener() {
   @Override
   public void onClick(View v) {
    data = edt1.getText().toString();
    if (data.equals("")) {
     Toast.makeText(getApplicationContext(), "Enter Name!", Toast.LENGTH_SHORT).show();
    } else {
     if (isUpdate) {
      updateData(data, id);
     } else {
      saveData(data);
     }
    }
   }
  });

 }

 public void onActivityResult(int requestCode, int resultCode, Intent data) {
  if (resultCode == RESULT_OK) {
   if (requestCode == SELECT_PICTURE) {
    Uri selectedImageUri = data.getData();
    String[] projection = {MediaStore.MediaColumns.DATA};
    Cursor cursor = getContentResolver().query(selectedImageUri, projection, null, null, null);
    int column_index = cursor.getColumnIndexOrThrow(MediaStore.MediaColumns.DATA);
    cursor.moveToFirst();
    selectedImagePath = cursor.getString(column_index);
    BitmapFactory.Options options = new BitmapFactory.Options();
    options.inJustDecodeBounds = true;
    BitmapFactory.decodeFile(selectedImagePath, options);
    int REQUIRED_SIZE = 200;
    int scale = 1;
    while (options.outWidth / scale / 2 >= REQUIRED_SIZE
      && options.outHeight / scale / 2 >=REQUIRED_SIZE)
     scale *= 2;
    options.inSampleSize = scale;
    options.inJustDecodeBounds = false;
    Bitmap bitmap = BitmapFactory.decodeFile(selectedImagePath, options);
    // Preview for Selected Image
    img1.setImageBitmap(bitmap);
   }
  }
 }

 // Function for insertion
 private void saveData(String data) {
  db = mHelper.getWritableDatabase();
  // the helper class for doing insert operation
  InsertHelper ins = new InsertHelper(this);
  ins.open();

  try {
   // Encoding the Selected Image into ByteArray
   if (selectedImagePath != null) {
    FileInputStream in_stream = new FileInputStream(selectedImagePath);
    BufferedInputStream bif = new BufferedInputStream(in_stream);
    byteImage = new byte[bif.available()];
    bif.read(byteImage);
   } else {
    Toast.makeText(getApplicationContext(), "Please Select Image!", Toast.LENGTH_SHORT).show();
   }
 
   if (byteImage != null) {
    // Function call to insert data
    long ret = ins.insert_profile(byteImage, data);
    if (ret > 0) {
     Toast.makeText(getApplicationContext(), "Error!", Toast.LENGTH_SHORT).show();
    } else {
     Toast.makeText(this.getBaseContext(), "Image Saved in DB successfully.", Toast.LENGTH_SHORT).show();
     intent = new Intent(AddUpdateActivity.this, MainActivity.class);
     startActivity(intent);
     finish();
    }
   } else {
    Toast.makeText(getApplicationContext(), "Select Image", Toast.LENGTH_SHORT).show();
   }
  } catch (IOException e) {
    Toast.makeText(getApplicationContext(), "Error Exception!", Toast.LENGTH_SHORT).show();
  }
  ins.close();
  db.close();
 }

 // Function for Updating the already stored value
 private void updateData(String data, String id) {
  db = mHelper.getWritableDatabase();
 
  UpdateHelper upd = new UpdateHelper(this);
  upd.open();
 
  try {
   // Encoding the Selected Image into ByteArray
   if (selectedImagePath != null) {
    FileInputStream in_stream = new FileInputStream(selectedImagePath);
    BufferedInputStream bif = new BufferedInputStream(in_stream);
    byteImage = new byte[bif.available()];
    bif.read(byteImage);
   }

   // Function call to update data
   long ret = upd.update_profile(id, byteImage, data);

   if (ret > 0) {
    Toast.makeText(getApplicationContext(), "Error!", Toast.LENGTH_SHORT).show();
   } else {
    Toast.makeText(this.getBaseContext(), "Image Saved in DB successfully.", Toast.LENGTH_SHORT).show();
    intent = new Intent(AddUpdateActivity.this, MainActivity.class);
    startActivity(intent);
    finish();
   }
  } catch (IOException e) {
   Toast.makeText(getApplicationContext(), "Error Exception!", Toast.LENGTH_SHORT).show();
  }
  upd.close();
  db.close();
 }
}

Download Full Source Code

Download Code