HarmonyOS应用开发实战 | ArkTS中SQLite数据库初始化详解

TodoApp/
├── src/
│   ├── main/
│   │   ├── ets/
│   │   │   ├── pages/
│   │   │   │   └── Index.ets  // 主页面
│   │   │   └── model/
│   │   │       └── TodoItem.ets  // 数据模型
│   │   └── resources/
│   └── ...

// src/main/ets/pages/Index.ets

import relationalStore from '@ohos.data.relationalStore';
import promptAction from '@ohos.promptAction';
import { BusinessError } from '@ohos.base';

// 待办事项数据接口
interface TodoItem {
  id?: number;
  title: string;
  description: string;
  isCompleted: boolean;
  createTime: number;
}

// 示例数据项接口
interface SampleItem {
  title: string;
  description: string;
  isCompleted: boolean;
}

@Entry
@Component
struct Index {
  @State todoItems: TodoItem[] = [];
  @State newTodoText: string = '';
  private storeConfig: relationalStore.StoreConfig = {
    name: 'TodoDatabase.db',
    securityLevel: relationalStore.SecurityLevel.S1
  };
  private rdbStore: relationalStore.RdbStore | null = null;
  private tableName: string = 'TodoTable';

  aboutToAppear() {
    // 初始化数据库
    this.initDatabase();
  }

  async initDatabase() {
    try {
      // 获取上下文
      const context = getContext(this);

      // 获取RDB数据库实例
      this.rdbStore = await relationalStore.getRdbStore(context, this.storeConfig);

      // 创建表SQL语句
      const createTableSql = `
        CREATE TABLE IF NOT EXISTS ${this.tableName} (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          title TEXT NOT NULL,
          description TEXT,
          isCompleted INTEGER DEFAULT 0,
          createTime INTEGER
        )`;

      // 执行创建表操作
      await this.rdbStore.executeSql(createTableSql);
      console.info('Database and table created successfully');

      // 加载待办事项
      this.loadTodoItems();

      // 如果没有数据，添加一些示例数据
      if (this.todoItems.length === 0) {
        await this.addSampleData();
      }
    } catch (error) {
      const err = error as BusinessError;
      console.error(`Failed to initialize database: ${err.message}`);
      promptAction.showToast({
        message: '初始化数据库失败',
        duration: 2000
      });
    }
  }

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

    // 定义示例数据，使用SampleItem接口
    const sampleItems: SampleItem[] = [
      { title: '完成项目报告', description: '周五之前提交给经理', isCompleted: false },
      { title: '购买生日礼物', description: '妈妈的生日在下周三', isCompleted: true },
      { title: '预约医生', description: '年度体检', isCompleted: false },
      { title: '学习HarmonyOS开发', description: '完成ArkTS基础课程', isCompleted: false }
    ];

