新建个sqlite.js文件
module.exports = {
dbName: 'epms-police', //数据库名称
dbPath: '_downloads/check.db', //数据库地址，推荐以下划线开头 ——doc/xxx.db
// 判断数据库是否打开
isOpen() {
// 数据库打开了就返回 true,否则返回 false
var open = plus.sqlite.isOpenDatabase({
name: this.dbName, // 数据库名称
path: this.dbPath // 数据库地址
})
return open;
},
// 创建数据库 或 有该数据库就打开
openSqlite() {
return new Promise((resolve, reject) => {
// 打开数据库
plus.sqlite.openDatabase({
name: this.dbName,
path: this.dbPath,
success(e) {
resolve(e); // 成功回调
},
fail(e) {
reject(e); // 失败回调
}
})
})
},
// 关闭数据库
closeSqlite() {
return new Promise((resolve, reject) => {
plus.sqlite.closeDatabase({
name: this.dbName,
success(e) {
resolve(e);
},
fail(e) {
reject(e);
}
})
})
},
// 数据库建表 sql：sql:'CREATE TABLE IF NOT EXISTS dbTable("id" varchar(50),"name" TEXT)
// 创建 CREATE TABLE IF NOT EXISTS 、 dbTable 是表名，不能用数字开头、括号里是表格的表头
createTable(dbTable, data) {
return new Promise((resolve, reject) => {
// executeSql: 执行增删改等操作的SQL语句
plus.sqlite.executeSql({
name: this.dbName,
sql: `CREATE TABLE IF NOT EXISTS ${dbTable}(${data})`,
success(e) {
resolve(e);
},
fail(e) {
reject(e);
}
})
})
},
// 数据库删表sql：'DROP TABLE dbTable'
dropTable(dbTable) {
return new Promise((resolve, reject) => {
plus.sqlite.executeSql({
name: this.dbName,
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, condition) {
// 判断有没有传参
if (dbTable !== undefined && data !== undefined) {
// 判断传的参是否有值
var bol = (JSON.stringify(data) == "{}");
if (!bol) {
if (condition == undefined) {
var sql = `INSERT INTO ${dbTable} VALUES('${data}')`;
} else {
var sql = `INSERT INTO ${dbTable} (${condition}) VALUES(${data})`;
}
// 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((resolve, reject) => {
reject("错误添加")
})
}
} else {
return new Promise((resolve, reject) => {
reject("错误添加")
})
}
},
// 根据条件向表格里添加数据 有数据更新、无数据插入
// (建表时需要设置主键) 例如 --- "roomid" varchar(50) PRIMARY KEY
insertOrReplaceData(dbTable, data, condition) {
// 判断有没有传参
if (dbTable !== undefined && data !== undefined) {
if (condition == undefined) {
var sql = `INSERT OR REPLACE INTO ${dbTable} VALUES('${data}')`;
} else {
var sql = `INSERT OR REPLACE INTO ${dbTable} (${condition}) VALUES(${data})`;
}
// 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((resolve, reject) => {
reject("错误添加")
})
}
},
// 查询获取数据库里的数据 sql:'SELECT * FROM dbTable WHERE lname = 'lvalue''
// 查询 SELECT * FROM 、 dbTable 是表名、 WHERE 查找条件 lname,lvalue 是查询条件的列名和列值
selectTableData(dbTable, uname, namevalue, upass, passvalue, urrn, rrnvalue) {
if (dbTable !== undefined) {
// 第一个是表单名称，后两个参数是列表名，用来检索
if (uname !== undefined && upass !== undefined && urrn !== undefined) {
// 三个检索条件
var sql =
`SELECT * FROM ${dbTable} WHERE ${uname} = '${namevalue}' AND ${upass} = '${passvalue}' AND ${urrn}='${rrnvalue}' ORDER BY checkTime DESC`;
}
if (uname !== undefined && upass !== undefined && urrn == undefined) {
// 两个检索条件
var sql = `SELECT * FROM ${dbTable} WHERE ${uname} = '${namevalue}' AND ${upass} = '${passvalue}' ORDER BY checkTime DESC`;
}
if (uname !== undefined && upass == undefined && urrn == undefined) {
// 一个检索条件
var sql = `SELECT * FROM ${dbTable} WHERE ${uname} = '${namevalue}' ORDER BY checkTime DESC`;
// console.log(sql);
}
if (uname == undefined) {
var sql = `SELECT * FROM ${dbTable} ORDER BY checkTime DESC`;
}
return new Promise((resolve, reject) => {
// 表格查询数据 执行查询的SQL语句
plus.sqlite.selectSql({
name: this.dbName,
sql: sql,
success(e) {
resolve(e);
},
fail(e) {
reject(e);
}
})
})
} else {
return new Promise((resolve, reject) => {
reject("错误查询")
});
}
},
// 删除表里的数据 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: this.dbName,
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, data, lname, lvalue) {
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(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);
},
fail(e) {
reject(e);
}
})
})
},
// WHERE ${lname} = '${lvalue}'
// dbTable表 type 1=总数 2=本月检查 3=上约检查 4=今年检查 lname=查询的名称 lvalue=查询的value值 zhname=查询的名称 zhvalue=查询的value值
CountSQL(dbTable,type,lname,lvalue,zhname,zhvalue) {
return new Promise((resolve, reject) => {
if(type==1){
var sql=`SELECT COUNT(*) as total FROM ${dbTable} WHERE ${zhname} = '${zhvalue}'`
}else{
var sql=`SELECT COUNT(*) as total FROM ${dbTable} WHERE ${lname} = '${lvalue}'AND ${zhname} = '${zhvalue}'`
}
plus.sqlite.selectSql({
name: this.dbName,
sql:sql,
success(e) {
resolve(e);
},
fail(e) {
reject(e);
}
})
})
}
}
如何使用
// 处理本地数据库带值结束
// 本地数据库操作开始
// 删除表数据
deleteTableData() {
let open = DB.isOpen();
if (open) {
// 删除表 DB.deleteTableData(表名,查询条件列名,查询条件列值)
DB.deleteTableData(tableName)
.then(res => {
showToast("success", "删除表数据成功");
})
.catch(error => {
console.log("删除失败", error);
});
} else {
showToast("数据库未打开");
}
},
// 打开数据库
openSQL() {
// 这个是查询有没有打开数据库
let open = DB.isOpen();
console.log("数据库状态", open ? "开启" : "关闭");
if (!open) {
DB.openSqlite()
.then(res => {
console.log("数据库已打开");
})
.catch(error => {
console.log("数据库开启失败");
});
}
},
// 关闭数据库
closeSQL() {
// 这个是查询有没有打开数据库
let open = DB.isOpen();
if (open) {
DB.closeSqlite()
.then(res => {
console.log("数据库已关闭");
})
.catch(error => {
console.log("数据库关闭失败");
});
}
},
// 新增场所信息表
// policeId=检查id account=权限设置 checkDate=检查日期 checkYear=检查年份 status=上传状态 1=已上传 2=未上传 companyFactName=检查单位 placeNo=场所号 sysCurOrgName sysCurUserName licenseRegisterName policeName（creator） checkWayCode=检查方式名称（日常检查）
// checkWayName=检查方式编码（'9'） checkTime=检查时间 eplaceName=licenseRegisterName eplacePerson=legalManName
// inputMan=sysCurUserName（录入人） deductScore=扣分分数 curScore=总分 safetyLevelCode=等级code safetyLevelName=等级名称
// handleOpinion=处理意见 handleResult=处理结果 remark creator createTime modifier businessTypeName=场所类型名称
// businessTypeCode=场所类型code checkList=检查模板的内容
//
createPlaceInfoTable() {
let open = DB.isOpen();
if (open) {
this.openSQL();
let sql =
'"id" INTEGER PRIMARY KEY AUTOINCREMENT,"policeId" text,"account" text,"checkDate" text,"checkYear" text,"status" text,"companyFactName" text,"placeNo" text,"sysCurOrgName" text,"sysCurUserName" text,"licenseRegisterName" text,"policeName" text,"checkWayCode" text,"checkWayName" text,"checkTime" text,"eplaceName" text,"eplacePerson" text,"inputMan" text,"deductScore" text,"curScore" text,"safetyLevelCode" text,"safetyLevelName" text,"handleOpinion" text,"handleResult" text,"remark" text,"creator" text,"createTime" text,"modifier" text,"businessTypeName" text,"businessTypeCode" text,"checkList" text';
// 创建表 DB.createTable(表名, 表的列)
DB.createTable(tableName, sql)
.then(res => {
console.log('创建场所信息表成功', JSON.stringify(res))
})
.catch(error => {
console.log(JSON.stringify(error))
console.log("创建场所信息表失败");
this.createPlaceInfoTable()
});
} else {
console.log("数据库未打开");
}
},
// 新增检查模板表数据
insertPlaceInfoData(item) {
let open = DB.isOpen();
if (open) {
item.checkList = JSON.stringify(item.checkList);
let account = uni.getStorageSync('account'); //账号获取
return new Promise((resolve, reject) => {
let sql =
`'${item.policeId}','${account}','${item.checkDate}','${item.checkYear}','${item.status}','${item.companyFactName}','${item.placeNo}','${item.sysCurOrgName}','${item.sysCurUserName}','${item.licenseRegisterName}','${item.policeName}','${item.checkWayCode}','${item.checkWayName}','${item.checkTime}','${item.licenseRegisterName}','${item.legalManName}'
,'${item.creator}','${item.deductScore}','${item.curScore}','${item.safetyLevelCode}','${item.safetyLevelName}'
,'${item.handleOpinion}','${item.handleResult}','${item.remark}','${item.creator}','${item.createTime}','${item.modifier}','${item.businessTypeName}','${item.businessTypeCode}','${item.checkList}'`;
let condition =
"'policeId','account','checkDate','checkYear','status','companyFactName','placeNo','sysCurOrgName','sysCurUserName','licenseRegisterName','policeName','checkWayCode','checkWayName','checkTime','eplaceName','eplacePerson','inputMan','deductScore','curScore','safetyLevelCode','safetyLevelName','handleOpinion','handleResult','remark','creator','createTime','modifier','businessTypeName','businessTypeCode','checkList'";
// 新增 DB.insertTableData(表名, 对应表头列的数据) checkListTable
DB.insertTableData(tableName, sql, condition)
.then(res => {
console.log(JSON.stringify(res))
console.log("新增数据成功");
resolve('1')
// this.selectTableData();
})
.catch(error => {
resolve('2')
console.log(JSON.stringify(error))
});
})
} else {
showToast('none', '数据库未打开')
}
},
// 修改表数据
updateTableData(item) {
return new Promise((resolve, reject) => {
let open = DB.isOpen();
if (open) {
item.checkList = JSON.stringify(item.checkList);
// let data = `content = '我被修改了',time = '${time}'`;
let data =
`status='${item.status}',handleOpinion='${item.handleOpinion}',handleResult='${item.handleResult}',safetyLevelCode='${item.safetyLevelCode}',safetyLevelName='${item.safetyLevelName}',curScore='${item.curScore}',checkList = '${item.checkList}'`
// 修改表数据 DB.updateTableData(表名, 要修改的列名=修改后列值, 修改条件的列名, 修改条件的列值)
DB.updateTableData(tableName, data, "id", item.id)
.then(res => {
resolve('1')
})
.catch(error => {
resolve('2')
});
} else {
showToast("数据库未打开");
}
})
},
this.updateTableData(finallyData).then(resData2 => {
if (resData2 == '1') { //成功
uni.switchTab({
url: '/pages/index/index'
});
} else {
showToast('error', '保存草稿失败，请重新再试')
}
})
this.insertPlaceInfoData(finallyData).then(resData2 => {
if (resData2 == '1') { //成功
uni.switchTab({
url: '/pages/index/index'
});
} else {
showToast('error', '保存草稿失败，请重新再试')
}
})
