Insert data into table and if already present bring it to the top SQLite

Question

I'm making a "Recently played" playlist in my music app. I have created a database for storing the info related to songs. What my current code does is whenever I play a song it add it the database and I can successfully display it. But the problem is when I play the same song twice, the database isn't updating. Example, I played song A,song B, song C, song D respectively. Then it will display song A, song B, song C, song D. But if i play song A,song B, song C, song D and song B again respectively. Then it again displays the same(song A, song B, song C, song D). But what I want is: song A, song C, song D, song B. It should update and show the latest song played, that is what i meant. I can't figure out how to do it.

Code for table creation:

 public void onCreate(SQLiteDatabase sqLiteDatabase) {

    String CREATE_TABLE = "CREATE TABLE " + TABLE_NAME+ "("
            + KEY_ID + " INTEGER PRIMARY KEY,"
            + KEY_NAME + " TEXT,"
            + KEY_ARTIST + " TEXT,"
            + KEY_DURATION + " INTEGER,"
            + KEY_ART + " TEXT"
            + ");";

    sqLiteDatabase.execSQL(CREATE_TABLE);

}

Code for getting and displaying the data :

   public ArrayList<SongInfoModel> getRecentlyPlayed(){

    ArrayList<SongInfoModel> rpList = new ArrayList<>();
    String selectQuery  = "SELECT * FROM " + TABLE_NAME;
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.rawQuery(selectQuery,null);
    if(cursor.moveToFirst()){

        do{

            long id = cursor.getLong(0);
            String SongName = cursor.getString(1);
            String artistName = cursor.getString(2);
            long dur = cursor.getLong(3);
            String Art = cursor.getString(4);

            SongInfoModel sh = new SongInfoModel(id,SongName,artistName,dur,null,Art);

            rpList.add(sh);
        }while (cursor.moveToNext());
    }

    return rpList;

Code for adding data:

 public void addSong(SongInfoModel songInfoModel){

        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(KEY_ID, songInfoModel.getSongID());
        values.put(KEY_NAME, songInfoModel.getSongName());
        values.put(KEY_ARTIST, songInfoModel.getArtistName());
        values.put(KEY_DURATION, songInfoModel.getDuration());
        values.put(KEY_ART, songInfoModel.getAlbumIDArtwork());

        db.insert(TABLE_NAME,null,values);
        db.close();

    }

you have list of song which is in String array then you which is played in `media player`.make another list and add it in . — Divyesh Kalotra, Dec 22 '17 at 08:04

score 0 · Answer 1 · answered Dec 22 '17 at 07:40

0

Change your table query trailing semicolon inside the create query ")";

String CREATE_TABLE = "CREATE TABLE " + TABLE_NAME+ "("
            + KEY_ID + " INTEGER PRIMARY KEY,"
            + KEY_NAME + " TEXT,"
            + KEY_ARTIST + " TEXT,"
            + KEY_DURATION + " INTEGER,"
            + KEY_ART + " TEXT"
            + ")";

    sqLiteDatabase.execSQL(CREATE_TABLE);

answered Dec 22 '17 at 07:40

Ratilal Chopda

4,162
4
18
31

No difference, it is the same. – Rektirino Dec 22 '17 at 07:45

Ivan · Answer 2 · 2017-12-22T11:14:54.510

What comes to my mind is to add one field which indicates the number of times played, lets say KEY_TIMES.

When adding a song check

If its already there, if it is there update the field
If not add it with the field at default value.

Finally, for getting the values just use the same query, but ordering by this new field:

String selectQuery  = "SELECT * FROM " + TABLE_NAME + " ORDER BY " + KEY_TIMES;

To create the table use:

public void onCreate(SQLiteDatabase sqLiteDatabase) {
    String CREATE_TABLE = 
    "CREATE TABLE " + TABLE_NAME+ "(" +
        KEY_ID + " INTEGER PRIMARY KEY," +
        KEY_NAME + " TEXT," + 
        KEY_ARTIST + " TEXT," +
        KEY_DURATION + " INTEGER," +
        KEY_LAST_PLAYED + " INTEGER," +
        KEY_TIMES + " INTEGER," +
        KEY_ART + " TEXT" + ");";

    sqLiteDatabase.execSQL(CREATE_TABLE); 
}

And then when you are going to add song, make a query with the ID you want to add, if the query returns an entry in the table, make a query to update the KEY_TIMES field. If it does not return an entry, make the insert as you are making it right now, but adding the new field. (Can't provide code for this right now)

Vasili Fedotov · Answer 3 · 2017-12-22T10:27:55.650

0

What you can do is add a lastPlayed date field to your code. So it looks like this.

public void onCreate(SQLiteDatabase sqLiteDatabase) {
    String CREATE_TABLE = 
    "CREATE TABLE " + TABLE_NAME+ "(" +
        KEY_ID + " INTEGER PRIMARY KEY," +
        KEY_NAME + " TEXT," + 
        KEY_ARTIST + " TEXT," +
        KEY_DURATION + " INTEGER," +
        KEY_LAST_PLAYED + " INTEGER," +
        KEY_ART + " TEXT" + ");";

    sqLiteDatabase.execSQL(CREATE_TABLE); 
}

Your code for displaying the data will look like this and make sure to order by last played:

public ArrayList<SongInfoModel getRecentlyPlayed() { 
    ArrayList<SongInfoModel> rpList = new ArrayList<>();
    String selectQuery = "SELECT * FROM " + TABLE_NAME + " ORDER BY " + KEY_LAST_PLAYED + " ASC;";
    SQLiteDatabase db = this.getWritableDatabase(); 
    Cursor cursor = db.rawQuery(selectQuery,null);
    if(cursor.moveToFirst()){ 
        do{ 
            long id = cursor.getLong(0); 
            String SongName = cursor.getString(1); 
            String artistName = cursor.getString(2); 
            long dur = cursor.getLong(3); 
            String Art = cursor.getString(5); 
            SongInfoModel sh = new SongInfoModel(id,SongName,artistName,dur,null,Art); 
            rpList.add(sh);
       }while (cursor.moveToNext()); 
} 
return rpList;
}

And your code for adding data:

public void addSong(SongInfoModel songInfoModel){
    SQLiteDatabase db = this.getWritableDatabase(); 
    ContentValues values = new ContentValues(); 
    values.put(KEY_ID, songInfoModel.getSongID()); 
    values.put(KEY_NAME, songInfoModel.getSongName()); 
    values.put(KEY_ARTIST, songInfoModel.getArtistName()); 
    values.put(KEY_DURATION, songInfoModel.getDuration()); 
    values.put(KEY_LAST_PLAYED, new Date().getTime());
    values.put(KEY_ART, songInfoModel.getAlbumIDArtwork()); 
    db.insert(TABLE_NAME,null,values); 
    db.close();
}

This combination will get you the data in the order last played.

edited Dec 22 '17 at 10:27

answered Dec 22 '17 at 08:41

Vasili Fedotov

1,091
14
31

values.put(KEY_LAST_PLAYED, new Date().getTime()); Is it an inbuilt class in android? new Date()?? – Rektirino Dec 22 '17 at 08:44
I think it's straight up a Java class – Vasili Fedotov Dec 22 '17 at 08:45
So it should be present, no need to bring new libraries for it. What the command does is give you a representation in milliseconds of the current time and date based on the local device clock. – Vasili Fedotov Dec 22 '17 at 08:47
BTW I typed this on my phone, so I couldn't make sure there are no mistakes. If the order is reversed try DSC instead of ASC in the Order by part. – Vasili Fedotov Dec 22 '17 at 08:51
it is giving me an error : no column exists KEY_LAST_PLAYED – Rektirino Dec 22 '17 at 09:10
Did you delete your old table, to make sure the Create function runs again? – Vasili Fedotov Dec 22 '17 at 09:11
It is still giving me the same error. I uninstalled the app, cleared app data, cache..and even incremented the database version ,still no luck – Rektirino Dec 22 '17 at 09:59
Is there any other way to add column to the table? – Rektirino Dec 22 '17 at 10:00
What are the values of your key constants? – Vasili Fedotov Dec 22 '17 at 10:01
private static final String TABLE_NAME = "RECENTLYPLAYED"; private static final String KEY_ID = "ID"; private static final String KEY_NAME = "SONG"; private static final String KEY_ARTIST = "ARTIST"; private static final String KEY_DURATION ="DURATION"; private static final String KEY_LAST_PLAYED ="LPLAYED"; private static final String KEY_ART = "ART"; – Rektirino Dec 22 '17 at 10:02
private static final int DATABASE_VER = 1; private static final String DATABASE_NAME = "MUSICANADB"; – Rektirino Dec 22 '17 at 10:04
Is the error when you try to write or when you read? If it's when you read try to search for the correct syntax to implement order by. – Vasili Fedotov Dec 22 '17 at 10:19
Also, I'm not sure if it's ASC; or just ASC without the semicolon. – Vasili Fedotov Dec 22 '17 at 10:29
it is not sorting according to time and its DESC. – Rektirino Dec 22 '17 at 10:37

Insert data into table and if already present bring it to the top SQLite

3 Answers3