HarmonyOS Next快速入门：RelationalStore关系型数据库

export default class SQLiteUtil {
  static getCreateTableSql(tableName: string, columns: ColumnInfo[]): string {
    let sql = `CREATE TABLE IF NOT EXISTS ${tableName} (`;
    columns.forEach((element, index) => {
      if (index == 0) {
        //拼接首个元素，默认为主键
        sql += `${element.name} ${DataType[element.type]} PRIMARY KEY AUTOINCREMENT,`;
      } else if (index == columns.length - 1) {
        //最后一个元素拼接语句
        sql += `${element.name} ${DataType[element.type]} NOT NULL);`;
      } else {
        sql += `${element.name} ${DataType[element.type]} NOT NULL,`;
      }
    });
    return sql;
  }
}

export interface ColumnInfo {
  name: string;
  type: DataType;
}

export enum DataType {
  NULL = 'NULL',
  INTEGER = 'INTEGER',
  REAL = 'REAL',
  TEXT = 'TEXT',
  BLOB = 'BLOB'
}

import SQLiteUtil, { ColumnInfo, DataType } from '../ChicKit/data/SQLiteUtil'
import relationalStore from '@ohos.data.relationalStore'
import { common } from '@kit.AbilityKit';
import Logger from '../utils/Logger';
import AppError from '../models/AppError';
import Schedule from '../entities/Schedule';
import { BusinessError } from '@kit.BasicServicesKit';
import { ValuesBucket, ValueType } from '@ohos.data.ValuesBucket';
import { DataModel } from '../ChicKit/data/DataModel';
import Target from '../entities/Target';
import Plan from '../entities/Plan';

const RelationalStoreName = 'shijianxu.db'

export default class RelationalStoreService {
  static rdbStore: relationalStore.RdbStore;

  /**
   * 初始化关系型数据库
   * @param context
   */
  static init(context: common.UIAbilityContext) {
    // RelationalStore配置
    let storeConfig: relationalStore.StoreConfig = {
      // 数据库文件名称
      name: RelationalStoreName,
      //安全等级
      securityLevel: relationalStore.SecurityLevel.S1
    }

    relationalStore.getRdbStore(context, storeConfig, (err, store) => {
      if (err) {
        Logger.error(`RelationalStoreService init error, error=${JSON.stringify(new AppError(err))}`)
        return;
      } else {
        RelationalStoreService.rdbStore = store
        RelationalStoreService.createScheduleTable()
        RelationalStoreService.createTargetTable()
        RelationalStoreService.createPlanTable()
      }
    });
  }

  /**
   * 创建schedule表
   */
  static createScheduleTable() {
    //表字段
    const columns: ColumnInfo[] = Schedule.getColumns()
    // 获取创建表SQL语句
    const sql = SQLiteUtil.getCreateTableSql(Schedule.TableName, columns)
    // 创建数据表
    RelationalStoreService.rdbStore.executeSql(sql, (err) => {
      if (err) {
        Logger.error(`RelationalStoreService createScheduleTable error, error=${JSON.stringify(new AppError(err))}`)
        return;
      }
    });
  }

  /**
   * 创建target表
   */
  static createTargetTable() {
    //表字段
    const columns: ColumnInfo[] = Target.getColumns()
    // 获取创建表SQL语句
    const sql = SQLiteUtil.getCreateTableSql(Target.TableName, columns)
    // 创建数据表
    RelationalStoreService.rdbStore.executeSql(sql, (err) => {
      if (err) {
        Logger.error(`RelationalStoreService createTargetTable error, error=${JSON.stringify(new AppError(err))}`)
        return;
      }
    });
  }

  /**
   * 创建plan表
   */
  static createPlanTable() {
    //表字段
    const columns: ColumnInfo[] = Plan.getColumns()
    // 获取创建表SQL语句
    const sql = SQLiteUtil.getCreateTableSql(Plan.TableName, columns)
    // 创建数据表
    RelationalStoreService.rdbStore.executeSql(sql, (err) => {
      if (err) {
        Logger.error(`RelationalStoreService createPlanTable error, error=${JSON.stringify(new AppError(err))}`)
        return;
      }
    });
  }

  /**
   * 插入数据
   * @param tableName
   * @param values
   */
  static insert(tableName: string, values: ValuesBucket) {
    RelationalStoreService.rdbStore.insert(tableName, values, (err: BusinessError, rowId: number) => {
      if (err) {
        Logger.error(`RelationalStoreService insert error, error=${JSON.stringify(new AppError(err))}`)
        return;
      } else {
        return rowId
      }
    })
  }

  /**
   * 删除
   * @param predicates
   * @returns 删除的条数
   */
  static delete(predicates: relationalStore.RdbPredicates):number{
    return RelationalStoreService.rdbStore.deleteSync(predicates)
  }

  /**
   * 更新
   * @param values
   * @param predicates
   * @returns 更新的记录条数
   */
  static update(values: ValuesBucket,predicates: relationalStore.RdbPredicates):number{
    let rows: number = RelationalStoreService.rdbStore.updateSync(values, predicates, relationalStore.ConflictResolution.ON_CONFLICT_REPLACE);
    return rows
  }

  static querySync(predicates: relationalStore.RdbPredicates, columns: ColumnInfo[]): DataModel[] {
    let dataList: DataModel[] = []
    try {
      let columnsStringArray: string[] = []
      columns.forEach(element => {
        columnsStringArray.push(element.name)
      });
      const resultSet = RelationalStoreService.rdbStore.querySync(predicates, columnsStringArray)
      resultSet.columnNames
      // resultSet.getColumnName('')
      // resultSet.getValue()

      //循环处理结果,循环条件：当所在行不是最后一行
      while (!resultSet.isAtLastRow) {
        //去往下一行
        resultSet.goToNextRow()
        let schedule: DataModel = {}
        columns.forEach(element => {
          switch (element.type) {
            case DataType.INTEGER:
              schedule[element.name] = resultSet.getLong(resultSet.getColumnIndex(element.name))
              break;
            case DataType.REAL:
              schedule[element.name] = resultSet.getDouble(resultSet.getColumnIndex(element.name))
              break;
            case DataType.TEXT:
              schedule[element.name] = resultSet.getString(resultSet.getColumnIndex(element.name))
              break;
            case DataType.BLOB:
              schedule[element.name] = resultSet.getBlob(resultSet.getColumnIndex(element.name))
              break;
          }
        })
        dataList.push(schedule)
      }
    } catch (err) {
      Logger.error(`RelationalStoreService querySync error, error=${JSON.stringify(new AppError(err))}`)
    }
    return dataList
  }
}