[转]html5 js 访问 sqlite 数据库的操作类

本文转自:http://blog.csdn.net/tsxw24/article/details/7613815

webkit 核心的浏览器提供了 3个
api接口,用于访问当地sqlite数据,但使用起来很不便宜
故而做这些js封装,以方便使用

 

参照小说:

sqlite API 说明  

http://www.mhtml5.com/resources/html5-js-api%E6%95%99%E7%A8%8B%EF%BC%88%E4%B8%89%EF%BC%89-%E6%9C%AC%E5%9C%B0%E6%95%B0%E6%8D%AE%E5%BA%93

此外的操作类 

 http://levi.cg.am/?p=1679

sqlite 语法

http://blog.csdn.net/ejzhang/article/details/6224915

 

[javascript] view
plain

copy
print?

  1. /** 
  2.  * js 操作数据库类 
  3.  *  
  4.  * @author 肖武<phpxiaowu@gmail.com>  
  5.  */  
  6.   
  7. /** 
  8.  * 1、数据库名(mydb) 
  9.  
  10. 2、版本号(1.0) 
  11.  
  12. 3、描述(Test DB) 
  13.  
  14. 4、数据库大小(2*1024*1024)  
  15.  */  
  16. var DB = function( db_name, size ){  
  17.      var _db = openDatabase(db_name, ‘1.0.0’,”, size );  
  18.        
  19.      return {  
  20.            
  21.          /** 
  22.           * 执行sql,回调再次来到影响条数 
  23.           */  
  24.          execute:function( sql, param, callback ) {  
  25.              //参数处理  
  26.             if( !param ){  
  27.                 param = [];  
  28.             }else if(typeof param == ‘function’ ){  
  29.                 callback = param;  
  30.                 param = [];  
  31.             }  
  32.                
  33.              this.query( sql, param, function(result){  
  34.                  if( typeof callback == ‘function’ ){  
  35.                      callback(result.rowsAffected);  
  36.                  }  
  37.              });  
  38.          },  
  39.   
  40.          /** 
  41.           * 执行sql,回调再次回到sql查询对象 
  42.           * 查询时,有数量重临数组,无数据再次来到0 
  43.           * 增删改时:再次回到int,影响条数 
  44.           * void query( string[, function]) 
  45.           * void query( string[, array[, function]]) 
  46.           */  
  47.          query:function(sql, param, callback){  
  48.             //参数处理  
  49.             if( !param ){  
  50.                 param = [];  
  51.             }else if(typeof param == ‘function’ ){  
  52.                 callback = param;  
  53.                 param = [];  
  54.             }  
  55.               
  56.             var self=this;  
  57.             //只有一个参数  
  58.             _db.transaction(function (tx) {  
  59.                 //4个参数:sql,替换sql中问号的数组,成功回调,出错回调  
  60.                 tx.executeSql(sql,param,function(tx,result){  
  61.                     if (typeof callback == ‘function’ ){  
  62.                         callback(result);  
  63.                     }  
  64.                 },self.onfail) ;  
  65.             })  
  66.         },  
  67.         /** 
  68.          * 插入,回调重临last id 
  69.          * void insert( string, object[, function]) 
  70.          */  
  71.         insert:function( table, data, callback ){  
  72.             if( typeof data != ‘object’ && typeof callback == ‘function’ ){  
  73.                 callback(0);  
  74.             }  
  75.               
  76.             var k=[];  
  77.             var v=[];  
  78.             var param=[];  
  79.             for(var i in data ){  
  80.                 k.push(i);  
  81.                 v.push(‘?’);  
  82.                 param.push(data[i]);  
  83.             }  
  84.             var sql=”INSERT INTO “+table+”(“+k.join(‘,’)+”)VALUES(“+v.join(‘,’)+”)”;  
  85.               
  86.             this.query(sql, param, function(result){  
  87.                 if ( typeof callback == ‘function’ ){  
  88.                     callback(result.insertId);  
  89.                 }  
  90.             });  
  91.         },  
  92.         /** 
  93.          * 修改,回调重临影响条数 
  94.          * void update( string, object[, string[, function]]) 
  95.          * void update( string, object[, string[, array[, function]]]) 
  96.          */  
  97.         update:function( table, data, where, param, callback ){  
  98.             //参数处理  
  99.             if( !param ){  
  100.                 param = [];  
  101.             }else if(typeof param == ‘function’ ){  
  102.                 callback = param;  
  103.                 param = [];  
  104.             }  
  105.               
  106.             var set_info = this.mkWhere(data);  
  107.             for(var i=set_info.param.length-1;i>=0; i–){  
  108.                 param.unshift(set_info.param[i]);  
  109.             }  
  110.             var sql = “UPDATE “+table+” SET “+set_info.sql;  
  111.             if( where ){  
  112.                 sql += ” WHERE “+where;  
  113.             }  
  114.               
  115.             this.query(sql, param, function(result){  
  116.                 if( typeof callback == ‘function’ ){  
  117.                     callback(result.rowsAffected);  
  118.                 }  
  119.             });  
  120.         },  
  121. sqlite,          
  122.         /** 
  123.          * 删除 
  124.          * void toDelete( string, string[, function]]) 
  125.          * void toDelete( string, string[, array[, function]]) 
  126.          */  
  127.         toDelete:function( table, where, param, callback ){  
  128.             //参数处理  
  129.             if( !param ){  
  130.                 param = [];  
  131.             }else if(typeof param == ‘function’ ){  
  132.                 callback = param;  
  133.                 param = [];  
  134.             }  
  135.               
  136.             var sql = “DELETE FROM “+table+” WHERE “+where;  
  137.             this.query(sql, param, function(result){  
  138.                 if( typeof callback == ‘function’ ){  
  139.                     callback(result.rowsAffected);  
  140.                 }  
  141.             });  
  142.         },  
  143.           
  144.         /** 
  145.          * 查询,回调再次来到结果集数组 
  146.          * void fetch_all( string[, function] ) 
  147.          * void fetch_all( string[, param[, function]] ) 
  148.          */  
  149.         fetchAll:function( sql, param, callback ){  
  150.             //参数处理  
  151.             if( !param ){  
  152.                 param = [];  
  153.             }else if(typeof param == ‘function’ ){  
  154.                 callback = param;  
  155.                 param = [];  
  156.             }  
  157.               
  158.             this.query( sql, param, function(result){  
  159.                 if (typeof callback == ‘function’ ){  
  160.                     var out=[];  
  161.                       
  162.                     if (result.rows.length){  
  163.                         for (var i=0;i<result.rows.length;i++){  
  164.                             out.push(result.rows.item(i));  
  165.                         }  
  166.                     }  
  167.                   
  168.                     callback(out);  
  169.                 }  
  170.             });  
  171.         },  
  172.           
  173.         /** 
  174.          * 查询表的音信 
  175.          * table_name: 表名称,支持 % *, 
  176.          */  
  177.         showTables:function( table_name, callback){  
  178.             this.fetchAll(“select * from sqlite_master where type=’table’ and name like ?”, [table_name], callback);  
  179.         },  
  180.           
  181.           
  182.         /** 
  183.          * 组装查询条件 
  184.          */  
  185.         mkWhere:function(data){  
  186.             var arr=[];  
  187.             var param=[];  
  188.             if( typeof data === ‘object’ ){  
  189.                 for (var i in data){  
  190.                     arr.push(i+”=?”);  
  191.                     param.push(data[i]);  
  192.                 console.log(‘data.i:’+i);  
  193.                 }  
  194.             }  
  195.             return {sql:arr.join(‘ AND ‘),param:param};  
  196.         },  
  197.           
  198.         /** 
  199.          * 错误处理 
  200.          */  
  201.         onfail:function(tx,e){  
  202.             console.log(‘sql error: ‘+e.message);  
  203.         }  
  204.      }  
  205. }  
  206.   
  207. /* 
  208. //使用示例: 
  209. //1.拿走db对象,连接数据库 test,分配2M大小 
  210. var db = new DB(‘test’,1024*1024*2); 
  211.  
  212. //2.创建表 
  213. d.query(“CREATE TABLE ids (id integer primary key autoincrement , ctime integer)”); 
  214.  
  215. //3.查看已经创办的表,补助表名通配符搜索。如:”%”查询所有表,”user_%”查询”user_”先河的表 
  216. db.showTables(“%”,function(ret){console.log(ret)}) 
  217.  
  218. //4.查询表里数据 
  219. db.fetchAll(‘select * from ids’,function(ret){console.log(ret)}); 
  220.  
  221. //5.修改 
  222. db.update(‘ids’,{ctime:123},”id=?”,[1],function(ret){console.log(ret)}); 
  223.  
  224. //6.删除 
  225. db.toDelete(‘ids’,”id=?”,[1],function(ret){console.log(ret)}); 
  226.  
  227. //7.其它,如删表 
  228. db.query(‘drop table ids’); 
  229.  
  230.  */   
网站地图xml地图