Titanium数据库SQLite的使用

在Titanium的Database sqllit的使用如下：

// this sets the background color of the master UIView (when there are no windows/tab groups on it)
Titanium.UI.setBackgroundColor('#000');
//创建相关的数据库

//打开相关的数据库
var db = Titanium.Database.open('mydb');

// create tab group
var tabGroup = Titanium.UI.createTabGroup();
//删除表中的数据
//db.execute('DELETE FROM DATABASETEST');
//创建相关的表
db.execute('CREATE TABLE IF NOT EXISTS DATABASETEST (userName TEXT, passwd TEXT)');

//
// create controls tab and root window
//
var win2 = Titanium.UI.createWindow({  
    title:'添加人员',
    backgroundColor:'#fff'
});
var tab2 = Titanium.UI.createTab({  
    icon:'KS_nav_ui.png',
    title:'添加人员',
    window:win2
});


var lblName = Titanium.UI.createLabel({

    text:'用户名：  ',
    
    color:"#000000",

    top : Ti.Platform.displayCaps.platformHeight/2-200,

    left: 10,

    height:60,

    width:100,

    textAlign:'right'

});

var txtName = Ti.UI.createTextField({

    top:Ti.Platform.displayCaps.platformHeight/2-200,

    left: lblName.width,

    width:Ti.Platform.displayCaps.platformWidth-lblName.width-40,

    height:60

});



var lblPassword = Titanium.UI.createLabel({
    text:' 密码：  ',
    
    color:"#000000",
    
    top : Ti.Platform.displayCaps.platformHeight/2-110,

    left: 10,

    height:60,

    width:100,

    textAlign:'right'
});
var txtPassword = Ti.UI.createTextField({
	top : Ti.Platform.displayCaps.platformHeight/2-110,

    left : lblPassword.width,

    width : Ti.Platform.displayCaps.platformWidth-lblPassword.width-40,

    height : 60
});
var btnSubmit = Ti.UI.createButton({
	title : '添加',
	top: Ti.Platform.displayCaps.platformHeight/2-10,
	left:Ti.Platform.displayCaps.platformWidth/2-110,
	width : 100,
	height : 60
});




btnSubmit.addEventListener('click',function(e){
	var userName=txtName.value;
	var passwd=txtPassword.value;
	//入参的集合
	var personArray=[userName,passwd];
	//执行插入操作
    db.execute('INSERT INTO DATABASETEST (userName, passwd ) VALUES (?, ?)', personArray);
    //db.execute("COMMIT");
    //获取影响的行数
     Titanium.API.info('JUST INSERTED, rowsAffected = ' + db.rowsAffected);
     //获取影响行数的rowid
     Titanium.API.info('JUST INSERTED, lastInsertRowId = ' + db.lastInsertRowId);
     
    //创建一个提示框    
	var a = Titanium.UI.createAlertDialog({
		title:'添加人员信息',
		message:"人员添加成功",
		buttonNames: ['确定'],
	});
	a.show();
	
});



win2.add(lblName);
win2.add(txtName);



win2.add(lblPassword);
win2.add(txtPassword);

win2.add(btnSubmit);



//
// create base UI tab and root window
//
var win1 = Titanium.UI.createWindow({  
    title:'人员信息展示',
    backgroundColor:'#fff'
});
var tab1 = Titanium.UI.createTab({  
    icon:'KS_nav_views.png',
     title:'人员信息展示',
    window:win1
});


var data = [];
function addRow(idx,nametext,passwordText)
{
	data[idx].add(Ti.UI.createLabel({
		text:nametext,
		height:20,
		width:50,
		left:10,
		right:50,
		top:10,
		textAlign:'left',
		bottom:10
	}));
	data[idx].add(Ti.UI.createLabel({
		text:passwordText,
		height:20,
		width:50,
		left:60,
		right:50,
		top:10,
		textAlign:'center',
		bottom:10
	}));
}





var header = Ti.UI.createView({
	backgroundColor:'#999',
	height:'auto'
});

var headerLabel = Ti.UI.createLabel({
	font:{fontFamily:'Helvetica Neue',fontSize:18,fontWeight:'bold'},
	text:'用户名称',
	color:'#222',
	height:20,
		width:50,
		left:10,
		right:50,
		top:10,
		textAlign:'left',
		bottom:10
});
var headerLabel2 = Ti.UI.createLabel({
	font:{fontFamily:'Helvetica Neue',fontSize:18,fontWeight:'bold'},
	text:'电子邮件',
	color:'#222',
	height:20,
		width:50,
		left:60,
		right:50,
		top:10,
		textAlign:'center',
		bottom:10
});
header.add(headerLabel);
header.add(headerLabel2);


// create table view
var tableview = Titanium.UI.createTableView({
	data:data,
	minRowHeight:80,
	headerView:header,
});

// create table view event listener
tableview.addEventListener('click', function(e)
{
	// event data
	var index = e.index;
	var section = e.section;
	var row = e.row;
	var rowdata = e.rowData;
	var msg = 'row ' + row + ' index ' + index + ' section ' + section  + ' row data ' + rowdata;
	Titanium.UI.createAlertDialog({title:'记录信息',message:msg}).show();
	
});


win1.add(tableview);

tab1.addEventListener('click',function(){
	//查询数据库的表的记录
	var rows = db.execute('SELECT * FROM DATABASETEST');
    //获取中的记录数
	Titanium.API.info('ROW COUNT = ' + rows.getRowCount());
	var index=0;
	while (rows.isValidRow())
	{
		
		data[index] = Ti.UI.createTableViewRow({hasDetail:true,height:'auto'});
		//获取数据集中记录的方式类似java jdbc
		//1.根据下表获取
		//2.根据列的名称获取
		addRow(index,rows.field(0),rows.fieldByName('passwd'));
	    Titanium.API.info('ID: ' + rows.field(0) + ' passwd: ' + rows.fieldByName('passwd') + ' COLUMN NAME ' + rows.fieldName(0));
			
		rows.next();
		index++;
	}
	tableview.setData(data);
	//关闭数据集
	rows.close();
	//db.close();  // close db when you're done to save resources

});

//
//  add tabs
//
tabGroup.addTab(tab2);  
tabGroup.addTab(tab1);  


// open tab group
tabGroup.open();