HarmonyOS 在关系数据库的两个表中查询同一条数据？

Question

比如下边的sql语句我该怎么使用 RdbStore 及predicates 的方法？麻烦帮转换下，我查阅了文档没找到相关的方法

//TAB_A，TAB_B是一个数据库中的两张表  
SELECT * FROM TAB_A a ,TAB_B b WHERE a.ID=b.ID ORDER BY ADD_SORT

Answer 1

通过querySql执行下面的语句，可以成功查询到数据

"SELECT a.ID id1,b.ID id2,a.NAME name1,b.NAME name2,a.AGE age1,b.AGE age2 FROM EMPLOYEE a ,EMPLOYEE2 b WHERE a.ID=b.ID"

支持别名的，下面的demo，可以试一下

1234顺序执行

import fs from '@ohos.file.fs';
import buffer from '@ohos.buffer';
import { ValuesBucket } from '@ohos.data.ValuesBucket';
import common from '@ohos.app.ability.common';
import relationalStore from '@ohos.data.relationalStore'; // 导入模块
import { BusinessError } from '@ohos.base';
import hilog from '@ohos.hilog';
import promptAction from '@ohos.promptAction';
import File from '@system.file';

let store: relationalStore.RdbStore | undefined = undefined;
let predicates = new relationalStore.RdbPredicates("EMPLOYEE");

const table_name: string = 'EMPLOYEE'

// 建表Sql语句1
const SQL_CREATE_TABLE =
  `CREATE TABLE IF NOT EXISTS ${table_name} (` +
    'ID INTEGER PRIMARY KEY AUTOINCREMENT, ' +
    'NAME TEXT NOT NULL, AGE INTEGER, ' +
    'CODES BLOB' +
    ')';

// 建表Sql语句2
const SQL_CREATE_TABLE2 =
  `CREATE TABLE IF NOT EXISTS EMPLOYEE2 (` +
    'ID INTEGER PRIMARY KEY AUTOINCREMENT, ' +
    'NAME TEXT NOT NULL, AGE INTEGER, ' +
    'CODES BLOB' +
    ')';

@Entry
@Component
struct Index {
  @State message: string = 'Hello World';
  @State database_version: number = 0;
  @State database_name: string = '未创建';
  @State database_flag: number = -1;
  @State create_flag: number = -1
  @State table_name1: string = 'EMPLOYEE'
  @State table_name2: string = 'EMPLOYEE2'
  context = getContext(this) as common.UIAbilityContext;

  build() {
    Column() {
      //1、数据库创建
      Row() {
        Text(`数据库名：${this.database_name}`)
          .textAlign(TextAlign.Start)
          .fontSize(12)//.border({ width: 1 })
          .padding(10)
          .width('50%')
      }
      .width('90%')
      .border({ width: 1 })

      //2、建表
      Row() {
        if (this.create_flag === 1) {
          Text(`表名1：${this.table_name1}`)
            .textAlign(TextAlign.Start)
            .fontSize(12)
            .padding(10)
            .width('50%')
        } else {
          Text(`表名：未建表`)
            .textAlign(TextAlign.Start)
            .fontSize(12)
            .padding(10)
            .width('50%')
        }
      }
      .width('90%')
      .border({ width: 1 })

      //2、建表2
      Row() {
        if (this.create_flag === 1) {
          Text(`表名2：${this.table_name2}`)
            .textAlign(TextAlign.Start)
            .fontSize(12)
            .padding(10)
            .width('50%')
        } else {
          Text(`表名：未建表`)
            .textAlign(TextAlign.Start)
            .fontSize(12)
            .padding(10)
            .width('50%')
        }
      }
      .width('90%')
      .border({ width: 1 })


      Button('1、创建数据库')
        .onClick(() => {
          if (this.database_flag === -1) {
            this.getDataStore()
          } else {
            if (this.database_flag === 1) {
              promptAction.showToast({
                message: '数据库已创建'
              });
            }
          }
        })


      Button('2、建表')
        .onClick(() => {
          if (this.database_flag === -1) {
            promptAction.showToast({
              message: '数据库未创建，请先创建数据库'
            });
            return;
          }
          this.createDatabase()
          this.createDatabase2()
        })

      Button('3、插入数据')
        .onClick(() => {
          this.insertDate()
          this.insertDate2()
        })

      Button('4、查询数据表')
        .onClick(() => {
          this.queryData3()
        })
    }
  }

