【HarmonyOS Next】关系型数据库封装DBHelper

背景

---

在实现单机应用时，用户所有的操作数据都需要保存在本地。官方提供了关系型数据库基于SQLite组件，适用于包含复杂关系数据的场景。

并且提供了大量的增删改查方法，不过在使用过程中还是会出现比较多的硬编码和不方便操作的情况，比如：

• 创建数据表：需要手写SQL语句

• 添加数据时，需要手写类型名称和数值

• 查询数据获取比较麻烦

因此，在@kit.ArkData的基础上进行使用的封装，方便我们在调用关系型数据库进行增删改查。

DBHelper

架构

DBServer

作用：数据操作服务类。初始化数据库方法、创建数据表名、数据库数据的增删改查入口。

import { relationalStore, ValuesBucket } from "@kit.ArkData";
import { DBTable, TableStruct } from "./DBCommon/DBTable";
import { DeletePredicates } from "./DBCommon/DeletePredicates";
import { QueryPredicates } from "./DBCommon/QueryPredicates";
import { UpdatePredicates } from "./DBCommon/UpdatePredicates";
import { BaseEntity } from "./Models/BaseEntity";

export class DBServer {
  static DBStore?: relationalStore.RdbStore;

  static Init(context: Context) {
    DBServer.HOInit(context);
  }

  private static async HOInit(context: Context) {
    const config: relationalStore.StoreConfig = {
      name: 'drug.db', // 数据库文件名
      securityLevel: relationalStore.SecurityLevel.S3, // 数据库安全级别
      encrypt: false, // 可选参数，指定数据库是否加密，默认不加密
      customDir: 'DrugDB', //存放地址
      isReadOnly: false
    };

    relationalStore.getRdbStore(context, config, (err, store) => {
      if (err) {
        console.error(`Failed to get RdbStore. Code:${err.code}, message:${err.message}`);
        return;
      }
      console.info('Succeeded in getting RdbStore.');

      DBServer.DBStore = store;
    });
  }

  /**
   *创建数据表
   * @param tableSQL
   */
  public static CreateTable(tableSQL: string) {
    DBServer.DBStore?.executeSql(tableSQL);
  }


  /**
   * 检查表名是否存在
   * @param tableName
   * @returns
   */
  public static async CheckTableName(tableName: string): Promise<boolean> {
    try {
      let tableNames: string[] = await DBServer.GetTableNames();
      if (tableNames.includes(tableName)) {
        return true;
      }
      return false;
    } catch (e) {
      return false;
    }
  }

  /**
   * 获取数据库表名称
   * @returns
   */
  public static async GetTableNames(): Promise<string[]> {
    let queSQL: string = "SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%';"
    let result: relationalStore.ResultSet = await (DBServer.DBStore as relationalStore.RdbStore).querySql(queSQL);
    const tables: string[] = [];
    try {
      while (result.goToNextRow()) {
        const tableName: string = result.getString(result.getColumnIndex("name"));
        tables.push(tableName);
      }
    } catch (e) {
      console.info(e);
    }
    result.close();
    return tables;
  }

  /**
   * 插入数据
   * @param instance
   * @returns
   */
  public static async InsertAsync<T extends BaseEntity>(instance: T): Promise<number> {
    try {
      let tableName: string = await DBServer.GetTableNameByInstance<T>(instance)
      //创建value对象
      let values: ValuesBucket = DBServer.GetValuesBucket<T>(instance)
      let result: number | undefined = await DBServer.DBStore?.insert(tableName, values)
      if (result) {
        return result;
      }
      return -1;
    } catch (e) {
      console.info(e);
      return -1;
    }
  }

  /**
   * 插入一组数据
   * @param instances
   * @returns
   */
  public static async InsertListAsync<T extends BaseEntity>(instances: T[]): Promise<number> {
    try {
      let tableName: string = await DBServer.GetTableNameByInstance<T>(instances[0]);
      //创建value对象
      let values: ValuesBucket[] = [];
      instances.forEach((x, i) => {
        let value: ValuesBucket = DBServer.GetValuesBucket<T>(x);
        values.push(value);
      })
      let result: number | undefined = await DBServer.DBStore?.batchInsert(tableName, values)
      if (result) {
        return result;
      }
      return -1;
    } catch (e) {
      console.info(e);
      return -1;
    }
  }

  /**
   * 获取对象的数据结构
   * @param instance
   * @returns
   */
  private static GetValuesBucket<T extends BaseEntity>(instance: T): ValuesBucket {
    //创建value对象
    let values: ValuesBucket = {};
    let keys = Object.keys(instance);
    let copyValue: object = JSON.parse(JSON.stringify(instance))
    keys.forEach((x: string, i: number) => {
      values[x] = copyValue[x]
    })
    return values;
  }

  /**
   * 查询方法
   * @param tableName
   * @returns
   */
  public static Query<T extends BaseEntity>(tableName: string): QueryPredicates<T> {
    return new QueryPredicates<T>(tableName);
  }

