Android SQLite how to insert values with Foreign KEY

Question

I have created a database with SQLite in Android and I have a table Playlist_Songs which use foreign keys on two tables: Songs and Playlists

How can I add a value with foreign key from another table because values.put(playlists_songs_playlistid, "(SELECT "+playlists_id+" FROM "+TABLE_PLAYLISTS+" WHERE "+playlists_name+"="+playlist+")"); doesn't work in my case.

public class MusicDB extends SQLiteOpenHelper{

public static String DATABASE_NAME = "MusicDB";
public static final int DATABASE_VERSION = 4;

public static final String TABLE_CONFIG = "config";
public static final String TABLE_SONGS = "songs";
public static final String TABLE_PLAYLISTS = "playlists";
public static final String TABLE_PLAYLISTS_SONGS = "playlists_songs";

// CONFIG TABLE //
public static final String config_id = "id";
public static final String config_Directory = "Directory";

// SONGS TABLE //
public static final String songs_id = "id";
public static final String songs_name = "Name";
public static final String songs_path = "Path";
public static final String songs_downloadLing = "downloadLing";

// PLAYLISTS TABLE //
public static final String playlists_id = "id";
public static final String playlists_name = "Name";

// PLAYLISTS SONGS TABLE //
public static final String playlists_songs_id = "id";
public static final String playlists_songs_playlistid = "playlist_id";
public static final String playlists_songs_songid = "song_id";

public MusicDB(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
}

@Override
public void onCreate(SQLiteDatabase db) {
    final String CREATE_CONFIG_TABLE = "CREATE TABLE "+TABLE_CONFIG+" ("+config_id+" INTEGER PRIMARY KEY,"+config_Directory+" TEXT NOT NULL UNIQUE)";
    db.execSQL(CREATE_CONFIG_TABLE);

    final String CREATE_SONGS_TABLE = "CREATE TABLE "+TABLE_SONGS+" ("+songs_id+" INTEGER PRIMARY KEY,"+songs_name+" TEXT NOT NULL UNIQUE,"+songs_path+" TEXT NOT NULL,"+songs_downloadLing+" TEXT UNIQUE)";
    db.execSQL(CREATE_SONGS_TABLE);

    final String CREATE_PLAYLISTS_TABLE = "CREATE TABLE "+TABLE_PLAYLISTS+" ("+playlists_id+" INTEGER PRIMARY KEY,"+playlists_name+" TEXT NOT NULL UNIQUE)";
    db.execSQL(CREATE_PLAYLISTS_TABLE);

    final String CREATE_PLAYLISTS_SONGS = "CREATE TABLE "+TABLE_PLAYLISTS_SONGS+" ("+playlists_songs_id+" INTEGER PRIMARY KEY,"+playlists_songs_playlistid+" INTEGER NOT NULL,"+
            playlists_songs_songid+" INTEGER NOT NULL, FOREIGN KEY ("+playlists_songs_playlistid+") REFERENCES "+TABLE_PLAYLISTS+"("+playlists_id+") ON DELETE CASCADE," +
            "FOREIGN KEY ("+playlists_songs_songid+") REFERENCES "+TABLE_SONGS+"("+songs_id+") ON DELETE CASCADE);";
    db.execSQL(CREATE_PLAYLISTS_SONGS);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    Log.i("TEST", "Se apeleaza onUPDATE");
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_CONFIG);
    db.execSQL("DROP TABLE IF EXISTS "+TABLE_SONGS);

    onCreate(db);
}

public void addDirectory(String name) {

    Log.i("TEST", "Adaug in Config " + name);

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

    values.put(config_Directory, name);

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

public ArrayList<String> getDirectories() {
    ArrayList<String> directories = new ArrayList<>();

    String selectQuery = "SELECT * FROM "+TABLE_CONFIG;

    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.rawQuery(selectQuery, null);

    if (cursor.moveToFirst()) {
        do {
            directories.add(cursor.getString(1));
        } while (cursor.moveToNext());
    }

    cursor.close();
    db.close();
    return directories;
}

public void addSong(String name, String path) {
    Log.i("TEST", "Adaug in Songs " + name);

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

    values.put(songs_name, name);
    values.put(songs_path, path);

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

public ArrayList<String> getSongsByName() {
    ArrayList<String> songs = new ArrayList<>();

    String selectQuery = "SELECT * FROM "+TABLE_SONGS;

    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.rawQuery(selectQuery, null);

    if (cursor.moveToFirst()) {
        do {
            songs.add(cursor.getString(1));
        } while (cursor.moveToNext());
    }

    cursor.close();
    db.close();
    return songs;
}

public void addPlaylist(String name) {
    Log.i("TEST", "Adaug in Playlists " + name);

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

    values.put(playlists_name, name);

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

public ArrayList<String> getPlaylists() {
    ArrayList<String> playlists = new ArrayList<>();

    String selectQuery = "SELECT * FROM "+TABLE_PLAYLISTS;

    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.rawQuery(selectQuery, null);

    if (cursor.moveToFirst()) {
        do {
            playlists.add(cursor.getString(1));
        } while (cursor.moveToNext());
    }

    cursor.close();
    db.close();
    return playlists;
}

public void addSongToPlaylist(String song, String playlist) {
    Log.i("TEST", "Adaug in Playlistul " + playlist +" melodia "+song);

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

    values.put(playlists_songs_playlistid, "(SELECT "+playlists_id+" FROM "+TABLE_PLAYLISTS+" WHERE "+playlists_name+"="+playlist+")");
    values.put(playlists_songs_songid, "(SELECT "+songs_id+" FROM "+TABLE_SONGS+" WHERE "+songs_name+"="+song+")");

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

}

Unrelated note: Your code seems to be vulnerable to SQL Injection, take care with what is being put into the variables or - better yet - use parameterized queries. — Jens
– Jens, Commented Oct 28, 2015 at 11:27

CL. · Accepted Answer · 2015-10-28 11:51:13Z

3

ContentValues supports only plain values, not SQL expressions.

You could either execute the lookup queries separately with query(), or use execSQL to execute your own SQL:

db.execSQL("INSERT INTO "+TABLE_PLAYLISTS_SONGS+
           "("+playlists_songs_playlistid+","+
               playlists_songs_songid+")"+
           "VALUES((SELECT "+playlists_id+
                   " FROM "+TABLE_PLAYLISTS+
                   " WHERE "+playlists_name+"=?),"+
                  "(SELECT "+songs_id+
                   " FROM "+TABLE_SONGS+
                   " WHERE "+songs_name+"=?))",
           new Object[]{ playlist, song });

answered Oct 28, 2015 at 11:51

CL.

182k18 gold badges241 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Marian Pavel Over a year ago

Thank you for your answer, that's what I was looking for.

Collectives™ on Stack Overflow

Android SQLite how to insert values with Foreign KEY

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related