  //获取store,创建数据库
  getDataStore() {
    //设置数据库基本配置信息
    const STORE_CONFIG: relationalStore.StoreConfig = {
      name: "RdbTest.db", // 数据库文件名
      securityLevel: relationalStore.SecurityLevel.S1// 数据库安全级别
      // encrypt: false, // 可选参数，指定数据库是否加密，默认不加密
      // dataGroupId: 'dataGroupID', // 可选参数，仅可在Stage模型下使用，表示为应用组ID，需要向应用市场获取。指定在此Id对应的沙箱路径下创建实例，当此参数不填时，默认在本应用沙箱目录下创建。
      // customDir: 'customDir/subCustomDir' // 可选参数，数据库自定义路径。数据库将在如下的目录结构中被创建：context.databaseDir + '/rdb/' + customDir，其中context.databaseDir是应用沙箱对应的路径，'/rdb/'表示创建的是关系型数据库，customDir表示自定义的路径。当此参数不填时，默认在本应用沙箱目录下创建RdbStore实例
    };

    relationalStore.getRdbStore(this.context, STORE_CONFIG).then(async (rdbStore: relationalStore.RdbStore) => {
      store = rdbStore; //操作关系型数据库
      console.info('成功获取RdbStore：' + store)
      this.database_name = STORE_CONFIG.name
      this.database_flag = 1
    }).catch((err: BusinessError) => {
      console.error(`获取RdbStore失败, code is ${err.code},message is ${err.message}`);
    })
  }

  //建表1
  createDatabase() {
    if (store != undefined) {
      (store as relationalStore.RdbStore).executeSql(SQL_CREATE_TABLE, (err) => {
        if (err) {
          console.error(`数据表创建失败, code is ${err.code},message is ${err.message}`);
          return;
        }
        console.info('数据表11111创建成功');
        this.create_flag = 1
        // 设置数据库版本
        if (store != undefined) {
          // this.database_version=(store as relationalStore.RdbStore).version+1;
          (store as relationalStore.RdbStore).version = 3;
          // 获取数据库版本
          console.info(`RdbStore version is ${store.version}`);
        }
      })
    }
  }

  //建表2
  createDatabase2() {
    if (store != undefined) {
      (store as relationalStore.RdbStore).executeSql(SQL_CREATE_TABLE2, (err) => {
        if (err) {
          console.error(`数据表创建失败, code is ${err.code},message is ${err.message}`);
          return;
        }
        console.info('数据表2222创建成功');
        this.create_flag = 1
        // 设置数据库版本
        if (store != undefined) {
          // this.database_version=(store as relationalStore.RdbStore).version+1;
          (store as relationalStore.RdbStore).version = 3;
          // 获取数据库版本
          console.info(`RdbStore version is ${store.version}`);
        }
      })
    }
  }

  //插入数据
  insertDate() {
    //数据初始化
    let value1 = 'Lisa';
    let value2 = 18;
    // 以下三种方式可用
    const valueBucket1: ValuesBucket = {
      'NAME': value1,
      'AGE': value2,
    };

    if (store != undefined) {
      (store as relationalStore.RdbStore).insert("EMPLOYEE", valueBucket1, (err: BusinessError, rowId: number) => {
        if (err) {
          console.error(`插入失败, code is ${err.code},message is ${err.message}`);
          return;
        }
        console.info(`插入成功1111, rowId是：${rowId}`);
        promptAction.showToast({
          message: `插入成功1111`
        });
      })
    }
  }

  //插入数据
  insertDate2() {
    //数据初始化
    let value1 = 'XIAOHONG';
    let value2 = 20;
    // 以下三种方式可用
    const valueBucket1: ValuesBucket = {
      'NAME': value1,
      'AGE': value2,
    };

    if (store != undefined) {
      (store as relationalStore.RdbStore).insert("EMPLOYEE2", valueBucket1, (err: BusinessError, rowId: number) => {
        if (err) {
          console.error(`插入失败, code is ${err.code},message is ${err.message}`);
          return;
        }
        console.info(`插入成功, rowId是：${rowId}`);
        promptAction.showToast({
          message: `插入成功`
        });
      })
    }
  }

  queryData3() {
    if (store != undefined) {
      (store as relationalStore.RdbStore).querySql("SELECT a.ID id1,b.ID id2,a.NAME name1,b.NAME name2,a.AGE age1,b.AGE age2 FROM EMPLOYEE a ,EMPLOYEE2 b WHERE a.ID=b.ID",
        (err, resultSet) => {
          if (err) {
            console.error(`Query failed, code is ${err.code},message is ${err.message}`);
            return;
          }
          console.info(`ResultSet column names: ${resultSet.columnNames}, column count: ${resultSet.columnCount}`);
          console.log("返回的行数:" + resultSet.rowCount);
          console.log("查询到列名:" + resultSet.columnNames);
          console.log("返回的列数:" + resultSet.columnCount);
          while (resultSet.goToNextRow()) {
            const id1 = resultSet.getLong(resultSet.getColumnIndex("id1"));
            const name1 = resultSet.getString(resultSet.getColumnIndex("name1"));
            const age1 = resultSet.getLong(resultSet.getColumnIndex("age1"));

            const id2 = resultSet.getLong(resultSet.getColumnIndex("id2"));
            const name2 = resultSet.getString(resultSet.getColumnIndex("name2"));
            const age2 = resultSet.getLong(resultSet.getColumnIndex("age2"));

            console.info(`id=${id1}, name=${name1}, age=${age1}`);
            console.info(`id2=${id2}, name2=${name2}, age2=${age2}`);

          }
          // 释放数据集的内存
          resultSet.close();
        })
    }
  }
}