如何在Room数据库DAO中拼接变量存储的多个子查询实现动态SQL

Room 针对动态SQL场景提供了@RawQuery注解方案，支持传入自行拼接的SQL语句执行，完全满足动态拼接子查询、排序规则的需求，具体实现如下：

1. 首先定义DAO层通用查询接口 #

@Dao
public interface UserDao {
    // 动态查询返回映射好的实体类列表
    @RawQuery
    List<User> executeDynamicQuery(SupportSQLiteQuery query);

    // 如需返回Cursor可使用该重载
    @RawQuery
    Cursor executeDynamicQueryReturnCursor(SupportSQLiteQuery query);
}

场景一：动态选择子查询实现 #

public List<User> loadAllUserAddress(boolean isLocal){
    String subQuery = isLocal? 
        "SELECT * FROM localAddressTable where activeAddressId = 1" : 
        "SELECT * FROM permanentAddressTable where activeAddressId = 1";
    // 注：原拼接逻辑中WHERE后直接跟子查询语法有误，需根据业务补IN/EXISTS等关联条件，此处仅做示例
    String fullQuery = "SELECT * FROM userTable WHERE userId IN (" + subQuery + ")";
    // 封装为Room支持的查询对象
    SupportSQLiteQuery query = new SimpleSQLiteQuery(fullQuery);
    // 调用DAO执行查询
    return userDao.executeDynamicQuery(query);
}

场景二：动态拼接排序规则实现 #

public List<User> loadAllUserDetails(int orderFlag){
    String orderClause;
    if(orderFlag == ORDER_TYPE_FIRST_NAME){
        orderClause = Constant.ORDER_BY_FIRST_NAME_DESC;
    }else{
        orderClause = Constant.ORDER_BY_LAST_NAME_DESC;
    }
    // 注：原逻辑中多余的WHERE关键字已删除，避免SQL语法错误
    String fullQuery = "SELECT * FROM userTable " + orderClause;
    SupportSQLiteQuery query = new SimpleSQLiteQuery(fullQuery);
    return userDao.executeDynamicQuery(query);
}

注意事项 #

• 拼接SQL时注意防范SQL注入风险，若拼接内容包含用户输入的值，务必做严格校验，优先使用带占位符的传参方式：new SimpleSQLiteQuery("SELECT * FROM userTable WHERE age > ?", new Object[]{18})
• 若需要将查询结果映射为自定义实体类，需保证查询返回的列名和实体类字段名完全匹配，Room会自动完成映射转换

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