uniapp中本地存储SQLite数据库实现增删改查

SQLite 数据库是一种通用数据库，适用于各种应用程序，包括移动应用，桌面应用，嵌入式系统等，它支持sql标准，因此可以执行常见的sql操作，如插入，查询，更新，和删除数据。SQLite 通常用于小到中等规模的应用，因为他是嵌入书数据库，不需要单独的数据库服务器，而是将数据库存储在应用程序的文件中。这使的他在许多场景下都非常有用。 在uniapp中已经集成了SQLite,我们只需进行二次封装就可以了。学习网址www.runoob.com/sqlite/sqli…

1.首先在manifest.json文件中勾选SQLite（数据库）

2.在根目录下创建js_sdk文件夹，创建JS文件，在文件中对操作数据库的方法进行二次封装。

// 监听数据库是否打开
function isOpenDB(name) {
    let dbName = name;
    let dbPath = `_doc/${name}_record.db`;
//数据库打开了就返回true,否则返回false
let isopen = plus.sqlite.isOpenDatabase({
    name: dbName,
    path: dbPath
})
return isopen
}

// 创建数据库/打开数据库
function openDB(name) {
  return new Promise((resolve, reject) => {
    plus.sqlite.openDatabase({
      name: name || 'testData',
      path: `_doc/${name}_record.db`,
      success: function (e) {
        resolve('openDatabase success!')
      },
      fail: function (e) {
        reject('openDatabase failed: ' + JSON.stringify(e))
      }
    });
  })
}

// 查询所有数据库表名
function queryDBTable(name) {
  return new Promise((resolve, reject) => {
    plus.sqlite.selectSql({
      name: name,
      sql: "select * FROM sqlite_master where type='table'",
      success(e) {
        resolve(e);
      },
      fail(e) {
        console.log(e)
        reject(e);
      }
    })
  })
}

// 创建表
function createTable(name, tabName, tableStructure) {
	// 注意：tabName不能用数字作为表格名的开头
	return new Promise((resolve, reject) => {
		plus.sqlite.executeSql({
			name: name,
			// 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 ${tabName}(${tableStructure})`,
			success(e) {
				resolve(e);
			},
			fail(e) {
				console.log(e)
				reject(e);
			}
		})
	})
}

// 查询表是否存在
function isTable(name, tabName) {
	return new Promise((resolve, reject) => {
		plus.sqlite.selectSql({
			name: name,
			sql: `select count(*) as isTable FROM sqlite_master where type='table' and name='${tabName}'`,
			success(e) {
				resolve(e[0].isTable ? true : false);
			},
			fail(e) {
				console.log(e)
				reject(e);
			}
		})
	})
}

// 添加数据
function addSaveData(name, tabName, obj) {
	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)
		return new Promise((resolve, reject) => {
			plus.sqlite.executeSql({
				name: name,
				sql: sqlStr,
				success(e) {
					resolve(e);
				},
				fail(e) {
					console.log(e)
					reject(e);
				}
			})
		})
	} else {
		return new Promise((resolve, reject) => {
			reject("错误")
		})
	}
}

// 查询数据库数据
function selectDataList(name, tabName, setData, byName, byType) {
	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)
	if (tabName !== undefined) {
		return new Promise((resolve, reject) => {
			plus.sqlite.selectSql({
				name: name,
				sql: sql,
				success(e) {
					resolve(e);
				},
				fail(e) {
					console.log(e)
					reject(e);
				}
			})
		})
	} else {
		return new Promise((resolve, reject) => {
			reject("错误")
		});
	}
}

// 获取数据库分页数据
/**
 * 
 * @param {*} name 
 * @param {*} tabName 
 * @param {*} num 页码
 * @param {*} size 页面大小返回条数
 * @param {*} byName 排序主键字段
 * @param {*} byType 排序类型 desc倒序 / asc正序
 */
