electron使用sqlite

使用sql.js和sqlite3两种插件操作sqlite数据库的工具方法。

新建sqlitedb.js文件，内容如下：

export default {
    db: {},
    prod:''
}

公共基础方法：

    initDb(){
        sqlitedb.db=this.connectDb(tableName)
    },
    //适配db实例
    dbInstance(){
        if(!sqlitedb.db){
            this.initDb()
        }
        return sqlitedb.db
    },
    dbAdapter(dbFilePath){
        let writeFileFlag=window.localStorage.getItem('writeFileFlag')
        console.log('writeFileFlag',writeFileFlag)
        if(writeFileFlag!='1'){
            fileUtil.writeFileHandle(dbFilePath)
        }
    },
    connectDb(table) {
        if(process.env.NODE_ENV === 'production'){
            // 获取安装目录（也就是文件安装目录中exe文件的目录）
            let homeDir =  path.dirname(app.getPath('exe'))
            sqlitedb.prod=homeDir+"\\"+table+".db"
            this.dbAdapter(sqlitedb.prod)
        }else{
            sqlitedb.prod="static/db/"+table+".db"
        }
        let dbt=null
        if(packageName=='sqljs'){
            dbt=sqljsUtil.dbInit(sqlitedb.prod)
        }else{
            dbt=sqlite3Util.dbInit(sqlitedb.prod)
        }
        return dbt
    },

 fileUtil内容：

    writeFileHandle(dbFilePath){
        if(!fs.existsSync(dbFilePath)){
            window.localStorage.setItem('writeFileFlag','1')
            fs.closeSync(fs.openSync(dbFilePath, 'w'))
        }
    },

sql.js封装工具

import fileUtil from "../file/fileUtil";
import fs from "fs";
import sqlitedb from "./sqlitedb";
const sqlite3=require("sql.js")

export default {
    async dbInit(prod) {
        try {
            let fileBuffer = fs.readFileSync(prod);
            await sqlite3().then(SQL => {
                sqlitedb.db = new SQL.Database(fileBuffer);
            })
        }catch (e) {
            fileUtil.writeFileHandle(prod)
            let fileBuffer = fs.readFileSync(prod);
            await sqlite3().then(SQL => {
                sqlitedb.db = new SQL.Database(fileBuffer);
            })
        }
        return sqlitedb.db
    },
    createTable(dbInstance,table,field,callback) {
        let sql = 'create table if not exists '+table+' ('+field+')';
        console.log(sql)
        dbInstance.run(sql);
        this.commitDb(dbInstance)
        callback()
    },
    inertData(dbInstance,table,field,data) {
        for(let i=0;i<data.length;i++){
            let sql = 'insert into '+table+' ('+field+') values ('+data[i]+')';
            this.inertDataSql(dbInstance,sql)
        }
        this.commitDb(dbInstance)
    },
    inertDataSql(dbInstance,sql) {
        console.log(sql)
        dbInstance.run(sql);
    },
    updateDataSql(dbInstance,sql,callback) {
        console.log(sql)
        dbInstance.run(sql);
        this.commitDb(dbInstance)
        callback()
    },
    del(dbInstance,table,name,callback) {
        let sql = ''
        if(name){
            sql='delete from '+table+' where name = '+name;
        }else{
            sql='delete from '+table;
        }
        dbInstance.run(sql)
        this.commitDb(dbInstance)
        callback()
    },
    delById(dbInstance,table,id,callback) {
        let sql ='delete from '+table+' where id = '+id;
        console.log(sql)
        dbInstance.run(sql);
        this.commitDb(dbInstance)
        callback()
    },
    clearTable(dbInstance,table,callback) {
        let sql ='delete from '+table;
        console.log(sql)
        dbInstance.run(sql);
        this.commitDb(dbInstance)
        callback()
    },
    dropTable(dbInstance,table,callback) {
        let sql = 'DROP TABLE IF EXISTS '+table
        dbInstance.run(sql)
        this.commitDb(dbInstance)
        callback()
    },
    ifTableExists(dbInstance,table,callback) {
        let sql = 'select name from '+table
        let res=dbInstance.exec(sql)
        callback(res)
    },

    queryList(dbInstance,table,key,param,callback) {
        let sql = 'select * from '+table
        if(param){
            sql+=" where "+key+" like '%"+param+"%'"
        }
        console.log(sql)
        let res=dbInstance.exec(sql)
        callback(res)
    },
    queryListPage(dbInstance,table,page,param,callback) {
        let sql = 'select * from '+table
        if(param){
            sql+=" where name like '%"+param+"%'"
        }
        if(page&&page.size!=0){
            sql+=' LIMIT '+page.size +' OFFSET '+ ((page.num-1) * page.size)
        }
        console.log(sql)
        let res=dbInstance.exec(sql)
        callback(res)
    },
    queryCount(dbInstance,table,param,callback) {
        let sql = 'select count(1) as total from '+table
        if(param){
            sql+=" where name like '%"+param+"%'"
        }
        console.log(sql)
        let res=dbInstance.exec(sql)
        callback(res.length||0)
    },
    closeDb(dbInstance){
        //使用全局引用，打开不关闭
        //  dbInstance.close();
    },
    getById(dbInstance,table,id,callback) {
        let sql = 'select * from '+table+' where id='+id
        let res=dbInstance.exec(sql)
        callback(res)
    },
    ifTableCreate(dbInstance,table,field,callback) {
        this.createTable(dbInstance, table, field, ()=>{
            callback()
        })
    },
    queryListBySql(dbInstance,sql,callback){
        let res=dbInstance.exec(sql)
        callback(res)
    },
    //需要提交到db中
    commitDb(dbInstance){
        let data = dbInstance.export();
        let buffer = Buffer.from(data, 'binary');
        fs.writeFileSync(sqlitedb.prod, buffer);
    }

}