  /**
   * 删除方法
   * @param tableName
   * @returns
   */
  public static Delete<T extends BaseEntity>(tableName: string): DeletePredicates<T> {
    return new DeletePredicates<T>(tableName);
  }

  /**
   * 实体更新方法
   * @param instance
   * @returns
   */
  public static Update<T extends BaseEntity>(tableName: string, instance: T): UpdatePredicates<T> {
    let value: ValuesBucket = DBServer.GetValuesBucket<T>(instance);
    return new UpdatePredicates<T>(tableName, value);
  }

  /**
   * 获取表名
   * @param instance
   * @returns
   */
  private static async GetTableNameByInstance<T extends BaseEntity>(instance: T): Promise<string> {
    let className: string = instance.constructor.name;
    let tableStruct: TableStruct | undefined = await DBTable.GetTableStructByClassName(className);
    if (tableStruct == undefined) {
      throw new Error("数据库表不存在");
    }
    let tableName: string = tableStruct.TableName as string;
    //检查数据表是否已经创建
    let checkResult: boolean = await DBServer.CheckTableName(tableName);
    if (!checkResult) {
      DBServer.CreateTable(DBTable.GetCreateTableSQL(className));
    }
    return tableName;
  }
}

Models->BaseEntity

作用：为了统一数据库实体类的继承

export class BaseEntity {

}

DBCommon->ColumnType

作用：定义数据表中每一项的数据类型

export enum ColumnType {
  /** 整数值是全数字(包括正和负) */
  INTEGER = "INTEGER",
  /** 实数是10进制的数值。SQLite使用8字节的符点数来存储实数。 */
  REAL = "REAL",
  /** 文本(TEXT)是字符数据 */
  TEXT = "TEXT",
  /** 二进制大对象(BLOB)是任意类型的数据 */
  BLOB = "BLOB",
}

DBCommon->DBTable

作用：数据表结构缓存类，里面定义了表数据结构和列数据结构。并且缓存类封装相对应的方法去操作对表数据结构进行增删改查。

import { ColumnType } from "./ColumnType";

class DBTableCache {
  // 类名-表数据
  private tableMaps: Map<string, TableStruct> = new Map()

  /**
   * 添加表
   * @param className
   * @param tableName
   * @returns
   */
  public async AddTable(className: string, tableName: string): Promise<boolean> {
    try {
      if (!this.HasClass(className)) {
        let table: TableStruct = new TableStruct();
        table.TableName = tableName;
        this.tableMaps.set(className, table);
      } else if ((this.tableMaps.get(className) as TableStruct).TableName != tableName) {
        (this.tableMaps.get(className) as TableStruct).TableName = tableName;
      }

      return true;
    } catch (e) {
      return false;
    }
  }

  /**
   * 添加列结构
   * @param className
   * @param column
   * @returns
   */
  public async AddColumn(className: string, column: ColumnStruct): Promise<boolean> {
    try {
      if (!this.HasClass(className)) {
        await this.AddTable(className, className);
      }
      (this.tableMaps.get(className) as TableStruct).Columns.push(column);
      return true;
    } catch (e) {
      return false;
    }
  }

  /**
   * 获取表数据结构
   * @param tableName
   * @returns
   */
  public async GetTableStructByTableName(tableName: string): Promise<TableStruct | undefined> {
    let values = this.tableMaps.values();
    let result = values.next();
    while (!result.done) {
      if (result.value.TableName === tableName) {
        return result.value;
      }
      result = values.next();
    }
    return undefined;
  }

  /**
   * 获取表数据结构
   * @param className
   * @returns
   */
  public async GetTableStructByClassName(className: string): Promise<TableStruct | undefined> {
    if (!this.tableMaps.has(className)) {
      return undefined;
    }
    return this.tableMaps.get(className);
  }

  /**
   * 获取创建数据表格的SQL语句
   * @param className
   * @returns
   */
  public GetCreateTableSQL(className: string): string {
    let result: string = "";
    const tableStrings: string[] = [];
    const tableStruct: TableStruct = this.tableMaps.get(className) as TableStruct;
    tableStrings.push(`CREATE TABLE IF NOT EXISTS ${tableStruct.TableName} (`)
    tableStruct.Columns.reverse().forEach((x: ColumnStruct, i: number) => {
      if (x.IsId) {
        tableStrings.push(`${x.ColumnName} ${x.Type} PRIMARY KEY ${x.Autoincrement ? 'AUTOINCREMENT' : ''},`);
      } else {
        tableStrings.push(`${x.ColumnName} ${x.Type},`)
      }
      if (i == tableStruct.Columns.length - 1) {
        tableStrings[i+1] = tableStrings[i+1].replace(',', '');
      }
    });
    tableStrings.push(')');
    result = tableStrings.join('');
    return result;
  }

  public HasClass(className: string): boolean {
    if (this.tableMaps.has(className)) {
      return true;
    }
    return false;
  }

  /**
   * 判断是否包含表名称
   * @param tableName
   * @returns
   */
  public HasTable(tableName: string): boolean {
    let values = this.tableMaps.values();
    let result = values.next();
    while (!result.done) {
      if (result.value.TableName === tableName) {
        return true;
      }
      result = values.next();
    }
    return false;
  }
}

