在TypeScript中将SQLite查询结果映射到类型化对象

import * as sqlite3 from 'sqlite3'; // 假设你已经配置了sqlite3

// 定义Obj接口，与数据库表结构匹配
interface Obj {
  id: number;
  name: string;
  amount: number;
}

// 假设db是一个已连接的sqlite3数据库实例
// const db = new sqlite3.Database(':memory:'); 

/**
 * 从ObjTable中获取所有对象并返回一个Promise
 * 该Promise解析为一个Obj数组
 */
export const GetAllObjs = (): Promise<Obj[]> => {
  const query = db.prepare("SELECT * FROM ObjTable");

  return new Promise((resolve, reject) => {
    query.all((err: Error | null, rows: any[]) => {
      if (err) {
        // 如果发生错误，拒绝Promise
        return reject(err);
      }

      const objs: Obj[] = [];
      // 使用 for...of 循环正确迭代行数据
      for (const row of rows) {
        // 将每一行数据映射到Obj接口
        objs.push({
          id: row.id,
          name: row.name,
          amount: row.amount,
        });
      }
      // 解析Promise，返回Obj数组
      resolve(objs);
    });
  });
};

import * as sqlite3 from 'sqlite3';

// 假设db是一个已连接的sqlite3数据库实例
// 通常在Electron主进程或Node.js服务启动时初始化
const db = new sqlite3.Database('./mydb.sqlite', (err) => {
  if (err) {
    console.error("Error opening database", err);
  } else {
    console.log("Database connected.");
  }
});

// 1. 定义Obj接口，与数据库表结构匹配
interface Obj {
  id: number;
  name: string;
  amount: number;
}

// 2. 表创建函数
export const CreateObjTable = (): Promise<sqlite3.RunResult> => {
  return new Promise((resolve, reject) => {
    const query = db.prepare(`
      CREATE TABLE IF NOT EXISTS ObjTable
      (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT,
        amount INTEGER
      )
    `);
    query.run((err: Error | null, result: sqlite3.RunResult) => {
      if (err) {
        return reject(err);
      }
      resolve(result);
    });
  });
};

// 3. 插入数据示例（可选，用于测试）
export const InsertObj = (name: string, amount: number): Promise<sqlite3.RunResult> => {
  return new Promise((resolve, reject) => {
    const query = db.prepare("INSERT INTO ObjTable (name, amount) VALUES (?, ?)");
    query.run(name, amount, function(err: Error | null) {
      if (err) {
        return reject(err);
      }
      // this.lastID 可以获取插入的ID
      resolve(this); 
    });
  });
};

// 4. 获取所有对象并反序列化为Obj[]
export const GetAllObjs = (): Promise<Obj[]> => {
  const query = db.prepare("SELECT * FROM ObjTable");

  return new Promise((resolve, reject) => {
    query.all((err: Error | null, rows: any[]) => {
      if (err) {
        return reject(err);
      }

      const objs: Obj[] = [];
      for (const row of rows) { // 正确使用 for...of 迭代
        objs.push({
          id: row.id,
          name: row.name,
          amount: row.amount,
        });
      }
      resolve(objs);
    });
  });
};

// 使用示例 (在async函数中或Promise链中调用)
(async () => {
  try {
    await CreateObjTable();
    console.log("ObjTable created or already exists.");

    await InsertObj("Item A", 100);
    await InsertObj("Item B", 250);
    console.log("Sample data inserted.");

    const allObjs = await GetAllObjs();
    console.log("Retrieved objects:", allObjs);

    // 确保关闭数据库连接
    db.close((err) => {
      if (err) {
        console.error("Error closing database", err);
      } else {
        console.log("Database closed.");
      }
    });

  } catch (error) {
    console.error("An error occurred:", error);
  }
})();