sqlite在H5中封装及使用(笔记)

1、sqlite.js

import Vue from 'vue'
const app = new Vue()

//name:数据库名字
//databaseName:表名字
//tableField:表的字段
//dataType:字段类型
//saveData:存储数据
//field:查询的字段名，数组形式传值
//数据库查询成功和失败的数据处理
let success = {
	code: 0,
	data: null,
	msg: '成功'
}
let fail = {
	code: 1,
	data: null,
	msg: null
}

//打开数据库
function createDB(name) {
	return new Promise((resolve, reject) => {
		plus.sqlite.openDatabase({
			name: name,
			path: '_doc/' + name + '.db',
			success(res) {
				resolve(success); //成功回调
			},
			fail(e) {
				reject(e); //失败回调
			}
		})
	})
}

//判断数据库是否打开
function validDBOpen(name) {
	return plus.sqlite.isOpenDatabase({
		name: name,
		path: '_doc/' + name + '.db'
	});
}

// 关闭数据库
function closeDB(name) {
	return new Promise((resolve, reject) => {
		plus.sqlite.closeDatabase({
			name: name,
			success: (e) => {
				resolve(success);
			},
			fail: (e) => {
				fail.data = e
				reject(fail); //失败回调
			}
		});
	})
}

//一次获取指定数据条数
//不想一次性把数据全拿过来就可以这样写
//id为表格名，desc代表倒序拿数据，正常是从第一条开始拿，倒序就从最后一条也是最新的一条数据开始拿
//limit 15 offset '+num+''，后面这是两个单引号，这句的意思是跳过多少条拿15条数据，num是动态值
//比如你刚开始给num设为0，那就从最后面的数据开始拿15条，你下次肯定不想再拿刚刚获取到的数据，所以可以让num为15，这样就能一步一步的拿完所有的数据
// function pullSQL(id,num){
// 	//id为表名，num为跳过多少条数据
// 	//根据list来倒序拿数据，跳过num条拿取15条
// 	return new Promise((resolve,reject) =>{
// 		plus.sqlite.selectSql({
// 			name:'pop',
// 			sql:'select * from '+id+' order by list desc limit 15 offset '+num+'',
// 			success(e){
// 				resolve(e);
// 			},
// 			fail(e){
// 				reject(e);
// 			}
// 		})
// 	})
// }

// 执行事务 begin（开始事务）、commit（提交）、rollback（回滚）。
function transactionDB(name, operation) {
	return new Promise((resolve, reject) => {
		plus.sqlite.transaction({
			name: name,
			operation: operation,
			success: function(e) {
				console.log("执行事务成功")
				resolve(success);
			},
			fail: function(e) {
				console.log("执行事务失败")
				fail.data = e;
				reject(fail); //失败回调
			}
		});
	})
}

// 执行(增删改)SQL语句
function executeSQL(name, sql) {
	return new Promise((resolve, reject) => {
		plus.sqlite.executeSql({
			name: name,
			sql: sql,
			success: function(e) {
				resolve(success);
			},
			fail: function(e) {
				fail.data = e;
				reject(fail); //失败回调
			}
		});
	})
}

// 执行查询SQL语句
function selectSQL(name, sql, success) {
	return new Promise((resolve, reject) => {
		plus.sqlite.selectSql({
			name: name,
			sql: sql,
			success: function(data) {
				success.data = data;
				resolve(success)
			},
			fail: function(error) {
				fail.data = error
				reject(fail)
			}
		});
	})
}

//删除表格
function daleteTable(name, databaseName) {
	return new Promise((resolve, reject) => {
		plus.sqlite.executeSql({
			name: name,
			sql: 'drop table ' + databaseName,
			success: function(e) {
				resolve(success);
			},
			fail: function(e) {
				fail.data = e;
				reject(fail); //失败回调
			}
		});
	})
}

//创建表格
function createTable(name, databaseName, tableField, dataType) {
	var data = [];
	for (var i = 0; i < tableField.length; i++) {
		data.push('"' + tableField[i] + '" ' + dataType[i])
	}
	var totalData = data.join(',')
	let sql = `create table if not exists ${databaseName}  (${totalData})`;
	console.log(sql)
	return new Promise((resolve, reject) => {
		plus.sqlite.executeSql({
			name: name,
			sql: sql,
			success: function(e) {
				resolve(success);
			},
			fail: function(e) {
				fail.data = e;
				reject(fail); //失败回调
			}
		});
	})
}

//增
async function insertSqlState(name, databaseName, tableField, saveData) {
	//databaseName:表名
	//tableField:字段名数组
	//saveData:存储数据
	var sql;
	var fielData = tableField.join(',');
	var result;
	//对象数据
	if (saveData instanceof Object) {
		var cellAry = [];
		tableField.forEach(function(item) {
			cellAry.push('"' + saveData[item] + '"')
		})
		var celldata = "(" + cellAry.join(',') + ")"
		sql = "insert into " + databaseName + "(" + fielData + ")" + " values" + celldata
	} else {
		//数组数据
		var totalAry = []
		saveData.forEach((currentValue, index) => {
			var cellAry = [];
			tableField.forEach((item, ind) => {
				cellAry.push('"' + currentValue[tableField[ind]] + '"')
			})
			var celldata = "(" + cellAry.join(',') + ")"
			totalAry.push(celldata)
		})
		var totalStr = totalAry.join(',')
		sql = "insert into " + databaseName + "(" + fielData + ")" + " values" + totalStr
		// console.log(totalStr)
	}
	console.log(sql)
	console.log(celldata)
	console.log(fielData)
	// console.log(sql)
	await  executeSQL(name, sql).then(res=>{
		console.log("执行增加操作成功")
		console.log(res)
		result = res;
	},error=>{
		console.log("执行增加操作失败")
		console.log(error)
		result = error;
	})
	return result
}

