关系型数据库使用分享

通过@ohos.data.relationalStore相关接口，完成关系型数据库的简单使用。

HarmonyOS

hujianwu

2024-05-22 23:28:15

浏览

回答 1

待解决

回答 1

按赞同

按时间

bho2000

使用的核心API

@ohos.data.relationalStore相关api

核心代码解释

创建数据库，表，封装增删改查，批量增加数据等方法

import relationalStore from '@ohos.data.relationalStore'; 
import ItemBean from './ItemBean'; 
import { ValuesBucket } from '@ohos.data.ValuesBucket'; 
 
 
export default class RdbTest { 
 rdbStore?: relationalStore.RdbStore; 
 context: Context = getContext(this); 
 dataSet?: relationalStore.ResultSet; 
 dbSet: Array<ItemBean> = []; 
 
 MyGetRdbStore() { 
   // SQLite建立 
   interface StoreConfig { 
     name: string; 
     securityLevel: relationalStore.SecurityLevel; 
  } 
   const STORE_CONFIG: relationalStore.StoreConfig = { 
     name: 'RdbTest3.db', // 数据库文件名 
     securityLevel: relationalStore.SecurityLevel.S1, // 数据库安全级别 
     // encrypt: true, // 可选参数，指定数据库是否加密，默认不加密 
     // dataGroupId: 'dataGroupID' // 可选参数，仅可在Stage模型下使用，表示为应用组ID，需要向应用市场获取。指定在此Id对应的沙箱路径下创建实例，当此参数不填时，默认在本应用沙箱目录下创建。 
  }; 
 
   relationalStore.getRdbStore(this.context, STORE_CONFIG, (err, store) => { 
     if (err) { 
       console.error(`Failed to get RdbStore. Code:${err.code}, message:${err.message}`); 
       return; 
    } else { 
       console.info(`Succeeded in getting RdbStore.`); 
    } 
     this.rdbStore = store; 
  }) 
} 
 
 
 CreateTable() { 
   const SQL_CREATE_TABLE = 'CREATE TABLE IF NOT EXISTS EMPLOYEE (ID INTEGER PRIMARY KEY AUTOINCREMENT,NAME TEXT NOT NULL,AGE INTEGER,SALARY REAL)'; 
 
   // create table if not exists employee (id integer primary key autoincrement, name text not null, age integer, salary real) 
   // 这是一条SQL语句，用于创建一个名为"employee"的表格，如果该表格不存在的话。 
   // 表格包含三个列：id、name和age，其中id是主键，自动递增；name是文本类型，不能为空；age是整数类型。另外还有一个名为salary的列，是实数类型。 
   if (this.rdbStore != undefined) { 
     this.rdbStore.executeSql(SQL_CREATE_TABLE); 
     console.log("建表成功") 
  } 
 
} 
//批量添加 
 Inserts(){ 
  { 
     if (this.rdbStore == null) { 
       console.error(`Create Store1.db failed! store1 is null`); 
       return; 
    } 
  } 
 
   let valueBucketArray: Array<relationalStore.ValuesBucket> = new Array(); 
   for (let i=0; i < 10; i++) { 
     const valueBucket: relationalStore.ValuesBucket = { 
       "name": "gzh",       // string类型 
       "age": 18,                // int类型 
       "salary": 10000,          // double类型 
    } 
     valueBucketArray.push(valueBucket); 
  } 
 
   try { 
      this.rdbStore.batchInsert("EMPLOYEE", valueBucketArray );  // 该接口内部使用事务 
     console.info(`Insert data successfully!`); 
  } catch (err) { 
     console.error(`Insert datae failed! err code:${err.code}, err message:${err.message}`) 
  } 
} 
 
 InsertData(name: string, age: number, salary: number) { 
   // 插入数据 
   const valueBucket: ValuesBucket = { 
     NAME: name, 
     AGE: age, 
     SALARY: salary 
  }; 
 
   if (this.rdbStore != undefined) { 
 
     try { 
       //开启事务 
       this.rdbStore.beginTransaction(); 
 
       this.rdbStore.insert('EMPLOYEE', valueBucket) 
       //提交事务 
       this.rdbStore.commit(); 
       // console.info(`Succeeded in inserting data. rowId:${rowId}`); 
    } 
     catch (err) { 
       //回滚事务 
       this.rdbStore.rollBack() 
       console.error(`Failed to insert data. Code:${err.code}, message:${err.message}`); 
       return; 
    } 
   console.log("_______________________________________") 
     this.QueryAllData() 
  } 
} 
 
 UpdateData(updateName: string, name: string, age: number, salary: number) { 
   // 修改数据 
   const valueBucket: ValuesBucket = { 
     NAME: name, 
     AGE: age, 
     SALARY: salary 
  }; 
 
   let predicates = new relationalStore.RdbPredicates('EMPLOYEE'); // 创建表'EMPLOYEE'的predicates 
   predicates.equalTo('NAME', updateName); // 匹配表'EMPLOYEE'中'NAME'为'Lisa'的字段 
   if (this.rdbStore != undefined) { 
     this.rdbStore.update(valueBucket, predicates, (err, rows) => { 
       if (err) { 
         console.error(`Failed to update data. Code:${err.code}, message:${err.message}`); 
         return; 
      } 
       console.info(`Succeeded in updating data. row count: ${rows}`); 
    }) 
  } 
} 
 
