uniapp sqllite笔记

缺乏、安全感 2022-09-09 13:44 208阅读 0赞

#### 调用h5+的 sqllite ####

// 创建数据库 ｜ 打开数据库
    function openSqllite() { 
    	return new Promise((resolve, reject) => { 
    		plus.sqlite.openDatabase({ 
    			name: 'life', 					// 数据库名称
    			path: '_doc/life.db',   // 数据库地址
    			success(e) {  resolve(e) },
    			fail(e) {  reject(e) }
    		})
    	})
    }
    
    // 关闭数据库
    function closeSqllite() { 
    	return new Promise((resolve, reject) => { 
    		plus.sqlite.closeDatabase({ 
    			name: 'life',
    			success(e) {  resolve() },
    			fail(e) {  reject() }
    		})
    	})
    }
    
    // 监听数据库是否开启 return type : Boolean
    function isOpen(name, path) { 
    	return plus.sqlite.isOpenDatabase({  name: 'life', path: '_doc/life.db' })
    }
    
    // 执行 sql 语句 for create table
    /* 例子 创建 table 'create table if not exists userInfo ( "index" INTEGER PRIMARY KEY AUTOINCREMENT, 自动增加 "avatar" TEXT )' 添加数据 'insert into userInfo (id,name,gender,avatar) values("'+id+'","'+name+'","'+gender+'","'+avatar+'")' 'insert into userInfo (name, age) values ("demo1", "23")' 删除数据 'delete from '+name+' where '+sol+'="'+qq+'" and '+ww+'='+ee+'' 'delete from '+name+' where '+sol+'="'+qq+'"' 修改数据 update table_name set field1 = new-value1, field2=new-value2 'update '+listName+' set '+name+'="'+cont+'" where '+use+'="'+sel+'"' */
    function executeSqlCode(sqlCode) { 
    	return new Promise((resolve, reject) => { 
    		plus.sqlite.executeSql({ 
    			name: 'life',
    			sql: sqlCode,
    			success(e) {  resolve(e) },
    			fail(e) {  reject(e) }
    		})
    	})
    }
    
    function synExecuteSqlCode(sqlCode) { 
    	plus.sqlite.executeSql({ 
    		name: 'life',
    		sql: sqlCode,
    		success(e) {  
    			console.log("用户资产添加成功")
    		},
    		fail(err) {  
    			console.log("用户资产添加失败")
    		}
    	})
    }
    
    // 查询数据
    /* 例子 'select * from '+name+' where '+aa+'='+bb+' and '+cc+'='+dd+'' 'select * from '+id+' order by list desc limit 15 offset '+num+'', */
    function selectFromTable(sqlCode) { 
    	return new Promise((resolve, reject) => { 
    		plus.sqlite.selectSql({ 
    			name: 'life',    // 数据库名称
    			sql: sqlCode,
    			success(res) {  resolve(res) },
    			fail(e) {  reject(e) }
    		})
    	})
    }
    
    export default { 
    	openSqllite,
    	closeSqllite,
    	isOpen,
    	executeSqlCode,
    	selectFromTable,
    	synExecuteSqlCode,
    }

#### 表结构 ####

export const createSql = [
    	`create table if not exists userInfo ( "userid" TEXT, "username" TEXT, "password" TEXT, "userphoto" TEXT, "realname" TEXT, "telephone" TEXT, "company" TEXT, "job_number" TEXT, "userstatus" TEXT, "role_id" TEXT, "role" TEXT )`,
    	`create table if not exists repair_schedule ( "netid" TEXT, "netname" TEXT, "netcode" TEXT, "uptime" TEXT, "parentid" TEXT, "netdesc" TEXT, "isdel" TEXT, "type" TEXT )`,
    	`create table if not exists vehicle ( "netid" TEXT, "netname" TEXT, "netcode" TEXT, "uptime" TEXT, "parentid" TEXT, "netdesc" TEXT, "isdel" TEXT, "type" TEXT )`
    }

#### 增删改查 ####

