ArKTS: DAL，Model，BLL，Interface，Factory using SQLite

HarmonyOS 用@ohos.data.rdb 用DBHelper.ets 共用调用SQLite 库，进行DAL,Model,BLL,Interface,Factory 框架模式，表为

CREATE TABLE IF NOT EXISTS signInRecord (
id INTEGER PRIMARY KEY AUTOINCREMENT,
employeeId TEXT NOT NULL,
employeeName TEXT NOT NULL,
signInTime TEXT NOT NULL
)

项目结构 ：
UtilitieDB
--DBHelper.ets
DAL
--signIn.ets
Model
--signIn.ets
BLL
----signIn.ets
Interface
----IsignIn.ets
Factory
--AbstractFactory.ets

/*
# encoding: utf-8
# 版权所有  2025 ©涂聚文有限公司™ ®
# 许可信息查看：言語成了邀功盡責的功臣，還需要行爲每日來值班嗎
# 描述：
# Author    : geovindu,Geovin Du 涂聚文.
# IDE       : DevEco Studio 5.1.1 HarmonyOS
# os        : windows 10
# database  : mysql 9.0 sql server 2019, postgreSQL 17.0  Oracle 21c Neo4j
# Datetime  : 2025/7/22 19:11
# User      :  geovindu
# Product   : DevEco Studio
# Project   : MyApplication
# File      : signIn.ets
*/
  
export interface SignInRecordDbObject {
  id?: number;
  employeeId: string;
  employeeName: string;
  signInTime: string;
}
  
export class SignInRecord {
  id: number | null;
  employeeId: string;
  employeeName: string;
  signInTime: string;
  
  constructor(
    employeeId: string,
    employeeName: string,
    signInTime: string,
    id: number | null = null
  ) {
    this.id = id;
    this.employeeId = employeeId;
    this.employeeName = employeeName;
    this.signInTime = signInTime;
  }
  
  // 转换为数据库存储的对象格式（类型安全版本）
  toDbObject(): SignInRecordDbObject {
    const dbObject: SignInRecordDbObject = {
      employeeId: this.employeeId,
      employeeName: this.employeeName,
      signInTime: this.signInTime
    };
  
    if (this.id !== null) {
      dbObject.id = this.id;
    }
  
    return dbObject;
  }
  
  // 从数据库对象创建模型实例（类型安全版本）
  static fromDbObject(dbObject: SignInRecordDbObject): SignInRecord {
    return new SignInRecord(
      dbObject.employeeId,
      dbObject.employeeName,
      dbObject.signInTime,
      dbObject.id !== undefined ? dbObject.id : null
    );
  }
}
  
  
  
/*
# encoding: utf-8
# 版权所有  2025 ©涂聚文有限公司™ ®
# 许可信息查看：言語成了邀功盡責的功臣，還需要行爲每日來值班嗎
# 描述：
# Author    : geovindu,Geovin Du 涂聚文.
# IDE       : DevEco Studio 5.1.1 HarmonyOS
# os        : windows 10
# database  : mysql 9.0 sql server 2019, postgreSQL 17.0  Oracle 21c Neo4j
# Datetime  : 2025/7/22 19:04
# User      :  geovindu
# Product   : DevEco Studio
# Project   : MyApplication
# File      : DBHelper.ets
*/
  
import relationalStore from '@ohos.data.relationalStore';
import { BusinessError } from '@ohos.base';
import { Context } from '@ohos.abilityAccessCtrl';
  
export class DBHelper {
  private static readonly DB_NAME: string = 'signInDB.db';
  private static readonly DB_VERSION: number = 1;
  private rdbStore: relationalStore.RdbStore | null = null;
  private static instance: DBHelper | null = null;
  private context: Context | null = null; // 新增上下文存储
  
  private constructor() {}
  /**
   *
   * @returns
   */
  public static getInstance(): DBHelper {
    if (!DBHelper.instance) {
      DBHelper.instance = new DBHelper();
    }
  
    if (!DBHelper.instance) {
      throw new Error('无法创建DBHelper实例，请检查实现');
    }
  
    return DBHelper.instance;
  }
  
