web sql的推荐使用方法和测试

/**
* Begin class defination XPager : 分页逻辑类
*/
var XPager = Base.extend({
	// 参考groovy biz.db.Pager
	constructor : function(cp, npp, total){
		this.currentPage = cp;
		this.numPerPage = npp;

		this.rowCount = total;
	}, 

	currentPage : 1, 
	numPerPage : 20, 
	rowCount : 0, 

	pagiStyle : 'digg', 

	ll: null, 

	// changeCp
	callbackFun : '', 

	getPageNum : function() {
		var r = this.rowCount % this.numPerPage;
		var r2 = this.rowCount / this.numPerPage;
		var result = r == 0 ? r2 : r2 + 1;
		return Math.floor(result);
	},

	getStart : function(){
		return (this.currentPage - 1) * this.numPerPage;
	},

	getEnd : function(){
		if(this.rowCount < this.numPerPage || this.currentPage == this.getPageNum())
			return this.rowCount;
		else
			return this.currentPage * this.numPerPage;
	},

	hasNext : function(){
		return this.currentPage < this.getPageNum();
	},

	hasPre : function(){
		return this.currentPage > 1 && this.getPageNum() > 1;
	}, 
	
	lang : {
		pageNum: '总页数：', 
		currentPage: '当前页：', 
		totalCount: '总记录：'
	}, 

	// use mollio pagination template
	genHtml : function(){
		var tpl = '<p>' + 
			'{3}' + 
			'<span><strong>{2}</strong></span>' + 
			'</p><h4>{1} / {0}</h4>';

		var tpl2 = '<a href="javascript:void();" onclick="{1}">{0}</a>';

		var param0 = this.lang.pageNum + this.getPageNum();
		var param1 = this.lang.currentPage + this.currentPage;
		var param2 = this.lang.totalCount + this.rowCount;

		var param3 = '';
		var i = 1;
		for (; i <= this.getPageNum(); i++){
			param3 += tpl2.format(i, "XPager.changeCp('" + this.callbackFun + "', '" + i + "');");
		}

		tpl = tpl.format(param0, param1, param2, param3);
		return tpl;
	}, 

	// use yahoo pagination template
	getHtml2 : function(){
		var tpl = '<div class="' + this.pagiStyle + '">' + 
			'<span style="font-size: 11px; color: silver;">' + this.lang.pageNum + '{0}</span>' + 
			'{1}' + 
			'<span style="font-size: 11px; color: silver;">' + this.lang.totalCount + '<font color="red">{2}</font></span>' + 
			'</div>';


		var param0 = this.getPageNum();
		var param2 = this.rowCount;

		var param1 = '';
		var tplLink = '<a href="javascript:void();" onclick="{1}">{0}</a>';

		var i = 1;
		for (; i <= this.getPageNum(); i++){
			if(i == this.currentPage)
				param1 += '<span class="current">{0}</span>'.format(i);
			else
				param1 += tplLink.format(i, "XPager.changeCp('" + this.callbackFun + "', '" + i + "');");
		}

		tpl = tpl.format(param0, param1, param2);
		return tpl;		
	}, 

	dump : '' // 避免,结尾，在IE浏览器中语义错误
	},{

	changeCp: function(callbackFunName, pageNo){
		var jsStr = callbackFunName + '(' + pageNo + ')';
		eval(jsStr);
	}, 

	dump : '' // 避免,结尾，在IE浏览器中语义错误
});

/**
* Begin class defination XDB : Web sql辅助方法
*/
var XDB = {
	db: null, 

	init: function(dbName, version, dbDisplayName, size){
		if(window.openDatabase)
			this.db = openDatabase(dbName, version, dbDisplayName, size);
	}, 

	exe: function(sql, args, callback){
		if(!this.db){
			X.log('Web sql not support!');
			return;
		}

		this.db.transaction(function(tx){  
			tx.executeSql(sql, args, function(tx, results){
				if(callback)
					callback(results);
			}, function(tx, err){
				// SQLError
				X.log('Web sql execute error: ' + err.message);
			});
		});
	},

	trans: function(fn){
		if(!this.db){
			X.log('Web sql not support!');
			return;
		}

		this.db.transaction(fn);
	}, 

	// crud
	add: function(data, table){
		var keys = _.keys(data);
		var str1 = keys.join(',');
		var str2 = _.map(keys, function(it){return '?';}).join(',');

		var sql = 'insert into {0} ({1}) values ({2})';
		sql = sql.format(table, str1, str2);
		var args = _.values(data);

		this.exe(sql, args)
	}, 

	del: function(data, table){
		var keys = _.keys(data);
		var strClause = _.map(keys, function(it){
			return it + ' = ?';
		}).join(' and ');

		var sql = 'delete from {0} where {1}';
		sql = sql.format(table, strClause);
		var args = _.values(data);

		this.exe(sql, args)
	}, 

	update: function(data, dataClause, table){
		var strSet = _.map(_.keys(data), function(it){
			return it + ' = ?';
		}).join(', ');
		var argsSet = _.values(data);

		var strClause = _.map(_.keys(dataClause), function(it){
			return it + ' = ?';
		}).join(' and ');
		var argsClause = _.values(dataClause);

		var sql = 'update {0} set {1} where {2}';
		sql = sql.format(table, strSet, strClause);

		this.exe(sql, argsSet.merge(argsClause))
	}, 

	query: function(sql, args, callback){
		this.exe(sql, args, function(results){
			if(callback)
				callback(results.rows);
		});
	}, 

	pi: function(sql, args, cp, npp, callback){
		var pager = new XPager(cp, npp, 0);
		var countSql = 'select count(1) as rowCount from ({0})'.format(sql);
		var subSql = 'select * from ({0}) limit {1}, {2}'.format(sql, pager.getStart(), npp);

		var _this = this;
		this.query(countSql, args, function(rows){
			pager.rowCount = rows.item(0)['rowCount'];

			_this.query(subSql, args, function(rows){
				if(callback)
					callback(pager, rows);
			});
		});
	}, 

	dump : ''
};

 

下面是测试代码

$(function(){
			appendLi('*** *** *** *** *** *** *** *** *** *** *** *** XDB');
			appendLi('*** *** *** *** *** *** *** *** *** *** *** *** XDB');
		
			runTest('XDB all', true, function(){
				XDB.init('test', '0.1', 'TestDB', 1024 * 10);
				XDB.exe('create table if not exists x(name varchar(30))');
				XDB.trans(function(tx){
					tx.executeSql('delete from x');
					var i = 0;
					for(; i < 10; i++){
						var sql = "insert into x(name) values(?)";
						tx.executeSql(sql, ['Kerry' + i]);
					}
				});

				XDB.query('select * from x', null, function(rows){
					var len = rows.length;
					var i = 0;
					for(; i < len; i++){
						X.log(rows.item(i)['name']);
					}
				});

				XDB.pi('select * from x', null, 3, 20, function(pi, rows){
					X.log(pi.rowCount);

					var len = rows.length;
					var i = 0;
					for(; i < len; i++){
						X.log(rows.item(i)['name']);
					}
				});

				XDB.update({name: 'XXX0'}, {name: 'Kerry0'}, 'x');
				XDB.query('select * from x where name = ?', ['XXX100'], function(rows){
					X.log(rows.length);
				});
				XDB.del({name: 'XXX0'}, 'x');

				return true;
			});

		});