如何在Android Room中使用SQLite Case表达式按指定列升降序查询数据

1. 预定义MediaStore列与Room字段的映射关系 #

首先建立常量映射表，统一关联MediaStore的列名和你Room实体的字段名，同时做合法列名校验，避免SQL注入风险：

// 键为MediaStore列名，值为对应Room实体字段名
public static final Map<String, String> COLUMN_MAPPING = new HashMap<>();
static {
    COLUMN_MAPPING.put(MediaStore.Audio.Media.TITLE, "_title");
    COLUMN_MAPPING.put(MediaStore.Audio.Media.ALBUM, "_album");
    COLUMN_MAPPING.put(MediaStore.Audio.Media.ARTIST, "_artist");
    COLUMN_MAPPING.put(MediaStore.Audio.Media.COMPOSER, "_composer");
    COLUMN_MAPPING.put(MediaStore.Audio.Media.DATE_ADDED, "_dateAdded");
    COLUMN_MAPPING.put(MediaStore.Audio.Media.DURATION, "_duration");
    // 其他需要的字段按需补充
}

2. 定义Room DAO的动态查询方法 #

使用@RawQuery注解实现动态SQL查询，支持自定义列、自定义排序规则：

@Dao
public interface SongDao {
    // 动态查询，直接传入拼装好的SupportSQLiteQuery对象
    @RawQuery
    List<SongEntity> querySongs(SupportSQLiteQuery query);
}

3. 拼装查询逻辑实现复用 #

参考你原MediaStore查询的逻辑，拼装对应Room查询语句：

// 入参1：要查询的MediaStore列名集合 对应原方法的columns()参数
// 入参2：排序用的MediaStore列名 对应原方法的SortOrder参数
// 入参3：是否升序排序 true为ASC false为DESC
public List<SongEntity> querySongsFromRoom(List<String> mediaStoreColumns, String sortMediaStoreColumn, boolean isAsc) {
    // 1. 校验并转换要查询的列名
    List<String> roomColumns = new ArrayList<>();
    for (String mediaCol : mediaStoreColumns) {
        if (COLUMN_MAPPING.containsKey(mediaCol)) {
            roomColumns.add(COLUMN_MAPPING.get(mediaCol));
        }
    }
    // 如果没有合法列，默认查所有字段
    if (roomColumns.isEmpty()) {
        roomColumns.add("*");
    }

    // 2. 校验并转换排序字段
    String orderClause = "";
    if (COLUMN_MAPPING.containsKey(sortMediaStoreColumn)) {
        String roomSortColumn = COLUMN_MAPPING.get(sortMediaStoreColumn);
        orderClause = "ORDER BY " + roomSortColumn + (isAsc ? " ASC" : " DESC");
    }

    // 3. 拼装SQL语句，这里示例条件和原逻辑对齐，你可以按需扩展其他查询条件
    String sql = "SELECT " + TextUtils.join(",", roomColumns) + " FROM SongEntity " + orderClause;

    // 4. 构造查询对象执行查询
    SimpleSQLiteQuery query = new SimpleSQLiteQuery(sql);
    return songDao.querySongs(query);
}

注意事项 #

• 所有动态拼接的列名必须经过COLUMN_MAPPING的存在性校验，禁止直接拼接外部传入的未校验字符串，避免SQL注入风险
• 如果需要加过滤条件，推荐使用SimpleSQLiteQuery的绑定参数功能，不要直接拼接条件值，示例如下：

  // 比如要查询时长大于1分钟的歌曲
  String sql = "SELECT " + TextUtils.join(",", roomColumns) + " FROM SongEntity WHERE _duration > ? " + orderClause;
  SimpleSQLiteQuery query = new SimpleSQLiteQuery(sql, new Object[]{60 * 1000});
  

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