一、場(chǎng)景介紹

關(guān)系型數(shù)據(jù)庫基于SQLite組件，適用于存儲(chǔ)包含復(fù)雜關(guān)系數(shù)據(jù)的場(chǎng)景，比如一個(gè)班級(jí)的學(xué)生信息，需要包括姓名、學(xué)號(hào)、各科成績等，又或者公司的雇員信息，需要包括姓名、工號(hào)、職位等，由于數(shù)據(jù)之間有較強(qiáng)的對(duì)應(yīng)關(guān)系，復(fù)雜程度比鍵值型數(shù)據(jù)更高，此時(shí)需要使用關(guān)系型數(shù)據(jù)庫來持久化保存數(shù)據(jù)。

大數(shù)據(jù)量場(chǎng)景下查詢數(shù)據(jù)可能會(huì)導(dǎo)致耗時(shí)長甚至應(yīng)用卡死，如有相關(guān)操作可參考文檔批量數(shù)據(jù)寫數(shù)據(jù)庫場(chǎng)景，且有建議如下：

• 單次查詢數(shù)據(jù)量不超過5000條。
• 在TaskPool中查詢。
• 拼接SQL語句盡量簡(jiǎn)潔。
• 合理地分批次查詢。

二、基本概念

謂詞：數(shù)據(jù)庫中用來代表數(shù)據(jù)實(shí)體的性質(zhì)、特征或者數(shù)據(jù)實(shí)體之間關(guān)系的詞項(xiàng)，主要用來定義數(shù)據(jù)庫的操作條件。

結(jié)果集：指用戶查詢之后的結(jié)果集合，可以對(duì)數(shù)據(jù)進(jìn)行訪問。結(jié)果集提供了靈活的數(shù)據(jù)訪問方式，可以更方便地拿到用戶想要的數(shù)據(jù)。

三、運(yùn)作機(jī)制

關(guān)系型數(shù)據(jù)庫對(duì)應(yīng)用提供通用的操作接口，底層使用SQLite作為持久化存儲(chǔ)引擎，支持SQLite具有的數(shù)據(jù)庫特性，包括但不限于事務(wù)、索引、視圖、觸發(fā)器、外鍵、參數(shù)化查詢和預(yù)編譯SQL語句。

關(guān)系型數(shù)據(jù)庫運(yùn)作機(jī)制

約束限制

• 系統(tǒng)默認(rèn)日志方式是WAL（Write Ahead Log）模式，系統(tǒng)默認(rèn)落盤方式是FULL模式。
• 數(shù)據(jù)庫中有4個(gè)讀連接和1個(gè)寫連接，線程獲取到空閑讀連接時(shí)，即可進(jìn)行讀取操作。當(dāng)沒有空閑讀連接且有空閑寫連接時(shí)，會(huì)將寫連接當(dāng)做讀連接來使用。
• 為保證數(shù)據(jù)的準(zhǔn)確性，數(shù)據(jù)庫同一時(shí)間只能支持一個(gè)寫操作。
• 當(dāng)應(yīng)用被卸載完成后，設(shè)備上的相關(guān)數(shù)據(jù)庫文件及臨時(shí)文件會(huì)被自動(dòng)清除。
• ArkTS側(cè)支持的基本數(shù)據(jù)類型：number、string、二進(jìn)制類型數(shù)據(jù)、boolean。
• 為保證插入并讀取數(shù)據(jù)成功，建議一條數(shù)據(jù)不要超過2M。超出該大小，插入成功，讀取失敗。

四、接口說明

以下是關(guān)系型數(shù)據(jù)庫持久化功能的相關(guān)接口，大部分為異步接口。異步接口均有callback和Promise兩種返回形式，下表均以callback形式為例，更多接口及使用方式請(qǐng)見關(guān)系型數(shù)據(jù)庫。

接口名稱	描述
getRdbStore(context: Context, config: StoreConfig, callback: AsyncCallback): void	獲得一個(gè)RdbStore，操作關(guān)系型數(shù)據(jù)庫，用戶可以根據(jù)自己的需求配置RdbStore的參數(shù)，然后通過RdbStore調(diào)用相關(guān)接口可以執(zhí)行相關(guān)的數(shù)據(jù)操作。
executeSql(sql: string, bindArgs: Array, callback: AsyncCallback):void	執(zhí)行包含指定參數(shù)但不返回值的SQL語句。
insert(table: string, values: ValuesBucket, callback: AsyncCallback):void	向目標(biāo)表中插入一行數(shù)據(jù)。
update(values: ValuesBucket, predicates: RdbPredicates, callback: AsyncCallback):void	根據(jù)predicates的指定實(shí)例對(duì)象更新數(shù)據(jù)庫中的數(shù)據(jù)。
delete(predicates: RdbPredicates, callback: AsyncCallback):void	根據(jù)predicates的指定實(shí)例對(duì)象從數(shù)據(jù)庫中刪除數(shù)據(jù)。
query(predicates: RdbPredicates, columns: Array, callback: AsyncCallback):void	根據(jù)指定條件查詢數(shù)據(jù)庫中的數(shù)據(jù)。
deleteRdbStore(context: Context, name: string, callback: AsyncCallback): void	刪除數(shù)據(jù)庫。