  /**
   * 初始化数据库时保存上下文
   * @param context
   * @returns
   */
  public async initDB(context: Context): Promise<relationalStore.RdbStore> {
    if (this.rdbStore) {
      return this.rdbStore;
    }
  
    /**
     * 保存上下文供后续使用
     */
    this.context = context;
  
    const storeConfig: relationalStore.StoreConfig = {
      name: DBHelper.DB_NAME,
      securityLevel: relationalStore.SecurityLevel.S1
    };
  
    try {
      this.rdbStore = await relationalStore.getRdbStore(context, storeConfig);
      await this.createTables();
      return this.rdbStore;
    } catch (error) {
      const err = error as BusinessError;
      console.error(`初始化数据库失败: ${err.code} - ${err.message}`);
      throw new Error(`数据库初始化失败: ${err.message}`);
    }
  }
  /**
   *
   * @returns
   */
  private async createTables(): Promise<void> {
    if (!this.rdbStore) {
      throw new Error('数据库未初始化，请先调用initDB方法');
    }
  
    const createTableSql: string = `
      CREATE TABLE IF NOT EXISTS signInRecord (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        employeeId TEXT NOT NULL,
        employeeName TEXT NOT NULL,
        signInTime TEXT NOT NULL
      )
    `;
  
    try {
      await this.rdbStore.executeSql(createTableSql);
      console.log('签到记录表创建成功或已存在');
    } catch (error) {
      const err = error as BusinessError;
      console.error(`创建签到记录表失败: ${err.code} - ${err.message}`);
      throw new Error(`创建数据表失败: ${err.message}`);
    }
  }
  /**
   *
   * @returns
   */
  public getRdbStore(): relationalStore.RdbStore | null {
    if (!this.rdbStore) {
      console.warn('数据库尚未初始化，请先调用initDB方法');
    }
    return this.rdbStore;
  }
  
  /**
   * 修正关闭数据库的方法
   * @returns
   */
  public async closeDB(): Promise<void> {
    if (this.rdbStore && this.context) {
      try {
        // 正确调用deleteRdbStore，需要传入上下文和数据库名称
        await relationalStore.deleteRdbStore(this.context, DBHelper.DB_NAME);
        this.rdbStore = null;
        this.context = null; // 清除上下文
        console.log('数据库已关闭并删除');
      } catch (error) {
        const err = error as BusinessError;
        console.error(`关闭数据库失败: ${err.code} - ${err.message}`);
        throw new Error(`关闭数据库失败: ${err.message}`);
      }
    } else {
      if (!this.context) {
        console.warn('上下文不存在，无法关闭数据库');
      } else {
        console.warn('数据库连接不存在，无需关闭');
      }
    }
  }
}
  
  
  
/*
# encoding: utf-8
# 版权所有  2025 ©涂聚文有限公司™ ®
# 许可信息查看：言語成了邀功盡責的功臣，還需要行爲每日來值班嗎
# 描述：
# Author    : geovindu,Geovin Du 涂聚文.
# IDE       : DevEco Studio 5.1.1 HarmonyOS
# os        : windows 10
# database  : mysql 9.0 sql server 2019, postgreSQL 17.0  Oracle 21c Neo4j
# Datetime  : 2025/7/22 19:14
# User      :  geovindu
# Product   : DevEco Studio
# Project   : MyApplication
# File      : IsignIn.ets
*/
  
import { SignInRecord } from '../model/signIn';
  
export interface ISignIn {
  
  
  /**
   * 添加签到记录
   * @param record
   * @returns
   */
  addSignInRecord(record: SignInRecord): Promise<number>;
  
  /**
   * 根据ID获取签到记录
   * @param id
   * @returns
   */
  getSignInRecordById(id: number): Promise<SignInRecord | null>;
  
  /**
   * 获取所有签到记录
   * @returns
   */
  getAllSignInRecords(): Promise<SignInRecord[]>;
  
  /**
   * 根据员工ID获取签到记录
   * @param employeeId
   * @returns
   */
  getSignInRecordsByEmployeeId(employeeId: string): Promise<SignInRecord[]>;
  
  /**
   * 更新签到记录
   * @param record
   * @returns
   */
  updateSignInRecord(record: SignInRecord): Promise<number>;
  
  /**
   * 删除签到记录
   * @param id
   * @returns
   */
  deleteSignInRecord(id: number): Promise<number>;
}
  
  
/*
# encoding: utf-8
# 版权所有  2025 ©涂聚文有限公司™ ®
# 许可信息查看：言語成了邀功盡責的功臣，還需要行爲每日來值班嗎
# 描述：
# Author    : geovindu,Geovin Du 涂聚文.
# IDE       : DevEco Studio 5.1.1 HarmonyOS
# os        : windows 10
# database  : mysql 9.0 sql server 2019, postgreSQL 17.0  Oracle 21c Neo4j
# Datetime  : 2025/7/22 19:16
# User      :  geovindu
# Product   : DevEco Studio
# Project   : MyApplication
# File      : AbstractFactory.ets
*/
import { ISignIn } from '../Interface/IsignIn';
import { SignInDAL } from '../DAL/signIn';
import { SignInBLL } from '../BLL/signIn';
  
/**
 * 抽象工厂类
 */
