H5+APP封装本地数据库sqlite的基础操作
文章目录已封装源码使用案例说明打开数据库是否已打开数据库关闭数据库执行增删改等操作的SQL语句执行查询的SQL语句执行事务创建表根据固定条件获取统计数根据条件获取统计数插入记录批量插入记录更新记录批量更新记录批量更新记录(若该记录不存在则新增)查询全部记录根据条件查询记录删除全部记录根据条件删除记录已封装源码https://gitee.com/zhangxuelian/h5plus-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)
});
});
},
更多推荐
已为社区贡献1条内容
所有评论(0)