五、開發(fā)步驟

因Stage模型、FA模型的差異，個(gè)別示例代碼提供了在兩種模型下的對(duì)應(yīng)示例；示例代碼未區(qū)分模型或沒有對(duì)應(yīng)注釋說明時(shí)默認(rèn)在兩種模型下均適用。

關(guān)系庫數(shù)據(jù)庫操作或者存儲(chǔ)過程中，有可能會(huì)因?yàn)楦鞣N原因發(fā)生非預(yù)期的數(shù)據(jù)庫損壞（拋出14800011）異常情況，此時(shí)需要對(duì)數(shù)據(jù)庫進(jìn)行重建并恢復(fù)數(shù)據(jù)，以保障正常的應(yīng)用開發(fā)，具體可見關(guān)系型數(shù)據(jù)庫損壞重建。

1.使用關(guān)系型數(shù)據(jù)庫實(shí)現(xiàn)數(shù)據(jù)持久化，需要獲取一個(gè)RdbStore，其中包括建庫、建表、升降級(jí)等操作。示例代碼如下所示：

Stage模型示例：

import { relationalStore } from '@kit.ArkData'; // 導(dǎo)入模塊
import { UIAbility } from '@kit.AbilityKit';
import { BusinessError } from '@kit.BasicServicesKit';
import { window } from '@kit.ArkUI';// 此處示例在Ability中實(shí)現(xiàn)，使用者也可以在其他合理場(chǎng)景中使用
class EntryAbility extends UIAbility {onWindowStageCreate(windowStage: window.WindowStage) {const STORE_CONFIG :relationalStore.StoreConfig= {name: 'RdbTest.db', // 數(shù)據(jù)庫文件名securityLevel: relationalStore.SecurityLevel.S3, // 數(shù)據(jù)庫安全級(jí)別encrypt: false, // 可選參數(shù)，指定數(shù)據(jù)庫是否加密，默認(rèn)不加密customDir: 'customDir/subCustomDir', // 可選參數(shù)，數(shù)據(jù)庫自定義路徑。數(shù)據(jù)庫將在如下的目錄結(jié)構(gòu)中被創(chuàng)建：context.databaseDir + '/rdb/' + customDir，其中context.databaseDir是應(yīng)用沙箱對(duì)應(yīng)的路徑，'/rdb/'表示創(chuàng)建的是關(guān)系型數(shù)據(jù)庫，customDir表示自定義的路徑。當(dāng)此參數(shù)不填時(shí)，默認(rèn)在本應(yīng)用沙箱目錄下創(chuàng)建RdbStore實(shí)例。isReadOnly: false // 可選參數(shù)，指定數(shù)據(jù)庫是否以只讀方式打開。該參數(shù)默認(rèn)為false，表示數(shù)據(jù)庫可讀可寫。該參數(shù)為true時(shí)，只允許從數(shù)據(jù)庫讀取數(shù)據(jù)，不允許對(duì)數(shù)據(jù)庫進(jìn)行寫操作，否則會(huì)返回錯(cuò)誤碼801。};// 判斷數(shù)據(jù)庫版本，如果不匹配則需進(jìn)行升降級(jí)操作// 假設(shè)當(dāng)前數(shù)據(jù)庫版本為3，表結(jié)構(gòu)：EMPLOYEE (NAME, AGE, SALARY, CODES, IDENTITY)const SQL_CREATE_TABLE = 'CREATE TABLE IF NOT EXISTS EMPLOYEE (ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT NOT NULL, AGE INTEGER, SALARY REAL, CODES BLOB, IDENTITY UNLIMITED INT)'; // 建表Sql語句, IDENTITY為bigint類型，sql中指定類型為UNLIMITED INTrelationalStore.getRdbStore(this.context, STORE_CONFIG, (err, store) => {if (err) {console.error(`Failed to get RdbStore. Code:${err.code}, message:${err.message}`);return;}console.info('Succeeded in getting RdbStore.');// 當(dāng)數(shù)據(jù)庫創(chuàng)建時(shí)，數(shù)據(jù)庫默認(rèn)版本為0if (store.version === 0) {store.executeSql(SQL_CREATE_TABLE); // 創(chuàng)建數(shù)據(jù)表// 設(shè)置數(shù)據(jù)庫的版本，入?yún)榇笥?的整數(shù)store.version = 3;}// 如果數(shù)據(jù)庫版本不為0且和當(dāng)前數(shù)據(jù)庫版本不匹配，需要進(jìn)行升降級(jí)操作// 當(dāng)數(shù)據(jù)庫存在并假定版本為1時(shí)，例應(yīng)用從某一版本升級(jí)到當(dāng)前版本，數(shù)據(jù)庫需要從1版本升級(jí)到2版本if (store.version === 1) {// version = 1：表結(jié)構(gòu)：EMPLOYEE (NAME, SALARY, CODES, ADDRESS) => version = 2：表結(jié)構(gòu)：EMPLOYEE (NAME, AGE, SALARY, CODES, ADDRESS)(store as relationalStore.RdbStore).executeSql('ALTER TABLE EMPLOYEE ADD COLUMN AGE INTEGER');store.version = 2;}// 當(dāng)數(shù)據(jù)庫存在并假定版本為2時(shí)，例應(yīng)用從某一版本升級(jí)到當(dāng)前版本，數(shù)據(jù)庫需要從2版本升級(jí)到3版本if (store.version === 2) {// version = 2：表結(jié)構(gòu)：EMPLOYEE (NAME, AGE, SALARY, CODES, ADDRESS) => version = 3：表結(jié)構(gòu)：EMPLOYEE (NAME, AGE, SALARY, CODES)(store as relationalStore.RdbStore).executeSql('ALTER TABLE EMPLOYEE DROP COLUMN ADDRESS TEXT');store.version = 3;}});// 請(qǐng)確保獲取到RdbStore實(shí)例后，再進(jìn)行數(shù)據(jù)庫的增、刪、改、查等操作}
}

FA模型示例：

import { relationalStore } from '@kit.ArkData'; // 導(dǎo)入模塊
import { featureAbility } from '@kit.AbilityKit';
import { BusinessError } from '@kit.BasicServicesKit';let context = featureAbility.getContext();const STORE_CONFIG :relationalStore.StoreConfig = {name: 'RdbTest.db', // 數(shù)據(jù)庫文件名securityLevel: relationalStore.SecurityLevel.S3 // 數(shù)據(jù)庫安全級(jí)別
};// 假設(shè)當(dāng)前數(shù)據(jù)庫版本為3，表結(jié)構(gòu)：EMPLOYEE (NAME, AGE, SALARY, CODES, IDENTITY)
const SQL_CREATE_TABLE = 'CREATE TABLE IF NOT EXISTS EMPLOYEE (ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT NOT NULL, AGE INTEGER, SALARY REAL, CODES BLOB, IDENTITY UNLIMITED INT)'; // 建表Sql語句，IDENTITY為bigint類型，sql中指定類型為UNLIMITED INTrelationalStore.getRdbStore(context, STORE_CONFIG, (err, store) => {if (err) {console.error(`Failed to get RdbStore. Code:${err.code}, message:${err.message}`);return;}console.info('Succeeded in getting RdbStore.');// 當(dāng)數(shù)據(jù)庫創(chuàng)建時(shí)，數(shù)據(jù)庫默認(rèn)版本為0if (store.version === 0) {store.executeSql(SQL_CREATE_TABLE); // 創(chuàng)建數(shù)據(jù)表// 設(shè)置數(shù)據(jù)庫的版本，入?yún)榇笥?的整數(shù)store.version = 3;}// 如果數(shù)據(jù)庫版本不為0且和當(dāng)前數(shù)據(jù)庫版本不匹配，需要進(jìn)行升降級(jí)操作// 當(dāng)數(shù)據(jù)庫存在并假定版本為1時(shí)，例應(yīng)用從某一版本升級(jí)到當(dāng)前版本，數(shù)據(jù)庫需要從1版本升級(jí)到2版本if (store.version === 1) {// version = 1：表結(jié)構(gòu)：EMPLOYEE (NAME, SALARY, CODES, ADDRESS) => version = 2：表結(jié)構(gòu)：EMPLOYEE (NAME, AGE, SALARY, CODES, ADDRESS)store.executeSql('ALTER TABLE EMPLOYEE ADD COLUMN AGE INTEGER');store.version = 2;}// 當(dāng)數(shù)據(jù)庫存在并假定版本為2時(shí)，例應(yīng)用從某一版本升級(jí)到當(dāng)前版本，數(shù)據(jù)庫需要從2版本升級(jí)到3版本if (store.version === 2) {// version = 2：表結(jié)構(gòu)：EMPLOYEE (NAME, AGE, SALARY, CODES, ADDRESS) => version = 3：表結(jié)構(gòu)：EMPLOYEE (NAME, AGE, SALARY, CODES)store.executeSql('ALTER TABLE EMPLOYEE DROP COLUMN ADDRESS TEXT');store.version = 3;}
});// 請(qǐng)確保獲取到RdbStore實(shí)例后，再進(jìn)行數(shù)據(jù)庫的增、刪、改、查等操作

