uniapp中sqlite数据库常用操作的简单封装

参考HTML5联盟

深入浅出：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}行数据`);
});

注意事项

1. 在实际使用中，请确保对输入数据进行适当的验证和转义，以防止SQL注入攻击。
2. 对于复杂的查询和操作，可能需要编写更复杂的SQL语句和函数。本文仅提供了最基本的封装示例。
3. 在使用数据库时，请注意及时关闭数据库连接，释放资源。

通过封装常用的数据库操作，我们可以大大提高开发效率和代码质量。希望本文能对你在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,
}