How to store video content in SQLite database (not

Question

I want to store a video in sqlite database. P.S. I do not want to store the path but the actual video contents. I have converted the video in byte array and stored byte array in sqlite database. Upon retrieval bytearray is being converted into File. But video is not playing. Please help.

Answer 1

You can use this approach

When save the video, save it in app private storage folder.

Context.getFilesDir()

This will give you the path to the app storage in ..\Andorid\data\data\com.example.app and it will be in internal storage.

Where com.example.app will be your application package id. You can make a new folder here like Videos then save videos in this folder. Save its path in the DB. Only your app can access to this folder. No any other app or device user can access this folder. So no one can edit or delete your files except your application.

Moreover if user reset mobile this data will be deleted as well as your database and maybe your app too in some cases. So no need to worry about it that your files will be deleted but database has still their path. If file deleted then DB deleted too as well but only when app Uninstall, device reset or SD card erase.

Answer 2

I want to store a video in sqlite database. P.S. I do not want to store the path but the actual video contents.

Unless the videos are very short and take up little space (say up to 200k each, perhaps 1/10th of a second but would depend upon the format it is saved in) then you would likely encounter issues and exceptions/crashes.

• Using a phone around 2 seconds of black took up 2.2Mb, 2 seconds of actually recording a video took up 7Mb.

Although SQLite has the ability to store relative large BLOB's as per :-

• Maximum length of a string or BLOB

  The maximum number of bytes in a string or BLOB in SQLite is defined by the preprocessor macro SQLITE_MAX_LENGTH. The default value of this macro is 1 billion (1 thousand million or 1,000,000,000). You can raise or lower this value at compile-time using a command-line option like this:

  -DSQLITE_MAX_LENGTH=123456789 The current implementation will only support a string or BLOB length up to 231-1 or 2147483647. And some built-in functions such as hex() might fail well before that point. In security-sensitive applications it is best not to try to increase the maximum string and blob length. In fact, you might do well to lower the maximum string and blob length to something more in the range of a few million if that is possible.

  During part of SQLite's INSERT and SELECT processing, the complete content of each row in the database is encoded as a single BLOB. So the SQLITE_MAX_LENGTH parameter also determines the maximum number of bytes in a row.

  The maximum string or BLOB length can be lowered at run-time using the sqlite3_limit(db,SQLITE_LIMIT_LENGTH,size) interface. Limits In SQLite

The Android SDK's CursorWindow has a limitation of 2Mb and that is for all the columns of the row(s) if buffers. As such even if you can store Videos successfully, you may not be able to retrieve those Videos.

The recommended way is what you don't want, that is to store the path to the Video.

If i store the video in my internal/external storage and store the path instead then how will i be able to access the same from some other device.

You would have the same issue with the database as it's typically stored within the Applications data which is protected. That is unless the database is a pre-existing database (i.e. populated with data), in which case the database is distributed with the App via the APK.

If the latter, a pre-existing database distributed via the APK, then the videos can also be distributed as part of the APK and hence as protected as and as exposable as the database.

If your intention is to distribute videos between devices that are not part of the APK then SQlite is probably not the correct solution as it's an embedded database and has no client/server functionality built in.

Besides what if my device gets formatted then I will lose all the data.

In such a scenario, the database would be as vulnerable as any other data, as that is all the database is, a file, just like a video, a word document etc which all need a suitable application to view/change the content. However, if the database is a pre-existing database, then simply re-installing the App would restore the database and other files from the APK.

Working Example

This uses the Suggested/Recommended method assuming the videos are to be distributed with the APK.

• Note Videos Courtesey of Sample Videos

After creating new project 4 videos were downloaded and copied into the res/raw folder (after creating the raw folder) as per :-

The Database Helper (subclass of SQLiteOpenHelper) was created for a 2 column table an with - _id column (note named _id for use with SimpleCursorAdapter). - video_path for storing the path/name of the video (not the full path but sufficient to be able to determine the path from the data stored) - Note UNIQUE has been coded to stop duplicates being added.

With some basic method to allow rows to be added and deleted and for all rows to be extracted (via a Cursor for use with the SimpleCursorAdapter).

DBHelper.java

public class DBHelper extends SQLiteOpenHelper {

    public static final String DBNAME = "myvideos";
    public static final int DBVERSION = 1;

    public static final String TBL_VIDEO = "video";

    public static final String COL_VIDEO_ID = BaseColumns._ID;
    public static final String COL_VIDEO_PATH = "video_path";


    SQLiteDatabase mDB;

    public DBHelper(Context context) {
        super(context, DBNAME, null, DBVERSION);
        mDB = this.getWritableDatabase();
    }


    @Override
    public void onCreate(SQLiteDatabase db) {

        String crt_video_table = "CREATE TABLE IF NOT EXISTS " + TBL_VIDEO + "(" +
                COL_VIDEO_ID + " INTEGER PRIMARY KEY," +
                COL_VIDEO_PATH + " TEXT UNIQUE" +
                ")";
        db.execSQL(crt_video_table);
    }

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

    }

    public long addVideo(String path) {
        ContentValues cv = new ContentValues();
        cv.put(COL_VIDEO_PATH,path);
        return mDB.insert(TBL_VIDEO,null,cv);
    }

    public Cursor getVideos() {
        return mDB.query(TBL_VIDEO,null,null,null,null,null,null);
    }

    public int deleteVideoFromDB(long id) {
        String whereclause = COL_VIDEO_ID + "=?";
        String[] whereargs = new String[]{String.valueOf(id)};
        return mDB.delete(TBL_VIDEO,whereclause,whereargs);
    }
}