//删
async function deleteSqlState(name, databaseName, condition) {
	//condition:条件
	// var sql = "delete from " + databaseName+ " where id=1";
	var sql;
	var result;
	if (!condition) {
		sql = "delete from " + databaseName
	} else {
		sql = "delete from " + databaseName + " where " + condition
	}
	await executeSQL(name, sql).then(res => {
		console.log("执行删除操作成功")
		console.log(res)
		result = res;
		console.log(result)
	}, error => {
		console.log("执行删除操作失败")
		console.log(error)
		result = error;
	})
	console.log(result)
	return result
}

//改
async function updateSqlState(name, databaseName, params, condition) {
	var result;
	var sql = "update " + databaseName + " set " + params + " where " + condition;
	await executeSQL(name, sql).then(res => {
		console.log("执行修改操作成功")
		console.log(res)
		result = res;
	}, error => {
		console.log("执行修改操作失败")
		console.log(error)
		result = error;
	})
	return result
}

//查询封装的sql
function checkSqlState(field, databaseName, condition) {
	//"select 字段名 from 表名 where 查询条件 group by 分组的字段 having 筛选条件 order by 排序字段"
	//field:查询的字段名，数组形式传值
	//condition:查询条件
	var sql;
	var fieldAry = []
	field.forEach((item) => {
		fieldAry.push(item)
	})
	var fieldStr = fieldAry.join(',')
	if (condition) {
		sql = "select " + fieldStr + " from " + databaseName + " where " + condition
	} else {
		sql = "select " + fieldStr + " from " + databaseName
	}
	// console.log(sql)
	return sql;
}

//存储数据
// function saveData(name, databaseName, tableField, dataType, saveData) {
// 	createDB(name); //打开数据库
// 	if (validDBOpen(name)) { //如果数据库打开
// 		createTable(name, databaseName, tableField, dataType) //如果不存在就创建表
// 		deleteSqlState(name, databaseName)
// 		insertSqlState(name, databaseName, tableField, saveData); //插入数据
// 		closeDB(name)
// 	}
// }

  function saveData(name, databaseName, tableField, dataType, saveData) {
	transactionDB(name, 'begin').then(res => {
		console.log("执行事务开始成功")
		console.log(res)
		//如果不存在就创建表
		 createTable(name, databaseName, tableField, dataType).then(res => {
			console.log("数据库创建成功")
			deleteSqlState(name, databaseName).then(res=>{
				if(res.code==0){
					insertSqlState(name, databaseName, tableField, saveData).then(res=>{
						console.log(res)
						if(res.code==0){
							console.log("保存数据成功")
							transactionDB(name, 'commit').then(res => {
								console.log('提交成功')
							})
						}else{
							transactionDB(name, 'rollback').then(res => {
								console.log('回滚成功')
							})
						}
					})
				}
			})
		})
	}, error => {
		console.log("执行事务开始失败")
		console.log(res)
	});
}

//查询数据
async function queryData(name, databaseName, field) {
	createDB(name); //打开数据库
	if (validDBOpen(name)) { //如果数据库打开
		let sql = checkSqlState(field, databaseName)
		let a = await selectSQL(name, sql)
		closeDB(name)
		return a;
	}
}

module.exports = {
	createDB,
	validDBOpen,
	closeDB,
	transactionDB,
	executeSQL,
	selectSQL,
	createTable,
	insertSqlState,
	deleteSqlState,
	updateSqlState,
	checkSqlState,
	saveData,
	queryData
}

2、constant.js

