H5+APP封装本地数据库sqlite的基础操作

已封装源码

https://gitee.com/zhangxuelian/h5plus-sqlite/blob/master/src/sqlite.js

使用案例

https://gitee.com/zhangxuelian/h5plus-sqlite/blob/master/src/app.vue

说明

打开数据库

/**
* 打开数据库
* @param {*} dbName 数据库名称
* @param {*} dbPath 数据库路径
* @returns Promise
*/
openDB({ dbName, dbPath }) {
   dbName = dbName || this.dbName;
   dbPath = dbPath || this.dbPath;
   return new Promise((resolve, reject) => {
       if (this.isPlus()) {
           plus.sqlite.openDatabase({
               name: dbName,
               path: dbPath,
               success: function (e) {
                   resolve('openDatabase success!');
               },
               fail: function (e) {
                   reject('openDatabase failed: ' + JSON.stringify(e));
               }
           });
       } else {
           reject("当前不是5+APP，无法操作本地数据库！");
       }
   })
},

是否已打开数据库

/**
 * 是否已打开数据库
 * @param {*} dbName 数据库名称
 * @param {*} dbPath 数据库路径
 * @returns Boolean
 */
isOpenDB({ dbName, dbPath }) {
    dbName = dbName || this.dbName;
    dbPath = dbPath || this.dbPath;
    if (this.isPlus()) {
        return plus.sqlite.isOpenDatabase({
            name: dbName,
            path: dbPath
        });
    } else {
        console.log("当前不是5+APP，无法操作本地数据库！");
        return false;
    }
},

关闭数据库

/**
 * 关闭数据库
 * @param {*} dbName 数据库名称
 * @returns Promise
 */
closeDB(dbName) {
    dbName = dbName || this.dbName;
    return new Promise((resolve, reject) => {
        plus.sqlite.closeDatabase({
            name: dbName,
            success: function (e) {
                resolve('closeDatabase success!');
            },
            fail: function (e) {
                reject('closeDatabase failed: ' + JSON.stringify(e));
            }
        });
    });
},

执行增删改等操作的SQL语句

/**
 * 执行增删改等操作的SQL语句
 * @param {*} dbName 数据库名称
 * @param {*} sql 需要执行的SQL语句 注意：Android平台不支持SQL语句中使用“;”分割多条命令，要运行多条命令请使用字符串数组参数。
 * @returns Promise
 */
executeSQL({ dbName, sql }) {
    dbName = dbName || this.dbName;
    return new Promise((resolve, reject) => {
        plus.sqlite.executeSql({
            name: dbName,
            sql: sql,
            success: function (e) {
                resolve('executeSql success!');
            },
            fail: function (e) {
                reject('executeSql failed: ' + JSON.stringify(e));
            }
        });
    });
},

执行查询的SQL语句

/**
 * 执行查询的SQL语句
 * @param {*} dbName 数据库名称
 * @param {*} sql 需要查询的SQL语句
 * @returns Promise
 */
selectSQL({ dbName, sql }) {
    dbName = dbName || this.dbName;
    return new Promise((resolve, reject) => {
        plus.sqlite.selectSql({
            name: dbName,
            sql: sql,
            success: function (data) {
                resolve(data);
            },
            fail: function (e) {
                reject('selectSql failed: ' + JSON.stringify(e));
            }
        });
    });
}

执行事务

/**
 * 执行事务
 * @param {*} dbName 数据库名称
 * @param {*} operation begin（开始事务）、commit（提交）、rollback（回滚）
 * 事务控制命令只与 DML 命令 INSERT、UPDATE 和 DELETE 一起使用。他们不能在创建表或删除表时使用，因为这些操作在数据库中是自动提交的。
 * @returns Promise
 */
transactionDB({ dbName, operation }) {
    dbName = dbName || this.dbName;
    return new Promise((resolve, reject) => {
        plus.sqlite.transaction({
            name: dbName,
            operation,
            success: function (e) {
                resolve('transaction success!');
            },
            fail: function (e) {
                reject('transaction failed: ' + JSON.stringify(e));
            }
        });
    });
},

创建表

/**
 * 创建表
 * @param {*} dbName 数据库名称
 * @param {*} tbName 表名
 * @param {*} fieldList 字段列表，例如：[{columnName:'id',dataType:'integer',constraints:['not null','primary key']},{columnName:'text',dataType:'varchar(100)'}]
 * @param {*} fieldList[0].columnName 字段名
 * @param {*} fieldList[0].dataType 数据类型
 * @param {*} fieldList[0].constraints 约束
 * @returns Promise
 */
