1# 批量数据写数据库场景
2
3## 使用TaskPool进行频繁数据库操作
4
5对于需要频繁数据库操作的场景,由于读写数据库存在耗时,因此推荐在子线程中操作,避免阻塞UI线程。
6
7通过ArkTS提供的TaskPool能力,可以将数据库操作任务移到子线程中,实现如下。
8
91. 创建多个子任务,支持数据库创建、插入、查询、清除等操作。
10
112. UI主线程调用子任务完成增删改查等数据库操作。
12
13```ts
14// Index.ets
15import { relationalStore, ValuesBucket } from '@kit.ArkData';
16import { taskpool } from '@kit.ArkTS';
17
18@Concurrent
19async function create(context: Context) {
20  const CONFIG: relationalStore.StoreConfig = {
21    name: "Store.db",
22    securityLevel: relationalStore.SecurityLevel.S1,
23  };
24
25  // 默认数据库文件路径为 context.databaseDir + rdb + StoreConfig.name
26  let store: relationalStore.RdbStore = await relationalStore.getRdbStore(context, CONFIG);
27  console.info(`Create Store.db successfully!`);
28
29  // 创建表
30  const CREATE_TABLE_SQL = "CREATE TABLE IF NOT EXISTS test (" +
31    "id INTEGER PRIMARY KEY AUTOINCREMENT, " +
32    "name TEXT NOT NULL, " +
33    "age INTEGER, " +
34    "salary REAL, " +
35    "blobType BLOB)";
36  await store.executeSql(CREATE_TABLE_SQL);
37  console.info(`Create table test successfully!`);
38}
39
40@Concurrent
41async function insert(context: Context, valueBucketArray: Array<relationalStore.ValuesBucket>) {
42  const CONFIG: relationalStore.StoreConfig = {
43    name: "Store.db",
44    securityLevel: relationalStore.SecurityLevel.S1,
45  };
46
47  // 默认数据库文件路径为 context.databaseDir + rdb + StoreConfig.name
48  let store: relationalStore.RdbStore = await relationalStore.getRdbStore(context, CONFIG);
49  console.info(`Create Store.db successfully!`);
50
51  // 数据插入
52  await store.batchInsert("test", valueBucketArray as Object as Array<relationalStore.ValuesBucket>);
53}
54
55@Concurrent
56async function query(context: Context): Promise<Array<relationalStore.ValuesBucket>> {
57  const CONFIG: relationalStore.StoreConfig = {
58    name: "Store.db",
59    securityLevel: relationalStore.SecurityLevel.S1,
60  };
61
62  // 默认数据库文件路径为 context.databaseDir + rdb + StoreConfig.name
63  let store: relationalStore.RdbStore = await relationalStore.getRdbStore(context, CONFIG);
64  console.info(`Create Store.db successfully!`);
65
66  // 获取结果集
67  let predicates: relationalStore.RdbPredicates = new relationalStore.RdbPredicates("test");
68  let resultSet = await store.query(predicates);  // 查询所有数据
69  console.info(`Query data successfully! row count:${resultSet.rowCount}`);
70  let index = 0;
71  let result = new Array<relationalStore.ValuesBucket>(resultSet.rowCount)
72  resultSet.goToFirstRow()
73  do {
74    result[index++] = resultSet.getRow()
75  } while (resultSet.goToNextRow());
76  resultSet.close();
77  return result
78}
79
80@Concurrent
81async function clear(context: Context) {
82  const CONFIG: relationalStore.StoreConfig = {
83    name: "Store.db",
84    securityLevel: relationalStore.SecurityLevel.S1,
85  };
86
87  // 默认数据库文件路径为 context.databaseDir + rdb + StoreConfig.name
88  await relationalStore.deleteRdbStore(context, CONFIG);
89  console.info(`Delete Store.db successfully!`);
90}
91
92@Entry
93@Component
94struct Index {
95  @State message: string = 'Hello World';
96
97  build() {
98    RelativeContainer() {
99      Text(this.message)
100        .id('HelloWorld')
101        .fontSize(50)
102        .fontWeight(FontWeight.Bold)
103        .alignRules({
104          center: { anchor: '__container__', align: VerticalAlign.Center },
105          middle: { anchor: '__container__', align: HorizontalAlign.Center }
106        })
107        .onClick(async () => {
108          let context = getContext(this);
109
110          // 数据准备
111          const count = 5
112          let valueBucketArray = new Array<relationalStore.ValuesBucket>(count);
113          for (let i = 0; i < count; i++) {
114            let v : relationalStore.ValuesBucket = {
115              id: i,
116              name: "zhangsan" + i,
117              age: 20,
118              salary: 5000 + 50 * i
119            };
120            valueBucketArray[i] = v;
121          }
122          await taskpool.execute(create, context)
123          await taskpool.execute(insert, context, valueBucketArray)
124          let index = 0
125          let ret = await taskpool.execute(query, context) as Array<relationalStore.ValuesBucket>
126          for (let v of ret) {
127            console.info(`Row[${index}].id = ${v.id}`)
128            console.info(`Row[${index}].name = ${v.name}`)
129            console.info(`Row[${index}].age = ${v.age}`)
130            console.info(`Row[${index}].salary = ${v.salary}`)
131            index++
132          }
133          await taskpool.execute(clear, context)
134        })
135    }
136    .height('100%')
137    .width('100%')
138  }
139}
140```
141
142## 使用Sendable进行大容量数据库操作
143
144由于数据库数据跨线程传递存在耗时,当数据量较大时,仍然会占用UI主线程,推荐采用Sendable封装数据库数据,降低跨线程开销。
145
1461. 定义数据库中的数据格式,可采用Sendable,减少跨线程耗时。
147
148   ```ts
149   // SharedValuesBucket.ets
150   export interface IValueBucket {
151     id: number
152     name: string
153     age: number
154     salary: number
155   }
156
157   @Sendable
158   export class SharedValuesBucket implements IValueBucket {
159     id: number = 0
160     name: string = ""
161     age: number = 0
162     salary: number = 0
163
164     constructor(v: IValueBucket) {
165       this.id = v.id;
166       this.name = v.name;
167       this.age = v.age;
168       this.salary = v.salary
169     }
170   }
171   ```
172
1732. UI主线程发起,在子线程进行数据的增删改查等操作。
174
175   ```ts
176   // Index.ets
177   import { relationalStore, ValuesBucket } from '@kit.ArkData';
178   import { collections, taskpool } from '@kit.ArkTS';
179   import { IValueBucket, SharedValuesBucket } from './SharedValuesBucket';
180
181   @Concurrent
182   async function create(context: Context) {
183     const CONFIG: relationalStore.StoreConfig = {
184       name: "Store.db",
185       securityLevel: relationalStore.SecurityLevel.S1,
186     };
187
188     // 默认数据库文件路径为 context.databaseDir + rdb + StoreConfig.name
189     let store: relationalStore.RdbStore = await relationalStore.getRdbStore(context, CONFIG);
190     console.info(`Create Store.db successfully!`);
191
192     // 创建表
193     const CREATE_TABLE_SQL = "CREATE TABLE IF NOT EXISTS test (" +
194       "id INTEGER PRIMARY KEY AUTOINCREMENT, " +
195       "name TEXT NOT NULL, " +
196       "age INTEGER, " +
197       "salary REAL, " +
198       "blobType BLOB)";
199     await store.executeSql(CREATE_TABLE_SQL);
200     console.info(`Create table test successfully!`);
201   }
202
203   @Concurrent
204   async function insert(context: Context, valueBucketArray: collections.Array<SharedValuesBucket | undefined>) {
205     const CONFIG: relationalStore.StoreConfig = {
206       name: "Store.db",
207       securityLevel: relationalStore.SecurityLevel.S1,
208     };
209
210     // 默认数据库文件路径为 context.databaseDir + rdb + StoreConfig.name
211     let store: relationalStore.RdbStore = await relationalStore.getRdbStore(context, CONFIG);
212     console.info(`Create Store.db successfully!`);
213
214     // 数据插入
215     await store.batchInsert("test", valueBucketArray as Object as Array<ValuesBucket>);
216   }
217
218   @Concurrent
219   async function query(context: Context): Promise<collections.Array<SharedValuesBucket | undefined>> {
220     const CONFIG: relationalStore.StoreConfig = {
221       name: "Store.db",
222       securityLevel: relationalStore.SecurityLevel.S1,
223     };
224
225     // 默认数据库文件路径为 context.databaseDir + rdb + StoreConfig.name
226     let store: relationalStore.RdbStore = await relationalStore.getRdbStore(context, CONFIG);
227     console.info(`Create Store.db successfully!`);
228
229     // 获取结果集
230     let predicates: relationalStore.RdbPredicates = new relationalStore.RdbPredicates("test");
231     let resultSet = await store.query(predicates); // 查询所有数据
232     console.info(`Query data successfully! row count:${resultSet.rowCount}`);
233     let index = 0;
234     let result = collections.Array.create<SharedValuesBucket | undefined>(resultSet.rowCount, undefined)
235     resultSet.goToFirstRow()
236     do {
237       let v: IValueBucket = {
238         id: resultSet.getLong(resultSet.getColumnIndex("id")),
239         name: resultSet.getString(resultSet.getColumnIndex("name")),
240         age: resultSet.getLong(resultSet.getColumnIndex("age")),
241         salary: resultSet.getLong(resultSet.getColumnIndex("salary"))
242       };
243       result[index++] = new SharedValuesBucket(v)
244     } while (resultSet.goToNextRow());
245     resultSet.close();
246     return result
247   }
248
249   @Concurrent
250   async function clear(context: Context) {
251     const CONFIG: relationalStore.StoreConfig = {
252       name: "Store.db",
253       securityLevel: relationalStore.SecurityLevel.S1,
254     };
255
256     // 默认数据库文件路径为 context.databaseDir + rdb + StoreConfig.name
257     await relationalStore.deleteRdbStore(context, CONFIG);
258     console.info(`Delete Store.db successfully!`);
259   }
260
261   @Entry
262   @Component
263   struct Index {
264     @State message: string = 'Hello World';
265
266     build() {
267       RelativeContainer() {
268         Text(this.message)
269           .id('HelloWorld')
270           .fontSize(50)
271           .fontWeight(FontWeight.Bold)
272           .alignRules({
273             center: { anchor: '__container__', align: VerticalAlign.Center },
274             middle: { anchor: '__container__', align: HorizontalAlign.Center }
275           })
276           .onClick(async () => {
277             let context = getContext(this);
278
279             // 数据准备
280             const count = 5
281             let valueBucketArray = collections.Array.create<SharedValuesBucket | undefined>(count, undefined);
282             for (let i = 0; i < count; i++) {
283               let v: IValueBucket = {
284                 id: i,
285                 name: "zhangsan" + i,
286                 age: 20,
287                 salary: 5000 + 50 * i
288               };
289               valueBucketArray[i] = new SharedValuesBucket(v);
290             }
291             await taskpool.execute(create, context)
292             await taskpool.execute(insert, context, valueBucketArray)
293             let index = 0
294             let ret: collections.Array<SharedValuesBucket> =
295               await taskpool.execute(query, context) as collections.Array<SharedValuesBucket>
296             for (let v of ret.values()) {
297               console.info(`Row[${index}].id = ${v.id}`)
298               console.info(`Row[${index}].name = ${v.name}`)
299               console.info(`Row[${index}].age = ${v.age}`)
300               console.info(`Row[${index}].salary = ${v.salary}`)
301               index++
302             }
303             await taskpool.execute(clear, context)
304           })
305       }
306       .height('100%')
307       .width('100%')
308     }
309   }
310   ```
311