跟老卫学HarmonyOS开发：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 }”的数据从数据库删除。

运行

运行应用显示的界面效果如下图10-3所示。

当用户点击“增加”后再点击“查询”时，界面如下图10-4所示，证明数据已经成功写入数据库。

当用户点击“修改”后再点击“查询”时，界面如下图10-5所示，证明数据已经被修改并更新回数据库。

当用户点击“删除”后再点击“查询”时，界面如下图10-6所示，证明数据已经从数据库删除。

源码

• 《跟老卫学HarmonyOS开发》 开源免费教程，https://github.com/waylau/harmonyos-tutorial