Saving an image in a SQLite database in your Android application

Cindy PotvinMay 29th, 2015Last Updated: May 28th, 2015

2 394 4 minutes read

When you start caching data to a local database from your Android application, sooner or later you’ll need to save images in that database too. For example, if you’re storing reports about observations the users make in the field that will be uploaded to the main system later, it can be handy to add a picture to better describe the problem.

The first idea that comes to mind to store those images is saving them directly in the database as BLOBs (Binary Large OBjects). It can work, but it’s not the most efficient way to do it since SQLite is meant to store values and not large binary values. Also, the local database could get big pretty fast, especially if you need to save full size, high quality pictures. You can check out a benchmark on the official SQLite site at https://www.sqlite.org/intern-v-extern-blob.html if you want to have a better idea of the performance.

What you really want to do in that case is keep only the path to the image in your database and save the image in the internal storage of your application. When you need to use the image, you can simply use the path to fetch the image from the file system. I recommend you save the pictures to the internal storage since so it’s accessible only from your application and is available at all times; actually, the SQLite database itself is also stored in the internal storage.

Here is an example of how to add an image to an existing report, saving it in the internal storage and keeping the path in the database. To learn more about how to create a database and store data to it, you can read my article about saving to a SQLite database.

public class ApplicationDatabaseHelper extends SQLiteOpenHelper {
/**
  * Updates the current picture for the report.
  *
  * @param reportId the identifier of the report for which to save the picture
  * @param picture the picture to save to the internal storage and save path in the database.
  */
public void updateReportPicture(long reportId, Bitmap picture) {
   // Saves the new picture to the internal storage with the unique identifier of the report as 
   // the name. That way, there will never be two report pictures with the same name.
   String picturePath = "";
   File internalStorage = mContext.getDir("ReportPictures", Context.MODE_PRIVATE);
   File reportFilePath = new File(internalStorage, reportId + ".png");
   String picturePath = reportFilePath.toString();

   FileOutputStream fos = null;
   try {
      fos = new FileOutputStream(reportFilePath);
      picture.compress(Bitmap.CompressFormat.PNG, 100 /*quality*/, fos);
      fos.close();
      }
   catch (Exception ex) {
      Log.i("DATABASE", "Problem updating picture", ex);
      picturePath = "";
      }

   // Updates the database entry for the report to point to the picture
   SQLiteDatabase db = getWritableDatabase();

   ContentValues newPictureValue = new ContentValues();
   newPictureValue.put(ReportContract.ReportEntry.COLUMN_PICTURE_TITLE, 
                       picturePath);

   db.update(ReportContract.TABLE_NAME,
             newPictureValue,
             ReportContract.ReportEntry._ID + "=?",
             new String[]{String.valueOf(reportId)});
   }
}

If you save a picture to the database, you also need a way to get that picture to display it in your application. Here is how to get the image that was just saved to display it again :

public class ApplicationDatabaseHelper extends SQLiteOpenHelper {
/**
  * Gets the picture for the specified report in the database.
  *
  * @param reportId the identifier of the report for which to get the picture.
  *
  * @return the picture for the report, or null if no picture was found.
  */
public Bitmap getReportPicture(long reportId) {
   String picturePath = getReportPicturePath(reportId);
   if (picturePath == null || picturePath.length() == 0)
      return (null);

   Bitmap reportPicture = BitmapFactory.decodeFile(picturePath);

   return (reportPicture);
   }

/**
  * Gets the path of the picture for the specified report in the database.
  *
  * @param reportId the identifier of the report for which to get the picture.
  *   
  * @return the picture for the report, or null if no picture was found.
  */
private String getReportPicturePath(long reportId) {
   // Gets the database in the current database helper in read-only mode
   SQLiteDatabase db = getReadableDatabase();

   // After the query, the cursor points to the first database row
   // returned by the request
   Cursor reportCursor = db.query(ReportContract.TABLE_NAME,
                                  null,
                                  ReportContract.ReportEntry._ID + "=?",
                                  new String[]{String.valueOf(reportId)},
                                  null,
                                  null,
                                  null);
   reportCursor.moveToNext();
    
   // Get the path of the picture from the database row pointed by
   // the cursor using the getColumnIndex method of the cursor.
   String picturePath = reportCursor.getString(reportCursor.
                             getColumnIndex(ReportContract.ReportEntry.COLUMN_PICTURE_TITLE));

   return (picturePath);
   }
}

Finally, you need to make sure to delete the picture from the internal storage if you delete the associated record from the database. If you keep the pictures, the internal storage used by your application will grow larger with time and your users won’t have any way to delete those pictures since the internal storage is hidden from them.

public class ApplicationDatabaseHelper extends SQLiteOpenHelper {
/**
  * Deletes the specified report from the database, removing also the associated picture from the
  * internal storage if any.
  *
  * @param reportId the report to remove.
  */
public void deleteReport(long reportId) {
   // Remove picture for report from internal storage
   String picturePath = getReportPicturePath(reportId); // See above
   if (picturePath != null && picturePath.length() != 0) {
      File reportFilePath = new File(picturePath);
      reportFilePath.delete();
   }

   // Remove the report from the database
   SQLiteDatabase db = getWritableDatabase();

   db.delete(ReportContract.TABLE_NAME,
             ReportContract.ReportEntry._ID + "=?",
             new String[]{String.valueOf(reportId)});
   }
}

Reference:

Saving an image in a SQLite database in your Android application from our JCG partner Cindy Potvin at the Web, Mobile and Android Programming blog.