DEV Community

harmonyPreacher
harmonyPreacher

Posted on

HarmonyOS 5 Relational Database Operation Guide: CRUD Practice

HarmonyOS 5 Relational Database Operation Guide: CRUD Practice

Preface

In HarmonyOS 5 application development, data persistence is a crucial aspect. Relational Database (RDB) provides a structured data storage method, which is ideal for storing complex data that requires frequent querying and manipulation. This article details how to use the @ohos.data.relationalStore module to perform create, read, update, and delete (CRUD) operations on databases in HarmonyOS 5 applications.

I. Environment Preparation

First, ensure that your DevEco Studio has the HarmonyOS 5 development environment configured, and add the necessary permissions in the module.json5 file:

"requestPermissions": [
  {
    "name": "ohos.permission.DISTRIBUTED_DATASYNC"
  }
]
Enter fullscreen mode Exit fullscreen mode

II. Database Initialization

1. Create Database and Table

We first need to initialize the database and create a table:

const SQL_CREATE_TABLE = `
  CREATE TABLE IF NOT EXISTS EMPLOYEE (
    ID INTEGER PRIMARY KEY AUTOINCREMENT,
    NAME TEXT NOT NULL,
    AGE INTEGER,
    SALARY REAL,
    Codes TEXT
  )`;

private async initRdbStore() {
  try {
    this.rdbStore = await relationalStore.getRdbStore(getContext(), {
      name: 'HarmonyOS 5.db',
      securityLevel: relationalStore.SecurityLevel.S1
    });
    await this.rdbStore.executeSql(SQL_CREATE_TABLE);
    console.info('Table created successfully');
    this.query(); // Initial data loading
  } catch (err) {
    console.error(`Database init failed: ${JSON.stringify(err)}`);
  }
}
Enter fullscreen mode Exit fullscreen mode

Key Points:

  • The getRdbStore() method is used to obtain or create a database.
  • SecurityLevel.S1 represents the database security level.
  • executeSql() is used to execute SQL statements for table creation.

III. CRUD Operation Implementation

1. Insert Data (Create) 

async insert() {
  if (!this.rdbStore) return;

  const employee: Employee = {
    id: null,
    name: 'zz',
    age: 18,
    salary: 100.86,
    Codes: 'Harmony'
  };

  try {
    const rowId = await this.rdbStore.insert("EMPLOYEE", employee);
    console.info(`Insert success, rowId: ${rowId}`);
    employee.id = rowId;
    this.employees = [...this.employees, employee];
  } catch (err) {
    this.handleError(err as BusinessError, 'Insert');
  }
}
Enter fullscreen mode Exit fullscreen mode

2. Query Data (Read) 

async query() {
  if (!this.rdbStore) return;

  const predicates = new relationalStore.RdbPredicates("EMPLOYEE");
  const columns = ["ID", "NAME", "AGE", "SALARY", "Codes"];

  try {
    const resultSet = await this.rdbStore.query(predicates, columns);
    this.processResultSet(resultSet);
  } catch (err) {
    this.handleError(err as BusinessError, 'Query');
  }
}

private processResultSet(resultSet: relationalStore.ResultSet) {
  const temp: Employee[] = [];
  if (resultSet.rowCount > 0) {
    while (resultSet.goToNextRow()) {
      temp.push({
        id: resultSet.getLong(0),
        name: resultSet.getString(1),
        age: resultSet.getLong(2),
        salary: resultSet.getDouble(3),
        Codes: resultSet.getString(4)
      });
    }
  }
  resultSet.close(); // ResultSet must be closed
  this.employees = [...temp];
}
Enter fullscreen mode Exit fullscreen mode

3. Update Data (Update) 

async update() {
  if (!this.rdbStore) return;

  const updateValues: ValuesBucket = {
    Codes: 'Java' // Only update the required field
  };

  const predicates = new relationalStore.RdbPredicates("EMPLOYEE");
  predicates.equalTo('NAME', 'zz');

  try {
    const affectedRows = await this.rdbStore.update(updateValues, predicates);
    console.info(`Updated ${affectedRows} rows`);
    this.query();
  } catch (err) {
    this.handleError(err as BusinessError, 'Update');
  }
}
Enter fullscreen mode Exit fullscreen mode

4. Delete Data (Delete) 

async remove() {
  if (!this.rdbStore) return;

  const predicates = new relationalStore.RdbPredicates("EMPLOYEE");
  predicates.equalTo("ID", 2); // Delete the record with ID 2

  try {
    const affectedRows = await this.rdbStore.delete(predicates);
    console.info(`Deleted ${affectedRows} rows`);
    this.query(); // Refresh after deletion
  } catch (err) {
    this.handleError(err as BusinessError, 'Delete');
  }
}
Enter fullscreen mode Exit fullscreen mode

IV. UI Interface Implementation

We use ArkUI to build a simple interface to display operation buttons and a data table:

@Builder
buildActionButtons() {
  Row() {
    Button('Add')
      .onClick(() => this.insert())
      .type(ButtonType.Capsule)

    Button('Delete')
      .onClick(() => this.remove())
      .type(ButtonType.Capsule)
      .margin({ left: 10 })

    Button('Update')
      .onClick(() => this.update())
      .type(ButtonType.Capsule)
      .margin({ left: 10 })

    Button('View')
      .onClick(() => this.query())
      .type(ButtonType.Capsule)
      .margin({ left: 10 })
  }
  .justifyContent(FlexAlign.Center)
  .margin({ bottom: 20 })
}

@Builder
buildDataTable() {
  Column() {
    // Table header
    this.buildTableHeader()

    // Data rows
    List({ space: 8 }) {
      ForEach(this.employees, (item: Employee) => {
        ListItem() {
          this.buildTableRow(item)
        }
      }, (item: Employee) => item.id?.toString() ?? '')
    }
    .layoutWeight(1)
    .divider({ strokeWidth: 1, color: Color.Gray })
  }
  .borderRadius(8)
  .border({ width: 1, color: Color.Gray })
  .padding(8)
}
Enter fullscreen mode Exit fullscreen mode

V. Summary

This article details CRUD operations for HarmonyOS 5 relational databases, including:

  • Creation of databases and tables
  • Insertion, querying, updating, and deletion of data
  • Building query conditions using RdbPredicates
  • Traversal and processing of result sets
  • Complete UI implementation

Top comments (0)