next Relational Database

Relational Database Operations

Initializing the Database

async initsqlite() {
  // Construct a StoreConfig object
  const STORE_CONFIG: relationalStore.StoreConfig = {
    name: 'RdbTest.db', // Database file name
    securityLevel: relationalStore.SecurityLevel.S1, // Database security level
    encrypt: false, // Optional parameter indicating whether the database is encrypted
    customDir: 'customDir/subCustomDir', // Optional parameter for a custom database path
    isReadOnly: false // Optional parameter indicating whether the database is read - only
  };

  // Check the database version and perform upgrade or downgrade operations if necessary
  // Default database version is 0, table structure: CLIENT_USER (ID INTEGER PRIMARY KEY AUTOINCREMENT, ACCOUNT TEXT NOT NULL, PASSWORD TEXT NOT NULL)
  const SQL_CREATE_TABLE =
    'CREATE TABLE IF NOT EXISTS CLIENT_USER (ID INTEGER PRIMARY KEY AUTOINCREMENT, ACCOUNT TEXT NOT NULL, PASSWORD TEXT NOT NULL)'; // CREATE TABLE SQL statement

  // Obtain a relevant RdbStore to operate the relational database
  relationalStore.getRdbStore(this.context, STORE_CONFIG, (err, store) => {
    if (err) {
      console.error(`Failed to get RdbStore. Code: ${err.code}, message: ${err.message}`);
      return;
    }
    console.info('Successfully obtained RdbStore.');
    // When the database is created, the default version is 0
    if (store.version === 0) {
      store.executeSql(SQL_CREATE_TABLE); // Create the data table
      // Set the database version to an integer greater than 0
      store.version = 1;
    }
    // Assign the obtained RdbStore instance to the current rdbStore object
    this.rdbStore = store;
  });
}

Inserting Data

addUserInfo() {
  if (this.account === undefined || this.account === '') {
    console.info('Please enter an account');
    return;
  }
  if (this.password === undefined || this.password === '') {
    console.info('Please enter a password');
    return;
  }
  // Use ArkData RDB to store user information in a table - based manner
  if (this.rdbStore !== undefined) {
    // Construct a ValuesBucket object required for the insert operation
    const userInfo: relationalStore.ValuesBucket = {
      ACCOUNT: this.account,
      PASSWORD: this.password
    };
    // Insert data into the specified table
    this.rdbStore.insert('CLIENT_USER', userInfo, (err: BusinessError, rowId: number) => {
      // If insertion is successful, return the row ID in the table; otherwise, return -1
      if (rowId === -1) {
        console.error(`Failed to insert data. Code: ${err.code}, message: ${err.message}`);
        return;
      }
      console.info(`Data inserted successfully. Row ID: ${rowId}`);
    });
  }
}

Querying Data

Querying All Data

queryUserInfoData() {
  if (this.rdbStore !== undefined) {
    // Test query
    this.rdbStore.querySql('SELECT ID, ACCOUNT, PASSWORD FROM CLIENT_USER ORDER BY ID DESC;',
      (err: BusinessError, resultSet) => {
        if (err) {
          console.error(`Failed to read the database table. Code: ${err.code}, message: ${err.message}`);
          return;
        }
        console.info(`Query result (object form): ${resultSet}`);
        // resultSet is a cursor over a collection of data, initially pointing to the -1st record, with valid data starting from 0.
        while (resultSet.goToNextRow()) {
          const id = resultSet.getLong(resultSet.getColumnIndex('ID'));
          const account = resultSet.getString(resultSet.getColumnIndex('ACCOUNT'));
          const passwd = resultSet.getString(resultSet.getColumnIndex('PASSWORD'));
          console.info(`id=${id}, account=${account}, passwd=${passwd}`);
          const userInfo = new UserInfo(id, account, passwd);
          this.listUserInfo.push(userInfo);
        }
        // Release the memory of the dataset
        resultSet.close();
      });
  }
}

Querying Specific Field Data

queryUserInfoByName(name: string) {
  const predicates2 = new relationalStore.RdbPredicates('CLIENT_USER');
  predicates2.equalTo('ACCOUNT', name);
  if (this.rdbStore !== undefined) {
    this.rdbStore.query(predicates2, ['ID', 'ACCOUNT', 'PASSWORD'], (err: BusinessError, resultSet) => {
      if (err) {
        console.error(`Failed to query data. Code: ${err.code}, message: ${err.message}`);
        return;
      }
      console.info(`ResultSet column names: ${resultSet.columnNames}, column count: ${resultSet.columnCount}`);
      // resultSet is a cursor over a collection of data, initially pointing to the -1st record, with valid data starting from 0.
      while (resultSet.goToNextRow()) {
        const id = resultSet.getLong(resultSet.getColumnIndex('ID'));
        const account = resultSet.getString(resultSet.getColumnIndex('ACCOUNT'));
        const passwd = resultSet.getString(resultSet.getColumnIndex('PASSWORD'));
        console.info(`id=${id}, account=${account}, passwd=${passwd}`);
        const userInfo = new UserInfo(id, account, passwd);
        this.listUserInfo.push(userInfo);
      }
      // Release the memory of the dataset
      resultSet.close();
    });
  }
}

Updating Data

updateUserInfo() {
  const userInfo: relationalStore.ValuesBucket = {
    ACCOUNT: "18674049006",
    PASSWORD: "1234562521"
  };
  const predicates1 = new relationalStore.RdbPredicates('CLIENT_USER');
  predicates1.equalTo('ACCOUNT', '18674049006');
  if (this.rdbStore !== undefined) {
    this.rdbStore.update(userInfo, predicates1, (err: BusinessError, rows: number) => {
      if (err) {
        console.error(`Failed to update data. Code: ${err.code}, message: ${err.message}`);
        return;
      }
      console.info(`Data updated successfully. Rows affected: ${rows}`);
    });
  }
}