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> <button type="button" onclick="app.createSql()">创建表</button> <button type="button" onclick="app.readSql()">导入数据</button> <button type="button" onclick="app.selectSql()">生产表格</button>
<div id="tabe"></div>
</body>
</html>
页面展示: