DEV Community

Cover image for HarmonyOS development: addition, deletion, modification and inspection of relational database encapsulation
程序员一鸣
程序员一鸣

Posted on

HarmonyOS development: addition, deletion, modification and inspection of relational database encapsulation

Foreword

this article is based on Api12 

for persistent storage with less amount of data, we can choose user preferences or distributed key-value databases to operate. If the first two cannot be met, Hongmeng also provides us relational database similar to Android, relational database is based on SQLite component and provides a complete mechanism for managing local database. It also provides a series of interfaces for adding, deleting, modifying and checking data. It is very convenient. In order to operate data more conveniently, a simple package is currently provided for the Api of the system. 

The content of this article is roughly as follows: 

1. Remote address dependency

2. Database creation and table creation

3. Add, delete, change and check the database

4. Use summary

i. Remote Address Dependency

set the three-party package dependency in the oh-package.json5 of the project. The configuration example is as follows:

 

"dependencies": { "@abner/datastore": "^1.0.0"}
Enter fullscreen mode Exit fullscreen mode

OpenHarmony Tripartite Library Central Warehouse Address:

https://ohpm.openharmony.cn/#/cn/detail/@abner%2Fdatastore 

second, database creation and table creation.

1. Database creation

when the database is created, it will be created by default during initialization and can be initialized in the AbilityStage.

 

DbUtil.getInstance().init(this.context)
Enter fullscreen mode Exit fullscreen mode

Property Introduction

Property  type  overview 
context  Context  context 
storeConfig  relationalStore.StoreConfig  database-related configuration, which can be not transmitted by default, will create a default abner_data.db database, securityLevel is S3,encrypt is true. 

storeConfig 

property  type  overview 
name  string  the database file name, which is also the database unique identifier. 
securityLevel  SecurityLevel  set the database security level. 
encrypt  boolean  specifies whether the database is encrypted. By default, the database is not encrypted. true: Encryption. false: Not encrypted. 
dataGroupId  string The application Group ID, which needs to be obtained from the application market. Model Constraint: This property is only available under the Stage model. Starting with API version 10, this optional parameter is supported. Specifies to create an RdbStore instance in the sandbox path corresponding to this dataGroupId. If this parameter is not specified, the RdbStore instance is created in the application Sandbox Directory by default. 
customDir  string  the database custom path. Usage constraint: The database path size is limited to 128 bytes. If it exceeds this size, the library will fail to open and an error will be returned. 
autoCleanDirtyData  boolean  specifies whether to automatically clean up the data that is synchronized to the local after being deleted from the cloud. true indicates that the data is automatically cleaned up. false indicates that the data is manually cleaned up. By default, the data is automatically cleaned up. 

2. Data table creation

there are two ways to create data tables, one is to execute SQL statements and the other is to execute objects. 

SQL statement execution

 

DbUtil.getInstance()
  .executeSql("CREATE TABLE table_name(id INTEGER PRIMARY KEY AUTOINCREMENT,name VARCHAR(50),age INT)")
Enter fullscreen mode Exit fullscreen mode

object Form Execution [recommend]] 

the object form weakens the SQL statement, but it still creates the data table in the form of SQL. It is only the operation of the business layer. Without SQL operation, the execution is very simple and intuitive. The first parameter is the name of the data table, that is, table_name, and the second parameter is an object, that is, the field of the data table. For each field, necessary parameters such as primary key, self-increment, length, etc.

 

DbUtil.getInstance().createTable("table_name", {
  "id": { type: DbTableFieldType.INTEGER, isPrimaryKey: true, isAutoIncrement: true },
  "name": { type: DbTableFieldType.VARCHAR, length: 120 },
  "age": { type: DbTableFieldType.INT, length: 30 }
})
Enter fullscreen mode Exit fullscreen mode

Three, database addition, deletion, modification and inspection

1, increase

normal storage

You need to define a ValuesBucket object to pass.

 

const valueBucket1: ValuesBucket = {
  'name': "AbnerMing",
  'age': 18,
}
DbUtil.getInstance().insert("table_name", valueBucket1)
Enter fullscreen mode Exit fullscreen mode