async function queryDataList(name, tabName, num, size, byName, byType) {
	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}`
	if (count < num - 1) {
		return new Promise((resolve, reject) => {
			reject("无数据")
		});
	} else {
		return new Promise((resolve, reject) => {
			plus.sqlite.selectSql({
				name: name,
				// sql: "select * from userInfo limit 3 offset 3",
				sql: sql,
				success(e) {
					resolve(e);
				},
				fail(e) {
					reject(e);
				}
			})
		})
	}
}

// 查询表数据总条数
function queryCount(name, tabName) {
	return new Promise((resolve, reject) => {
		plus.sqlite.selectSql({
			name: name,
			sql: "select count(*) as num from " + tabName,
			success(e) {
				resolve(e);
			},
			fail(e) {
				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) {
	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)
		return new Promise((resolve, reject) => {
			plus.sqlite.executeSql({
				name: name,
				sql: `update ${tabName} set ${setStr} where ${setName} = "${setVal}"`,
				success(e) {
					resolve(e);
				},
				fail(e) {
					console.log(e)
					reject(e);
				}
			})
		})
	} else {
		return new Promise((resolve, reject) => {
			reject("错误")
		});
	}
}

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

在页面中调用

<template>
	<view class="content">
		<!-- SQLite数据库 测试 -->
		<view class="db-test">
			<button @click="checkDBstatus">检查数据库状态</button>
			<button @click="openDB">打开数据库</button>
			<button @click="closeDB">关闭数据库</button>
			<button @click="createTable">创建caps_record表</button>
			<!-- <button @click="createPersonnelTable">创建人员信息表</button> -->
			<button @click="queryDBTable">查询数据库所有的表</button>
			<button @click="isTable">查询表是否存在</button>
			<button @click="addSave">存储数据</button>
			<!-- <button @click="addPersonneSave">存储人员信息数据</button> -->
			<button @click="queryData">查询数据</button>
			<!-- <button @click="queryPersonnelData">查询人员信息数据</button> -->
			<button @click="updateSqlData">修改更新数据</button>
			<button @click="queryCount">查询数据总条数</button>
			<button @click="queryDataList">分页获取数据</button>
			<button @click="delData">删除数据</button>
		</view>
		<view class="">
			{{ list }}
		</view>
		<!-- SQLite数据库 -->
	</view>
</template>

<script>
import sql from '../../js_sdk/wt-sqlite/sqlite';
export default {
	data() {
		return {
			// 数据库
			dataBase: 'knowledgeCaps', // 数据库名
			dbTable: 'caps_record', // 数据库表名
			dbPerTable: 'per_table',
			list: []
		};
	},
	async onLoad() {},
	methods: {
		// ************************************ sqlite数据库 *************************************
		// 检查数据库状态
		checkDBstatus() {
			uni.showToast({
				title: sql.isOpenDB(this.dataBase) ? '数据库已打开' : '数据库已关闭',
				icon: 'none'
			});
		},
		// 打开数据库
		openDB() {
			if (sql.isOpenDB(this.dataBase)) return;
			sql.openDB(this.dataBase)
				.then((res) => {
					console.log(res);
					uni.showToast({
						title: `数据库打开成功！${res}`,
						icon: 'none'
					});
				})
				.catch((err) => {
					console.log(err);
				});
		},
		// 关闭数据库
		closeDB() {
			sql.closeDB(this.dataBase)
				.then((res) => {
					console.log(res);
					uni.showToast({
						title: `数据库关闭成功！${res}`,
						icon: 'none'
					});
				})
				.catch((err) => {
					console.log(err);
				});
		},
		// 创建人员信息表
		createPersonnelTable() {
			sql.createTable(
				this.dataBase,
				this.dbPerTable,
				`"personnel_id" INTEGER PRIMARY KEY AUTOINCREMENT, "user_name" TEXT, "work_type" TEXT, "user_age" TEXT, "content" TEXT`
			).then((res) => {
				uni.showToast({
					title: `创建人员信息表成功！${res}`,
					icon: 'none'
				});
				console.log(res);
			});
		},
		// 添加人员信息
		addPersonneSave() {
			sql.addSaveData(this.dataBase, this.dbPerTable, {
				user_name: '张三',
				work_type: '软件工程师',
				user_age: '18',
				content: '人员信息'
			}).then((res) => {
				uni.showToast({
					title: `储存人员信息成功！${res}`,
					icon: 'none'
				});
				console.log(res);
			});
		},
		// 查询人员信息数据
		queryPersonnelData() {
			sql.selectDataList(this.dataBase, this.dbPerTable, {}).then((res) => {
				console.log(res);
			});
		},
		// 创建表
		createTable() {
			sql.createTable(this.dataBase, this.dbTable, `"chat_id" INTEGER PRIMARY KEY AUTOINCREMENT, "feed_type" TEXT, "role_type" TEXT, "user_image" TEXT, "content" TEXT`).then(
				(res) => {
					console.log(res);
				}
			);
		},
		// 查询数据库所有表
		queryDBTable() {
			sql.queryDBTable(this.dataBase).then((res) => {
				console.log(res);
			});
		},
		// 查询表是否存在
		isTable() {
			sql.isTable(this.dataBase, this.dbTable).then((res) => {
				console.log(res);
			});
		},
		// 添加、存储数据
		addSave() {
			sql.addSaveData(this.dataBase, this.dbTable, {
				feed_type: '333',
				role_type: '3',
				user_image: '',
				content: '测试数据hahaha，hello Word！你好 世界！'
			}).then(() => {
				uni.showToast({
					title: `储存数据成功`,
					icon: 'none'
				});
			});
		},
		// 查询数据
		queryData() {
			sql.selectDataList(this.dataBase, this.dbTable, {}).then((res) => {
				this.list = res;
				console.log(res);
			});
		},
		// 修改更新数据
		updateSqlData() {
			sql.updateSqlData(
				this.dataBase,
				this.dbTable,
				{
					feed_type: '333',
					content: '这是修改成功的值789！！！嘻嘻嘻哈哈哈'
				},
				'chat_id',
				4
			).then((res) => {
				console.log(res);
				uni.showToast({
					title: `修改数据成功`,
					icon: 'none'
				});
			});
		},
		// 删除数据
		delData() {
			sql.deleteInformationType(this.dataBase, this.dbTable, {
				chat_id: 3
			}).then((res) => {
				uni.showToast({
					title: `删除数据成功`,
					icon: 'none'
				});
				console.log(res);
			});
		},
		// 查询表数据总条数
		queryCount() {
			sql.queryCount(this.dataBase, this.dbTable).then((res) => {
				console.log(res);
				uni.showToast({
					title: `表数据总数为${res[0].num}`,
					icon: 'none'
				});
			});
		},
		// 分页获取数据
		queryDataList() {
			sql.queryDataList(this.dataBase, this.dbTable, 1, 5, 'chat_id', 'desc').then((res) => {
				console.log(res);
			});
		}
	}
};
</script>

<style>
.content button {
	margin-bottom: 20rpx;
}

.input_box {
	width: 690rpx;
	height: 80rpx;
	font-size: 40rpx;
	line-height: 40rpx;
	margin: 10rpx auto;
	background-color: pink;
}

.table_box {
	width: 690rpx;
	margin: 10rpx auto;
	text-align: center;
}
</style>