export abstract class AbstractFactory {
  // 创建数据访问层实例
  public abstract createSignInDAL(): ISignIn;
  
  // 创建业务逻辑层实例
  public abstract createSignInBLL(): ISignIn;
}
  
/**
 * 签到记录工厂类
 */
export class SignInFactory extends AbstractFactory {
  // 创建签到记录数据访问层实例
  public createSignInDAL(): ISignIn {
    return new SignInDAL();
  }
  
  /**
   * 创建签到记录业务逻辑层实例
   * @returns
   */
  public createSignInBLL(): ISignIn {
    // 可以在这里注入不同的DAL实现，方便测试或切换数据源
    const dal = this.createSignInDAL();
    return new SignInBLL(dal);
  }
}
  
  
  
/*
# encoding: utf-8
# 版权所有  2025 ©涂聚文有限公司™ ®
# 许可信息查看：言語成了邀功盡責的功臣，還需要行爲每日來值班嗎
# 描述：
# Author    : geovindu,Geovin Du 涂聚文.
# IDE       : DevEco Studio 5.1.1 HarmonyOS
# os        : windows 10
# database  : mysql 9.0 sql server 2019, postgreSQL 17.0  Oracle 21c Neo4j
# Datetime  : 2025/7/22 19:17
# User      :  geovindu
# Product   : DevEco Studio
# Project   : MyApplication
# File      : signIn.ets
*/
import relationalStore from '@ohos.data.relationalStore';
import { DBHelper } from '../UtilitieDB/DBHelper';
import { SignInRecord, SignInRecordDbObject } from '../model/signIn';
import { ISignIn } from '../Interface/IsignIn';
import { BusinessError } from '@ohos.base';
  
// 定义与数据库字段对应的类型映射
type SignInColumn = 'id' | 'employeeId' | 'employeeName' | 'signInTime';
  
export class SignInDAL implements ISignIn {
  private getRdbStore() {
    const rdbStore = DBHelper.getInstance().getRdbStore();
    if (!rdbStore) {
      throw new Error('数据库未初始化，请先调用initDB方法');
    }
    return rdbStore;
  }
  
  /**
   * 添加签到记录
   * @param record
   * @returns
   */
  async addSignInRecord(record: SignInRecord): Promise<number> {
    try {
      const dbObject = record.toDbObject();
  
      // 构建符合要求的ValuesBucket
      const valuesBucket: relationalStore.ValuesBucket = {
        'employeeId': dbObject.employeeId,
        'employeeName': dbObject.employeeName,
        'signInTime': dbObject.signInTime
      };
  
      const rowId = await this.getRdbStore().insert('signInRecord', valuesBucket);
      return rowId;
    } catch (error) {
      const err = error as BusinessError;
      console.error(`添加签到记录失败: ${err.code} - ${err.message}`);
      throw new Error(`添加签到记录失败: ${err.message}`);
    }
  }
  /**
   * 根据ID获取签到记录
   * @param id
   * @returns
   */
  async getSignInRecordById(id: number): Promise<SignInRecord | null> {
    try {
      const predicates = new relationalStore.RdbPredicates('signInRecord');
      predicates.equalTo('id', id);
  
      const resultSet = await this.getRdbStore().query(predicates, ['*']);
      let record: SignInRecord | null = null;
  
      if (await resultSet.goToFirstRow()) {
        const dbObject: SignInRecordDbObject = {
          id: resultSet.getLong(resultSet.getColumnIndex('id')),
          employeeId: resultSet.getString(resultSet.getColumnIndex('employeeId')),
          employeeName: resultSet.getString(resultSet.getColumnIndex('employeeName')),
          signInTime: resultSet.getString(resultSet.getColumnIndex('signInTime'))
        };
        record = SignInRecord.fromDbObject(dbObject);
      }
  
      await resultSet.close();
      return record;
    } catch (error) {
      const err = error as BusinessError;
      console.error(`查询签到记录失败: ${err.code} - ${err.message}`);
      throw new Error(`查询签到记录失败: ${err.message}`);
    }
  }
  
