Insert and Retrieve Images in DB using Sqlite DB Browser

Hello all,

In the Previous Post ,I have shared the code to insert and retrieve the data from DB.Now in this post I am going to share the code to save and retrieve the employee's picture also.

for this in Sqlite "BLOB" datatype is used to save in images in the form of byte[]

So let's move to our tasks that is to create ,insert and retrieve the data from DB..so to do this we have to follow following steps:

-> create a Database in Sqlite browser named employee.db

->create a table in employee db named user  table. this table will have following fields:id,name,city,img
and all are TEXT type except img  it is if BLOB type.

->Now in Employee.db ,the user table having four columns : id,name,city,img has been created

->after all these above steps save this db file into you projects Assets folder

Description:

->I have made a layout having two buttons insert and retrieve and one listview.
On click on insert button an alert dialog will open which is used to fill the data from the user including image also.This alert button will also have the submit button ,which is used to insert the entered data in the DB

->there is also a listview which is used to retrieve the data from the DB in the form of listview .
In that listview ,if user click on the image of one listview item ,a dialog will appear,which will show the actual size of image which is saved in DB.


Explaination With Code: 


->first below is the layout having two buttons (insert,retrieve) and one listview  named
db_layout.xml:

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical"
    android:background="#C0C0C0" >

    <Button
        android:id="@+id/btn_retrieve"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Retrieve" />

    <Button
        android:id="@+id/btn_insert"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignParentTop="true"
        android:layout_marginLeft="36dp"
        android:layout_toRightOf="@+id/btn_retrieve"
        android:text="Insert" />

    <ListView
        android:id="@+id/list_dblayout"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_below="@+id/btn_retrieve"
        android:layout_marginTop="23dp"
      android:listSelector="@android:color/transparent" 
      android:divider="@android:color/transparent"
      android:cacheColorHint="@android:color/transparent">
    </ListView>

</RelativeLayout>


->Next is the list view item layout ,which is used to show the single item in list view in adapter,
named layout_list_itm_img.xml  :

<?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:orientation="vertical" >
    

    
    <RelativeLayout android:layout_width="match_parent"
        android:layout_height="100sp"
        android:background="#DD0000"
        android:layout_margin="5sp"
        >
        
         <ImageView
            android:id="@+id/img_pic_layout"
            android:layout_width="80sp"
            android:layout_height="80sp"
            android:scaleType="fitXY"
            android:layout_centerVertical="true"
            android:src="@android:drawable/star_big_on" />
   
    <LinearLayout android:layout_width="match_parent"
        android:layout_height="80sp"
        android:background="#FFFFFF"
        android:layout_margin="5sp"
        android:layout_centerVertical="true"
        android:layout_toRightOf="@+id/img_pic_layout"
        android:orientation="vertical">

        <TextView
            android:id="@+id/txt_name_listitemlayout"
            android:layout_width="match_parent"
            android:layout_marginLeft="5sp"
            android:layout_marginRight="5sp"
            android:layout_marginTop="5sp"
            android:layout_height="wrap_content"
            android:text="Name"
              android:textColor="#000000"
              android:textSize="13sp" />

       

        <TextView
            android:id="@+id/txt_city_listitemlayout"
           
            android:layout_height="wrap_content"
            android:text="City"
            android:textColor="#000000"
            android:textSize="13sp"
             android:layout_width="match_parent"
            android:layout_marginLeft="5sp"
            android:layout_marginRight="5sp"
         
            android:layout_marginTop="10sp" />

    </LinearLayout>
     </RelativeLayout>
</LinearLayout>

-> Now lets move to the DatabaseHandler class ,this class is used to connect with DB.I have named it DatabaseHandler.java  :


import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.List;

