websql的使用/phonegap操作資料庫 sqlite

標籤：

對websql的常用操作進行了一個封裝，項目是基於phonegap的，不過phonegap預設已經整合了資料庫操作的外掛程式，所以無需再配置什麼，直接用就可以了：

/***資料庫操作輔助類,定義對象、資料操作方法都在這裡定義*/var dbname=‘mydb‘;var version=‘1.1‘;var dbdesc=‘mydb‘;var table_picture=‘be_picture‘;var dbsize=30000;var db=null;/** * 開啟資料庫 * @returns {Boolean} */function openDB(callback){try{ if (!window.openDatabase) {             console.log(‘該瀏覽器不支援資料庫‘);             return false;         } db = window.openDatabase(dbname, version, dbdesc, dbsize);return true;}catch(e){if(e==2){console.log("資料庫版本無效");}else{console.log("未知錯誤 "+e+".");}return false;}}/** * 執行一段sql * @param sql */function execSql(sql,param,callback){if(db==null){openDB();}db.transaction(function(tx) {tx.executeSql(sql,param, function(tx, result) {if(typeof(callback) == ‘function‘) {callback(true)}return true;}, function(tx, error) {if(typeof(callback) == ‘function‘) {callback(false)}console.log(error);return false;});});}var pictureFields=[   ‘id‘,   ‘app_flow_no‘,   ‘ptr_type‘,   ‘ptr_name‘,   ‘ptr_address‘,   ‘blurred‘,   ‘original‘,   ‘local_address‘  ]/** * 初始化資料庫 */function initDB(){if(db==null){openDB();}createTable(table_picture,pictureFields,{"id":"primary key","app_flow_no":"not null"});}/** * 建立資料庫 * @param tableName表名稱 * @param fields表欄位 * @param constraint約束或者欄位的其他補充，可以為空白, * 格式如：{"id":"integer primary key autoincrement","app_flow_no":"not null"} */function createTable(tableName,fields,constraint){if(db==null){openDB();}var sql = ‘CREATE TABLE IF NOT EXISTS ‘+tableName+‘ (‘;for(i in fields){var key = "";if(typeof(constraint)!="undefined" && typeof(constraint[fields[i]]) !="undefined"){key = " "+constraint[fields[i]];}sql+=fields[i]+key+",";}sql = sql.substr(0,sql.length-1);sql += ")";//log(sql);execSql(sql);}/** * 更新資料 * @param tableName表名稱 * @param setFields要更新的欄位數組 * @param setParams要更新的欄位對應的參數數組 * @param whereStrwhere語句，如果沒有可不傳，不包含where關鍵字，參數用?代替，如：id=? and name=? * @param wherParamswhere語句用到的參數數組,如[‘111‘,‘2222‘] */function updateTable(tableName,setFields,setParams,whereStr,wherParams){var sql = "update "+tableName+" set ";for(i in setFields){sql+=setFields[i]+"=?,";}sql = sql.substr(0,sql.length-1);if(typeof(whereStr)!="undefined" && typeof(wherParams)!="undefined"&& whereStr!=""){sql += " where " + whereStr;setParams = setParams.concat(wherParams);}execSql(sql,setParams);}/** * 插入資料 * @param tableName * @param insertFields * @param insertParams */function insertTable(tableName,insertFields,insertParams){var sql = "insert into "+tableName+" (";var sql2 = " values(";for(i in insertFields){sql+=insertFields[i]+",";sql2 +="?,"}sql = sql.substr(0,sql.length-1);sql2 = sql2.substr(0,sql2.length-1);sql += ")";sql2 +=  ")";execSql(sql+sql2,insertParams);}/** * 刪除資料 * @param tableName * @param whereStr * @param wherParams */function deleteRow(tableName,whereStr,wherParams){var sql = "delete from "+tableName;if(typeof(whereStr)!="undefined" && typeof(wherParams)!="undefined"&& whereStr!=""){sql += " where " + whereStr;}execSql(sql,wherParams);}/** * 查詢 * @param tableName * @param selectFieldsselect的欄位，用逗號分開，如果全部傳"*" * @param whereStrwhere語句，參數用?代替 * @param wherParams參數數組 * @callback 必填，返回的對象會放在callback函數作為參數傳遞 */function select(tableName,selectFields,whereStr,wherParams,callback){if(db==null){openDB();}var sql = "SELECT "+selectFields+" FROM "+tableName;if(typeof(whereStr)!="undefined" && typeof(wherParams)!="undefined"&& whereStr!=""){sql += " where " + whereStr;} db.transaction(function(tx){          tx.executeSql(sql,wherParams,function(tx,results){          if(results.rows.length<1){          if (typeof(callback) == ‘function‘) {callback(false)} //沒有資料          }else{                if(typeof(callback) == ‘function‘) {callback(results.rows)}          }          },function(tx,error){              return false;          });      });}/** * 插入或更新 * @param tableName * @param insertFields * @param insertParams * @param key根據該key來判斷是否有資料 * @param keyVal */function saveOrUpdate(tableName,insertFields,insertParams,key,keyVal){if(typeof(key)!="undefined" && typeof(keyVal)!="undefined"&& key!=""){select(tableName,insertFields[0],key+"=?",[keyVal],function(rows){if(rows){updateTable(tableName,insertFields,insertParams,key+"=?",[keyVal]);}else{insertFields.push(key);insertParams.push(keyVal);insertTable(tableName,insertFields,insertParams);}})}else{insertTable(tableName,insertFields,insertParams);}}

 查詢：

select(table_picture,"*","id=?",[id],function(rows){if(rows){//如果查詢到資料}})

 

注意：因為websql是非同步執行的，要擷取到返回的結果然後進一步操作，需要傳入回呼函數，如上面的function(rows)....

插入或者更新：

saveOrUpdate(table_picture,[‘id‘,‘app_flow_no‘,‘original‘,‘ptr_type‘,‘ptr_name‘],[pic.id,pic.appFlowNo,pic.original,pic.ptrType,pic.ptrName],‘id‘,pic.id);

 刪除：

deleteRow(table_picture,"id=?",[123])

 轉自：http://blog.csdn.net/linshutao/article/details/21398483

websql的使用/phonegap操作資料庫 sqlite