HarmonyOS之sqlite数据库的使用

从API Version 9开始，鸿蒙开发中sqlite使用新接口@ohos.data.relationalStore

但是 relationalStore在 getRdbStore操作时，在预览模式运行或者远程模拟器运行都会报错，导致无法使用。查了一圈说只有在真机上可以正常使用，因此这里暂且使用 @ohos.data.rdb

二者的接口非常相似，会使用了ohos.data.rdb，自然也会使用ohos.data.relationalStore

在harmonyos开发中，操作数据库时，我们通常习惯将一个功能模块数据库操作全部写在一个ets文件中并export，在界面文件中直接导入使用。

1.数据库配置以及建表

新建userDb.ets文件并添加以下代码

import data_rdb from '@ohos.data.rdb'
const STORE_CONFIG = {name: "test.db"}
const TAB_USER = "user"
const CREATE_TABLE_CODE = "CREATE TABLE IF NOT EXISTS "+TAB_USER+" ("
  + "id INTEGER PRIMARY KEY AUTOINCREMENT, "
  + "name TEXT , "
  + "age TEXT , "
  + "sex TEXT ) "
export function createTable(context) {
  data_rdb.getRdbStore(context,STORE_CONFIG, 1, function (err, rdbStore) {
    rdbStore.executeSql(CREATE_TABLE_CODE)
    console.info('create table done.')
  })
}

在User.ets界面导入并调用

import {createTable} from '../utils/userDb'

aboutToAppear() {
    createTable(getContext(this))
}

2.插入数据

userDb.ets文件添加 insertData方法，这里需要注意的是promise的用法，因为需要将执行结果返回界面，所以方法里面多次使用了Promise来返回结果

插入的字段要和数据库字段保持一致。

export function insertData(context,list):any{
  const promise = data_rdb.getRdbStore(context,STORE_CONFIG, 1)
  return promise.then(async (rdbStore) => {
    let arr:any = [];
    for(let i:number=0; i<list.length; i++){
      const obj = list[i]
      const item ={
        name: obj.name,
        age: obj.age,
        sex: obj.sex
      }
      rdbStore.insert(TAB_USER, item);
    }
    console.log('--start')
    console.log('完了')
    return true;
  })
}

在User.ets界面导入并调用

import {insertData} from '../utils/userDb'

addData(){
    let array = [];
    for(let i=0; i<20; i++){
      array.push({
        name:'张飞'+i,
        age: 20+i,
        sex: '男'
      })
    }
    insertData(getContext(this), array)
      .then(res=>{
        if(res){
          this.loading = false
          this.showDialog('添加成功')
          this.search(true)
        }
      })
  }

3.查询

userDb.ets文件添加 queryDataPage方法，这里用了分页查询的方式，还有一种谓词的查询方式请参考官方文档。

export function queryDataPage(context,param):any {
  let promise = data_rdb.getRdbStore(context, STORE_CONFIG, 1)
  return promise.then(async (rdbStore) => {
    const sql: string = "select * from "+TAB_USER+" where name like ? " +
      "order by id  asc limit  ? OFFSET ? ";
    console.log('----sql---', sql)
    const pS = param.pageSize
    const page = param.page
    console.log('param.code', param.code)
    console.log('pS', pS)
    console.log('(page-1)*pS', (page - 1) * pS)
    // param.code,pS,(page-1)*pS]
    let promisequery = rdbStore.querySql(sql, [param.code, pS, (page - 1) * pS])
    return promisequery.then(async (resultSet) => {
      const rowCount = resultSet.rowCount;
      let list = [];
      console.log("rowCount --" + rowCount)
      resultSet.goToFirstRow();
      for (let i = 0; i < rowCount; i++) {
        const name = resultSet.getString(resultSet.getColumnIndex("NAME"))
        const age = resultSet.getString(resultSet.getColumnIndex("AGE"))
        const sex = resultSet.getString(resultSet.getColumnIndex("SEX"))
        const id = resultSet.getString(resultSet.getColumnIndex("ID"))
        resultSet.goToNextRow();
        const data = {
          name,
          id,
          age,
          sex,
        }
        list.push(data);
      }
      resultSet.close();
      console.log('--array--', list.length)
      return list;
    })
  }).catch((err) => {
    console.log("Get RdbStore failed, err: " + err)
  })
}