  /**
   * 根据员工ID获取签到记录
   * @param employeeId
   * @returns
   */
  async getSignInRecordsByEmployeeId(employeeId: string): Promise<SignInRecord[]> {
    try {
      const predicates = new relationalStore.RdbPredicates('signInRecord');
      predicates.equalTo('employeeId', employeeId)
        .orderByDesc('signInTime');
  
      const resultSet = await this.getRdbStore().query(predicates, ['*']);
      const records: SignInRecord[] = [];
  
      while (await resultSet.goToNextRow()) {
        const dbObject: SignInRecordDbObject = {
          id: resultSet.getLong(resultSet.getColumnIndex('id')),
          employeeId: resultSet.getString(resultSet.getColumnIndex('employeeId')),
          employeeName: resultSet.getString(resultSet.getColumnIndex('employeeName')),
          signInTime: resultSet.getString(resultSet.getColumnIndex('signInTime'))
        };
        records.push(SignInRecord.fromDbObject(dbObject));
      }
  
      await resultSet.close();
      return records;
    } catch (error) {
      const err = error as BusinessError;
      console.error(`根据员工ID查询签到记录失败: ${err.code} - ${err.message}`);
      throw new Error(`根据员工ID查询签到记录失败: ${err.message}`);
    }
  }
  /**
   * 获取所有签到记录
   * @returns
   */
  async getAllSignInRecords(): Promise<SignInRecord[]> {
    try {
      const predicates = new relationalStore.RdbPredicates('signInRecord');
      predicates.orderByDesc('signInTime');
  
      const resultSet = await this.getRdbStore().query(predicates, ['*']);
      const records: SignInRecord[] = [];
  
      while (await resultSet.goToNextRow()) {
        const dbObject: SignInRecordDbObject = {
          id: resultSet.getLong(resultSet.getColumnIndex('id')),
          employeeId: resultSet.getString(resultSet.getColumnIndex('employeeId')),
          employeeName: resultSet.getString(resultSet.getColumnIndex('employeeName')),
          signInTime: resultSet.getString(resultSet.getColumnIndex('signInTime'))
        };
        records.push(SignInRecord.fromDbObject(dbObject));
      }
  
      await resultSet.close();
      return records;
    } catch (error) {
      const err = error as BusinessError;
      console.error(`获取所有签到记录失败: ${err.code} - ${err.message}`);
      throw new Error(`获取所有签到记录失败: ${err.message}`);
    }
  }
  /**
   * 更新签到记录
   * @param record
   * @returns
   */
  async updateSignInRecord(record: SignInRecord): Promise<number> {
    if (record.id === null) {
      throw new Error('更新失败，签到记录ID不能为空');
    }
  
    try {
      const dbObject = record.toDbObject();
      const predicates = new relationalStore.RdbPredicates('signInRecord');
      predicates.equalTo('id', record.id); // 这里已经指定了要更新的记录ID
  
      // 构建ValuesBucket，不包含id字段，因为不需要更新主键
      const valuesBucket: relationalStore.ValuesBucket = {
        'employeeId': dbObject.employeeId,
        'employeeName': dbObject.employeeName,
        'signInTime': dbObject.signInTime
      };
  
      // 执行更新操作
      const rowsUpdated = await this.getRdbStore().update(valuesBucket, predicates);
      return rowsUpdated;
    } catch (error) {
      const err = error as BusinessError;
      console.error(`更新签到记录失败: ${err.code} - ${err.message}`);
      throw new Error(`更新签到记录失败: ${err.message}`);
    }
  }
  
  /**
   * 删除签到记录
   * @param id
   * @returns
   */
  async deleteSignInRecord(id: number): Promise<number> {
    try {
      const predicates = new relationalStore.RdbPredicates('signInRecord');
      predicates.equalTo('id', id);
  
      const rowsDeleted = await this.getRdbStore().delete(predicates);
      return rowsDeleted;
    } catch (error) {
      const err = error as BusinessError;
      console.error(`删除签到记录失败: ${err.code} - ${err.message}`);
      throw new Error(`删除签到记录失败: ${err.message}`);
    }
  }
  /**
   * 将结果集转换为签到记录数组
   * @param resultSet
   * @returns
   */
  private convertResultSetToSignInRecords(resultSet: relationalStore.ResultSet): SignInRecord[] {
    const records: SignInRecord[] = [];
  
    resultSet.goToFirstRow();
    do {
      const record = this.convertResultSetToSignInRecord(resultSet);
      records.push(record);
    } while (resultSet.goToNextRow());
  
    return records;
  }
  
  /**
   * 结果集转换为单个签到记录
   * @param resultSet
   * @returns
   */
  private convertResultSetToSignInRecord(resultSet: relationalStore.ResultSet): SignInRecord {
    return new SignInRecord(
      resultSet.getString(resultSet.getColumnIndex('employeeId')),
      resultSet.getString(resultSet.getColumnIndex('employeeName')),
      resultSet.getString(resultSet.getColumnIndex('signInTime')),
      resultSet.getLong(resultSet.getColumnIndex('id'))
    );
  }
  
}
  
  
  
