【鸿蒙学习笔记】关系型数据库概述

关系型数据库的运行机制

1、 关系型数据库对应用提供通用的操作接口，底层使用SQLite作为持久化存储引擎，支持SQLite具有的数据库特性，包括但不限于事务、索引、视图、触发器、外键、参数化查询和预编译SQL语句。

样例代码

共通方法 DBUtils

import relationalStore from '@ohos.data.relationalStore'
import { common } from '@kit.AbilityKit'export class DBUtils {// 数据库名称private tableName: string = 'accountTable'// 建表语句private sqlCreate: string = 'CREATE TABLE IF NOT EXISTS accountTable(id INTEGER PRIMARY KEY AUTOINCREMENT, accountType INTEGER, typeText TEXT, amount INTEGER)'// 表字段private columns: string[] = ['id', 'accountType', 'typeText', 'amount']// 数据库核心类private rdbStore: relationalStore.RdbStore | null = null// 数据库配置DB_CONFIG: relationalStore.StoreConfig = {name: 'RdbTest.db', // 数据库文件名securityLevel: relationalStore.SecurityLevel.S1, // 数据库安全级别};/*** 获取rdb* @param context：上下文* @param callback：回调函数，我们第一次获取数据时，需要在获取到rdb之后才能获取，所以有此回调*/getRdbStore(context: common.UIAbilityContext, callback: Function) {relationalStore.getRdbStore(context, this.DB_CONFIG, (error, store) => {if (this.rdbStore !== null) {//如果已经有rdb，直接建表store.executeSql(this.sqlCreate)return}//保存rdb，下边会用this.rdbStore = store//建表store.executeSql(this.sqlCreate)console.log("test", "successed get dbStore")if (callback) callback()})}/*** 插入数据* @param data：数据对象* @param callback：回调函数，这里的结果是通过回调函数返回的（也可使用返回值）*/insertData(data: AccountData, callback: Function) {//将数据对象，转换为ValuesBucket类型const valueBucket: relationalStore.ValuesBucket = generateValueBucket(data);// 调用insert插入数据this.rdbStore && this.rdbStore.insert(this.tableName, valueBucket, (err, res) => {if (err) {console.log("test，插入失败", err)callback(-1)return}console.log("test，插入成功", res)callback(res) //res为行号})}/*** 获取数据* @param callback：接收结果的回调函数*/query(callback: Function) {//predicates是用于添加查询条件的let predicates = new relationalStore.RdbPredicates(this.tableName)// 查询所有，不需要条件// predicates.equalTo("字段",数据)this.rdbStore && this.rdbStore.query(predicates, this.columns, (error, resultSet: relationalStore.ResultSet) => {if (error) {console.log("test,获取数据失败", JSON.stringify(error))return}let rowCount: number = resultSet.rowCountconsole.log("test", "数据库中数据数量：" + rowCount) //没数据时返回-1或0if (rowCount <= 0 || typeof rowCount === 'string') {callback([])return}let result: AccountData[] = []//上来必须调用一次goToNextRow，让游标处于第一条数据，while(resultSet.goToNextRow())是最有写法while (resultSet.goToNextRow()) {let accountData: AccountData = { id: 0, accountType: 0, typeText: '', amount: 0 }accountData.id = resultSet.getDouble(resultSet.getColumnIndex('id'));accountData.typeText = resultSet.getString(resultSet.getColumnIndex('typeText'))accountData.accountType = resultSet.getDouble(resultSet.getColumnIndex('accountType'))accountData.amount = resultSet.getDouble(resultSet.getColumnIndex('amount'))result.push(accountData)}callback(result)resultSet.close() //释放数据集内容})}
}function generateValueBucket(account: AccountData): relationalStore.ValuesBucket {let obj: relationalStore.ValuesBucket = {};obj.accountType = account.accountType;obj.typeText = account.typeText;obj.amount = account.amount;return obj;
}export class AccountData {id: number = -1;accountType: number = 0;typeText: string = '';amount: number = 0;
}

Index 代码