說明

• 應(yīng)用創(chuàng)建的數(shù)據(jù)庫與其上下文（Context）有關(guān)，即使使用同樣的數(shù)據(jù)庫名稱，但不同的應(yīng)用上下文，會(huì)產(chǎn)生多個(gè)數(shù)據(jù)庫，例如每個(gè)UIAbility都有各自的上下文。
• 當(dāng)應(yīng)用首次獲取數(shù)據(jù)庫（調(diào)用getRdbStore）后，在應(yīng)用沙箱內(nèi)會(huì)產(chǎn)生對(duì)應(yīng)的數(shù)據(jù)庫文件。使用數(shù)據(jù)庫的過程中，在與數(shù)據(jù)庫文件相同的目錄下可能會(huì)產(chǎn)生以-wal和-shm結(jié)尾的臨時(shí)文件。此時(shí)若開發(fā)者希望移動(dòng)數(shù)據(jù)庫文件到其它地方使用查看，則需要同時(shí)移動(dòng)這些臨時(shí)文件，當(dāng)應(yīng)用被卸載完成后，其在設(shè)備上產(chǎn)生的數(shù)據(jù)庫文件及臨時(shí)文件也會(huì)被移除。
• 錯(cuò)誤碼的詳細(xì)介紹請(qǐng)參見通用錯(cuò)誤碼和關(guān)系型數(shù)據(jù)庫錯(cuò)誤碼。