/*
# encoding: utf-8
# 版权所有  2025 ©涂聚文有限公司™ ®
# 许可信息查看：言語成了邀功盡責的功臣，還需要行爲每日來值班嗎
# 描述：
# Author    : geovindu,Geovin Du 涂聚文.
# IDE       : DevEco Studio 5.1.1 HarmonyOS
# os        : windows 10
# database  : mysql 9.0 sql server 2019, postgreSQL 17.0  Oracle 21c Neo4j
# Datetime  : 2025/7/22 18:18
# User      :  geovindu
# Product   : DevEco Studio
# Project   : MyApplication
# File      : signIn.ets
*/
import { ISignIn } from '../Interface/IsignIn';
import { SignInRecord } from '../model/signIn';
import { SignInDAL } from '../DAL/signIn';
  
export class SignInBLL implements ISignIn {
  private signInDAL: ISignIn;
  
  constructor(signInDAL: ISignIn = new SignInDAL()) {
    this.signInDAL = signInDAL;
  }
  
  /**
   * 添加签到记录，包含业务逻辑验证
   * @param record
   * @returns
   */
  async addSignInRecord(record: SignInRecord): Promise<number> {
    // 业务逻辑验证：检查必要字段
    if (!record.employeeId || record.employeeId.trim() === '') {
      throw new Error('员工ID不能为空');
    }
  
    if (!record.employeeName || record.employeeName.trim() === '') {
      throw new Error('员工姓名不能为空');
    }
  
    // 检查签到时间格式是否合法
    if (!this.isValidDateTime(record.signInTime)) {
      throw new Error('签到时间格式不正确');
    }
  
    // 调用数据访问层添加记录
    return this.signInDAL.addSignInRecord(record);
  }
  
  /**
   * 根据ID获取签到记录
   * @param id
   * @returns
   */
  async getSignInRecordById(id: number): Promise<SignInRecord | null> {
    if (id <= 0) {
      throw new Error('无效的记录ID');
    }
    return this.signInDAL.getSignInRecordById(id);
  }
  
  /**
   * 获取所有签到记录
   * @returns
   */
  async getAllSignInRecords(): Promise<SignInRecord[]> {
    return this.signInDAL.getAllSignInRecords();
  }
  
  /**
   * 根据员工ID获取签到记录
   * @param employeeId
   * @returns
   */
  async getSignInRecordsByEmployeeId(employeeId: string): Promise<SignInRecord[]> {
    if (!employeeId || employeeId.trim() === '') {
      throw new Error('员工ID不能为空');
    }
    return this.signInDAL.getSignInRecordsByEmployeeId(employeeId);
  }
  
  /**
   * 更新签到记录
   * @param record
   * @returns
   */
  async updateSignInRecord(record: SignInRecord): Promise<number> {
    if (!record.id) {
      throw new Error('签到记录ID不能为空');
    }
  
    /**
     * 验证更新的数据
     */
    if (!record.employeeId || record.employeeId.trim() === '') {
      throw new Error('员工ID不能为空');
    }
  
    if (!this.isValidDateTime(record.signInTime)) {
      throw new Error('签到时间格式不正确');
    }
  
    return this.signInDAL.updateSignInRecord(record);
  }
  
  /**
   * 删除签到记录
   * @param id
   * @returns
   */
  async deleteSignInRecord(id: number): Promise<number> {
    if (id <= 0) {
      throw new Error('无效的记录ID');
    }
    return this.signInDAL.deleteSignInRecord(id);
  }
  
  // 验证日期时间格式 (YYYY-MM-DD HH:MM:SS)
  private isValidDateTime(dateTime: string): boolean {
    const regex = /^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/;
    return regex.test(dateTime);
  }
}
  
  
  
/*
# encoding: utf-8
# 版权所有  2025 ©涂聚文有限公司™ ®
# 许可信息查看：言語成了邀功盡責的功臣，還需要行爲每日來值班嗎
# 描述：
# Author    : geovindu,Geovin Du 涂聚文.
# IDE       : DevEco Studio 5.1.1 HarmonyOS
# os        : windows 10
# database  : mysql 9.0 sql server 2019, postgreSQL 17.0  Oracle 21c Neo4j
# Datetime  : 2025/7/22 18:56
# User      :  geovindu
# Product   : DevEco Studio
# Project   : MyApplication
# File      : SignInExample.ets
*/
import { Context } from '@ohos.abilityAccessCtrl';
import { SignInFactory } from '../Factory/AbstractFactory';
import { DBHelper } from '../UtilitieDB/DBHelper';
import { SignInRecord } from '../model/signIn';
import { ISignIn } from '../Interface/IsignIn';
  
export class SignInExample {
  private signInBLL: ISignIn;
  
  constructor(context: Context) {
    // 初始化数据库
    this.initDatabase(context);
  
    // 通过工厂获取BLL实例
    const factory = new SignInFactory();
    this.signInBLL = factory.createSignInBLL();
  }
  