export const DBTable: DBTableCache = new DBTableCache();

/**
 * 表数据结构
 */
export class TableStruct {
  /**
   * 表名字
   */
  TableName?: string
  /**
   * 列数据结构
   */
  Columns: ColumnStruct[] = [];
}

/**
 * 列数据结构
 */
export class ColumnStruct {
  /**
   * 属性名
   */
  Name?: string
  /**
   * 数据表名称
   */
  ColumnName?: string
  /**
   * 数据表类型
   */
  Type?: ColumnType
  /**
   * 是否是ID
   */
  IsId?: boolean
  /**
   * 是否自增长
   */
  Autoincrement?: boolean
}

DBCommon->DeletePredicates

作用：在RdbPredicates基础上同步封装的方法，并提供在方法拼接后直接删除方法。

import { DBServer } from "../DBServer";
import { BaseEntity } from "../Models/BaseEntity";
import { relationalStore, ValueType } from "@kit.ArkData";


export class DeletePredicates<T extends BaseEntity> {
  private DBPredicates?: relationalStore.RdbPredicates;

  constructor(tableName: string) {
    this.DBPredicates = new relationalStore.RdbPredicates(tableName);
  }


  /**
   * 判断field列的值是否为value的字段
   * @param field
   * @param value
   * @returns
   */
  public EqualTo(field: string, value: ValueType): DeletePredicates<T> {
    this.DBPredicates = this.DBPredicates?.equalTo(field, value);
    return this;
  }

  /**
   *配置谓词以匹配数据表的field列中值不为value的字段
   * @param field
   * @param value
   * @returns
   */
  public NotEqualTo(field: string, value: ValueType): DeletePredicates<T> {
    this.DBPredicates = this.DBPredicates?.notEqualTo(field, value);
    return this;
  }

  /**
   *添加左括号
   * @returns
   */
  public BeginWrap(): DeletePredicates<T> {
    this.DBPredicates = this.DBPredicates?.beginWrap();
    return this;
  }

  /**
   * 添加右括号
   * @returns
   */
  public EndWrap(): DeletePredicates<T> {
    this.DBPredicates = this.DBPredicates?.endWrap();
    return this;
  }

  /**
   * 将或条件添加到谓词中。
   * @returns
   */
  public Or(): DeletePredicates<T> {
    this.DBPredicates = this.DBPredicates?.or();
    return this;
  }

  /**
   * 向谓词添加和条件。
   * @returns
   */
  public And(): DeletePredicates<T> {
    this.DBPredicates = this.DBPredicates?.and();
    return this;
  }

  /**
   * 配置谓词以匹配数据表的field列中包含value的字段。
   * @param field
   * @param value
   * @returns
   */
  public Contains(field: string, value: string): DeletePredicates<T> {
    this.DBPredicates = this.DBPredicates?.contains(field, value);
    return this;
  }

  /**
   *配置谓词以匹配数据表的field列中以value开头的字段。
   * @param field
   * @param value
   * @returns
   */
  public BeginsWith(field: string, value: string): DeletePredicates<T> {
    this.DBPredicates = this.DBPredicates?.beginsWith(field, value);
    return this;
  }

  /**
   * 以匹配数据表的field列中以value结尾的字段
   * @param field
   * @param value
   * @returns
   */
  public EndsWith(field: string, value: string): DeletePredicates<T> {
    this.DBPredicates = this.DBPredicates?.endsWith(field, value);
    return this;
  }

  /**
   * 以匹配数据表的field列中值为null的字段。
   * @param field
   * @returns
   */
  public IsNull(field: string): DeletePredicates<T> {
    this.DBPredicates = this.DBPredicates?.isNull(field);
    return this;
  }

  /**
   * 以匹配数据表的field列中值不为null的字段。
   * @param field
   * @returns
   */
  public IsNotNull(field: string): DeletePredicates<T> {
    this.DBPredicates = this.DBPredicates?.isNotNull(field);
    return this;
  }

  /**
   * 以匹配数据表的field列中值类似于value的字段。
   * @param field
   * @param value
   * @returns
   */
  public Like(field: string, value: string): DeletePredicates<T> {
    this.DBPredicates = this.DBPredicates?.like(field, value);
    return this;
  }

  /**
   * 以匹配数据表的field列中值在给定范围内的字段（包含范围边界）
   * @param field
   * @param low
   * @param high
   * @returns
   */
  public Between(field: string, low: ValueType, high: ValueType): DeletePredicates<T> {
    this.DBPredicates = this.DBPredicates?.between(field, low, high);
    return this;
  }

  /**
   * 以匹配数据表的field列中值超出给定范围的字段（不包含范围边界）。
   * @param field
   * @param low
   * @param high
   * @returns
   */
  public NotBetween(field: string, low: ValueType, high: ValueType): DeletePredicates<T> {
    this.DBPredicates = this.DBPredicates?.notBetween(field, low, high);
    return this;
  }

