在具有多个表的sqlite数据库中检索链接对象列表的查询是什么？

Question

我制作了一个sqlite数据库，其表如下：

public void onCreate(SQLiteDatabase db) {
    db.execSQL("CREATE TABLE "+TABLE_CITY+"("
            +KEY_ID+" INTEGER PRIMARY KEY, "
            +KEY_CITY+" TEXT, "+KEY_COUNTRY+" TEXT)");
    db.execSQL("CREATE TABLE "+TABLE_DIARY+"("
            +KEY_ID+" INTEGER PRIMARY KEY, "
            +KEY_DATE+" TEXT, "+KEY_STORY+" TEXT)");
    db.execSQL("CREATE TABLE "+TABLE_CITY_DIARY+"("
            +KEY_ID+" INTEGER PRIMARY KEY, "
            +KEY_CITY_ID+" TEXT, "+KEY_DIARY_ID+" TEXT)");
}

现在，我想检索属于某个城市的所有日记条目的列表(例如，巴塞罗那包含3个日记条目，罗马5个日志条目，但我只想要一个属于巴塞罗那的3个日志条目列表)。我为此创建了一个方法，但我不确定查询应该是什么。

    public List<Diary> getDiariesByCity(String city_name){
    List<Diary>diaries=new ArrayList<Diary>();
    SQLiteDatabase db=this.getReadableDatabase();
    Cursor curs=db.rawQuery("SELECT * FROM "+TABLE_DIARY........,
            new String[]{city_name});
    if(curs.moveToFirst()){
        do{
            Diary diary=new Diary();
            diary.setId(curs.getInt(curs.getColumnIndex(KEY_ID)));
            diary.setDate(curs.getString(curs.getColumnIndex(KEY_DATE)));
            diary.setStory(curs.getString(curs.getColumnIndex(KEY_STORY)));
            diaries.add(diary);
        }while(curs.moveToNext());
    }return diaries;
}

Answer 1

您应该像这样使用内部连接：

Cursor curs=db.rawQuery("SELECT * FROM " +  TABLE_CITY_DIARY + 
+ " INNER JOIN " + TABLE_CITY + " ON " + TABLE_CITY_DIARY + "." + KEY_CITY_ID + "=" + TABLE_CITY + "." + KEY_ID 
+ " INNER JOIN " + TABLE_DIARY + " ON " + TABLE_CITY_DIARY + "." + KEY_DIARY_ID + "=" + TABLE_DIARY + "." + KEY_ID 
+ " WHERE " +  TABLE_CITY + "." + KEY_CITY + "=?", new String[]{city_name});