pms-app/utils/sqlite.js

var dbName = ''
var dbPath = ''
var dbFilePath = ''

const andStr = ' and '
const orStr = ' or '
const kongStr = ' '
const douStr = ','

// 事务操作
const transactionBeginStr = 'begin'
const transactionCommitStr = 'commit'
const transactionRollbackStr = 'rollback'

const dbNameKey = 'ljw_db_name_sqlite_key'
const dbFilePathKey = 'ljw_db_file_path_sqlite_key'

/**
 * 初始化和开启数据库
 */
export const initDB = async (name) => {
	// 获取应用操作路径
	const dbDir = await new Promise((resolve, reject) => {
		plus.io.requestFileSystem(plus.io.PRIVATE_DOC, res => {
			console.log('requestFileSystem--res--' + res)
			res.root.getDirectory('db', {
				create: true
			}, res => {
				console.log('getDirectory--res--' + res.fullPath)
				resolve(res.fullPath)
			}, rej => {
				console.error('getDirectory--' + rej)
				reject(rej)
			});
		}, rej => {
			console.error('requestFileSystem--' + rej)
			reject(rej)
		})
	})
	console.log('dbDir--', dbDir)

	// 保存路劲
	dbName = name
	dbPath = dbDir
	dbFilePath = dbPath + dbName + '.db'
	// 存储一次
	uni.setStorageSync(dbNameKey, dbName)
	uni.setStorageSync(dbFilePathKey, dbFilePath)

	const isOpen = plus.sqlite.isOpenDatabase({
		name: dbName,
		path: dbFilePath
	});
	if (!isOpen) {
		// 开启数据库
		const status = await new Promise((resolve, reject) => {
			plus.sqlite.openDatabase({
				name: dbName,
				path: dbFilePath,
				success: function(e) {
					console.log('数据库成功开启');
					resolve(true)
				},
				fail: function(e) {
					console.error('数据库开启失败--', JSON.stringify(e));
					reject(e)
				}
			});
		})
		return status
	} else {
		console.log('数据库已是开启状态！')
		return true
	}
}
/**
 * 关闭数据库
 */
export const closeDB = (db_name = '') => {
	return new Promise((resolve, reject) => {
		plus.sqlite.closeDatabase({
			name: dbNameBack(db_name),
			success: function(res) {
				console.log('数据库关闭ok')
				resolve(true)
			},
			fail: function(e) {
				if (e.message.includes('Not Open')) {
					resolve(true)
				} else {
					console.error('数据库关闭fail', e)
				}
			}
		})
	})
}
/**
 * 开启数据库
 */
export const openDB = async (db_name = '', db_FilePath = '') => {
	const isOpen = plus.sqlite.isOpenDatabase({
		name: dbNameBack(db_name),
		path: dbFilePathBack(db_FilePath)
	});
	if (!isOpen) {
		// 开启数据库
		const status = await new Promise((resolve, reject) => {
			plus.sqlite.openDatabase({
				name: dbNameBack(db_name),
				path: dbFilePathBack(db_FilePath),
				success: function(e) {
					console.log('数据库成功开启');
					resolve(true)
				},
				fail: function(e) {
					console.error('数据库开启失败--', JSON.stringify(e));
					reject(e)
				}
			});
		})
		return status
	} else {
		console.log('数据库已是开启状态！')
		return true
	}
}
/**
 * 新建表  开启数据库后，如果想立即执行，则需要放到initDB 回调函数 或者 使用await
*
`CREATE TABLE if not exists ${EPsqlite.FA_QUESTION} (
id INTEGER PRIMARY KEY AUTOINCREMENT,
template_id INTEGER NOT NULL DEFAULT 0,
appuser_id INTEGER DEFAULT 0,
status TEXT DEFAULT '1'
)`
 */
export const createTable = (sql, db_name = '') => {
	return new Promise((resolve, reject) => {
		plus.sqlite.executeSql({
			name: dbNameBack(db_name),
			sql: sql,
			success: function(e) {
				console.log('createTable success!');
				resolve(true)
			},
			fail: function(e) {
				console.log('createTable failed: ' + JSON.stringify(e));
				reject(e)
			}
		});
	})
}
/**
 * 插入数据
 */
export const insertData = (tableName, datas, db_name = '') => {
	return new Promise((resolve, reject) => {
		plus.sqlite.executeSql({
			name: dbNameBack(db_name),
			sql: disponseInsertData(tableName, datas),
			success: function(e) {
				console.log('insertData success!');
				resolve(e)
			},
			fail: function(e) {
				console.log('insertData failed: ' + JSON.stringify(e));
				reject(e)
			}
		});
	})
}
/**
 * 查询数据
 * order by xx desc/asc
 * group by xx
 */
export const queryData = (tableName, where, suffixSql = '', db_name = '') => {
	return new Promise((resolve, reject) => {
		plus.sqlite.selectSql({
			name: dbNameBack(db_name),
			sql: disponseQueryData(tableName, where) + kongStr + suffixSql,
			success: function(e) {
				console.log('queryData success!', e);
				resolve(sucData(e))
			},
			fail: function(e) {
				console.log('queryData failed: ' + JSON.stringify(e));
				reject(failData(e.code, e.message))
			}
		});
	})
}
/**
 * 查询数据page
 * order by xx desc/asc
 * group by xx
 */
export const queryDataPage = (tableName, where = {}, suffixSql = '', db_name = '') => {
	return new Promise((resolve, reject) => {
		queryDataCount(tableName, where).then(res => {
			let total = res.data
			let pageNo = where.pageNo || 1
			let pageSize = where.pageSize || 10
			let index = (pageNo - 1) * pageSize
			plus.sqlite.selectSql({
				name: dbNameBack(db_name),
				sql: disponseQueryData(tableName, where) + kongStr + suffixSql + kongStr +
					`limit ${index},${pageSize}`,
				success: function(e) {
					console.log('queryDataPage success!', e);
					resolve(sucData(e, total))
				},
				fail: function(e) {
					console.log('queryDataPage failed: ' + JSON.stringify(e));
					reject(failData(e.code, e.message))
				}
			});
		})
	})
}
/**
 * 查询数据总数
 */
export const queryDataCount = (tableName, where, db_name = '') => {
	return new Promise((resolve, reject) => {
		plus.sqlite.selectSql({
			name: dbNameBack(db_name),
			sql: disponseQueryDataCount(tableName, where),
			success: function(e) {
				console.log('queryDataPage success!', e);
				resolve(sucData(e[0].total))
			},
			fail: function(e) {
				console.log('queryDataPage failed: ' + JSON.stringify(e));
				reject(failData(e.code, e.message))
			}
		});
	})
}
/**
 * 查询数据原味sql
 */
export const selectSqlData = (where, db_name = '') => {
	return new Promise((resolve, reject) => {
		plus.sqlite.selectSql({
			name: dbNameBack(db_name),
			sql: where,
			success: function(e) {
				console.log('selectSqlData success!', e);
				resolve(sucData(e))
			},
			fail: function(e) {
				console.log('selectSqlData failed: ' + JSON.stringify(e));
				reject(failData(e.code, e.message))
			}
		});
	})
}
/**
 * 执行增删改等操作语句数据原味sql
 * 清空表数据 同时清空了自增id TRUNCATE TABLE tableName;
 * 删除表 DROP TABLE IF EXISTS tableName;
 */
export const executeSqlData = (where, db_name = '') => {
	return new Promise((resolve, reject) => {
		plus.sqlite.executeSql({
			name: dbNameBack(db_name),
			sql: where,
			success: function(e) {
				console.log('executeSqlData success!', e);
				resolve(sucData(e))
			},
			fail: function(e) {
				console.log('executeSqlData failed: ' + JSON.stringify(e));
				reject(failData(e.code, e.message))
			}
		});
	})
}
/**
 * 更新数据
 */
export const updateData = (tableName, update, where, db_name = '') => {
	return new Promise((resolve, reject) => {
		plus.sqlite.executeSql({
			name: dbNameBack(db_name),
			sql: disponseUpdateData(tableName, update, where),
			success: function(e) {
				console.log('updateData success!', e);
				resolve(sucData(e))
			},
			fail: function(e) {
				console.log('updateData failed: ' + JSON.stringify(e));
				reject(failData(e.code, e.message))
			}
		});
	})
}
/**
 * 删除数据
 */
export const deleteData = (tableName, where, db_name = '') => {
	return new Promise((resolve, reject) => {
		plus.sqlite.executeSql({
			name: dbNameBack(db_name),
			sql: disponseDeleteData(tableName, where),
			success: function(e) {
				console.log('deleteData success!', e);
				resolve(sucData(e))
			},
			fail: function(e) {
				console.log('deleteData failed: ' + JSON.stringify(e));
				reject(failData(e.code, e.message))
			}
		});
	})
}
/**
 * 检查表是否存在
 */
export const checkTable = (tableName, db_name = '') => {
	return new Promise((resolve, reject) => {
		plus.sqlite.selectSql({
			name: dbNameBack(db_name),
			sql: 'select name from sqlite_master where type="table" and name=' + packageStr(tableName),
			success: function(e) {
				console.log('checkTable success!', e);
				resolve(true)
			},
			fail: function(e) {
				console.log('checkTable failed: ' + JSON.stringify(e));
				reject(failData(e.code, e.message))
			}
		});
	})
}
/**
 * 包装执行事务：支持同步/异步回调
 * 		packageTransaction(()=>{
			insertDataLJW('fa_user', {name:'wxyqaz'})
		})
 */
export const packageTransaction = async (callback, db_name = '') => {
    if (typeof callback !== 'function') {
        console.error('callback 必须是一个函数');
        return;
    }
    try {
        // 开启事务
        await transactionBegin(db_name);
        // 执行回调（支持异步）
        const result = await callback();
        // 提交事务
        await transactionCommit(db_name);
		if (objectIsEmpty(result)) {
			result = true
		}
        console.log('packageTransaction -- 事务提交成功', result);
        return result;
    } catch (error) {
        // 回滚事务
        await transactionRollback(db_name);
        console.error('事务执行失败，已回滚。错误内容:', error);
        // 可选：重新抛出错误供外部处理
        throw error;
    }
};
/**
 * -------------------------------------------------------------------------------------------------------------------------------------------------
 * * -------------------------------------------------------------------------------------------------------------------------------------------------
 * * -------------------------------------------------------------------------------------------------------------------------------------------------
 * * -------------------------------------------------------------------------------------------------------------------------------------------------
 * * -------------------------------------------------------------------------------------------------------------------------------------------------
 */
const packageStr = (str)=>{
	return '"' + str + '"'
}
const transactionBegin = (db_name = '') => {
	return new Promise((resolve, reject) => {
		// 开启事务
		plus.sqlite.transaction({
			name: dbNameBack(db_name),
			operation: transactionBeginStr,
			success: function(e) {
				console.log('transaction begin success!', e);
				resolve(e)
			},
			fail: function(e) {
				console.log('transaction begin failed: ' + JSON.stringify(e));
				reject(failData(e.code, e.message))
			}
		});
	})
}
const transactionCommit = (db_name = '') => {
	return new Promise((resolve, reject) => {
		// 提交事务
		plus.sqlite.transaction({
			name: dbNameBack(db_name),
			operation: transactionCommitStr,
			success: function(e) {
				console.log('transaction commit success!', e);
				resolve(e)
			},
			fail: function(e) {
				console.log('transaction commit failed: ' + JSON.stringify(e));
				reject(failData(e.code, e.message))
			}
		});
	})
}
const transactionRollback = (db_name = '') => {
	return new Promise((resolve, reject) => {
		// 回滚事务
		plus.sqlite.transaction({
			name: dbNameBack(db_name),
			operation: transactionRollbackStr,
			success: function(e) {
				console.log('transaction rollback success!', e);
				resolve(e)
			},
			fail: function(e) {
				console.log('transaction rollback failed: ' + JSON.stringify(e));
				reject(failData(e.code, e.message))
			}
		});
	})
}
const disponseInsertData = (tableName, datas) => {
	if (Array.isArray(datas)) {
		let arr = [...datas]
		var sql = 'insert into ' + tableName + ' (' + Object.keys(arr[0]).join(',') + ') values '
		var valueSql = ''
		for (const map of arr) {
			valueSql += '('
			for (const key of Object.keys(map)) {
				const value = map[key]
				if (typeof value == 'string') {
					valueSql += '"' + value + '"' + ','
				} else {
					valueSql += value + ','
				}
			}
			valueSql = valueSql.substring(0, valueSql.length - 1) + '),'
		}
		sql += valueSql.substring(0, valueSql.length - 1)
		return sql
	} else {
		let map = {
			...datas
		}
		var sql = 'insert into ' + tableName + ' (' + Object.keys(map).join(',') + ') values ('
		for (const key of Object.keys(map)) {
			const value = map[key]
			if (typeof value == 'string') {
				sql += '"' + value + '"' + ','
			} else {
				sql += value + ','
			}
		}
		sql = sql.substring(0, sql.length - 1) + ')'
		return sql
	}
}
const disponseDeleteData = (tableName, where) => {
	var sql = `delete from ${tableName} where `
	if (typeof where == 'string') {
		sql += where
	} else {
		let map = {
			...where
		}
		var valueSql = ''
		for (const key of Object.keys(map)) {
			const value = map[key]
			valueSql += key + '='
			if (typeof value == 'string') {
				valueSql += '"' + value + '"' + andStr
			} else {
				valueSql += value + andStr
			}
		}
		valueSql = valueSql.substring(0, valueSql.length - andStr.length)
		sql += valueSql
	}
	return sql
}
const disponseUpdateData = (tableName, update, where) => {
	var sql = `update ${tableName} set `
	if (typeof update == 'string') {
		sql += update
	} else {
		let map = {
			...update
		}
		var valueSql = ''
		for (const key of Object.keys(map)) {
			const value = map[key]
			valueSql += key + '='
			if (typeof value == 'string') {
				valueSql += '"' + value + '"' + douStr
			} else {
				valueSql += value + douStr
			}
		}
		valueSql = valueSql.substring(0, valueSql.length - douStr.length)
		sql += valueSql
	}

	if (!objectIsEmpty(where)) {
		sql += kongStr + 'where' + kongStr

		if (typeof where == 'string') {
			sql += where
		} else {
			let map = {
				...where
			}
			var valueSql = ''
			for (const key of Object.keys(map)) {
				const value = map[key]
				valueSql += key + '='
				if (typeof value == 'string') {
					valueSql += '"' + value + '"' + andStr
				} else {
					valueSql += value + andStr
				}
			}
			valueSql = valueSql.substring(0, valueSql.length - andStr.length)
			sql += valueSql
		}
	}
	return sql
}
const disponseQueryData = (tableName, where) => {
	return disponseQuerySql(tableName, where)
}
const disponseQueryDataCount = (tableName, where) => {
	return disponseQuerySql(tableName, where, 'count(1) as total')
}
const disponseQuerySql = (tableName, where, sel = '*') => {
	var sql = `select ${sel} from ` + tableName
	if (objectIsEmpty(where)) {
		return sql
	}
	if (typeof where == 'string') {
		sql += kongStr + 'where' + kongStr + where
	} else {
		let map = {
			...where
		}
		var valueSql = ''
		for (const key of Object.keys(map)) {
			if (key === 'pageNo' || key === 'pageSize') {
				continue
			}
			const value = map[key]
			valueSql += key + '='
			if (typeof value == 'string') {
				valueSql += '"' + value + '"' + andStr
			} else {
				valueSql += value + andStr
			}

		}
		valueSql = valueSql.substring(0, valueSql.length - andStr.length)
		sql += kongStr + 'where' + kongStr + valueSql
	}
	return sql
}
const sucData = (data, total = 0) => {
	return {
		code: 0,
		msg: 'ok',
		data: data,
		total: total
	}
}
const failData = (code = -1, msg = 'fail') => {
	return {
		code: -1,
		msg: msg
	}
}
const dbNameBack = (db_name = '') => {
	let name = objectIsEmpty(db_name) ? dbName : db_name
	if (objectIsEmpty(name)) {
		name = uni.getStorageSync(dbNameKey)
	}
	return name
}
const dbFilePathBack = (db_filePath = '') => {
	let filePath = objectIsEmpty(db_filePath) ? dbFilePath : db_filePath
	if (objectIsEmpty(filePath)) {
		filePath = uni.getStorageSync(dbFilePathKey)
	}
	return filePath
}
/**
 * 空判断
 */
const objectIsEmpty = (obj) => {
	if (obj === undefined || obj === null) {
		return true
	}
	if (typeof obj === 'string') {
		return obj.trim().length === 0
	}
	if (Array.isArray(obj)) {
		return obj.length === 0
	}
	if (Object.prototype.toString.call(obj) === '[object Object]') {
		return Object.keys(obj).length === 0
	}
	return false
}