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.executeInsert();
    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);
        cur.close();
        return BitmapFactory.decodeByteArray(imgByte, 0, imgByte.length);
    }
    if (cur != null && !cur.isClosed()) {
        cur.close();
    }       

    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

DatabaseHandler.java-

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
    @Override
    public void onCreate(SQLiteDatabase db) {
        String CREATE_LIST_TABLE = "CREATE TABLE " + TABLE_LIST + "(" + KEY_ID
                + " INTEGER," + KEY_ListItem + " TEXT" + ")";

        db.execSQL(CREATE_LIST_TABLE);
    }

    // Upgrading database
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Drop older table if existed
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_LIST);

        // Create tables again
        onCreate(db);
    }

    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;
    }

}

MainActivity.java

package com.androidhive.androidsqlite;

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

import android.app.Activity;
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;

    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);

        your_list_array ArrayList = new ArrayList<String>();

        your_list_arrayArrayList.add("Item1");
        your_list_arrayArrayList.add("Item2");
        your_list_arrayArrayList.add("Item3");
        your_list_arrayArrayList.add("Item4");
        your_list_arrayArrayList.add("Item5");

        DatabaseHandler db = new DatabaseHandler(this);

        db.addListItem(your_list_arrayArrayList);

        Cursor cursor = db.getListItem();

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

            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:

SELECT * FROM table
WHERE strftime('%m', your_date_column) == '01'

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

SELECT * FROM table
WHERE strftime('%H:%M', your_date_column) == '09:40'

Or also:

SELECT * FROM table
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>

exemple
/data/data/com.my.package/databases/mydb

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

You have 2 choices:

• TEXT type - SQLite supports very long text.

Source: http://www.sqlite.org/faq.html#q9

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