Android SQLite Example

SQLite Android Tutorial

"DatabaseHandler.java" class in Android project.

package com.digipicsworks.friendlien.SqliteDatabase;

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;

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

/**
 * Created by user on 1/22/2018.
 */

public class DatabaseHandler extends SQLiteOpenHelper {
    private static final int DATABASE_VERSION = 1;

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

    // MyPlayListTable table name
    private static final String TABLE_MYPLAYLIST = "myPlayList";

    private static final String TABLE_SUGGESTED_VIDEO="suggestedVideos";

    // MyPlayLIst Table Columns names
    private static final String KEY_ID = "videoId";
    private static final String KEY_URL = "url";
    private static final String KEY_TITLE = "title";
    private static final String KEY_DESCRIPTION = "description";
    private static final String KEY_THUMBNAIL = "thumbnail";

    //SuggestVedios Table Columns Names
    private static final String ID="id";
    private static final String KEY_ID_SUGGESTED="videoId";
    private static final String KEY_URL_SUGGESTED="url";
    private static final String KEY_TITLE_SUGGESTED="title";
    private static final String KEY_DESCRIPTION_SUGGESTED="description";
    private static final String KEY_THUMBNAIL_SUGGESTED="thumbnail";


    public DatabaseHandler(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }
    //MyPlayList Table Creat Command
    private static String CREATE_MY_PLAY_LIST_TABL = "CREATE TABLE " + TABLE_MYPLAYLIST + "(" + KEY_ID + " TEXT," + KEY_URL + " TEXT," + KEY_TITLE + " TEXT," +
            KEY_DESCRIPTION + " TEXT," + KEY_THUMBNAIL + " TEXT" + ")";
    //Suggersted Videos
    private static String CREATE_SUGGESTED_VIDEOS_TABLE="CREATE TABLE "+TABLE_SUGGESTED_VIDEO+"(" + ID + " INTEGER PRIMARY KEY,"+KEY_ID_SUGGESTED+" TEXT,"+KEY_URL_SUGGESTED+" TEXT,"
            +KEY_TITLE_SUGGESTED+" TEXT,"+KEY_DESCRIPTION_SUGGESTED+" TEXT ,"+KEY_THUMBNAIL_SUGGESTED+" TEXT"+")";

    @Override
    public void onCreate(SQLiteDatabase db) {
        Log.d("CreateTableQuery", CREATE_MY_PLAY_LIST_TABL);
        Log.d("CreateTableQuery", CREATE_SUGGESTED_VIDEOS_TABLE);
        db.execSQL(CREATE_MY_PLAY_LIST_TABL);
        db.execSQL(CREATE_SUGGESTED_VIDEOS_TABLE);

        Log.d("ENDLINE","END_LINE");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int i, int i1) {
        // Drop older table if existed
        Log.d("onUpgrade",TABLE_MYPLAYLIST);
        Log.d("onUpgrade",TABLE_SUGGESTED_VIDEO);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_MYPLAYLIST);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_SUGGESTED_VIDEO);

        // Create tables again
        onCreate(db);

    }

    //Add Video TO PlayList
    public void addVideo(VideoInfo videoInfo) {

        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();

        values.put(KEY_ID, videoInfo.getVideoId());
        values.put(KEY_URL, videoInfo.getUrl());
        values.put(KEY_TITLE, videoInfo.getTitle());
        values.put(KEY_DESCRIPTION, videoInfo.getDescription());
        values.put(KEY_THUMBNAIL, videoInfo.getThumbnail());
        //Insert Row
        db.insert(TABLE_MYPLAYLIST, null, values);
        db.close();

    }
    //Add Videos To Suggestion PlayList
    public void addSuggestedVideo(VideoInfo videoInfo){
        SQLiteDatabase db=this.getWritableDatabase();
        ContentValues values=new ContentValues();

        Log.d("VideoInformation",videoInfo.getTitle());
        Log.d("VideoInformation",videoInfo.getThumbnail());
        Log.d("VideoInformation",videoInfo.getDescription());
        Log.d("VideoInformation",videoInfo.getUrl());
        Log.d("VideoInformation",videoInfo.getVideoId());

        values.put(KEY_ID_SUGGESTED,videoInfo.getVideoId());
        values.put(KEY_URL_SUGGESTED,videoInfo.getUrl());
        values.put(KEY_TITLE_SUGGESTED,videoInfo.getTitle());
        values.put(KEY_DESCRIPTION_SUGGESTED,videoInfo.getDescription());
        values.put(KEY_THUMBNAIL_SUGGESTED,videoInfo.getThumbnail());

        //Insert Row

        db.insert(TABLE_SUGGESTED_VIDEO,null,values);
        db.close();
    }

    //Getting All Video of PlayList
    public List<VideoInfo> getAllVideos() {
        List<VideoInfo> videoInfoList = new ArrayList<VideoInfo>();
        //Select All Query
        String selectAllQuery = "SELECT * FROM " + TABLE_MYPLAYLIST;
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(selectAllQuery, null);

        //looping through all the row and adding to list

        VideoInfo videoInfo = null;
        if (cursor.moveToFirst()) {
            do {
                Log.d("GetFirstStringg", String.valueOf(cursor.getString(1)));

                videoInfo = new VideoInfo();

                videoInfo.setVideoId(cursor.getString(0));
                videoInfo.setUrl(cursor.getString(1));
                videoInfo.setTitle(cursor.getString(2));
                videoInfo.setDescription(cursor.getString(3));
                videoInfo.setThumbnail(cursor.getString(4));

                videoInfoList.add(videoInfo);

            } while (cursor.moveToNext());

        }
        return videoInfoList;
    }
    //Get All Suggested Video
    public List<VideoInfo> getAllSuggestedVideo() {
        List<VideoInfo> videoInfoList = new ArrayList<VideoInfo>();
        //Select All Query
        String selectAllQuery = "SELECT * FROM " + TABLE_SUGGESTED_VIDEO;
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(selectAllQuery, null);
        Log.d("TABLE_INFORMATion",cursor.getColumnName(1));
        Log.d("TABLE_INFORMATion", String.valueOf(cursor.getColumnCount()));
        Log.d("TABLE_INFORMATion", String.valueOf(cursor.getCount()));

        //looping through all the row and adding to list

        VideoInfo videoInfo = null;
        if (cursor.moveToFirst()) {
            do {
                Log.d("GetFirstStringg", String.valueOf(cursor.getString(0)));

                videoInfo = new VideoInfo();
                videoInfo.setVideoId(cursor.getString(0));
                videoInfo.setUrl(cursor.getString(2));
                videoInfo.setTitle(cursor.getString(3));
                videoInfo.setDescription(cursor.getString(4));
                videoInfo.setThumbnail(cursor.getString(5));

                videoInfoList.add(videoInfo);

            } while (cursor.moveToNext());

        }
        return videoInfoList;
    }

    public void deleteVideo(String videoId) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE_MYPLAYLIST, KEY_ID + " = ?", new String[]{videoId});
        db.close();

    }
    public void deleteSuggestedVideos(String id){
        SQLiteDatabase db=this.getWritableDatabase();
        db.delete(TABLE_SUGGESTED_VIDEO,ID+" = ?",new String[]{id});
    }
}

Acess these function from another classes in Android project

"MainActivity.java" class

DatabseHendler db = new DatabaseHandler(getActivity());
db.addVideo(new VideoInfo(videos.get((int) index).getVideoId(), videos.get((int) index).getUrl(),
        videos.get((int) index).getTitle(),
        videos.get((int) index).getDescription(), videos.get((int) index).getThumbnail()));