场景描述
关系型数据库(Relational Database,RDB)是一种基于关系模型来管理数据的数据库。关系型数据库基于SQLite组件提供了一套完整的对本地数据库进行管理的机制,对外提供了一系列的增、删、改、查等接口,也可以直接运行用户输入的SQL语句来满足复杂的场景需要。
场景一:基于RDB已提供API的数据库基础使用
场景二:基于executeSql、querySql执行增删改查复杂SQL语句
场景三:事务的使用
场景四:批量插入数据的不同实现方式及性能对比
场景五:数据库备份与恢复
场景六:全文检索(FTS)使用思路
方案描述
场景一:基于RDB已提供API的数据库基础使用
方案
通过insert、update、delete、query接口及关系型数据库谓词predicates的数据库基础操作。RdbPredicates:数据库中用来代表数据实体的性质、特征或者数据实体之间关系的词项,主要用来定义数据库的操作条件。常用的方法有equalTo,notEqualTo,or,and,isnull,between,orderby,groupby等。
核心代码
文中的操作主要基于如下的数据库,详细数据库使用步骤可参考:关系型数据库使用指南。
//创建数据库及相关表 export default class Rdb{ rdbStore?: relationalStore.RdbStore; context:Context = getContext(); constructor(storeName: string) { // 数据库配置 const STORE_CONFIG:relationalStore.StoreConfig = { name: storeName, securityLevel: relationalStore.SecurityLevel.S1, }; // 获取数据库Store relationalStore.getRdbStore(this.context, STORE_CONFIG, (err: BusinessError, rdbStore: relationalStore.RdbStore) => { this.rdbStore = rdbStore; if (err) { console.error(`Get RdbStore failed, code is ${err.code},message is ${err.message}`); return; } console.info(`Get ${storeName} RdbStore successfully.`); }) } CreateTable() { //建表语句 const SQL_CREATE_TABLE = 'CREATE TABLE IF NOT EXISTS STUDENT (ID INTEGER PRIMARY KEY AUTOINCREMENT,NAME TEXT,AGE INTEGER,SALARY REAL)'; if(this.rdbStore){ this.rdbStore.executeSql(SQL_CREATE_TABLE); console.info(`CreateTable successfully.`); } } }
通过ValuesBucket构建数据,insert接口插入。
InsertData(name:string,age:number,salary:number) { // 插入数据 const valueBucket: ValuesBucket = { 'NAME': name, 'AGE': age, 'SALARY': salary }; if(this.rdbStore){ this.rdbStore.insert('STUDENT', valueBucket, (err, rowId) => { if (err) { console.error(`Failed to insert data. Code:${err.code}, message:${err.message}`); return; } console.info(`Succeeded in inserting data. rowId:${rowId}`); }) } }
通过predicates构建删除条件,删除name为Lisa的数据,delete接口执行删除。
DeleteData() { let predicates = new relationalStore.RdbPredicates("STUDENT"); predicates.equalTo("NAME", "Lisa"); if(this.rdbStore != undefined) { (this.rdbStore as relationalStore.RdbStore).delete(predicates, (err, rows) => { if (err) { console.error(`Delete failed, code is ${err.code},message is ${err.message}`); return; } console.info(`Delete rows: ${rows}`); }) } }
通过update接口,根据predicates构造的条件,及valueBucket传入的数据修改数据。
UpdateData(name:string,age:number,salary:number) { const valueBucket: ValuesBucket = { 'NAME': name, 'AGE': age, 'SALARY': salary }; let predicates = new relationalStore.RdbPredicates("EMPLOYEE"); predicates.equalTo("NAME", "Lisa"); if(this.rdbStore != undefined) { (this.rdbStore as relationalStore.RdbStore).update(valueBucket, predicates, relationalStore.ConflictResolution.ON_CONFLICT_REPLACE, (err, rows) => { if (err) { console.error(`Updated failed, code is ${err.code},message is ${err.message}`); return; } console.info(`Updated row count: ${rows}`); }) } }
通过query接口查询数据,返回resultSet结果集,并解析查询到的数据。
QueryData(callback:Callback<string>) { // 配置谓词 let predicates = new relationalStore.RdbPredicates("STUDENT"); let jsonData:Array<ValuesBucket> = new Array<ValuesBucket>(); if(this.rdbStore){ this.rdbStore.query(predicates, ["ID", 'NAME', 'AGE', 'SALARY'], (err, resultSet) => { if (err) { console.error(`Failed to query data. Code:${err.code}, message:${err.message}`); return; } console.info(`ResultSet column names: ${resultSet.columnNames}, row count: ${resultSet.rowCount}`); if(resultSet.rowCount == -1){ console.info("rowCount=-1") } // resultSet是一个数据集合的游标,默认指向第-1个记录,有效的数据从0开始。 while (resultSet.goToNextRow()) { const id = resultSet.getLong(resultSet.getColumnIndex("ID")); const name = resultSet.getString(resultSet.getColumnIndex("NAME")); const age = resultSet.getLong(resultSet.getColumnIndex("AGE")); const salary = resultSet.getDouble(resultSet.getColumnIndex("SALARY")); console.info(`id=${id}, name=${name}, age=${age}, salary=${salary}`); const valueBucket: ValuesBucket = { 'ID':id, 'NAME': name, 'AGE': age, 'SALARY': salary }; jsonData.push(valueBucket) } // 释放数据集的内存 resultSet.close(); // console.info("JSON: " + JSON.stringify(jsonData)) callback(JSON.stringify(jsonData)) }) } }
向数据库添加数据
场景二:基于executeSql、querySql执行增删改查复杂SQL语句
方案
在实际使用过程中,复杂的SQL语句可能无法直接通过场景一提供的方式实现,此时需要开发者通过executeSql、querySql接口执行自定义SQL语句。executeSql能够执行包含指定参数但不返回值的SQL语句,如创建表、创建索引、数据库触发器等场景。querySql能够根据指定SQL语句查询数据库中的数据,并返回查询结果ResultSet结果集。如递归查询、子查询等场景。
核心代码
//创建表 const SQL_CREATE_TABLE = 'CREATE TABLE IF NOT EXISTS EMPLOYEE (ID INTEGER PRIMARY KEY AUTOINCREMENT,NAME TEXT NOT NULL,AGE INTEGER,SALARY REAL)'; //创建索引 const CTEATE_INDEX ='CREATE INDEX idx_name ON EMPLOYEE (NAME)' this.rdbStore.executeSql(SQL_CREATE_TABLE); this.rdbStore.executeSql(CTEATE_INDEX);
//数据库触发器 const TRIGGER_SQL2 = "CREATE TRIGGER test AFTER INSERT ON EMPLOYEE" + " BEGIN" + " UPDATE EMPLOYEE SET SALARY = '123456' WHERE ID = 1;" + " END;"
//子查询 const SubQuery_SQL = "SELECT * FROM STUDENT WHERE ID IN (SELECT ID FROM STUDENT WHERE NAME = 'John');"
//联合查询 const JoinQuerySQL ="SELECT student.name, courses.name, courses.credits FROM students JOIN courses ON students.id = courses.student_id;"
对于上述的复杂SQL语句,就可以使用executeSql、querySql接口执行,。
myExecuteSql(sql:string) { if(this.rdbStore != undefined) { (this.rdbStore as relationalStore.RdbStore).executeSql(sql, (err) => { if (err) { console.error(`ExecuteSql failed, code is ${err.code},message is ${err.message}`); return; } console.info('ExecuteSql success'); }) } } myQuerySql(sql:string) { if(this.rdbStore){ (this.rdbStore as relationalStore.RdbStore).querySql(sql, (err, resultSet) => { if (err) { console.error(`Query failed, code is ${err.code},message is ${err.message}`); return; } console.info(`ResultSet column names: ${resultSet.columnNames}, column count: ${resultSet.columnCount}`); // resultSet是一个数据集合的游标,默认指向第-1个记录,有效的数据从0开始。 while (resultSet.goToNextRow()) { const name = resultSet.getString(resultSet.getColumnIndex("NAME")); const age = resultSet.getLong(resultSet.getColumnIndex("AGE")); console.info(`name=${name}, age=${age}`); } // 释放数据集的内存 resultSet.close(); }) } }
场景三:事务的使用
方案
数据库事务可以保证指一组数据库操作要么全部执行成功,要么全部回滚。鸿蒙关系型数据库提供了事务相关接口 beginTransaction、commit、rollBack。本例通过模拟一组操作中,有一条操作失败后,回滚已经执行的SQL语句。
核心代码
// 插入数据 if (this.rdbStore != undefined) { //构建两条数据,其中第二条数据与表结构不符,会操作失败,数据库会回滚。 const valueBucket: ValuesBucket = { NAME: name, AGE: age, SALARY: salary }; const valueBucket2: ValuesBucket = { NAME: 'scd', SEX:'男', AGE: 18, SALARY: 18888 }; try { //开启事务 this.rdbStore.beginTransaction(); await this.rdbStore.insert('STUDENT', valueBucket) await this.rdbStore.insert('STUDENT', valueBucket2) //提交事务 this.rdbStore.commit(); } catch (err) { //回滚事务 let code = (err as BusinessError).code; let message = (err as BusinessError).message console.error(`Transaction failed, code is ${code},message is ${message}`); this.rdbStore.rollBack() return; } } }
上述代码中第一条数据插入成功,第二条数据与表结构不符,插入时会失败,此时数据库会回滚,第一条插入的数据被撤销,数据库不会发生变化。
场景四:批量插入数据的不同实现方式及性能对比
方案
分别使用insert、事务insert、batchInsert三种方式各自插入5000条数据,统计各自任务耗时
核心代码
直接使用insert插入5000条数据。
InsertDatas(name:string,age:number,salary:number) { for (let i = 1; i <= 5000; i++) { // 插入数据 const valueBucket: ValuesBucket = { 'NAME': name+i, 'AGE': age, 'SALARY': salary }; if(this.rdbStore){ this.rdbStore.insert('STUDENT', valueBucket, (err, rowId) => { if (err) { console.error(`Failed to insert data. Code:${err.code}, message:${err.message}`); return; } console.info(`Succeeded in inserting data. rowId:${rowId}`); }) } } }
在事务中使用insert插入5000条数据
InsertDataByTransaction(name:string,age:number,salary:number){ if (this.rdbStore != undefined) { try { //开启事务 this.rdbStore.beginTransaction(); for (let i = 1; i <= 5000; i++) { // 插入数据 const valueBucket: ValuesBucket = { 'NAME': name+i, 'AGE': age, 'SALARY': salary }; if(this.rdbStore){ this.rdbStore.insert('STUDENT', valueBucket, (err, rowId) => { if (err) { console.error(`Failed to insert data. Code:${err.code}, message:${err.message}`); return; } console.info(`Succeeded in inserting data. rowId:${rowId}`); }) } } //提交事务 this.rdbStore.commit(); } catch (err) { //回滚事务 let code = (err as BusinessError).code; let message = (err as BusinessError).message console.error(`Transaction failed, code is ${code},message is ${message}`); this.rdbStore.rollBack() return; } } }
使用barchInsert插入5000条数据
//批量添加 BatchInsert(name:string,age:number,salary:number){ { if (this.rdbStore == null) { console.error(`Create Store1.db failed! store1 is null`); return; } } let valueBucketArray: Array<relationalStore.ValuesBucket> = new Array(); for (let i=1; i <=5000; i++) { const valueBucket: relationalStore.ValuesBucket = { 'NAME': name+i, 'AGE': age, 'SALARY': salary } valueBucketArray.push(valueBucket); } try { this.rdbStore.batchInsert("STUDENT", valueBucketArray ); // 该接口内部使用事务 console.info(`Insert data successfully!`); } catch (err) { console.error(`Insert datae failed! err code:${err.code}, err message:${err.message}`) } }
对上述三个方法进行耗时统计,根据测试结果,事务中批量添加数据与直接for循环添加数据耗时相差无几,而使用batchInsert时,时间明显更快,且batchInsert接口内部使用了事务,因此建议批量插入场景使用batchInsert。
场景五:数据库备份与恢复
方案
在数据库的使用过程中,数据库可能会因为数据丢失、数据损坏、脏数据等而不可用情况,为了预防这种情况,可以通过backup接口,提前备份数据库数据到本地文件中,当发生意外后,可以通过restore接口,从指定的数据库备份文件恢复数据库。
核心代码
//备份数据库 myBackup(){ if(this.rdbStore){ (this.rdbStore as relationalStore.RdbStore).backup("dbBackup.db", (err) => { if (err) { console.error(`Backup failed, code is ${err.code},message is ${err.message}`); return; } console.info(`Backup success.`); }) } } //恢复数据库 myRestore(backupFileName:string){ // value:备份数据库名 if(this.rdbStore){ (this.rdbStore as relationalStore.RdbStore).restore(backupFileName, (err) => { if (err) { console.error(`Restore failed, code is ${err.code},message is ${err.message}`); return; } console.info(`Restore success.`); }) } }
测试过程中,先通过myBackup备份数据库,数据库文件路径生成数据库备份文件dbBackup.db。
随后使用delete删除name为testA、testB数据后,此时数据库已无相关数据。
再使用myRestore恢复数据库后,数据库恢复到备份时状态。
注:数据库文件路径可通过context.databaseDir获取。如:/data/app/el2/100/database/{bundleName}/entry/rdb
场景六:全文检索(FTS)使用思路
方案
HarmonyOS关系型数据库底层使用的是Sqlite,在Sqite中FTS的核心是倒排索引,它是一种将词汇映射到出现该词汇的文档集合的数据结构。在创建FTS虚拟表时,SQLite会为每个词汇生成一个倒排索引,记录该词汇在哪些文档(即数据库记录)中出现。倒排索引使得全文搜索能够快速找到包含特定词汇的文档,而无需遍历整个数据库。
FTS虚拟表(Full-Text Search Virtual Table)是SQLite中实现全文搜索的一种特殊表结构。它用于存储全文索引数据,包括倒排索引的信息。虽然FTS虚拟表在查询时表现得像普通的SQLite表,但其实现和存储方式与普通表有很大不同。
使用全文检索时,创建FTS表需要使用CREATE VIRTUAL TABLE语句,执行全文搜索需要使用MATCH关键字,当前HarmonyOS关系型数据库并没有直接提供相关接口,但是数据库底层是支持的,因此可以通过executeSql、querySql执行相关SQL语句。
核心代码
//备份数据库 myBackup(){ if(this.rdbStore){ (this.rdbStore as relationalStore.RdbStore).backup("dbBackup.db", (err) => { if (err) { console.error(`Backup failed, code is ${err.code},message is ${err.message}`); return; } console.info(`Backup success.`); }) } } //恢复数据库 myRestore(backupFileName:string){ // value:备份数据库名 if(this.rdbStore){ (this.rdbStore as relationalStore.RdbStore).restore(backupFileName, (err) => { if (err) { console.error(`Restore failed, code is ${err.code},message is ${err.message}`); return; } console.info(`Restore success.`); })//创建虚拟表 const sql1:string = "CREATE VIRTUAL TABLE products_fts USING fts5(NAME , AGE)" this.rdbTest.myExecuteSql(sql1) //虚拟表添加数据 InsertDataToVIRTUAL(){ let sql:string = "INSERT INTO products_fts(NAME , AGE) SELECT NAME,AGE FROM STUDENT" this.myExecuteSql(sql) } //使用全文检索查询数据 let sql:string = "SELECT * FROM products_fts WHERE AGE MATCH 18" this.rdbTest.myQuerySql(sql) myExecuteSql(sql:string) { if(this.rdbStore != undefined) { (this.rdbStore as relationalStore.RdbStore).executeSql(sql, (err) => { if (err) { console.error(`ExecuteSql failed, code is ${err.code},message is ${err.message}`); return; } console.info('ExecuteSql success'); }) } } myQuerySql(sql:string) { if(this.rdbStore){ (this.rdbStore as relationalStore.RdbStore).querySql(sql, (err, resultSet) => { if (err) { console.error(`Query failed, code is ${err.code},message is ${err.message}`); return; } console.info(`ResultSet column names: ${resultSet.columnNames}, column count: ${resultSet.columnCount}`); // resultSet是一个数据集合的游标,默认指向第-1个记录,有效的数据从0开始。 while (resultSet.goToNextRow()) { const name = resultSet.getString(resultSet.getColumnIndex("NAME")); const age = resultSet.getLong(resultSet.getColumnIndex("AGE")); console.info(`name=${name}, age=${age}`); } // 释放数据集的内存 resultSet.close(); }) } } } }
查询虚拟表中AGE"字段匹配关键词"18"的记录,虚拟表查询结果: