基于OpenHarmony的RdbStore工具类

环境：OpenHarmony 4.0
设备：润和大禹200开发板
项目地址

1.功能实现

实现对于relationalStore.RdbStore的获取，添加，修改，删除等基本功能

2.代码实现

(1)成员变量与初始化

  private context: common.UIAbilityContext | undefined = undefined
  // 存储已创建的 RdbStore
  private rdbMap = new Map<string,relationalStore.RdbStore>()
  private currentRDB: string = 'default'
  // 初始化 UIAbilityContext
  init(context: common.UIAbilityContext) {
    this.context = context
  }

(2)创建RdbStore

  /**
   * 创建数据库/获取连接
   * @param rdbName
   * @param config
   * @returns
   */
  async createRDB(rdbName: string, config ?: relationalStore.StoreConfig): Promise<relationalStore.RdbStore> {
    let promise: Promise<relationalStore.RdbStore> = new Promise(async (resolve, reject) => {
      if (!this.context) {
        console.error(TAG, '  (init) ERROR => Context 未获取')
        reject(TAG + '  (init) ERROR => Context 未获取')
      }
      this.currentRDB = rdbName
      const STORE_CONFIG: relationalStore.StoreConfig = config ? config : {name: rdbName, securityLevel: 1}
      try {
        let rdb = this.rdbMap.get(rdbName)
        if (!rdb) {
          rdb = await relationalStore.getRdbStore(this.context, STORE_CONFIG)
          this.rdbMap.set(rdbName, rdb)
        }
        console.info(TAG, `  (createRDB)  SUCCESS TO CREATE: ${rdbName} => 创建连接成功`)
        resolve(rdb)
      } catch (err) {
        let code = (err as BusinessError).code
        let message = (err as BusinessError).message
        console.error(TAG, `  (createRDB)  FAIL TO CREATE: ${rdbName} => code: ${code}, message: ${message}`)
        reject(err as BusinessError)
      }
    })
    return promise
  }

(3)获取/切换连接

  /**
   * 获取/切换 RDB数据库
   * @param rdbName
   * @returns RdbStore
   */
  async getConnection(rdbName: string): Promise<relationalStore.RdbStore> {
    try {
      this.currentRDB = rdbName
      let rdb = this.rdbMap.get(rdbName)
      if (!rdb) {
        console.error(TAG, `  (getConnection)   FAIL TO GET => ${this.currentRDB} RDB连接不存在 重新创建`)
        rdb = await this.createRDB(rdbName)
      }
      console.info(TAG, `  (getConnection)   SUCCESS TO GET => ${this.currentRDB} RDB连接获取成功`)
      return Promise.resolve(rdb)
    } catch (err) {
      let code = (err as BusinessError).code
      let message = (err as BusinessError).message
      console.error(TAG, `  (getConnection)  FAIL TO GET: ${this.currentRDB} => code: ${code}, message: ${message}`)
      return Promise.reject(err as BusinessError)
    }
  }

(4)对象=>ValueBucket 数组=>ValueBuckets

  /**
   * Object => ValueBucket
   * @param arr
   * @returns ValueBucket
   */
  private obj2ValueBucket <T> (obj: T) {
    let valuesBucket: ValuesBucket = {}
    // 获取对象属性/成员变量
    let keys: string[] = Object.keys(obj)
    for (let key of keys) {
      // 获取键值对
      let value: ValueType = obj[key]
      // 将键值对添加到ValuesBucket对象中
      valuesBucket[key] = value
    }
    return valuesBucket
  }

  /**
   * Array => ValueBucket[]
   * @param arr
   * @returns ValueBucket[]
   */
  private array2ValueBuckets <T> (arr: Array<T>) {
    let valuesBuckets: ValuesBucket[] = []
    // 遍历对象集合
    for (let obj of arr) {
      valuesBuckets.push(this.obj2ValueBucket <T> (obj))
    }
    // 返回ValuesBucket对象
    return valuesBuckets
  }

