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来处理异步操作，确保代码的清晰和可读性。