Android SQLite查询所有乐队及歌曲关联状态仅返回关联乐队问题排查

问题描述 #

开发基于SQLite的Android音乐应用时，DatabaseHelper类的getAllBandsWithConnectionStatus方法未达到预期效果：该方法需返回所有乐队，并标记每个乐队是否与指定歌曲关联（关联则isConnected为1，否则为0），但当前仅返回已关联指定歌曲的乐队，未关联的乐队未被纳入结果列表。

目标方法原代码 #

public List<BandInfo> getAllBandsWithConnectionStatus(int songId) {
    List<BandInfo> bandList = new ArrayList<>();
    SQLiteDatabase db = this.getReadableDatabase();

    // Query to retrieve all bands with connection status for a specific song
    String[] columns = {"bands._id", "bands.band_name", "CASE WHEN songs_bands.band_id IS NULL THEN 0 ELSE 1 END AS is_connected"};
    String selection = "songs_bands.song_id = ?";
    String[] selectionArgs = {String.valueOf(songId)};

    Cursor cursor = db.query(
            "bands LEFT JOIN songs_bands ON bands._id = songs_bands.band_id",
            columns,
            selection,
            selectionArgs,
            null,
            null,
            null
    );

    if (cursor.moveToFirst()) {
        int idColumnIndex = cursor.getColumnIndex("_id");
        int nameColumnIndex = cursor.getColumnIndex("band_name");
        int isConnectedColumnIndex = cursor.getColumnIndex("is_connected");

        do {
            int bandId = cursor.getInt(idColumnIndex);
            String bandName = cursor.getString(nameColumnIndex);
            int isConnected = cursor.getInt(isConnectedColumnIndex);

            BandInfo bandInfo = new BandInfo(bandId, bandName, isConnected == 1);
            bandList.add(bandInfo);
        } while (cursor.moveToNext());
    }

    cursor.close();
    // Print the list of BandInfo objects before returning
    for (BandInfo bandInfo : bandList) {
        Log.d("SplashActivityZZ", "(DatabaseHelper) BandInfo in list: " + bandInfo.toString());
    }
    return bandList;
}

涉及表创建SQL #

bands表 #

private static final String SQL_CREATE_BANDS_TABLE =
        "CREATE TABLE " + DatabaseContract.Bands.TABLE_NAME + " (" +
                DatabaseContract.Bands._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                DatabaseContract.Bands.COLUMN_BAND_NAME + " TEXT NOT NULL, " +
                DatabaseContract.Bands.COLUMN_BAND_DESCRIPTION + " TEXT)";

user_bands表 #

private static final String SQL_CREATE_USER_BANDS_TABLE =
        "CREATE TABLE " + DatabaseContract.UserBands.TABLE_NAME + " (" +
                DatabaseContract.UserBands._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                DatabaseContract.UserBands.COLUMN_USER_ID + " INTEGER, " +
                DatabaseContract.UserBands.COLUMN_BAND_ID + " INTEGER, " +
                "FOREIGN KEY (" + DatabaseContract.UserBands.COLUMN_USER_ID + ") REFERENCES " +
                DatabaseContract.Users.TABLE_NAME + "(" + DatabaseContract.Users._ID + "), " +
                "FOREIGN KEY (" + DatabaseContract.UserBands.COLUMN_BAND_ID + ") REFERENCES " +
                DatabaseContract.Bands.TABLE_NAME + "(" + DatabaseContract.Bands._ID + "))";

问题根源与修复方案 #

问题原因 #

原代码中，将songs_bands.song_id = ?作为WHERE条件（selection参数），但左连接后未关联歌曲的乐队对应的songs_bands所有字段均为NULL，NULL = ?的判断结果为false，导致这些行被过滤，最终只保留了已关联的乐队。

修复后的代码 #

将歌曲ID的匹配条件从WHERE子句移至LEFT JOIN的ON条件中，确保左连接时仅匹配关联该歌曲的记录，未关联的乐队依然保留在结果中：

public List<BandInfo> getAllBandsWithConnectionStatus(int songId) {
    List<BandInfo> bandList = new ArrayList<>();
    SQLiteDatabase db = this.getReadableDatabase();

    // 修改JOIN条件，将song_id匹配合并到ON子句中
    String joinTable = "bands LEFT JOIN songs_bands ON bands._id = songs_bands.band_id AND songs_bands.song_id = ?";
    String[] columns = {"bands._id", "bands.band_name", "CASE WHEN songs_bands.band_id IS NULL THEN 0 ELSE 1 END AS is_connected"};
    // 移除原selection参数，因为条件已放到JOIN中
    String[] selectionArgs = {String.valueOf(songId)};

    Cursor cursor = db.query(
            joinTable,
            columns,
            null, // 不再需要WHERE条件
            selectionArgs,
            null,
            null,
            null
    );

    if (cursor.moveToFirst()) {
        int idColumnIndex = cursor.getColumnIndex("_id");
        int nameColumnIndex = cursor.getColumnIndex("band_name");
        int isConnectedColumnIndex = cursor.getColumnIndex("is_connected");

        do {
            int bandId = cursor.getInt(idColumnIndex);
            String bandName = cursor.getString(nameColumnIndex);
            int isConnected = cursor.getInt(isConnectedColumnIndex);

            BandInfo bandInfo = new BandInfo(bandId, bandName, isConnected == 1);
            bandList.add(bandInfo);
        } while (cursor.moveToNext());
    }

    cursor.close();
    for (BandInfo bandInfo : bandList) {
        Log.d("SplashActivityZZ", "(DatabaseHelper) BandInfo in list: " + bandInfo.toString());
    }
    return bandList;
}

修复说明 #

• 将songs_bands.song_id = ?加入LEFT JOIN的ON条件后，左连接会仅匹配与指定歌曲关联的乐队记录，未关联的乐队对应的songs_bands字段仍为NULL。
• CASE语句会正确识别NULL值，将is_connected设为0，确保所有乐队都出现在结果列表中。

内容的提问来源于stack exchange，提问作者kiteandwindsurfer