最近一段时间在用uniapp写app，在项目中有聊天服务，打算把聊天记录存储到sqlite数据库中，由于习惯了链式调用，决定封装下sqlite的操作，花了几个小时，应该能完成简单的功能了，能力有限，有bug当我没发，实在不行直接写sql。
执行如下语句
DB('tb_user')
.insert({name:'张三',age:22,sex:1,birthday:'2000-01-01'})
.do()
DB('tb_user').select().where(`name='张三' or sex = 1`).do()
DB('tb_user')
.update({name:'李四',age:22})
.equal({sex:1})
.like({name:'张%'})
.do()
DB('tb_user').select().equal({name:'李四'}).order({id:'asc',name:'desc'}).do()
DB('tb_user').delete().equal({id:1}).do()
DB('tb_user').sql(`select * from tb_user`).do()
结果如下
INSERT INTO tb_user(name,age,sex,birthday) VALUES('张三',22,1,'2000-01-01')
SELECT * FROM tb_user where name='张三' or sex = 1
UPDATE tb_user SET name='李四',age=22 where sex=1 and name like '张%'
SELECT * FROM tb_user where name='李四' order by id asc,name desc
DELETE FROM tb_user where id=1
select * from tb_user
export default function useSqlite(dbName) {
//Sqlite数据库常用类型
const SqliteType = {
Number: 'INTEGER',
Real: 'REAL',
String: 'TEXT',
Date: 'DATETIME'
}
/**
* 如果数据库存在则打开，不存在则创建
*/
const OpenDB = () => {
// #ifdef APP-PLUS
return new Promise((resolve, reject) => {
plus.sqlite.openDatabase({
name: dbName, //数据库名称
path: `_doc/${dbName}.db`, //数据库地址
success(e) {
resolve(e);
},
fail(e) {
reject(e);
}
})
})
// #endif
}
/**
* 关闭数据库
*/
const CloseDB = () => {
// #ifdef APP-PLUS
return new Promise((resolve, reject) => {
plus.sqlite.closeDatabase({
name: dbName,
success(e) {
resolve(e)
},
fail(e) {
reject(e)
}
})
})
// #endif
}
/**
* 执行原生的execute语句
* @param {String} sql sql语句
*/
const SqlExecute = async (sql) => {
console.log(sql)
// #ifdef APP-PLUS
return new Promise((resolve, reject) => {
plus.sqlite.executeSql({
name: dbName,
sql: sql,
success(e) {
resolve(e)
},
fail(e) {
reject(e)
}
})
})
// #endif
}
/**
* 执行原生的select语句
* @param {String} sql sql语句
*/
const SqlSelect = async (sql) => {
console.log(sql)
// #ifdef APP-PLUS
return new Promise((resolve, reject) => {
plus.sqlite.selectSql({
name: dbName,
sql: sql,
success(e) {
resolve(e)
},
fail(e) {
reject(e)
}
})
})
// #endif
}
/**
* 数据库新增表
* @param {String} tbName 表名
* @param {Object} data 操作对象
*/
const CreateTable = (tbName, data) => {
data = Object.entries(data).map(item => {
return item[0] + ' ' + item[1]
}).join(',')
const sql = `CREATE TABLE IF NOT EXISTS ${tbName}(id INTEGER PRIMARY KEY AUTOINCREMENT,${data})`
return SqlExecute(sql)
}
/**
* 数据库删表
* @param {String} tbName 表名
*/
const DropTable = (tbName) => {
const sql = `DROP TABLE ${tbName}`
return SqlExecute(sql)
}
/**
* 处理对象，将对象转化为两个数组，分别为keys，values
*/
const DealObject = (obj, convert = true) => {
const keys = []
const values = []
Object.entries(obj).forEach(item => {
keys.push(item[0])
if (typeof(item[1]) == 'string' && convert) {
values.push(`'${item[1]}'`)
} else {
values.push(item[1])
}
})
return {
keys,
values
}
}
//sqlite操作类
class SqliteOperation {
#StepEnum = {
Init: 0, //初始化
Operation: 1, //操作方式
Where: 2, //条件
Order: 3, //排序
Define: 4 //自定义SQL语句
}
#tbName = '' //表名
#step = this.#StepEnum.Init //步骤，用于对执行方法进行先后排序
#operation = '' //操作方式，增/删/查/改
statement = '' //sql语句
constructor(tbName) {
this.#tbName = tbName
}
/**
* 获取操作对象
* @param {String} tbName 数据库表
*/
static getInstance(tbName) {
if (tbName == '') {
console.error('数据库表名不能为空！');
return null
}
return new SqliteOperation(tbName)
}
////////操作数据，示例如下////////////
// 新增(对象) 必传 => 要新增列名与对应的值 {name:'张三'}
// 删除（无需传）
// 查询(数组) 可选 => 要查询列名，不传或者空数组默认为查询所有 ['name']
// 更新(对象) 必传 => 要更新的列名与对象的值 {name:'李四'}
////////////////////////////////////
/**
* 操作方式：新增
* @param {Object} data 操作对象
*/
insert(data) {
if (!data) {
console.error('操作对象不能为空！');
return
}
const {
keys,
values
} = DealObject(data)
this.statement = `INSERT INTO ${this.#tbName}(${keys.join(',')}) VALUES(${values.join(',')})`
this.#operation = 'INSERT'
this.#step = this.#StepEnum.Operation
return this
}
/**
* 操作方式：删除
*/
delete() {
this.statement = `DELETE FROM ${this.#tbName}`
this.#operation = 'DELETE'
this.#step = this.#StepEnum.Operation
return this
}
/**
* 操作方式：查询
* @param {Array} data 操作对象
*/
select(data = []) {
if (data.length == 0) {
this.statement = `SELECT * FROM ${this.#tbName}`
} else {
this.statement = `SELECT ${data.join(',')} FROM ${this.#tbName}`
}
this.#operation = 'SELECT'
this.#step = this.#StepEnum.Operation
return this
}
/**
* 操作方式：更新
* @param {Object} data 操作对象
*/
update(data) {
if (!data) {
console.error('操作对象不能为空！');
return
}
const {
keys,
values
} = DealObject(data)
for (let i = 0; i < values.length; i++) {
keys[i] = `${keys[i]}=${values[i]}`
}
this.statement = `UPDATE ${this.#tbName} SET ${keys.join(',')}`
this.#operation = 'UPDATE'
this.#step = this.#StepEnum.Operation
return this
}
/**
* 自定义条件
* @param {String} condition 条件
* @param {String} link 连接
*/
where(condition, link = 'and') {
if (condition == '') {
console.error('自定义条件不能为空！');
return
}
if (this.#step < this.#StepEnum.Operation ||
this.#step > this.#StepEnum.Where) {
console.error('执行方法顺序有误！');
return
}
if (this.#step == this.#StepEnum.Where) {
this.statement = `${this.statement} ${link} ${condition}`
} else {
this.statement = `${this.statement} where ${condition}`
this.#step = this.#StepEnum.Where
}
return this
}
/**
* 相等条件,数据格式如下
* {name:'张三'}
* @param {Object} data 条件
* @param {String} link 连接
*/
equal(data, link = 'and') {
if (!data) {
console.error('操作对象不能为空！');
return
}
if (this.#step < this.#StepEnum.Operation ||
this.#step > this.#StepEnum.Where) {
console.error('执行方法顺序有误！');
return
}
const {
keys,
values
} = DealObject(data)
for (let i = 0; i < values.length; i++) {
keys[i] = `${keys[i]}=${values[i]}`
}
if (this.#step == this.#StepEnum.Where) {
this.statement = `${this.statement} and ${keys.join(' ' + link +' ')}`
} else {
this.statement = `${this.statement} where ${keys.join(' ' + link +' ')}`
this.#step = this.#StepEnum.Where
}
return this
}
/**
* 相似条件,数据格式如下
* {name:'张%'}
* @param {Object} data 条件
* @param {String} link 连接
*/
like(data, link = 'and') {
if (!data) {
console.error('操作对象不能为空！');
return
}
if (this.#step < this.#StepEnum.Operation ||
this.#step > this.#StepEnum.Where) {
console.error('执行方法顺序有误！');
return
}
const {
keys,
values
} = DealObject(data)
for (let i = 0; i < values.length; i++) {
keys[i] = `${keys[i]} like ${values[i]}`
}
if (this.#step == this.#StepEnum.Where) {
this.statement = `${this.statement} ${link} ${keys.join(' ' + link +' ')}`
} else {
this.statement = `${this.statement} where ${keys.join(' ' + link +' ')}`
this.#step = this.#StepEnum.Where
}
return this
}
/**
* 排序,数据格式如下
* {id:'desc',name:'asc'}
* @param {Object} data 操作对象
*/
order(data) {
if (!data) {
console.error('操作对象不能为空！');
return
}
if (this.#step < this.#StepEnum.Operation ||
this.#step >= this.#StepEnum.Define) {
console.error('执行方法顺序有误');
return
}
const {
keys,
values
} = DealObject(data, false)
for (let i = 0; i < values.length; i++) {
keys[i] = `${keys[i]} ${values[i]}`
}
this.statement = `${this.statement} order by ${keys.join(',')}`
this.#step = this.#StepEnum.Order
return this
}
/**
* 想要执行自定义sql必须创建对象后马上调用此方法
* 如过前面有其他步骤则直接返回自定义sql语句
* @param {Object} sql 自定义sql语句
*/
sql(sql) {
if (this.#step != this.#StepEnum.Init) {
console.error('执行方法顺序有误！');
return
}
if (!sql || sql == '') {
console.error('自定义SQL语句不能为空！');
return
}
this.statement = sql
this.#step = this.#StepEnum.Define
return this
}
/**
* 执行sql语句
*/
do() {
if (this.#step == this.#StepEnum.Init) {
console.error('执行方法顺序有误！');
return
}
return new Promise(async (resolve, reject) => {
if (this.statement == '') {
reject('执行语句为空，无法执行！')
return
}
let result
if (this.#operation == 'SELECT') {
result = await SqlSelect(this.statement)
} else {
result = await SqlExecute(this.statement)
}
resolve(result)
})
}
}
/**
* 初始操作对象
* @param {String} tbName 表名
*/
const DB = (tbName) => {
return SqliteOperation.getInstance(tbName)
}
return {
SqliteType,
OpenDB,
CloseDB,
SqlExecute,
SqlSelect,
CreateTable,
DropTable,
DB
}
}