export default {
	dbName: "xazysoft.db",
	version: 1,
	Test: {
		fieldName: "Test",
		fieldHeader: ["id", "age", "name"],
		fieldType: ["INT(100)", "INT(100)", "CHAR(100)"]
	},
	Submit: {
		fieldName: "Submit",
		//主键ID,商户信息ID,商户信息Code,组织机构管理处ID,项目名称,
		//项目编号,项目地址,建筑面积,占地面积,绿化面积,
		//项目简介,项目照片,是否删除：0未删除、1已删除,创建时间,操作账户类型：0企业账户/1员工账户,
		//操作人id,备注,组织机构名称,操作人姓名,组织机构电话
		fieldHeader: ["id", "merchantId", "merchantCode", "groupId", "community",
			"number", "address", "buildArea", "floorArea", "greenArea",
			"intro", "picture", "remove", "createDate", "accountType",
			"operatorId", "remark", "`group`", "operatorName", "groupPhone","flag"
		],
		fieldType: ["INTEGER", "INTEGER", "TEXT", "INTEGER", "TEXT",
			"TEXT", "TEXT", "REAL", "REAL", "REAL",
			"TEXT", "TEXT", "INTEGER", "TEXT", "INTEGER",
			"INTEGER", "TEXT", "TEXT", "TEXT", "TEXT","INT(100)"
		]
	},
	// 测试所有小区数据
	testProject: {
		fieldName: "testProject",
		//主键ID,商户信息ID,商户信息Code,组织机构管理处ID,项目名称,
		//项目编号,项目地址,建筑面积,占地面积,绿化面积,
		//项目简介,项目照片,是否删除：0未删除、1已删除,创建时间,操作账户类型：0企业账户/1员工账户,
		//操作人id,备注,组织机构名称,操作人姓名,组织机构电话
		fieldHeader: ["id", "merchantId", "merchantCode", "groupId", "community",
			"number", "address", "buildArea", "floorArea", "greenArea",
			"intro", "picture", "remove", "createDate", "accountType",
			"operatorId", "remark", "`group`", "operatorName", "groupPhone"
		],
		fieldType: ["INTEGER", "INTEGER", "TEXT", "INTEGER", "TEXT",
			"TEXT", "TEXT", "REAL", "REAL", "REAL",
			"TEXT", "TEXT", "INTEGER", "TEXT", "INTEGER",
			"INTEGER", "TEXT", "TEXT", "TEXT", "TEXT"
		]
	},
	// 集团公司
	company: {
		fieldName: "company",
		//主键ID,商户信息Code,组织机构名称,组织机构编号,组织机构地址,
		//组织机构电话,组织机构类别(集团公司/分公司/管理处),父类ID,父类名称,创建日期,
		//操作账户类型（0企业账户/1员工账户）,操作人id,V6组织机构父级主键ID,V6组织机构主键ID,备注,
		//操作人姓名,小区信息
		fieldHeader: ["id", "merchantCode", "groupName", "number", "address",
			"phone", "category", "parentId", "father", "createDate",
			"accountType", "operatorId", "externalParentId", "externalId", "remark",
			"operatorName", "communities"
		],
		fieldType: ["INTEGER", "TEXT", "TEXT", "TEXT", "TEXT",
			"TEXT", "TEXT", "INTEGER", "TEXT", "TEXT",
			"INTEGER", "INTEGER", "TEXT", "TEXT", "TEXT",
			"TEXT", "TEXT"
		]
	},
	// 分公司
	filiale: {
		fieldName: "filiale",
		//主键ID,商户信息Code,组织机构名称,组织机构编号,组织机构地址,
		//组织机构电话,组织机构类别(集团公司/分公司/管理处),父类ID,父类名称,创建日期,
		//操作账户类型（0企业账户/1员工账户）,操作人id,V6组织机构父级主键ID,V6组织机构主键ID,备注,
		//操作人姓名,小区信息
		fieldHeader: ["id", "merchantCode", "groupName", "number", "address",
			"phone", "category", "parentId", "father", "createDate",
			"accountType", "operatorId", "externalParentId", "externalId", "remark",
			"operatorName", "communities"
		],
		fieldType: ["INTEGER", "TEXT", "TEXT", "TEXT", "TEXT",
			"TEXT", "TEXT", "INTEGER", "TEXT", "TEXT",
			"INTEGER", "INTEGER", "TEXT", "TEXT", "TEXT",
			"TEXT", "TEXT"
		]
	},
	// 管理处
	group: {
		fieldName: "group",
		//主键ID,商户信息Code,组织机构名称,组织机构编号,组织机构地址,
		//组织机构电话,组织机构类别(集团公司/分公司/管理处),父类ID,父类名称,创建日期,
		//操作账户类型（0企业账户/1员工账户）,操作人id,V6组织机构父级主键ID,V6组织机构主键ID,备注,
		//操作人姓名,小区信息
		fieldHeader: ["id", "merchantCode", "groupName", "number", "address",
			"phone", "category", "parentId", "father", "createDate",
			"accountType", "operatorId", "externalParentId", "externalId", "remark",
			"operatorName", "communities"
		],
		fieldType: ["INTEGER", "TEXT", "TEXT", "TEXT", "TEXT",
			"TEXT", "TEXT", "INTEGER", "TEXT", "TEXT",
			"INTEGER", "INTEGER", "TEXT", "TEXT", "TEXT",
			"TEXT", "TEXT"
		]
	},
	// 所有小区数据
	project: {
		fieldName: "project",
		//主键ID,商户信息ID,商户信息Code,组织机构管理处ID,项目名称,
		//项目编号,项目地址,建筑面积,占地面积,绿化面积,
		//项目简介,项目照片,是否删除：0未删除、1已删除,创建时间,操作账户类型：0企业账户/1员工账户,
		//操作人id,备注,组织机构名称,操作人姓名,组织机构电话
		fieldHeader: ["id", "merchantId", "merchantCode", "groupId", "community",
			"number", "address", "buildArea", "floorArea", "greenArea",
			"intro", "picture", "remove", "createDate", "accountType",
			"operatorId", "remark", "`group`", "operatorName", "groupPhone"
		],
		fieldType: ["INTEGER", "INTEGER", "TEXT", "INTEGER", "TEXT",
			"TEXT", "TEXT", "REAL", "REAL", "REAL",
			"TEXT", "TEXT", "INTEGER", "TEXT", "INTEGER",
			"INTEGER", "TEXT", "TEXT", "TEXT", "TEXT"
		]
	},
	//楼宇
	building: {
		fieldName: "building",
		//楼宇/公区ID,商户信息ID,商户信息Code,小区ID,组织机构ID,
		//楼宇/公区名称,楼宇/公区编号,楼层数,套户数,单元数,
		//建筑面积,占地面积,使用面积,是否公区(0否、1是),是否删除(0未删除、1已删除),
		//地理位置,创建日期,操作账户类型(0企业账户/1员工账户),操作人id,备注,
		//小区名称,操作人姓名,单元实体
		fieldHeader: ["id", "merchantId", "merchantCode", "groupId", "community",
			"number", "address", "buildArea", "floorArea", "greenArea",
			"intro", "picture", "remove", "createDate", "accountType",
			"operatorId", "remark", "`group`", "operatorName", "groupPhone"
		],
		fieldType: ["INTEGER", "INTEGER", "TEXT", "INTEGER", "INTEGER",
			"TEXT", "TEXT", "INTEGER", "INTEGER", "INTEGER",
			"REAL", "REAL", "REAL", "INTEGER", "INTEGER",
			"TEXT", "TEXT", "INTEGER", "INTEGER", "TEXT",
			"TEXT", "TEXT", "TEXT"
		]
	},
	//单元
	unit: {
		fieldName: "unit",
		//单元ID,商户信息ID,商户信息Code,单元名,楼宇ID,
		//是否删除(0未删除、1已删除),创建日期,操作账户类型(0企业账户/1员工账户),操作人id,备注
		fieldHeader: ["id", "merchantId", "merchantCode", "unitName", "buildingId",
			"remove", "createDate", "accountType", "operatorId", "remark"
		],
		fieldType: ["INTEGER", "INTEGER", "TEXT", "TEXT", "INTEGER",
			"INTEGER", "TEXT", "INTEGER", "INTEGER", "TEXT"
		]
	},
	//套户
	room: {
		fieldName: "room",
		//主键ID,商户信息编号,商户信息ID,单元ID,套户编号,
		//套户长编号,楼宇ID,楼层/片区ID,组织机构Id,项目ID,
		//计租面积,建筑面积,计费面积,套户户型,套户图片,
		//套户是否可租(0不可租、1可租),是否删除(0未删除、1已删除),套户使用状态(已租、未租、自住),创建日期,操作账户类型(0企业账户/1员工账户),
		//操作人id,备注,楼层名称,单元名称,楼宇名称,
		//项目名称,套户使用状态名称(已租、未租、自住),操作人姓名,业主ID集合
		fieldHeader: ["id", "merchantCode", "merchantId", "unitName", "buildingId",
			"remove", "createDate", "accountType", "operatorId", "remark"
		],
		fieldType: ["INTEGER", "TEXT", "INTEGER", "INTEGER", "TEXT",
			"TEXT", "INTEGER", "INTEGER", "INTEGER", "INTEGER",
			"REAL", "REAL", "REAL", "TEXT", "TEXT",
			"INTEGER", "INTEGER", "INTEGER", "TEXT", "INTEGER",
			"INTEGER", "TEXT", "TEXT", "TEXT", "TEXT",
			"TEXT", "TEXT", "TEXT", "TEXT"
		]
	},
	// 小区
	community: {
		fieldName: "community",
		//主键ID,商户信息ID,商户信息Code,组织机构管理处ID,项目名称,
		//项目编号,项目地址,建筑面积,占地面积,绿化面积,
		//项目简介,项目照片,是否删除：0未删除、1已删除,创建时间,操作账户类型：0企业账户/1员工账户,
		//操作人id,备注,组织机构名称,操作人姓名,组织机构电话
		fieldHeader: ["id", "merchantId", "merchantCode", "groupId", "community",
			"number", "address", "buildArea", "floorArea", "greenArea",
			"intro", "picture", "remove", "createDate", "accountType",
			"operatorId", "remark", "`group`", "operatorName", "groupPhone"
		],
		fieldType: ["INTEGER", "INTEGER", "TEXT", "INTEGER", "TEXT",
			"TEXT", "TEXT", "REAL", "REAL", "REAL",
			"TEXT", "TEXT", "INTEGER", "TEXT", "INTEGER",
			"INTEGER", "TEXT", "TEXT", "TEXT", "TEXT"
		]
	},
	// 所有部门数据
	department: {
		fieldName: "department",
		//主键ID,商户信息ID,部门名称,部门电话,组织机构管理处ID(可能是:集团公司/分公司/管理处),
		//项目ID(可能是:项目下的部门),父级部门ID,是否删除,创建时间,商户编码,
		//操作账户类型（0企业账户/1员工账户）,操作人id,备注,组织机构名称,组织机构类型 集团公司、分公司、管理处,
		//项目名称,子部门信息集合,操作人姓名
		fieldHeader: ["id", "merchantId", "deptName", "deptPhone", "groupId",
			"communityId", "parentId", "remove", "createDate", "merchantCode",
			"accountType", "operatorId", "remark", "groupName", "category",
			"community", "departments", "operatorName"
		],
		fieldType: ["INTEGER", "INTEGER", "TEXT", "TEXT", "INTEGER",
			"INTEGER", "INTEGER", "INTEGER", "TEXT", "TEXT",
			"INTEGER", "INTEGER", "TEXT", "TEXT", "TEXT",
			"TEXT", "TEXT", "TEXT"
		]
	},
	//所有人员数据
	employee: {
		fieldName: "employee",
		//主键ID,商户信息ID,商户编码,员工姓名,员工性别,
		//联系电话,身份证号,组织机构Id,所属部门ID,岗位ID,
		//民族,学历,籍贯,婚姻状况（Code：未婚、已婚、丧偶）,员工状态（Code：在职、离职、请假、休假）,
		//是否在岗,员工属性（0内部员工、1外部员工）,是否删除,头像地址,创建时间,
		//操作账户类型(0企业账户/1员工账户),操作人id,备注,组织机构名称,部门名称,
		//岗位名称,婚姻状况名称,员工状态名称,操作人姓名,特殊字段(导入时使用,可以存组织机构名也可以存项目名)
		fieldHeader: ["id", "merchantId", "merchantCode", "employeeName", "sex",
			"phone", "idCard", "groupId", "deptId", "postId",
			"nation", "education", "nationality", "marriageStatus", "stateCode",
			"inPost", "property", "remove", "headUrl", "createDate",
			"accountType", "operatorId", "remark", "groupName", "deptName",
			"postName", "marriageStatusName", "stateCodeName", "operatorName", "organizationName"
		],
		fieldType: ["INTEGER", "INTEGER", "TEXT", "TEXT", "INTEGER",
			"TEXT", "TEXT", "INTEGER", "INTEGER", "INTEGER",
			"TEXT", "TEXT", "TEXT", "INTEGER", "INTEGER",
			"INTEGER", "INTEGER", "INTEGER", "TEXT", "TEXT",
			"INTEGER", "INTEGER", "TEXT", "TEXT", "TEXT",
			"TEXT", "TEXT", "TEXT", "TEXT", "TEXT",
		]
	},
	//故障种类
	orderType: {
		fieldName: "orderType",
		//主键ID,商户编码,管理处ID,故障类型,是否删除(0未删除、1已删除),
		//创建时间,备注
		fieldHeader: ["id", "merchantCode", "groupId", "faultType", "remove",
			"createDate", "remark"
		],
		fieldType: ["INTEGER", "TEXT", "INTEGER", "TEXT", "INTEGER",
			"TEXT", "TEXT"
		]
	}
};

