HarmonyOS鸿蒙Next跟老卫学ArkTS关系型数据库开发

HarmonyOS鸿蒙Next跟老卫学ArkTS关系型数据库开发本节以一个“账本”为例，使用关系型数据库的相关接口实现了对账单的增、删、改、查操作。

为了演示该功能，创建一个名为“ArkTSRdb”的应用，源码见文末。

操作RdbStore

首先要获取一个RdbStore来操作关系型数据库。

在src/main/ets目录下创建名为“common/database”目录，用于存放常用的数据库相关的类。在该common/database目录创建工具类Rdb，代码如下：

import { relationalStore } from '@kit.ArkData';
import CommonConstants from '../constants/CommonConstants';

export default class Rdb {
 private rdbStore: relationalStore.RdbStore | null = null;
 private tableName: string;
 private sqlCreateTable: string;
 private columns: Array<string>;

 constructor(tableName: string, sqlCreateTable: string, columns: Array<string>) {
   this.tableName = tableName;
   this.sqlCreateTable = sqlCreateTable;
   this.columns = columns;
 }

 getRdbStore(callback: Function = () => {
 }) {
   if (!callback || typeof callback === 'undefined' || callback === undefined) {
     console.info('getRdbStore() has no callback!');
     return;
   }
   if (this.rdbStore !== null) {
     console.info('The rdbStore exists.');
     callback();
     return
   }
   let context: Context = getContext(this) as Context;
   relationalStore.getRdbStore(context, CommonConstants.STORE_CONFIG, (err, rdb) => {
     if (err) {
       console.error(`gerRdbStore() failed, err: ${err}`);
       return;
     }
     this.rdbStore = rdb;
     this.rdbStore.executeSql(this.sqlCreateTable);
     console.info('getRdbStore() finished.');
     callback();
   });
 }

 // ...
}

数据库所需要的配置存在以下common/constants/CommonConstants.ets文件中：

import { relationalStore } from '@kit.ArkData';

export default class CommonConstants {
 /**
  * Rdb database config.
  */
 static readonly STORE_CONFIG: relationalStore.StoreConfig = {
   name: 'database.db',
   securityLevel: relationalStore.SecurityLevel.S1
 };
}

为了对数据进行增、删、改、查操作，我们要封装对应接口。关系型数据库接口提供的增、删、改、查方法均有callback和Promise两种异步回调方式，本例子使用了callback异步回调。代码如下：

insertData(data: relationalStore.ValuesBucket, callback: Function = () => {
}) {
 if (!callback || typeof callback === 'undefined' || callback === undefined) {
   console.info('insertData() has no callback!');
   return;
 }
 let resFlag: boolean = false;
 const valueBucket: relationalStore.ValuesBucket = data;
 if (this.rdbStore) {
   this.rdbStore.insert(this.tableName, valueBucket, (err, ret) => {
     if (err) {
       console.error(`insertData() failed, err: ${err}`);
       callback(resFlag);
       return;
     }
     console.info(`insertData() finished: ${ret}`);
     callback(ret);
   });
 }
}

deleteData(predicates: relationalStore.RdbPredicates, callback: Function = () => {
}) {
 if (!callback || typeof callback === 'undefined' || callback === undefined) {
   console.info('deleteData() has no callback!');
   return;
 }
 let resFlag: boolean = false;
 if (this.rdbStore) {
   this.rdbStore.delete(predicates, (err, ret) => {
     if (err) {
       console.error(`deleteData() failed, err: ${err}`);
       callback(resFlag);
       return;
     }
     console.info(`deleteData() finished: ${ret}`);
     callback(!resFlag);
   });
 }
}

