database---query中selection用法和selectionArgs用法

本文介绍了一种使用SQL进行数据库查询的方法,特别关注了如何通过构造特定的条件语句来精确地定位到所需的数据记录。
public void doQuery(long id, final String name) {  
  mDb.query("some_table", // table name   
        null, // columns  
        "id=" + id + " AND name=?", // selection  
        new String[] {name}, //selectionArgs  
         //...... 更多参数省略  
  );  
  // ...... 更多代码  
} 

 

public void doQuery(long id, final String name) {  
  mDb.query("some_table", // table name   
        null, // columns  
        "id=" + id + " AND name=?", // selection  
        new String[] {name}, //selectionArgs  
         //...... 更多参数省略  
  );  
  // ...... 更多代码  
} 

把以下Android代码翻译成Java版本:class DatabaseProvider : ContentProvider() { private val bookDir = 0 private val bookItem = 1 private val categoryDir = 2 private val categoryItem = 3 private val authority = "com.example.databasetest.provider" private var dbHelper: MyDatabaseHelper? = null private val uriMatcher by lazy { val matcher = UriMatcher(UriMatcher.NO_MATCH) matcher.addURI(authority, "book", bookDir) matcher.addURI(authority, "book/#", bookItem) matcher.addURI(authority, "category", categoryDir) matcher.addURI(authority, "category/#", categoryItem) matcher } override fun onCreate() = context?.let { dbHelper = MyDatabaseHelper(it, "BookStore.db", 2) true } ?: false www.blogss.cn override fun query(uri: Uri, projection: Array<String>?, selection: String?, selectionArgs: Array<String>?, sortOrder: String?) = dbHelper?.let { // 查询数据 val db = it.readableDatabase val cursor = when (uriMatcher.match(uri)) { bookDir -> db.query("Book", projection, selection, selectionArgs, null, null, sortOrder) bookItem -> { val bookId = uri.pathSegments[1] db.query("Book", projection, "id = ?", arrayOf(bookId), null, null, sortOrder) } categoryDir -> db.query("Category", projection, selection, selectionArgs, null, null, sortOrder) categoryItem -> { val categoryId = uri.pathSegments[1] db.query("Category", projection, "id = ?", arrayOf(categoryId), null, null, sortOrder) } else -> null } cursor } override fun insert(uri: Uri, values: ContentValues?) = dbHelper?.let { // 添加数据 val db = it.writableDatabase val uriReturn = when (uriMatcher.match(uri)) { bookDir, bookItem -> { val newBookId = db.insert("Book", null, values) Uri.parse("content://$authority/book/$newBookId") } categoryDir, categoryItem -> { val newCategoryId = db.insert("Category", null, values) Uri.parse("content://$authority/category/$newCategoryId") } else -> null } uriReturn } override fun update(uri: Uri, values: ContentValues?, selection: String?, selectionArgs: Array<String>?) = dbHelper?.let { // 更新数据 val db = it.writableDatabase val updatedRows = when (uriMatcher.match(uri)) { bookDir -> db.update("Book", values, selection, selectionArgs) bookItem -> { val bookId = uri.pathSegments[1] db.update("Book", values, "id = ?", arrayOf(bookId)) } categoryDir -> db.update("Category", values, selection, selectionArgs) categoryItem -> { val categoryId = uri.pathSegments[1] db.update("Category", values, "id = ?", arrayOf(categoryId)) } else -> 0 } updatedRows } ?: 0 override fun delete(uri: Uri, selection: String?, selectionArgs: Array<String>?) = dbHelper?.let { // 删除数据 www.blogss.cn val db = it.writableDatabase val deletedRows = when (uriMatcher.match(uri)) { bookDir -> db.delete("Book", selection, selectionArgs) bookItem -> { val bookId = uri.pathSegments[1] db.delete("Book", "id = ?", arrayOf(bookId)) } categoryDir -> db.delete("Category", selection, selectionArgs) categoryItem -> { val categoryId = uri.pathSegments[1] db.delete("Category", "id = ?", arrayOf(categoryId)) } else -> 0 } deletedRows } ?: 0 override fun getType(uri: Uri) = when (uriMatcher.match(uri)) { bookDir -> "vnd.android.cursor.dir/vnd.com.example.databasetest.provider.book" bookItem -> "vnd.android.cursor.item/vnd.com.example.databasetest.provider.book" categoryDir -> "vnd.android.cursor.dir/vnd.com.example.databasetest. provider.category" categoryItem -> "vnd.android.cursor.item/vnd.com.example.databasetest. provider.category" else -> null } }
07-22
<think>我们之前提供的DatabaseManager类中,query方法已经支持传入条件(selectionselectionArgs),但排序参数确实没有加入。现在需要扩展查询方法以支持排序其他常用参数。 修改后的query方法应该支持以下参数: - table: 表名 - columns: 要查询的列 - selection: WHERE条件 - selectionArgs: WHERE条件的参数 - groupBy: GROUP BY子句 - having: HAVING条件 - orderBy: 排序字段方式(如 "name ASC") - limit: 限制返回行数 同时,为了保持灵活性,我们还可以增加一个offset参数用于分页,但通常limitoffset可以合并为一个字符串(如"10 OFFSET 20"),或者单独传递。 由于Android的SQLiteDatabase.query方法已经提供了这些参数,我们可以直接扩展我们的方法。 修改后的query方法如下:</think>关于在 `database.query()` 中处理查询条件排序,以下是完整的实现方案最佳实践: ### 增强的查询方法实现 ```kotlin // 扩展查询方法,支持完整参数 fun query( table: String, columns: Array<String>? = null, selection: String? = null, selectionArgs: Array<String>? = null, groupBy: String? = null, having: String? = null, orderBy: String? = null, limit: String? = null, callback: (Cursor) -> Unit ) { executor.execute { database.query( table, columns, selection, selectionArgs, groupBy, having, orderBy, limit ).use { cursor -> if (cursor.moveToFirst()) { do { callback(cursor) } while (cursor.moveToNext()) } } } } // 使用示例:带条件排序的查询 dbManager.query( table = "user_data", columns = arrayOf("name", "email", "created_at"), selection = "created_at > ? AND name LIKE ?", selectionArgs = arrayOf("1680000000", "%John%"), orderBy = "created_at DESC, name ASC", limit = "20" ) { cursor -> // 处理结果 } ``` ### 关键参数详解 1. **条件参数 (WHERE 子句)**: ```kotlin selection = "age > ? AND department = ?" selectionArgs = arrayOf("30", "Engineering") ``` - 使用 `?` 占位符防止 SQL 注入 - 参数按顺序对应 `selectionArgs` 数组 2. **排序参数 (ORDER BY)**: ```kotlin // 单字段排序 orderBy = "created_at DESC" // 多字段排序 orderBy = "last_name ASC, first_name ASC" // 随机排序 orderBy = "RANDOM()" ``` 3. **高级查询参数**: | 参数 | 用途 | 示例 | |------|------|------| | `groupBy` | 分组查询 | `"department"` | | `having` | 分组条件 | `"COUNT(*) > 5"` | | `limit` | 结果限制 | `"10"` 或 `"20 OFFSET 40"` | ### 最佳实践建议 1. **条件构建技巧**: ```kotlin // 动态构建安全查询 val conditions = mutableListOf<String>() val args = mutableListOf<String>() if (minAge > 0) { conditions.add("age >= ?") args.add(minAge.toString()) } if (!department.isNullOrEmpty()) { conditions.add("department = ?") args.add(department) } val selection = conditions.joinToString(" AND ") val selectionArgs = args.toTypedArray() ``` 2. **排序优化**: - 为排序字段创建索引: ```sql CREATE INDEX idx_users_created ON user_data(created_at DESC) ``` - 避免排序大结果集,使用 `LIMIT` 分页: ```kotlin orderBy = "created_at DESC" limit = "20 OFFSET ${page * 20}" ``` 3. **空值处理**: ```kotlin // 处理可能为空的排序字段 orderBy = "CASE WHEN last_seen IS NULL THEN 1 ELSE 0 END, last_seen DESC" ``` ### 完整查询示例 ```kotlin // 查询销售部门业绩前10的员工 dbManager.query( table = "employees", columns = arrayOf("name", "SUM(sales) AS total_sales"), selection = "department = ? AND hire_date > ?", selectionArgs = arrayOf("Sales", "2023-01-01"), groupBy = "name", having = "total_sales > 10000", orderBy = "total_sales DESC", limit = "10" ) { cursor -> val name = cursor.getString(0) val sales = cursor.getDouble(1) println("$name: $sales") } ``` ### 性能注意事项 1. **索引使用**: - WHERE ORDER BY 中的字段应建立索引 - 多字段排序时创建复合索引(如 `(last_name, first_name)`) 2. **结果集处理**: - 使用 `cursor.getColumnIndex()` 替代硬编码位置 - 及时关闭 Cursor(通过 `use{}` 块自动关闭) 3. **分页优化**: ```kotlin // 更高效的分页(使用ROWID) val lastId = ... // 上一页最后一项的ID selection = "id > ?" selectionArgs = arrayOf(lastId.toString()) orderBy = "id ASC" limit = "20" ```
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值