  /**
   * 以匹配数据表的field列中值大于value的字段
   * @param field
   * @param value
   * @returns
   */
  public GreaterThan(field: string, value: ValueType): DeletePredicates<T> {
    this.DBPredicates = this.DBPredicates?.greaterThan(field, value);
    return this;
  }

  /**
   * 以匹配数据表的field列中值小于value的字段
   * @param field
   * @param value
   * @returns
   */
  public LessThan(field: string, value: ValueType): DeletePredicates<T> {
    this.DBPredicates = this.DBPredicates?.lessThan(field, value);
    return this;
  }

  /**
   * 以匹配数据表的field列中值按升序排序的列
   * @param field
   * @returns
   */
  public OrderByAsc(field: string): DeletePredicates<T> {
    this.DBPredicates = this.DBPredicates?.orderByAsc(field);
    return this;
  }

  /**
   *以匹配数据表的field列中值按降序排序的列
   * @param field
   * @returns
   */
  public OrderByDesc(field: string): DeletePredicates<T> {
    this.DBPredicates = this.DBPredicates?.orderByDesc(field);
    return this;
  }

  /**
   * 以过滤重复记录并仅保留其中一个。
   * @returns
   */
  public Distinct(): DeletePredicates<T> {
    this.DBPredicates = this.DBPredicates?.distinct();
    return this;
  }

  /**
   *以匹配数据表的field列中值在给定范围内的字段
   * @param field
   * @param value
   * @returns
   */
  public In(field: string, value: Array<ValueType>): DeletePredicates<T> {
    this.DBPredicates = this.DBPredicates?.in(field, value);
    return this;
  }

  /**
   * 配置为匹配数据字段为ValueType且值超出给定范围的指定字段
   * @param field
   * @param value
   * @returns
   */
  public NotIn(field: string, value: Array<ValueType>): DeletePredicates<T> {
    this.DBPredicates = this.DBPredicates?.notIn(field, value);
    return this;
  }

  /**
   * 以匹配数据表的field列中不包含value的字段。
   * @param field
   * @param value
   * @returns
   */
  public NotContains(field: string, value: string): DeletePredicates<T> {
    this.DBPredicates = this.DBPredicates?.notContains(field, value);
    return this;
  }

  /**
   * 以匹配数据表的field列中值不存在类似于value的字段
   * @param field
   * @param value
   * @returns
   */
  public NotLike(field: string, value: string): DeletePredicates<T> {
    this.DBPredicates = this.DBPredicates?.notLike(field, value);
    return this;
  }

  /**
   * 执行删除方法
   * @returns
   */
  public async DeleteAsync(): Promise<number> {
    try {
      let deleteResult: number | undefined =
        await DBServer.DBStore?.delete(this.DBPredicates as relationalStore.RdbPredicates);
      if (deleteResult) {
        return deleteResult;
      } else {
        return -1;
      }
    } catch (e) {
      console.info(e);
      return -1;
    }
  }
}

DBCommon->QueryPredicates

作用：对RdbPredicates进行封装，实现搜索时候的谓词添加，并提供自定义查询方法

import { relationalStore, ValuesBucket, ValueType } from "@kit.ArkData"
import { DBServer } from "../DBServer";
import { BaseEntity } from "../Models/BaseEntity";
import { ColumnType } from "./ColumnType";
import { ColumnStruct, DBTable, TableStruct } from "./DBTable";

export class QueryPredicates<T extends BaseEntity> {
  private DBPredicates?: relationalStore.RdbPredicates;
  private TableName: string = "";

  constructor(tableName: string) {
    this.DBPredicates = new relationalStore.RdbPredicates(tableName);
    this.TableName = tableName;
  }


  /**
   * 判断field列的值是否为value的字段
   * @param field
   * @param value
   * @returns
   */
  public EqualTo(field: string, value: ValueType): QueryPredicates<T> {
    this.DBPredicates = this.DBPredicates?.equalTo(field, value);
    return this;
  }

  /**
   *配置谓词以匹配数据表的field列中值不为value的字段
   * @param field
   * @param value
   * @returns
   */
  public NotEqualTo(field: string, value: ValueType): QueryPredicates<T> {
    this.DBPredicates = this.DBPredicates?.notEqualTo(field, value);
    return this;
  }

  /**
   *添加左括号
   * @returns
   */
  public BeginWrap(): QueryPredicates<T> {
    this.DBPredicates = this.DBPredicates?.beginWrap();
    return this;
  }

  /**
   * 添加右括号
   * @returns
   */
  public EndWrap(): QueryPredicates<T> {
    this.DBPredicates = this.DBPredicates?.endWrap();
    return this;
  }

  /**
   * 将或条件添加到谓词中。
   * @returns
   */
  public Or(): QueryPredicates<T> {
    this.DBPredicates = this.DBPredicates?.or();
    return this;
  }

  /**
   * 向谓词添加和条件。
   * @returns
   */
  public And(): QueryPredicates<T> {
    this.DBPredicates = this.DBPredicates?.and();
    return this;
  }