(5)ResultSet => Array

==注意: 由于ResultSet的getBlob()方法在获取integer类型的字段无法获取,所以只能使用getString()==

  /**
   * ResultSet => Array
   * @param resultSet
   * @returns 数组
   */
  private resultSet2Array <T> (resultSet: relationalStore.ResultSet) : Array<T> {
    let arr: T[] = []
    if (resultSet.rowCount == 0){
      console.error('result 为 null');
    }
    resultSet.goToFirstRow()
    let str : string = '{'
    do {
      for (let index = 0; index < resultSet.columnCount; index++) {

        str += `"${resultSet.getColumnName(index)}":"${resultSet.getString(index)}"`
        if (index != resultSet.columnCount - 1) {
          str += ','
        }
      }
      str +='}'
      let obj: T = JSON.parse(str)
      arr.push(obj)
      console.log(JSON.stringify(obj));
      str = '{'
    } while (resultSet.goToNextRow())
    return arr
  }

(6)对象插入

  /**
   * 插入对象
   * @param tableName
   * @param obj
   * @returns
   */
  async insert <T> (tableName: string, obj: T): Promise<number> {
    try {
      // 默认使用最近一次连接
      let rdb = this.rdbMap.get(this.currentRDB)
      if (!rdb) {
        console.error(TAG, `  (insert)   FAIL TO INSERT => ${this.currentRDB} 未获取RDB连接`)
        return Promise.reject(TAG + `  (insert)   FAIL TO INSERT => ${this.currentRDB} 未获取RDB连接`)
      }
      let value: ValuesBucket = this.obj2ValueBucket <T> (obj)
      let insertNum: number = await rdb.insert(tableName, value)
      console.info(TAG, `  (batchInsert)   SUCCESS TO INSERT => ${this.currentRDB} ${tableName} INSERT ${insertNum}`)
      return Promise.resolve(insertNum)
    } catch (err) {
      let code = (err as BusinessError).code
      let message = (err as BusinessError).message
      console.error(TAG, `  (insert)  FAIL TO INSERT: ${this.currentRDB} => code: ${code}, message: ${message}`)
      return Promise.reject(err as BusinessError)
    }
  }

(7)数组批量插入

  /**
   * 批量插入数组
   * @param tableName
   * @param arr
   * @returns 插入成功数量
   */
  async batchInsert <T> (tableName: string, arr: T[]): Promise<number> {
    try {
      // 默认使用最近一次连接
      let rdb = this.rdbMap.get(this.currentRDB)
      let values: ValuesBucket[] = this.array2ValueBuckets <T> (arr)
      if (!rdb) {
        console.error(TAG, `  (batchInsert)   FAIL TO BATCHINSERT => ${this.currentRDB} 未获取RDB连接`)
        return Promise.reject(TAG + `  (batchInsert)   FAIL TO BATCHINSERT => ${this.currentRDB} 未获取RDB连接`)
      }
      let insertNum: number = await rdb.batchInsert(tableName, values)
      console.info(TAG, `  (batchInsert)   SUCCESS TO BATCHINSERT => ${this.currentRDB} ${tableName} BATCHINSERT ${insertNum}}`)
      return Promise.resolve(insertNum)
    } catch (err) {
      let code = (err as BusinessError).code
      let message = (err as BusinessError).message
      console.error(TAG, `  (batchInsert)  FAIL TO BATCHINSERT: ${this.currentRDB} => code: ${code}, message: ${message}`)
      return Promise.reject(err as BusinessError)
    }
  }