3、downloads.js

import Vue from 'vue'
const app = new Vue()
import {
	saveData,
	daleteTable
} from "@/common/js/sqlite.js"
import constant from '@/common/js/constant.js'
// 请求项目（小区）
export async function testLoad() {
	
	try {
		let data = await app.$u.get(app.$api.cache.getProject + app.$SysCache.get('loginInfo').loginData.employeeId);
			saveData(constant.dbName, constant.testProject.fieldName, constant.testProject.fieldHeader, constant.testProject.fieldType, data.data)
		
	} catch (e) {
		// daleteTable(constant.dbName, constant.haha.fieldName)
	}
}
export async function downloadProject() {
	
	try {
		let data = await app.$u.get(app.$api.cache.getProject + app.$SysCache.get('loginInfo').loginData.employeeId);
			// saveData(constant.dbName, constant.project.fieldName, constant.project.fieldHeader, constant.project.fieldType, data.data)
		
	} catch (e) {
		// daleteTable(constant.dbName, constant.haha.fieldName)
	}
}
//请求部门
export async function downloadDepartment() {
	try {
		let data = await app.$u.get(app.$api.address.getDept + app.$SysCache.get('loginInfo').loginData.merchantId);
		console.log(data)
		// saveData(constant.dbName, constant.department.fieldName, constant.department.fieldHeader, constant.department.fieldType, data.data)
	} catch (e) {
		// daleteTable(constant.dbName, constant.department.fieldName)
	}
}
//请求在职在岗人员
export async function downloadEmployee() {
	try {
		let data = await app.$u.get(app.$api.address.getEmployee +'?merchantId='+ app.$SysCache.get('loginInfo').loginData.merchantId);
		console.log(data)
		// saveData(constant.dbName, constant.employee.fieldName, constant.employee.fieldHeader, constant.employee.fieldType, data.data)
	} catch (e) {
		console.log(e)
		// daleteTable(constant.dbName, constant.employee.fieldName)
	}
}

 集团公司->分公司->管理处(这三层都是组织机构)
 组织机构/小区/楼宇/单元/套户