  /**
   * 配置谓词以匹配数据表的field列中包含value的字段。
   * @param field
   * @param value
   * @returns
   */
  public Contains(field: string, value: string): QueryPredicates<T> {
    this.DBPredicates = this.DBPredicates?.contains(field, value);
    return this;
  }

  /**
   *配置谓词以匹配数据表的field列中以value开头的字段。
   * @param field
   * @param value
   * @returns
   */
  public BeginsWith(field: string, value: string): QueryPredicates<T> {
    this.DBPredicates = this.DBPredicates?.beginsWith(field, value);
    return this;
  }

  /**
   * 以匹配数据表的field列中以value结尾的字段
   * @param field
   * @param value
   * @returns
   */
  public EndsWith(field: string, value: string): QueryPredicates<T> {
    this.DBPredicates = this.DBPredicates?.endsWith(field, value);
    return this;
  }

  /**
   * 以匹配数据表的field列中值为null的字段。
   * @param field
   * @returns
   */
  public IsNull(field: string): QueryPredicates<T> {
    this.DBPredicates = this.DBPredicates?.isNull(field);
    return this;
  }

  /**
   * 以匹配数据表的field列中值不为null的字段。
   * @param field
   * @returns
   */
  public IsNotNull(field: string): QueryPredicates<T> {
    this.DBPredicates = this.DBPredicates?.isNotNull(field);
    return this;
  }

  /**
   * 以匹配数据表的field列中值类似于value的字段。
   * @param field
   * @param value
   * @returns
   */
  public Like(field: string, value: string): QueryPredicates<T> {
    this.DBPredicates = this.DBPredicates?.like(field, value);
    return this;
  }

  /**
   * 以匹配数据表的field列中值在给定范围内的字段（包含范围边界）
   * @param field
   * @param low
   * @param high
   * @returns
   */
  public Between(field: string, low: ValueType, high: ValueType): QueryPredicates<T> {
    this.DBPredicates = this.DBPredicates?.between(field, low, high);
    return this;
  }

  /**
   * 以匹配数据表的field列中值超出给定范围的字段（不包含范围边界）。
   * @param field
   * @param low
   * @param high
   * @returns
   */
  public NotBetween(field: string, low: ValueType, high: ValueType): QueryPredicates<T> {
    this.DBPredicates = this.DBPredicates?.notBetween(field, low, high);
    return this;
  }

  /**
   * 以匹配数据表的field列中值大于value的字段
   * @param field
   * @param value
   * @returns
   */
  public GreaterThan(field: string, value: ValueType): QueryPredicates<T> {
    this.DBPredicates = this.DBPredicates?.greaterThan(field, value);
    return this;
  }

  /**
   * 以匹配数据表的field列中值小于value的字段
   * @param field
   * @param value
   * @returns
   */
  public LessThan(field: string, value: ValueType): QueryPredicates<T> {
    this.DBPredicates = this.DBPredicates?.lessThan(field, value);
    return this;
  }

  /**
   * 以匹配数据表的field列中值按升序排序的列
   * @param field
   * @returns
   */
  public OrderByAsc(field: string): QueryPredicates<T> {
    this.DBPredicates = this.DBPredicates?.orderByAsc(field);
    return this;
  }

  /**
   *以匹配数据表的field列中值按降序排序的列
   * @param field
   * @returns
   */
  public OrderByDesc(field: string): QueryPredicates<T> {
    this.DBPredicates = this.DBPredicates?.orderByDesc(field);
    return this;
  }

  /**
   * 以过滤重复记录并仅保留其中一个。
   * @returns
   */
  public Distinct(): QueryPredicates<T> {
    this.DBPredicates = this.DBPredicates?.distinct();
    return this;
  }

  /**
   *以匹配数据表的field列中值在给定范围内的字段
   * @param field
   * @param value
   * @returns
   */
  public In(field: string, value: Array<ValueType>): QueryPredicates<T> {
    this.DBPredicates = this.DBPredicates?.in(field, value);
    return this;
  }

  /**
   * 配置为匹配数据字段为ValueType且值超出给定范围的指定字段
   * @param field
   * @param value
   * @returns
   */
  public NotIn(field: string, value: Array<ValueType>): QueryPredicates<T> {
    this.DBPredicates = this.DBPredicates?.notIn(field, value);
    return this;
  }

  /**
   * 以匹配数据表的field列中不包含value的字段。
   * @param field
   * @param value
   * @returns
   */
  public NotContains(field: string, value: string): QueryPredicates<T> {
    this.DBPredicates = this.DBPredicates?.notContains(field, value);
    return this;
  }

  /**
   * 以匹配数据表的field列中值不存在类似于value的字段
   * @param field
   * @param value
   * @returns
   */
  public NotLike(field: string, value: string): QueryPredicates<T> {
    this.DBPredicates = this.DBPredicates?.notLike(field, value);
    return this;
  }