import android.annotation.SuppressLint;
import android.app.Activity;
import android.content.ContentValues;
import android.content.res.AssetManager;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DatabaseHandler extends SQLiteOpenHelper {

public static final String TAG = "DatabaseHandler";

private SQLiteDatabase db;

private Activity activity;
String mydate;
     private String DB_PATH = "/data/data/com.example.demo" + "/databases/";

private static String DB_NAME = "employee.db";


public DatabaseHandler(Activity activity) throws IOException {
super(activity, DB_NAME, null, 1);
this.activity = activity;
boolean dbexist = checkdatabase();
if (dbexist) {
Log.d("Trong", "Database exists");
opendatabase();
} else {
System.out.println("Database doesn't exist");
createdatabase();
}
}

public void createdatabase() throws IOException {
boolean dbexist = checkdatabase();
if (dbexist) {
// System.out.println(" Database exists.");
} else {
this.getReadableDatabase();
try {
copydatabase();
} catch (IOException e) {
e.printStackTrace();
}
}
}

private boolean checkdatabase() {
// SQLiteDatabase checkdb = null;
boolean checkdb = false;
try {
String myPath = DB_PATH + DB_NAME;
Log.d("Trong", "DB_PATH + DB_NAME " + DB_PATH + DB_NAME);
File dbfile = new File(myPath);
// checkdb =
// SQLiteDatabase.openDatabase(myPath,null,SQLiteDatabase.OPEN_READWRITE);
checkdb = dbfile.exists();
} catch (SQLiteException e) {
Log.d("Trong", "Database doesn't exist");
}
return checkdb;
}


private void copydatabase() throws IOException {
        AssetManager am = activity.getAssets();
        OutputStream os = new FileOutputStream(DB_PATH + DB_NAME);
        byte[] b = new byte[1024];
        String[] files = am.list("");
        Arrays.sort(files);
        int r;
      
            InputStream is = am.open("employee.db");
            while ((r = is.read(b)) != -1) {
                os.write(b, 0, r);
            }
            Log.i("BABY_DATABASE_HELPER", "Copying the database (part " 
                    + " of 9)");
            is.close();
        
        os.close();
    }

public void opendatabase() throws SQLException {
// Open the database
String mypath = DB_PATH + DB_NAME;
db = SQLiteDatabase.openDatabase(mypath, null,
SQLiteDatabase.OPEN_READWRITE);

}

public synchronized void close() {
if (db != null) {
db.close();
}
super.close();
}

  @Override
public void onCreate(SQLiteDatabase db) {

}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

}

public void deleteAll()
{
   SQLiteDatabase db = this.getWritableDatabase();
   db.delete("user",null,null);
   db.close();
}

public int insertImgvalue(String id, String str_name, String str_city,
byte[] imagearray) {
ContentValues cvalue=new ContentValues();
cvalue.put("id",id);
cvalue.put("name",str_name);
cvalue.put("city",str_city);
cvalue.put("img",imagearray);
 db.insert("user", null, cvalue);
return 1;
}

public ArrayList<DbEntity> getImgInputMonths() {
ArrayList<DbEntity> ittoentity = new ArrayList<DbEntity>();
 
 String selectQuery = "SELECT  * FROM "+"user";
 Log.d("rawquery", "inputs"+selectQuery);
 Cursor cursor = db.rawQuery(selectQuery, null);
                          if (cursor.moveToFirst()) {
  do {
  DbEntity itto = new DbEntity();
  itto.setImgAry(cursor.getBlob(0));//here img is fetching
  itto.set_name(cursor.getString(2));
   itto.set_city(cursor.getString(3));
   ittoentity.add(itto);
  } while (cursor.moveToNext());
 }

 return ittoentity;
}
}

->Now there are two custom dialog is used ,one is to insert the data from the user ,I have named it :
insert_img_dialog_layout.xml

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="300sp"
    android:layout_height="320sp"
    android:orientation="vertical" 
    android:background="#ff9900">

    <ImageView
        android:id="@+id/img_gallery_insertimgdialog"
        android:layout_width="100sp"
        android:layout_height="100sp"
        android:layout_gravity="center_horizontal"
        android:layout_marginTop="10sp"
        android:src="@android:drawable/toast_frame" />

    <EditText
        android:id="@+id/edttxt_name_insertimgdialog"
        android:layout_width="match_parent"
        android:layout_height="50sp"
       android:layout_marginLeft="8sp"
       android:layout_marginRight="8sp"
       android:hint="Name"
       android:singleLine="true"
       android:layout_marginTop="15sp"
       android:textColor="#000000"
       >

    </EditText>
    
        <EditText
        android:id="@+id/edttxt_city_insertimgdialog"
        android:layout_width="match_parent"
        android:layout_height="50sp"
       android:layout_marginLeft="8sp"
       android:layout_marginRight="8sp"
       android:hint="City"
       android:singleLine="true"
       android:layout_marginTop="15sp"
       android:textColor="#000000"
       >

    </EditText>

        <Button
            android:id="@+id/btn_insert_insertimgdialog"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_gravity="center_horizontal"
               android:layout_marginTop="15sp"
            android:text="INSERT" />


</LinearLayout>

->And another custom dialog is used to show the size of actual image which is saved in DB .I have named it : custom_alert_dialog.xml

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="300dp"
    android:layout_height="300dp"
    android:orientation="vertical"
    android:background="#FF0022" >

    <ImageView
        android:id="@+id/img_profile_alertlayout"
        android:layout_width="match_parent"
        android:layout_height="match_parent"
       android:scaleType="fitXY"
        android:layout_margin="5dp"
        android:src="@drawable/ic_launcher" />
   

</RelativeLayout>

->An Entity is used ,named DBEntity.java

import java.sql.Blob;

public class DbEntity {
private String _name,_city;
byte[] imgAry;
        public DbEntity() {
super();
}
        public DbEntity(String _name,String _city) {
super();
this._name = _name;
this._city = _city;
}
       public String get_name() {
return _name;
}
      public void set_name(String _name) {
this._name = _name;
}
      public String get_city() {
return _city;
}
      public void set_city(String _city) {
this._city = _city;
}
      public byte[] getImgAry() {
return imgAry;
}
       public void setImgAry(byte[] imgAry) {
this.imgAry = imgAry;
}
}

-> Now Lets move to the main Activity I have named it : DbFetchImgActivity.java

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.ArrayList;

import com.demo.db.DatabaseHandler;
import com.demo.db.DbEntity;

import android.app.Activity;
import android.app.Dialog;
import android.content.Intent;
import android.database.Cursor;
import android.database.SQLException;
import android.graphics.Bitmap;
import android.graphics.BitmapFactory;
import android.graphics.Matrix;
import android.net.Uri;
import android.os.Bundle;
import android.provider.MediaStore;
import android.util.Log;
import android.view.View;
import android.view.Window;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ImageView;
import android.widget.ListView;
import android.widget.Toast;