    try {
      for (const item of sampleItems) {
        const valuesBucket: relationalStore.ValuesBucket = {
          'title': item.title,
          'description': item.description,
          'isCompleted': item.isCompleted ? 1 : 0,
          'createTime': new Date().getTime() - Math.floor(Math.random() * 86400000) // 随机时间
        };

        await this.rdbStore.insert(this.tableName, valuesBucket);
      }

      console.info('Sample data added successfully');
      this.loadTodoItems();
    } catch (error) {
      const err = error as BusinessError;
      console.error(`Failed to add sample data: ${err.message}`);
    }
  }

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

    try {
      // 定义查询条件
      const predicates = new relationalStore.RdbPredicates(this.tableName);
      predicates.orderByDesc('createTime');

      // 执行查询
      const resultSet = await this.rdbStore.query(predicates, ['id', 'title', 'description', 'isCompleted', 'createTime']);

      // 清空当前列表
      this.todoItems = [];

      // 遍历结果集
      while (resultSet.goToNextRow()) {
        const id = resultSet.getLong(resultSet.getColumnIndex('id'));
        const title = resultSet.getString(resultSet.getColumnIndex('title'));
        const description = resultSet.getString(resultSet.getColumnIndex('description'));
        const isCompleted = resultSet.getLong(resultSet.getColumnIndex('isCompleted')) === 1;
        const createTime = resultSet.getLong(resultSet.getColumnIndex('createTime'));

        // 添加到列表
        this.todoItems.push({
          id: id,
          title: title,
          description: description,
          isCompleted: isCompleted,
          createTime: createTime
        });
      }

      // 关闭结果集
      resultSet.close();
    } catch (error) {
      const err = error as BusinessError;
      console.error(`Failed to load todo items: ${err.message}`);
      promptAction.showToast({
        message: '加载待办事项失败',
        duration: 2000
      });
    }
  }

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

    if (!this.newTodoText.trim()) {
      promptAction.showToast({
        message: '请输入待办事项内容',
        duration: 2000
      });
      return;
    }

    try {
      // 创建待办事项数据
      const valuesBucket: relationalStore.ValuesBucket = {
        'title': this.newTodoText,
        'description': '',
        'isCompleted': 0,
        'createTime': new Date().getTime()
      };

      // 插入数据
      const rowId = await this.rdbStore.insert(this.tableName, valuesBucket);
      console.info(`Inserted row ID: ${rowId}`);

      // 清空输入框
      this.newTodoText = '';

      // 重新加载数据
      this.loadTodoItems();

      promptAction.showToast({
        message: '添加成功',
        duration: 2000
      });
    } catch (error) {
      const err = error as BusinessError;
      console.error(`Failed to add todo item: ${err.message}`);
      promptAction.showToast({
        message: '添加待办事项失败',
        duration: 2000
      });
    }
  }

  async toggleTodoItem(id: number, isCompleted: boolean) {
    if (!this.rdbStore) return;

    try {
      // 创建更新数据
      const valuesBucket: relationalStore.ValuesBucket = {
        'isCompleted': isCompleted ? 0 : 1
      };

      // 定义更新条件
      const predicates = new relationalStore.RdbPredicates(this.tableName);
      predicates.equalTo('id', id);

      // 执行更新
      const rows = await this.rdbStore.update(valuesBucket, predicates);
      console.info(`Updated rows: ${rows}`);

      // 重新加载数据
      this.loadTodoItems();
    } catch (error) {
      const err = error as BusinessError;
      console.error(`Failed to update todo item: ${err.message}`);
      promptAction.showToast({
        message: '更新待办事项失败',
        duration: 2000
      });
    }
  }

  async deleteTodoItem(id: number) {
    if (!this.rdbStore) return;

    try {
      // 定义删除条件
      const predicates = new relationalStore.RdbPredicates(this.tableName);
      predicates.equalTo('id', id);

      // 执行删除
      const rows = await this.rdbStore.delete(predicates);
      console.info(`Deleted rows: ${rows}`);

      // 重新加载数据
      this.loadTodoItems();

      promptAction.showToast({
        message: '删除成功',
        duration: 2000
      });
    } catch (error) {
      const err = error as BusinessError;
      console.error(`Failed to delete todo item: ${err.message}`);
      promptAction.showToast({
        message: '删除待办事项失败',
        duration: 2000
      });
    }
  }

  build() {
    Column() {
      // 标题
      Text('待办事项')
        .fontSize(28)
        .fontWeight(FontWeight.Bold)
        .margin({ top: 20, bottom: 20 })

      // 输入框和添加按钮
      Row() {
        TextInput({ placeholder: '请输入待办事项...', text: this.newTodoText })
          .onChange((value) => {
            this.newTodoText = value;
          })
          .layoutWeight(1)
          .height(50)
          .margin({ right: 10 })
          .backgroundColor('#F0F0F0')
          .borderRadius(25)
          .padding({ left: 20, right: 20 })

        Button('添加')
          .onClick(() => {
            this.addTodoItem();
          })
          .height(50)
          .width(80)
          .borderRadius(25)
          .backgroundColor('#0A59F7')
      }
      .width('90%')
      .margin({ bottom: 20 })

      // 待办事项列表
      List({ space: 8, initialIndex: 0 }) {
        ForEach(this.todoItems, (item: TodoItem) => {
          ListItem() {
            Column() {
              Row() {
                Checkbox()
                  .select(item.isCompleted)
                  .onChange(() => {
                    this.toggleTodoItem(item.id!, item.isCompleted);
                  })
                  .margin({ right: 10 })

                Text(item.title)
                  .fontSize(16)
                  .decoration({ type: item.isCompleted ? TextDecorationType.LineThrough : TextDecorationType.None })
                  .fontColor(item.isCompleted ? '#999999' : '#333333')
                  .layoutWeight(1)

                Button({ type: ButtonType.Circle, stateEffect: true }) {
                  Row() {
                    Text('删')
                      .fontSize(14)
                      .fontWeight(FontWeight.Normal)
                      .fontColor('#FFFFFF')
                  }
                  .width('100%')
                  .height('100%')
                  .justifyContent(FlexAlign.Center)
                }
                .width(36)
                .height(36)
                .backgroundColor('#FF4757')
                .onClick(() => {
                  this.deleteTodoItem(item.id!);
                })
              }
              .width('100%')
              .alignItems(VerticalAlign.Center)

              if (item.description) {
                Text(item.description)
                  .fontSize(14)
                  .fontColor('#666666')
                  .margin({ top: 5, left: 34 })
                  .opacity(item.isCompleted ? 0.6 : 1.0)
              }
            }
            .width('100%')
            .padding(15)
            .borderRadius(12)
            .backgroundColor(Color.White)
            .shadow({
              radius: 6,
              color: '#20000000',
              offsetX: 0,
              offsetY: 2
            })
          }
        })
      }
      .width('90%')
      .layoutWeight(1)

      // 底部状态栏
      Row() {
        Text(`共 ${this.todoItems.length} 项`)
          .fontSize(14)
          .fontColor('#666666')

        Text(`已完成 ${this.todoItems.filter(item => item.isCompleted).length} 项`)
          .fontSize(14)
          .fontColor('#27AE60')
      }
      .width('90%')
      .justifyContent(FlexAlign.SpaceBetween)
      .padding({ top: 10, bottom: 10 })
    }
    .width('100%')
    .height('100%')
    .backgroundColor('#F1F3F5')
    .padding({ top: 10, bottom: 10 })
  }
}

// 在module.json5中添加数据权限
"requestPermissions": [
  {
    "name": "ohos.permission.DISTRIBUTED_DATASYNC"
  }
]

// 使用日志输出SQL语句进行调试
console.info(`Executing SQL: ${sql}`);

// 在StoreConfig中指定版本号
const storeConfig = {
  name: 'TodoDatabase.db',
  securityLevel: relationalStore.SecurityLevel.S1,
  version: 2 // 指定版本号
};