Object Storage

 

let bean = new DbDataBean()
bean.name = "AbnerMing"
bean.age = 18
DbUtil.getInstance().insertBean("table_name", bean)
Enter fullscreen mode Exit fullscreen mode

2, delete

normal Delete 

you need to use the RdbPredicates object to set filtering conditions. You can directly view the official website Api.

 

let deleteRdbPredicates = new relationalStore.RdbPredicates("table_name");
deleteRdbPredicates.equalTo("id", 1);

DbUtil.getInstance().delete(deleteRdbPredicates)
Enter fullscreen mode Exit fullscreen mode

Object Delete 

this is similar to normal deletion, except that the RdbPredicates object is simply encapsulated and you need to call the filterRdbPredicates method to set the filter conditions.

 

DbUtil.getInstance()
  .filterRdbPredicates({ equalTo: { "id": 2 } })
  .deleteBean("table_name")
Enter fullscreen mode Exit fullscreen mode

3, change.

Ordinary Modification

 

const valueBucket: ValuesBucket = {
  'name': "ming",
  'age': 28,
}

let upDataRdbPredicates = new relationalStore.RdbPredicates("table_name");
upDataRdbPredicates.equalTo("id", 1)

DbUtil.getInstance().update(upDataRdbPredicates, valueBucket)
Enter fullscreen mode Exit fullscreen mode

object Modification 

the filterRdbPredicates method is the filter condition, that is, which piece of data you want to change.

 

let uBean = new DbDataBean()
uBean.name = "Ming"
uBean.age = 20
DbUtil.getInstance()
  .filterRdbPredicates({ equalTo: { "id": 2 } })
  .updateBean("table_name", uBean)
Enter fullscreen mode Exit fullscreen mode

4, check

common Query

 

let predicates = new relationalStore.RdbPredicates("table_name");

DbUtil.getInstance().query(predicates, (resultSet: relationalStore.ResultSet) => {
 while (resultSet.goToNextRow()) {
    const id = resultSet.getLong(resultSet.getColumnIndex("id"));
    const name = resultSet.getString(resultSet.getColumnIndex("name"));
    const age = resultSet.getLong(resultSet.getColumnIndex("age"));
  }
})
Enter fullscreen mode Exit fullscreen mode

object Query 

get All

DbUtil.getInstance()
  .queryAllBean<DbDataBean>("table_name", (data) => {

  })
Enter fullscreen mode Exit fullscreen mode

get single

DbUtil.getInstance()
  .filterRdbPredicates({ equalTo: { "id": 1 } })
  .queryBean<DbDataBean>("table_name", (data) => {

  })
Enter fullscreen mode Exit fullscreen mode

5. filterRdbPredicates

in the addition, deletion, modification and inspection of object operations, this method cannot be ignored, and the executable attributes are as follows: 

property  type  overview 
orderByDesc  string  configures the predicate to match the values in the field column of the data table to the column sorted in descending order.
orderByAsc  string  configure the predicate to match the values in the field column of the data table in ascending order. 
limitAs  number  predicate to set the maximum number of data records 
offsetAs  number  configure the predicate to specify the starting position of the returned result 
equalTo  {}  configure the predicate to match the field in the field column of the data table with the value value value. 
notEqualTo  {}  configure the predicate to match a field whose value is not value in the field column of the data table 
contains  {}  configure the predicate to match a field with value in the field column of the data table 
between  {}  configure the predicate to match the fields in the field column of the data table with values in the given range (including range boundaries), for example: low = high, with the equal sign. 

IV. Use Summary

Each method is reserved for multiple calling methods, such as using callback asynchronous callback or Promise asynchronous callback, or synchronous execution. In the process of using, you can selectively call according to your own business needs, and also expose the successful and failed methods respectively, which can be targeted to determine whether the execution is successful in the process of execution. 

The following cases:

DbUtil.getInstance().delete(deleteRdbPredicates,
                            (rows: number)=>{

                            },
                            (err: BusinessError)=>{

                            })
Enter fullscreen mode Exit fullscreen mode

Top comments (0)