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>