Store Android SQLite

How to store image in SQLite database

You have to use "blob" to store image.

ex: to store a image in to db:

public void insertImg(int id , Bitmap img ) {   

byte[] data = getBitmapAsByteArray(img); // this is a function

insertStatement_logo.bindLong(1, id);
insertStatement_logo.bindBlob(2, data);

insertStatement_logo.clearBindings() ;


public static byte[] getBitmapAsByteArray(Bitmap bitmap) {
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
bitmap.compress(CompressFormat.PNG, 0, outputStream);
return outputStream.toByteArray();

To retrieve a image from db:

public Bitmap getImage(int i){

String qu = "select img from table where feedid=" + i ;
Cursor cur = db.rawQuery(qu, null);

if (cur.moveToFirst()){
byte[] imgByte = cur.getBlob(0);
return BitmapFactory.decodeByteArray(imgByte, 0, imgByte.length);
if (cur != null && !cur.isClosed()) {

return null;

I want to store the list of item displayed in listview to android Sqlite Database

Try Below code -

First you have to create DatabaseHandler Class

package com.androidhive.androidsqlite;

import java.util.ArrayList;
import java.util.List;

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

public class DatabaseHandler extends SQLiteOpenHelper {

// All Static variables
// Database Version
private static final int DATABASE_VERSION = 1;

// Database Name
private static final String DATABASE_NAME = "MyDatabase";

// Database table name
private static final String TABLE_LIST = "MyListItem";

// Table Columns names
private static final String KEY_ID = "id";
private static final String KEY_ListItem = "listitem";

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

// Creating Tables
public void onCreate(SQLiteDatabase db) {
+ " INTEGER," + KEY_ListItem + " TEXT" + ")";


// Upgrading database
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// Drop older table if existed

// Create tables again

void addListItem(ArrayList<String> listItem) {
SQLiteDatabase db = this.getWritableDatabase();

ContentValues values = new ContentValues();
for (int i = 0; i < listItem.size(); i++) {

Log.e("vlaue inserting==", "" + listItem.get(i));
values.put(KEY_ListItem, listItem.get(i));
db.insert(TABLE_LIST, null, values);


db.close(); // Closing database connection

Cursor getListItem() {
String selectQuery = "SELECT * FROM " + TABLE_LIST;

SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(selectQuery, null);

return cursor;


package com.androidhive.androidsqlite;

import java.util.ArrayList;
import java.util.List;

import android.database.Cursor;
import android.os.Bundle;
import android.util.Log;

public class MainActivity extends Activity {
/** Called when the activity is first created. */

ArrayList<String> your_list_arrayArrayList;

public void onCreate(Bundle savedInstanceState) {

your_list_array ArrayList = new ArrayList<String>();


DatabaseHandler db = new DatabaseHandler(this);


Cursor cursor = db.getListItem();

Log.e("count", " " + cursor.getCount());
if (cursor != null) {

do {

Log.e("value==", "" + cursor.getString(1));

} while (cursor.moveToNext());

If its not working please let me know I will try to help you more.

Android - How to properly store Date and Time in SQLite?

According to Sqlite documentation (section 2.2), you should store your date in one of the following ways:

  • TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
  • REAL as Julian day numbers.
  • INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

The comparison problem can be solved using the strftime function that allows you to compere dates regardeless their storage type (TEXT, REAL or INTEGER).

Let's say that you want to get all the rows in your database that have date Jenuary. You can do:

WHERE strftime('%m', your_date_column) == '01'

Or maybe you want get all the rows with time 09:40:

WHERE strftime('%H:%M', your_date_column) == '09:40'

Or also:

WHERE strftime('%s', your_date_column) < strftime('%s','now')

will return you all the rows with date preceding the current date (now).

SUMMARY: The function strftime abstracts the database storing mode, so it doesn't actually metter which mode you use.

What is a good way to store order in an SQLite database

The problem with adding a sort_position value to each row is that it can't be updated atomically, because to swap two items in the list you need to change both of their positions. The database is much better at guaranteeing atomicity when the changes are to the same row.

A better way would be to treat the ordering column as a priority - the values can be sparse so a rows can be placed between two adjacent ones, and two items can have the same value without it being a consistency problem. If the values are sparse, you can swap two items by just changing one value, so concurrent updates are more gracefully handled (although you might still get unexpected situations if two agents are trying to rearrange the same part of the list).

An alternative way of handling it might be to have the ordering be stored as one value in a different table - for example as a list of item ids. This has the advantage of allowing more precise control of the ordering while not ever getting garbled state from interleaved updates, but is more complicated (handling missing or unknown items, how to handle both updating at the same time). I wouldn't try this unless you really need it.

Where is my sqlite database stored in android?

In my rooted phone, it's in : /data/data/<your_app_package_name>/databases/<database_name>


How to store and fetch large text data from SQLite database?

You have 2 choices:

  • TEXT type - SQLite supports very long text.


SQLite will be happy to store a 500-million character string there

  • BLOB type: If you don't want to use TEXT, you can use BLOB instead ( You need to convert the large text to a byte array when inserting/updating, and convert the byte array to back to the large text back later)

For database design. You can do like this:

- Book table ( _id, name, ...)

- Chapter table (_id, content (TEXT,BLOB), book_id, chapter_number, ...);

Related Topics

Leave a reply