mui 封裝splite資料庫操作 hbuilderx

/**
 * @Time 2019-7-12 13:57:21
 * SQLite資料庫操作
 * @URL http://www.html5plus.org/doc/zh_cn/sqlite.html
 * @權限 需要在manifest中添加SQLite權限
 */
var SQLiteDB = {
	DBName: 'MyAPPDb',
	DBPath: '_doc/Database.db',
	init: function() {
		if (window.plus) {
			mui.plusReady(function() {
				setTimeout(function() {
					var ws = plus.webview.currentWebview();
					ws.addEventListener('close', function() { //視窗關閉時，關閉資料庫
						SQLiteDB.closeDatabase();
						console.log('webview close---closeDatabase');
					});
				}, 200);
			})
		}
	},
	openDB: function(success, fail) { //打開資料庫
		if (!SQLiteDB.isOpenDatabase()) {
			plus.sqlite.openDatabase({
				name: SQLiteDB.DBName,
				path: SQLiteDB.DBPath,
				success: function(e) {
					console.log('openDatabase success!');
					if (success)
						success(e)
				},
				fail: function(e) {
					console.log('openDatabase failed: ' + JSON.stringify(e));
					if (fail)
						fail(e)
				}
			});
		} else {
			console.log('Database already open!');
			if (success)
				success()
		}
	},
	isOpenDatabase: function() { //檢查資料庫是否打開
		var options = {
			name: SQLiteDB.DBName,
			path: SQLiteDB.DBPath
		}
		return plus.sqlite.isOpenDatabase(options);
	},
	closeDatabase: function(success, fail) { //關閉資料庫
		if (SQLiteDB.isOpenDatabase()) {
			var options = {
				name: SQLiteDB.DBName,
				success: success,
				fail: fail,
			}
			plus.sqlite.closeDatabase(options);
		} else {
			if (success)
				success()
		}
	},
	/**
	 * @param {Object} operation  需要執行的事務操作 begin（開始事務）、commit（送出）、rollback（復原）。
	 */
	transaction: function(operation, success, fail) {
		var options = {
			name: SQLiteDB.DBName,
			operation: operation,
			success: success,
			fail: fail,
		}
		plus.sqlite.transaction(options);
	},
	transactionBegin: function(success, fail) { //開啟事務
		SQLiteDB.transaction("begin", success, fail);
	},
	transactionCommit: function(success, fail) { //送出事務
		SQLiteDB.transaction("commit", success, fail);
	},
	transactionRollback: function(success, fail) { //復原事務
		SQLiteDB.transaction("rollback", success, fail);
	},
	_exec: function(sql, success, fail) {
		plus.sqlite.executeSql({
			name: SQLiteDB.DBName,
			sql: sql,
			success,
			fail
		})
	},
	executeSQL: function(sql, useTran, success, fail, noclose) { //執行語句
		SQLiteDB.openDB(
			function() { //打開成功
				exec();
			},
			function(e) { //打開失敗
				mui.alert("資料庫打開失敗");
				if (fail)
					fail(e);
			});

		function exec() {
			if (useTran) { //使用事務
				SQLiteDB.transactionBegin(function() {
					SQLiteDB._exec(
						sql,
						function(data) {
							if (success) //執行回調
								success(data);
							SQLiteDB.transactionCommit();
							if (!noclose)
								SQLiteDB.closeDatabase();
						},
						function(e) {
							if (fail) //執行回調
								fail(e);
							SQLiteDB.transactionRollback();
							if (!noclose)
								SQLiteDB.closeDatabase();
						}
					)
				});
			} else { //不使用事務
				//console.log(sql)
				SQLiteDB._exec(
					sql,
					function(data) {
						//console.log('Execute Sql success')
						if (success) //執行回調
							success(data);
						if (!noclose)
							SQLiteDB.closeDatabase();
					},
					function(e) {
						//console.log('Execute Sql fail')
						if (fail) //執行回調
							fail(e);
						if (!noclose)
							SQLiteDB.closeDatabase();
					}
				)
			}
		}
	},
	selectSQL: function(sql, success, fail) {
		SQLiteDB.openDB(
			function() { //打開成功
				plus.sqlite.selectSql({
					name: SQLiteDB.DBName,
					sql: sql,
					success: function(data) {
						if (success)
							success(data)
					},
					fail: function(e) {
						if (fail)
							fail(e);
					}
				});
			},
			function(e) { //打開失敗
				mui.alert("資料庫打開失敗");
				if (fail)
					fail(e);
			});
		
	},
	insertData: function(tableName, tbdata, success, fail) { //插入
		SQLiteDB.openDB(
			function() { //打開成功
				SQLiteDB.createTbSql(tableName, tbdata,
					function() { //建立表成功
						SQLiteDB.insertTbSql(tableName, tbdata, success, fail);
					},
					function() { //建立表失敗
						if (fail)
							fail(e);
					});
			},
			function(e) { //打開失敗
				if (fail)
					fail(e);
			});
	},
	insertTbSql: function(tbname, listData, success, fail) { //插入
		if (listData.length > 0) {
			for (var i = 0; i < listData.length; i++) {
				var insertSql = "";
				var textcl = "";
				var valuel = "";
				var jcList = [];
				for (var ls in listData[i]) {
					var tName = JSON.stringify(ls).replace('"', "'").replace('"', "'");

					if (textcl == "") {
						textcl += tName;
						valuel += "'" + listData[i][ls] + "'";
					} else {
						textcl += "," + tName;
						valuel += ",'" + listData[i][ls] + "'";
					}
				}

				if (textcl != "") {
					insertSql = "insert into " + tbname + " (" + textcl + ") values (" + valuel + ")";
					SQLiteDB.executeSQL(insertSql, false, success, fail, true);
				}
			}
		}
	},
	createTbSql: function(tbname, listData, success, fail) { //建立表
		var tbsql = "";
		if (listData.length > 0) {
			for (var ls in listData[0]) {
				var tName = JSON.stringify(ls).replace('"', "'").replace('"', "'");
				if (tbsql == "") {
					tbsql += tName + " text null ";
				} else {
					tbsql += "," + tName + " text null ";
				}
			}
		}
		if (tbsql != "") {
			tbsql = "create table if not exists " + tbname + " (" + tbsql + ")"
			SQLiteDB.executeSQL(tbsql, false, success, fail);
		}
	},
	dropTable:function(tbname, success, fail){  //删除表
		var sql = 'drop table if exists  ' + tbname;
		SQLiteDB.executeSQL(sql, false, success, fail);
	},
	clearTable:function(tbname, success, fail){  //清空表
		var sql = 'DELETE FROM ' + tbname;
		SQLiteDB.executeSQL(sql, false, success, fail);
	}
};

setTimeout(function() {
	SQLiteDB.init();
}, 100);
           

封裝plus中的sqlite操作，包括簡化事務、插入等操作，插入隻需傳進資料結構和表名即可

測試:

<button onclick="insert()">測試插入</button>
<button onclick="select()">測試讀取</button>
<button onclick="deleteTb()">删除表</button>
<button onclick="clearTable()">清空表</button>

           

<script>
			function clearTable(){
				SQLiteDB.clearTable('testTb', function(data){
					console.log(JSON.stringify(data))
				}, function(e){
					console.log(JSON.stringify(e))
				});
			}
			function deleteTb(){
				SQLiteDB.dropTable('testTb', function(data){
					console.log(JSON.stringify(data))
				}, function(e){
					console.log(JSON.stringify(e))
				});
			}
			
			function select(){
				SQLiteDB.selectSQL('select * from testTb', function(data){
					console.log(JSON.stringify(data))
				}, function(e){
					console.log(JSON.stringify(e))
				});
			}
			function insert(){
				var tbdata = [{
					fldGuid:guid(),
					name:'12345',
					sex:'1',
					state:'3',
					remark:'456789789',
				},
				{
					fldGuid:guid(),
					name:'123452232',
					sex:'2',
					state:'3',
					remark:'456789789',
				}]
				SQLiteDB.insertData('testTb', tbdata, function(e){
					console.log(123123123)
				}, function(e){
					console.log(e)
				});
			}

            function guid() {
	            return 'xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx'.replace(/[xy]/g,                         
                function(c) {
	    	        var r = Math.random() * 16 | 0,
			        v = c == 'x' ? r : (r & 0x3 | 0x8);
		            return v.toString(16);
	          });
           }
</script>