一个SQLite操作类(JS)
源码：
/*************************************//* Helman, heldes.com *//* helman at heldes dot com *//* sqlitedb.js *//* SQLite Database Class For HTML5 *//*************************************/function cDB(confs){ var ret = { _db: null, _response: null, _error: null, check : function(tbl){ if(!this._db) return false; var _sql = '', _sqlField='', _field=[]; for(var i=0;i<tbl.length;i++){ _sql = "CREATE TABLE IF NOT EXISTS "+tbl[i].table+" ("; _field = tbl[i].properties; _sqlField = ''; for (var j=0;j<_field.length;j++){ _sqlField += ',`'+_field[j].name+'` '+_field[j].type; } _sql += _sqlField.substr(1)+");"; this.query(_sql,null,null,null); } return true; }, getResult:function(){ return this._response; }, getError:function(){ return this._error; }, callback_error: function(tx,_er){ var err = ''; if(typeof(tx) == 'object'){ for(var q in tx){ err += q+' = "'+tx[q]+'"; '; } }else{ err += tx+'; '; } if(typeof(_er) == 'object'){ for(var q in _er){ err += q+' = "'+_er[q]+'"; '; } }else if(typeof(_er) == 'undefined'){ err += _er+'; '; } console.log(err); //if(callback) callback(); return false; }, query: function(sql,callback,params,er){ if(!this._db) return false; var self = this; function _er(tx,__er){ __er = jQuery.extend(__er,{sql:sql}); if(er) er(tx,__er); else self.callback_error(tx,__er); }; this._db.transaction(function(tx){ tx.executeSql(sql,(params?params:[]),callback,_er); }, _er); }, update:function(tbl,sets,clauses,callback){ var __sql = 'UPDATE '+tbl, _field = null, __set = '', __clause = '',__values=[]; for(var i=0;i<sets.length;i++){0 _field = sets[i]; for(var j=0;j<_field.length;j++){ __set += ',`'+_field[j].name+'`=?'; __values.push(_field[j].value); } } for(var i=0;i<clauses.length;i++){ __clause += ',`'+clauses[i].name+'`=?'; __values.push(clauses[i].value); } __sql += ((__set!='')?' SET '+__set.substr(1):'')+((__clause!='')?' WHERE '+__clause.substr(1):'')+';'; this.query(__sql,callback,__values); return true; }, remove:function(tbl,clauses){ var __sql = 'DELETE FROM '+tbl, __clause = ''; for(var i=0;i<clauses.length;i++) __clause += ',`'+clauses[i].name+'`="'+escape(clauses[i].value)+'"'; __sql += ' WHERE '+((__clause!='')?__clause.substr(1):'FALSE')+';'; this.query(__sql); return true; }, multiInsert: function(tbl,rows,callback,er){ if(!this._db) return false; var self = this; var __sql = '', _field = null, __field = '', __qs = [], __values = []; this._db.transaction(function(tx){ for(var i=0;i<rows.length;i++){ __qs = []; __values = []; __field = ''; _field = rows[i]; for(var j=0;j<_field.length;j++){ __field += ',`'+_field[j].name+'`'; __qs.push('?'); __values.push(_field[j].value); } tx.executeSql('INSERT INTO '+tbl+' ('+__field.substr(1)+') VALUES('+__qs.join(',')+');',__values,function(){return false;},(er ? er : self.callback_error)); } }, self.callback_error, function(){ if(callback) callback(); return true; }); return true; }, insert:function(tbl,rows,callback){ var __sql = '', _field = null, __field = '', __qs = [], __values = [], __debug = ''; for(var i=0;i<rows.length;i++){ __qs = []; __field = ''; _field = rows[i]; __debug += _field[0].name+' = '+_field[0].value+';'; for(var j=0;j<_field.length;j++){ __field += ',`'+_field[j].name+'`'; __qs.push('?'); __values.push(_field[j].value); } __sql += 'INSERT INTO '+tbl+' ('+__field.substr(1)+') VALUES('+__qs.join(',')+');'; } this.query(__sql,callback,__values); return true; }, insertReplace:function(tbl,rows,debug){ var __sql = '', _field = null, __field = '', __qs = [], __values = [], __debug = ''; for(var i=0;i<rows.length;i++){ __qs = []; __field = ''; _field = rows[i]; __debug += _field[0].name+' = '+_field[0].value+';'; for(var j=0;j<_field.length;j++){ __field += ',`'+_field[j].name+'`'; __qs.push('?'); __values.push(_field[j].value); } __sql += 'INSERT OR REPLACE INTO '+tbl+' ('+__field.substr(1)+') VALUES('+__qs.join(',')+');'; } this.query(__sql,null,__values); return true; }, dropTable:function(tbl,callback){ var __sql = ''; if(tbl==null) return false; __sql = 'DROP TABLE IF EXISTS '+tbl; this.query(__sql,callback); return true; } } return jQuery.extend(ret,confs);}?
使用方法：
?
/*=======================================*/创建数据库：/* Create or open database with 'websiteDB' as database name and 'website DB' as title, and database site is 5MB *//* I'm not using 1024 for the size multiplying because i don't want to be near at the margin size */var db = new cDB({_db:window.openDatabase("websiteDB", "", "website DB";, 5*1000*1000)});/*=======================================*/建表:/* dbTable is database structure in this example, and contains 2 tables 'foo' and 'boo' *//* and also the table structure in table properties */var dbTable = [ {table:'foo',properties: [ {name:'foo_id', type: 'INT PRIMARY KEY ASC'}, {name:'foo_field_1', type: ''}, {name:'foo)field_2', type: ''} ]}, {table:'boo',properties: [ {name:'boo_id', type: 'INT PRIMARY KEY ASC'}, {name:'boo_field_1', type: ''}, {name:'boo_field_2', type: ''} ]} ]; /* this line is checking if the database exist or not and then create the database structure. *//* table will be created if the table is not exist yet, if the table already exist, it will skip the *//* table and continue with others tables */if(!db.check(dbTable)){ db = false; alert('Failed to cennect to database.');}/*=======================================*/删除表：db.dropTable('foo');/*=======================================*/插入数据：var row = [];row.push([ {'name':'foo_id','value':1}, {'name':'foo_field_1','value':'value 1 field_1'}, {'name':'foo_field_2','value':'value 1 field_2']}]);db.insert('foo',row);插入多行记录：/*SQLite is not accepting more than 1 line statement, that is the reason why we not able to do more than one statement query, like insertion. If you want to insert more than 1 record at the time, you need to use this function.*/var rows = [];rows.push([ {'name':'boo_id','value':1}, {'name':'boo_field_1','value':'value 1 field_1'}, {'name':'boo_field_2','value':'value 1 field_2']}]);rows.push([ {'name':'boo_id','value':2}, {'name':'boo_field_1','value':'value 2 field_1'}, {'name':'boo_field_2','value':'value 2 field_2']}]);db.multiInsert('boo',rows,function(){alert('insertion done');});/*如果想合并insert 和 multiInsert两个函数，可以按下面的方法增加一个判断来处理*/if(rows.length>=2){ db.multiInsert('boo',rows,function(){alert('insertion done');});}else{ db.insert('boo',rows);}/*=======================================*/删除数据：db.remove('boo',[{'name':'boo_id','value':1}])/*=======================================*/更新数据db.update('boo',[[ {'name':'boo_id','value':2}, {'name':'boo_field_1','value':'boo value'}]],['name':'boo_id','value':2])/*=======================================*/查询var query = 'SELECT * FROM foo';db.query(query,function(tx,res){ if(res.rows.length){ alert('found '+res.rows.length+' record(s)'); }else{ alert('table foo is empty'); }});?