public class DbFetchImgActivity extends  Activity{
Button btn_retreive,btn_insert;
ImageView img_choosefrmgallery;
DatabaseHandler db;
EditText edttxt_name, edttxt_city ;
Dialog dialog;
     Button btn_submit;
     ListView list ;
     private  Bitmap bitma,btmset ;
     byte[] imagearray =null;
@Override
protected void onCreate(Bundle savedInstanceState) {
// TODO Auto-generated method stub
super.onCreate(savedInstanceState);
setContentView(R.layout.db_layout);

btn_retreive = (Button)findViewById(R.id.btn_retrieve);
btn_insert= (Button)findViewById(R.id.btn_insert);
list = (ListView)findViewById(R.id.list_dblayout);

btn_retreive.setOnClickListener(new View.OnClickListener() {

@Override
public void onClick(View v) {
RetriveDataFromDB();
}
});
btn_insert.setOnClickListener(new View.OnClickListener() {

@Override
public void onClick(View v) {
dialog = new Dialog(DbFetchImgActivity.this);
           dialog.getWindow();
         dialog.requestWindowFeature(Window.FEATURE_NO_TITLE);
           dialog.setContentView(R.layout.insert_img_dialog_layout);
          dialog.setCancelable(true);
           dialog.show();
           
     edttxt_name = (EditText)dialog.findViewById(R.id.edttxt_name_insertimgdialog);
    edttxt_city = (EditText)dialog.findViewById(R.id.edttxt_city_insertimgdialog);
    img_choosefrmgallery= (ImageView)dialog.findViewById(R.id.img_gallery_insertimgdialog);
           
    img_choosefrmgallery.setOnClickListener(new View.OnClickListener() {

@Override
public void onClick(View v) {
Intent i = new Intent(
                 Intent.ACTION_PICK,
                 android.provider.MediaStore.Images.Media.EXTERNAL_CONTENT_URI);
      startActivityForResult(i,0);
}
});
           Button btn_submit = (Button)dialog.findViewById(R.id.btn_insert_insertimgdialog);
           
           btn_submit.setOnClickListener(new View.OnClickListener() {

@Override
public void onClick(View v) {
String str_name = "",str_city="";

str_city = edttxt_city.getText().toString();
str_name = edttxt_name.getText().toString();

if (str_city.equals("")||(str_name.equals(""))
||imagearray.length==0) {
Toast.makeText(DbFetchImgActivity.this,"Please fill all the fields!!",Toast.LENGTH_LONG).show();
}
else
{
insertImgIntoDb("11",str_name,str_city,imagearray);
}
}

private void insertImgIntoDb(String id,
String str_name, String str_city, byte[] imagearray) {
try {
db = new DatabaseHandler(DbFetchImgActivity.this);
} catch (IOException e) {
e.printStackTrace();
}
db.opendatabase();

int i = db.insertImgvalue(id, str_name, str_city,imagearray);

if (i==1) {
Toast.makeText(DbFetchImgActivity.this,"Infomation saved succesfully.",Toast.LENGTH_LONG).show();
dialog.dismiss();

}
else
{
Toast.makeText(DbFetchImgActivity.this,"Something went wrong.Please try again later.",Toast.LENGTH_LONG).show();
dialog.dismiss();
}

try {
db.close();
} catch (Exception e) {
e.printStackTrace();
}

}
});
           
}
});
}
protected void RetriveDataFromDB() {
try {
db = new DatabaseHandler(DbFetchImgActivity.this);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
   db.createdatabase();

  } catch (IOException ioe) {
   throw new Error("Unable to create database");
  }
  try {
   db.opendatabase();
  } catch (SQLException sqle) {
   throw sqle;
  }
  
  ArrayList<DbEntity>  menuItems = new  ArrayList<DbEntity>();
  menuItems.clear();
 
ArrayList<DbEntity> contacts = db.getImgInputMonths();  
 
 
Log.e("Array",">>"+menuItems.size());
list.setAdapter(null);
list.setAdapter(new DbRetrieveImgAdapter(DbFetchImgActivity.this,contacts));
}

@Override
public void onActivityResult(int requestCode, int resultCode, Intent data) {
// TODO Auto-generated method stub
super.onActivityResult(requestCode, resultCode, data);
Log.e("BaseContaineronActivityResult","akjlaks");
 switch(requestCode){
   case 0: 

if(resultCode == RESULT_OK && null != data)
    {
     Uri selectedImage = data.getData();
         String[] filePathColumn = { MediaStore.Images.Media.DATA };

         Cursor cursor = getContentResolver().query(selectedImage,
                 filePathColumn, null, null, null);
         cursor.moveToFirst();

         int columnIndex = cursor.getColumnIndex(filePathColumn[0]);
         String picturePath = cursor.getString(columnIndex);
         cursor.close();
         Log.d("BaseContaineronActivityResult","PICTURE PATH"+picturePath);
          bitma = BitmapFactory.decodeFile((picturePath));
       
          Log.d("BaseContaineronActivityResult","Bitmap  height:"+bitma.getHeight()+"   width:"+bitma.getWidth());
         btmset = getResizedBitmap(bitma, 250, 250);
      
         ByteArrayOutputStream bao = new ByteArrayOutputStream();
         bitma.compress(Bitmap.CompressFormat.JPEG,100, bao);
imagearray = bao.toByteArray();

img_choosefrmgallery.setImageBitmap(btmset);
         
      }
   break;

 }  
 
}

private Bitmap getResizedBitmap(Bitmap bm, int newHeight, int newWidth) {
// TODO Auto-generated method stub
int width = bm.getWidth();
int height = bm.getHeight();
float scaleWidth = ((float) newWidth) / width;
float scaleHeight = ((float) newHeight) / height;
Matrix matrix = new Matrix();
// RESIZE THE BIT MAP
matrix.postScale(scaleWidth, scaleHeight);
// RECREATE THE NEW BITMAP
Bitmap resizedBitmap = Bitmap.createBitmap(bm, 0, 0, width, height,
 matrix, false);
return resizedBitmap;
}

}