  /**
   * 获取数据
   * @returns
   */
  public async GetAsync(): Promise<T[]> {
    let dbResult: relationalStore.ResultSet | undefined =
      await DBServer.DBStore?.query(this.DBPredicates as relationalStore.RdbPredicates);
    if (dbResult == undefined) {
      return [];
    }
    let tableStruct: TableStruct | undefined = await DBTable.GetTableStructByTableName(this.TableName);
    if (tableStruct) {
      return await this.ParseResult(dbResult, tableStruct.Columns);
    } else {
      throw new Error("数据库结构为空，需重新检查适配")
    }
    return [];
  }


  /**
   * 查询结果转化
   * @param resultSet
   * @param structs
   * @returns
   */
  private async ParseResult(resultSet: relationalStore.ResultSet, structs: Array<ColumnStruct>): Promise<T[]> {
    let rows = new Array<T>()
    while (!resultSet.isAtLastRow) {
      resultSet.goToNextRow()
      let row: ValuesBucket = {};
      for (let columIndex = 0; columIndex < resultSet.columnNames.length; columIndex++) {
        const columnName = resultSet.columnNames[columIndex]
        for (let strIndex = 0; strIndex < structs.length; strIndex++) {
          const item: ColumnStruct = structs[strIndex]
          let value: ValueType = "";
          if (item.ColumnName == columnName) {
            if (item.Type == ColumnType.INTEGER) {
              value = resultSet.getLong(resultSet.getColumnIndex(columnName));
              row[columnName] = value;
            } else if (item.Type == ColumnType.REAL) {
              value = resultSet.getDouble(resultSet.getColumnIndex(columnName))
              row[columnName] = value;
            } else if (item.Type == ColumnType.BLOB) {
              value = resultSet.getString(resultSet.getColumnIndex(columnName))
              row[columnName] = value;
            } else if (item.Type == ColumnType.TEXT) {
              value = resultSet.getString(resultSet.getColumnIndex(columnName))
              row[columnName] = value;
            }
          }
        }
      }

      rows.push(JSON.parse(JSON.stringify(row)) as T)
    }
    return rows
  }
}

DBCommon->UpdatePredicates

作用：对RdbPredicates进行封装，实现搜索时候的谓词添加，并提供自定义更新方法。

import { BaseEntity } from "../Models/BaseEntity";
import { relationalStore, ValuesBucket, ValueType } from "@kit.ArkData";
import { DBServer } from "../DBServer";

export class UpdatePredicates<T extends BaseEntity> {
  private DBPredicates?: relationalStore.RdbPredicates;
  private Values: ValuesBucket = {}

  constructor(tableName: string, values: ValuesBucket) {
    this.DBPredicates = new relationalStore.RdbPredicates(tableName);
    this.Values = values;
  }


  /**
   * 判断field列的值是否为value的字段
   * @param field
   * @param value
   * @returns
   */
  public EqualTo(field: string, value: ValueType): UpdatePredicates<T> {
    this.DBPredicates = this.DBPredicates?.equalTo(field, value);
    return this;
  }

  /**
   *配置谓词以匹配数据表的field列中值不为value的字段
   * @param field
   * @param value
   * @returns
   */
  public NotEqualTo(field: string, value: ValueType): UpdatePredicates<T> {
    this.DBPredicates = this.DBPredicates?.notEqualTo(field, value);
    return this;
  }

  /**
   *添加左括号
   * @returns
   */
  public BeginWrap(): UpdatePredicates<T> {
    this.DBPredicates = this.DBPredicates?.beginWrap();
    return this;
  }

  /**
   * 添加右括号
   * @returns
   */
  public EndWrap(): UpdatePredicates<T> {
    this.DBPredicates = this.DBPredicates?.endWrap();
    return this;
  }

  /**
   * 将或条件添加到谓词中。
   * @returns
   */
  public Or(): UpdatePredicates<T> {
    this.DBPredicates = this.DBPredicates?.or();
    return this;
  }

  /**
   * 向谓词添加和条件。
   * @returns
   */
  public And(): UpdatePredicates<T> {
    this.DBPredicates = this.DBPredicates?.and();
    return this;
  }

  /**
   * 配置谓词以匹配数据表的field列中包含value的字段。
   * @param field
   * @param value
   * @returns
   */
  public Contains(field: string, value: string): UpdatePredicates<T> {
    this.DBPredicates = this.DBPredicates?.contains(field, value);
    return this;
  }

  /**
   *配置谓词以匹配数据表的field列中以value开头的字段。
   * @param field
   * @param value
   * @returns
   */
  public BeginsWith(field: string, value: string): UpdatePredicates<T> {
    this.DBPredicates = this.DBPredicates?.beginsWith(field, value);
    return this;
  }

  /**
   * 以匹配数据表的field列中以value结尾的字段
   * @param field
   * @param value
   * @returns
   */
  public EndsWith(field: string, value: string): UpdatePredicates<T> {
    this.DBPredicates = this.DBPredicates?.endsWith(field, value);
    return this;
  }

