module.exports = {
|
dbName: "warns",
|
dbPath: "_doc/warns.db",
|
|
isOpen() {
|
return plus.sqlite.isOpenDatabase({
|
name: "warns",
|
path: "_doc/warns.db"
|
})
|
},
|
|
openSqlite() {
|
return new Promise((resolve, reject) => {
|
// 打开数据库
|
plus.sqlite.openDatabase({
|
name: "warns",
|
path: "_doc/warns.db",
|
success(e) {
|
console.log('成功打开数据库:', e)
|
resolve(e); // 成功回调
|
},
|
fail(e) {
|
console.log('打开数据库failed', e)
|
reject(e); // 失败回调
|
}
|
})
|
})
|
},
|
closeSqlite() {
|
return new Promise((resolve, reject) => {
|
plus.sqlite.closeDatabase({
|
name: "warns",
|
success(e) {
|
console.log(e)
|
resolve(e);
|
},
|
fail(e) {
|
console.log(e)
|
reject(e);
|
}
|
})
|
})
|
},
|
// 数据库建表 sql:'create table if not exists dbTable("id" varchar(50),"name" TEXT)
|
// 创建 create table if not exists 、 dbTable 是表名，不能用数字开头、括号里是表格的表头
|
createTable(tableName, columnStr) {
|
return new Promise((resolve, reject) => {
|
// executeSql: 执行增删改等操作的SQL语句
|
plus.sqlite.executeSql({
|
name: "warns",
|
sql: `CREATE TABLE if not exists ${tableName}(${columnStr})`,
|
success(e) {
|
console.log(e)
|
resolve(e);
|
},
|
fail(e) {
|
console.log(e)
|
reject(e);
|
}
|
})
|
})
|
},
|
// 数据库删表 sql:'DROP TABLE dbTable'
|
dropTable(dbTable) {
|
return new Promise((resolve, reject) => {
|
plus.sqlite.executeSql({
|
name: "warns",
|
sql: `DROP TABLE ${dbTable}`,
|
success(e) {
|
resolve(e);
|
},
|
fail(e) {
|
reject(e);
|
}
|
})
|
})
|
},
|
// 向表格里添加数据 sql:'insert into dbTable values('x','x','x')' 对应新增
|
// 或者 sql:'insert into dbTable ('x','x','x') values('x','x','x')' 具体新增
|
// 插入 insert into 、 dbTable 是表名、根据表头列名插入列值
|
insertTableData(dbTable, data) {
|
// 判断有没有传参
|
if (dbTable !== undefined && data !== undefined) {
|
// 判断传的参是否有值
|
var bol = (JSON.stringify(data) == "{}");
|
if (!bol) {
|
return new Promise((resolve, reject) => {
|
// 表格添加数据
|
plus.sqlite.executeSql({
|
name: "warns",
|
sql: `insert or ignore into ${dbTable} values(${data})`,
|
success(e) {
|
resolve(e);
|
},
|
fail(e) {
|
reject(e);
|
}
|
})
|
})
|
} else {
|
return new Promise((resolve, reject) => {
|
reject("错误添加")
|
})
|
}
|
} else {
|
return new Promise((resolve, reject) => {
|
reject("错误添加")
|
})
|
}
|
},
|
|
// 查询获取数据库里的数据 sql:'select * from dbTable where lname = 'lvalue''
|
// 查询 select * from 、 dbTable 是表名、 where 查找条件 lname,lvalue 是查询条件的列名和列值
|
selectTableData(dbTable, lname, lvalue, cc, dd) {
|
if (dbTable !== undefined) {
|
// 第一个是表单名称，后两个参数是列表名，用来检索
|
if (lname !== undefined && cc !== undefined) {
|
// 两个检索条件
|
var sql = `select * from ${dbTable} where ${lname} = '${lvalue}' and ${cc} = '${dd}'`;
|
}
|
if (lname !== undefined && cc == undefined) {
|
// 一个检索条件
|
var sql = `select * from ${dbTable} where ${lname} = '${lvalue}'`;
|
}
|
if (lname == undefined) {
|
var sql = `select * from ${dbTable} order by unixTime desc`;
|
}
|
return new Promise((resolve, reject) => {
|
// 表格查询数据 执行查询的SQL语句
|
plus.sqlite.selectSql({
|
name: "warns",
|
sql: sql,
|
success(e) {
|
resolve(e);
|
},
|
fail(e) {
|
reject(e);
|
}
|
})
|
})
|
} else {
|
return new Promise((resolve, reject) => {
|
reject("错误查询")
|
});
|
}
|
},
|
|
getDistinctVal(dbTable, columnName) {
|
if (dbTable === undefined || columnName === undefined) {
|
return new Promise((resolve, reject) => {
|
reject("错误查询")
|
});
|
}
|
var sql = `select distinct ${columnName} from ${dbTable}`;
|
return new Promise((resolve, reject) => {
|
plus.sqlite.selectSql({
|
name: "warns",
|
sql: sql,
|
success(e) {
|
resolve(e);
|
},
|
fail(e) {
|
reject(e);
|
}
|
})
|
})
|
},
|
|
// 删除表里的数据 sql:'delete from dbTable where lname = 'lvalue''
|
// 删除 delete from 、 dbTable 是表名、 where 查找条件 lname,lvalue 是查询条件的列名和列值
|
deleteTableData(dbTable, lname, lvalue, ww, ee) {
|
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: "warns",
|
sql: sql,
|
success(e) {
|
resolve(e);
|
},
|
fail(e) {
|
reject(e);
|
}
|
})
|
})
|
} else {
|
return new Promise((resolve, reject) => {
|
reject("错误删除")
|
});
|
}
|
},
|
|
// 修改数据表里的数据 sql:"update dbTable set 列名 = '列值',列名 = '列值' where lname = 'lvalue'"
|
// 修改 update 、 dbTable 是表名, data: 要修改的列名=修改后列值, lname,lvalue 是查询条件的列名和列值
|
updateTableData(dbTable, l1,v1, l2, v2) {
|
var sql = `update ${dbTable} set ${l1} = ${v1} where ${l2} = '${v2}'`;
|
// where 前面是要修改的列名、列值，后面是条件的列名、列值
|
return new Promise((resolve, reject) => {
|
// 修改表数据
|
plus.sqlite.executeSql({
|
name: "warns",
|
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(dbTable, num) {
|
return new Promise((resolve, reject) => {
|
plus.sqlite.selectSql({
|
name: "warns",
|
sql: `select * from ${dbTable} order by id desc limit 15 offset '${num}'`,
|
success(e) {
|
resolve(e);
|
},
|
fail(e) {
|
reject(e);
|
}
|
})
|
})
|
}
|
}
|
