深入浅出:uniapp中SQLite数据库的常用操作封装
在移动应用开发过程中,SQLite数据库以其轻量级、跨平台、自包含等特点被广泛使用。uniapp
作为近年来兴起的跨平台开发框架,同样支持对SQLite数据库的操作。本文将详细介绍如何在uniapp
中封装SQLite数据库的常用操作,以便更高效地进行数据存取。
准备工作
在开始之前,请确保你的uniapp
项目已经正确配置并安装了SQLite插件。一般来说,你需要在项目的manifest.json
文件中配置相关插件信息,并在项目中导入对应的模块。
数据库操作封装的重要性
在实际开发中,我们往往需要对数据库进行频繁的增删改查操作。如果每次都写一遍完整的SQL语句并执行,不仅代码冗余,而且维护起来也非常困难。因此,将常用的数据库操作封装成函数,可以大大提高代码的复用性和可维护性。
封装常用操作
1. 打开/创建数据库
在使用SQLite数据库之前,我们需要先打开或创建一个数据库。这个操作可以封装成一个函数,方便后续调用。
function openDatabase(dbName, storeName) { if (!uni.openDatabase) { console.error('当前环境不支持SQLite数据库'); return null; } return uni.openDatabase({ name: dbName, location: 'default', storeName: storeName }); }
2. 执行SQL语句
执行SQL语句是数据库操作的核心。我们可以封装一个通用的执行函数,接收SQL语句和参数作为输入,返回执行结果。
async function executeSQL(db, sql, params = []) { return new Promise((resolve, reject) => { db.transaction((tx) => { tx.executeSql(sql, params, (tx, res) => { resolve(res); }, (tx, err) => { reject(err); }); }); }); }
3. 插入数据
插入数据是常见的数据库操作之一。我们可以封装一个插入函数,接收表名、数据对象作为参数,自动生成SQL语句并执行。
async function insertData(db, tableName, data) { let columns = Object.keys(data).join(','); let placeholders = Object.keys(data).map(() => '?').join(','); let sql = `INSERT INTO ${tableName} (${columns}) VALUES (${placeholders})`; try { let res = await executeSQL(db, sql, Object.values(data)); return res.insertId; } catch (err) { throw err; } }
4. 查询数据
查询数据也是非常常见的操作。我们可以封装一个查询函数,接收表名、查询条件等参数,返回查询结果。
async function queryData(db, tableName, conditions = {}) { let whereClause = Object.entries(conditions).map(([key, value]) => `${key} = ?`).join(' AND '); let sql = `SELECT * FROM ${tableName}`; let params = []; if (whereClause) { sql += ` WHERE ${whereClause}`; params.push(...Object.values(conditions)); } try { let res = await executeSQL(db, sql, params); return res.rows; } catch (err) { throw err; } }
5. 更新数据
更新数据操作同样可以封装成一个函数,接收表名、更新数据和查询条件作为参数。
async function updateData(db, tableName, updateData, conditions) { let setClause = Object.entries(updateData).map(([key, value]) => `${key} = ?`).join(', '); let whereClause = Object.entries(conditions).map(([key, value]) => `${key} = ?`).join(' AND '); let sql = `UPDATE ${tableName} SET ${setClause}`; let params = [...Object.values(updateData)]; if (whereClause) { sql += ` WHERE ${whereClause}`; params.push(...Object.values(conditions)); } try { let res = await executeSQL(db, sql, params); return res.rowsAffected; } catch (err) { throw err; } }
6. 删除数据
删除数据操作与更新数据类似,也需要指定删除条件和表名。
async function deleteData(db, tableName, conditions) { let whereClause = Object.entries(conditions).map(([key, value]) => `${key} = ?`).join(' AND '); let sql = `DELETE FROM ${tableName}`; let params = []; if (whereClause) { sql += ` WHERE ${whereClause}`; params.push(...Object.values(conditions)); } try { let res = await executeSQL(db, sql, params); return res.rowsAffected; } catch (err) { throw err; } }
使用示例
下面是一个简单的使用示例,展示了如何使用上面封装的函数进行数据库操作。
let db = openDatabase('mydb', 'mydbstore'); // 插入数据 insertData(db, 'users', { name: 'Alice', age: 25, email: 'alice@example.com' }).then(id => { console.log(`插入成功,ID为:${id}`); }); // 查询数据 queryData(db, 'users', { name: 'Alice' }).then(users => { console.log('查询结果:', users); }); // 更新数据 updateData(db, 'users', { age: 26 }, { name: 'Alice' }).then(affectedRows => { console.log(`更新成功,影响了${affectedRows}行数据`); }); // 删除数据 deleteData(db, 'users', { name: 'Alice' }).then(affectedRows => { console.log(`删除成功,影响了${affectedRows}行数据`); });
注意事项
- 在实际使用中,请确保对输入数据进行适当的验证和转义,以防止SQL注入攻击。
- 对于复杂的查询和操作,可能需要编写更复杂的SQL语句和函数。本文仅提供了最基本的封装示例。
- 在使用数据库时,请注意及时关闭数据库连接,释放资源。
通过封装常用的数据库操作,我们可以大大提高开发效率和代码质量。希望本文能对你在uniapp
中使用SQLite数据库有所帮助!
案例实践
// 监听数据是否打开 function isOpenDB(name, path = "") { let dbName = name; let dbPath = path || `_doc/${name}.db`; //数据库打开了就返回true,否则返回false let isopen = plus.sqlite.isOpenDatabase({ name: dbName, path: dbPath }) return isopen } // 创建数据库/打开数据库 function openDB(name, path = "") { return new Promise((resolve, reject) => { plus.sqlite.openDatabase({ name: name || 'test', path: path || `_doc/${name}.db`, success: async function(res) { let a = await closeDB(name) resolve('success!') }, fail: async (e) =>{ let a = await closeDB(name) console.log(e) reject(e); } }); }) } // 查询所有数据库表名 function queryDBTable(name, path = "") { return new Promise((resolve, reject) => { let isOpen = isOpenDB(name) if (isOpen) { plus.sqlite.selectSql({ name: name, sql: "select * FROM sqlite_master where type='table'", success:async (e)=> { let a = await closeDB(name) resolve(e); }, fail: async (e) =>{ let a = await closeDB(name) console.log(e) reject(e); } }) } else { plus.sqlite.openDatabase({ name: name || 'test', path: path || `_doc/${name}.db`, success:async (res)=> { plus.sqlite.selectSql({ name: name, sql: "select * FROM sqlite_master where type='table'", success:async (res)=> { let a = await closeDB(name) resolve(res); }, fail: async (e) =>{ let a = await closeDB(name) console.log(e) reject(e); } }) }, fail: async (e) =>{ let a = await closeDB(name) console.log(e) reject(e); } }); } }) } /** * 创建表 * data={dbname,tablename,describe} */ function createTable(data) { // 注意:tabName不能用数字作为表格名的开头 return new Promise((resolve, reject) => { if (data.dbname && data.tablename && data.describe) { let { dbname, tablename, describe } = data let isOpen = isOpenDB(dbname) if (isOpen) { plus.sqlite.executeSql({ name: dbname, // sql: 'create table if not exists dataList("list" INTEGER PRIMARY KEY AUTOINCREMENT,"id" TEXT,"name" TEXT,"gender" TEXT,"avatar" TEXT)', sql: `create table if not exists ${tablename}(${describe})`, success:async (e)=> { let a = await closeDB(dbname) resolve(`创建表${tablename}完成`); }, fail:async (e) =>{ let a = await closeDB(dbname) reject(e); } }) } else { plus.sqlite.openDatabase({ name: dbname, path: data.path || `_doc/${dbname}.db`, success: function(res) { plus.sqlite.executeSql({ name: dbname, // sql: 'create table if not exists dataList("list" INTEGER PRIMARY KEY AUTOINCREMENT,"id" TEXT,"name" TEXT,"gender" TEXT,"avatar" TEXT)', sql: `create table if not exists ${tablename}(${JSON.parse(describe) })`, success:async (e)=> { let a = await closeDB(dbname) resolve(`创建表${tablename}完成`); }, fail:async (e)=>{ let a = await closeDB(dbname) reject(e); } }) }, fail:async (e)=> { let a = await closeDB(dbname) reject('打开数据库失败: ' + JSON.stringify(e)) } }); } } else { reject("参数不满足条件") } }) } // 查询表是否存在 function isTable(name, tabName, path = "") { return new Promise((resolve, reject) => { let isOpen = isOpenDB(name) if (isOpen) { plus.sqlite.selectSql({ name: name, sql: `select count(*) as isTable FROM sqlite_master where type='table' and name='${tabName}'`, success: async (e)=> { let a = await closeDB(name) resolve(e[0].isTable ? true : false); }, fail: async (e) =>{ let a = await closeDB(name) reject(e); } }) } else { plus.sqlite.openDatabase({ name: name, path: path || `_doc/${name}.db`, success: function(res) { plus.sqlite.selectSql({ name: name, sql: `select count(*) as isTable FROM sqlite_master where type='table' and name='${tabName}'`, success: async (e) => { let a = await closeDB(name) resolve(e[0].isTable ? true : false); }, fail: async (e)=> { let a = await closeDB(name) reject(e); } }) }, fail: async function(e) { let a = await closeDB(name) reject('failed: ' + JSON.stringify(e)) } }); } }) } /** * 添加数据 * @param {String} name * @param {String} tabName * @param {Object} obj */ function addSaveData(name, tabName, obj, path = "") { return new Promise((resolve, reject) => { if (obj) { let keys = Object.keys(obj) let keyStr = keys.toString() let valStr = '' keys.forEach((item, index) => { if (keys.length - 1 == index) { valStr += ('"' + obj[item] + '"') } else { valStr += ('"' + obj[item] + '",') } }) // console.log(valStr) let sqlStr = `insert into ${tabName}(${keyStr}) values(${valStr})` console.log(sqlStr) let isOpen = isOpenDB(name) if (isOpen) { plus.sqlite.executeSql({ name: name, sql: sqlStr, success:async(e)=> { let a = await closeDB(name) resolve(e); }, fail: async (e)=> { let a = await closeDB(name) reject(e); } }) } else { console.log(path) plus.sqlite.openDatabase({ name: name, path: path || `_doc/${name}.db`, success: function(res) { plus.sqlite.executeSql({ name: name, sql: sqlStr, success:async (e)=> { let a = await closeDB(name) resolve(e); }, fail:async (e) =>{ let a = await closeDB(name) reject(e); } }) }, fail: async function(e) { let a = await closeDB(name) reject('failed: ' + JSON.stringify(e)) } }); } } else { reject("错误") } }) } /** * 简单查询,selectSql为复杂查询 * @param {String} name * @param {String} tabName * @param {Object} setData * @param {String} byName 排序值 * @param {String} byType 正序倒序 */ function selectDataList(name, tabName, setData, byName, byType, path = "") { let setStr = '' let sql = '' if (JSON.stringify(setData) !== '{}') { let dataKeys = Object.keys(setData) dataKeys.forEach((item, index) => { console.log(setData[item]) setStr += ( `${item}=${JSON.stringify(setData[item])}${dataKeys.length - 1 !== index ? " and " : ""}`) }) sql = `select * from ${tabName} where ${setStr}` } else { sql = `select * from ${tabName}` } if (byName && byType) { // desc asc sql += ` order by ${byName} ${byType}` } console.log(sql) return new Promise((resolve, reject) => { if (tabName !== undefined) { let isOpen = isOpenDB(name) if (isOpen) { plus.sqlite.selectSql({ name: name, sql: sql, success: async (e) => { let a = await closeDB(name) resolve(e); }, fail: async (e)=> { let a = await closeDB(name) reject(e); } }) } else { plus.sqlite.openDatabase({ name: name || 'test', path: path || `_doc/${name}.db`, success: function(res) { plus.sqlite.selectSql({ name: name, sql: sql, success: async (e) => { let a = await closeDB(name) resolve(e); }, fail: async (e)=> { let a = await closeDB(name) console.log(e) reject(e); } }) }, fail: async (e)=> { let a = await closeDB(name) console.log(e) reject(e); } }); } } else { reject("错误") } }); } // 获取数据库分页数据 /** * * @param {*} name * @param {*} tabName * @param {*} num 页码 * @param {*} size 页面大小返回条数 * @param {*} byName 排序主键字段 * @param {*} byType 排序类型 desc倒序 / asc正序 */ async function queryDataList(name, tabName, num, size, byName, byType, path = "") { let count = 0 let sql = '' let numindex = 0 await queryCount(name, tabName).then((resNum) => { count = Math.ceil(resNum[0].num / size) }) if (((num - 1) * size) == 0) { numindex = 0 } else { numindex = ((num - 1) * size) + 1 } sql = `select * from ${tabName}` if (byName && byType) { // desc asc sql += ` order by ${byName} ${byType}` } sql += ` limit ${numindex},${size}` return new Promise((resolve, reject) => { if (count < num - 1) { reject("无数据") } else { let isOpen = isOpenDB(name) if (isOpen) { plus.sqlite.selectSql({ name: name, // sql: "select * from userInfo limit 3 offset 3", sql: sql, success: async (e)=> { let a = await closeDB(name) resolve(e); }, fail: async (e)=> { let a = await closeDB(name) reject(e); } }) } else { plus.sqlite.openDatabase({ name: name, path: path || `_doc/${name}.db`, success: function(res) { plus.sqlite.selectSql({ name: name, // sql: "select * from userInfo limit 3 offset 3", sql: sql, success: async (e)=> { let a = await closeDB(name) resolve(e); }, fail: async (e)=> { let a = await closeDB(name) reject(e); } }) }, fail: async (e)=> { let a = await closeDB(name) reject(e); } }); } } }); } // 查询表数据总条数 function queryCount(name, tabName, path = "") { return new Promise((resolve, reject) => { let isOpen = isOpenDB(name) if (isOpen) { plus.sqlite.selectSql({ name: name, sql: "select count(*) as num from " + tabName, success: async (e)=> { let a = await closeDB(name) resolve(e); }, fail: async (e)=> { let a = await closeDB(name) reject(e); } }) } else { plus.sqlite.openDatabase({ name: name, path: path || `_doc/${name}.db`, success: function(res) { plus.sqlite.selectSql({ name: name, sql: "select count(*) as num from " + tabName, success: async (e)=> { let a = await closeDB(name) resolve(e); }, fail: async (e)=> { let a = await closeDB(name) reject(e); } }) }, fail: async (e)=> { let a = await closeDB(name) reject(e); } }); } }) } // 修改(更新)数据 // 示例:UPDATE COMPANY SET ADDRESS = 'Texas' WHERE ID = 6; // UPDATE 表名 SET 要修改字段 = '修改内容' WHERE 筛选条件 = 6; /** * 简单更新 * @param {*} name 数据库名 * @param {*} tabName 表名 * @param {*} setData 设置值 (修改字段 + 修改内容) * @param {*} setName 筛选条件 * @param {*} setVal 筛选值 * @returns */ function updateSqlData(name, tabName, setData, setName, setVal, path = "") { return new Promise((resolve, reject) => { if (JSON.stringify(setData) !== '{}') { let dataKeys = Object.keys(setData) let setStr = '' dataKeys.forEach((item, index) => { // console.log(item, setData[item]) setStr += ( `${item} = ${JSON.stringify(setData[item])}${dataKeys.length - 1 !== index ? "," : ""}` ) }) console.log(setStr) let isOpen = isOpenDB(name) if (isOpen) { plus.sqlite.executeSql({ name: name, sql: `update ${tabName} set ${setStr} where ${setName} = "${setVal}"`, success: async (e)=> { let a = await closeDB(name) resolve(e); }, fail: async (e)=> { let a = await closeDB(name) reject(e); } }) } else { plus.sqlite.openDatabase({ name: name, path: path || `_doc/${name}.db`, success: function(res) { plus.sqlite.executeSql({ name: name, sql: `update ${tabName} set ${setStr} where ${setName} = "${setVal}"`, success: async (e)=> { let a = await closeDB(name) resolve(e); }, fail: async (e)=> { let a = await closeDB(name) reject(e); } }) }, fail: async (e)=> { let a = await closeDB(name) reject(e); } }); } } else { reject("错误") } }); } //删除表 function deleteTable(dbName, table, path = "") { return new Promise((resolve, reject) => { let isOpen = isOpenDB(dbName) if (isOpen) { plus.sqlite.executeSql({ name: dbName, sql: `DROP TABLE ${table}`, success: async (e)=> { let a = await closeDB(dbName) resolve(`删除表 ${table},成功`) }, fail: async (e)=> { let a = await closeDB(dbName) reject(e); } }); } else { plus.sqlite.openDatabase({ //如果数据库存在则打开,不存在则创建。 name: dbName, path: path || `_doc/${dbName}.db`, success: function(e) { console.log('数据库打开成功'); //执行 增\删\改 操作的SQL语句 plus.sqlite.executeSql({ name: dbName, sql: `DROP TABLE ${table}`, success: async (e)=> { let a = await closeDB(dbName) resolve(`删除表 ${table},成功`) }, fail: async (e)=> { let a = await closeDB(dbName) reject(e); } }); }, fail: async (e)=> { let a = await closeDB(dbName) reject(e); } }); } }) } /** * @param {Object} dbName * @param {Object} dbsql 执行 增\删\改 操作的SQL语句 */ function executeSql(dbName, dbsql, path = "") { console.log(dbsql); return new Promise((resolve, reject) => { // //判断数据库是否打开 let bool = plus.sqlite.isOpenDatabase({ name: dbName, path: path || `_doc/${dbName}.db` }); if (bool) { plus.sqlite.executeSql({ name: dbName, sql: dbsql, success: async (e)=> { let a = await closeDB(dbName) resolve(e) }, fail: async (e)=> { let a = await closeDB(dbName) reject(e); } }); } else { plus.sqlite.openDatabase({ //如果数据库存在则打开,不存在则创建。 name: dbName, path: path || `_doc/${dbName}.db`, success: function(e) { console.log('数据库打开成功'); //执行 增\删\改 操作的SQL语句 console.log(dbsql); plus.sqlite.executeSql({ name: dbName, sql: dbsql, success: async (e)=> { let a = await closeDB(dbName) resolve(e) }, fail: async (e)=> { let a = await closeDB(dbName) reject(e); } }); }, fail: async (e)=> { let a = await closeDB(dbName) reject(e); } }); } }) } /** * @param {Object} dbName * @param {Object} selectNcDuli */ function selectSql(dbName, selectNcDuli, path = "") { return new Promise((resolve, reject) => { let bool = plus.sqlite.isOpenDatabase({ name: dbName, path: path || `_doc/${dbName}.db` }) console.log(bool); if (bool) { plus.sqlite.selectSql({ name: dbName, sql: selectNcDuli, success: async (e)=> { let a = await closeDB(dbName) resolve(e) }, fail: async (e)=> { let a = await closeDB(dbName) reject(e); } }); } else { plus.sqlite.openDatabase({ name: dbName, //这里是数据库的名称 path: path || `_doc/${dbName}.db`, //_doc是相对路径的应用私有文档目录 success: function(e) { //查询数据 plus.sqlite.selectSql({ name: dbName, sql: selectNcDuli, success: async (e)=> { let a = await closeDB(dbName) resolve(e) }, fail: async (e)=> { let a = await closeDB(dbName) reject(e); } }); }, fail: async (e)=> { let a = await closeDB(dbName) reject(e); } }); } }) } //关闭数据库 function closeDB(dbName) { return new Promise((resolve, reject) => { plus.sqlite.closeDatabase({ name: dbName, success: function(e) { console.log('数据库关闭成功'); resolve(true) }, fail: function(e) { console.log('数据库关闭失败'); reject(false) } }); }) } export const db = { openDB, closeDB, isOpenDB, queryDBTable, createTable, isTable, deleteTable, addSaveData, selectDataList, queryCount, updateSqlData, queryDataList, executeSql, selectSql, }