  /**
   * 以匹配数据表的field列中值为null的字段。
   * @param field
   * @returns
   */
  public IsNull(field: string): UpdatePredicates<T> {
    this.DBPredicates = this.DBPredicates?.isNull(field);
    return this;
  }

  /**
   * 以匹配数据表的field列中值不为null的字段。
   * @param field
   * @returns
   */
  public IsNotNull(field: string): UpdatePredicates<T> {
    this.DBPredicates = this.DBPredicates?.isNotNull(field);
    return this;
  }

  /**
   * 以匹配数据表的field列中值类似于value的字段。
   * @param field
   * @param value
   * @returns
   */
  public Like(field: string, value: string): UpdatePredicates<T> {
    this.DBPredicates = this.DBPredicates?.like(field, value);
    return this;
  }

  /**
   * 以匹配数据表的field列中值在给定范围内的字段（包含范围边界）
   * @param field
   * @param low
   * @param high
   * @returns
   */
  public Between(field: string, low: ValueType, high: ValueType): UpdatePredicates<T> {
    this.DBPredicates = this.DBPredicates?.between(field, low, high);
    return this;
  }

  /**
   * 以匹配数据表的field列中值超出给定范围的字段（不包含范围边界）。
   * @param field
   * @param low
   * @param high
   * @returns
   */
  public NotBetween(field: string, low: ValueType, high: ValueType): UpdatePredicates<T> {
    this.DBPredicates = this.DBPredicates?.notBetween(field, low, high);
    return this;
  }

  /**
   * 以匹配数据表的field列中值大于value的字段
   * @param field
   * @param value
   * @returns
   */
  public GreaterThan(field: string, value: ValueType): UpdatePredicates<T> {
    this.DBPredicates = this.DBPredicates?.greaterThan(field, value);
    return this;
  }

  /**
   * 以匹配数据表的field列中值小于value的字段
   * @param field
   * @param value
   * @returns
   */
  public LessThan(field: string, value: ValueType): UpdatePredicates<T> {
    this.DBPredicates = this.DBPredicates?.lessThan(field, value);
    return this;
  }

  /**
   * 以匹配数据表的field列中值按升序排序的列
   * @param field
   * @returns
   */
  public OrderByAsc(field: string): UpdatePredicates<T> {
    this.DBPredicates = this.DBPredicates?.orderByAsc(field);
    return this;
  }

  /**
   *以匹配数据表的field列中值按降序排序的列
   * @param field
   * @returns
   */
  public OrderByDesc(field: string): UpdatePredicates<T> {
    this.DBPredicates = this.DBPredicates?.orderByDesc(field);
    return this;
  }

  /**
   * 以过滤重复记录并仅保留其中一个。
   * @returns
   */
  public Distinct(): UpdatePredicates<T> {
    this.DBPredicates = this.DBPredicates?.distinct();
    return this;
  }

  /**
   *以匹配数据表的field列中值在给定范围内的字段
   * @param field
   * @param value
   * @returns
   */
  public In(field: string, value: Array<ValueType>): UpdatePredicates<T> {
    this.DBPredicates = this.DBPredicates?.in(field, value);
    return this;
  }

  /**
   * 配置为匹配数据字段为ValueType且值超出给定范围的指定字段
   * @param field
   * @param value
   * @returns
   */
  public NotIn(field: string, value: Array<ValueType>): UpdatePredicates<T> {
    this.DBPredicates = this.DBPredicates?.notIn(field, value);
    return this;
  }

  /**
   * 以匹配数据表的field列中不包含value的字段。
   * @param field
   * @param value
   * @returns
   */
  public NotContains(field: string, value: string): UpdatePredicates<T> {
    this.DBPredicates = this.DBPredicates?.notContains(field, value);
    return this;
  }

  /**
   * 以匹配数据表的field列中值不存在类似于value的字段
   * @param field
   * @param value
   * @returns
   */
  public NotLike(field: string, value: string): UpdatePredicates<T> {
    this.DBPredicates = this.DBPredicates?.notLike(field, value);
    return this;
  }

  /**
   * 执行更新方法
   * @returns
   */
  public async UpdateAsync(): Promise<number> {
    try {
      let result: number | undefined =
        await DBServer.DBStore?.update(this.Values, this.DBPredicates as relationalStore.RdbPredicates);
      if (result) {
        return result;
      }
      return -1;
    } catch (e) {
      console.info(e)
      return -1;
    }
  }
}

Annotations->Columns

作用：属性的装饰器。用来定义数据表实体中每一列的数据结构

import { ColumnType } from "../DBCommon/ColumnType"
import { ColumnStruct, DBTable } from "../DBCommon/DBTable";

export const Columns = (value: ColumnsValue): PropertyDecorator =>
(target: object, primaryKey: string | Symbol) => {
  if (value && !value.ColumnName) {
    value.ColumnName = primaryKey as string;
  }
  let columnStruct: ColumnStruct = new ColumnStruct();
  columnStruct.Name = primaryKey as string;
  columnStruct.ColumnName = value.ColumnName;
  columnStruct.Type = value.Type;
  columnStruct.IsId = value.IsId;
  columnStruct.Autoincrement = value.Autoincrement;
  DBTable.AddColumn(target.constructor.name, columnStruct);
}