import sqlLite from '@/common/sqllite-utils.js';
    import {  createSql } from "@/common/table-structure.js";
    
    // 判断数据库是否已经打开
    function dbIsOpen(self) { 
    	if (!(sqlLite.isOpen())) { 
    		sqlLite.openSqllite()
    		.then((e) => console.log("打开数据库 成功"))
    		.catch((e) => { 
    			self.$showModal({ title: "提示",concent: '打开数据库 失败',delCancel: true})
    		})
    	}
    }
    
    // 判断表（没有 则 创建）
    function hesTable(self, tableName, createSqlIndex) { 
    	sqlLite.selectFromTable("SELECT name FROM sqlite_master WHERE type= 'table' AND name= " + tableName).then((e) => { 
    		// 没有用户表 创建用户表
    		if (e.length === 0) { 
    			sqlLite.selectFromTable(createSql[createSqlIndex]).then((e) => { 
    				console.log("创建表成功:", e);
    			}).catch((err) => { 
    				console.log("创建表失败:", err);
    				self.$showModal({  title: "提示",concent: '创建 表失败',delCancel: true })
    			})
    		}
    	}).catch((e) => { 
    		self.$showModal({ title: "提示",concent: '查看表结构失败', delCancel: true })
    	})
    }
    
    function intoLogListTable(self, data, callback, clear = false) { 
    	// 1.清空表中的数据
    	if (clear) { 
    		clearTable(self, 'log_list')
    	}
    	// 2.添加数据
    	for (var i = 0; i < data.length; i++) { 
    		sqlLite.executeSqlCode(
    				`insert into log_list ( userid, desc, addtime, log_code, gznumber ) values ( "${ data[i].userid}", "${ data[i].desc}", "${ data[i].addtime}", "${ generateId()}", "${ data[i].gznumber}" )`
    			)
    			.then((e) => { 
    				callback(true)
    			})
    			.catch((e) => { 
    				console.log(e);
    				self.$showModal({ 
    					title: "提示",
    					concent: '添加失败',
    					delCancel: true
    				})
    				callback(false)
    			})
    	}
    }
    
    // 判断
    function intoLogListUTable(self, data, clear = false) { 
    	// 1.清空表中的数据
    	if (clear) { 
    		clearTable(self, 'log_list')
    	}
    	// 2.添加数据
    	for (var i = 0; i < data.length; i++) { 
    		sqlLite.executeSqlCode(
    				`insert into log_list ( creater, content, date, userid, num ) values ( "${ data[i].creater}", "${ data[i].content}", "${ data[i].date}", "${ data[i].userid}", "${ data[i].num}" )`
    			)
    			.then((e) => { 
    				callback(true)
    			})
    			.catch((err) => { 
    				console.log(err);
    				self.$showModal({ 
    					title: "提示",
    					concent: '添加失败',
    					delCancel: true
    				})
    				callback(false)
    			})
    	}
    }
    
    // 查询某个表中的数据
    function selectFromTable(self, tableName) { 
    	return new Promise((resolve, reject) => { 
    		sqlLite.selectFromTable("select * from " + tableName).then((result) => { 
    			resolve(result)
    		}).catch((e) => { 
    			reject(e)
    		})
    	})
    }
    
    // limit 查询
    function selectFromTableLimit(self, tableName, limit, offset) { 
    	return new Promise((resolve, reject) => { 
    		sqlLite.selectFromTable("select * from " + tableName + " limit " + limit + " offset " + offset).then((
    			result) => { 
    			resolve(result)
    		}).catch((e) => { 
    			reject(e)
    		})
    	})
    }
    
    // 按ID倒序查询 
    function selectFromTableLimitDescID(self, tableName, limit, offset) { 
    	return new Promise((resolve, reject) => { 
    		sqlLite.selectFromTable("select * from " + tableName + " order by id desc" + " limit " + limit +
    				" offset " + offset)
    			.then((result) => { 
    				resolve(result)
    			})
    			.catch((e) => { 
    				reject(e)
    			})
    	})
    }
    
    // 按条件进行查询并且limit
    function selectFromTableLimitDescIDWhere(self, tableName, limit, offset, option, id = "id") { 
    	return new Promise((resolve, reject) => { 
    		sqlLite.selectFromTable(
    				"select * from " + tableName + " where " + option + " order by " + id + " desc" + " limit " +
    				limit + " offset " + offset
    			)
    			.then((result) => { 
    				resolve(result)
    			})
    			.catch((err) => { 
    				console.log(err);
    				reject(e)
    			})
    	})
    }
    
    // 按条件进行查询
    function selectFromTableWhere(self, tableName, option) { 
    	return new Promise((resolve, reject) => { 
    		sqlLite.selectFromTable(
    				"select * from " + tableName + " where " + option
    			)
    			.then((result) => { 
    				resolve(result)
    			})
    			.catch((e) => { 
    				reject(e)
    			})
    	})
    }
    
    // 清除某个表中的数据
    function clearTable(self, tableName) { 
    	sqlLite.executeSqlCode("delete from " + tableName)
    		.then((e) => console.log('清除数据成功'))
    		.catch((e) => { 
    			self.$showModal({ 
    				title: "提示",
    				concent: '清除数据失败',
    				delCancel: true
    			})
    		})
    }
    
    // 修改某个表中的字段 updateTableCol(this, 'userinfo', "name = 'name'", "id = 250")
    function updateTableCol(self, tableName, col, option, callback = () => { }) { 
    	sqlLite.executeSqlCode("update " + tableName + " set " + col + " where " + option)
    		.then((e) => { 
    			console.log('修改成功')
    			callback(true)
    		})
    		.catch((e) => { 
    			console.log("e:", e);
    			self.$showModal({ 
    				title: "提示",
    				concent: '修改失败',
    				delCancel: true
    			})
    			callback(false)
    		})
    }
    
    // 删除某个表 调用：sqltool.delTable(this, "userInfo")
    function delTable(self, tableName) { 
    	sqlLite.executeSqlCode(
    			"drop table " + tableName
    		).then((e) => console.log('删除成功'))
    		.catch((e) => console.log('删除失败'))
    }
    
    // 删除表中的某表数据
    function delTableCol(self, tableName, option, callback=()=>{ }) { 
    	sqlLite.selectFromTable(
    			"DELETE FROM " + tableName + " where " + option
    		)
    		.then((result) => { 
    			console.log("删除成功");
    			callback(true)
    		})
    		.catch((err) => { 
    			console.log("err", err);
    			self.$showModal({ title: "提示",concent: '删除失败',delCancel: true})
    			callback(false)
    		})
    }
    
    // 生成10为时间戳
    function timest() { 
    	var tmp = Date.parse(new Date()).toString();
    	tmp = tmp.substr(0, 10);
    	return tmp;
    }
    
    // 字符串转base64
    function encode(str) { 
    	var encode = encodeURI(str); // 对字符串进行编码
    	var base64 = btoa(encode); // 对编码的字符串转化base64
    	return base64;
    }
    
    // base64转字符串
    function decode(base64) { 
    	var decode = atob(base64); // 对base64转编码
    	var str = decodeURI(decode); // 编码转字符串
    	return str;
    }
    
    function generateId() { 
    	var randStr = "";
    	for (var i = 0; i < 12; i++) { 
    		var randItem = Math.floor(Math.random() * 10);
    		randStr += randItem;
    	}
    	return randStr
    }
    
    function generateId9() { 
    	var randStr = "";
    	for (var i = 0; i < 9; i++) { 
    		var randItem = Math.floor(Math.random() * 10);
    		randStr += randItem;
    	}
    	return randStr
    }
    
    function generateOutId() { 
    	var randStr = "";
    	for (var i = 0; i < 12; i++) { 
    		var randItem = Math.floor(Math.random() * 10);
    		randStr += randItem;
    	}
    	// randStr = "CK" + randStr
    	return randStr
    }
    
    function generateGDId() { 
    	var randStr = "";
    	for (var i = 0; i < 9; i++) { 
    		var randItem = Math.floor(Math.random() * 10);
    		randStr += randItem;
    	}
    	return randStr
    }
    
    function add0 ( m ) {  return m < 10 ? '0' + m : m }
    function format(shijianchuo)
    { 
    	//shijianchuo是整数，否则要parseInt转换
    	var time = new Date(shijianchuo);
    	var y = time.getFullYear();
    	var m = time.getMonth()+1;
    	var d = time.getDate();
    	var h = time.getHours();
    	var mm = time.getMinutes();
    	var s = time.getSeconds();
    	return y+'年'+add0(m)+'月'+add0(d)+'日 '+add0(h)+':'+add0(mm)+':'+add0(s);
    }
    
    export default { 
    	dbIsOpen,
    	hesTable,
    	intoUserInfoTable,
    	intoRepairScheduleTable,
    	intoUserUpdateTimeTable,
    	disposeUserThingList,
    	timest,
    	format,
    }