electron sqlite3 封装应用

一、sqlite3 基本应用封装

两个公共方法：

/**
 * 将字典转换为等式结构
 * @param {字典} dict_args 
 * @param {关系符} operator 
 * @returns 
 */
export function dictToEquation(dict_args, operator = "=") {
    let param = []
    for (let key in dict_args) {
        let type = typeof (dict_args[key])
        let sub_set = `${key}${operator}${dict_args[key]}`
        if (operator == "=" && type == "string" && !sqltype) {
            sub_set = `${key}${operator}'${dict_args[key]}'`
        }

        param.push(sub_set)
    }
    return param
}
/**
 * 判断字典是否为空
 * @param {字典} dict_args 
 * @returns 
 */
export function isEmptyDict(dict_args) {
    for (var n in dict_args) {
        return false
    }
    return true;
}

创建数据库：

# electron-build时 __dirname路径会多一层app.asar 所以需要替换掉 保证路径正确
const db = new sqlite_3.Database(path.join(__dirname.replace("app.asar", ""), 'db.db'));

创建表：

db.serialize(function () {
    const sql=
        "CREATE TABLE IF NOT EXISTS 表名(" +
        "id integer primary key," +
        "age integer," +
        "name varchar(50)," +
        "createtime varchar(50))"
    db.run(sql);
});

写入数据：

/**
 * 写入数据
 * @param {表明} table 
 * @param {对应字段的字典结构} param 
 * @returns 
 */
function insertDB(table, param) {
    return new Promise((resolve, reject) => {
        let column = [], complement = [], value = [];
        for (let key in param) {
            column.push(key)
            complement.push("?")
            value.push(param[key])
        }
        let sql = `INSERT INTO ${table}(${column.join(',')}) VALUES(${complement.join(',')})`
        db.serialize(function () {
            db.run(sql, value, (err, res) => {
                if (err != null) {
                    reject(err)
                } else {
                    resolve(res)
                }
            });
        })
    });
}
/**
 * 写入数据 批量
 * @param {*} table 
 * @param {*} params 
 * @returns 
 */
function insertListDB(table, params) {
    // return new Promise((resolve, reject) => {

    // });
    let column = [], complement = [], values = [];
    params.forEach((item, index) => {
        let value = [];
        for (let key in item) {
            if (index == 0) {
                column.push(key)
                complement.push("?")
            }
            value.push(item[key])
        }
        values.push(value);
    });
    let sql = `INSERT INTO ${table}(${column.join(',')}) VALUES(${complement.join(',')})`
    db.serialize(function () {
        var stmt = db.prepare(sql, function (err, res) {
            // console.log(res)
            // if (err) {
            //     reject(err)
            // } else {
            //     resolve(res)
            // }
        })
        values.forEach(item => {
            stmt.run(item)
        })
        stmt.finalize()
    })
    return null
}

查询：

/**
 * 查询数据列表
 * @param {表名} table 
 * @param {查询条件} filters 
 * @param {排序条件} orders 
 * @returns 
 */
function selectListDB(table, filters = {}, orders = {}, tag = "and") {
    return new Promise((resolve, reject) => {
        let sql = `SELECT * FROM ${table}`
        if (!isEmptyDict(filters)) {
            let filter = dictToEquation(filters)
            sql = `${sql} WHERE ${filter.join(' ' + tag + ' ')}`
        }
        if (!isEmptyDict(orders)) {
            let order = dictToEquation(orders, " ")
            sql = `${sql} ORDER BY ${order.join(',')}`
        }
        db.all(sql, (err, rows) => {
            if (err != null) reject(err);
            if (rows && rows.length >= 0) {
                resolve(rows);
            }
            else {
                reject(err);
            }
        });
    });
}
/**
 * 查询数据
 * @param {表名} table 
 * @param {查询条件} filters 
 * @param {排序条件} orders 
 * @returns 
 */
function selectDB(table, filters = {}, tag = "and") {
    return new Promise((resolve, reject) => {
        let sql = `SELECT * FROM ${table}`
        if (!isEmptyDict(filters)) {
            let filter = dictToEquation(filters)
            sql = `${sql} WHERE ${filter.join(' ' + tag + ' ')}`
        }
        console.log(sql)
        db.get(sql, (err, rows) => {
            if (err != null) {
                reject(err);
            }
            else {
                resolve(rows);
            }
        });
    });
}

