基于两个不同ID从SQLite数据库查询记录的技术问题

我来帮你梳理下代码里的问题，再给出正确的实现方案～

核心错误点 #

你的getSeries方法主要有两个致命问题：

• SQL查询条件语法错误：你直接把两个列名拼接在一起，既没加AND连接条件，也没给每个条件设置独立的占位符?，这会导致SQL语句完全无法被数据库解析。
• query方法参数传递错误：你重复写了两次参数数组，这直接违反了方法的参数格式要求，会导致编译报错。正确的做法是把两个查询参数放在同一个字符串数组里。

修正后的代码实现 #

方案1：返回单个赛事记录（适用于确定只有一条匹配结果的场景） #

public Series getSeries(String leagueId, String bowlerId) {
    Series series = null;
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = null;
    
    try {
        // 正确构建多条件查询语句：用AND连接两个字段的匹配条件
        String selection = Series.COLUMN_LEAGUE_ID + "=? AND " + Series.COLUMN_BOWLER_ID + "=?";
        // 参数数组顺序要和占位符一一对应
        String[] selectionArgs = new String[]{leagueId, bowlerId};
        
        cursor = db.query(
                Series.TABLE_NAME,
                new String[]{Series.COLUMN_ID, Series.COLUMN_LEAGUE_ID, Series.COLUMN_BOWLER_ID, 
                             Series.COLUMN_NAME, Series.COLUMN_SERIES_AVERAGE, Series.COLUMN_TIMESTAMP},
                selection,
                selectionArgs,
                null,
                null,
                null,
                null
        );
        
        // 先判断cursor非空，再读取数据避免空指针
        if (cursor != null && cursor.moveToFirst()) {
            series = new Series(
                    cursor.getInt(cursor.getColumnIndex(Series.COLUMN_ID)),
                    cursor.getString(cursor.getColumnIndex(Series.COLUMN_LEAGUE_ID)),
                    cursor.getString(cursor.getColumnIndex(Series.COLUMN_BOWLER_ID)),
                    cursor.getString(cursor.getColumnIndex(Series.COLUMN_NAME)),
                    cursor.getString(cursor.getColumnIndex(Series.COLUMN_SERIES_AVERAGE)),
                    cursor.getString(cursor.getColumnIndex(Series.COLUMN_TIMESTAMP))
            );
        }
    } finally {
        // 确保资源一定会被释放，防止内存泄漏
        if (cursor != null) {
            cursor.close();
        }
        db.close();
    }
    
    return series;
}

方案2：返回所有匹配的赛事记录（更符合业务逻辑，通常选手在联赛下会有多条赛事记录） #

public List<Series> getSeriesList(String leagueId, String bowlerId) {
    List<Series> seriesList = new ArrayList<>();
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = null;
    
    try {
        String selection = Series.COLUMN_LEAGUE_ID + "=? AND " + Series.COLUMN_BOWLER_ID + "=?";
        String[] selectionArgs = new String[]{leagueId, bowlerId};
        
        cursor = db.query(
                Series.TABLE_NAME,
                new String[]{Series.COLUMN_ID, Series.COLUMN_LEAGUE_ID, Series.COLUMN_BOWLER_ID, 
                             Series.COLUMN_NAME, Series.COLUMN_SERIES_AVERAGE, Series.COLUMN_TIMESTAMP},
                selection,
                selectionArgs,
                null,
                null,
                Series.COLUMN_TIMESTAMP + " DESC" // 可选：按时间倒序排列结果
        );
        
        // 遍历cursor读取所有匹配数据
        if (cursor != null && cursor.moveToFirst()) {
            do {
                Series series = new Series(
                        cursor.getInt(cursor.getColumnIndex(Series.COLUMN_ID)),
                        cursor.getString(cursor.getColumnIndex(Series.COLUMN_LEAGUE_ID)),
                        cursor.getString(cursor.getColumnIndex(Series.COLUMN_BOWLER_ID)),
                        cursor.getString(cursor.getColumnIndex(Series.COLUMN_NAME)),
                        cursor.getString(cursor.getColumnIndex(Series.COLUMN_SERIES_AVERAGE)),
                        cursor.getString(cursor.getColumnIndex(Series.COLUMN_TIMESTAMP))
                );
                seriesList.add(series);
            } while (cursor.moveToNext());
        }
    } finally {
        if (cursor != null) {
            cursor.close();
        }
        db.close();
    }
    
    return seriesList;
}

额外优化建议 #

1. 避免空指针风险：原代码直接调用cursor.moveToFirst()未判断cursor是否为null，修正后增加了非空校验，防止崩溃。
2. 资源安全管理：用try-finally块确保cursor和数据库连接一定会被关闭，避免内存泄漏。
3. 更简便的实现方式：如果是Android项目，推荐使用Room Persistence Library——Google官方封装的SQLite库，通过注解自动生成查询代码，完全避免手写SQL的语法错误。比如实现这个查询只需要在Dao接口里写：

@Query("SELECT * FROM " + Series.TABLE_NAME + " WHERE " + Series.COLUMN_LEAGUE_ID + " = :leagueId AND " + Series.COLUMN_BOWLER_ID + " = :bowlerId")
List<Series> getSeriesByLeagueAndBowler(String leagueId, String bowlerId);

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