createTb({ dbName, tbName, fieldList }) {
    dbName = dbName || this.dbName;
    let defineTableList = [];
    fieldList.forEach(item => {
        let column = `${item.columnName} ${item.dataType}`;
        if (item.constraints && item.constraints.length) {
            column += ` ${item.constraints.join(" ")}`;
        }
        defineTableList.push(column);
    });
    let sql = `create table if not exists ${tbName}(${defineTableList.join(",")})`;
    return this.executeSQL({ dbName, sql });
},

根据固定条件获取统计数

/**
 * 根据固定条件获取统计数
 * @param {*} dbName 数据库名称
 * @param {*} tbName 表名
 * @param {*} key 字段名
 * @param {*} value 值
 * @returns Promise
 */
getCountByKey({ dbName, tbName, key, value }) {
    return new Promise((reslove, reject) => {
        let sql = `select count(*) as count from ${tbName}`;
        if (key && value) {
            sql += ` where ${key} = ${JSON.stringify(value)}`;
        }
        this.selectSQL({ dbName, sql }).then(ret => {
            if (ret.length && ret[0] && ret[0].count) {
                reslove(ret[0].count);
            } else {
                reslove(0);
            }
        }, err => {
            reject(err);
        });
    });
},

根据条件获取统计数

/**
 * 根据条件获取统计数
 * @param {*} dbName 数据库名称
 * @param {*} tbName 表名
 * @param {*} condition 条件：name = 'zxl' and loginTime > 10 and sal in (5000,3000,1500)
 * @returns Promise
 */
getCountByCondition({ dbName, tbName, condition }) {
    return new Promise((reslove, reject) => {
        let sql = `select count(*) as count from ${tbName}`;
        if (condition) {
            sql += ` where ${condition}`;
        }
        this.selectSQL({ dbName, sql }).then(ret => {
            if (ret.length && ret[0] && ret[0].count) {
                reslove(ret[0].count);
            } else {
                reslove(0);
            }
        }, err => {
            reject(err);
        });
    });
},

插入记录

/**
 * 插入记录
 * @param {*} dbName 数据库名称
 * @param {*} tbName 表名
 * @param {*} fieldList 字段列表，例如：{id:1,text:'abc'}
 * @returns Promise
 */
insert({ dbName, tbName, record }) {
    return new Promise((resolve, reject) => {
        dbName = dbName || this.dbName;
        let columns = [], values = [];
        for (let i in record) {
            columns.push(i);
            values.push(JSON.stringify(record[i]));
        }
        let sql = `insert into ${tbName} (${columns.join(",")}) values (${values.join(",")})`;
        this.executeSQL({ dbName, sql }).then(ret => {
            resolve(ret);
        }, err => {
            reject(err);
        });
    })
},

批量插入记录

/**
 * 批量插入记录
 * @param {*} dbName 数据库名称
 * @param {*} tbName 表名
 * @param {*} fieldList 字段列表，例如：[{id:1,text:'abc'},{id:2,text:'def'}]
 * @returns Promise
 */
insertBatch({ dbName, tbName, fieldList }) {
    return new Promise((resolve, reject) => {
        dbName = dbName || this.dbName;
        let len = fieldList.length, result = { success: [], error: [] };
        let insertRecord = (i) => {
            this.insert({ dbName, tbName, record: fieldList[i] }).then(ret => {
                result.success.push(ret);
                if (i < len - 1) {
                    insertRecord(i + 1);
                } else {
                    resolve(result);
                }
            }, err => {
                result.error.push(err);
                if (i < len - 1) {
                    insertRecord(i + 1);
                } else {
                    resolve(result);
                }
            });
        }
        insertRecord(0);
    });
},

更新记录

/**
 * 更新记录
 * @param {*} dbName 数据库名称
 * @param {*} tbName 表名
 * @param {*} fieldKey 字段主键，默认id
 * @param {*} record 字段列表，例如：{id:1,text:'abc'}
 * @returns Promise
 */
update({ dbName, tbName, fieldKey, record }) {
    return new Promise((reslove, reject) => {
        dbName = dbName || this.dbName;
        fieldKey = fieldKey || "id";
        let recordArr = [];
        for (let i in record) {
            recordArr.push(`${i} = ${JSON.stringify(record[i])}`);
        }
        let sql = `update ${tbName} set ${recordArr.join(",")} where ${fieldKey} = ${JSON.stringify(record[fieldKey])}`;
        this.executeSQL({ dbName, sql }).then(ret => {
            reslove(ret);
        }, err => {
            reject(err);
        });
    });
},

批量更新记录

/**
 * 批量更新记录
 * @param {*} dbName 数据库名称
 * @param {*} tbName 表名
 * @param {*} fieldKey 字段主键，默认id
 * @param {*} fieldList 字段列表，例如：[{id:1,text:'abc'},{id:2,text:'def'}]
 * @returns Promise
 */
