HistoryInfoManager：
import { HistoryInfo } from './HistoryInfo';
import {HistoryInfoDataHelper} from './HistoryInfoDataHelper';
import { IHistoryCacheManager } from './IHistoryCacheManager';
const TAG = 'rdbtest :HistoryInfoManager '
export class HistoryInfoManager implements IHistoryCacheManager {
helper: HistoryInfoDataHelper = HistoryInfoDataHelper.getInstance()
private static instance: HistoryInfoManager
public static getInstance(): HistoryInfoManager {
console.log(TAG, 'instance', HistoryInfoManager.instance)
if (!HistoryInfoManager.instance) {
HistoryInfoManager.instance = new HistoryInfoManager();
console.log(TAG, 'instance init success', HistoryInfoManager.instance)
}
return HistoryInfoManager.instance
}
constructor() {
this.helper.initDatabase()
}
//timingWrite参数暂不处理
async uploadHistory(info: HistoryInfo, timingWrite: boolean): Promise<boolean> {
return this.helper.saveHistoryData(info)
}
//考虑读取搜索历史记录是否需要异步
async getHistorys(): Promise<HistoryInfo[]> {
return this.helper.queryHistory()
}
async getHistoryInfoByqipuId(qipuId: number): Promise<HistoryInfo> {
return this.helper.queryHistoryByQipuId(qipuId)
}
async getHistoryInfoByTvId(tvId: string): Promise<HistoryInfo[]> {
return this.helper.queryHistory()
}
async deleteAll(): Promise<boolean> {
return this.helper.deleteHistory('-1')
}
}
HistoryInfoDataHelper：
import relationalStore from '@ohos.data.relationalStore'
import { BusinessError } from '@kit.BasicServicesKit'
import { EPGData, PayMarkType, RecordHistoryData } from '@kiwi/tvapi'
import { HistoryInfo } from './HistoryInfo'
// 数据库配置
const DB_NAME = 'HisToryInfoDB.db'
const DB_VERSION = 1
const TABLE_USER = 'HisToryInfo'
let store: relationalStore.RdbStore | undefined = undefined
const TAG = 'rdbtest'
// 数据库管理类
export class HistoryInfoDataHelper {
private static instance: HistoryInfoDataHelper
public static getInstance(): HistoryInfoDataHelper {
if (!HistoryInfoDataHelper.instance) {
HistoryInfoDataHelper.instance = new HistoryInfoDataHelper();
}
return HistoryInfoDataHelper.instance
}
constructor() {
}
private isInitializing: boolean = false
// 初始化数据库
async initDatabase(): Promise<boolean> {
console.log(TAG, 'initDatabase')
// 第一层校验：已初始化直接返回
if (store !== undefined) {
return true
}
// 防止并发场景下的重复初始化
if (this.isInitializing) {
return true
}
const HISTORY_CONFIG: relationalStore.StoreConfig = {
name: DB_NAME, // 数据库名称
securityLevel: relationalStore.SecurityLevel.S1 // 安全级别
}
const uiContext: UIContext | undefined = AppStorage.get('uiContext')
await relationalStore.getRdbStore(uiContext?.getHostContext(), HISTORY_CONFIG,
async (err: BusinessError, rdbStore: relationalStore.RdbStore) => {
if (err) {
console.error(TAG + `Failed to get RdbStore. Code:${err.code}, message:${err.message}`)
}
store = rdbStore
if (store !== undefined) {
// 定义数据表结构
const SQL_CREATE_TABLE_HISTORYINFO =
'CREATE TABLE IF NOT EXISTS ' + TABLE_USER + ' ( ' +
'qipuId INTEGER PRIMARY KEY , ' +
'mEpgData TEXT, ' +
'mAddTime INTEGER NOT NULL DEFAULT 0, ' +
'mUploadTime INTEGER NOT NULL DEFAULT 0, ' +
'mMaxPlayTime INTEGER NOT NULL DEFAULT 0, ' +
'mCookie TEXT, ' +
'mIsCommitted INTEGER NOT NULL DEFAULT 0, ' +
'mIsStopPlay INTEGER NOT NULL DEFAULT 0, ' +
'mPlayTime INTEGER NOT NULL DEFAULT -1, ' +
'mVid TEXT, ' +
'mHistoryData TEXT, ' +
'mDefVideoTime TEXT, ' +
'mPayMarkType TEXT NOT NULL DEFAULT "", ' +
'mIndiviDemand INTEGER NOT NULL DEFAULT 0, ' +
'mIsSportsHistory INTEGER NOT NULL DEFAULT 0, ' +
'mTm INTEGER, ' +
'mVe TEXT, ' +
'ckuid TEXT, ' +
'terminalId TEXT, ' +
'agentType TEXT, ' +
'tail INTEGER ' +
')'
await (store as relationalStore.RdbStore).executeSql(SQL_CREATE_TABLE_HISTORYINFO).then(() => {
console.info(TAG, `Create ${TABLE_USER} table done.`);
}).catch((err: BusinessError) => {
console.error(TAG, `Create ${TABLE_USER} failed, code is ${err.code},message is ${err.message}`);
});
(store as relationalStore.RdbStore).version = DB_VERSION
this.isInitializing = true
}
console.info(TAG + 'Succeeded in getting RdbStore.')
})
return true
}
/**
* 单个历史信息插入
* 视频插入/更新信息都会调此接口
* 先查询是否存在数据，存在则更新，不存在则插入
* @param history
*/
async saveHistoryData(history: HistoryInfo): Promise<boolean> {
// 1. 数据库初始化检查
if (!store) {
console.error(TAG, 'Database not initialized');
return false;
}
console.log(TAG, 'saveHistoryData start')
try {
const qipuId = history.mEpgData.qipuId ?? 0;
let queryRet: number = 0
// 2. 创建谓词（重用对象）
const predicates = new relationalStore.RdbPredicates(TABLE_USER);
predicates.equalTo('qipuId', qipuId);
await (store as relationalStore.RdbStore).querySql(`select qipuid from ${TABLE_USER} where qipuid=${qipuId}`)
.then((resultSet: relationalStore.ResultSet) => {
console.info(TAG + ` ResultSet column rowCount: ${resultSet.rowCount}`);
queryRet = resultSet.rowCount
resultSet.close()
})
if (queryRet > 0) {
await store.update(
history.toValuesBucket(),
predicates,
relationalStore.ConflictResolution.ON_CONFLICT_REPLACE
);
} else {
await store.insert(TABLE_USER, history.toValuesBucket());
}
return true
} catch (error) {
// 7. 全局错误处理
const mappedError = this.mapErrorCode(error.code);
console.error(TAG, `Operation failed: ${error.code} - ${error.message}`);
return false
}
}
/**
* 根据历史记录的主键ID更新
* @param history
*/
async updateHistory(history: HistoryInfo): Promise<number> {
if (!store) {
console.error('Database not initialized');
return 0
}
const predicates = new relationalStore.RdbPredicates(TABLE_USER);
predicates.equalTo('qipuId', history.qipuId);
(store as relationalStore.RdbStore).update(history.toValuesBucket(), predicates,
relationalStore.ConflictResolution.ON_CONFLICT_REPLACE).then((rows: Number) => {
console.info(`Updated row count: ${rows}`);
return rows
}).catch((err: BusinessError) => {
console.error(`Update failed: code is ${err.code},message is ${err.message}`);
console.error(this.mapErrorCode(err.code))
return 0
});
return 0
}
/**
* 根据历史记录的主键ID删除
* @param history
*/
async deleteHistory(id: string): Promise<boolean> {
if (!store) {
console.error('Database not initialized');
return false
}
const predicates = new relationalStore.RdbPredicates(TABLE_USER);
Number(id) === -1 ? "" : predicates.equalTo('id', id);
await (store as relationalStore.RdbStore).delete(predicates).then(async (rows: Number) => {
console.info(`Updated row count: ${rows}`);
}).catch((err: BusinessError) => {
console.error(`Update failed: code is ${err.code},message is ${err.message}`);
console.error(this.mapErrorCode(err.code));
return false
});
return true
}
/**
* 查询所有历史记录，按照addTime逆序排序
* @param count 查询的条数
* @returns
*/
async queryHistory(count?: number): Promise<HistoryInfo[]> {
if (!store) {
console.log(TAG, 'Waiting for database initialization...');
return []
}
let select = `SELECT * FROM ${TABLE_USER} ORDER BY mAddTime DESC`
if (count) {
select += ` LIMIT ${count}`;
}
console.log(TAG, 'sql: ', select);
let historyList: HistoryInfo[] = []
await (store as relationalStore.RdbStore).querySql(select)
.then(async (resultSet: relationalStore.ResultSet) => {
console.info(`ResultSet column names: ${resultSet.columnNames}, column count: ${resultSet.columnCount}`);
historyList = await this.processTypedResultSet(resultSet)
console.log(TAG, 'historyList.length: ', historyList.length);
})
.catch((err: BusinessError) => {
console.error(`Query failed, code is ${err.code},message is ${err.message}`);
console.error(this.mapErrorCode(err.code))
});
return historyList
}
/**
* 根据qipuId查询数据，qipuId为主键，唯一一条，直接返回historyinfo，若未查询到返回new HistoryInfo,qipuId为空
* @param qipuId
* @returns
*/
async queryHistoryByQipuId(qipuId: number): Promise<HistoryInfo> {
console.log(TAG, 'qipuId :', qipuId)
if (!store) {
console.error(TAG + 'Database not initialized');
return new HistoryInfo()
// throw new Error('123123123123123')
}
console.log(TAG, 'can query-------------------- :', qipuId)
if (typeof qipuId !== 'number') {
return new HistoryInfo()
}
let select = `SELECT * FROM ${TABLE_USER} where qipuId=${qipuId} `;
console.log(TAG, 'sql: ', select);
let historyList: HistoryInfo[] = [];
(store as relationalStore.RdbStore).querySql(select)
.then((resultSet: relationalStore.ResultSet) => {
console.info(`ResultSet column names: ${resultSet.columnNames}, column count: ${resultSet.columnCount}`);
historyList = this.processTypedResultSet(resultSet)
console.log(TAG, 'historyList.length: ', historyList.length);
})
.catch((err: BusinessError) => {
console.error(TAG, `Query failed, code is ${err.code},message is ${err.message}`);
console.error(this.mapErrorCode(err.code));
});
return historyList.length > 0 ? historyList[0] : new HistoryInfo()
}
// 错误代码映射
private mapErrorCode(code: number): string {
switch (code) {
case 14800001:
return (TAG + ' 数据库未初始化');
case 14800005:
return (TAG + ' 记录不存在');
case 14800011:
return (TAG + ' 并发冲突');
default:
return (TAG + ` 数据库错误 (${code})`);
}
}
private processTypedResultSet(resultSet: relationalStore.ResultSet): HistoryInfo[] {
const items: HistoryInfo[] = [];
console.log(TAG, 'result count:', resultSet.rowCount)
// 遍历结果集
while (resultSet.goToNextRow()) {
console.log(TAG, ' qipiId:', resultSet.getLong(resultSet.getColumnIndex("qipuId")))
const history: HistoryInfo = new HistoryInfo()
history.qipuId = resultSet.getLong(resultSet.getColumnIndex("qipuId"));
try {
history.mEpgData = JSON.parse(resultSet.getString(resultSet.getColumnIndex("mEpgData"))) as EPGData;
history.mHistoryData =
JSON.parse(resultSet.getString(resultSet.getColumnIndex("mHistoryData"))) as RecordHistoryData;
} catch (err) {
console.error(TAG, "JSON解析失败(mHistoryData):", err.message);
}
history.mPayMarkType = resultSet.getString(resultSet.getColumnIndex("mPayMarkType")) as PayMarkType;
history.mAddTime = resultSet.getLong(resultSet.getColumnIndex("mAddTime"));
history.mUploadTime = resultSet.getLong(resultSet.getColumnIndex("mUploadTime"));
history.mMaxPlayTime = resultSet.getLong(resultSet.getColumnIndex("mMaxPlayTime"));
history.mCookie = resultSet.getString(resultSet.getColumnIndex("mCookie"));
history.mIsCommitted = resultSet.getLong(resultSet.getColumnIndex("mIsCommitted"));
history.mIsStopPlay = resultSet.getLong(resultSet.getColumnIndex("mIsStopPlay")) !== 0
history.mPlayTime = resultSet.getLong(resultSet.getColumnIndex("mPlayTime"))
history.mVid = resultSet.getString(resultSet.getColumnIndex("mVid"));
history.mDefVideoTime = resultSet.getString(resultSet.getColumnIndex("mDefVideoTime"));
history.mIndiviDemand = resultSet.getLong(resultSet.getColumnIndex("mIndiviDemand"))
history.mIsSportsHistory = resultSet.getLong(resultSet.getColumnIndex("mIsSportsHistory")) !== 0
history.mTm = resultSet.getLong(resultSet.getColumnIndex("mTm"))
history.mVe = resultSet.getString(resultSet.getColumnIndex("mVe"))
history.ckuid = resultSet.getString(resultSet.getColumnIndex("ckuid"));
history.terminalId = resultSet.getString(resultSet.getColumnIndex("terminalId"));
history.agentType = resultSet.getString(resultSet.getColumnIndex("agentType"));
history.tail = resultSet.getLong(resultSet.getColumnIndex("tail"))
console.log(TAG, 'historyinfo: ', JSON.stringify(history));
items.push(history)
}
resultSet.close();
return items;
}
}
const historyInfo = await HistoryInfoManager.getInstance()
.getHistoryInfoByqipuId(StringUtils.parseInt(data.getOutsideAlbumId(), 0));
此方法执行时，数据库未init就执行了查询，导致第一次查询失败
本文详细解析了在preparedstatement对象中使用booleanexecute方法执行SQL语句的过程，包括其返回值true和false的区别，以及如何处理查询和更新语句的结果。
扫一扫
2036
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