A pretty straigforward MainActivity.java (see comments)

public class MainActivity extends AppCompatActivity {

    TextView mMyTextView;
    ListView mVideoList;
    VideoView mVideoViewer;
    DBHelper mDBHlpr;
    Cursor mCsr;
    SimpleCursorAdapter mSCA;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        mMyTextView =  this.findViewById(R.id.mytext);
        mVideoList = this.findViewById(R.id.videolist);
        mVideoViewer = this.findViewById(R.id.videoviewer);

        mDBHlpr = new DBHelper(this);
        addVideosFromRawResourceToDB();
    }

    @Override
    protected void onDestroy() {
        mCsr.close(); //<<<<<<<<<< clear up the Cursor
        super.onDestroy();
    }

    @Override
    protected void onResume() {
        super.onResume();
        manageListView(); //<<<<<<<<<< rebuild and redisplay the List of Videos (in case they have changed) 
    }

    /**
     *  Setup or Refresh the ListView adding the OnItemClick and OnItemLongClick listeners
     */
    private void manageListView() {
        mCsr = mDBHlpr.getVideos();

        // Not setup so set it up
        if (mSCA == null) {
            // Instantiate the SimpleCursorAdapter
            mSCA = new SimpleCursorAdapter(
                    this,
                    android.R.layout.simple_list_item_1, // Use stock layout
                    mCsr, // The Cursor with the list of videos
                    new String[]{DBHelper.COL_VIDEO_PATH}, // the column (columns)
                    new int[]{android.R.id.text1}, // the view id(s) into which the column(s) data will be placed
                    0 
            );
            mVideoList.setAdapter(mSCA); // Set the adpater for the ListView
            /**
             * Add The Long Click Listener (will delete the video row from the DB (NOT the video))
             */
            mVideoList.setOnItemLongClickListener(new AdapterView.OnItemLongClickListener() {
                @Override
                public boolean onItemLongClick(AdapterView<?> parent, View view, int position, long id) {
                    mDBHlpr.deleteVideoFromDB(id);
                    manageListView(); // <<<<<<<<<< refresh the ListView as data has changed
                    return true;
                }
            });
            /**
             * Play the respective video when the item is clicked
             * Note Cursor should be at the correct position so data can be extracted directly from the Cursor
             */
            mVideoList.setOnItemClickListener(new AdapterView.OnItemClickListener() {
                @Override
                public void onItemClick(AdapterView<?> parent, View view, int position, long id) {
                    setCurrentVideo(mCsr.getString(mCsr.getColumnIndex(DBHelper.COL_VIDEO_PATH)));
                }
            });
        } else {
            mSCA.swapCursor(mCsr); //<<<<<<<<<< apply the changed Cursor
        }
    }

    /**
     * Set the currrent video and play it
     * @param path the path (resource name of the video)
     */
    private void setCurrentVideo(String path) {

        mVideoViewer.setVideoURI(
                Uri.parse(
                       "android.resource://" + getPackageName() + "/" + String.valueOf(
                               getResources().getIdentifier(
                                       path,
                               "raw",
                               getPackageName())
                       )
                )
        );
        mVideoViewer.start();
    }

    /**
     *  Look at all the resources in the res/raw folder and add the to the DB (not if they are duplicates due to UNQIUE)
     */
    private void addVideosFromRawResourceToDB() {
            Field[] fields=R.raw.class.getFields();
            for(int count=0; count < fields.length; count++){
                Log.i("Raw Asset: ", fields[count].getName());
                mDBHlpr.addVideo(fields[count].getName());
            }
    }
}

Results

When first started (nothing plays) :-

After long clicking the 1Mb video (deleting the DB entry) :-

After clicking A Video in the List :-