updateBatch({ dbName, tbName, fieldKey, fieldList }) {
    return new Promise((resolve, reject) => {
        dbName = dbName || this.dbName;
        fieldKey = fieldKey || "id";
        let len = fieldList.length, result = { success: [], error: [] };
        let updateRecord = (i) => {
            this.update({ dbName, tbName, fieldKey, record: fieldList[i] }).then(ret => {
                result.success.push(ret);
                if (i < len - 1) {
                    updateRecord(i + 1);
                } else {
                    resolve(result);
                }
            }, err => {
                result.error.push(err);
                if (i < len - 1) {
                    updateRecord(i + 1);
                } else {
                    resolve(result);
                }
            });
        }
        updateRecord(0);
    });
},

批量更新记录(若该记录不存在则新增)

/**
 * 批量更新记录(若该记录不存在则新增)
 * @param {*} dbName 数据库名称
 * @param {*} tbName 表名
 * @param {*} fieldKey 字段主键，默认id
 * @param {*} fieldList 字段列表，例如：[{id:1,text:'abc'},{id:2,text:'def'}]
 * @returns Promise
 */
dealUpdateBatch({ dbName, tbName, fieldKey, fieldList }) {
    return new Promise((resolve, reject) => {
        dbName = dbName || this.dbName;
        fieldKey = fieldKey || "id";
        let len = fieldList.length, result = { success: [], error: [] };
        let updateRecord = (i) => {
            this.getCountByKey({ dbName, tbName, key: fieldKey, value: fieldList[i][fieldKey] }).then(count => {
                if (count) {
                    // 已存在并更新
                    this.update({ dbName, tbName, fieldKey, record: fieldList[i] }).then(ret => {
                        result.success.push(ret);
                        if (i < len - 1) {
                            updateRecord(i + 1);
                        } else {
                            resolve(result);
                        }
                    }, err => {
                        result.error.push(err);
                        if (i < len - 1) {
                            updateRecord(i + 1);
                        } else {
                            resolve(result);
                        }
                    });
                } else {
                    // 不存并插入
                    this.insert({ dbName, tbName, record: fieldList[i] }).then(ret => {
                        result.success.push(ret);
                        if (i < len - 1) {
                            updateRecord(i + 1);
                        } else {
                            resolve(result);
                        }
                    }, err => {
                        result.error.push(err);
                        if (i < len - 1) {
                            updateRecord(i + 1);
                        } else {
                            resolve(result);
                        }
                    });
                }
            })

        }
        updateRecord(0);
    });
},

查询全部记录

/**
 * 查询全部记录
 * @param {*} dbName 数据库名称
 * @param {*} tbName 表名
 */
selectAll({ dbName, tbName }) {
    return new Promise((resolve, reject) => {
        dbName = dbName || this.dbName;
        this.selectSQL({
            dbName,
            sql: `select * from ${tbName}`
        }).then(ret => {
            resolve(ret);
        }, err => {
            reject(err)
        });
    });
},

根据条件查询记录

/**
 * 根据条件查询记录
 * @param {*} dbName 数据库名称
 * @param {*} tbName 表名
 * @param {*} condition 条件,例如: {columns:['id','name'],where: "id=1",orderBy:"id asc"}
 */
selectByCondition({ dbName, tbName, condition }) {
    return new Promise((resolve, reject) => {
        dbName = dbName || this.dbName;
        let sql = `select `;
        if (condition.columns) {
            sql += `${condition.columns.join(",")}`;
        } else {
            sql += `*`
        }
        sql += ` from ${tbName}`;
        if (condition.where) {
            sql += ` where ${condition.where}`;
        }
        if (condition.orderBy) {
            sql += ` order by ${condition.orderBy}`
        }
        this.selectSQL({
            dbName,
            sql
        }).then(ret => {
            resolve(ret);
        }, err => {
            reject(err)
        });
    });
},

删除全部记录

/**
 * 删除全部记录
 * @param {*} dbName 数据库名称
 * @param {*} tbName 表名
 */
deleteAll({ dbName, tbName }) {
    return new Promise((resolve, reject) => {
        dbName = dbName || this.dbName;
        this.executeSQL({
            dbName,
            sql: `delete from ${tbName}`
        }).then(ret => {
            resolve(ret);
        }, err => {
            reject(err)
        });
    });
},

根据条件删除记录

/**
 * 根据条件删除记录
 * @param {*} dbName 数据库名称
 * @param {*} tbName 表名
 * @param {*} condition where条件,例如: "id=1"
 */
deleteByCondition({ dbName, tbName, condition }) {
    return new Promise((resolve, reject) => {
        dbName = dbName || this.dbName;
        let sql = `delete from ${tbName} `;
        if (condition) {
            sql += `where ${condition}`;
        }
        this.executeSQL({
            dbName,
            sql
        }).then(ret => {
            resolve(ret);
        }, err => {
            reject(err)
        });
    });
},