OpenHarmonyOS 鸿蒙开发关系型数据库简单使用

下载文件数据库储存操作：配合super_fast_file_trans使用
super_fast_file_trans

import { relationalStore, ValueType } from "@kit.ArkData";
import { common } from "@kit.AbilityKit";
import { LogUtil } from "./LogUtil";
import { FileUtil } from "./FileUtil";
import { GlobalContext } from "./GlobalContext";
import { DownloadManager } from "@hadss/super_fast_file_trans";
import { url } from "@kit.ArkTS";
import { PreferencesUtil } from "./PreferencesUtil";
import { Constant } from "../model/Constant";

const STORE_CONFIG: relationalStore.StoreConfig = {
  name: 'sfft_download.db',
  securityLevel: relationalStore.SecurityLevel.S1,
  encrypt: false
};

export class RDBUtils {
  private static instance: RDBUtils | null = null;
  // 文件下载使用relationalStore，如需操作其他数据库单独初始化
  private downloadRdbStore: relationalStore.RdbStore | undefined;

  public static getInstance(): RDBUtils {
    if (!RDBUtils .instance) {
      RDBUtils .instance = new RDBUtils ();
    }
    return RDBUtils .instance;
  }

  public async initDownloadRdbStore(context: common.Context) {
    try {
      this.downloadRdbStore =
        await relationalStore.getRdbStore(context, STORE_CONFIG);
      this.updateDownloadUrl()
    } catch (err) {
      LogUtil.error(`initDownloadRdbStore error,code: ${err.code}, message: ${err.message}`);
    }
  }

  public getDownloadRdbStore(){
    return this.downloadRdbStore
  }

  public async clearDownloadCache(): Promise<void> {
    try {
      // 1. 获取下载任务数据
      const downloadFile = await this.getDownloadFileNames();
      // 2. 获取本地下载目录中的文件
      const context = GlobalContext.getContext();
      const pickerPath = `${GlobalContext.getDownloadPathByPicker()}`;
      const localDownloadFile: string[] = []
      if (FileUtil.accessSync(pickerPath + 'apk/')) {
        const apkFiles = FileUtil.listFileSync(pickerPath + 'apk/', { recursion: true }) || [];
        localDownloadFile.push(...apkFiles)
      }
      if (FileUtil.accessSync(pickerPath + 'resource/')) {
        const resourceFiles = FileUtil.listFileSync(pickerPath + 'resource/', { recursion: true }) || [];
        localDownloadFile.push(...resourceFiles)
      }
      // 3. 如果没有本地文件，清理所有下载记录
      if (localDownloadFile.length === 0) {
        await DownloadManager.getInstance().cleanAll(context);
        await DownloadManager.getInstance().init(context)
        return;
      }

      // 4. 对比数据库和本地文件，清理不存在的记录
      const cleanupPromises: Promise<void>[] = [];
      for (const fileName of Array.from(downloadFile.keys())) {
        if (!localDownloadFile.includes(`/${fileName}`) && downloadFile.get(fileName)) {
          // 并行执行数据库清理操作
          let resultSet = await this.getData('sfft_download_block', 'host_id', downloadFile.get(fileName)!!)
          resultSet.goToNextRow()
          const content_length = resultSet.getLong(resultSet.getColumnIndex("content_length"));
          const content_offset = resultSet.getLong(resultSet.getColumnIndex("current_offset"));
          if (content_length === content_offset) {
            cleanupPromises.push(
              DownloadRDBUtils.getInstance().deleteByKv('*************', 'file_name', fileName)
            );
            cleanupPromises.push(
              DownloadRDBUtils.getInstance().deleteByKv('sfft_download_block', 'host_id', downloadFile.get(fileName)!!)
            );
          }
        }
      }
      // 等待所有清理操作完成
      if (cleanupPromises.length > 0) {
        await Promise.all(cleanupPromises);
      }
    } catch (error) {
      LogUtil.error('clearDownloadCache error:', error);
    }
  }

  // 删除数据库
  public deleteDB(context: common.Context, dbName: string) {
    relationalStore.deleteRdbStore(context, dbName, (err) => {
      if (err) {
        LogUtil.error(`Failed to delete RdbStore. Code:${err.code}, message:${err.message}`);
        return;
      }
      LogUtil.info('Succeeded in deleting RdbStore ==> ' + dbName);
    });
  }

  async deleteByKv(tableName: string, key: string, value: ValueType) {
    let predicates = new relationalStore.RdbPredicates(tableName);
    predicates.equalTo(key, value)
    if (this.downloadRdbStore != undefined) {
      try {
        let rows = this.downloadRdbStore.deleteSync(predicates);
        LogUtil.info(`${tableName}, Delete rows: ${rows}} `)
      } catch (err) {
        LogUtil.error(`Failed to delete data. Code:${err.code}, message:${err.message}`);
      }
    }
  }


