野外泛在线考核系统(十)
三、平板端
接着(九)捋一捋这几天遇到的问题。
(一)平板新建数据表
问题: 新建数据表,插入数据而后再显示出来?
这个问题,本来不是问题,因为有成熟的例子关系型数据库(ArkTS),有源代码,但是源代码过于复杂,是通过自定义弹窗输入数据,而后插入到数据表中的,我就模仿着自己弄了个数据表。然后进行插入,查询,更新,删除等操作。
- 首先要新建数据库的功能文件Rdb.ets。如下图:
其代码如下:
/*
* Copyright (c) 2023 Huawei Device Co., Ltd.
* Licensed under the Apache License,Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
import relationalStore from '@ohos.data.relationalStore';
import CommonConstants from '../constants/CommonConstants';
import Logger from '../utils/Logger';
import call from '@ohos.telephony.call';
export default class Rdb {
private rdbStore:relationalStore.RdbStore|null =null;
private tableName:string;
private sqlCreateTable:string;
private columns:Array<string>;
constructor(tableName:string,sqlCreateTable:string,columns:Array<string>) {
this.tableName = tableName;
this.sqlCreateTable = sqlCreateTable;
this.columns = columns;
}
getRdbStore(callback:Function=()=>{}){
if (!callback || typeof callback === 'undefined' || callback === undefined){
Logger.info(CommonConstants.RDB_TAG,'getRdbStore() has no callback!');
return;
}
if (this.rdbStore !== null){
Logger.info(CommonConstants.RDB_TAG,'The rdbStore exists.');
callback();
return
}
let context:Context = getContext(this) as Context;
relationalStore.getRdbStore(context,CommonConstants.STORE_CONFIG,(err,rdb)=>{
if(err){
Logger.error(CommonConstants.RDB_TAG,`getRdbStore() failed,err:${err}`);
return;
}
this.rdbStore = rdb;
this.rdbStore.executeSql(this.sqlCreateTable);
Logger.info(CommonConstants.RDB_TAG,'getRdbStore() finished.');
callback();
});
}
insertData(data: relationalStore.ValuesBucket, callback: Function = () => {
}) {
if (!callback || typeof callback === 'undefined' || callback === undefined) {
Logger.info(CommonConstants.RDB_TAG, 'insertData() has no callback!');
return;
}
let resFlag: boolean = false;
const valueBucket: relationalStore.ValuesBucket = data;
// Logger.info(CommonConstants.RDB_TAG, `insertData() insert--start: ${data[0]}`);
if (this.rdbStore) {
this.rdbStore.insert(this.tableName, valueBucket, (err, ret) => {
if (err) {
Logger.error(CommonConstants.RDB_TAG, `insertData() failed, err: ${err}`);
callback(resFlag);
return;
}
Logger.info(CommonConstants.RDB_TAG, `insertData() finished: ${ret}`);
callback(ret);
});
}
}
deleteData(predicates: relationalStore.RdbPredicates, callback: Function = () => {
}) {
if (!callback || typeof callback === 'undefined' || callback === undefined) {
Logger.info(CommonConstants.RDB_TAG, 'deleteData() has no callback!');
return;
}
let resFlag: boolean = false;
if (this.rdbStore) {
this.rdbStore.delete(predicates, (err, ret) => {
if (err) {
Logger.error(CommonConstants.RDB_TAG, `deleteData() failed, err: ${err}`);
callback(resFlag);
return;
}
Logger.info(CommonConstants.RDB_TAG, `deleteData() finished: ${ret}`);
callback(!resFlag);
});
}
}
updateData(predicates: relationalStore.RdbPredicates, data: relationalStore.ValuesBucket, callback: Function = () => {
}) {
if (!callback || typeof callback === 'undefined' || callback === undefined) {
Logger.info(CommonConstants.RDB_TAG, 'updateDate() has no callback!');
return;
}
let resFlag: boolean = false;
const valueBucket: relationalStore.ValuesBucket = data;
if (this.rdbStore) {
this.rdbStore.update(valueBucket, predicates, (err, ret) => {
if (err) {
Logger.error(CommonConstants.RDB_TAG, `updateData() failed, err: ${err}`);
callback(resFlag);
return;
}
Logger.info(CommonConstants.RDB_TAG, `updateData() finished: ${ret}`);
callback(!resFlag);
});
}
}
query(predicates: relationalStore.RdbPredicates, callback: Function = () => {
}) {
if (!callback || typeof callback === 'undefined' || callback === undefined) {
Logger.info(CommonConstants.RDB_TAG, 'query() has no callback!');
return;
}
if (this.rdbStore) {
this.rdbStore.query(predicates, this.columns, (err, resultSet) => {
if (err) {
Logger.error(CommonConstants.RDB_TAG, `query() failed, err: ${err}`);
return;
}
Logger.info(CommonConstants.RDB_TAG, 'query() finished.');
callback(resultSet);
resultSet.close();
});
}
}
}
- 接着先创建接口文件ConstantsInterface.ets,构建接口:CreateTable 用于创建数据表,如下图:
export interface CreateTable{
tableName:string;
sqlCreate:string;
columns:string[];
}
- 接着创建表,在此之前,先要新建一个文件ScoreData.ets,并在其中构建一个类ContentScoreData,如下图:
该类和即将创建的数据表对应,字段和字段类型必须一致。
export class ContentScoreData{
id:number = -1;
content:string = '';
item:string = '';
team:string = '';
teacher:string = '';
contentScore:number = 78;
contentDate:string = '2023-12-10';
}
- 接着要在CommonConstants.ets文件中,利用CreateTable 接口创建表的语句。如下图:
**注意:**sqlCreate 的结尾一定是 ) ,我就是因为少了这个 ) ,运行不会错,但是数据死活插入不进表,找了一天终于找到这个错误。
import relationalStore from '@ohos.data.relationalStore';
import {CreateTable} from '../../viewmodel/ConstantsInterface';
export class CommonConstants{
static readonly STORE_CONFIG:relationalStore.StoreConfig ={
name:"database.db",
securityLevel:relationalStore.SecurityLevel.S1
};
/*
数据表设置
*/
//ContentScore 表
static readonly CONTENTSCORE_TABLE:CreateTable = {
tableName:'contentScoreTable',
sqlCreate:'CREATE TABLE IF NOT EXISTS contentScoreTable(id INTEGER PRIMARY KEY,content TEXT,'+
'item TEXT, team TEXT, teacher TEXT, '+'contentScore INTEGER,'+'contentDate TEXT)',
columns:['id','content','item','team','teacher','contentScore','contentDate']
};
- 接着创建表文件ContentScoreTable.ets,用于对表进行操作。
import relationalStore from '@ohos.data.relationalStore';
import { ContentScoreData } from '../../bean/ScoreData';
import {CommonConstants} from '../../constants/CommonConstants';
import Rdb from '../Rdb';
export default class ContentScoreTable{
private ContentScoreTable = new Rdb(CommonConstants.CONTENTSCORE_TABLE.tableName,CommonConstants.CONTENTSCORE_TABLE.sqlCreate,
CommonConstants.CONTENTSCORE_TABLE.columns);
constructor(callback:Function=()=>{}) {
this.ContentScoreTable.getRdbStore(callback);
}
getRdbStore(callback:Function=()=>{}){
this.ContentScoreTable.getRdbStore(callback);
}
insertData(score:ContentScoreData,callback:Function){
const valueBucket: relationalStore.ValuesBucket = generateBucket(score);
this.ContentScoreTable.insertData(valueBucket,callback);
}
deleteData(score:ContentScoreData,callback:Function){
let predicates = new relationalStore.RdbPredicates(CommonConstants.CONTENTSCORE_TABLE.tableName);
predicates.equalTo('id',score.id);
this.ContentScoreTable.deleteData(predicates,callback);
}
updateData(score:ContentScoreData,callback:Function){
const valueBucket:relationalStore.ValuesBucket = generateBucket(score);
let predicates = new relationalStore.RdbPredicates(CommonConstants.CONTENTSCORE_TABLE.tableName);
predicates.equalTo('id',score.id);
this.ContentScoreTable.updateData(predicates,valueBucket,callback);
}
query(teacher:string,callback:Function,isALL:boolean =true){
// query(id:number,teacher:string,callback:Function,isALL:boolean =true){
let predicates = new relationalStore.RdbPredicates(CommonConstants.CONTENTSCORE_TABLE.tableName);
if(!isALL){
predicates.equalTo('teacher',teacher);
//predicates.equalTo('id',id).equalTo('teacher',teacher);
}
this.ContentScoreTable.query(predicates,(resultSet:relationalStore.ResultSet)=>{
let count:number = resultSet.rowCount;
console.log(`${CommonConstants.CONTENTSCORE_TABLE}`+'Query get results --'+count.toString());
if(count === 0 || typeof count === 'string'){
console.log(`${CommonConstants.CONTENTSCORE_TABLE}`+'Query no results!');
callback([]);
}else{
resultSet.goToFirstRow();
const result:ContentScoreData[] = [];
console.log(`${CommonConstants.CONTENTSCORE_TABLE}`+'Query get results --start!');
for (let i=0;i<count;i++){
let tmp:ContentScoreData = {
// id:0, content:'', item:'', team:'', teacher:'', contentScore:0, contentDate:null
id:0, content:'', item:'', team:'', teacher:'', contentScore:85, contentDate:'2023-12-01'
};
// let today = new Date();
tmp.id = resultSet.getDouble(resultSet.getColumnIndex('id'));
tmp.content = resultSet.getString(resultSet.getColumnIndex('content'));
tmp.item = resultSet.getString(resultSet.getColumnIndex('item'));
tmp.team = resultSet.getString(resultSet.getColumnIndex('team'));
tmp.teacher = resultSet.getString(resultSet.getColumnIndex('teacher'));
tmp.contentScore = resultSet.getDouble(resultSet.getColumnIndex('contentScore'));
tmp.contentDate = resultSet.getString(resultSet.getColumnIndex('contentDate'));
result[i] = tmp;
console.log(`${CommonConstants.CONTENTSCORE_TABLE}`+'Query get results --end!');
resultSet.goToNextRow();
}
callback(result);
}
});
}
}
function generateBucket(score:ContentScoreData):relationalStore.ValuesBucket{
let obj:relationalStore.ValuesBucket={};
obj.content = score.content;
obj.item = score.item;
obj.team = score.team;
obj.teacher = score.teacher;
obj.contentScore = score.contentScore;
obj.contentDate = score.contentDate;
return obj;
}
- 至此,算是完成了表的创建,具体要插入数据和显示数据看执行如下操作:
import promptAction from '@ohos.promptAction';
import {CommonConstants} from '../common/constants/CommonConstants';
import {ContentScoreData } from '../common/bean/ScoreData';
import ContentScoreTable from '../common/database/tables/ContentScoreTable';
import DataViewModel from '../viewmodel/DataViewModel';
import Logger from '../common/utils/Logger';
@Component
export default struct ManageData {
@State message: string = '插入数据的试验'
@State scores:Array<ContentScoreData>=[];
@State index: number = -1;
@State newScore1: ContentScoreData = {id:0, content:'', item:'', team:'', teacher:'', contentScore:0 , contentDate:'2023-01-01'};
private ContentScoreTable = new ContentScoreTable(()=>{});
private deleteList: Array<ContentScoreData> = [];
//从服务器获取数据相关设置
@State path:string = 'search_itemScore'
@State teacherID:string = 'qp001'
@State contentScores:Array<ContentScoreData> = []
changeCategory() {
DataViewModel.getDataList(this.path, this.teacherID).then((data: ContentScoreData[]) => {
this.contentScores = data;
}).catch((err: string | Resource) => {
promptAction.showToast({
message: err,
duration: CommonConstants.ANIMATION_DURATION
});
});
}
insertDataToTable0():void{
this.newScore1.id = 1;
this.newScore1.content = '准备';
this.newScore1.item = '坦克大战'
this.newScore1.team = '坦克大战1组';
this.newScore1.teacher = '王老师';
this.newScore1.contentScore = 98;
// this.newScore1.contentDate = '2023-12-16';
this.ContentScoreTable.insertData(this.newScore1, (id: number) => {
});
}
insertDataToTable1():void{
this.newScore1.id = 2;
this.newScore1.content = '准备';
this.newScore1.item = '坦克大战'
this.newScore1.team = '坦克大战1组';
this.newScore1.teacher = '张老师';
this.newScore1.contentScore = 98;
this.newScore1.contentDate = new Date().toLocaleDateString();
this.ContentScoreTable.insertData(this.newScore1, (id: number) => {
});
}
insertDataToTable2():void{
this.newScore1.id = 3;
this.newScore1.content = '进攻';
this.newScore1.item = '坦克大战'
this.newScore1.team = '坦克大战1组';
this.newScore1.teacher = '张老师';
this.newScore1.contentScore = 98;
this.newScore1.contentDate = new Date().toLocaleDateString()
this.ContentScoreTable.insertData(this.newScore1, (id: number) => {
});
}
insertDataToTable3():void{
this.newScore1.id = 4;
this.newScore1.content = '撤离';
this.newScore1.item = '坦克大战'
this.newScore1.team = '坦克大战1组';
this.newScore1.teacher = '张老师';
this.newScore1.contentScore = 98;
this.newScore1.contentDate = new Date().toDateString();
this.ContentScoreTable.insertData(this.newScore1, (id: number) => {
});
}
showData():void{
this.ContentScoreTable.getRdbStore(() => {
this.ContentScoreTable.query('0', (result: ContentScoreData[]) => {
this.scores = result;
}, true);
});
}
selectListItem(item: ContentScoreData) {
//this.isInsert = false;
this.index = this.scores.indexOf(item);
this.newScore1 = {
id: item.id,
content:item.content,
item:item.item,
team:item.team,
teacher:item.teacher,
contentScore:item.contentScore,
contentDate:item.contentDate
};
}
deleteListItem() {
for (let i = 0; i < this.deleteList.length; i++) {
let index = this.scores.indexOf(this.deleteList[i]);
this.scores.splice(index, 1);
this.ContentScoreTable.deleteData(this.deleteList[i], () => {
});
}
this.deleteList = [];
}
aboutToAppear() {
this.ContentScoreTable.getRdbStore(() => {
this.ContentScoreTable.query('0', (result: ContentScoreData[]) => {
this.scores = result;
}, true);
});
}
build() {
Stack(){
Column() {
Text(this.message)
.fontSize(50)
.fontWeight(FontWeight.Bold)
Row(){
Button('插入王数据')
.fontSize(30)
.fontWeight(FontWeight.Bold)
.onClick(() => {
this.insertDataToTable0()
})
Button('插入数据1')
.fontSize(30)
.fontWeight(FontWeight.Bold)
.onClick(() => {
this.insertDataToTable1()
})
Button('插入数据2')
.fontSize(30)
.fontWeight(FontWeight.Bold)
.onClick(() => {
this.insertDataToTable2()
})
}
Button('插入数据3')
.fontSize(30)
.fontWeight(FontWeight.Bold)
.onClick(() => {
this.insertDataToTable3()
})
Button('显示数据')
.fontSize(30)
.fontWeight(FontWeight.Bold)
.onClick(() => {
this.showData()
})
Text('----------------------------------------------')
Row(){
List({space:CommonConstants.FULL_SIZE}){
ForEach(this.scores,(item:ContentScoreData)=>{
ListItem(){
Row(){
Text(item.id.toString())
.fontSize(20)
.fontWeight(FontWeight.Bold)
.width('5%')
Text(item.content)
.fontSize(20)
.fontWeight(FontWeight.Bold)
.width('10%')
Text(item.item)
.fontSize(20)
.fontWeight(FontWeight.Bold)
.width('15%')
Text(item.team)
.fontSize(20)
.fontWeight(FontWeight.Bold)
.width('20%')
Text(item.teacher)
.fontSize(20)
.fontWeight(FontWeight.Bold)
.width('10%')
Text(item.contentScore.toString())
.fontSize(20)
.fontWeight(FontWeight.Bold)
.width('5%')
Text(item.contentDate)
.fontSize(20)
.fontWeight(FontWeight.Bold)
.width('35%')
Toggle({ type: ToggleType.Checkbox })
.onChange((isOn) => {
if (isOn) {
this.deleteList.push(item);
} else {
let index = this.deleteList.indexOf(item);
this.deleteList.splice(index, 1);
}
})
}.border({ width: 1 }).width('80%').alignItems(VerticalAlign.Bottom)
}
.onClick(() => {
this.selectListItem(item);
})
})
}
}.border({ width: 2 }).width('60%')
.alignItems(VerticalAlign.Bottom)
Text('----------------------------------------------')
}
.width('100%')
.alignItems(HorizontalAlign.Center)
Button() {
Image($rawfile('delete.png'))
}
.width($r('app.float.component_size_MP'))
.height($r('app.float.component_size_MP'))
.backgroundColor($r('app.color.background_color'))
.markAnchor({ x: $r('app.float.mark_anchor'), y: CommonConstants.MINIMUM_SIZE })
.position({ x: CommonConstants.DELETE_POSITION_X, y: CommonConstants.DELETE_POSITION_Y })
.onClick(() => {
this.deleteListItem();
})
}
}
}
至此,基本搞懂关系数据库创建表,插入数据显示数据了,下一步研究显示单条数据。