HarmonyOS NEXT关系型数据库增删改查

前言

数据存储基本绕不开数据库，后续工作迟早要用上，趁着周末有空，提前踩下坑和熟悉相关api和用法

效果图

获取RdbStore

  private dbStore: relationalStore.RdbStore | undefined = undefined

  public async initDbStored(context: common.Context) {
    let storeConfig: relationalStore.StoreConfig =
      {
        name: "People.db", //数据库文件名
        securityLevel: relationalStore.SecurityLevel.S2 //数据库的安全级别
      }

    //获得一个相关的RdbStore，操作关系型数据库，用户可以根据自己的需求配置RdbStore的参数，然后通过RdbStore调用相关接口可以执行相关的数据操作
    this.dbStore = await relationalStore.getRdbStore(context, storeConfig)
  }

创建数据表(student)

const storeConfig: relationalStore.StoreConfig = 
{
    name: "People.db", //数据库文件名
    securityLevel: relationalStore.SecurityLevel.S2 //数据库的安全级别
}

async createTable(context: common.Context) {
  //获取RdbStore
  this.dbStore = await relationalStore.getRdbStore(context,storeConfig)
  
  //创建student表的sql语句(分别是id,name,age三列数据,id设置自增长)
  let createTableSql="CREATE TABLE IF NOT EXISTS student" +
    "(" +
    "id INTEGER PRIMARY KEY AUTOINCREMENT" +
    ", name TEXT NOT NULL" +
    ", age INTEGER NOT NULL" +
    ")"
    
  await this.dbStore.executeSql(createTableSql)
}

添加数据

async insert(context: common.Context): Promise<boolean> {
    const value: relationalStore.ValuesBucket = {
      "name": "张三",
      "age": 18
    };
    try {
      this.dbStore = await relationalStore.getRdbStore(context,storeConfig)
      let number = this.dbStore.insertSync("student", value,relationalStore.ConflictResolution.ON_CONFLICT_REPLACE) ?? 0
      return number >= 0
    } catch (e) {
    }
    return false
  }

删除数据

async delete(context: common.Context, uid: string="666"): Promise<number> {
  try {
    let predicates = new relationalStore.RdbPredicates("student")
    //删除id=666的数据
    predicates.equalTo("id", uid);
    
    //获取RdbStore
    this.dbStore = await relationalStore.getRdbStore(context,storeConfig)
    let count = this.dbStore.deleteSync(predicates)
    return Promise.resolve(count ?? 0)
  } catch (e) {
  }
  return 0
}

修改数据

async update(context: common.Context, student: Student): Promise<number> {
  const value: relationalStore.ValuesBucket = {
    "name": student.name,
    "age": student.age
  };
  try {
    //获取RdbStore
    this.dbStore = await relationalStore.getRdbStore(context,storeConfig)
    
    let predicates = new relationalStore.RdbPredicates("student")
    //修改id==student.id一列的name和age
    predicates.equalTo("id", student.id);
    
    let count: number | undefined = this.dbStore.updateSync(value, predicates,relationalStore.ConflictResolution.ON_CONFLICT_REPLACE)
    return count ?? 0
  } catch (e) {
  }
  return 0
}

查询数据

async query(context: common.Context): Promise<Student[]> {
  try {
    //获取RdbStore
    this.dbStore = await relationalStore.getRdbStore(context,storeConfig)
    
    let predicates = new relationalStore.RdbPredicates("student")
    //查询所有列
    let result = this.dbStore.querySync(predicates)
    //查询id,name,age三列数据
    let result = this.dbStore.querySync(predicates, ["id", "name", "age"])
    
    if (!result) {
      return []
    }
    
    let data: Student[] = []
    
    while (result.goToNextRow()) {
      let id = result?.getLong(result.getColumnIndex("id"))
      let name = result?.getString(result.getColumnIndex("name"))
      let age = result?.getLong(result.getColumnIndex("age"))
      data.push({
        id: id,
        name: name,
        age: age
      })
    }
    return data
  } catch (err) {
  }
  return []
}

源码地址

https://gitee.com/zhongrui_developer/test-db