// BoxDBManager.ets
import Box from '../bean/Box';
import relationalStore from '@ohos.data.relationalStore';
import common from '@ohos.app.ability.common';
import emitter from '@ohos.events.emitter';
import { Record } from '../bean/Record';
import { BusinessError } from '@ohos.base';
import { Logger } from './Logger';
import { uartUtils } from './UartUtils';
import { Member } from '../bean/Member';
// 定义锁信息接口
interface LockInfo {
lockBoard: number;
lockNumber: number;
}
interface SizeLockMap {
size: string;
start: number;
}
interface SizeLockMigrationMap {
sizeChar: string;
fullSize: string;
start: number;
}
interface MemberInfo {
id: number;
name: string;
phone: string;
password: string;
}
const TAG = "UartUtils";
class BoxDBManager {
private static instance: BoxDBManager;
private rdbStore: relationalStore.RdbStore | null = null;
private readonly DB_NAME = 'BoxDB.db';
private readonly TABLE_NAME = 'BOX';
public readonly DATA_CHANGED_EVENT_ID = 1001;
private readonly DB_CONFIG: relationalStore.StoreConfig = {
name: this.DB_NAME,
securityLevel: relationalStore.SecurityLevel.S1
};
private readonly CREATE_TABLE_SQL = `
CREATE TABLE IF NOT EXISTS ${this.TABLE_NAME} (
ID INTEGER PRIMARY KEY AUTOINCREMENT,
PHONE TEXT DEFAULT '',
PASSWORD TEXT DEFAULT '',
BOX_ID TEXT UNIQUE,
SIZE TEXT,
STATUS INTEGER DEFAULT 0,
ENABLED INTEGER DEFAULT 1,
LOCK_BOARD INTEGER DEFAULT 1,
LOCK_NUMBER INTEGER DEFAULT 1
)`;
private readonly CREATE_LOG_TABLE_SQL = `
CREATE TABLE IF NOT EXISTS BOX_OPERATION_LOG (
ID INTEGER PRIMARY KEY AUTOINCREMENT,
BOX_ID INTEGER DEFAULT 0,
OPERATION_TIME TEXT NOT NULL,
OPERATOR TEXT NOT NULL,
ACTION_TYPE TEXT NOT NULL,
ACCESS_METHOD TEXT NOT NULL
)`;
private readonly CREATE_MEMBER_TABLE_SQL = `
CREATE TABLE IF NOT EXISTS MEMBER (
ID INTEGER PRIMARY KEY AUTOINCREMENT,
MEMBER_ID TEXT NOT NULL,
NAME TEXT NOT NULL,
PASSWORD TEXT NOT NULL
)`;
private constructor() {}
public static getInstance(): BoxDBManager {
if (!BoxDBManager.instance) {
BoxDBManager.instance = new BoxDBManager();
}
return BoxDBManager.instance;
}
// 初始化数据库（使用Promise）
async initDatabase(context: common.Context): Promise<boolean> {
try {
this.rdbStore = await relationalStore.getRdbStore(context, this.DB_CONFIG);
await this.rdbStore.executeSql(this.CREATE_TABLE_SQL);
await this.rdbStore.executeSql(this.CREATE_LOG_TABLE_SQL);
await this.rdbStore.executeSql(this.CREATE_MEMBER_TABLE_SQL);
await this.initDefaultBoxes();
console.info('Database initialized successfully');
return true;
} catch (err) {
console.error(`Database init failed: ${JSON.stringify(err)}`);
return false;
}
}
// 新增：添加会员（使用 Member 类）
async addMember(member: Member): Promise<boolean> {
if (!this.rdbStore){return false}
try {
const valueBucket: relationalStore.ValuesBucket = {
'MEMBER_ID': member.id, // 会员编号（字符串）
'NAME': member.name, // 姓名
'PASSWORD': member.password // 密码（已拼接好的完整密码）
};
// 插入会员表
await this.rdbStore.insert('MEMBER', valueBucket);
console.info(`会员 ${member.name} 添加成功`);
return true;
} catch (err) {
console.error(`添加会员失败: ${JSON.stringify(err)}`);
return false;
}
}
// 验证会员（通过密码）
async verifyMember(password: string): Promise<boolean> {
if (!this.rdbStore) return false;
try {
// 使用RegExp构造函数替代正则表达式字面量
const passwordRegex = new RegExp('^\\d{8}$');
if (!passwordRegex.test(password)) {
console.error("密码格式错误，必须是8位数字");
return false;
}
const predicates = new relationalStore.RdbPredicates('MEMBER');
predicates.equalTo('PASSWORD', password);
const columns = ['ID'];
const resultSet = await this.rdbStore.query(predicates, columns);
const isMember = resultSet.rowCount > 0;
resultSet.close();
return isMember;
} catch (err) {
console.error(`验证会员失败: ${JSON.stringify(err)}`);
return false;
}
}
// 删除会员
async deleteMember(memberId: string): Promise<boolean> {
if (!this.rdbStore) return false;
try {
const predicates = new relationalStore.RdbPredicates('MEMBER');
predicates.equalTo('MEMBER_ID', memberId);
const rowsDeleted = await this.rdbStore.delete(predicates);
console.info(`删除会员成功，ID: ${memberId}，删除行数: ${rowsDeleted}`);
this.notifyDataChanged(); // 通知数据变化
return rowsDeleted > 0;
} catch (err) {
console.error(`删除会员失败: ${JSON.stringify(err)}`);
return false;
}
}
// 获取所有会员（用于后台管理页面展示）
async getAllMembers(): Promise<Member[]> {
if (!this.rdbStore) return [];
try {
const predicates = new relationalStore.RdbPredicates('MEMBER');
const columns = ['MEMBER_ID', 'NAME', 'PASSWORD'];
const resultSet = await this.rdbStore.query(predicates, columns);
const members: Member[] = [];
while (resultSet.goToNextRow()) {
members.push(new Member(
resultSet.getLong(resultSet.getColumnIndex('MEMBER_ID')).toString(),
resultSet.getString(resultSet.getColumnIndex('NAME')),
resultSet.getString(resultSet.getColumnIndex('PASSWORD')) // 返回存储的密码
));
}
resultSet.close();
return members;
} catch (err) {
console.error(`查询会员列表失败: ${JSON.stringify(err)}`);
return [];
}
}
// 在BoxDBManager类中添加以下方法
// 更新会员信息（支持ID修改）
async updateMember(oldId: string, newMember: Member): Promise<boolean> {
if (!this.rdbStore) return false;
try {
// 检查新ID是否已存在（如果修改了ID）
if (oldId !== newMember.id) {
// 直接查询新ID是否已存在
const checkPredicates = new relationalStore.RdbPredicates('MEMBER');
checkPredicates.equalTo('MEMBER_ID', newMember.id);
const resultSet = await this.rdbStore.query(checkPredicates, ['MEMBER_ID']);
const exists = resultSet.rowCount > 0;
resultSet.close();
if (exists) {
console.error(`更新失败：会员编号 ${newMember.id} 已存在`);
return false;
}
}
// 构建更新条件
const predicates = new relationalStore.RdbPredicates('MEMBER');
predicates.equalTo('MEMBER_ID', oldId);
// 构建更新内容
const valueBucket: relationalStore.ValuesBucket = {
'MEMBER_ID': newMember.id,
'NAME': newMember.name,
'PASSWORD': newMember.password
};
// 执行更新
const rowsUpdated = await this.rdbStore.update(valueBucket, predicates);
if (rowsUpdated === 0) {
console.error(`未找到会员编号 ${oldId} 的记录`);
return false;
}
console.info(`更新会员成功，从ID: ${oldId} 更新为 ${newMember.id}`);
this.notifyDataChanged(); // 通知数据变化
return true;
} catch (err) {
console.error(`更新会员失败: ${JSON.stringify(err)}`);
return false;
}
}
// 初始化默认箱子
private async initDefaultBoxes(): Promise<void> {
const count = await this.getBoxCount();
if (count > 0) return;
const sizeLockMapping:SizeLockMap[] = [
{ size: "特大", start: 1 },
{ size: "大", start: 4 },
{ size: "中", start: 7 },
{ size: "小", start: 10 }
];
const boxes: relationalStore.ValuesBucket[] = [];
for (const mapping of sizeLockMapping) {
for (let i = 1; i <= 3; i++) {
boxes.push({
'BOX_ID': `${mapping.size.charAt(0)}${i}`,
'SIZE': mapping.size,
'STATUS': 0,
'ENABLED': 1,
'LOCK_BOARD': 1,
'LOCK_NUMBER': mapping.start + (i - 1) // 计算正确的锁号
});
}
}
await this.rdbStore?.batchInsert(this.TABLE_NAME, boxes);
console.info(`${boxes.length} default boxes initialized`);
}
async migrateLockNumbers(): Promise<void> {
if (!this.rdbStore) return;
try {
// 获取所有箱子
const allBoxes = await this.getAllBoxes();
// 使用类型安全的映射定义
const sizeLockMapping:SizeLockMigrationMap[] = [
{ sizeChar: "特", fullSize: "特大", start: 1 },
{ sizeChar: "大", fullSize: "大", start: 4 },
{ sizeChar: "中", fullSize: "中", start: 7 },
{ sizeChar: "小", fullSize: "小", start: 10 }
];
for (const box of allBoxes) {
// 提取尺寸和序号
const regex = new RegExp("^(\\D+)(\\d+)$");
const match = regex.exec(box.boxId);
if (!match) continue;
const sizeChar = match[1];
const index = parseInt(match[2]);
// 查找匹配的尺寸映射
const mapping = sizeLockMapping.find(m => m.sizeChar === sizeChar);
if (!mapping) continue;
// 计算新锁号
const newLockNumber = mapping.start + (index - 1);
// 更新数据库
const predicates = new relationalStore.RdbPredicates(this.TABLE_NAME);
predicates.equalTo('ID', box.id);
const valueBucket: relationalStore.ValuesBucket = {
'LOCK_NUMBER': newLockNumber
};
await this.rdbStore.update(valueBucket, predicates);
}
console.info("Lock numbers migrated successfully");
} catch (err) {
console.error("Lock number migration failed:", err);
}
}
// 获取箱子数量
private async getBoxCount(): Promise<number> {
if (!this.rdbStore) return 0;
const predicates = new relationalStore.RdbPredicates(this.TABLE_NAME);
const result = await this.rdbStore.query(predicates, ['count(*) as count']);
if (result.rowCount > 0 && result.goToFirstRow()) {
return result.getLong(result.getColumnIndex('count'));
}
return 0;
}
// 获取所有箱子
async getAllBoxes(): Promise<Box[]> {
if (!this.rdbStore) return [];
try {
const predicates = new relationalStore.RdbPredicates(this.TABLE_NAME);
const columns = ['ID', 'PHONE', 'PASSWORD', 'BOX_ID', 'SIZE', 'STATUS', 'ENABLED', 'LOCK_BOARD', 'LOCK_NUMBER'];
// 直接使用await获取结果集
const resultSet = await this.rdbStore.query(predicates, columns);
const boxes: Box[] = [];
if (resultSet.rowCount > 0) {
while (resultSet.goToNextRow()) {
const box = new Box(
resultSet.getString(resultSet.getColumnIndex('BOX_ID')),
resultSet.getString(resultSet.getColumnIndex('SIZE')),
resultSet.getLong(resultSet.getColumnIndex('STATUS')),
resultSet.getLong(resultSet.getColumnIndex('ENABLED')) === 1,
resultSet.getLong(resultSet.getColumnIndex('LOCK_BOARD')),
resultSet.getLong(resultSet.getColumnIndex('LOCK_NUMBER'))
);
box.id = resultSet.getLong(resultSet.getColumnIndex('ID'));
box.phone = resultSet.getString(resultSet.getColumnIndex('PHONE'));
box.password = resultSet.getString(resultSet.getColumnIndex('PASSWORD'));
boxes.push(box);
}
}
resultSet.close();
return boxes;
} catch (err) {
console.error(`Query failed: ${JSON.stringify(err)}`);
return [];
}
}
// 更新箱子状态
async updateBoxStatus(id: number, status: number): Promise<boolean> {
if (!this.rdbStore) return false;
const predicates = new relationalStore.RdbPredicates(this.TABLE_NAME);
predicates.equalTo('ID', id);
const valueBucket: relationalStore.ValuesBucket = { 'STATUS': status };
await this.rdbStore.update(valueBucket, predicates);
this.notifyDataChanged();
return true;
}
// 占用箱子
async occupyBox(boxId: string, phone: string, password: string): Promise<boolean> {
if (!this.rdbStore) return false;
const predicates = new relationalStore.RdbPredicates(this.TABLE_NAME);
predicates.equalTo('BOX_ID', boxId);
predicates.equalTo('STATUS', 0);
const valueBucket: relationalStore.ValuesBucket = {
'PHONE': phone,
'PASSWORD': password,
'STATUS': 1
};
await this.rdbStore.update(valueBucket, predicates);
this.notifyDataChanged();
return true;
}
// 释放箱子
async releaseBox(phoneSuffix: string, password: string): Promise<string[]> {
if (!this.rdbStore) return [];
// 查询匹配的箱子
const predicates = new relationalStore.RdbPredicates(this.TABLE_NAME);
predicates.endsWith('PHONE', phoneSuffix);
predicates.equalTo('PASSWORD', password);
predicates.equalTo('STATUS', 1);
predicates.equalTo('ENABLED', 1);
const result = await this.rdbStore.query(predicates, ['ID', 'BOX_ID']);
const boxIds: string[] = [];
while (result.goToNextRow()) {
boxIds.push(result.getString(result.getColumnIndex('BOX_ID')));
}
result.close();
if (boxIds.length === 0) return [];
// 更新匹配的箱子
const updatePredicates = new relationalStore.RdbPredicates(this.TABLE_NAME);
updatePredicates.endsWith('PHONE', phoneSuffix);
updatePredicates.equalTo('PASSWORD', password);
updatePredicates.equalTo('STATUS', 1);
updatePredicates.equalTo('ENABLED', 1);
const valueBucket: relationalStore.ValuesBucket = {
'PHONE': '',
'PASSWORD': '',
'STATUS': 0
};
await this.rdbStore.update(valueBucket, updatePredicates);
this.notifyDataChanged();
return boxIds;
}
// 更新箱子信息
public async updateBoxInfo(
id: number,
boxId: string,
size: string,
status: number, // 新增状态参数
enabled: boolean,
lockBoard: number,
lockNumber: number
): Promise<boolean> {
if (!this.rdbStore) return false;
try {
const predicates = new relationalStore.RdbPredicates(this.TABLE_NAME);
predicates.equalTo('ID', id);
const valueBucket: relationalStore.ValuesBucket = {
'BOX_ID': boxId,
'SIZE': size,
'STATUS': status, // 保存状态值
'ENABLED': enabled ? 1 : 0,
'LOCK_BOARD': lockBoard,
'LOCK_NUMBER': lockNumber
};
const changedRows = await this.rdbStore.update(valueBucket, predicates);
console.info(`Updated ${changedRows} rows for box ID: ${id}, new status: ${status}`);
this.notifyDataChanged();
return changedRows > 0;
} catch (err) {
console.error(`Update box info failed: ${JSON.stringify(err)}`);
return false;
}
}
// 删除箱子
public async deleteBox(id: number): Promise<number> {
if (!this.rdbStore) return 0;
try {
const predicates = new relationalStore.RdbPredicates(this.TABLE_NAME);
predicates.equalTo('ID', id);
const rows = await this.rdbStore.delete(predicates);
this.notifyDataChanged();
return rows;
} catch (err) {
console.error(`Delete box failed: ${JSON.stringify(err)}`);
return 0;
}
}
// 添加新箱子
public async addBox(box: Box): Promise<number> {
if (!this.rdbStore) return -1;
try {
const valueBucket: relationalStore.ValuesBucket = {
'BOX_ID': box.boxId,
'SIZE': box.size,
'ENABLED': box.enabled ? 1 : 0,
'LOCK_BOARD': box.lockBoard,
'LOCK_NUMBER': box.lockNumber
};
const id = await this.rdbStore.insert(this.TABLE_NAME, valueBucket);
this.notifyDataChanged();
return id;
} catch (err) {
console.error(`Add box failed: ${JSON.stringify(err)}`);
return -1;
}
}
// 清空箱子的手机号和密码
public async clearBoxCredentials(id: number): Promise<boolean> {
if (!this.rdbStore) return false;
try {
const predicates = new relationalStore.RdbPredicates(this.TABLE_NAME);
predicates.equalTo('ID', id);
const valueBucket: relationalStore.ValuesBucket = {
'PHONE': '',
'PASSWORD': ''
};
await this.rdbStore.update(valueBucket, predicates);
return true;
} catch (err) {
console.error(`Clear credentials failed: ${JSON.stringify(err)}`);
return false;
}
}
// 通知数据变化
private notifyDataChanged() {
try {
console.info("Emitting DATA_CHANGED event");
emitter.emit({
eventId: this.DATA_CHANGED_EVENT_ID
}, {
data: { action: "DATA_CHANGED" }
});
} catch (err) {
console.error(`Emit event failed: ${err.message}`);
}
}
addOperationLog(record:Record, callback: (success: boolean) => void) {
const rdbStore = this.rdbStore;
if (!rdbStore) {
console.error('RdbStore is not initialized');
callback(false);
return;
}
const valueBucket: relationalStore.ValuesBucket = {
'BOX_ID': record.Box_ID,
'OPERATION_TIME': record.Operation_Time, // 当前时间戳
'OPERATOR': record.Operator,
'ACTION_TYPE': record.Action_Type,
'ACCESS_METHOD': record.Access_Method
};
rdbStore.insert('BOX_OPERATION_LOG', valueBucket, (err: BusinessError) => {
if (err) {
console.error(`Insert log failed. Code:${err.code}, message:${err.message}`);
callback(false);
return;
}
callback(true);
});
}
getLogsByTime(time: string, callback: (logs: Record[]) => void) {
const rdbStore = this.rdbStore;
if (!rdbStore) {
console.error('RdbStore is not initialized');
callback([]);
return;
}
const predicates = new relationalStore.RdbPredicates('BOX_OPERATION_LOG');
// 只查询Record类需要的字段
const columns = ['BOX_ID', 'OPERATION_TIME', 'OPERATOR', 'ACTION_TYPE', 'ACCESS_METHOD'];
// 构建时间查询条件
if (time.length === 10) { // YYYY-MM-DD 格式
predicates.greaterThanOrEqualTo('OPERATION_TIME', `${time}T00:00:00Z`);
predicates.lessThan('OPERATION_TIME', `${time}T23:59:59.999Z`);
} else if (time.length === 7) { // YYYY-MM 格式
predicates.like('OPERATION_TIME', `${time}-%`);
} else {
console.error('Invalid time format. Use YYYY-MM or YYYY-MM-DD');
callback([]);
return;
}
rdbStore.query(predicates, columns, (err: BusinessError, resultSet: relationalStore.ResultSet) => {
if (err) {
console.error(`Query failed. Code:${err.code}, message:${err.message}`);
callback([]);
return;
}
const logs: Record[] = [];
if (resultSet.rowCount > 0) {
while (resultSet.goToNextRow()) {
logs.push(new Record(
resultSet.getLong(resultSet.getColumnIndex('BOX_ID')),
resultSet.getString(resultSet.getColumnIndex('OPERATION_TIME')),
resultSet.getString(resultSet.getColumnIndex('OPERATOR')),
resultSet.getString(resultSet.getColumnIndex('ACTION_TYPE')),
resultSet.getString(resultSet.getColumnIndex('ACCESS_METHOD'))
));
}
}
resultSet.close();
callback(logs);
});
}
// 新增方法：根据箱子ID获取锁板编号和锁编号
public async getLockInfoById(id: number): Promise<LockInfo | null> {
if (!this.rdbStore) return null;
try {
const predicates = new relationalStore.RdbPredicates(this.TABLE_NAME);
predicates.equalTo('ID', id);
const columns = ['LOCK_BOARD', 'LOCK_NUMBER'];
const resultSet = await this.rdbStore.query(predicates, columns);
if (resultSet.rowCount > 0 && resultSet.goToFirstRow()) {
return {
lockBoard: resultSet.getLong(resultSet.getColumnIndex('LOCK_BOARD')),
lockNumber: resultSet.getLong(resultSet.getColumnIndex('LOCK_NUMBER'))
};
}
return null;
} catch (err) {
console.error(`Get lock info failed: ${JSON.stringify(err)}`);
return null;
}
}
// 新增方法：根据箱子ID直接开锁
public async openLockById(id: number): Promise<boolean> {
try {
// 获取锁信息
const lockInfo = await this.getLockInfoById(id);
if (!lockInfo) {
Logger.e(TAG, `No lock info found for box ID: ${id}`);
return false;
}
// 检查锁编号是否在有效范围内 (1-12)
if (lockInfo.lockNumber < 1 || lockInfo.lockNumber > 12) {
Logger.e(TAG, `Invalid lock number ${lockInfo.lockNumber} for box ID: ${id}`);
return false;
}
// 开锁
uartUtils.openSpecificLock(lockInfo.lockNumber);
return true;
} catch (error) {
Logger.e(TAG, `Failed to open lock for box ID ${id}: ${error.message}`);
return false;
}
}
}
export default BoxDBManager.getInstance();
根据box类将上面的代码修改一下 deveco studio4.1版本API11// Box.ets
export default class Box {
id: number = 0;
phone: string = '';
password: string = '';
boxId: string = '';
size: string = '';
status: number = 0; // 0: 空闲, 1: 占用, 2: 打开
enabled: boolean = true; // 新增：能用状态
lockBoard: number = 1; // 新增：锁板
lockNumber: number = 1; // 新增：锁号
constructor(
boxId: string,
size: string,
status: number,
enabled: boolean = true,
lockBoard: number = 1,
lockNumber: number = 1
) {
this.boxId = boxId;
this.size = size;
this.status = status;
this.enabled = enabled;
this.lockBoard = lockBoard;
this.lockNumber = lockNumber;
}
}
最新发布
本文介绍了MySQL数据库通过读写分离来提升系统性能的方法。详细解释了读写分离的基本原理及其实现方式，并分享了一些实际应用的经验。
扫一扫
3187
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