  // 向数据库中插入数据
  async insertData(tableName: string, valueBucket: relationalStore.ValuesBucket) {
    if (this.downloadRdbStore != undefined) {
      this.downloadRdbStore.insert(tableName, valueBucket, (err, rowId: number) => {
        if (err) {
          LogUtil.error(`Failed to insert data. Code:${err.code}, message:${err.message}`);
          return;
        }
        LogUtil.info(`Succeeded in inserting data. rowId:${rowId}`);
      })
    }
  }


  // 对数据进行修改
  async updateData(tableName: string, valueBucket: relationalStore.ValuesBucket, key: string, value: ValueType) {
    let predicates = new relationalStore.RdbPredicates(tableName); // 创建表tableName的predicates
    predicates.equalTo(key, value); // 匹配表tableName中key为val的字段
    if (this.downloadRdbStore != undefined) {
      this.downloadRdbStore.update(valueBucket, predicates, (err, rowId: number) => {
        if (err) {
          LogUtil.error(`Failed to insert data. Code:${err.code}, message:${err.message}`);
          return;
        }
        LogUtil.info(`Succeeded in inserting data. rowId:${rowId}`);
      })
    }
  }

  async updateDownloadUrl() {
    let downloadUrls = await this.queryDownloadUrl()
    if (downloadUrls.length > 0) {
      downloadUrls.forEach(downloadUrl => {
        try {
          let urlObject = url.URL.parseURL(downloadUrl);
          let ip = PreferencesUtil.getStringSync("ip", Constant.IP);
          let port = PreferencesUtil.getStringSync("port", Constant.PORT);
          if (urlObject.host !== ip || urlObject.port !== port) {
            urlObject.host = ip
            urlObject.port = port
            const valueBucket: relationalStore.ValuesBucket = {
              'URL': urlObject.toString()
            };
            this.updateData('*************', valueBucket, 'url', downloadUrl)
          }
        } catch (error) {
          LogUtil.error(`Invalid URL: ${error.message}`);
        }
      })

    }
  }


  async queryDownloadUrl() {
    let predicates = new relationalStore.RdbPredicates('*************');
    let downloadUrl: string[] = []
    if (this.downloadRdbStore != undefined) {
      try {
        const resultSet = await this.downloadRdbStore.query(predicates);
        try {
          while (resultSet.goToNextRow()) {
            const url = resultSet.getString(resultSet.getColumnIndex("url"));
            downloadUrl.push(url)
          }
        } catch (err) {
          LogUtil.error(`queryDownloadUrl failed, code is ${err.code},message is ${err.message}`);
        } finally {
          // 释放数据集的内存，若不释放可能会引起fd泄露与内存泄露
          resultSet.close();
        }
      } catch (err) {
        LogUtil.error(`Failed to queryDownloadUrl data. Code:${err.code}, message:${err.message}`);
      }
    }
    return Promise.resolve(downloadUrl);
  }


  async getDownloadFileNames(tableName: string = '*************'): Promise<Map<string, number>> {
    let predicates = new relationalStore.RdbPredicates(tableName);
    let fileNameMap: Map<string, number> = new Map()
    if (this.downloadRdbStore != undefined) {
      try {
        const resultSet = await this.downloadRdbStore.query(predicates);
        try {
          while (resultSet.goToNextRow()) {
            const id = resultSet.getLong(resultSet.getColumnIndex("id"));
            const name = resultSet.getString(resultSet.getColumnIndex("file_name"));
            fileNameMap.set(name, id)
          }
        } catch (err) {
          LogUtil.error(`Query failed, code is ${err.code},message is ${err.message}`);
        } finally {
          // 释放数据集的内存，若不释放可能会引起fd泄露与内存泄露
          resultSet.close();
        }
      } catch (err) {
        LogUtil.error(`Failed to getDownloadFileNames data. Code:${err.code}, message:${err.message}`);
      }
    }
    return Promise.resolve(fileNameMap);
  }

  async getData(tableName: string, key: string, val: ValueType,
    atomArr: Array<string> = ['*']): Promise<relationalStore.ResultSet> {
    let predicates = new relationalStore.RdbPredicates(tableName);
    predicates.equalTo(key, val);
    if (this.downloadRdbStore != undefined) {
      return new Promise((resolve, reject) => {
        this.downloadRdbStore?.query(predicates, atomArr, (err, resultSet) => {
          if (err) {
            LogUtil.error(`Failed to query data. Code:${err.code}, message:${err.message}`);
            reject(err);
            return;
          }
          resolve(resultSet);
          LogUtil.info(`ResultSet column names: ${resultSet.columnNames}, column count: ${resultSet.columnCount}`);
        })
      })
    }
    return Promise.reject('downloadRdbStore is undefined')
  }
}