//请求集团公司
export async function downloadCompany() {
	try {
		let data = await app.$u.get(app.$api.cache.getCompanys + app.$SysCache.get('loginInfo').loginData.merchantCode);
		console.log(data)
		// saveData(constant.dbName, constant.company.fieldName, constant.company.fieldHeader, constant.company.fieldType, data.data)
	} catch (e) {
		console.log(e)
		// daleteTable(constant.dbName, constant.company.fieldName)
	}
}
//请求分公司
export async function downloadFiliale() {
	try {
		let data = await app.$u.get(app.$api.cache.getFiliale + app.$SysCache.get('loginInfo').loginData.merchantCode);
		console.log(data)
		// saveData(constant.dbName, constant.filiale.fieldName, constant.filiale.fieldHeader, constant.filiale.fieldType, data.data)
	} catch (e) {
		console.log(e)
		// daleteTable(constant.dbName, constant.filiale.fieldName)
	}
}
//请求管理处
export async function downloadGroup() {
	try {
		let data = await app.$u.get(app.$api.cache.getGroup + app.$SysCache.get('loginInfo').loginData.merchantCode);
		console.log(data)
		// saveData(constant.dbName, constant.group.fieldName, constant.group.fieldHeader, constant.group.fieldType, data.data)
	} catch (e) {
		console.log(e)
		// daleteTable(constant.dbName, constant.group.fieldName)
	}
}
//请求小区
export async function downloadCommunity() {
	try {
		let data = await app.$u.get(app.$api.cache.getCommunity + app.$SysCache.get('loginInfo').loginData.merchantCode);
		console.log(data)
		// saveData(constant.dbName, constant.community.fieldName, constant.community.fieldHeader, constant.community.fieldType, data.data)
	} catch (e) {
		console.log(e)
		// daleteTable(constant.dbName, constant.community.fieldName)
	}
}
//请求楼宇
export async function downloadBuilding() {
	try {
		let data = await app.$u.get(app.$api.cache.getBuilding + app.$SysCache.get('loginInfo').loginData.merchantCode);
		console.log(data)
		// saveData(constant.dbName, constant.building.fieldName, constant.building.fieldHeader, constant.building.fieldType, data.data)
	} catch (e) {
		console.log(e)
		// daleteTable(constant.dbName, constant.building.fieldName)
	}
}
//请求单元
export async function downloadUnit() {
	try {
		let data = await app.$u.get(app.$api.cache.getUnit + app.$SysCache.get('loginInfo').loginData.merchantCode);
		console.log(data)
		// saveData(constant.dbName, constant.unit.fieldName, constant.unit.fieldHeader, constant.unit.fieldType, data.data)
	} catch (e) {
		console.log(e)
		// daleteTable(constant.dbName, constant.unit.fieldName)
	}
}
//请求套户
export async function downloadRoom() {
	try {
		let data = await app.$u.get(app.$api.cache.getRoom + app.$SysCache.get('loginInfo').loginData.merchantCode);
		console.log(data)
		// saveData(constant.dbName, constant.room.fieldName, constant.room.fieldHeader, constant.room.fieldType, data.data)
	} catch (e) {
		console.log(e)
		// daleteTable(constant.dbName, constant.room.fieldName)
	}
}
//请求故障类型
export async function downloadOrderType() {
	try {
		let data = await app.$u.get(app.$api.workorder.orderType+app.$SysCache.get('currentComInfo').groupId+"/"+app.$SysCache.get('loginInfo').loginData.merchantCode);
		console.log(data)
		// saveData(constant.dbName, constant.orderType.fieldName, constant.orderType.fieldHeader, constant.orderType.fieldType, data.data)
	} catch (e) {
		// console.log(e)
		// daleteTable(constant.dbName, constant.orderType.fieldName)
	}
}

