1# RDB Development 2 3## When to Use 4 5A relational database (RDB) store allows you to manage local data with or without native SQL statements based on SQLite. 6 7 8## Available APIs 9Most of the RDB store APIs are asynchronous interfaces, which can use a callback or promise to return the result. This document uses the promise-based APIs as an example. For details about the APIs, see [Relational Database](../reference/apis/js-apis-data-rdb.md). 10 11### Creating or Deleting an RDB Store 12 13The following table describes the APIs available for creating and deleting an RDB store. 14 15**Table 1** APIs for creating and deleting an RDB store 16 17| API| Description| 18| -------- | -------- | 19|getRdbStore(config: StoreConfig, version: number): Promise<RdbStore> | Obtains an RDB store. This API uses a promise to return the result. You can set parameters for the RDB store based on service requirements and call APIs to perform data operations.<br>- **config**: configuration of the RDB store.<br>- **version**: version of the RDB store.| 20| deleteRdbStore(name: string): Promise<void> | Deletes an RDB store. This API uses a promise to return the result.<br>- **name**: name of the RDB store to delete.| 21 22### Managing Data in an RDB Store 23 24The **RDB** module provides APIs for inserting, deleting, updating, and querying data in a local RDB store. 25 26- **Inserting Data** 27 28 The **RDB** module provides APIs for inserting data through a **ValuesBucket** in a data table. If the data is inserted, the row ID of the data inserted will be returned; otherwise, **-1** will be returned. 29 30 **Table 2** API for inserting data 31 32 | Class| API| Description| 33 | -------- | -------- | -------- | 34 | RdbStore | insert(name: string, values: ValuesBucket): Promise<number> | Inserts a row of data into a table. This API uses a promise to return the result.<br>If the operation is successful, the row ID will be returned; otherwise, **-1** will be returned.<br>- **name**: name of the target table.<br>- **values**: data to be inserted into the table.| 35 36- **Updating Data** 37 38 Call the **update()** method to pass new data and specify the update conditions by using **RdbPredicates**. If the data is updated, the number of rows of the updated data will be returned; otherwise, **0** will be returned. 39 40 **Table 3** API for updating data 41 42 | Class| API| Description| 43 | -------- | -------- | -------- | 44 | RdbStore | update(values: ValuesBucket, rdbPredicates: RdbPredicates): Promise\<number> | Updates data based on the specified **RdbPredicates** object. This API uses a promise to return the result.<br>Return value: number of rows updated.<br>- **values**: data to update, which is stored in **ValuesBucket**.<br>- **rdbPredicates**: conditions for updating data.| 45 46- **Deleting Data** 47 48 Call the **delete()** method to delete data meeting the conditions specified by **RdbPredicates**. If the data is deleted, the number of rows of the deleted data will be returned; otherwise, **0** will be returned. 49 50 **Table 4** API for deleting data 51 52 | Class| API| Description| 53 | -------- | -------- | -------- | 54 | RdbStore | delete(rdbPredicates: RdbPredicates): Promise\<number> | Deletes data from the RDB store based on the specified **RdbPredicates** object. This API uses a promise to return the result.<br>Return value: number of rows updated.<br>- **rdbPredicates**: conditions for deleting data.| 55 56- **Querying data** 57 58 You can query data in an RDB store in either of the following ways: 59 60 - Call the **query()** method to query data based on the predicates, without passing any SQL statement. 61 - Run the native SQL statement. 62 63 **Table 5** APIs for querying data 64 65 | Class| API| Description| 66 | -------- | -------- | -------- | 67 | RdbStore | query(rdbPredicates: RdbPredicates, columns: Array): Promise<ResultSet> | Queries data from the RDB store based on specified conditions. This API uses a promise to return the result.<br>- **rdbPredicates**: conditions for querying data.<br>- **columns**: columns to query. If this parameter is not specified, the query applies to all columns.| 68 | RdbStore | querySql(sql: string, bindArgs?: Array<ValueType>): Promise<ResultSet> | Queries data using the specified SQL statement. This API uses a promise to return the result.<br>- **sql**: SQL statement.<br>- **bindArgs**: arguments in the SQL statement.| 69 70### Using Predicates 71 72The **RDB** module provides **RdbPredicates** for you to set database operation conditions. 73 74The following table lists common predicates. For more information about predicates, see [**RdbPredicates**](../reference/apis/js-apis-data-rdb.md#rdbpredicates). 75 76**Table 6** APIs for using RDB store predicates 77 78| Class| API| Description| 79| -------- | -------- | -------- | 80| RdbPredicates | equalTo(field: string, value: ValueType): RdbPredicates | Sets an **RdbPredicates** object to match the field with data type **ValueType** and value equal to the specified value.<br>- **field**: column name in the database table.<br>- **value**: value to match the **RdbPredicates**.<br>- **RdbPredicates**: **RdbPredicates** object created.| 81| RdbPredicates | notEqualTo(field: string, value: ValueType): RdbPredicates | Sets an **RdbPredicates** object to match the field with data type **ValueType** and value not equal to the specified value.<br>- **field**: column name in the database table.<br>- **value**: value to match the **RdbPredicates**.<br>- **RdbPredicates**: **RdbPredicates** object created.| 82| RdbPredicates | or(): RdbPredicates | Adds the OR condition to the **RdbPredicates** object.<br>- **RdbPredicates**: **RdbPredicates** with the OR condition.| 83| RdbPredicates | and(): RdbPredicates | Adds the AND condition to the **RdbPredicates** object.<br>- **RdbPredicates**: **RdbPredicates** with the AND condition.| 84| RdbPredicates | contains(field: string, value: string): RdbPredicats | Sets an **RdbPredicates** object to match a string that contains the specified value.<br>- **field**: column name in the database table.<br>- **value**: value to match the **RdbPredicates**.<br>- **RdbPredicates**: **RdbPredicates** object created.| 85 86 87### Using the Result Set 88 89A result set can be regarded as a row of data in the queried results. It allows you to traverse and access the data you have queried. 90 91For details about how to use result set APIs, see [Result Set](../reference/apis/js-apis-data-resultset.md). 92 93> **NOTICE**<br> 94> After a result set is used, you must call the **close()** method to close it explicitly. 95 96**Table 7** APIs for using the result set 97 98| Class| API| Description| 99| -------- | -------- | -------- | 100| ResultSet | goToFirstRow(): boolean | Moves to the first row of the result set.| 101| ResultSet | getString(columnIndex:number): string | Obtains the value in the form of a string based on the specified column and current row.| 102| ResultSet | getBlob(columnIndex:number): Uint8Array | Obtains the value in the form of a byte array based on the specified column and the current row.| 103| ResultSet | getDouble(columnIndex:number): number | Obtains the value in the form of double based on the specified column and current row.| 104| ResultSet | getLong(columnIndex:number): number | Obtains the value in the form of a long integer based on the specified column and current row. | 105| ResultSet | close(): void | Closes the result set.| 106 107 108 109### Setting Distributed Tables 110 111**Setting Distributed Tables** 112 113**Table 8** API for setting distributed tables 114 115| Class| API| Description| 116| -------- | -------- | -------- | 117| RdbStore | setDistributedTables(tables: Array\<string>): Promise\<void>; | Sets distributed tables. This API uses a promise to return the result.<br>- **tables**: names of the distributed tables to set.| 118 119**Obtaining the Distributed Table Name for a Remote Device** 120 121You can obtain the distributed table name for a remote device based on the local table name. The distributed table name can be used to query the RDB store of the remote device. 122 123**Table 9** API for obtaining the distributed table name of a remote device 124 125| Class| API| Description| 126| -------- | -------- | -------- | 127| RdbStore | obtainDistributedTableName(device: string, table: string): Promise\<string>; | Obtains the distributed table name for a remote device based on the local table name. The distributed table name is required when the RDB store of a remote device is queried.<br>- **device**: remote device.<br>- **table**: local table name.| 128 129**Synchronizing Data Between Devices** 130 131**Table 10** API for synchronizing data between devices 132 133| Class| API| Description| 134| -------- | -------- | -------- | 135| RdbStore | sync(mode: SyncMode, predicates: RdbPredicates): Promise<Array<[string, number]>>;| Synchronizes data between devices. This API uses a promise to return the result.<br>- **mode**: synchronization mode. **SYNC_MODE_PUSH** means to push data from the local device to a remote device. **SYNC_MODE_PULL** means to pull data from a remote device to the local device.<br>- **predicates**: specifies the data and devices to synchronize.<br>- **string**: device ID. <br>- **number**: synchronization status of that device. The value **0** indicates a successful synchronization. Other values indicate a synchronization failure. | 136 137**Registering an RDB Store Observer** 138 139**Table 11** API for registering an observer 140 141| Class| API| Description| 142| -------- | -------- | -------- | 143| RdbStore |on(event: 'dataChange', type: SubscribeType, observer: Callback<Array\<string>>): void;| Registers an observer for this RDB store to subscribe to distributed data changes. When data in the RDB store changes, a callback will be invoked to return the data changes.<br>- **type**: subscription type. **SUBSCRIBE_TYPE_REMOTE** means to subscribe to remote data changes.<br>- **observer**: observer that listens for data changes in the RDB store.| 144 145**Unregistering an RDB Store Observer** 146 147**Table 12** API for unregistering an observer 148 149| Class| API| Description| 150| -------- | -------- | -------- | 151| RdbStore |off(event:'dataChange', type: SubscribeType, observer: Callback<Array\<string>>): void;| Unregisters the observer of the specified type for the RDB store. This method uses a callback to return the result.<br>- **type**: subscription type. **SUBSCRIBE_TYPE_REMOTE** means to subscribe to remote data changes.<br>- **observer**: observer to unregister.| 152 153 154## How to Develop 155 1561. Create an RDB store. 157 1. Configure the RDB store attributes, including the RDB store name, storage mode, and whether read-only mode is used. 158 2. Initialize the table structure and related data in the RDB store. 159 3. Create an RDB store. 160 161 The sample code is as follows: 162 163 ```js 164 import data_rdb from '@ohos.data.rdb' 165 166 const CREATE_TABLE_TEST = "CREATE TABLE IF NOT EXISTS test (" + "id INTEGER PRIMARY KEY AUTOINCREMENT, " + "name TEXT NOT NULL, " + "age INTEGER, " + "salary REAL, " + "blobType BLOB)"; 167 const STORE_CONFIG = {name: "rdbstore.db"} 168 data_rdb.getRdbStore(STORE_CONFIG, 1, function (err, rdbStore) { 169 rdbStore.executeSql(CREATE_TABLE_TEST ) 170 console.info('create table done.') 171 }) 172 ``` 173 1742. Insert data. 175 1. Create a **ValuesBucket** to store the data you need to insert. 176 2. Call the **insert()** method to insert data into the RDB store. 177 178 The sample code is as follows: 179 180 ```js 181 var u8 = new Uint8Array([1, 2, 3]) 182 const valueBucket = {"name": "Tom", "age": 18, "salary": 100.5, "blobType": u8} 183 let insertPromise = rdbStore.insert("test", valueBucket) 184 ``` 185 1863. Query data. 187 1. Create an **RdbPredicates** object to specify query conditions. 188 2. Call the **query()** method to query data. 189 3. Call the **ResultSet()** method to obtain the query result. 190 191 The sample code is as follows: 192 193 ```js 194 let predicates = new data_rdb.RdbPredicates("test"); 195 predicates.equalTo("name", "Tom") 196 let promisequery = rdbStore.query(predicates) 197 promisequery.then((resultSet) => { 198 resultSet.goToFirstRow() 199 const id = resultSet.getLong(resultSet.getColumnIndex("id")) 200 const name = resultSet.getString(resultSet.getColumnIndex("name")) 201 const age = resultSet.getLong(resultSet.getColumnIndex("age")) 202 const salary = resultSet.getDouble(resultSet.getColumnIndex("salary")) 203 const blobType = resultSet.getBlob(resultSet.getColumnIndex("blobType")) 204 resultSet.close() 205 }) 206 ``` 207 2084. Set the distributed tables to be synchronized. 209 1. Set the distributed tables. 210 2. Check whether the setting is successful. 211 212 The sample code is as follows: 213 214 ```js 215 let promise = rdbStore.setDistributedTables(["test"]) 216 promise.then(() => { 217 console.info("setDistributedTables success.") 218 }).catch((err) => { 219 console.info("Failed to set distributed tables.") 220 }) 221 ``` 222 223 5. Synchronize data across devices. 224 1. Construct an **RdbPredicates** object to specify remote devices within the network to be synchronized. 225 2. Call the **sync()** method to synchronize data. 226 3. Check whether the data synchronization is successful. 227 228 The sample code is as follows: 229 230 ```js 231 let predicate = new data_rdb.RdbPredicates('test') 232 predicate.inDevices(['12345678abcde']) 233 let promise = rdbStore.sync(rdb.SyncMode.SYNC_MODE_PUSH, predicate) 234 promise.then((result) => { 235 console.log('sync done.') 236 for (let i = 0; i < result.length; i++) { 237 console.log('device=' + result[i][0] + ' status=' + result[i][1]) 238 } 239 }).catch((err) => { 240 console.log('sync failed') 241 }) 242 ``` 243 2446. Subscribe to distributed data. 245 1. Register an observer to listen for distributed data changes. 246 2. When data in the RDB store changes, a callback will be invoked to return the data changes. 247 248 The sample code is as follows: 249 250 ```js 251 function storeObserver(devices) { 252 for (let i = 0; i < devices.length; i++) { 253 console.log('device=' + devices[i] + ' data changed') 254 } 255 } 256 try { 257 rdbStore.on('dataChange', rdb.SubscribeType.SUBSCRIBE_TYPE_REMOTE, storeObserver) 258 } catch (err) { 259 console.log('Failed to register observer') 260 } 261 ``` 262 2637. Query data across devices. 264 1. Obtain the distributed table name for a remote device based on the local table name. 265 2. Call the **ResultSet()** method to obtain the query result. 266 267 The sample code is as follows: 268 269 ```js 270 let tableName = rdbStore.obtainDistributedTableName(deviceId, "test"); 271 let resultSet = rdbStore.querySql("SELECT * FROM " + tableName) 272 ``` 273