在User.ets界面导入并调用

import {queryDataPage} from '../utils/userDb'
 @State list:Array<any> = []
 @State keyword:string = ""
 @State page:number = 1
 @State pageSize:number = 20
search(firstPage:boolean){
    if(firstPage){
      this.page = 1
    }
    const params = {
      code: '%'+this.keyword+'%',
      page: this.page,
      pageSize: this.pageSize,
    }
    queryDataPage(getContext(this), params)
      .then(data=>{
        if(data){
          console.log('res',JSON.stringify(data));
          if(this.page == 1){
            this.list = data
          } else {
            this.list = this.list.concat(data);
          }
        }
      })
  }

4.更新数据

userDb.ets文件添加 updateData方法

export  function updateData(context,newInfo):any{
  const promise = data_rdb.getRdbStore(context,STORE_CONFIG, 1)
  return promise.then(async (rdbStore) => {
    let predicates = new data_rdb.RdbPredicates(TAB_USER);
    predicates.equalTo("id", newInfo.id)
    let promiseUp = rdbStore.update(newInfo, predicates)
    return promiseUp.then(async (rows) => {
      if(rows == 1){
        return true
      }
    }).catch((err) => {
      console.info("Updated failed, err: " + err)
      return false
    })
  })
}

在User.ets界面导入并调用

import {updateData} from '../utils/userDb'

updateOne(item:any){
    item.name = '张飞111'
    updateData(getContext(this), item)
      .then(res=>{
        if(res){
          this.showDialog('更新成功')
          this.search(true)
        }
      })
  }

5.删除数据

userDb.ets文件添加 deleteOneData方法

export function deteteOneData(context,id):any{
  const promise = data_rdb.getRdbStore(context,STORE_CONFIG, 1)
  return promise.then(async (rdbStore) => {
    let predicates = new data_rdb.RdbPredicates(TAB_USER);
    predicates.equalTo("id", id)
    const result = rdbStore.delete(predicates);
    console.log('--result--'+JSON.stringify(result))
    return true;
  })
}

在User.ets界面导入并调用

import {deteteOneData} from '../utils/userDb'

deleteOne(id:string){
    deteteOneData(getContext(this), id)
      .then(res=>{
        if(res){
          this.showDialog('删除成功')
          this.search(true)
        }
      })
  }

6.界面效果

点击右上角Add按钮，插入数据；

点击删除按钮，删除一条数据；

点击编辑按钮，将该条数据姓名字段更新为马超000。

7.完整代码

界面文件 User.ets

import {createTable,insertData,
  deteteOneData,updateData,
 queryDataPage} from '../utils/userDb'