4、test.vue

<template>
	<view class="" style="margin-top: 40px;">
		<u-button type="primary" @click="chuangjian">创建数据库</u-button>
		<u-button type="success" @click="jiancha">检查是否打开</u-button>
		<u-button type="warning" @click="guanbi">关闭数据库</u-button>
		<u-button type="error" @click="kaishi">开始事务</u-button>
		<u-button type="primary" @click="xieru">写入</u-button>
		<u-field v-model="shuru" label="输入框" placeholder="输入框" />
		<u-button type="success" @click="chaxun">查询</u-button>
		<!-- 	<u-button type="warning" @click="shanchu">删除</u-button>
		<u-button type="error" @click="xiugai">修改</u-button> -->
		<!-- <u-button type="error" @click="queryTable">查询数据</u-button> -->
		<u-button type="error" @click="deleteTable">删除表</u-button>
		<u-button type="error" @click="insertData">插入数据</u-button>
		<u-button type="error" @click="updateData">修改数据</u-button>
		<u-button type="error" @click="deleteData">删除数据</u-button>
		<u-button type="primary" @click="operateData">操作数据</u-button>
		<u-button type="success" @click="getOfflineData">查询待提交的离线数据</u-button>
	</view>
</template>
<script>
	import {
		createDB,
		validDBOpen,
		closeDB,
		transactionDB,
		executeSQL,
		selectSQL,
		createTable,
		insertSqlState,
		deleteSqlState,
		updateSqlState,
		checkSqlState,
		queryData
	} from '@/common/js/sqlite.js';
	//下载数据
	import {
		testLoad,
		downloadCompany,
		downloadFiliale,
		downloadGroup,
		downloadCommunity,
		downloadBuilding,
		downloadUnit,
		downloadRoom,
		downloadProject,
		downloadDepartment,
		downloadEmployee,
		downloadOrderType
	} from "@/common/js/downloads.js"
	import constant from '@/common/js/constant.js'
	export default {
		data() {
			return {
				shuru: "",
				test: [{
						"id": 1,
						"age": 4,
						"name": "李易峰"
					},
					{
						"id": 2,
						"age": 4,
						"name": "张峰"
					},
					{
						"id": 3,
						"age": 3,
						"name": "王一博"
					}
				]
			}
		},
		computed: {},
		onLoad() {
			this.isOpenDB();
			// uni.onNetworkStatusChange((res) => {
			// 	//监听网络  
			// 	console.log('MIXIN 下监听网络');
			// 	// console.log(res.isConnected);  
			// 	// console.log(res.networkType);  
			// 	if (res.networkType == 'none') {
			// 		console.log('无网络');
			// 		this.insertData()
			// 		// 获取离线数据 
			// 	} else if (res.networkType == 'wifi' || res.networkType == '4g') {
			// 		console.log('wifi');
			// 		// 有网的情况下先提交离线数据
			// 		// 再更新本地数据库
			// 		this.xieru()
			// 		//切换到有网络时，需要查看是否有离线数据，并进行提交。  
			// 		this.getOfflineData(); //查询是否有离线数据  
			// 	}
			// })
			this.getNetworkType()
		},
		methods: {
			loadData() {
				let requestList = [
					testLoad()
				]
				return new Promise((resolve, reject) => {
					return Promise.all(requestList).then(res => {
						resolve(res);
					}).catch(err => {
						reject(err);
					})
				})
			},
			getNetworkType() {
				//获取网络信息  
				uni.getNetworkType({
					success: res => {
						if (res.networkType == 'none') {
							console.log('无网络');
							// this.insertData()
							// 获取离线数据 
						} else if (res.networkType == 'wifi' || res.networkType == '4g') {
							console.log('wifi');
							// 有网的情况下先提交离线数据
							// 再更新本地数据库
							this.loadData().then(res => {
								console.log("执行成功")
								console.log(res)
							}, error => {
								console.log(error)
							})
							//切换到有网络时，需要查看是否有离线数据，并进行提交。  
							this.getOfflineData(); //查询是否有离线数据  
						}
					}
				})
			},
			//查询是否有缓存
			getOfflineData() {
				//查询是否有离线写入，未提交数据； flag == 0  false。  
				// console.log("切换至网络，查询是否有缓存未提交数据");  
				var sql = `SELECT * FROM ${constant.Submit.fieldName}  WHERE  flag = 'false' `
				console.log(sql)
				selectSQL(constant.dbName, sql).then(res => {
					console.log(res)
					console.log(res.length)
					for (var i = 0; i < res.length; i++) {
						let val = this.submitData();
						console.log(val)
						if (val) {
							//更新本地数据库待提交数据状态
							this.updatePointStatus(res[i].id)
						} else {
							//重新提交
						}
					}
					//调用后端的接口提交数据
				}, res => {
					console.log(JSON.stringify(res))
					console.log(res.length)
				})
			},
			submitData() {
				let val = true;
				// setTimeout(()=>{
				// },1000)
				return val
			},
			updatePointStatus(curId) {
				// 切换至有网络后，提交成功后，更新已提交成功数据 更新巡检点状态  
				//修改flag = 1  - true ；  
				var updateSQL = `UPDATE ${constant.Submit.fieldName} SET  flag = 'true' WHERE  id = ${curId}`
				executeSQL(constant.dbName, updateSQL)
			},
			//打开数据库
			isOpenDB() {
				var isOpen = validDBOpen(constant.dbName)
				console.log("数据库是否打开：" + !isOpen);
				if (!isOpen) {
					console.log('unopen:' + isOpen)
					createDB(constant.dbName).then((res) => {
						console.log(res)
					}, (error) => {
						console.log(error)
					})
				}
			},
			deleteTable() {
				let sql = "drop table if exists Test";
				executeSQL(constant.dbName, sql)
			},
			queryTable() {
				let sql = "show tables";
				executeSQL(constant.dbName, sql)
			},

			//打开数据库
			chuangjian() {
				// createDB(constant.dbName)
				// console.log(constant.dbName)
				createDB(constant.dbName).then((res) => {
				
				}, (error) => {

				})
			},
			//修改数据
			updateData() {
				// Update 语句用于修改表中的数据。
				// UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值 （更新某一行中的一个列）
				// 将id等于2的人的年龄修改为12 
				// var sql = `UPDATE ${constant.Test.fieldName} SET  age = 12 WHERE  id = 2`
				// 更新某一行中的若干列
				// 我们会修改地址（address），并添加城市名称（city）：
				// UPDATE Person SET Address = 'Zhongshan 23', City = 'Nanjing'
				// WHERE LastName = 'Wilson'
				var sql = `UPDATE ${constant.Test.fieldName} SET  age = 3, name = 'lili' WHERE  id = 2`
				console.log(sql)
				executeSQL(constant.dbName, sql)
			},
			//插入数据
			insertData() {
				// INSERT INTO 表名称 VALUES (值1, 值2,....)
				createTable(constant.dbName, constant.Submit.fieldName, constant.Submit.fieldHeader, constant.Submit.fieldType)
				deleteSqlState(constant.dbName, constant.Submit.fieldName)
				let obj ={
					"id":"6714724409665388544",
					"merchantId":"6659366380980142080",
					"merchantCode":"6103260000",
					"groupId":"6714724234423173120",
					"community":"人大测试",
					"number":"xianxiangmu2",
					"address":"星光大道",
					"buildArea":"0",
					"floorArea":'',
					"greenArea":'',
					"intro":'',
					"picture":'',
					"remove":false,
					"createDate":"2020-11-12 10:34:32",
					"accountType":false,
					"operatorId":'6659366380980142080',
					"remark":'',
					"group":'',
					"operatorName":'',
					"groupPhone":'',
					"flag":0
				}
				// insertSqlState(name, databaseName, tableField, saveData)
				insertSqlState(constant.dbName, constant.Submit.fieldName, constant.Submit.fieldHeader,obj)
				// var sql = `INSERT INTO ${constant.Test.fieldName} VALUES (${obj.id}, ${obj.age }, '${obj.name}')`
				// var sql = `INSERT INTO ${constant.Submit.fieldName} VALUES (${obj.id}, ${obj.age }, '${obj.name}','${obj.flag}')`
				// // var sql = `INSERT INTO ${constant.Test.fieldName} VALUES (5,18,'sfdsfd')`
				// console.log(sql)
				// executeSQL(constant.dbName, sql)
				// insertSqlState(constant.dbName, constant.Test.fieldName, constant.Test.fieldHeader, this.ceshishuju)
			},
			deleteData() {
				// DELETE 语句用于删除表中的行。
				// DELETE FROM 表名称 WHERE 列名称 = 值
				// 删除所有行
				// DELETE FROM table_name
				// var sql = `DELETE FROM ${constant.Test.fieldName} WHERE id = '1'`
				var sql = `DELETE FROM  ${constant.Test.fieldName}`
				console.log(sql)
				executeSQL(constant.dbName, sql)
			},
			//操作数据
			operateData() {
				//查询固定数量的数据
				//sqlite不支持top 使用select * from aa order by ids desc LIMIT 2
				// SELECT Company, OrderNumber FROM Orders ORDER BY Company
				// 例：select * from table where name='Xiao ming' order by id limit 0,5;
				// 意思为查找table表里，字段name为Xiao ming的记录 根据字段id显示0到5条记录；
				// 当然还有需求更为严格的：
				// 例：select * from table where name='Xiao ming' order by date desc,id limit 0,5;
				// 这条语句的意思为根据条件找到的0到5条记录然后根据字段date 去倒序排列；
				//  例：select * from table order by id desc limit 0,5;
				// 意思为根据id 找到0-5条记录并倒序排列：
				// （1）var sql =`SELECT * FROM  ${constant.Test.fieldName} LIMIT 2`  //按顺序查询返回
				// (2)从第三位开始提取 3 个记录（意思是前两个不算，实现分页查询）  SELECT * FROM 表名 LIMIT （当前需要查询的数量）3 OFFSET  （查询起始位置）2
				// var sql =`SELECT * FROM ${constant.Test.fieldName} LIMIT 2 OFFSET 1`
				// (3) ORDER BY SALARY ASC(升序)  ORDER BY NAME DESC（降序）
				// var sql=`select * from ${constant.Test.fieldName} order by id desc LIMIT 2 OFFSET 0`

				//SQLite 的 DISTINCT 关键字与 SELECT 语句一起使用，来消除所有重复的记录，并只获取唯一一次记录。
				// (1)SELECT DISTINCT column1, column2,.....columnN FROM table_name WHERE [condition]
				// let condition=
				// var sql=`SELECT DISTINCT name FROM ${constant.Test.fieldName}`
				// LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。(实现模糊查询)
				// SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern
				// var sql=`SELECT * FROM ${constant.Test.fieldName} WHERE name LIKE '%王%' `
				// var sql=`SELECT * FROM ${constant.Test.fieldName} WHERE name NOT LIKE '%王%' `
				// var sql=`SELECT * FROM ${constant.Test.fieldName} WHERE name  LIKE '%王' `
				// var sql=`SELECT * FROM ${constant.Test.fieldName} WHERE name  LIKE '王%' `
				var sql = `SELECT * FROM ${constant.Test.fieldName} WHERE  name  LIKE '%[峰]%' `
				console.log(sql)
				selectSQL(constant.dbName, sql).then(res => {
					console.log(res)
					console.log(res.length)
				}, res => {
					console.log(JSON.stringify(res))
					console.log(res.length)
				})
			},
			//检查数据库是否打开,返回boolean
			jiancha() {
				console.log(validDBOpen(constant.dbName))
			},
			//关闭数据库
			guanbi() {
				closeDB(constant.dbName)
			},
			//数据库开启事务  begin（开始事务）、commit（提交）、rollback（回滚）
			kaishi() {
				transactionDB(constant.dbName, "begin")
			},
			//执行增加操作
			xieru() {
				createTable(constant.dbName, constant.Test.fieldName, constant.Test.fieldHeader, constant.Test.fieldType)
				deleteSqlState(constant.dbName, constant.Test.fieldName)
				insertSqlState(constant.dbName, constant.Test.fieldName, constant.Test.fieldHeader, this.test)
			},
			//查询
			chaxun() {
				// var sql=checkSqlState(["*"], constant.Test.fieldName, "id=1")
				// var sql = checkSqlState(["*"], constant.Test.fieldName)
				// var sql = checkSqlState(["*"], 'projectTest')
				// var sql = checkSqlState(["*"],constant.Test.fieldName)
				// let condition =" name = 'wangdan'" //查询name为王丹的人
				// let condition =" id = '3'" //查询id
				// select用于查询数据
				//update用于
				// SELECT * FROM Persons WHERE (FirstName='Thomas' OR FirstName='William')
				// AND LastName='Carter'
				// let condition =" id = '1'  or   age = '4'" 
				// let condition =" id = '1'  And   age = '4'"  
				// ORDER BY 语句用于对结果集进行排序。
				// INSERT INTO 语句
				// INSERT INTO 表名称 VALUES (值1, 值2,....)
				// var sql = checkSqlState(['*'], constant.Test.fieldName,condition )
				console.log("sfdsf")
				var sql = `SELECT * FROM  ${constant.Test.fieldName}`
				console.log(sql)
				selectSQL(constant.dbName, sql).then(res => {
					console.log(res)
					console.log(res.length)
				}, res => {
					console.log(JSON.stringify(res))
					console.log(res.length)
				})
			},
			//执行删除操作
			shanchu() {
				createTable(constant.dbName, constant.Test.fieldName, constant.Test.fieldHeader, constant.Test.fieldType)
				deleteSqlState(constant.dbName, constant.Test.fieldName)
			},
			//修改
			xiugai() {
				createTable(constant.dbName, constant.Test.fieldName, constant.Test.fieldHeader, constant.Test.fieldType)
				updateSqlState(constant.dbName, constant.Test.fieldName, "name='才华',age=10", "id=1")
			}
		}
	}
</script>
<style>
</style>