updateData(predicates: relationalStore.RdbPredicates, data: relationalStore.ValuesBucket, callback: Function = () => {
}) {
 if (!callback || typeof callback === 'undefined' || callback === undefined) {
   console.info('updateDate() has no callback!');
   return;
 }
 let resFlag: boolean = false;
 const valueBucket: relationalStore.ValuesBucket = data;
 if (this.rdbStore) {
   this.rdbStore.update(valueBucket, predicates, (err, ret) => {
     if (err) {
       console.error(`updateData() failed, err: ${err}`);
       callback(resFlag);
       return;
     }
     console.info(`updateData() finished: ${ret}`);
     callback(!resFlag);
   });
 }
}

query(predicates: relationalStore.RdbPredicates, callback: Function = () => {
}) {
 if (!callback || typeof callback === 'undefined' || callback === undefined) {
   console.info('query() has no callback!');
   return;
 }
 if (this.rdbStore) {
   this.rdbStore.query(predicates, this.columns, (err, resultSet) => {
     if (err) {
       console.error(`query() failed, err:  ${err}`);
       return;
     }
     console.info('query() finished.');
     callback(resultSet);
     resultSet.close();
   });
 }
}

账目信息的表示

由于需要记录账目的类型（收入/支出）、具体类别和金额，因此我们需要创建一张存储账目信息的表，SQL脚本下：

export default class CommonConstants {
 /**
  * Account table config.
  */
 static readonly ACCOUNT_TABLE: TableConfig = {
   tableName: 'accountTable',
   sqlCreate: 'CREATE TABLE IF NOT EXISTS accountTable(id INTEGER PRIMARY KEY AUTOINCREMENT, accountType INTEGER, ' +
     'typeText TEXT, amount INTEGER)',
   columns: ['id', 'accountType', 'typeText', 'amount']
 };

interface TableConfig {
 tableName: string;
 sqlCreate: string;
 columns: Array<string>;
}

accountTable表的各字段含义如下：

id：主键。
accountType：账目类型。0表示支出；1表示收入。
typeText：账目的具体类别。
amount：账目金额。

在src/main/ets目录下创建名为“viewmodel”目录，并在该目录下创建与上述脚本对应的类AccountData，代码如下：

export default class AccountData {
 id: number = -1;
 accountType: number = 0;
 typeText: string = '';
 amount: number = 0;
}

操作账目信息表

创建针对账目信息表的操作类common/database/tables/AccountTable.ets。AccountTable类封装了增、删、改、查接口。代码如下：

import { relationalStore } from '@kit.ArkData';
import AccountData from '../../../viewmodel/AccountData';
import CommonConstants from '../../constants/CommonConstants';
import Rdb from '../Rdb';

export default class AccountTable {
 private accountTable = new Rdb(CommonConstants.ACCOUNT_TABLE.tableName, CommonConstants.ACCOUNT_TABLE.sqlCreate,
   CommonConstants.ACCOUNT_TABLE.columns);

 constructor(callback: Function = () => {
 }) {
   this.accountTable.getRdbStore(callback);
 }

 getRdbStore(callback: Function = () => {
 }) {
   this.accountTable.getRdbStore(callback);
 }

 insertData(account: AccountData, callback: Function) {
   const valueBucket: relationalStore.ValuesBucket = generateBucket(account);
   this.accountTable.insertData(valueBucket, callback);
 }

 deleteData(account: AccountData, callback: Function) {
   let predicates = new relationalStore.RdbPredicates(CommonConstants.ACCOUNT_TABLE.tableName);
   predicates.equalTo('id', account.id);
   this.accountTable.deleteData(predicates, callback);
 }

 updateData(account: AccountData, callback: Function) {
   const valueBucket: relationalStore.ValuesBucket = generateBucket(account);
   let predicates = new relationalStore.RdbPredicates(CommonConstants.ACCOUNT_TABLE.tableName);
   predicates.equalTo('id', account.id);
   this.accountTable.updateData(predicates, valueBucket, callback);
 }

