HTML5 Web SQL分析实现增，删，改，查

Web Sql 数据库有三个核心方法：

一、openDatabase()

this.db = window.openDatabase(‘hnjz’, ‘1.0’, ‘Test DB’,210241024);

• hnjz：数据库名
• 1.0：数据库版本
• Test DB：数据库描述
• 210241024：数据库大小（单位：字节）
• this.db： 返回的一个对象链接

二、transaction()

此方法可以用于控制事务处理，执行提交操作或回滚操作。

this.db.transaction(function (tx) {
	
})

• this.db: 链接的数据库对象
• function:回调函数，参数是数据库对象内容

三、executeSQL

此方法用于执行sql语句

this.db.transaction(function (tx) {
			var tab = 'CREATE TABLE IF NOT EXISTS transaction_record (data_json)';
				tx.executeSql(tab,[],function (trans, res) {
					alert('创建表成功');
					console.log(trans,res)
				},function (trans, error) {//消息的回调函数
					alert('创建表失败:' + error.message);
				});
		})

• tab：sql语句
• []:传入参数
• function：成功回调
• function：失败回调

源码分析：

var app = {
		db:null,
		json_data:[{
			  "交易账卡号": "62226***6497477",
			  "交易账号": "6222620***497477",
			  "交易户名": "吴*红",
			  "交易证件号码": "44142519****0092X",
			  "交易日期": "2018-05-25 14:31:43",
			  "交易金额": "558540",
			  "交易余额": "754858",
			  "收付标志": "进",
			  "对手账号": "6222621***867569",
			  "对手卡号": "",
			  "现金标志": "其他",
			  "对手户名": "吴*玲",
			  "对手身份证号": "",
			  "对手开户银行": "01443999999",
			  "摘要说明": "",
			  "交易币种": "CNY",
			  "交易网点名称": "业务处理中心",
			  "交易场所": "",
			  "交易发生地": "",
			  "交易是否成功": "成功",
			  "传票号": "",
			  "IP地址": "",
			  "MAC地址": "",
			  "对手交易余额": "",
			  "交易流水号": "EFC0002016123899",
			  "对手余额": "",
			  "渠道": "",
			  "日志号": "",
			  "凭证种类": "",
			  "凭证号": "",
			  "交易柜员号": "EFC0002",
			  "备注": "个人网银"
			}, {
			  "交易账卡号": "6222620****6497477",
			  "交易账号": "62226207****6497477",
			  "交易户名": "吴*红",
			  "交易证件号码": "44142519****10092X",
			  "交易日期": "2018-05-25 16:45:39",
			  "交易金额": "172125",
			  "交易余额": "582733",
			  "收付标志": "出",
			  "对手账号": "62122****33266",
			  "对手卡号": "",
			  "现金标志": "其他",
			  "对手户名": "唐*",
			  "对手身份证号": "",
			  "对手开户银行": "102100004951",
			  "摘要说明": "",
			  "交易币种": "CNY",
			  "交易网点名称": "交通银行中山分行营业部",
			  "交易场所": "",
			  "交易发生地": "",
			  "交易是否成功": "成功",
			  "传票号": "",
			  "IP地址": "",
			  "MAC地址": "",
			  "对手交易余额": "",
			  "交易流水号": "EDK0001021292336",
			  "对手余额": "",
			  "渠道": "",
			  "日志号": "",
			  "凭证种类": "",
			  "凭证号": "",
			  "交易柜员号": "EDK0001",
			  "备注": "个人网银"
		}],
		//创建数据库初始化
		initSql:function(){
			this.db = window.openDatabase('hnjz', '1.0', 'Test DB',2*1024*1024);
			alert(this.db ? "数据库创建成功" : "数据库创建失败");
		},
		//创建表
		createSql:function(){
			if(!this.db){
				alert('先创建库');
				return
			}
			this.db.transaction(function (tx) {
				//创建表和字段创建transaction_record表和相应data_json字段主键ID自动创建
				var tab = 'CREATE TABLE IF NOT EXISTS transaction_record (data_json)';
				tx.executeSql(tab,[],function (trans, res) {
					alert('创建表成功');
					console.log(trans,res)
				},function (trans, error) {//消息的回调函数
					alert('创建表失败:' + error.message);
				});
			})
		},
		/导入数据
		readSql:function(){
			if(!this.db){
				alert('先创建库');
				return
			}
			var tempResult = this.json_data;
			for ( var i = 0; i <tempResult.length; i++){
				var map = {'transactionAccountCardNum':tempResult[i]['交易账卡号'],
						'transactionAccountCard':tempResult[i]['交易账号'],
						'transactionAccountName':tempResult[i]['交易户名'],
						'transactionCode':tempResult[i]['交易日期'],
						'transactionTime':tempResult[i]['交易账卡号'],
						'transactionMoney':tempResult[i]['交易金额'],
						'transactionBalance':tempResult[i]['交易余额'],
						'payFlag':tempResult[i]['收付标志'],
						'receiptAccountCard':tempResult[i]['对手账号'],
						'receiptAccountCardNum':tempResult[i]['对手卡号'],
						'cashFlag':tempResult[i]['现金标志'],
						'receiptAccountName':tempResult[i]['对手户名'],
						'receiptCode':tempResult[i]['对手身份证号'],
						'receiptOpenAccountBank':tempResult[i]['对手开户银行'],
						'summaryAbout':tempResult[i]['摘要说明'],
						'currency':tempResult[i]['交易币种'],
						'transactionBranchName':tempResult[i]['交易网点名称'],
						'transactionPlace':tempResult[i]['交易场所'],
						'transactionAddress':tempResult[i]['交易发生地'],
						'transactionIsSuccess':tempResult[i]['交易是否成功'],
						'voucherNum':tempResult[i]['传票号'],
						'ip':tempResult[i]['IP地址'],
						'mac':tempResult[i]['MAC地址'],
						'receiptTransactionBalance':tempResult[i]['对手交易余额'],
						'transactionSerialNum':tempResult[i]['交易流水号'],
						'receiptBalance':tempResult[i]['对手余额'],
						'channel':tempResult[i]['渠道'],
						'logNum':tempResult[i]['日志号'],
						'certificateType':tempResult[i]['凭证种类'],
						'certificateNum':tempResult[i]['凭证号'],
						'transactionTellerNum':tempResult[i]['交易柜员号'],
						'remark':tempResult[i]['备注']
					};
					map = JSON.stringify(map);
					this.db.transaction(function (tx) {
						//循环插入数据库
						tx.executeSql('INSERT INTO transaction_record (data_json) VALUES (?)',[map],function (trans, res) {
							console.log('导入数据成功');
							console.log(trans,res)
						},function (trans, error) {//消息的回调函数
							console.log('导入数据失败:' + error.message);
						});
					})
			}
		},
		//数据查询返回表格
		selectSql:function(){
			this.db.transaction(function (tx) {
				tx.executeSql("select * from transaction_record ", [], function (ts, data) {
					var html = '<table><tr><th>交易账号</th><th>交易户名</th><th>交易账卡号</th></tr>';
					for(var i in data.rows){
						var json = data.rows[i].data_json;
						if(json){
							json = JSON.parse(json);
							html +="<tr><td>" + json['transactionAccountCard'] + "</td><td>" +json['transactionAccountName'] + "</td><td>" +json['transactionAccountCardNum'] + "</td>";
						}

					}
					html+="</table>";
					document.getElementById("tabe").innerHTML = html;
				})
			})
		}
	}




</script>
<body>
	<button type="button" onclick="app.initSql()">创建数据库</button>&nbsp;<button type="button" onclick="app.createSql()">创建表</button>&nbsp;<button type="button" onclick="app.readSql()">导入数据</button>&nbsp;<button type="button" onclick="app.selectSql()">生产表格</button>
	<div id="tabe"></div>
</body>
</html>

页面展示：