WebSQL实例记录
<table id="content">
</table>
<br>
<input type="button" id="clearBtnHook" value="更新缓存" />
<script>
/** 静态初始化本地数据库**/
function initSql(dbname,version,description,maxSize,callback){
var myDB = null;
// var support = testAgentSupoort();
var support = true;
try{
//不支持本地存储，执行回调函数兼容
if(!window.openDatabase || support == false){
if(typeof callback ==="function"){
callback();
return false;
}
}else{
var mydbname = dbname || "myDB",
myversion = version || "1.0",
mydescription = description || "firstTest",
mymaxSize = maxSize || 100000;
myDB = openDatabase(mydbname,myversion,mydescription,mymaxSize);
}
}catch(e){
alert("Unknown error "+e+".");
}
return myDB;
};
/**创建数据表**/
function creatTables(db,str){
if(!db){
alert("Failed to connect to database.");
}else{
db.transaction(function (transaction) {
transaction.executeSql(
'CREATE TABLE IF NOT EXISTS '+str,
[],
function(result){
// alert('创建表成功');
},
function(tx,error){
alert("建表错误"+error);
});
});
}
};
function testAgentSupoort(){
var ua = navigator.userAgent,
platform = navigator.platform,
// Rendering engine is Webkit, and capture major version
wkmatch = ua.match( /AppleWebKit\/([0-9]+)/ ),
wkversion = !!wkmatch && wkmatch[ 1 ];
if (
/** 是否为os系统**/
(ua.indexOf( "iPhone" ) > -1 || ua.indexOf( "iPad" ) > -1 || ua.indexOf( "iPod" ) > -1 ) ||
/** 是否为安卓，并且版本不于534（Android 4.0）**/
( ua.indexOf( "Android" ) > -1 && wkversion && wkversion>=534)
){
return true;
}
return false;
}
var dbUtil = {
insert:function(db,tabName,Arr){ /**向表插入数据**/
var str=[];
if(Object.prototype.toString.call(Arr) === '[object Array]'){
for(var i=0,len =Arr.length;i<len;i++){
str.push("?");
}
str = str.join(',');
}else{
alert("最后一位参数必须为数组");
}
db.transaction(
function (transaction) {
transaction.executeSql('INSERT INTO '+tabName+' values('+str+')',Arr,
function(result){}, function(tx,error){});
}) ;
},
deleted:function(db,tabName,keyArr){ /**删除数据**/
var str="",key=[]
if(typeof keyArr==="object"){
for(var i in keyArr){
str+=i+"=? ";
key.push(keyArr[i]);
}
}
db.transaction(
function (transaction) {
transaction.executeSql("DELETE FROM "+tabName+" where "+str,key,
function (tx, result) {},function (tx, error) {alert('更新失败: ' + error.message);});
});
},
update:function(db,tabName,upArr,keyArr,callback){ /**更新数据**/
var strs=[],str1="",key=[],up =[];
if(typeof upArr==="object" && typeof keyArr==="object" ){
for(var i in upArr){
strs.push(i+"=? ");
up.push(upArr[i]);
}
for(var k in keyArr){
str1+=k+"=? ";
key.push(keyArr[k]);
}
}else{
alert("后两位参数必须为对象");
}
strs = strs.join(',');
lastArr =up.concat(key);
db.transaction(
function (transaction) {
transaction.executeSql("UPDATE "+tabName+" SET "+strs+" where "+str1,lastArr,
function (tx, result) {
if(typeof callback ==="function"){
callback(result)
}
},function (tx, error) {alert('更新失败: ' + error.message);});
});
},
query:function(db,mysql,keyArr,callback){ /**查询数据**/
if(mysql){
db.transaction(
function (transaction) {
transaction.executeSql( mysql,keyArr,
function (tx, result) {
if(typeof callback ==="function"){
callback(result)
}
},function (tx, error) {alert('查询失败: ' + error.message);});
});
}
},
droptable: function(db,tabName){ /** 删除数据表**/
db.transaction(function (tx) {
tx.executeSql('drop table '+tabName);
});
}
}
window.localData = {
hname:location.hostname?location.hostname:'localStatus',
isLocalStorage:window.localStorage?true:false,
dataDom:null,
initDom:function(){
if(!this.dataDom){
try{
this.dataDom = document.createElement('input');
this.dataDom.type = 'hidden';
this.dataDom.style.display = "none";
this.dataDom.addBehavior('#default#userData');
document.body.appendChild(this.dataDom);
var exDate = new Date();
exDate = exDate.getDate()+30;
this.dataDom.expires = exDate.toUTCString();
}catch(ex){
return false;
}
}
return true;
},
set:function(key,value){
if(this.isLocalStorage){
window.localStorage.setItem(key,value);
}else{
if(this.initDom()){
this.dataDom.load(this.hname);
this.dataDom.setAttribute(key,value);
this.dataDom.save(this.hname)
}
}
},
get:function(key){
if(this.isLocalStorage){
return window.localStorage.getItem(key);
}else{
if(this.initDom()){
this.dataDom.load(this.hname);
return this.dataDom.getAttribute(key);
}
}
},
remove:function(key){
if(this.isLocalStorage){
localStorage.removeItem(key);
}else{
if(this.initDom()){
this.dataDom.load(this.hname);
this.dataDom.removeAttribute(key);
this.dataDom.save(this.hname)
}
}
}
}
//创建本地数据库
var MDB = initSql('','','','','');
//设置本地存储标示
var flag = localData.get('flag');
// alert(flag)
if(flag){
//删除数据
// var id = 1;
// dbUtil.deleted(MDB,"Shop",{id:id});
var weid = 1;
//查询数据
// dbUtil.query(MDB,"SELECT * FROM Shop where id=?",[weid],function(result){ //查询单条数据
dbUtil.query(MDB,"SELECT * FROM Shop",[],function(result){
// alert(result.rows.item(0)["id"]);
var shopname = '';
var id = '';
var addtime = '';
var html = '<tr><th>id</th><th>shopname</th><th>addtime</th></tr>';
for(var i=0,le = result.rows.length;i<le;i++){
shopname = result.rows.item(i)["shopname"];
id = result.rows.item(i)["id"];
addtime = result.rows.item(i)["addtime"];
html += "<tr><th>"+id+"</th><th>"+shopname+"</th><th>"+addtime+"</th></tr>";
}
var con = document.getElementById("content");
con.innerHTML = html;
});
}else{
//创建本地数据表结构
creatTables(MDB,"Shop(id INTEGER UNIQUE,shopname TEXT NOT NULL,addtime TEXT NOT NULL)");
var shopArr1 = [1,'测试'+Math.ceil(Math.random()*100),Math.ceil(Math.random()*10000)];
var shopArr2 = [2,'测试'+Math.ceil(Math.random()*100),Math.ceil(Math.random()*10000)];
//插入数据
dbUtil.insert(MDB,"Shop",shopArr1);
dbUtil.insert(MDB,"Shop",shopArr2);
//设置本地存储标示
localData.set('flag',1);
dbUtil.query(MDB,"SELECT * FROM Shop",[],function(result){
// alert(result.rows.item(0)["id"]);
var shopname = '';
var id = '';
var addtime = '';
var html = '<tr><th>id</th><th>shopname</th><th>addtime</th></tr>';
for(var i=0,le = result.rows.length;i<le;i++){
shopname = result.rows.item(i)["shopname"];
id = result.rows.item(i)["id"];
addtime = result.rows.item(i)["addtime"];
html += "<tr><th>"+id+"</th><th>"+shopname+"</th><th>"+addtime+"</th></tr>";
}
var con = document.getElementById("content");
con.innerHTML = html;
});
}
//更新缓存
var btn = document.getElementById('clearBtnHook');
btn.onclick = function(){
//删除本地数据表
dbUtil.droptable(MDB,"Shop");
//取消本地存储标示
localData.remove('flag');
alert('缓存清除成功！');
window.location.reload();
};
</script>
http://www.cnblogs.com/xumingxiang/archive/2012/03/25/2416418.html
php资料
