12. 数据模型——通用DB工具

12.1 通用型数据库

需要用到的时候直接导包就可以

import common from '@ohos.app.ability.common';
import relationalStore from '@ohos.data.relationalStore';
import { ColumnInfo, ColumnType } from '../bean/ColumnInfo';
import Logger from './Logger';

const DB_FILENAME: string = 'HeiMaHealthy.db'

class DbUtil {
  rdbStore: relationalStore.RdbStore
//异步操作所以用promise
  initDB(context: common.UIAbilityContext): Promise<void> {
    let config: relationalStore.StoreConfig = {
      name: DB_FILENAME,
      securityLevel: relationalStore.SecurityLevel.S1
    }
    return new Promise<void>((resolve, reject) => {
      relationalStore.getRdbStore(context, config)//获得数据库操作的对象
        .then(rdbStore => {
          this.rdbStore = rdbStore
          Logger.debug('rdbStore 初始化完成！')
          resolve()
        })
        .catch(reason => {
          Logger.debug('rdbStore 初始化异常', JSON.stringify(reason))
          reject(reason)
        })
    })
  }

  createTable(createSQL: string): Promise<void> {
    return new Promise((resolve, reject) => {
      this.rdbStore.executeSql(createSQL)
        .then(() => {
          Logger.debug('创建表成功', createSQL)
          resolve()
        })
        .catch(err => {
          Logger.error('创建表失败,' + err.message, JSON.stringify(err))
          reject(err)
        })
    })
  }


}


let dbUtil: DbUtil = new DbUtil();

export default dbUtil as DbUtil

12.2 初始化

  //由于RDB是异步的所以加上async
  async onCreate(want, launchParam) {
    //1.加载用户首选项
    PreferenceUtil.loadPreference(this.context)
    //2.初始化日期
    AppStorage.SetOrCreate(CommonConstants.RECORD_DATE,DateUtil.beginTimeOfDay(new Date()))
    //3.初始化RDB工具
    await DbUtil.initDB(this.context)
    //4.创建record表
    DbUtil.createTable(RecordModel.getCreateTableSql())
    hilog.info(0x0000, 'testTag', '%{public}s', 'Ability onCreate');

  }

12.3 对数据库增删改查方法

数据库字段信息由于创建表的属性名称和之前定义的名称不一样

export interface ColumnInfo{
  name: string
  columnName: string
  type: ColumnType
}

export enum ColumnType{
  LONG,
  DOUBLE,
  STRING,
  BLOB
}

//RecordModel.ets
const COLUMNS: ColumnInfo[] = [
  {name: 'id', columnName: 'id', type: ColumnType.LONG},
  {name: 'typeId', columnName: 'type_id', type: ColumnType.LONG},
  {name: 'itemId', columnName: 'item_id', type: ColumnType.LONG},
  {name: 'amount', columnName: 'amount', type: ColumnType.DOUBLE},
  {name: 'createTime', columnName: 'create_time', type: ColumnType.LONG}
]

通用方法DbUtil.ets封装一下

 buildValueBucket(obj: any, columns: ColumnInfo[]): relationalStore.ValuesBucket {
    let value = {}
    columns.forEach(info => {
      let val = obj[info.name]
      if (typeof val !== 'undefined') {
        value[info.columnName] = val
      }
    })
    return value
  }

12.3.1 添加方法（增）

insert(tableName: string, obj: any, columns: ColumnInfo[]): Promise<number> {
  return new Promise((resolve, reject) => {
    // 1.构建新增数据
    let value = this.buildValueBucket(obj, columns)
    // 2.新增
    this.rdbStore.insert(tableName, value, (err, id) => {
      if (err) {
        Logger.error('新增失败！', JSON.stringify(err))
        reject(err)
      } else {
        Logger.debug('新增成功！新增id：', id.toString())
        resolve(id)
      }
    })
  })
}

12.3.2 删除方法（删）

 delete(predicates: relationalStore.RdbPredicates): Promise<number> {
    return new Promise((resolve, reject) => {
      this.rdbStore.delete(predicates, (err, rows) => {
        if (err) {
          Logger.error('删除失败！', JSON.stringify(err))
          reject(err)
        } else {
          Logger.debug('删除成功！删除行数：', rows.toString())
          resolve(rows)
        }
      })
    })
  }

12.3.3 查询方法（查）

  queryForList<T>(predicates: relationalStore.RdbPredicates, columns: ColumnInfo[]): Promise<T[]> {
    return new Promise((resolve, reject) => {
      this.rdbStore.query(predicates, columns.map(info => info.columnName), (err, result) => {
        if (err) {
          Logger.error('查询失败！', JSON.stringify(err))
          reject(err)
        } else {
          Logger.debug('查询成功！查询行数：', result.rowCount.toString())
          resolve(this.parseResultSet(result, columns))
        }
      })
    })
  }

封装一个方法用来解析 

  parseResultSet<T> (result: relationalStore.ResultSet, columns: ColumnInfo[]): T[] {
    // 1.声明最终返回的结果
    let arr = []
    // 2.判断是否有结果
    if (result.rowCount <= 0) {
      return arr
    }
    // 3.处理结果
    while (!result.isAtLastRow) {
      // 3.1.去下一行
      result.goToNextRow()
      // 3.2.解析这行数据，转为对象
      let obj = {}
      columns.forEach(info => {
        let val = null
        switch (info.type) {
          case ColumnType.LONG:
            val = result.getLong(result.getColumnIndex(info.columnName))
            break
          case ColumnType.DOUBLE:
            val = result.getDouble(result.getColumnIndex(info.columnName))
            break
          case ColumnType.STRING:
            val = result.getString(result.getColumnIndex(info.columnName))
            break
          case ColumnType.BLOB:
            val = result.getBlob(result.getColumnIndex(info.columnName))
            break
        }
        obj[info.name] = val
      })
      // 3.3.将对象填入结果数组
      arr.push(obj)
      Logger.debug('查询到数据：', JSON.stringify(obj))
    }
    return arr
  }

在RecordModel操作有关饮食记录的model增删改查

const TABLE_NAME = 'record'
const ID_COLUMN = 'id'
const DATE_COLUMN = 'create_time'
  insert(record: RecordPO): Promise<number>{
    return DbUtil.insert(TABLE_NAME, record, COLUMNS)
  }

  deleteById(id: number): Promise<number>{
    // 1.删除条件
    let predicates = new relationalStore.RdbPredicates(TABLE_NAME)
    predicates.equalTo(ID_COLUMN, id)
    // 2.删除
    return DbUtil.delete(predicates)
  }
//根据日期查询
  listByDate(date: number): Promise<RecordPO[]>{
    // 1.查询条件
    let predicates = new relationalStore.RdbPredicates(TABLE_NAME)
    predicates.equalTo(DATE_COLUMN, date)
    // 2.查询
    return DbUtil.queryForList(predicates, COLUMNS)
  }

12.4 遇到问题

导包倒不过来，因为RecordModel是ets而EntryAbility是ts所以需要把EntryAbility变为ets