(8) 查询数据

  /**
   * 查询
   * @param predicates
   * @param columns
   * @returns 所有数据 => 数组
   */
  async queryArray <T> (predicates: relationalStore.RdbPredicates, columns ?: Array<string>): Promise<T[]> {
    try {
      // 默认使用最近一次连接
      let rdb = this.rdbMap.get(this.currentRDB)
      if (!rdb) {
        console.error(TAG, `  (queryArray)   FAIL TO QUERY => ${this.currentRDB} 未获取RDB连接`)
        return Promise.reject(TAG +  `  (queryArray)   FAIL TO QUERY => ${this.currentRDB} 未获取RDB连接`)
      }
      let resultSet: relationalStore.ResultSet
      if (columns) {
        resultSet = await rdb.query(predicates, columns)
      } else {
        resultSet = await rdb.query(predicates, columns)
      }
      if (resultSet.rowCount == 0){
        console.error(TAG, `  (queryArray)   FAIL TO QUERY => 该表未查询到数据`)
        return Promise.reject(TAG + `  (queryArray)   FAIL TO QUERY => 该表未查询到数据`)
      }
      let arr: T[] = this.resultSet2Array <T> (resultSet)
      resultSet.close()
      console.info(TAG, `  (queryArray)   SUCCESS TO QUERY => ${JSON.stringify(arr)}`)
      return Promise.resolve(arr)
    } catch (err) {
      let code = (err as BusinessError).code
      let message = (err as BusinessError).message
      console.error(TAG, `  (queryArray)  FAIL TO QUERY: ${this.currentRDB} => code: ${code}, message: ${message}`)
      return Promise.reject(err as BusinessError)
    }
  }

(9)update与updateById

  /**
   * 更新数据
   * @param obj
   * @param predicates
   * @returns 更新数量
   */
  async update <T> (obj: T, predicates: relationalStore.RdbPredicates): Promise<number> {
    try {
      // 默认使用最近一次连接
      let rdb = this.rdbMap.get(this.currentRDB)
      if (!rdb) {
        console.error(TAG, `  (update)   FAIL TO UPDATE => ${this.currentRDB} 未获取RDB连接`)
        return Promise.reject(TAG + `  (update)   FAIL TO UPDATE => ${this.currentRDB} 未获取RDB连接`)
      }
      let value = this.obj2ValueBucket <T>(obj)
      let updateNum = await rdb.update(value, predicates)
      console.info(TAG, `  (update)   SUCCESS TO UPDATE => ${this.currentRDB} UPDATE ${updateNum}}`)
      return Promise.resolve(updateNum)
    } catch (err) {
      let code = (err as BusinessError).code
      let message = (err as BusinessError).message
      console.error(TAG, `  (update)  FAIL TO UPDATE: ${this.currentRDB} => code: ${code}, message: ${message}`)
      return Promise.reject(err as BusinessError)
    }
  }

  /**
   * 更新数据 ById
   * @param obj
   * @param predicates
   * @returns 成功更新数量
   */
  async updateById <T> (obj: T, tableName: string, id: number|string): Promise<number> {
    try {
      let value = this.obj2ValueBucket <T>(obj)
      let predicates = new relationalStore.RdbPredicates(tableName)
      predicates.equalTo('id', id)
      let updateNum = await this.update(value, predicates)
      console.info(TAG, `  (updateById)   SUCCESS TO UPDATE => ${this.currentRDB} UPDATE ${updateNum}}`)
      return Promise.resolve(updateNum)
    } catch (err) {
      let code = (err as BusinessError).code
      let message = (err as BusinessError).message
      console.error(TAG, `  (updateById)  FAIL TO UPDATE: ${this.currentRDB} => code: ${code}, message: ${message}`)
      return Promise.reject(err as BusinessError)
    }
  }

