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