How to populate SQLite db with images, and retrieve images from it?

Question

I have 100 images in my Assets folder. I would like to set an image to the imageview, and below four buttons with 4 possible answers to connect with the image (one correct, of course). What I was thinking, I have a similar quiz game already done, I'm using SQLite database for question and answers. So, is there a way I could use sqlite database for this, but only where my question is in database I would place some term or an integer, for example 1 for image_1.png, 2 for image_2.png and so on.

Or another scenario is to prepopulate database with images, but I don't know if that's even possible or how to do it.

Is there a way to rewrite my db helper to use it with this? Here's my db helper and testAdapter:

public class DataBaseHelper extends SQLiteOpenHelper
{
   private static String TAG = "DataBaseHelper"; // Tag just for the LogCat window
    //destination path (location) of our database on device
    private static String DB_PATH = "/data/data/rs.androidaplikacijekvizopstekulture/databases/"; 
    private static String DB_NAME ="pitanja.sqlite";// Database name
    private static SQLiteDatabase mDataBase; 
    private final Context mContext;
    private static final String KEY_ID = "_ID";
    private static final String TABLE_NAME = "tblPitanja";

    public DataBaseHelper(Context mojContext) 
    {
        super(mojContext, DB_NAME, null, 1);// 1? its Database Version
        DB_PATH = mojContext.getApplicationInfo().dataDir + "/databases/";
        this.mContext = mojContext;
    }

    public void createDataBase() throws IOException
    {
    //If database not exists copy it from the assets


        this.getReadableDatabase();
        this.close();
        try 
        {
            //Copy the database from assests
            copyDataBase();
            Log.e(TAG, "createDatabase database created");
        } 
        catch (IOException mIOException) 
        {
            throw new Error("ErrorCopyingDataBase");
        }
    }
    /*Check that the database exists here: /data/data/your package/databases/Da Name
    private boolean checkDataBase()
    {
        File dbFile = new File(DB_PATH + DB_NAME);
        //Log.v("dbFile", dbFile + "   "+ dbFile.exists());
        return dbFile.exists();
    }
    */

    //Copy the database from assets
    private void copyDataBase() throws IOException
    {
        InputStream mInput = mContext.getAssets().open(DB_NAME);
        String outFileName = DB_PATH + DB_NAME;
        OutputStream mOutput = new FileOutputStream(outFileName);
        byte[] mBuffer = new byte[1024];
        int mLength;
        while ((mLength = mInput.read(mBuffer))>0)
        {
            mOutput.write(mBuffer, 0, mLength);
        }
        mOutput.flush();
        mOutput.close();
        mInput.close();
    }

    //Open the database, so we can query it
    public boolean openDataBase() throws SQLException
    {
        String mPath = DB_PATH + DB_NAME;
        //Log.v("mPath", mPath);
        mDataBase = SQLiteDatabase.openDatabase(mPath, null, SQLiteDatabase.CREATE_IF_NECESSARY);
        //mDataBase = SQLiteDatabase.openDatabase(mPath, null, SQLiteDatabase.NO_LOCALIZED_COLLATORS);
        return mDataBase != null;
    }
    @Override
    public void close() 
    {
        if(mDataBase != null)
            mDataBase.close();
        super.close();
    }

    @Override
    public void onCreate(SQLiteDatabase arg0) {
        }

    @Override
    public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
        Log.w("DataBaseHelper", "Upgrading database!!!!!");
          onCreate(arg0);

    }

}

And here's testAdaper:

public class TestAdapter 
{
    protected static final String TAG = "DataAdapter";

    private final Context mContext;
    private SQLiteDatabase mDb;
    private DataBaseHelper mDbHelper;

    public TestAdapter(Context context) 
    {
        this.mContext = context;
        mDbHelper = new DataBaseHelper(mContext);
    }

    public TestAdapter createDatabase() throws SQLException 
    {
        try 
        {
            mDbHelper.createDataBase();
        } 
        catch (IOException mIOException) 
        {
            Log.e(TAG, mIOException.toString() + "  UnableToCreateDatabase");
            throw new Error("UnableToCreateDatabase");
        }
        return this;
    }

    public TestAdapter open() throws SQLException 
    {
        try 
        {
            mDbHelper.openDataBase();
            mDbHelper.close();
            mDb = mDbHelper.getReadableDatabase();
        } 
        catch (SQLException mSQLException) 
        {
            Log.e(TAG, "open >>"+ mSQLException.toString());
            throw mSQLException;
        }
        return this;
    }

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

     public Cursor getTestData(String whereClause)
     {;
         try
         {
             String sql ="SELECT * FROM tblPitanja WHERE 1 = 1 " + whereClause + " ORDER BY RANDOM() LIMIT 1";

             Cursor mCur = mDb.rawQuery(sql, null);
             if (mCur!=null)
             {
                mCur.moveToNext();
             }
             return mCur;
         }
         catch (SQLException mSQLException) 
         {
             Log.e(TAG, "getTestData >>"+ mSQLException.toString());
             throw mSQLException;
         }
     }
}

Why put them in the database? They're already in assets and perfectly accessible to your app. — 323go, Mar 30 '13 at 02:21
@323go I agree. But some cases like cross-platform apps using the same database may be a more simple solution. — wtsang02, Mar 30 '13 at 02:29
@wtsang02, iOS let's you bundle files too, and I'd expect that Windows has the same concept. Putting static blobs in a database is generally a bad idea. — 323go, Mar 30 '13 at 02:35
@323go I agree, as I said, its a simple solution and has downsides but it does save time in some cases. — wtsang02, Mar 30 '13 at 02:38

score 1 · Answer 1 · edited May 23 '17 at 12:14

1

You can save the images in sqlite by using blob datatype. Use SQLite manager (firefox plugin) to pre-populate your database on a computer. Then you just need the db file in assets folder, and not those 100 images!

Check this: how to store Image as blob in Sqlite & how to retrieve it?

Another way (faster) is to store images in an external folder and then enter its URL in the database.

edited May 23 '17 at 12:14

Community

1
1

answered Mar 30 '13 at 02:39

Kartik

7,677
4
28
50

Is this better solution then images in assets folder without pupulating the database? – nabuko Mar 30 '13 at 11:33

Parvaz Bhaskar · Answer 2 · 2013-03-30T12:04:45.673

Since you have images in assets already I would suggest you to save only the names of the associated image in the sqlite. I prefer always to avoid saving blob in sqlite. You can always get the correct name and load it into the imageview directly from the database.

EDIT : your code can be as simple as this.

Cursor getname = db.rawQuery("Select * from table_name where QuestionID = ...");
if(getname.movetofirst())
{
String drawablename = getname.getString(getname.getColumnIndex("PicName"));

ImageView iw= (ImageView)findViewById(R.id.imageView1);  
int resID = getResources().getIdentifier(drawableName, "drawable",  getPackageName());
iw.setImageResource(resID);
}

OK, how to do that? Can you give an example, what to correct in my code to make that happend? — nabuko, Mar 30 '13 at 11:33

How to populate SQLite db with images, and retrieve images from it?

2 Answers2