@Entry
@Component
struct User {
  controller: SearchController = new SearchController()
  @State codeList:Array<any> = [];
  @State list:Array<any> = [];
  @State loading:boolean = false;
  @State keyword:string = "";
  @State page:number = 1;
  @State pageSize:number = 20;
  aboutToAppear() {
    createTable(getContext(this))
  }
  onReachEnd(){
    console.log('--------onReachEnd---');
    //this.page = this.page+1
    this.search(false)
  }
  search(firstPage:boolean){
    if(firstPage){
      this.page = 1
    }
    const params = {
      code: '%'+this.keyword+'%',
      page: this.page,
      pageSize: this.pageSize,
    }
    queryDataPage(getContext(this), params)
      .then(data=>{
        if(data){
          console.log('res',JSON.stringify(data));
          if(this.page == 1){
            this.list = data
          } else {
            this.list = this.list.concat(data);
          }
        }
      })
  }
  addData(){
    let array = [];
    for(let i=0; i<20; i++){
      array.push({
        name:'张飞'+i,
        age: 20+i,
        sex: '男'
      })
    }
    insertData(getContext(this), array)
      .then(res=>{
        if(res){
          this.loading = false
          this.showDialog('添加成功')
          this.search(true)
        }
      })
  }
  deleteOne(id:string){
    deteteOneData(getContext(this), id)
      .then(res=>{
        if(res){
          this.showDialog('删除成功')
          this.search(true)
        }
      })
  }
  updateOne(item:any){
    item.name = '马超000'
    updateData(getContext(this), item)
      .then(res=>{
        if(res){
          this.showDialog('更新成功')
          this.search(true)
        }
      })
  }
  showDialog(text:string){
    AlertDialog.show(
      {
        title: '提示',
        message: text,
        autoCancel: true,
        alignment: DialogAlignment.Center,
        gridCount: 4,
        offset: { dx: 0, dy: -20 },
        primaryButton: {
          value: '确定',
          action: () => {
            console.info('Callback when the first button is clicked')
          }
        },

      }
    )
  }
  toAddData(){
    AlertDialog.show(
      {
        title: '提示',
        message: '确定要添加数据吗',
        autoCancel: true,
        alignment: DialogAlignment.Center,
        offset: { dx: 0, dy: -20 },
        gridCount: 5,
        primaryButton: {
          value: '确定',
          action: () => {
            console.info('确定 is clicked')
            this.loading = true
            this.addData()
          }
        },
        secondaryButton: {
          value: '取消',
          action: () => {
            console.info('取消 is clicked')
          }
        },
        cancel: () => {
          console.info('Closed callbacks')
        }
      }
    )
  }
  @Builder NavigationMenus() {
    Row() {
      Text("Add")
        .width(32)
        .height(28)
        .onClick(()=>this.toAddData())
    }
  }
  @Builder buildList(){
    Row(){
      Text('姓名')
      Text('年龄')
      Text('性别')
      Text('操作')
    }.justifyContent(FlexAlign.SpaceAround)
    .width('100%')
    .padding({top:5,bottom: 10})
    List({ space: 20, initialIndex: 0 }) {
      ForEach(this.list, (item) => {
        ListItem() {
          Row(){
            Text(item.name)
            Text(item.age)
            Text(item.sex)
            Row(){
              Text('删除').onClick(()=>{
                this.deleteOne(item.id)
              }).fontColor(Color.Red)
                .margin({right:5})
              Text('编辑').onClick(()=>{
                this.updateOne(item)
              }).fontColor(Color.Blue)
            }
          }
          .justifyContent(FlexAlign.SpaceAround)
          .width('100%')

        }.editable(true)
      }, item => item.name)
    }
    .onScrollIndex((firstIndex: number, lastIndex: number) => {
      //console.info('first' + firstIndex)
      //console.info('last' + lastIndex)
    })
    .onReachEnd(()=>{
      this.onReachEnd()
    })
    .listDirection(Axis.Vertical) // 排列方向
    .divider({ strokeWidth: 2, color: 0xFFFFFF, startMargin: 20, endMargin: 20 }) // 每行之间的分界线
    .edgeEffect(EdgeEffect.None) // 滑动到边缘无效果
    .chainAnimation(false) // 联动特效关闭
    .width('100%')
  }
  @Builder buildSearch(){
    Search({ value: this.keyword, placeholder: '请输入姓名...', controller: this.controller })
      .searchButton('搜索')
      .width('100%')
      .height(40)
      .backgroundColor('#F5F5F5')
      .placeholderColor(Color.Grey)
      .placeholderFont({ size: 14, weight: 400 })
      .textFont({ size: 14, weight: 400 })
      .onSubmit((value: string) => {
        console.log('---999')
        //this.submitValue = value
        this.keyword = value
        this.search(true)
      })
      .onChange((value: string) => {
        this.keyword = value
      })
  }
  build() {
    Column(){
      Navigation() {
        Column(){
          this.buildSearch()
          if(this.loading){
            Column(){
              LoadingProgress()
                .color(Color.Blue)
            }
            .width('40%').height('40%')
          }
          this.buildList()

        }.height('100%')
      }
      .title("用户管理")
      .menus(this.NavigationMenus)
      .titleMode(NavigationTitleMode.Mini)
    }
    .height('100%')
  }
}