sqlite3封装工具

import fileUtil from "../file/fileUtil";
const sqlite3 = require("sqlite3").verbose();

export default {
    dbInit(prod) {
        let dbt=null
        try {
            dbt=new sqlite3.Database(prod)
        }catch (e) {
            fileUtil.writeFileHandle(prod)
            dbt=new sqlite3.Database(prod)
        }
        return dbt
    },
    async createTable(dbInstance,table,field,callback) {
        let sql = 'create table if not exists '+table+' ('+field+')';
        console.log(sql)
        await dbInstance.all(sql,(err, rows)=>{
            if(err){
                return err
            }
            callback()
        });
    },
    inertData(dbInstance,table,field,data) {
        for(let i=0;i<data.length;i++){
            let sql = 'insert into '+table+' ('+field+') values ('+data[i]+')';
            this.inertDataSql(dbInstance,sql,()=>{})
        }
    },
    async inertDataSql(dbInstance,sql,callback) {
        console.log(sql)
        await dbInstance.all(sql,(err, rows)=>{
            if(err){
                console.log(err)
                return
            }
            callback()
        });
    },
    async updateDataSql(dbInstance,sql,callback) {
        console.log(sql)
        await dbInstance.all(sql,(err, rows)=>{
            callback(err)
        });
    },
    async del(dbInstance,table,name,callback) {
        let sql = ''
        if(name){
            sql='delete from '+table+' where name = '+name;
        }else{
            sql='delete from '+table;
        }
        await dbInstance.all(sql,(err, rows)=>{
            if(err){
                console.log(err)
                return err
            }
            callback()
        });
    },
    async delById(dbInstance,table,id,callback) {
        let sql ='delete from '+table+' where id = '+id;
        console.log(sql)
        await dbInstance.all(sql,(err, rows)=>{
            if(err){
                return err
            }
            callback()
        });
    },
    async clearTable(dbInstance,table,callback) {
        let sql ='delete from '+table;
        console.log(sql)
        await dbInstance.all(sql,(err, rows)=>{
            if(err){
                return err
            }
            callback()
        });
    },
    async dropTable(dbInstance,table,callback) {
        let sql = 'DROP TABLE IF EXISTS '+table
        await dbInstance.all(sql,(err, rows)=>{
            if(err){
                return err
            }
            callback()
        });
    },
    async ifTableExists(dbInstance,table,callback) {
        let sql = 'select name from '+table
        await dbInstance.all(sql, (err, rows) => {
            if(err){
                return
            }
            callback(rows)
        });
    },

    async queryList(dbInstance,table,key,param,callback) {
        let sql = 'select * from '+table
        if(param){
            sql+=" where "+key+" like '%"+param+"%'"
        }
        console.log(sql)
        await dbInstance.all(sql, (err, rows) => {
            if(err){
                callback([])
                return
            }
            callback(rows)
        });
    },
    async queryListPage(dbInstance,table,page,param,callback) {
        let sql = 'select * from '+table
        if(param){
            sql+=" where name like '%"+param+"%'"
        }
        if(page&&page.size!=0){
            sql+=' LIMIT '+page.size +' OFFSET '+ ((page.num-1) * page.size)
        }
        console.log(sql)
        await dbInstance.all(sql, (err, rows) => {
            if(err){
                console.log(err)
                return
            }
            callback(rows)
        });
    },
    async queryCount(dbInstance,table,param,callback) {
        let sql = 'select count(1) as total from '+table
        if(param){
            sql+=" where name like '%"+param+"%'"
        }
        console.log(sql)
        await dbInstance.all(sql, (err, total) => {
            if(err){
                console.log(err)
                return
            }
            callback(total[0].total||0)
        });
    },
    async closeDb(dbInstance){
        //使用全局引用，打开不关闭
        // await dbInstance.close();
    },
    async getById(dbInstance,table,id,callback) {
        let sql = 'select * from '+table+' where id='+id
        await dbInstance.all(sql, (err, rows) => {
            if(err){
                console.log(err)
                return
            }
            callback(rows)
        });
    },
    ifTableCreate(dbInstance,table,field,callback) {
        this.createTable(dbInstance, table, field, ()=>{
            callback()
        })
    },
    async queryListBySql(dbInstance,sql,callback){
        await dbInstance.all(sql, (err, rows) => {
            if(err){
                console.log(err)
                return
            }
            callback(rows)
        });
    }
}

两个插件都可以很好的操作sqlite，不同在于使用electron-builder编译打包时，如果项目中依赖了sqlite3插件，他会主动再去下载sqlite3的5.1.7版本，但是会遇到下载sqlite3-v5.1.7-napi-v36-win32-x64.tar.gz的包下载不下来，因为是sqlite3-v5.1.7-napi-v3-win32-x64.tar.gz和sqlite3-v5.1.7-napi-v6-win32-x64.tar.gz两种包，这里没有找到太好的解决方法，所以可以使用sql.js来替代sqlite3；也可以使用electron-packager打包插件来替代electron-builder来打包，这样就可以使用sqlite3了。