 query(amount: number, callback: Function, isAll: boolean = true) {
   let predicates = new relationalStore.RdbPredicates(CommonConstants.ACCOUNT_TABLE.tableName);
   if (!isAll) {
     predicates.equalTo('amount', amount);
   }
   this.accountTable.query(predicates, (resultSet: relationalStore.ResultSet) => {
     let count: number = resultSet.rowCount;
     if (count === 0 || typeof count === 'string') {
       console.log('Query no results!');
       callback([]);
     } else {
       resultSet.goToFirstRow();
       const result: AccountData[] = [];
       for (let i = 0; i < count; i++) {
         let tmp: AccountData = {
           id: 0, accountType: 0, typeText: '', amount: 0
         };
         tmp.id = resultSet.getDouble(resultSet.getColumnIndex('id'));
         tmp.accountType = resultSet.getDouble(resultSet.getColumnIndex('accountType'));
         tmp.typeText = resultSet.getString(resultSet.getColumnIndex('typeText'));
         tmp.amount = resultSet.getDouble(resultSet.getColumnIndex('amount'));
         result[i] = tmp;
         resultSet.goToNextRow();
       }
       callback(result);
     }
   });
 }
}

function generateBucket(account: AccountData): relationalStore.ValuesBucket {
 let obj: relationalStore.ValuesBucket = {};
 obj.accountType = account.accountType;
 obj.typeText = account.typeText;
 obj.amount = account.amount;
 return obj;
}

设计界面

为了简化程序，突出核心逻辑，我们的界面设计的非常简单，只是一个Text组件和四个Button组件。四个Button组件用于触发增、删、改、查操作，而Text组件用于展示每次操作后的结果。修改Index代码如下：

import AccountTable from '../common/database/tables/AccountTable';
import AccountData from '../viewmodel/AccountData';

@Entry
@Component
struct Index {
 @State message: string = 'Hello World'
 private accountTable = new AccountTable();

 build() {
   Row() {
     Column() {
       Text(this.message)
         .fontSize(50)
         .fontWeight(FontWeight.Bold)

       // 增加
       Button(('增加'), { type: ButtonType.Capsule })
         .width(140)
         .fontSize(40)
         .fontWeight(FontWeight.Medium)
         .margin({ top: 20, bottom: 20 })
         .onClick(() => {
           let newAccount: AccountData = { id: 1, accountType: 0, typeText: '苹果', amount: 0 };
           this.accountTable.insertData(newAccount, () => {
           })
         })

       // 查询
       Button(('查询'), { type: ButtonType.Capsule })
         .width(140)
         .fontSize(40)
         .fontWeight(FontWeight.Medium)
         .margin({ top: 20, bottom: 20 })
         .onClick(() => {
           this.accountTable.query(0, (result: AccountData[]) => {
             this.message = JSON.stringify(result);
           }, true);
         })

       // 修改
       Button(('修改'), { type: ButtonType.Capsule })
         .width(140)
         .fontSize(40)
         .fontWeight(FontWeight.Medium)
         .margin({ top: 20, bottom: 20 })
         .onClick(() => {
           let newAccount: AccountData = { id: 1, accountType: 1, typeText: '栗子', amount: 1 };
           this.accountTable.updateData(newAccount, () => {
           })
         })

       // 删除
       Button(('删除'), { type: ButtonType.Capsule })
         .width(140)
         .fontSize(40)
         .fontWeight(FontWeight.Medium)
         .margin({ top: 20, bottom: 20 })
         .onClick(() => {
           let newAccount: AccountData = { id: 1, accountType: 1, typeText: '栗子', amount: 1 };
           this.accountTable.deleteData(newAccount, () => {
           })
         })
     }
     .width('100%')
   }
   .height('100%')
 }
}

上述代码，在aboutToAppear生命周期阶段，初始化了数据库。点击“新增”会将预设好的数据“{ id: 1, accountType: 0, typeText: '苹果', amount: 0 }”写入到数据库。点击“修改”会将预设好的“{ id: 1, accountType: 1, typeText: '栗子', amount: 1 }”的数据更新到数据库。点击“删除”则会将预设好的“{ id: 1, accountType: 1, typeText: '栗子', amount: 1 }”的数据从数据库删除。