为关系型数据库动态添加列：

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

export class DatabaseManager {
  /**
   * 动态添加可为空的列
   * @param store RdbStore实例
   * @param tableName 表名
   * @param columnDef 列定义
   */
  async addNullableColumn(
    store: relationalStore.RdbStore,
    tableName: string,
    columnDef: ColumnDefinition
  ): Promise<boolean> {

    try {
      // 1. 检查表是否存在
      if (!await this.tableExists(store, tableName)) {
        console.error(` djj djj Table ${tableName} does not exist`);
        return false;
      }

      // 2. 检查列是否已存在
      if (await this.columnExists(store, tableName, columnDef.columnName)) {
        console.info(` djj  djj djj Column ${columnDef.columnName} already exists in table ${tableName}`);
        return true;
      }

      // 3. 构建 ALTER TABLE 语句
      let alterSql = `ALTER TABLE ${tableName} ADD COLUMN ${columnDef.columnName} ${columnDef.columnType}`;

      // 添加约束条件
      if (columnDef.notNull) {
        alterSql += ' NOT NULL';
      }

      if (columnDef.defaultValue !== null && columnDef.defaultValue !== undefined) {
        // 处理不同类型的默认值
        if (typeof columnDef.defaultValue === 'string') {
          alterSql += ` DEFAULT '${columnDef.defaultValue}'`;
        } else if (typeof columnDef.defaultValue === 'number') {
          alterSql += ` DEFAULT ${columnDef.defaultValue}`;
        } else if (typeof columnDef.defaultValue === 'boolean') {
          alterSql += ` DEFAULT ${columnDef.defaultValue ? 1 : 0}`;
        } else if (columnDef.defaultValue === null) {
          alterSql += ' DEFAULT NULL';
        }
      } else if (!columnDef.notNull) {
        // 如果不为 NOT NULL 且没有指定默认值，默认可为空
        alterSql += ' DEFAULT NULL';
      }

      console.info(` djj Executing SQL: ${alterSql}`);

      // 4. 执行 SQL
      await store.executeSql(alterSql);

      // 5. 验证列是否添加成功
      const columnAdded = await this.columnExists(store, tableName, columnDef.columnName);
      if (columnAdded) {
        console.info(` djj  djjSuccessfully added nullable column: ${columnDef.columnName}`);
        return true;
      } else {
        console.error(` djj Failed to verify column addition: ${columnDef.columnName}`);
        return false;
      }
    } catch (error) {
      console.error(` djj Error adding column ${columnDef.columnName}:`, error);
    }
    return false;
  }

  /**
   * 检查表是否存在
   */
  private async tableExists(
    store: relationalStore.RdbStore,
    tableName: string
  ): Promise<boolean> {
    const sql = `SELECT name FROM sqlite_master WHERE type='table' AND name=?`;
    const resultSet = await store.querySql(sql, [tableName]);
    const exists = resultSet.rowCount > 0;
    resultSet.close();
    return exists;
  }

  /**
   * 检查列是否存在
   */
  private async columnExists(
    store: relationalStore.RdbStore,
    tableName: string,
    columnName: string
  ): Promise<boolean> {
    const sql = `PRAGMA table_info(${tableName})`;
    const resultSet = await store.querySql(sql);

    let exists = false;
    while (resultSet.goToNextRow()) {
      const name = resultSet.getString(resultSet.getColumnIndex('name'));
      if (name === columnName) {
        exists = true;
        break;
      }
    }
    resultSet.close();
    return exists;
  }

  /**
   * 添加 appIcon 列（可为空）
   */
  async addAppIconColumn(store: relationalStore.RdbStore,): Promise<boolean> {
    const columnDef: ColumnDefinition = {
      columnName: 'appIcon',
      columnType: 'TEXT',
      defaultValue: null, // 默认值为 NULL
      notNull: false      // 允许为空
    };

    return await this.addNullableColumn(store, '**********', columnDef);
  }

  /**
   * 批量添加多个可为空的列
   */
  async addMultipleNullableColumns(store: relationalStore.RdbStore,columns: ColumnDefinition[]): Promise<boolean> {
    let allSuccess = true;
    for (const columnDef of columns) {
      try {
        const success = await this.addNullableColumn(
          store,
          '**********',
          columnDef
        );
        if (!success) {
          allSuccess = false;
          console.error(` djj Failed to add column: ${columnDef.columnName}`);
        }
      } catch (error) {
        console.error(` djj Error adding column ${columnDef.columnName}:`, error);
        allSuccess = false;
      }
    }

    return allSuccess;
  }
}

// 列定义接口
interface ColumnDefinition {
  columnName: string;
  columnType: 'TEXT' | 'INTEGER' | 'REAL' | 'BLOB';
  defaultValue?: string | number | boolean | null;
  notNull?: boolean;
}