Titanium数据库SQLite的使用

zhaolu

　　在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();