Android 多條件動態查詢 SQLite：城市、時間、類型靈活組合

核心思路：在應用層構建動態 SQL

SQLite 不支持存儲過程/動態 SQL，最簡潔做法是 Java/Kotlin 端根據條件拼接 WHERE 子句。

實現代碼

public List query(String city, String time, String type) {
    StringBuilder sql = new StringBuilder("SELECT * FROM items WHERE 1=1");
    List args = new ArrayList<>();

    if (!"任何城市".equals(city)) {
        sql.append(" AND city = ?");
        args.add(city);
    }
    if (!"任何時間".equals(time)) {
        sql.append(" AND time = ?");
        args.add(time);
    }
    if (!"任何類型".equals(type)) {
        sql.append(" AND type = ?");
        args.add(type);
    }

    Cursor cursor = db.rawQuery(sql.toString(), args.toArray(new String[0]));
    // ... 解析 cursor
    return list;
}

進階：QueryBuilder 封裝

public class ItemQueryBuilder {
    private final SQLiteDatabase db;
    private String city, time, type;

    public ItemQueryBuilder(SQLiteDatabase db) { this.db = db; }
    public ItemQueryBuilder city(String c) { this.city = c; return this; }
    public ItemQueryBuilder time(String t) { this.time = t; return this; }
    public ItemQueryBuilder type(String t) { this.type = t; return this; }

    public List build() {
        SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
        qb.setTables("items");
        String[] selectionArgs = new String[3];
        int idx = 0;
        StringBuilder where = new StringBuilder();
        if (city != null && !"任何城市".equals(city)) { where.append("city = ?"); selectionArgs[idx++] = city; }
        if (time != null && !"任何時間".equals(time)) { if (where.length()>0) where.append(" AND "); where.append("time = ?"); selectionArgs[idx++] = time; }
        if (type != null && !"任何類型".equals(type)) { if (where.length()>0) where.append(" AND "); where.append("type = ?"); selectionArgs[idx++] = type; }
        Cursor c = qb.query(db, null, where.length()>0?where.toString():null, Arrays.copyOf(selectionArgs, idx), null, null, null);
        // parse cursor
        return list;
    }
}

優勢

• 避免巨大的 CASE WHEN 或 OR 邏輯導致索引失效。
• 參數化查詢防止 SQL 注入。
• 代碼清晰，易於擴展新條件。