赞
踩
const obj = {
dbName: 'test', // 数据库名称
dbPath: '_doc/demo.db', // 数据库地址,推荐以下划线为开头 _doc/xxx.db
// 判断数据库是否打开
isOpen():boolean {
// 数据库打开了就返回 true,否则返回 false
let open = plus.sqlite.isOpenDatabase({
name: this.dbName, // 数据库名称
path: this.dbPath // 数据库地址
} as PlusSqliteIsOpenDatabaseOptions)
return open;
},
// 创建数据库 或 有该数据库就打开
openSqlite() {
return new Promise((resolve, reject) => {
// 打开数据库
plus.sqlite.openDatabase({
name: this.dbName,
path: this.dbPath,
success(e) {
console.log('数据库开启');
resolve(e); // 成功回调
},
fail(e) {
reject(e); // 失败回调
}
} as PlusSqliteOpenDatabaseOptions)
})
},
// 关闭数据库
closeSqlite() {
return new Promise((resolve, reject) => {
plus.sqlite.closeDatabase({
name: this.dbName,
success(e) {
console.log('数据库关闭成功');
resolve(e);
},
fail(e) {
reject(e);
}
} as PlusSqliteCloseDatabaseOptions)
})
},
/**
* 数据库建表
* @param {Object} dbTable:表名
* @param {Object} data : 表结构
*/
createTable(dbTable : string, data : string) {
return new Promise((resolve, reject) => {
plus.sqlite.executeSql({
name: this.dbName,
sql: [`CREATE TABLE IF NOT EXISTS ${dbTable}(${data});`],
success(e) {
resolve(e);
},
fail(e) {
reject(e);
}
} as PlusSqliteExecuteSqlOptions)
})
},
//
/**
* 数据库删表,
* @param {Object} dbTable: 表名
* @param {Object} sql:'DROP TABLE dbTable'
*/
dropTable(dbTable : string) {
return new Promise((resolve, reject) => {
plus.sqlite.executeSql({
name: this.dbName,
sql: [`DROP TABLE ${dbTable}`],
success(e) {
resolve(e);
},
fail(e) {
reject(e);
}
} as PlusSqliteExecuteSqlOptions)
})
},
/**
* 新增数据
* @param {Object} dbTable :表名
* @param {Object} condition: 键名 []
* @param {Object} data : 值[]
*/
insertTableData<T>(dbTable : string, condition : string[] = [], data : string | string[] = []) {
// 判断传的参是否有值
if (condition.length) {
let sql:string
if (typeof data === 'string') {
sql = `INSERT INTO ${dbTable} (${condition.join(',')}) VALUES(${data})`;
} else if (typeof data === 'object') {
sql = `INSERT INTO ${dbTable} (${condition.join(',')}) VALUES ${data.join(',')}`;
}
return new Promise((resolve, reject) => {
// 表格添加数据
plus.sqlite.executeSql({
name: this.dbName,
sql: [sql],
success(e) {
resolve(e as T);
},
fail(e) {
reject(e);
}
} as PlusSqliteExecuteSqlOptions)
})
} else {
return new Promise((_, reject) => { reject("错误添加") })
}
},
/**
* 根据条件向表格里添加数据 有数据更新、无数据插入
* @param {Object} dbTable 表名
* @param {Object} data 值[]
* @param {Object} condition 键
*/
insertOrReplaceData(dbTable:string, data:string[], condition:string[]) {
// 判断有没有传参
if (!dbTable && data.length && condition.length) {
let sql:string = `INSERT OR REPLACE INTO ${dbTable} (${condition.join(',')}) VALUES(${data.join(',')})`;
// console.log(sql);
return new Promise((resolve, reject) => {
// 表格添加数据
plus.sqlite.executeSql({
name: this.dbName,
sql: [sql],
success(e) {
resolve(e);
},
fail(e) {
reject(e);
}
})
})
} else {
return new Promise((_, reject) => { reject("错误添加") })
}
},
// 查询获取数据库里的数据 sql:'SELECT * FROM dbTable WHERE lname = 'lvalue''
// 查询 SELECT * FROM 、 dbTable 是表名、 WHERE 查找条件 lname,lvalue 是查询条件的列名和列值
/**
* 查询数据
* @param {Object} dbTable 表名
* @param {Object} otherSql 其他语句
*/
selectTableData<T>(dbTable:string, otherSql:string) {
if (otherSql.indexOf(';') !== -1) return new Promise((_, reject) => { reject("错误查询") });
if (dbTable) {
let sql:string = `SELECT * FROM ${dbTable} ${otherSql}`
return new Promise((resolve, reject) => {
// 表格查询数据 执行查询的SQL语句
plus.sqlite.selectSql({
name: this.dbName,
sql: sql,
success(e) {
resolve(e as T);
},
fail(e) {
reject(e);
}
} as PlusSqliteSelectSqlOptions)
})
} else {
return new Promise((_, reject) => { reject("错误查询") });
}
},
/**
* 自定义查询
*/
selectTableCustom<T>(sql:string):any {
if (sql) {
return new Promise((resolve, reject) => {
// 表格查询数据 执行查询的SQL语句
plus.sqlite.selectSql({
name: this.dbName,
sql: sql,
success(e) {
resolve(e as T);
},
fail(e) {
reject(e);
}
} as PlusSqliteSelectSqlOptions)
})
} else {
return new Promise((_, reject) => { reject("错误查询") });
}
},
// 删除表里的数据 sql:'DELETE FROM dbTable WHERE lname = 'lvalue''
// 删除 DELETE FROM 、 dbTable 是表名、 WHERE 查找条件 lname,lvalue 是查询条件的列名和列值
deleteTableData(dbTable:string, lname?:string, lvalue?:string, ww?:string, ee?:string) {
if (dbTable !== undefined) {
if (lname == undefined) {
var sql = `DELETE FROM ${dbTable}`;
} else {
if (ww !== undefined) {
// 两个检索条件
var sql = `DELETE FROM ${dbTable} WHERE ${lname} = '${lvalue}' AND ${ww} = '${ee}'`;
} else {
// 一个检索条件
var sql = `DELETE FROM ${dbTable} WHERE ${lname} = '${lvalue}'`;
}
}
return new Promise((resolve, reject) => {
// 删除表数据
plus.sqlite.executeSql({
name: this.dbName,
sql: [sql],
success(e) {
resolve(e);
},
fail(e) {
reject(e);
}
})
})
} else {
return new Promise((_, reject) => { reject("错误删除") });
}
},
// 修改数据表里的数据 sql:"UPDATE dbTable SET 列名 = '列值',列名 = '列值' WHERE lname = 'lvalue'"
// 修改 UPDATE 、 dbTable 是表名, data: 要修改的列名=修改后列值, lname,lvalue 是查询条件的列名和列值
updateTableData(dbTable:string, data:string, lname:string, lvalue:string) {
if (lname == undefined) {
var sql = `UPDATE ${dbTable} SET ${data}`;
} else {
var sql = `UPDATE ${dbTable} SET ${data} WHERE ${lname} = '${lvalue}'`;
}
// WHERE 前面是要修改的列名、列值,后面是条件的列名、列值
return new Promise((resolve, reject) => {
// 修改表数据
plus.sqlite.executeSql({
name: this.dbName,
sql: [sql],
success(e) {
resolve(e);
},
fail(e) {
reject(e);
}
})
})
},
// 获取指定数据条数 sql:"SELECT * FROM dbTable ORDER BY 'id' DESC LIMIT 15 OFFSET 'num'"
// dbTable 表名, ORDER BY 代表排序默认正序, id 是排序的条件 DESC 代表倒序,从最后一条数据开始拿
// LIMIT 15 OFFSET '${num}',这句的意思是跳过 num 条拿 15 条数据, num 为跳过多少条数据是动态值
// 例 初始num设为0,就从最后的数据开始拿15条,下次不拿刚获取的数据,所以可以让num为15,这样就能一步一步的拿完所有的数据
pullSQL<T>(dbTable, id, num) {
return new Promise((resolve, reject) => {
plus.sqlite.selectSql({
name: this.dbName,
sql: `SELECT * FROM ${dbTable} ORDER BY '${id}' DESC LIMIT 15 OFFSET '${num}'`,
success(e) {
resolve(e as T);
},
fail(e) {
reject(e);
}
})
})
}
}
export default obj
页面使用
import sqlite from '@/libs/sqlite'
const arrRender = ref([])
async function addData () {
let list : string[] = new Array(100000).fill(0).map((_, index) => `("${index}--${Math.floor(Math.random() * 1000)}你好")`)
await sqlite.insertTableData('data', ['name'], list)
useToast('添加成功')
}
async function selectData() {
let selectData = await sqlite.selectTableCustom('SELECT * FROM data limit 100 offset 100')
console.log(selectData, '部分數據 ');
arrRender.value = selectData
}
async function clearData () {
await sqlite.deleteTableData('data')
useToast('数据库清空成功')
}
async function selectSum (){
let allNum = await sqlite.selectTableCustom<totalOptions[]>('SELECT count(*) as total FROM data')
useToast(allNum[0].total)
}
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。