2.獲取到RdbStore后，調(diào)用insert()接口插入數(shù)據(jù)。示例代碼如下所示：

let store: relationalStore.RdbStore | undefined = undefined;let value1 = 'Lisa';
let value2 = 18;
let value3 = 100.5;
let value4 = new Uint8Array([1, 2, 3, 4, 5]);
let value5 = BigInt('15822401018187971961171');
// 以下三種方式可用
const valueBucket1: relationalStore.ValuesBucket = {'NAME': value1,'AGE': value2,'SALARY': value3,'CODES': value4,'IDENTITY': value5,
};
const valueBucket2: relationalStore.ValuesBucket = {NAME: value1,AGE: value2,SALARY: value3,CODES: value4,IDENTITY: value5,
};
const valueBucket3: relationalStore.ValuesBucket = {"NAME": value1,"AGE": value2,"SALARY": value3,"CODES": value4,"IDENTITY": value5,
};if (store !== undefined) {(store as relationalStore.RdbStore).insert('EMPLOYEE', valueBucket1, (err: BusinessError, rowId: number) => {if (err) {console.error(`Failed to insert data. Code:${err.code}, message:${err.message}`);return;}console.info(`Succeeded in inserting data. rowId:${rowId}`);})
}

說明
關(guān)系型數(shù)據(jù)庫沒有顯式的flush操作實(shí)現(xiàn)持久化，數(shù)據(jù)插入即保存在持久化文件。

3.根據(jù)謂詞指定的實(shí)例對(duì)象，對(duì)數(shù)據(jù)進(jìn)行修改或刪除。

調(diào)用update()方法修改數(shù)據(jù)，調(diào)用delete()方法刪除數(shù)據(jù)。示例代碼如下所示：

let value6 = 'Rose';
let value7 = 22;
let value8 = 200.5;
let value9 = new Uint8Array([1, 2, 3, 4, 5]);
let value10 = BigInt('15822401018187971967863');
// 以下三種方式可用
const valueBucket4: relationalStore.ValuesBucket = {'NAME': value6,'AGE': value7,'SALARY': value8,'CODES': value9,'IDENTITY': value10,
};
const valueBucket5: relationalStore.ValuesBucket = {NAME: value6,AGE: value7,SALARY: value8,CODES: value9,IDENTITY: value10,
};
const valueBucket6: relationalStore.ValuesBucket = {"NAME": value6,"AGE": value7,"SALARY": value8,"CODES": value9,"IDENTITY": value10,
};// 修改數(shù)據(jù)
let predicates1 = new relationalStore.RdbPredicates('EMPLOYEE'); // 創(chuàng)建表'EMPLOYEE'的predicates
predicates1.equalTo('NAME', 'Lisa'); // 匹配表'EMPLOYEE'中'NAME'為'Lisa'的字段
if (store !== undefined) {(store as relationalStore.RdbStore).update(valueBucket4, predicates1, (err: BusinessError, rows: number) => {if (err) {console.error(`Failed to update data. Code:${err.code}, message:${err.message}`);return;}console.info(`Succeeded in updating data. row count: ${rows}`);})
}// 刪除數(shù)據(jù)
predicates1 = new relationalStore.RdbPredicates('EMPLOYEE');
predicates1.equalTo('NAME', 'Lisa');
if (store !== undefined) {(store as relationalStore.RdbStore).delete(predicates1, (err: BusinessError, rows: number) => {if (err) {console.error(`Failed to delete data. Code:${err.code}, message:${err.message}`);return;}console.info(`Delete rows: ${rows}`);})
}