->The adapter which is used to show the list view items ,I have named it : DbRetrieveImgAdapter.java 

import java.util.ArrayList;

import android.app.Activity;
import android.app.Dialog;
import android.content.Context;
import android.graphics.Bitmap;
import android.graphics.BitmapFactory;
import android.util.DisplayMetrics;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.view.Window;
import android.widget.BaseAdapter;
import android.widget.ImageView;
import android.widget.TextView;

import com.demo.db.DbEntity;

public class DbRetrieveImgAdapter extends BaseAdapter {

    private LayoutInflater mLayoutInflater = null;
private Activity activity;
private ViewHolder holder;
private String name,cat_id,str_flag="";

ArrayList<DbEntity>  Show;
 int i = 0 ;


private class ViewHolder {
public TextView tvName,tvCity;
public ImageView img_profile;

}


public DbRetrieveImgAdapter(Activity activity2,
ArrayList<DbEntity> contacts) {
this.activity = activity2;
this.Show = contacts;
mLayoutInflater = (LayoutInflater)activity.getSystemService(Context.LAYOUT_INFLATER_SERVICE);
 
}

@Override
public int getCount() {
return Show.size();
}

@Override
public Object getItem(int position) {
return Show.get(position);
}

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

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

if (convertView == null) {
convertView = mLayoutInflater.inflate(R.layout.layout_list_itm_img, null);

holder = new ViewHolder();
holder.tvName = (TextView) convertView.findViewById(R.id.txt_name_listitemlayout);
holder.tvCity= (TextView) convertView.findViewById(R.id.txt_city_listitemlayout);
holder.img_profile = (ImageView) convertView.findViewById(R.id.img_pic_layout);

convertView.setTag(holder);
} else {
holder = (ViewHolder) convertView.getTag();
}

holder.tvName.setText("Name : "+Show.get(position).get_name()); 
holder.tvCity.setText("City : "+Show.get(position).get_city());

 Bitmap bm = BitmapFactory.decodeByteArray(Show.get(position).getImgAry(), 0, Show.get(position).getImgAry().length);
       DisplayMetrics dm = new DisplayMetrics();
       activity.getWindowManager().getDefaultDisplay().getMetrics(dm);
       holder.img_profile.setImageBitmap(bm);
 holder.img_profile.setOnClickListener(new View.OnClickListener() {

@Override
public void onClick(View v) {
Dialog dialog = new Dialog(activity);
           dialog.getWindow();
         dialog.requestWindowFeature(Window.FEATURE_NO_TITLE);
           dialog.setContentView(R.layout.custom_alert_dialog);
          dialog.setCancelable(true);
           dialog.show();
           ImageView img = (ImageView)dialog.findViewById(R.id.img_profile_alertlayout);
           Bitmap bm = BitmapFactory.decodeByteArray(Show.get(position).getImgAry(), 0,Show.get(position).getImgAry().length);
       DisplayMetrics dm = new DisplayMetrics();
       activity.getWindowManager().getDefaultDisplay().getMetrics(dm);

       img.setImageBitmap(bm);
}
});
return convertView;
}


}

->Also add two user premission in the menifest : 

 <uses-permission android:name="android.permission.WRITE_EXTERNAL_STORAGE"/>

       <uses-permission android:name="android.permission.READ_EXTERNAL_STORAGE"/>

->That's all ,The above all the code for the insert and retrieve the images using SQLite DB.
And below is the screenshots for the above task: