简介

SQLite是一个轻量级、跨平台的关系型数据库。SQLite的很小，它的数据库就是一个文件，但是它并不缺乏功能。不需要复杂的安装或管理。SQLite事务是完全兼容ACID的，允许从多个进程或线程安全访问。SQLite特别适合为单个应用程序和设备提供本地数据存储，使用很简单，将sqlite3.exe可执行文件复制到目标计算机上就可以运行它，新版本对SQL支持比较完善了，因此可以很方便的用来学习SQL。

本篇主要介绍安卓app封装sqlite工具类，使之更容易进行增删改查等操作

dbUtils

工具类主要封装了数据库连接，关闭等常用操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
// 数据库名称
const dbName = 'jpd'

/**
 * 打开数据库,无需主动操作，后边的函数会先调用
 */
const openDb = () => {
	// 判断是否打开，没打开则打开
	return new Promise((resolve, reject) => {
		const isOpen = plus.sqlite.isOpenDatabase({
			name: dbName, //数据库名称
			path: `_doc/${dbName}.db`, //数据库地址
		})
		if (!isOpen) {
			plus.sqlite.openDatabase({
				name: dbName, //数据库名称
				path: `_doc/${dbName}.db`, //数据库地址
				success(e) {
					resolve(e);
				},
				fail(e) {
					reject(e);
				}
			})
		} else {
			resolve();
		}
	})
}

/**
 * 查询所有数据表名
 */
const getTable = () => {
	return new Promise((resolve, reject) => {
		plus.sqlite.selectSql({
			name: dbName,
			sql: "select * FROM sqlite_master where type='table'",
			success(e) {
				resolve(e);
			},
			fail(e) {
				reject(e);
			}
		})
	})
}
//关闭数据库
const closeSQL = (name) => {
	return new Promise((resolve, reject) => {
		plus.sqlite.closeDatabase({
			name: dbName,
			success(e) {
				resolve(e);
			},
			fail(e) {
				reject(e);
			}
		})
	})
}

//监听数据库是否开启
const isOpen = (name) => {
	let open = plus.sqlite.isOpenDatabase({
		name: dbName,
		path: `_doc/${dbName}.db`,
	})
	return open;
}


/**
 * 将查询到的数据进行驼峰映射
 */
const camelCaseData = (data) => {
	const result = data.map(row => {
		const camelCaseRow = {};
		Object.keys(row).forEach(key => {
			const camelCaseKey = key.replace(/_([a-z])/g, g => g[1].toUpperCase());
			camelCaseRow[camelCaseKey] = row[key];
		});
		return camelCaseRow;
	});
	return result
}

/**
 * 将对象的驼峰字段转为_   例如firstName -> first_name
 */
const convertKeysToUnderscore = (obj) => {
	let newObj = {};

	for (let key in obj) {
		if (obj.hasOwnProperty(key)) {
			let newKey = key.replace(/([a-z])([A-Z])/g, '$1_$2').toLowerCase();
			newObj[newKey] = obj[key];
		}
	}

	return newObj;
}

/**
 * 过滤掉 model 非数据库字段
 */
const filterNotFieldNotValue = (obj, fieldList = []) => {
	if (fieldList.length === 0) {
		return obj
	}
	const filterObj = {}
	const filterField = fieldList.length !== 0
	for (let entry of Object.entries(obj)) {
		if (entry[1] !== undefined && entry[1] !== null && entry[1] !== 'null' && (!filterField || fieldList
				.includes(entry[0]))) {
			filterObj[entry[0]] = entry[1]
		}
	}
	return filterObj
}

/**
 * 事务处理
 * @param {String} operation [begin/rollback/commit]
 */
const openTransaction = operation => {
	// console.log('execute transcation: ' + operation)
	return new Promise((resolve, reject) => {
		plus.sqlite.transaction({
			name: dbName,
			operation: operation,
			success() {
				resolve();
			},
			fail(e) {
				reject()
			}
		})
	})
}




export default {
	openDb, //打开数据库
	getTable, //获取所有的表信息
	closeSQL, //关闭数据库
	isOpen, //监听数据库是否开启
	dbName,
	camelCaseData, // 将查询到的数据进行驼峰映射
	convertKeysToUnderscore, // 将对象的驼峰字段转为_
	filterNotFieldNotValue, // 过滤掉 model 非数据库字段, 非空值,
	openTransaction,
}

BaseMapper.js

主要封装了数据库常用的增删改查常用操作，方便与数据库进行交互

  1. tableName 数据库表名
  2. fieldList 表的所有字段，驼峰命名
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
import dbUtil from '@/utils/dbUtil'
import snowflakeUtil from '@/utils/snowflakeUtil'
import dayjs from '@/utils/dayjs'

async function getById(tableName, id) {
	await dbUtil.openDb()
	return new Promise((resolve, reject) => {
		const sql = `select * from ${tableName} where id = ${id}`
		plus.sqlite.selectSql({
			name: dbUtil.dbName,
			sql: sql,
			success: function (data) {
				resolve(dbUtil.camelCaseData(data)[0])
			},
			fail: function (e) {
				console.log('selectSql failed: ' + JSON.stringify(e))
			}
		})
	})
}

async function list(tableName) {
	await dbUtil.openDb()
	return new Promise((resolve, reject) => {
		const sql = `
					SELECT * FROM ${tableName}
		`
		plus.sqlite.selectSql({
			name: dbUtil.dbName,
			sql: sql,
			success: function (data) {
				resolve(dbUtil.camelCaseData(data))
			},
			fail: function (e) {
				console.log('selectSql failed: ' + JSON.stringify(e))
			}
		})
	})
}

async function getByOrderId(tableName, orderId) {
	await dbUtil.openDb()
	return new Promise((resolve, reject) => {
		const sql = `select * from ${tableName} where order_id = ${orderId}`
		plus.sqlite.selectSql({
			name: dbUtil.dbName,
			sql: sql,
			success: function (data) {
				resolve(dbUtil.camelCaseData(data)[0])
			},
			fail: function (e) {
				console.log('selectSql failed: ' + JSON.stringify(e))
			}
		})
	})
}

async function listByOrderId(tableName, orderId) {
	await dbUtil.openDb()
	return new Promise((resolve, reject) => {
		const sql = `select * from ${tableName} where order_id = ${orderId}`
		plus.sqlite.selectSql({
			name: dbUtil.dbName,
			sql: sql,
			success: function (data) {
				resolve(dbUtil.camelCaseData(data))
			},
			fail: function (e) {
				console.log('selectSql failed: ' + JSON.stringify(e))
			}
		})
	})
}

/**
 * @param {Object} tableName 表名
 * @param {Object} param 对象参数
 */
async function updateById(tableName, param, fieldList = []) {
	await dbUtil.openDb()
	return new Promise((resolve, reject) => {
		param = dbUtil.filterNotFieldNotValue(param, fieldList)
		param = dbUtil.convertKeysToUnderscore(param)
		const sql = buildUpdateByIdSQL(tableName, param)
		plus.sqlite.executeSql({
			name: dbUtil.dbName,
			sql: sql,
			success: function (data) {
				resolve()
			},
			fail: function (e) {
				console.log('executeSql failed: ' + JSON.stringify(e))
			}
		})
	})
}

/**
 * @param {Object} tableName 表名
 * @param {Object} id 主键id
 */
async function deleteById(tableName, id) {
	await dbUtil.openDb()
	return new Promise((resolve, reject) => {
		const sql = `delete from ${tableName} where id = ${id}`
		plus.sqlite.executeSql({
			name: dbUtil.dbName,
			sql: sql,
			success: function (data) {
				resolve()
			},
			fail: function (e) {
				console.log('executeSql failed: ' + JSON.stringify(e))
			}
		})
	})
}

/**
 * @param {Object} tableName 表名
 * @param {Object} condition 删除条件  对象类型
 * 只支持AND拼接，特殊的请自己手写删除sql
 */
async function deleteByCondition(tableName, condition) {
	await dbUtil.openDb()
	return new Promise((resolve, reject) => {
		condition = dbUtil.convertKeysToUnderscore(condition)
		const sql = buildDeleteByConditionSql(tableName, condition)
		plus.sqlite.executeSql({
			name: dbUtil.dbName,
			sql: sql,
			success: function (data) {
				resolve()
			},
			fail: function (e) {
				console.log('executeSql failed: ' + JSON.stringify(e))
			}
		})
	})
}

/**
 * 批量新增
 * @param {String} tableName 数据表名称
 * @param {Array} arrayObj 数组对象
 */
async function saveBatch(tableName, arrayObj, fieldList = []) {
	await dbUtil.openDb()
	return new Promise((resolve, reject) => {
		let sqlList = []
		let id = snowflakeUtil.getId()
		for (let obj of arrayObj) {
			obj.id = id++
			obj = dbUtil.filterNotFieldNotValue(obj, fieldList)
			obj = dbUtil.convertKeysToUnderscore(obj)
			const sql = buildSaveSql(tableName, obj)
			sqlList.push(sql)
		}
		// console.log({ saveBatchSqlList: sqlList })
		plus.sqlite.executeSql({
			name: dbUtil.dbName,
			sql: sqlList,
			success: function (data) {
				resolve()
			},
			fail: function (e) {
				console.log('executeSql failed: ' + JSON.stringify(e))
			}
		})
	})
}

/**
 * 批量更新
 * @param {String} tableName 数据表名称
 * @param {Array} arrayObj 数组对象
 */
async function updateBatch(tableName, arrayObj, fieldList = []) {
	await dbUtil.openDb()
	return new Promise((resolve, reject) => {
		let sqlList = []
		for (let obj of arrayObj) {
			obj = dbUtil.filterNotFieldNotValue(obj, fieldList)
			obj = dbUtil.convertKeysToUnderscore(obj)
			const sql = buildUpdateByIdSQL(tableName, obj, fieldList)
			sqlList.push(sql)
		}
		plus.sqlite.executeSql({
			name: dbUtil.dbName,
			sql: sqlList,
			success: function (data) {
				resolve()
			},
			fail: function (e) {
				console.log('executeSql failed: ' + JSON.stringify(e))
			}
		})
	})
}

function buildSaveSql(tableName, obj) {
	let keys = Object.keys(obj)
	let keyStr = keys.toString()
	let valStr = ''
	keys.forEach((item, index) => {
		let sqlValue // 用于拼接的值，字符串包含前后引号 ""
		const value = obj[item]
		if (typeof value === 'object') {
			sqlValue = `'${JSON.stringify(value).replace(/'/g, "''")}'`
		} else if (typeof value === 'string') {
			sqlValue = `'${value.replace(/'/g, "''")}'`
		} else {
			sqlValue = JSON.stringify(value)
		}
		valStr += `${sqlValue}${keys.length - 1 !== index ? ',' : ''}`
	})
	let sqlStr = `insert into ${tableName}(${keyStr}) values(${valStr})`
	// console.log({saveSql: sqlStr})
	return sqlStr
}

/**
 * 构建updateById语句，自动更新时间
 */
const buildUpdateByIdSQL = (tableName, obj, fieldList = []) => {
	if (JSON.stringify(obj) !== '{}') {
		let dataKeys = Object.keys(obj).filter(x => x !== 'id' && x !== 'update_time')
		let setStr = ''
		dataKeys.forEach((item, index) => {
			let sqlValue // 用于拼接的值，字符串包含前后引号 ""
			const value = obj[item]
			if (typeof value === 'object') {
				sqlValue = `'${JSON.stringify(value).replace(/'/g, "''")}'`
			} else if (typeof value === 'string') {
				sqlValue = `'${value.replace(/'/g, "''")}'`
			} else {
				sqlValue = JSON.stringify(value)
			}
			setStr += `\`${item}\` = ${sqlValue}${dataKeys.length - 1 !== index ? ',' : ''}`
		})
		const sql = `update ${tableName} set ${setStr}, update_time = ${JSON.stringify(dayjs().format('YYYY-MM-DD HH:mm:ss'))} where id = ${obj.id}`
		//console.log({buildUpdateByIdSQL: sql})
		return sql
	}
	return undefined
}

function buildDeleteByConditionSql(tableName, setData) {
	if (JSON.stringify(setData) !== '{}') {
		let dataKeys = Object.keys(setData)
		let setStr = ''
		dataKeys.forEach((item, index) => {
			let value = JSON.stringify(setData[item])
			if (typeof setData[item] === 'object' || typeof setData[item] === 'array') {
				value = `'${value}'`
			}
			setStr += `\`${item}\` = ${value}${dataKeys.length - 1 !== index ? 'AND' : ''}`
		})
		return `delete from ${tableName} where ${setStr}`
	}
	return undefined
}

async function saveOrUpdateBatch(tableName, list, fieldList = []) {
	return new Promise(async (resolve, reject) => {
		if (!list || list.length === 0) {
			resolve()
		}
		const updateList = list.filter(x => x.id)
		const saveList = list.filter(x => !x.id)
		if (updateList.length > 0) {
			await updateBatch(tableName, updateList, fieldList)
		}

		if (saveList.length > 0) {
			await saveBatch(tableName, saveList, fieldList)
		}
		resolve()
	})
}

/**
 * @param tableName 表名
 * @param obj 对象
 * @param fieldList 数据库字段
 */
async function saveOrUpdate(tableName, obj, fieldList = []) {
	const list = obj ? [obj] : []
	return saveOrUpdateBatch(tableName, list, fieldList)
}

export default {
	getById,
	list,
	getByOrderId,
	listByOrderId,
	updateById,
	deleteById,
	deleteByCondition,
	saveBatch,
	updateBatch,
	saveOrUpdateBatch,
	saveOrUpdate
}

GlobalController.js

全局事务异常控制

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
import {
	R
} from '@/utils/R.js'
import dbUtil from '@/utils/dbUtil.js'

/**
 * 异常控制
 */
const exceptionWrapper = (func) => {
	return async function(message) {
		try {
			const res = await func(...arguments)
			return res
		} catch (e) {
			console.log(func.name + " execute fail: " + e.stack)
			if (e.name = 'ApiException') {
				return R.error(e.message)
			} else {
				return R.error("服务异常")
			}
		}
	}
}

/**
 * 异常事务控制
 */
const exceptionTranscationWrapper = (func) => {
	return async function() {
		try {
			await dbUtil.openDb()
			await dbUtil.openTransaction('begin')
			const res = await func(...arguments)
			await dbUtil.openTransaction('commit')
			return res
		} catch (e) {

			await dbUtil.openTransaction('rollback')
			console.error(e.stack);
			if (e.name = 'ApiException') {
				return R.error(e.message)
			} else {
				return R.error("服务异常")
			}

		}
	}
}

export {
	exceptionWrapper,
	exceptionTranscationWrapper,
}

ApiException.js

自定义异常类

1
2
3
4
5
6
7
// 自定义异常类
export class ApiException extends Error {
	constructor(message) {
		super(message); // 调用父类的构造函数，并传入错误信息
		this.name = 'ApiException'; // 设置错误名称
	}
}