更改：

/**
 * 更改数据
 * @param {*} table 
 * @param {*} param 
 * @param {*} filters 
 * @param {*} tag 
 * @returns 
 */
function updateDB(table, param, filters, tag = "and") {
    return new Promise((resolve, reject) => {
        if (isEmptyDict(filters)) {
            return reject("条件不能为空")
        }
        if (isEmptyDict(param)) {
            return reject("内容不能为空")
        }
        if (tag != "and" && tag != "or") {
            return reject("tag标识只能是and 或or")
        }
        let sets = dictToEquation(param), filter = dictToEquation(filters);

        let sql = `UPDATE ${table} SET ${sets.join(',')} WHERE ${filter.join(' ' + tag + ' ')}`
        db.serialize(function () {
            db.run(sql, (err, res) => {
                if (err != null) {
                    reject(err);
                }
                else {
                    resolve(res);
                }
            });
        })

    });
}

删除：

/**
 * 删除
 * @param {*} table 
 * @param {*} filters 
 * @param {*} tag 
 * @returns 
 */
function removeDB(table, filters, tag = "and") {
    return new Promise((resolve, reject) => {
        if (isEmptyDict(filters)) {
            return reject("条件不能为空")
        }
        if (tag != "and" && tag != "or") {
            return reject("tag标识只能是and 或or")
        }
        let filter = dictToEquation(filters);
        let sql = `DELETE FROM ${table} WHERE ${filter.join(' ' + tag + ' ')}`
        console.log(sql)
        db.serialize(function () {
            db.run(sql, (err, res) => {
                console.log("delete:", err, res, this.changes, this.lastID);
                resolve(this)
            });
        })
    });
}

sql原始语句执行：

/**
 * 直接执行sql 原始语句
 * @param {*} sql 
 * @param {*} type 
 * @returns 
 */
function executeDB(sql, type="get"){
    return new Promise((resolve, reject) => {
        console.log(sql)
        db.serialize(function () {
            if(type=="get"){
                db.get(sql, (err, res) => {
                    console.log(err)
                    console.log(res)
                    if (err != null) {
                        reject(err);
                    }
                    else {
                        resolve(res);
                    }
                });
            }else{
                db.run(sql, (err, res) => {
                    console.log(err)
                    console.log(res)
                    if (err != null) {
                        reject(err);
                    }
                    else {
                        resolve(res);
                    }
                });
            }
            
        })
    });
}

对外暴露成员：

/**
 * 对外暴露
 * @param {表名} table 
 */
export function tableDB(table) {
    this.table = table
    this.add = async function (param) {
        return insertDB(table, param)
    }
    this.addList = function (params) {
        return insertListDB(table, params)
    }
    this.remove = async function (filter) {
        return removeDB(table, filter)
    }
    this.update = async function (param, filter) {
        return updateDB(table, param, filter)
    }
    this.find = async function (filter = {}, order = {}) {
        return selectListDB(table, filter, order)
    }
    this.get = async function (filter = {}) {
        return selectDB(table, filter)
    }
    this.execute = async function (sql) {
        return executeDB(sql)
    }
}

二、引用方式：

此处可以与上面代码放在同一js ；也可以新创建一个js 文件单独处理表格式

import { tableDB } from "./Sqlite3";

export function table() {
    return new tableDB("表名")
}

增：

table().add(param).then(ree=>{
       console.log("创建：",ree)
 })

删：

table().remove(filter).then(ree=>{
        console.log("删除：",ree)
 })

改：

table().update(param, filter).then(ree=>{
     console.log("更新：",ree)
})

查：

// 单条数据
table().get(filter).then(res => {
    console.log("单条数据：",res )
})

// 多条数据
table().find(filter, order).then(res => {
    console.log("多条数据：",res )
})

源sql：

setting().execute("sql").then(res=>{
     console.log("执行原始sql：",res)
})

posted @ 2021-09-16 10:53 记不起的回忆阅读(1181) 评论(0) 收藏举报

刷新页面返回顶部

记不起的回忆

electron sqlite3 封装应用

公告