npm install sql.js --save

import initSqlJs from 'sql.js';
import sqlWasm from 'sql.js/dist/sql-wasm.wasm';

// 加載SQLite模塊
const SQL = await initSqlJs({
    locateFile: () => sqlWasm
});

// 創(chuàng)建一個新的數(shù)據(jù)庫
const db = new SQL.Database();

// 執(zhí)行SQL語句創(chuàng)建表
db.run(`
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY,
        name TEXT,
        age INTEGER,
        email TEXT UNIQUE
    )
`);

// 插入數(shù)據(jù)
db.run("INSERT INTO users (name, age, email) VALUES (?, ?, ?)", ['John Doe', 30, 'john@example.com']);
db.run("INSERT INTO users (name, age, email) VALUES (?, ?, ?)", ['Jane Smith', 25, 'jane@example.com']);

// 查詢數(shù)據(jù)
const result = db.exec("SELECT * FROM users");
console.log(result);
/*
結(jié)果格式:
[
    {
        columns: ['id', 'name', 'age', 'email'],
        values: [
            [1, 'John Doe', 30, 'john@example.com'],
            [2, 'Jane Smith', 25, 'jane@example.com']
        ]
    }
]
*/

// 獲取格式化的結(jié)果
const users = result[0].values.map(row => {
    return {
        id: row[0],
        name: row[1],
        age: row[2],
        email: row[3]
    };
});
console.log(users);

// 關(guān)閉數(shù)據(jù)庫
db.close();

// 插入數(shù)據(jù)（參數(shù)化查詢）
const stmt = db.prepare("INSERT INTO users (name, age, email) VALUES (:name, :age, :email)");
stmt.bind({
    ':name': 'Alice Johnson',
    ':age': 28,
    ':email': 'alice@example.com'
});
stmt.run();
stmt.free();

// 查詢數(shù)據(jù)（參數(shù)化查詢）
const queryStmt = db.prepare("SELECT * FROM users WHERE age > :minAge");
queryStmt.bind({ ':minAge': 25 });
while (queryStmt.step()) {
    const row = queryStmt.getAsObject();
    console.log(row);
}
queryStmt.free();

// 導(dǎo)出數(shù)據(jù)庫為二進(jìn)制數(shù)據(jù)
const binaryArray = db.export();
const blob = new Blob([binaryArray], { type: 'application/octet-stream' });

// 創(chuàng)建下載鏈接
const url = URL.createObjectURL(blob);
const a = document.createElement('a');
a.href = url;
a.download = 'mydatabase.sqlite';
document.body.appendChild(a);
a.click();
document.body.removeChild(a);
URL.revokeObjectURL(url);

// 從服務(wù)器加載數(shù)據(jù)庫
async function loadDatabaseFromServer() {
    const response = await fetch('mydatabase.sqlite');
    const arrayBuffer = await response.arrayBuffer();
    const db = new SQL.Database(new Uint8Array(arrayBuffer));
    return db;
}

// 從本地文件加載數(shù)據(jù)庫（通過文件輸入）
function loadDatabaseFromFile(file) {
    return new Promise((resolve, reject) => {
        const reader = new FileReader();
        reader.onload = function(e) {
            const db = new SQL.Database(new Uint8Array(e.target.result));
            resolve(db);
        };
        reader.onerror = reject;
        reader.readAsArrayBuffer(file);
    });
}

// src/services/sqlite.js
import initSqlJs from 'sql.js';
import sqlWasm from 'sql.js/dist/sql-wasm.wasm';

class SQLiteService {
    constructor() {
        this.db = null;
    }
    
    async init() {
        if (this.db) return;
        
        const SQL = await initSqlJs({
            locateFile: () => sqlWasm
        });
        
        // 嘗試從本地存儲加載數(shù)據(jù)庫
        const dbData = localStorage.getItem('sqlite_db');
        if (dbData) {
            const binaryArray = Uint8Array.from(atob(dbData), c => c.charCodeAt(0));
            this.db = new SQL.Database(binaryArray);
        } else {
            this.db = new SQL.Database();
            this.createTables();
        }
        
        // 定期保存數(shù)據(jù)庫
        setInterval(() => this.saveDatabase(), 10000);
    }
    
    createTables() {
        // 創(chuàng)建表結(jié)構(gòu)
        this.db.run(`
            CREATE TABLE IF NOT EXISTS tasks (
                id INTEGER PRIMARY KEY,
                title TEXT NOT NULL,
                completed BOOLEAN NOT NULL DEFAULT 0,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        `);
    }
    