 DeleteData(deleteName: string) { 
   // 删除数据 
   let predicates = new relationalStore.RdbPredicates('EMPLOYEE'); 
   predicates.equalTo('NAME', deleteName); 
   if (this.rdbStore != undefined) { 
     this.rdbStore.delete(predicates, (err, rows) => { 
       if (err) { 
         console.error(`Failed to delete data. Code:${err.code}, message:${err.message}`); 
         return; 
      } 
       console.info(`Delete rows: ${rows}`); 
    }) 
  } 
} 
 
 UpdateDb(predicates: relationalStore.RdbPredicates) { 
   // 刷新 
   if (this.rdbStore != undefined) { 
     this.rdbStore.query(predicates, ['ID', 'NAME', 'AGE', 'SALARY'], (err, resultSet) => { 
       if (err) { 
         console.error(`Failed to query data. Code:${err.code}, message:${err.message}`); 
         return; 
      } 
       console.info(`ResultSet column names: ${resultSet.columnNames}, column count: ${resultSet.columnCount}`); 
       // resultSet是一个数据集合的游标，默认指向第-1个记录，有效的数据从0开始。 
       this.dbSet = []; 
 
       while (resultSet.goToNextRow()) { 
         const id = resultSet.getLong(resultSet.getColumnIndex("ID")); 
         const name = resultSet.getString(resultSet.getColumnIndex("NAME")); 
         const age = resultSet.getLong(resultSet.getColumnIndex("AGE")); 
         const salary = resultSet.getDouble(resultSet.getColumnIndex("SALARY")); 
         console.info(`id=${id}, name=${name}, age=${age}, salary=${salary}`); 
         const tmp = new ItemBean(name, age.toString(), salary.toString()); 
         this.dbSet.push(tmp); 
      } 
       console.log("查询到的数据数量为："+resultSet.rowCount); 
       // 释放数据集的内存 
       this.dataSet = resultSet; 
       resultSet.close(); 
    }) 
  } 
} 
 
 QueryAllData() { 
   let predicates = new relationalStore.RdbPredicates('EMPLOYEE'); 
   predicates.inAllDevices(); 
   this.UpdateDb(predicates) 
} 
 
  query(){ 
  { 
     if (this.rdbStore == null) { 
       console.error(`Create Store1.db failed! store1 is null`); 
       return; 
    } 
  } 
 
   try { 
     let predicates: relationalStore.RdbPredicates = new relationalStore.RdbPredicates("EMPLOYEE"); 
     let resultSet =  this.rdbStore.query(predicates);  // 查询所有数据 
     // console.info(`Query data successfully! row count:${resultSet.rowCount}`); 
     // resultSet.close(); 
  } catch (err) { 
     console.error(`Query data failed! err code:${err.code}, err message:${err.message}`) 
  } 
} 
 
 QueryData(query: string) { 
      console.log("开始查询_______________________________________") 
   let predicates = new relationalStore.RdbPredicates('EMPLOYEE'); 
 
   predicates.equalTo("NAME", query) 
   this.UpdateDb(predicates) 
} 
 
}

获得一个相关的RdbStore，操作关系型数据库

import ItemBean from '../DataBase/ItemBean'; 
import RdbTest from '../DataBase/RdbTest' 
 
@Entry 
@Component 
struct Index { 
 
 @State rdbTest: RdbTest = new RdbTest() 
 @State name: string = 'gzh' 
 @State age: string = '24' 
 @State salary: string = '0' 
 @State src: string = '123' 
 
 aboutToAppear() { 
   this.rdbTest.MyGetRdbStore(); 
} 
 
 build() { 
   Row() { 
     Column() { 
       Button("使用前先建表") 
        .fontSize(50) 
        .fontWeight(FontWeight.Bold) 
        .onClick(()=>{ 
           this.rdbTest.CreateTable(); 
        }) 
        .margin(20) 
       Button("增") 
        .fontSize(50) 
        .fontWeight(FontWeight.Bold) 
        .onClick(()=>{ 
           this.rdbTest.InsertData(this.name, Number(this.age), Number(this.salary)) 
        }) 
        .margin(20) 
       Button("删") 
        .fontSize(50) 
        .fontWeight(FontWeight.Bold) 
        .onClick(()=>{ 
           this.rdbTest.DeleteData("gzh") 
        }) 
        .margin(20) 
       Button("改") 
        .fontSize(50) 
        .fontWeight(FontWeight.Bold) 
        .onClick(()=>{ 
           this.rdbTest.UpdateData("gzh","gzh123",25,1 ) 
        }) 
        .margin(20) 
       Button("查") 
        .fontSize(50) 
        .fontWeight(FontWeight.Bold) 
        .onClick(()=>{ 
           this.rdbTest.QueryData("gzh") 
        }) 
        .margin(20) 
       Button("批量增") 
        .fontSize(50) 
        .fontWeight(FontWeight.Bold) 
        .onClick(()=>{ 
           this.rdbTest.Inserts() 
        }) 
        .margin(20) 
 
    } 
    .width('100%') 
  } 
  .height('100%') 
} 
}

实现效果