数据操作文件 userDb.ets

import data_rdb from '@ohos.data.rdb'
const STORE_CONFIG = {name: "test.db"}
const TAB_USER = "user"
const CREATE_TABLE_CODE = "CREATE TABLE IF NOT EXISTS "+TAB_USER+" ("
  + "id INTEGER PRIMARY KEY AUTOINCREMENT, "
  + "name TEXT , "
  + "age TEXT , "
  + "sex TEXT ) "
export function createTable(context) {
  data_rdb.getRdbStore(context,STORE_CONFIG, 1, function (err, rdbStore) {
    rdbStore.executeSql(CREATE_TABLE_CODE)
    console.info('create table done.')
  })
}
export  function updateData(context,newInfo):any{
  const promise = data_rdb.getRdbStore(context,STORE_CONFIG, 1)
  return promise.then(async (rdbStore) => {
    let predicates = new data_rdb.RdbPredicates(TAB_USER);
    predicates.equalTo("id", newInfo.id)
    let promiseUp = rdbStore.update(newInfo, predicates)
    return promiseUp.then(async (rows) => {
      if(rows == 1){
        return true
      }
    }).catch((err) => {
      console.info("Updated failed, err: " + err)
      return false
    })
  })
}
export function insertData(context,list):any{
  const promise = data_rdb.getRdbStore(context,STORE_CONFIG, 1)
  return promise.then(async (rdbStore) => {
    let arr:any = [];
    for(let i:number=0; i<list.length; i++){
      const obj = list[i]
      const item ={
        name: obj.name,
        age: obj.age,
        sex: obj.sex
      }
      rdbStore.insert(TAB_USER, item);
    }
    console.log('--start')
    console.log('完了')
    return true;
  })
}
export function deteteOneData(context,id):any{
  const promise = data_rdb.getRdbStore(context,STORE_CONFIG, 1)
  return promise.then(async (rdbStore) => {
    let predicates = new data_rdb.RdbPredicates(TAB_USER);
    predicates.equalTo("id", id)
    const result = rdbStore.delete(predicates);
    console.log('--result--'+JSON.stringify(result))
    return true;
  })
}
export function detelteAllCode(context){
  data_rdb.getRdbStore(context,STORE_CONFIG, 1, function (err, rdbStore) {
    rdbStore.executeSql("delete from "+TAB_USER)
    console.info('--delete code done.')
  })

}
export function queryDataPage(context,param):any {
  let promise = data_rdb.getRdbStore(context, STORE_CONFIG, 1)
  return promise.then(async (rdbStore) => {
    const sql: string = "select * from "+TAB_USER+" where name like ? " +
      "order by id  asc limit  ? OFFSET ? ";
    console.log('----sql---', sql)
    const pS = param.pageSize
    const page = param.page
    console.log('param.code', param.code)
    console.log('pS', pS)
    console.log('(page-1)*pS', (page - 1) * pS)
    // param.code,pS,(page-1)*pS]
    let promisequery = rdbStore.querySql(sql, [param.code, pS, (page - 1) * pS])
    return promisequery.then(async (resultSet) => {
      const rowCount = resultSet.rowCount;
      let list = [];
      console.log("rowCount --" + rowCount)
      resultSet.goToFirstRow();
      for (let i = 0; i < rowCount; i++) {
        const name = resultSet.getString(resultSet.getColumnIndex("NAME"))
        const age = resultSet.getString(resultSet.getColumnIndex("AGE"))
        const sex = resultSet.getString(resultSet.getColumnIndex("SEX"))
        const id = resultSet.getString(resultSet.getColumnIndex("ID"))
        resultSet.goToNextRow();
        const data = {
          name,
          id,
          age,
          sex,
        }
        list.push(data);
      }
      resultSet.close();
      console.log('--array--', list.length)
      return list;
    })
  }).catch((err) => {
    console.log("Get RdbStore failed, err: " + err)
  })
}