    // 執(zhí)行SQL查詢并返回結(jié)果
    executeQuery(query, params = []) {
        if (!this.db) throw new Error('Database not initialized');
        
        try {
            const stmt = this.db.prepare(query);
            stmt.bind(params);
            
            const result = [];
            while (stmt.step()) {
                result.push(stmt.getAsObject());
            }
            
            stmt.free();
            return result;
        } catch (error) {
            console.error('SQL執(zhí)行錯誤:', error, query, params);
            throw error;
        }
    }
    
    // 執(zhí)行更新操作
    executeUpdate(query, params = []) {
        if (!this.db) throw new Error('Database not initialized');
        
        try {
            const stmt = this.db.prepare(query);
            stmt.bind(params);
            stmt.run();
            stmt.free();
            
            // 保存數(shù)據(jù)庫
            this.saveDatabase();
            
            return true;
        } catch (error) {
            console.error('SQL更新錯誤:', error, query, params);
            throw error;
        }
    }
    
    // 保存數(shù)據(jù)庫到localStorage
    saveDatabase() {
        if (!this.db) return;
        
        try {
            const binaryArray = this.db.export();
            const base64 = btoa(String.fromCharCode.apply(null, binaryArray));
            localStorage.setItem('sqlite_db', base64);
        } catch (error) {
            console.error('保存數(shù)據(jù)庫失敗:', error);
        }
    }
    
    // 關(guān)閉數(shù)據(jù)庫
    close() {
        if (this.db) {
            this.db.close();
            this.db = null;
        }
    }
}

export const sqliteService = new SQLiteService();

// src/components/TaskList.js
import React, { useState, useEffect } from 'react';
import { sqliteService } from '../services/sqlite';

const TaskList = () => {
    const [tasks, setTasks] = useState([]);
    const [newTask, setNewTask] = useState('');
    
    useEffect(() => {
        // 初始化數(shù)據(jù)庫
        sqliteService.init().then(() => {
            // 加載任務(wù)
            loadTasks();
        });
        
        return () => {
            // 組件卸載時關(guān)閉數(shù)據(jù)庫
            sqliteService.close();
        };
    }, []);
    
    const loadTasks = () => {
        const result = sqliteService.executeQuery('SELECT * FROM tasks ORDER BY created_at DESC');
        setTasks(result);
    };
    
    const addTask = () => {
        if (!newTask.trim()) return;
        
        sqliteService.executeUpdate(
            'INSERT INTO tasks (title, completed) VALUES (?, ?)',
            [newTask, false]
        );
        
        setNewTask('');
        loadTasks();
    };
    
    const toggleTask = (id, completed) => {
        sqliteService.executeUpdate(
            'UPDATE tasks SET completed = ? WHERE id = ?',
            [!completed, id]
        );
        
        loadTasks();
    };
    
    const deleteTask = (id) => {
        if (confirm('確定要刪除這個任務(wù)嗎？')) {
            sqliteService.executeUpdate('DELETE FROM tasks WHERE id = ?', [id]);
            loadTasks();
        }
    };
    
    return (
        <div className="task-list">
            <h2>任務(wù)列表</h2>
            
            <div className="add-task">
                <input
                    type="text"
                    value={newTask}
                    onChange={(e) => setNewTask(e.target.value)}
                    placeholder="輸入新任務(wù)..."
                />
                <button onClick={addTask}>添加</button>
            </div>
            
            <ul>
                {tasks.map(task => (
                    <li key={task.id}>
                        <input
                            type="checkbox"
                            checked={task.completed}
                            onChange={() => toggleTask(task.id, task.completed)}
                        />
                        <span style={{ textDecoration: task.completed ? 'line-through' : 'none' }}>
                            {task.title}
                        </span>
                        <button onClick={() => deleteTask(task.id)}>刪除</button>
                    </li>
                ))}
            </ul>
        </div>
    );
};

export default TaskList;

// 批量插入數(shù)據(jù)
db.run("BEGIN TRANSACTION");
for (let i = 0; i < 1000; i++) {
    db.run("INSERT INTO users (name, age) VALUES (?, ?)", [`User ${i}`, Math.floor(Math.random() * 100)]);
}
db.run("COMMIT");

// 創(chuàng)建索引
db.run("CREATE INDEX IF NOT EXISTS idx_age ON users (age)");

// 查詢時使用索引
const result = db.exec("SELECT * FROM users WHERE age > 30");

// 釋放不再使用的Statement
stmt.free();

// 定期清理不再需要的數(shù)據(jù)
db.run("DELETE FROM logs WHERE created_at < ?", [oldDate]);

try {
    db.run("INSERT INTO users (name, email) VALUES (?, ?)", ["John", "john@example.com"]);
} catch (error) {
    console.error("SQL執(zhí)行錯誤:", error);
    // 可以進(jìn)行回滾等操作
}