关系型数据库使用分享

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

HarmonyOS

牙膏只用咸的

2024-05-22 23:28:15

浏览

回答 1

按赞同

按时间

唯你而画

使用的核心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) 
} 
 
}1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
192.
193.
194.
195.
196.
197.
198.
199.
200.
201.
202.
203.

获得一个相关的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%') 
} 
}1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.