  /**
   * 初始化数据库
   * @param context
   */
  private async initDatabase(context: Context) {
    try {
      await DBHelper.getInstance().initDB(context);
      console.log('数据库初始化成功');
    } catch (error) {
      console.error('数据库初始化失败:', error);
    }
  }
  
  /**
   * 添加签到记录示例
   * @returns
   */
  async addSignInRecordExample() {
    try {
      // 创建签到记录对象
      const now = new Date();
      const signInTime = `${now.getFullYear()}-${(now.getMonth() + 1).toString().padStart(2, '0')}-${now.getDate().toString().padStart(2, '0')} ${now.getHours().toString().padStart(2, '0')}:${now.getMinutes().toString().padStart(2, '0')}:${now.getSeconds().toString().padStart(2, '0')}`;
  
      const record = new SignInRecord(
        'EMP001',       // employeeId
        '张三',          // employeeName
        signInTime       // signInTime
      );
  
      // 调用BLL层添加记录
      const rowId = await this.signInBLL.addSignInRecord(record);
      console.log(`添加签到记录成功，ID: ${rowId}`);
      return rowId;
    } catch (error) {
      console.error('添加签到记录失败:', error);
      return null;
    }
  }
  
  /**
   * 查询签到记录示例
   */
  async querySignInRecordsExample() {
    try {
      // 1. 查询所有签到记录
      const allRecords = await this.signInBLL.getAllSignInRecords();
      console.log(`所有签到记录(${allRecords.length}条):`, allRecords);
  
      // 2. 根据ID查询记录（假设我们知道一个存在的ID）
      if (allRecords.length > 0) {
        const firstRecordId = allRecords[0].id;
        if (firstRecordId !== null) {
          const singleRecord = await this.signInBLL.getSignInRecordById(firstRecordId);
          console.log(`ID为${firstRecordId}的签到记录:`, singleRecord);
        }
  
      }
  
      // 3. 根据员工ID查询记录
      const empRecords = await this.signInBLL.getSignInRecordsByEmployeeId('EMP001');
      console.log(`员工EMP001的签到记录(${empRecords.length}条):`, empRecords);
      return allRecords;
    } catch (error) {
      console.error('查询签到记录失败:', error);
      return null;
    }
  }
  /**
   *
   * @param id
   * @returns
   */
  async querySignInRecordsExampleId(id:string) {
    try {
      // 1. 查询所有签到记录
      const allRecords = await this.signInBLL.getSignInRecordsByEmployeeId(id);
      console.log(`所有签到记录(${allRecords.length}条):`, allRecords);
  
      // 2. 根据ID查询记录（假设我们知道一个存在的ID）
      if (allRecords.length > 0) {
        const firstRecordId = allRecords[0].id;
        if (firstRecordId !== null) {
          const singleRecord = await this.signInBLL.getSignInRecordById(firstRecordId);
          console.log(`ID为${firstRecordId}的签到记录:`, singleRecord);
        }
  
      }
  
      // 3. 根据员工ID查询记录
      const empRecords = await this.signInBLL.getSignInRecordsByEmployeeId('EMP001');
      console.log(`员工EMP001的签到记录(${empRecords.length}条):`, empRecords);
      return allRecords;
  
    } catch (error) {
      console.error('查询签到记录失败:', error);
      return null;
    }
  }
  /**
   * 更新签到记录示例
   * @param recordId
   */
  async updateSignInRecordExample(recordId: number) {
    try {
      // 先查询要更新的记录
      const record = await this.signInBLL.getSignInRecordById(recordId);
      if (!record) {
        console.log(`未找到ID为${recordId}的签到记录`);
        return -1;
      }
  
      // 修改记录内容
      record.employeeName = '张三(更新)'; // 修改员工姓名
  
      // 调用BLL层更新记录
      const rowsUpdated = await this.signInBLL.updateSignInRecord(record);
      console.log(`更新成功，影响行数: ${rowsUpdated}`);
  
      // 验证更新结果
      const updatedRecord = await this.signInBLL.getSignInRecordById(recordId);
      console.log('更新后的记录:', updatedRecord);
      return 1;
    } catch (error) {
      console.error('更新签到记录失败:', error);
      return -1;
    }
  }
  
  /**
   * 删除签到记录示例
   * @param recordId
   */
  async deleteSignInRecordExample(recordId: number) {
    try {
      // 调用BLL层删除记录
      const rowsDeleted = await this.signInBLL.deleteSignInRecord(recordId);
      console.log(`删除成功，影响行数: ${rowsDeleted}`);
  
      // 验证删除结果
      const deletedRecord = await this.signInBLL.getSignInRecordById(recordId);
      if (!deletedRecord) {
        console.log(`ID为${recordId}的签到记录已成功删除`);
  
      }
      return 1;
  
    } catch (error) {
      console.error('删除签到记录失败:', error);
      return -1;
    }
  }
  