import { AccountData, DBUtils } from '../uitls/DBUtils';
import { common } from '@kit.AbilityKit';@Entry
@Component
struct Index_DBPage {// 数据库工具类private dbUtils: DBUtils = new DBUtils()private context: common.UIAbilityContext = getContext(this) as common.UIAbilityContext// 账户数据数组@State accountDataArray: AccountData[] = []// 列表的图片数组imageArr: Resource[] = [$r('app.media.foods'), $r('app.media.games'), $r('app.media.fuel')]// 添加数据弹框addDialogController: CustomDialogController = new CustomDialogController({builder: AddDialog({//点击确定的回调confirm: (insertData: AccountData) => {this.onConfirm(insertData)}})})// 界面打开时，查询数据，展示胡静aboutToAppear(): void {this.dbUtils.getRdbStore(this.context, () => {this.queryData()})}// 查询数据方法queryData() {this.dbUtils.query((result: AccountData[]) => {this.accountDataArray = resultconsole.log("test，获取数据成功：", JSON.stringify(this.accountDataArray))})}// 点击确定回调onConfirm(insertData: AccountData) {console.log("test", JSON.stringify(insertData))// 插入数据this.dbUtils.insertData(insertData, (res: number) => {if (res > 0) {// AlertDialog.show({ message: "添加成功" })this.queryData()} else {AlertDialog.show({ message: "添加失败" })}})}build() {Stack() {Column() {Row() {Text('关系型数据库').height(33).fontSize(24).margin({ left: 24 }).fontColor(Color.Red)}.width('100%').justifyContent(FlexAlign.SpaceBetween).margin(12).backgroundColor(Color.Grey)//数据列表List({ space: 20 }) {ForEach(this.accountDataArray, (item: AccountData, index: number) => {ListItem() {Row() {// 图片Image(this.imageArr[item.accountType]).width(40).aspectRatio(1).margin({ right: 16 })// 内容Text(item.typeText).height(22).fontSize(16)Blank().layoutWeight(1)// 金额Text("￥: " + String(item.amount)).height(22).fontSize(16)}.width('90%').padding({ left: 12, right: 12 }).margin({ left: 20 }).backgroundColor('#f1f3f5').padding(10).borderRadius(30)}})}}.width('100%')Button() {Image($r('app.media.add'))}.width(48).height(48).position({ x: '80%', y: '90%' }).onClick(() => {this.addDialogController.open()})}}
}interface Item {icon: Resource;text: string;
}@CustomDialog
struct AddDialog {controller: CustomDialogController;//确认回调confirm?: (insertData: AccountData) => voiditems: Array<Item> = [{ icon: $r('app.media.foods'), text: '吃饭' },{ icon: $r('app.media.games'), text: '娱乐' },{ icon: $r('app.media.fuel'), text: '加油' },]@State currentIndex: number = -1@State money: number = 0build() {Column() {Row() {ForEach(this.items, (item: Item, index) => {Column() {Image(item.icon).width(40).height(40)Text(item.text).fontSize(12).fontColor('#FF007DFF').margin({ top: 8 })}.width(86).aspectRatio(1) //指定当前组件的宽高比.padding({ top: 12 }).margin({ top: 16, left: 12 }).align(Alignment.TopStart).backgroundColor(this.currentIndex === index ? '#ccc' : '#FFF1F3F5').borderRadius(16).onClick(() => {this.currentIndex = index})})}.width('100%').justifyContent(FlexAlign.Center)Row() {Column() {Text('金额').width('100%').fontSize(20).fontColor(Color.Black)Column() {TextInput({placeholder: '请输入金额'}).padding({ left: 0, top: 0, bottom: 0 }).borderRadius(0).backgroundColor(Color.White).type(InputType.Number).onChange((value: string) => {this.money = Number(value)})}.height(48).padding({ top: 15, bottom: 11 }).borderWidth({ bottom: 1 }).borderColor('#33182431')Button("确定").onClick((event: ClickEvent) => {if (this.currentIndex === -1) {AlertDialog.show({ message: "请选择种类" })return}if (this.money === 0) {AlertDialog.show({ message: "请输入金额" })return}let insertData: AccountData = {id: 0,accountType: this.currentIndex,typeText: this.items[this.currentIndex].text,amount: this.money}this.confirm && this.confirm(insertData)this.controller.close()}).width('90%').margin(20)}.width('100%').padding({ left: 12, right: 12 })}.width('100%').margin(20)}}
}

效果