export class ColumnsValue {
  /**
   * 数据表名称
   */
  ColumnName?: string;
  /**
   * 数据表类型
   */
  Type: ColumnType = ColumnType.INTEGER;
  /**
   * 是否是ID
   */
  IsId?: boolean = false;
  /**
   * 是否自增长(是ID类才会生效)
   */
  Autoincrement?: boolean = true;
}

Annotations->Table

作用：class装饰器。用来定义数据表名字，可以自定义名字，也可以默认当前class名字。

import { DBTable } from "../DBCommon/DBTable";

export const Table = (tableName?: string): ClassDecorator => (target: Function) => {
  let name: string = target.name;
  if (tableName != undefined) {
    name = tableName;
  }
  target.prototype['Table'] = name;
  DBTable.AddTable(target.name, name);
}

实例使用

实现数据库初始化、数据库增删改查操作

EntryAbility

在EntryAbility中的OnCreate中进行初始化

onCreate(want: Want, launchParam: AbilityConstant.LaunchParam): void {
    this.context.getApplicationContext().setColorMode(ConfigurationConstant.ColorMode.COLOR_MODE_NOT_SET);
    hilog.info(0x0000, 'testTag', '%{public}s', 'Ability onCreate');
    DBServer.Init(this.context);
  }

定义数据库表实体类

• @Table()：定义当前class为数据库表实体类。默认数据库表名为class名字
• @Columns()：定义数据表列数据。默认列数据名为属性名字

import { Columns, Table, ColumnType, BaseEntity } from "dbhelper"

@Table()
export class TbDrug extends BaseEntity {
  @Columns({
    Type: ColumnType.INTEGER,
    IsId: true,
    Autoincrement: true
  })
  Id?: number
  @Columns({
    Type: ColumnType.TEXT
  })
  Name?: string
  @Columns({
    Type: ColumnType.REAL
  })
  RemainingNumber?: number
  @Columns({
    Type: ColumnType.TEXT
  })
  CreateTime?: string
  @Columns({
    Type: ColumnType.TEXT
  })
  ExpirationTime?: string
  @Columns({
    Type: ColumnType.TEXT
  })
  IconPath?: string
  @Columns({
    Type: ColumnType.INTEGER
  })
  Unit?: number

  constructor() {
    super();
  }
}

页面使用

import { DBTable, DBServer } from 'dbhelper';
import { TbDrug } from '../HODBModel/TbDrug';
import { JSON } from '@kit.ArkTS';
import { promptAction } from '@kit.ArkUI';

@Entry
@ComponentV2
struct Index {
  @Local DrugName: string = '';
  @Local DrugNumber: string = '';

  build() {
    Column({ space: 10 }) {
      TextInput({ placeholder: "药品名字", text: $$this.DrugName })
      TextInput({ placeholder: "药品数量", text: $$this.DrugNumber }).type(InputType.Number)
      Button("添加药品").onClick(async () => {
        let drug: TbDrug = new TbDrug();
        drug.Name = this.DrugName;
        drug.RemainingNumber = Number.parseFloat(this.DrugNumber);
        let result: number = await DBServer.InsertAsync<TbDrug>(drug);
        if (result != -1) {
          promptAction.showToast({ message: `添加成功：ID：${result}` })
        } else {
          promptAction.showToast({ message: `添加失败` })
        }
      })
      Button("删除指定名字的所有药品").onClick(async () => {
        try {
          let result: number = await DBServer.Delete<TbDrug>("TbDrug")
            .EqualTo("Name", this.DrugName)
            .DeleteAsync();
          promptAction.showToast({ message: `删除药品数量：${result}` })
        } catch (e) {
          console.info(e)
        }
      })
      Button("按药品名字修改药品数量").onClick(async () => {
        let result: TbDrug[] = await DBServer.Query<TbDrug>("TbDrug")
          .EqualTo("Name", this.DrugName)
          .GetAsync();
        result.forEach(async (x, i) => {
          x.RemainingNumber = Number.parseFloat(this.DrugNumber);
          let result: number = await DBServer.Update<TbDrug>("TbDrug", x)
            .EqualTo("Id", x.Id as number)
            .UpdateAsync();
          console.info(`更新成功 ${result}`)
        })
      })

      Button("查询所有的药品信息").onClick(async () => {
        try {
          let result: TbDrug[] = await DBServer.Query<TbDrug>("TbDrug")
            .GetAsync();
          result.forEach((x, i) => {
            console.info(JSON.stringify(x))
          })
          promptAction.showToast({ message: `药品数量：${result.length}` })
        } catch (e) {
          console.info(e)
        }
      })


    }
    .height('100%')
    .width('100%')
  }
}

总结

上面的代码是全开源的，可以通过源码下载 下载代码来自行改装，上面只是初步实现了增删改查，后续还会有持续的更新操作，比如外键适配等操作，如果有自定义需求或者bug也欢迎提Issues。