class Sqlite {
#privatedbName = '';
#privatePath = '';
constructor(dbName, path = '') {
this.#privatedbName = dbName;
this.#privatePath = path;
console.log(this.#privatedbName, this.#privatePath);
}
// 监听数据是否打开
isOpenDB() {
let dbName = this.#privatedbName;
let dbPath = this.#privatePath || `_doc/${dbName}.db`;
//数据库打开了就返回true,否则返回false
let isopen = plus.sqlite.isOpenDatabase({
name: dbName,
path: dbPath,
});
return isopen;
}
// 创建数据库/打开数据库
openDB() {
return new Promise((resolve, reject) => {
plus.sqlite.openDatabase({
name: this.#privatedbName || 'ot',
path: this.#privatePath || `_doc/${this.#privatedbName}.db`,
success: function (res) {
resolve(true);
},
fail: async (e) => {
let a = await closeDB();
console.log(e);
reject(false);
},
});
});
}
// 查询所有数据库表名
queryDBTable() {
return new Promise((resolve, reject) => {
this.execute(
'selectSql',
`select * FROM sqlite_master where type='table'`
)
.then((e) => {
resolve(e);
})
.catch((e) => {
reject(e);
});
});
}
/**
* 创建表
* data={dbname,tablename,describe}
*/
createTable(data) {
// 注意：tabName不能用数字作为表格名的开头
return new Promise((resolve, reject) => {
if (data.tablename && data.describe) {
let { tablename, describe } = data;
this.execute(
'executeSql',
`create table if not exists ${tablename}(${describe})`
)
.then((e) => {
resolve(`创建表${tablename}完成`);
})
.catch((e) => {
reject(e);
});
} else {
reject('参数不满足条件');
}
});
}
// 查询表是否存在
isTable(tabName) {
return new Promise((resolve, reject) => {
this.execute(
'selectSql',
`select count(*) as isTable FROM sqlite_master where type='table' and name='${tabName}'`
)
.then((e) => {
resolve(e[0].isTable ? true : false);
})
.catch((e) => {
reject(e);
});
});
}
/**
* 添加数据
* @param {String} tabName
* @param {Object} obj
*/
addSaveData(tabName, obj) {
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);
this.execute('executeSql', sqlStr)
.then((e) => {
resolve(e);
})
.catch((e) => {
reject(e);
});
} else {
reject('错误');
}
});
}
/**
* 简单查询，selectSql为复杂查询
* @param {String} tabName
* @param {Object} setData
* @param {String} byName 排序值
* @param {String} byType 正序倒序
*/
selectDataList(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);
return new Promise((resolve, reject) => {
if (tabName !== undefined) {
execute('selectSql', sql)
.then((e) => {
resolve(e);
})
.catch((e) => {
reject(e);
});
} else {
reject('错误');
}
});
}
// 获取数据库分页数据
/**
*
* @param {*} name
* @param {*} tabName
* @param {*} num 页码
* @param {*} size 页面大小返回条数
* @param {*} byName 排序主键字段
* @param {*} byType 排序类型 desc倒序 / asc正序
*/
async queryDataList(tabName, num, size, byName, byType) {
let count = 0;
let sql = '';
let numindex = 0;
await queryCount(this.dbName, 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 {
this.execute('selectSql', sql)
.then((e) => {
resolve(e);
})
.catch((e) => {
reject(e);
});
}
});
}
// 查询表数据总条数
queryCount(tabName) {
return new Promise((resolve, reject) => {
this.execute('selectSql', `select count(*) as num from ${tabName}`)
.then((e) => {
resolve(e);
})
.catch((e) => {
reject(e);
});
});
}
// 修改（更新）数据
// 示例：UPDATE COMPANY SET ADDRESS = 'Texas' WHERE ID = 6;
// UPDATE 表名 SET 要修改字段 = '修改内容' WHERE 筛选条件 = 6;
/**
* 简单更新数据
* @param {*} tabName 表名
* @param {*} setData 设置值 （修改字段 + 修改内容）
* @param {*} setName 筛选条件
* @param {*} setVal 筛选值
* @returns
*/
updateSqlData(tabName, setData, setName, setVal) {
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 ? ',' : ''
}`;
});
this.execute(
'executeSql',
`update ${tabName} set ${setStr} where ${setName} = "${setVal}"`
)
.then((e) => {
resolve(e);
})
.catch((e) => {
reject(e);
});
} else {
reject('错误');
}
});
}
/**
* 删除表
* @param {*} table
* @returns
*/
deleteTable(table) {
return new Promise((resolve, reject) => {
this.execute('executeSql', `DROP TABLE ${table}`)
.then((e) => {
resolve(e);
})
.catch((e) => {
reject(e);
});
});
}
/**
* @param {Object} dbName
* @param {Object} dbsql 执行 增\删\改 操作的SQL语句
*/
executeSql(dbsql) {
console.log('方法执行', dbsql);
return new Promise((resolve, reject) => {
this.execute('executeSql', dbsql)
.then((e) => {
resolve(e);
})
.catch((e) => {
reject(e);
});
});
}
/**
* @param {Object} selectNcDuli
*/
selectSql(selectNcDuli) {
return new Promise((resolve, reject) => {
execute('selectSql', selectNcDuli)
.then((e) => {
resolve(e);
})
.catch((e) => {
reject(e);
});
});
}
/**
* 执行方法
* @param {*} key sqlite方法名
* @param {*} sql
* @returns
*/
execute(key, sql) {
console.log('执行方法', key, sql);
return new Promise(async (resolve, reject) => {
console.log('执行方法2', key, sql);
try {
let isOpen = this.isOpenDB();
if (!isOpen) {
let isopen = await this.openDB();
if (!isopen) {
reject(e);
return;
}
}
plus.sqlite[key]({
name: this.#privatedbName,
sql: sql,
success: async (e) => {
let a = await this.closeDB();
resolve(e);
},
fail: async (e) => {
let a = await this.closeDB();
reject(e);
},
});
} catch (e) {
//TODO handle the exception
reject(e);
}
});
}
//关闭数据库
closeDB() {
return new Promise((resolve, reject) => {
plus.sqlite.closeDatabase({
name: this.#privatedbName,
success: function (e) {
console.log('数据库关闭成功');
resolve(true);
},
fail: function (e) {
console.log('数据库关闭失败');
reject(false);
},
});
});
}
}
export default Sqlite;
main.js 导入
import Sqlite from '@/util/Sqlite';
uni.$sqlite = new Sqlite('demo', '_doc/db/demo.db');
发表评论