4.根據(jù)謂詞指定的查詢條件查找數(shù)據(jù)。

調(diào)用query()方法查找數(shù)據(jù)，返回一個(gè)ResultSet結(jié)果集。示例代碼如下所示：

let predicates2 = new relationalStore.RdbPredicates('EMPLOYEE');
predicates2.equalTo('NAME', 'Rose');
if (store !== undefined) {(store as relationalStore.RdbStore).query(predicates2, ['ID', 'NAME', 'AGE', 'SALARY', 'IDENTITY'], (err: BusinessError, resultSet) => {if (err) {console.error(`Failed to query data. Code:${err.code}, message:${err.message}`);return;}console.info(`ResultSet column names: ${resultSet.columnNames}, column count: ${resultSet.columnCount}`);// resultSet是一個(gè)數(shù)據(jù)集合的游標(biāo)，默認(rèn)指向第-1個(gè)記錄，有效的數(shù)據(jù)從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'));const identity = resultSet.getValue(resultSet.getColumnIndex('IDENTITY'));console.info(`id=${id}, name=${name}, age=${age}, salary=${salary}, identity=${identity}`);}// 釋放數(shù)據(jù)集的內(nèi)存resultSet.close();})
}

說明
當(dāng)應(yīng)用完成查詢數(shù)據(jù)操作，不再使用結(jié)果集（ResultSet）時(shí)，請(qǐng)及時(shí)調(diào)用close方法關(guān)閉結(jié)果集，釋放系統(tǒng)為其分配的內(nèi)存。

5.在同路徑下備份數(shù)據(jù)庫。關(guān)系型數(shù)據(jù)庫支持兩種手動(dòng)備份和自動(dòng)備份（僅系統(tǒng)應(yīng)用可用）兩種方式，具體可見關(guān)系型數(shù)據(jù)庫備份。

此處以手動(dòng)備份為例：

if (store !== undefined) {// "Backup.db"為備份數(shù)據(jù)庫文件名，默認(rèn)在RdbStore同路徑下備份。也可指定路徑：customDir + "backup.db"(store as relationalStore.RdbStore).backup("Backup.db", (err: BusinessError) => {if (err) {console.error(`Failed to backup RdbStore. Code:${err.code}, message:${err.message}`);return;}console.info(`Succeeded in backing up RdbStore.`);})
}

6.從備份數(shù)據(jù)庫中恢復(fù)數(shù)據(jù)。關(guān)系型數(shù)據(jù)庫支持兩種方式：恢復(fù)手動(dòng)備份數(shù)據(jù)和恢復(fù)自動(dòng)備份數(shù)據(jù)（僅系統(tǒng)應(yīng)用可用），具體可見關(guān)系型數(shù)據(jù)庫數(shù)據(jù)恢復(fù)。

此處以調(diào)用restore接口恢復(fù)手動(dòng)備份數(shù)據(jù)為例：

if (store !== undefined) {(store as relationalStore.RdbStore).restore("Backup.db", (err: BusinessError) => {if (err) {console.error(`Failed to restore RdbStore. Code:${err.code}, message:${err.message}`);return;}console.info(`Succeeded in restoring RdbStore.`);})
}

7.刪除數(shù)據(jù)庫。
調(diào)用deleteRdbStore()方法，刪除數(shù)據(jù)庫及數(shù)據(jù)庫相關(guān)文件。示例代碼如下：

Stage模型示例：

relationalStore.deleteRdbStore(this.context, 'RdbTest.db', (err: BusinessError) => {if (err) {console.error(`Failed to delete RdbStore. Code:${err.code}, message:${err.message}`);return;}console.info('Succeeded in deleting RdbStore.');
});

FA模型示例：

relationalStore.deleteRdbStore(context, 'RdbTest.db', (err: BusinessError) => {if (err) {console.error(`Failed to delete RdbStore. Code:${err.code}, message:${err.message}`);return;}console.info('Succeeded in deleting RdbStore.');
});

六、示例完整代碼

效果圖

TestRdbStore.ets代碼

import { relationalStore } from '@kit.ArkData'
import { common } from '@kit.AbilityKit'
import { util } from '@kit.ArkTS'
import { BusinessError } from '@kit.BasicServicesKit';
import { window } from '@kit.ArkUI';let context = getContext(this) as common.UIAbilityContext;
let mStore: relationalStore.RdbStore | undefined = undefined;const STORE_CONFIG: relationalStore.StoreConfig = {name: 'RdbTest.db', // 數(shù)據(jù)庫文件名securityLevel: relationalStore.SecurityLevel.S3, // 數(shù)據(jù)庫安全級(jí)別encrypt: false, // 可選參數(shù)，指定數(shù)據(jù)庫是否加密，默認(rèn)不加密customDir: 'customDir/subCustomDir', // 可選參數(shù)，數(shù)據(jù)庫自定義路徑。數(shù)據(jù)庫將在如下的目錄結(jié)構(gòu)中被創(chuàng)建：context.databaseDir + '/rdb/' + customDir，其中context.databaseDir是應(yīng)用沙箱對(duì)應(yīng)的路徑，'/rdb/'表示創(chuàng)建的是關(guān)系型數(shù)據(jù)庫，customDir表示自定義的路徑。當(dāng)此參數(shù)不填時(shí)，默認(rèn)在本應(yīng)用沙箱目錄下創(chuàng)建RdbStore實(shí)例。isReadOnly: false // 可選參數(shù)，指定數(shù)據(jù)庫是否以只讀方式打開。該參數(shù)默認(rèn)為false，表示數(shù)據(jù)庫可讀可寫。該參數(shù)為true時(shí)，只允許從數(shù)據(jù)庫讀取數(shù)據(jù)，不允許對(duì)數(shù)據(jù)庫進(jìn)行寫操作，否則會(huì)返回錯(cuò)誤碼801。
};// 判斷數(shù)據(jù)庫版本，如果不匹配則需進(jìn)行升降級(jí)操作
// 假設(shè)當(dāng)前數(shù)據(jù)庫版本為3，表結(jié)構(gòu)：EMPLOYEE (NAME, AGE, SALARY, CODES, IDENTITY)
const SQL_CREATE_TABLE ='CREATE TABLE IF NOT EXISTS EMPLOYEE (ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT NOT NULL, AGE INTEGER, SALARY REAL, CODES BLOB, IDENTITY UNLIMITED INT)'; // 建表Sql語句, IDENTITY為bigint類型，sql中指定類型為UNLIMITED INTfunction initRdbTestDB() {relationalStore.getRdbStore(context, STORE_CONFIG, (err, store) => {if (err) {console.error(`獲取數(shù)據(jù)庫失敗，Failed to get RdbStore. Code:${err.code}, message:${err.message}`);return;}mStore = store;console.info('獲取數(shù)據(jù)庫成功，Succeeded in getting RdbStore.');});
}function createTable() {if (mStore == null || mStore == undefined) {console.log("createTable 失敗 ， mStore 為空或未定義")return;}// 當(dāng)數(shù)據(jù)庫創(chuàng)建時(shí)，數(shù)據(jù)庫默認(rèn)版本為0if (mStore.version === 0) {mStore.executeSql(SQL_CREATE_TABLE); // 創(chuàng)建數(shù)據(jù)表// 設(shè)置數(shù)據(jù)庫的版本，入?yún)榇笥?的整數(shù)// store.version = 3;mStore.version = 1;console.log("createTable 完畢")}}function alterTable1() {if (mStore == null || mStore == undefined) {console.log("alterTable1 失敗 ， mStore 為空或未定義")return;}// 如果數(shù)據(jù)庫版本不為0且和當(dāng)前數(shù)據(jù)庫版本不匹配，需要進(jìn)行升降級(jí)操作// 當(dāng)數(shù)據(jù)庫存在并假定版本為1時(shí)，例應(yīng)用從某一版本升級(jí)到當(dāng)前版本，數(shù)據(jù)庫需要從1版本升級(jí)到2版本if (mStore.version === 1) {// version = 1：表結(jié)構(gòu)：EMPLOYEE (NAME, SALARY, CODES, ADDRESS) => version = 2：表結(jié)構(gòu)：EMPLOYEE (NAME, AGE, SALARY, CODES, ADDRESS)mStore.executeSql('ALTER TABLE EMPLOYEE ADD COLUMN AGE INTEGER');mStore.version = 2;console.log("alterTable1 完畢")}
}function alterTable2() {if (mStore == null || mStore == undefined) {console.log("alterTable2 失敗 ， mStore 為空或未定義")return;}// 當(dāng)數(shù)據(jù)庫存在并假定版本為2時(shí)，例應(yīng)用從某一版本升級(jí)到當(dāng)前版本，數(shù)據(jù)庫需要從2版本升級(jí)到3版本if (mStore.version === 2) {// version = 2：表結(jié)構(gòu)：EMPLOYEE (NAME, AGE, SALARY, CODES, ADDRESS) => version = 3：表結(jié)構(gòu)：EMPLOYEE (NAME, AGE, SALARY, CODES)(mStore as relationalStore.RdbStore).executeSql('ALTER TABLE EMPLOYEE DROP COLUMN ADDRESS TEXT');mStore.version = 3;console.log("alterTable2 完畢")}
}/*** 獲取到RdbStore后，調(diào)用insert()接口插入數(shù)據(jù)。*/
function insertData() {let value1 = 'Lisa';let value2 = 18;let value3 = 100.5;let value4 = new Uint8Array([1, 2, 3, 4, 5]);let value5 = BigInt('15822401018187971961171');// 以下三種方式可用const valueBucket1: relationalStore.ValuesBucket = {'NAME': value1,'AGE': value2,'SALARY': value3,'CODES': value4,'IDENTITY': value5,};const valueBucket2: relationalStore.ValuesBucket = {NAME: value1,AGE: value2,SALARY: value3,CODES: value4,IDENTITY: value5,};const valueBucket3: relationalStore.ValuesBucket = {"NAME": value1,"AGE": value2,"SALARY": value3,"CODES": value4,"IDENTITY": value5,};if (mStore !== undefined) {(mStore as relationalStore.RdbStore).insert('EMPLOYEE', valueBucket1, (err: BusinessError, rowId: number) => {if (err) {console.error(`插入數(shù)據(jù)失敗：Failed to insert data. Code:${err.code}, message:${err.message}`);return;}console.info(`插入數(shù)據(jù)成功：Succeeded in inserting data. rowId:${rowId}`);})}
}/*** 根據(jù)謂詞指定的實(shí)例對(duì)象，對(duì)數(shù)據(jù)進(jìn)行修改或刪除。調(diào)用update()方法修改數(shù)據(jù)，調(diào)用delete()方法刪除數(shù)據(jù)*/
function updateData() {let value6 = 'Rose';let value7 = 22;let value8 = 200.5;let value9 = new Uint8Array([1, 2, 3, 4, 5]);let value10 = BigInt('15822401018187971967863');// 以下三種方式可用const valueBucket4: relationalStore.ValuesBucket = {'NAME': value6,'AGE': value7,'SALARY': value8,'CODES': value9,'IDENTITY': value10,};const valueBucket5: relationalStore.ValuesBucket = {NAME: value6,AGE: value7,SALARY: value8,CODES: value9,IDENTITY: value10,};const valueBucket6: relationalStore.ValuesBucket = {"NAME": value6,"AGE": value7,"SALARY": value8,"CODES": value9,"IDENTITY": value10,};// 修改數(shù)據(jù)let predicates1 = new relationalStore.RdbPredicates('EMPLOYEE'); // 創(chuàng)建表'EMPLOYEE'的predicatespredicates1.equalTo('NAME', 'Lisa'); // 匹配表'EMPLOYEE'中'NAME'為'Lisa'的字段if (mStore !== undefined) {(mStore as relationalStore.RdbStore).update(valueBucket4, predicates1, (err: BusinessError, rows: number) => {if (err) {console.error(`修改數(shù)據(jù) Failed to update data. Code:${err.code}, message:${err.message}`);return;}console.info(`修改數(shù)據(jù) Succeeded in updating data. row count: ${rows}`);})}
}/*** 刪除數(shù)據(jù)*/
function deleteData() {let predicates1 = new relationalStore.RdbPredicates('EMPLOYEE'); // 創(chuàng)建表'EMPLOYEE'的predicates// 刪除數(shù)據(jù)predicates1 = new relationalStore.RdbPredicates('EMPLOYEE');predicates1.equalTo('NAME', 'Lisa');if (mStore !== undefined) {(mStore as relationalStore.RdbStore).delete(predicates1, (err: BusinessError, rows: number) => {if (err) {console.error(`刪除數(shù)據(jù) Failed to delete data. Code:${err.code}, message:${err.message}`);return;}console.info(`刪除數(shù)據(jù) Delete rows: ${rows}`);})}
}/*** 根據(jù)謂詞指定的查詢條件查找數(shù)據(jù)。調(diào)用query()方法查找數(shù)據(jù)，返回一個(gè)ResultSet結(jié)果集。說明當(dāng)應(yīng)用完成查詢數(shù)據(jù)操作，不再使用結(jié)果集（ResultSet）時(shí)，請(qǐng)及時(shí)調(diào)用close方法關(guān)閉結(jié)果集，釋放系統(tǒng)為其分配的內(nèi)存。*/
function queryData() {let predicates2 = new relationalStore.RdbPredicates('EMPLOYEE');predicates2.equalTo('NAME', 'Rose');if (mStore !== undefined) {(mStore as relationalStore.RdbStore).query(predicates2, ['ID', 'NAME', 'AGE', 'SALARY', 'IDENTITY'],(err: BusinessError, resultSet) => {if (err) {console.error(`查找數(shù)據(jù) Failed to query data. Code:${err.code}, message:${err.message}`);return;}console.info(`查找數(shù)據(jù) ResultSet column names: ${resultSet.columnNames}, column count: ${resultSet.columnCount}`);// resultSet是一個(gè)數(shù)據(jù)集合的游標(biāo)，默認(rèn)指向第-1個(gè)記錄，有效的數(shù)據(jù)從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'));const identity = resultSet.getValue(resultSet.getColumnIndex('IDENTITY'));console.info(`id=${id}, name=${name}, age=${age}, salary=${salary}, identity=${identity}`);}// 釋放數(shù)據(jù)集的內(nèi)存resultSet.close();})}
}/*** 在同路徑下備份數(shù)據(jù)庫。關(guān)系型數(shù)據(jù)庫支持兩種手動(dòng)備份和自動(dòng)備份（僅系統(tǒng)應(yīng)用可用）兩種方式，具體可見關(guān)系型數(shù)據(jù)庫備份。此處以手動(dòng)備份為例：*/
function backupData() {if (mStore !== undefined) {// "Backup.db"為備份數(shù)據(jù)庫文件名，默認(rèn)在RdbStore同路徑下備份。也可指定路徑：customDir + "backup.db"(mStore as relationalStore.RdbStore).backup("Backup.db", (err: BusinessError) => {if (err) {console.error(`備份數(shù)據(jù)庫 Failed to backup RdbStore. Code:${err.code}, message:${err.message}`);return;}console.info(`備份數(shù)據(jù)庫 Succeeded in backing up RdbStore.`);})}
}/*** 從備份數(shù)據(jù)庫中恢復(fù)數(shù)據(jù)。關(guān)系型數(shù)據(jù)庫支持兩種方式：恢復(fù)手動(dòng)備份數(shù)據(jù)和恢復(fù)自動(dòng)備份數(shù)據(jù)（僅系統(tǒng)應(yīng)用可用），具體可見關(guān)系型數(shù)據(jù)庫數(shù)據(jù)恢復(fù)。此處以調(diào)用restore接口恢復(fù)手動(dòng)備份數(shù)據(jù)為例：*/function restoreData() {if (mStore !== undefined) {(mStore as relationalStore.RdbStore).restore("Backup.db", (err: BusinessError) => {if (err) {console.error(`從備份數(shù)據(jù)庫中恢復(fù)數(shù)據(jù) Failed to restore RdbStore. Code:${err.code}, message:${err.message}`);return;}console.info(`從備份數(shù)據(jù)庫中恢復(fù)數(shù)據(jù) Succeeded in restoring RdbStore.`);})}
}/*** 刪除數(shù)據(jù)庫。調(diào)用deleteRdbStore()方法，刪除數(shù)據(jù)庫及數(shù)據(jù)庫相關(guān)文件。示例代碼如下：Stage模型示例：*/
function deleteDB() {relationalStore.deleteRdbStore(context, 'RdbTest.db', (err: BusinessError) => {if (err) {console.error(`Failed to delete RdbStore. Code:${err.code}, message:${err.message}`);return;}console.info('Succeeded in deleting RdbStore.');});
}@Entry
@Component
struct TestRdbStore {@State message: string = '通過關(guān)系型數(shù)據(jù)庫實(shí)現(xiàn)數(shù)據(jù)持久化';build() {Column({ space: 10 }) {Text(this.message).id('TestRdbStoreHelloWorld').fontSize(20).fontWeight(FontWeight.Bold)Button("初始化數(shù)據(jù)庫").fontColor(Color.White).fontSize(20).fontWeight(FontWeight.Medium).onClick(() => {initRdbTestDB()})Button("創(chuàng)建數(shù)據(jù)表").fontColor(Color.White).fontSize(20).fontWeight(FontWeight.Medium).onClick(() => {createTable()})Button("修改數(shù)據(jù)表1").fontColor(Color.White).fontSize(20).fontWeight(FontWeight.Medium).onClick(() => {alterTable1()})Button("修改數(shù)據(jù)表2").fontColor(Color.White).fontSize(20).fontWeight(FontWeight.Medium).onClick(() => {alterTable2()})Button("插入數(shù)據(jù)").fontColor(Color.White).fontSize(20).fontWeight(FontWeight.Medium).onClick(() => {insertData()})Button("更新數(shù)據(jù)").fontColor(Color.White).fontSize(20).fontWeight(FontWeight.Medium).onClick(() => {updateData()})Button("刪除數(shù)據(jù)").fontColor(Color.White).fontSize(20).fontWeight(FontWeight.Medium).onClick(() => {deleteData()})Button("查詢數(shù)據(jù)").fontColor(Color.White).fontSize(20).fontWeight(FontWeight.Medium).onClick(() => {queryData()})Button("備份數(shù)據(jù)").fontColor(Color.White).fontSize(20).fontWeight(FontWeight.Medium).onClick(() => {backupData()})Button("從備份數(shù)據(jù)庫中恢復(fù)數(shù)據(jù)").fontColor(Color.White).fontSize(20).fontWeight(FontWeight.Medium).onClick(() => {restoreData()})Button("刪除數(shù)據(jù)庫").fontColor(Color.White).fontSize(20).fontWeight(FontWeight.Medium).onClick(() => {deleteDB()})}.height('100%').width('100%')}
}