  /**
   * 完整操作流程示例
   */
  async completeOperationFlow() {
    // 1. 添加一条新记录
    const newRecordId = await this.addSignInRecordExample();
  
    if (newRecordId) {
      // 2. 查询记录
      await this.querySignInRecordsExample();
  
      // 3. 更新记录
      await this.updateSignInRecordExample(newRecordId);
  
      // 4. 删除记录
      await this.deleteSignInRecordExample(newRecordId);
    }
  
    // 关闭数据库（通常在应用退出时调用）
    // await DBHelper.getInstance().closeDB();
  }
}
  
// 在Ability中使用示例
// export default class SignInAbility extends Ability {
//   onWindowStageCreate(windowStage: window.WindowStage) {
//     // 初始化示例并执行操作
//     const signInExample = new SignInExample(this.context);
//     signInExample.completeOperationFlow();
//   }
// }

调用：

/**
 # encoding: utf-8
 # 版权所有 2024 ©涂聚文有限公司
 # 许可信息查看：言語成了邀功盡責的功臣，還需要行爲每日來值班嗎
 # 描述：
 # Author    : geovindu,Geovin Du 涂聚文.
 # IDE       : DevEco Studio 5.1.1
 # OS        : windows 10
 # database  : mysql 9.0 sql server 2019, postgreSQL 17.0  Oracle 21c Neo4j
 # Datetime  : 2025/7/22 19:33
 # User      : geovindu
 # Product   : MyApplicatin
 # Project   : MyApplicatin
 # File      : Register.ets
 * */
  
import router from '@ohos.router'
import promptAction from '@ohos.promptAction'
import { SignInExample } from '../Controller/SignInExample';
import { SignInRecord } from '../model/signIn';
import { Context } from '@ohos.abilityAccessCtrl';
  
  
  
@Entry
@Component
struct Register {
  @State message: string = '注册用户';
  // 页面数据
  @State allRecords: SignInRecord[] = [];
  @State employeeId: string = 'EMP001';
  @State employeeName: string = '张三';
  @State currentRecordId: number | null = null;
  // 页面上下文（用于数据库初始化）
  private context: Context = getContext(this) as Context;
  // 签到业务实例
  private signInExample: SignInExample = new SignInExample(this.context);
  
  // 页面加载时初始化数据
  async aboutToAppear() {
    await this.loadAllRecords(); // 加载所有签到记录
  }
  
  // 加载所有签到记录
  // 修正后的加载所有签到记录方法
  async loadAllRecords() {
    try {
      // 调用查询方法并获取结果
      const result = await this.signInExample.querySignInRecordsExample();
  
      // 空值判断：如果结果为null，设置为空数组
      if (result === null) {
        this.allRecords = [];
        promptAction.showToast({ message: '没有查询到签到记录' });
        return;
      }
  
      // 类型判断：确保返回的是数组
      if (!Array.isArray(result)) {
        this.allRecords = [];
        promptAction.showToast({ message: '数据格式错误' });
        console.error('查询签到记录返回非数组类型:', result);
        return;
      }
  
      // 正常赋值
      this.allRecords = result;
  
      // 空数组提示
      if (this.allRecords.length === 0) {
        promptAction.showToast({ message: '当前没有签到记录' });
      }
    } catch (error) {
      // 错误处理：统一捕获并处理所有可能的异常
      this.allRecords = []; // 出错时清空列表
      const errorMsg = error instanceof Error ? error.message : String(error);
      promptAction.showToast({ message: `加载失败: ${errorMsg}` });
      console.error('加载签到记录失败:', error);
    }
  }
  
  /**
   * 添加签到记录（绑定按钮点击事件）
   */
  async handleAdd() {
    if (!this.employeeId || !this.employeeName) {
      promptAction.showToast({ message: '请输入员工ID和姓名' });
      return;
    }
  
    try {
      // 生成当前时间（格式：yyyy-MM-dd HH:mm:ss）
      const now = new Date();
      const signInTime = `${now.getFullYear()}-${(now.getMonth() + 1).toString().padStart(2, '0')}-${now.getDate()
        .toString()
        .padStart(2, '0')} ${now.getHours().toString().padStart(2, '0')}:${now.getMinutes()
        .toString()
        .padStart(2, '0')}:${now.getSeconds().toString().padStart(2, '0')}`;
  
      // 创建记录并添加
      const record = new SignInRecord(this.employeeId, this.employeeName, signInTime);
      const newId = await this.signInExample.addSignInRecordExample(); //record
      promptAction.showToast({ message: `添加成功，ID: ${newId}` });
  
      // 刷新列表
      await this.loadAllRecords();
    } catch (error) {
      promptAction.showToast({ message: `添加失败: ${error.message}` });
    }
  }
  
  /**
   * 更新签到记录（绑定按钮点击事件）
   * @param record
   */
  async handleUpdate(record: SignInRecord) {
    if (!record.id) {
      promptAction.showToast({ message: '记录ID不存在' });
      return;
    }
  
    try {
      // 修改记录（示例：在姓名后加"[已更新]"）
      const updatedRecord = new SignInRecord(
        record.employeeId,
        `${record.employeeName}[已更新]`,
        record.signInTime,
        record.id
      );
  
      // 执行更新
      const rows = await this.signInExample.updateSignInRecordExample(1) //(updatedRecord);
      if (rows > 0) {
        promptAction.showToast({ message: '更新成功' });
        await this.loadAllRecords(); // 刷新列表
      }
    } catch (error) {
      promptAction.showToast({ message: `更新失败: ${error.message}` });
    }
  }
  
  /**
   * 删除签到记录（绑定按钮点击事件）
   * @param id
   */
  async handleDelete(id: number) {
    try {
      const rows = await this.signInExample.deleteSignInRecordExample(id);
      if (rows > 0) {
        promptAction.showToast({ message: '删除成功' });
        await this.loadAllRecords(); // 刷新列表
      }
    } catch (error) {
      promptAction.showToast({ message: `删除失败: ${error.message}` });
    }
  }
  
  /**
   * 根据员工ID查询（绑定按钮点击事件）
   */
  async handleQueryByEmpId() {
    // 验证员工ID输入
    if (!this.employeeId || this.employeeId.trim() === '') {
      promptAction.showToast({ message: '请输入有效的员工ID' });
      return;
    }
  
    try {
      // 调用BLL层方法查询（修正方法名和调用方式）
      const result = await this.signInExample.querySignInRecordsExampleId(this.employeeId.trim());
  
      // 空值判断
      if (result === null) {
        this.allRecords = [];
        promptAction.showToast({ message: '未查询到相关记录' });
        return;
      }
  
      // 类型验证
      if (!Array.isArray(result)) {
        this.allRecords = [];
        promptAction.showToast({ message: '查询数据格式错误' });
        console.error('员工签到记录查询返回非数组类型:', result);
        return;
      }
  
      // 赋值并提示结果
      this.allRecords = result;
  
      // 根据结果数量显示不同提示
      if (this.allRecords.length === 0) {
        promptAction.showToast({ message: `未查询到员工${this.employeeId}的签到记录` });
      } else {
        promptAction.showToast({ message: `查询到${this.allRecords.length}条记录` });
      }
    } catch (error) {
      // 错误处理
      this.allRecords = [];
      const errorMsg = error instanceof Error ? error.message : String(error);
      promptAction.showToast({ message: `查询失败: ${errorMsg}` });
      console.error(`查询员工${this.employeeId}的签到记录失败:`, error);
    }
  }
  
  // 界面渲染
  build() {
    Column() {
      // 操作区域
      Column() {
        Text('签到管理').fontSize(20).fontWeight(FontWeight.Bold).margin(10);
  
        // 输入区域
        Row() {
  
  
          Button('添加签到').onClick(() => this.handleAdd())
            .backgroundColor('#007DFF');
  
          Button('查询该员工').onClick(() => this.handleQueryByEmpId())
            .backgroundColor('#00B42A');
  
          Button('查询所有').onClick(() => this.loadAllRecords())
            .backgroundColor('#F53F3F');
  
          Button('返回').onClick(() => {
            router.back();
          })
            .backgroundColor('#F53F3F');
  
        }.margin(10);
  
        // 记录列表
        Scroll() {
          List() {
            ForEach(this.allRecords, (record: SignInRecord) => {
              ListItem() {
                Row() {
                  Column() {
                    Text(`ID: ${record.id}`).fontSize(12);
                    Text(`员工: ${record.employeeName}(${record.employeeId})`).fontSize(14);
                    Text(`签到时间: ${record.signInTime}`).fontSize(12);
                  }.width('70%');
  
                  Column() {
                    Button('修改').onClick(() => this.handleUpdate(record))
                      .width(80)
                      .margin(5)
                      .backgroundColor('#FF7D00');
  
                    Button('删除').onClick(() => this.handleDelete(record.id!))
                      .width(80)
                      .margin(5)
                      .backgroundColor('#F53F3F');
                  }
                }.padding(10);
              }
            })
          }
        }.width('100%').flexGrow(1);
      }.width('100%').height('100%');
    }.width('100%').height('100%');
  }
}

输出：