(10)delete与deleteById

  /**
   * 删除数据
   * @param obj
   * @param predicates
   * @returns 删除数量
   */
  async delete(predicates: relationalStore.RdbPredicates) :Promise<number> {
    try {
      // 默认使用最近一次连接
      let rdb = this.rdbMap.get(this.currentRDB)
      if (!rdb) {
        console.error(TAG, `  (delete)   FAIL TO DELETE => ${this.currentRDB} 未获取RDB连接`)
        return Promise.reject(TAG + `  (update)   FAIL TO DELETE => ${this.currentRDB} 未获取RDB连接`)
      }
      let deleteNum = await rdb.delete(predicates)
      console.info(TAG, `  (delete)   SUCCESS TO DELETE => ${this.currentRDB} DELETE ${deleteNum}}`)
      return Promise.resolve(deleteNum)
    } catch (err) {
      let code = (err as BusinessError).code
      let message = (err as BusinessError).message
      console.error(TAG, `  (delete)  FAIL TO DELETE: ${this.currentRDB} => code: ${code}, message: ${message}`)
      return Promise.reject(err as BusinessError)
    }
  }

  /**
   * 删除数据 ById
   * @param obj
   * @param predicates
   * @returns 成功删除数量
   */
  async deleteById(tableName: string, id: number|string) :Promise<number> {
    try {
      let predicates = new relationalStore.RdbPredicates(tableName)
      predicates.equalTo('id', id)
      let deleteNum = await this.delete(predicates)
      console.info(TAG, `  (deleteById)   SUCCESS TO DELETE => ${this.currentRDB} DELETE ${deleteNum}}`)
      return Promise.resolve(deleteNum)
    } catch (err) {
      let code = (err as BusinessError).code
      let message = (err as BusinessError).message
      console.error(TAG, `  (deleteById)  FAIL TO DELETE: ${this.currentRDB} => code: ${code}, message: ${message}`)
      return Promise.reject(err as BusinessError)
    }
  }

3.测试界面(User类)

实体类

export default class User {
  id : string|number
  name : string = ''

  constructor(id : string|number , name : string) {
    this.id = id
    this.name = name


  }
  toString() : string {
    return '{userId=' + this.id + ',userName=' + this.name + '}'
  }
}

Page

import relationalStore from '@ohos.data.relationalStore'
import dataSharePredicates from '@ohos.data.dataSharePredicates'
import User from '../pojo/User'
import { rdbUtil } from '../utils/RDBUtil'

@Entry
@Component
struct RDBUtilTest {
  @State rdbName: string = ''
  @State sql: string = 'create table if not exists User(id integer primary key autoincrement,name text)'
  @State tableName: string = ''
  @State userId: string = ''
  @State userName: string = ''
  @State data: string = ''

  rdb: relationalStore.RdbStore | undefined = undefined

  build() {
    Column({space: 10}) {
      TextInput({placeholder: '输入RdbName', text: this.rdbName}).onChange((value) => {
        this.rdbName = value
      })
      TextInput({placeholder: '输入sql', text: this.sql}).onChange((value) => {
        this.sql = value
      })
      TextInput({placeholder: '输入TableName', text: this.tableName}).onChange((value) => {
        this.tableName = value
      })
      TextInput({placeholder: '输入id', text: this.userId}).onChange((value) => {
        this.userId = value
      })
      TextInput({placeholder: '输入name', text: this.userName}).onChange((value) => {
        this.userName = value
      })
      Text(this.data)
      Row({space: 5}) {
        Button('createRdb').onClick(async () => {
          this.rdb = await rdbUtil.createRDB(this.rdbName)
        })
        Button('建表').onClick(() => {
          if (this.rdb) {
            this.rdb.executeSql(this.sql)
          }

        })
        Button('getConnection').onClick(() => {
          rdbUtil.getConnection(this.rdbName)
        })
      }
      Row({space: 5}) {
        Button('插入User数据').onClick(() => {
          let user: User = new User(this.userId, this.userName)
          rdbUtil.insert<User>(this.tableName, user)
        })
        Button('查询所有').onClick(() => {
          let p: relationalStore.RdbPredicates = new relationalStore.RdbPredicates(this.tableName)
          rdbUtil.queryArray <User> (p)
        })
        Button('修改ById').onClick(() => {
          let user: User = new User(this.userId, this.userName)
          rdbUtil.updateById <User> (user, this.tableName, this.userId)
        })
      }
    }
    .width('100%')
    .height('100%')
  }
}