08 关系型数据库入门——待办事项持久化移动应用中的数据持久化是核心能力之一。HarmonyOS 提供了kit.ArkData中的关系型数据库RDBAPI支持完整的 SQL 操作。本文将以「柚兔学伴」项目的TodoDatabase.ets为例从数据库初始化到增删改查全面讲解关系型数据库的使用方法。1. 数据库配置与表结构1.1 StoreConfig 配置import{relationalStore}fromkit.ArkData;import{BusinessError}fromkit.BasicServicesKit;constSTORE_CONFIG:relationalStore.StoreConfig{name:TodoDatabase.db,securityLevel:relationalStore.SecurityLevel.S1,encrypt:false};参数说明name数据库文件名存储在应用的沙箱目录下securityLevel安全级别S1低~ S4高S1 适合普通应用数据encrypt是否加密false表示不加密1.2 数据模型接口exportinterfaceTodoItem{id?:number;date?:string;dayOfWeek?:string;time?:string;content?:string;isCompleted?:boolean;}exportinterfaceStatItem{total?:number;completed?:number;uncompleted?:number;}TodoItem待办事项模型id为自增主键isCompleted为布尔值。StatItem统计模型用于展示总任务数、已完成数和未完成数。1.3 建表 SQLconstTABLE_NAMEtodo_items;constCREATE_TABLE_SQLCREATE TABLE IF NOT EXISTS${TABLE_NAME}( id INTEGER PRIMARY KEY AUTOINCREMENT, date TEXT NOT NULL, dayOfWeek TEXT NOT NULL, time TEXT NOT NULL, content TEXT NOT NULL, isCompleted INTEGER NOT NULL DEFAULT 0 );关键点IF NOT EXISTS避免重复建表导致错误。AUTOINCREMENT主键自动递增插入时无需指定id。isCompleted INTEGERSQLite 没有原生的 BOOLEAN 类型使用INTEGER0/1存储布尔值。2. 数据库初始化exportclassTodoDatabase{privatestore:relationalStore.RdbStore|nullnull;privatecontext:Context;constructor(context:Context){this.contextcontext;}asyncinitialize():Promisevoid{try{this.storeawaitrelationalStore.getRdbStore(this.context,STORE_CONFIG);awaitthis.store.executeSql(CREATE_TABLE_SQL);console.info(TodoDatabase initialized successfully);}catch(error){console.error(Failed to initialize TodoDatabase:,error);thrownewError(Failed to initialize TodoDatabase: (errorasBusinessError).message);}}}初始化流程getRdbStore获取或创建数据库实例传入Context和StoreConfig。executeSql执行建表 SQL 语句。错误处理捕获异常并抛出带详细信息的Error。重要所有数据库操作前必须先调用initialize()否则this.store为null后续操作会抛出异常。3. 增addTodoasyncaddTodo(todo:TodoItem):Promisenumber{if(!this.store){thrownewError(Database not initialized);}try{constvalueBucket:relationalStore.ValuesBucket{date:todo.date!!,dayOfWeek:todo.dayOfWeek!!,time:todo.time!!,content:todo.content!!,isCompleted:todo.isCompleted?1:0};constrowIdawaitthis.store.insert(TABLE_NAME,valueBucket);console.info(Todo added successfully, rowId:${rowId});returnrowId;}catch(error){thrownewError(Failed to add todo: (errorasBusinessError).message);}}ValuesBucket键值对容器键为列名值为要插入的数据。布尔值转换todo.isCompleted ? 1 : 0将 JavaScript 的boolean转为 SQLite 的INTEGER。insert执行插入操作返回新行的rowId。4. 查多种查询方式4.1 查询所有待办asyncgetAllTodos():PromiseTodoItem[]{if(!this.store){thrownewError(Database not initialized);}constpredicatesnewrelationalStore.RdbPredicates(TABLE_NAME);predicates.orderByDesc(date).orderByAsc(time);constresultSetawaitthis.store.query(predicates);consttodos:TodoItem[][];if(resultSet.rowCount0){resultSet.goToFirstRow();do{todos.push(this.resultSetToTodoItem(resultSet));}while(resultSet.goToNextRow());}resultSet.close();returntodos;}RdbPredicates是查询谓词构造器支持链式调用orderByDesc(date)按日期降序。orderByAsc(time)按时间升序。ResultSet 遍历模式goToFirstRow() → 读取数据 → goToNextRow() → 读取数据 → ... → 循环结束必须关闭 ResultSet每次查询后调用resultSet.close()释放资源否则会造成内存泄漏。4.2 按条件查询asyncgetTodoById(id:number):PromiseTodoItem|null{constpredicatesnewrelationalStore.RdbPredicates(TABLE_NAME);predicates.equalTo(id,id);constresultSetawaitthis.store.query(predicates);if(resultSet.rowCount0){resultSet.close();returnnull;}resultSet.goToFirstRow();consttodothis.resultSetToTodoItem(resultSet);resultSet.close();returntodo;}equalTo等值条件等价于 SQL 的WHERE id ?。4.3 按日期查询asyncgetTodosByDate(date:string):PromiseTodoItem[]{constpredicatesnewrelationalStore.RdbPredicates(TABLE_NAME);predicates.equalTo(date,date).orderByAsc(time);// ... 遍历 resultSet}4.4 查询未完成待办asyncgetUncompletedTodos():PromiseTodoItem[]{constpredicatesnewrelationalStore.RdbPredicates(TABLE_NAME);predicates.equalTo(isCompleted,0).orderByDesc(date).orderByAsc(time);// ... 遍历 resultSet}注意查询条件使用0而非false因为数据库中布尔值存储为INTEGER。4.5 模糊搜索asyncsearchTodos(keyword:string):PromiseTodoItem[]{constpredicatesnewrelationalStore.RdbPredicates(TABLE_NAME);predicates.like(content,%${keyword}%).orderByDesc(date).orderByAsc(time);// ... 遍历 resultSet}like模糊匹配%keyword%等价于 SQL 的WHERE content LIKE %keyword%。5. 改updateTodo 与 toggleTodoCompletion5.1 动态更新asyncupdateTodo(id:number,todo:TodoItem):Promiseboolean{if(!this.store){thrownewError(Database not initialized);}constvalueBucket:relationalStore.ValuesBucket{};if(todo.date!undefined)valueBucket.datetodo.date;if(todo.dayOfWeek!undefined)valueBucket.dayOfWeektodo.dayOfWeek;if(todo.time!undefined)valueBucket.timetodo.time;if(todo.content!undefined)valueBucket.contenttodo.content;if(todo.isCompleted!undefined)valueBucket.isCompletedtodo.isCompleted?1:0;constpredicatesnewrelationalStore.RdbPredicates(TABLE_NAME);predicates.equalTo(id,id);constrowsAffectedawaitthis.store.update(valueBucket,predicates);returnrowsAffected0;}动态 ValuesBucket只设置需要更新的字段undefined检查未设置的字段不会被更新。这样实现部分更新PATCH 语义而非全量覆盖。5.2 切换完成状态asynctoggleTodoCompletion(id:number):Promiseboolean{consttodoawaitthis.getTodoById(id);if(!todo){returnfalse;}returnawaitthis.updateTodo(id,{isCompleted:!todo.isCompleted});}先读取当前状态取反后更新——这是「读取-修改-写入」模式的典型实现。6. 删单条删除与批量删除6.1 单条删除asyncdeleteTodo(id:number):Promiseboolean{if(!this.store){thrownewError(Database not initialized);}constpredicatesnewrelationalStore.RdbPredicates(TABLE_NAME);predicates.equalTo(id,id);constrowsAffectedawaitthis.store.delete(predicates);returnrowsAffected0;}6.2 批量删除已完成项asyncdeleteCompletedTodos():Promisenumber{if(!this.store){thrownewError(Database not initialized);}constpredicatesnewrelationalStore.RdbPredicates(TABLE_NAME);predicates.equalTo(isCompleted,1);constrowsAffectedawaitthis.store.delete(predicates);returnrowsAffected;}返回受影响的行数UI 层可以据此显示「已清除 N 条已完成待办」。7. 统计查询COUNT 聚合asyncgetStatistics():PromiseStatItem{if(!this.store){thrownewError(Database not initialized);}// 总数consttotalPredicatesnewrelationalStore.RdbPredicates(TABLE_NAME);consttotalResultawaitthis.store.query(totalPredicates,[COUNT(*) as count]);totalResult.goToFirstRow();consttotaltotalResult.getLong(0);totalResult.close();// 已完成数constcompletedPredicatesnewrelationalStore.RdbPredicates(TABLE_NAME);completedPredicates.equalTo(isCompleted,1);constcompletedResultawaitthis.store.query(completedPredicates,[COUNT(*) as count]);completedResult.goToFirstRow();constcompletedcompletedResult.getLong(0);completedResult.close();return{total,completed,uncompleted:total-completed};}query(predicates, columns)第二个参数指定查询列[COUNT(*) as count]执行聚合查询。getLong(0)按列索引读取整数值0对应查询结果的第一列。两次查询分别获取总数和已完成数未完成数通过计算得出。8. ResultSet 转对象resultSetToTodoItemprivateresultSetToTodoItem(resultSet:relationalStore.ResultSet):TodoItem{return{id:resultSet.getLong(resultSet.getColumnIndex(id)),date:resultSet.getString(resultSet.getColumnIndex(date)),dayOfWeek:resultSet.getString(resultSet.getColumnIndex(dayOfWeek)),time:resultSet.getString(resultSet.getColumnIndex(time)),content:resultSet.getString(resultSet.getColumnIndex(content)),isCompleted:resultSet.getLong(resultSet.getColumnIndex(isCompleted))1};}这是数据库行到业务对象的映射方法getColumnIndex根据列名获取列索引解耦列顺序依赖。getLong/getString按类型读取列值。布尔值转换getLong(...) 1将INTEGER还原为boolean。9. 关闭数据库asyncclose():Promisevoid{if(this.store){awaitthis.store.close();this.storenull;console.info(TodoDatabase closed);}}在应用退出或不再需要数据库时调用释放资源。10. 布尔值存储技巧总结由于 SQLite 不支持BOOLEAN类型ArkData RDB 中使用INTEGER存储布尔值业务层数据库写入读取true1isCompleted ? 1 : 0getLong(...) 1false0isCompleted ? 1 : 0getLong(...) 1这种模式在关系型数据库开发中非常常见务必牢记。11. CRUD 方法速查表方法SQL 等价返回值initialize()getRdbStoreCREATE TABLEvoidaddTodo()INSERT INTOrowId: numbergetAllTodos()SELECT * ORDER BYTodoItem[]getTodoById()SELECT * WHERE id ?TodoItem | nullgetTodosByDate()SELECT * WHERE date ?TodoItem[]getUncompletedTodos()SELECT * WHERE isCompleted 0TodoItem[]updateTodo()UPDATE SET ... WHERE id ?booleantoggleTodoCompletion()SELECT→UPDATEbooleandeleteTodo()DELETE WHERE id ?booleandeleteCompletedTodos()DELETE WHERE isCompleted 1numbersearchTodos()SELECT * WHERE content LIKE ?TodoItem[]getStatistics()SELECT COUNT(*)StatItemclose()store.close()void12. 总结知识点说明kit.ArkData关系型数据库 KitStoreConfig数据库配置文件名、安全级别、加密getRdbStore获取数据库实例executeSql执行原生 SQLValuesBucket键值对用于 INSERT/UPDATERdbPredicates查询谓词equalTo、like、orderByResultSet查询结果集goToFirstRow、goToNextRow、getLong、getStringgetColumnIndex按列名取索引解耦列顺序INTEGER 0/1SQLite 布尔值存储方案resultSet.close()释放结果集资源关系型数据库是移动端最可靠的数据持久化方案之一。掌握RdbPredicates的链式查询、ValuesBucket的动态构建和ResultSet的安全遍历就能应对绝大多数本地存储需求。