Electron+Ts+Vue+Vite桌面应用系列：sqlite增删改查操作篇

import { Database } from 'better-sqlite3';
import { open } from 'sqlite';
 
// 定义数据库操作的接口
interface IDBOperation {
  openDB: () => Promise<Database>,
  closeDB: (db: Database) => Promise<void>,
  addData: (db: Database, data: any) => Promise<void>,
  getData: (db: Database, query: string) => Promise<any[]>,
  updateData: (db: Database, data: any) => Promise<void>,
  deleteData: (db: Database, id: number) => Promise<void>
}
 
// 实现接口的具体操作
class DBOperation implements IDBOperation {
  private dbPath: string = 'path/to/your/database.db';
 
  public async openDB(): Promise<Database> {
    return open({
      filename: this.dbPath,
      driver: require('sqlite3').verbose,
    });
  }
 
  public async closeDB(db: Database): Promise<void> {
    await db.close();
  }
 
  public async addData(db: Database, data: any): Promise<void> {
    // 假设data是一个对象，含有id和name属性
    const stmt = db.prepare(`INSERT INTO your_table (id, name) VALUES (?, ?);`);
    stmt.run(data.id, data.name);
    stmt.finalize();
  }
 
  public async getData(db: Database, query: string): Promise<any[]> {
    const stmt = db.prepare(`SELECT * FROM your_table WHERE name = ?;`);
    const rows = stmt.all(query);
    stmt.finalize();
    return rows;
  }
 
  public async updateData(db: Database, data: any): Promise<void> {
    const stmt = db.prepare(`UPDATE your_table SET name = ? WHERE id = ?;`);
    stmt.run(data.name, data.id);
    stmt.finalize();
  }
 
  public async deleteData(db: Database, id: number): Promise<void> {
    const stmt = db.prepare(`DELETE FROM your_table WHERE id = ?;`);
    stmt.run(id);
    stmt.finalize();
  }
}
 
// 使用示例
async function useDBOperation() {
  const dbOperation = new DBOperation();
  const db = await dbOperation.openDB();
 
  try {
    // 添加数据
    await dbOperation.addData(db, { id: 1, name: 'Alice' });
 
    // 查询数据
    const data = await dbOperation.getData(db, 'Alice');
    console.log(data);
 
    // 更新数据
    await dbOperation.updateData(db, { id: 1, name: 'Bob' });
 
    // 删除数据
    await dbOperation.deleteData(db, 1);
  } finally {
    await dbOperation.closeDB(db);
  }
}
 
useDBOperation();

这段代码展示了如何使用TypeScript和better-sqlite3库来实现一个简单的SQLite数据库操作类。这个类遵循IDBOperation接口，提供了打开数据库、关闭数据库、添加数据、查询数据、更新数据和删除数据的方法。使用async/await来处理异步操作，确保代码的清晰和可读性。