1# Batch Data Writing to the Database
2
3## Using TaskPool for Frequent Database Operations
4
5In scenarios where frequent database operations are required, it is time-consuming to read and write the database. Therefore, you are advised to perform operations in sub-threads to avoid blocking the UI thread.
6
7The TaskPool capability provided by ArkTS can be used to move database operation tasks to subthreads. The implementation is as follows:
8
91. Create multiple subtasks and supports operations such as database creation, insertion, query, and clearing.
10
112. The UI main thread calls subtasks to perform database operations such as adding, deleting, modifying, and querying data.
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  // The default database file path is context.databaseDir + rdb + StoreConfig.name.
26  let store: relationalStore.RdbStore = await relationalStore.getRdbStore(context, CONFIG);
27  console.info(`Create Store.db successfully!`);
28
29  // Create a table.
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  // The default database file path is context.databaseDir + rdb + StoreConfig.name.
48  let store: relationalStore.RdbStore = await relationalStore.getRdbStore(context, CONFIG);
49  console.info(`Create Store.db successfully!`);
50
51  // Insert data.
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  // The default database file path is context.databaseDir + rdb + StoreConfig.name.
63  let store: relationalStore.RdbStore = await relationalStore.getRdbStore(context, CONFIG);
64  console.info(`Create Store.db successfully!`);
65
66  // Obtain the result set.
67  let predicates: relationalStore.RdbPredicates = new relationalStore.RdbPredicates("test");
68  let resultSet = await store.query (predicates); // Query all data.
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  // The default database file path is 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          // Prepare data.
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## Using Sendable for Large-Capacity Database Operations
143
144It takes a long time to transfer database data across threads. When the data volume is large, the UI main thread is still occupied. You are advised to use Sendable to encapsulate database data to reduce the cross-thread overhead.
145
1461. Define the data format in the database. Sendable can be used to reduce the cross-thread time consumption.
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. Initiated by the UI main thread to add, delete, modify, and query data in subthreads.
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     // The default database file path is context.databaseDir + rdb + StoreConfig.name.
189     let store: relationalStore.RdbStore = await relationalStore.getRdbStore(context, CONFIG);
190     console.info(`Create Store.db successfully!`);
191
192     // Create a table.
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     // The default database file path is context.databaseDir + rdb + StoreConfig.name.
211     let store: relationalStore.RdbStore = await relationalStore.getRdbStore(context, CONFIG);
212     console.info(`Create Store.db successfully!`);
213
214     // Insert data.
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     // The default database file path is context.databaseDir + rdb + StoreConfig.name.
226     let store: relationalStore.RdbStore = await relationalStore.getRdbStore(context, CONFIG);
227     console.info(`Create Store.db successfully!`);
228
229     // Obtain the result set.
230     let predicates: relationalStore.RdbPredicates = new relationalStore.RdbPredicates("test");
231     let resultSet = await store.query (predicates); // Query all data.
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     // The default database file path is 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             // Prepare data.
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