下面是代码:
//sqlite 常用语句
- [selfopenDataBase];
- [selfcreatetable];
- [selfinserttable];
- [selfquerytable];
- [selfdeletetable];
- [selfquerytable];
实现:
//opendatabase
- -(voID)openDataBase
- {
- NSArray*documentsPaths=NSSearchPathForDirectorIEsInDomains(NSdocumentDirectory,NSUserDomainMask,YES);
- NsString*databasefilePath=[[documentsPathsobjectATindex:0]StringByAppendingPathComponent:@"db.sql"];
- if(sqlite3_open([databasefilePathUTF8String],&database)==@R_755_5607@OK)
- {
- NSLog(@"opensqlitedbok.");
- }
- else
- {
- NSLog(@"cAnnotopensqlitedb");
- //closedatabase
- sqlite3_close(database);
- }
- }
//createtable
- -(voID)createtable
- {
- char*errorMsg;
- constchar*createsql="createtableifnotexistspersons(IDIntegerprimaryKeyautoincrement,nametext)";
-
- if(sqlite3_exec(database,createsql,NulL,&errorMsg)==@R_755_5607@OK)
- {
- NSLog(@"createok.");
- }
- else
- {
- NSLog(@"cAnnotcreatetable");
- [selfErrorReport:(NsString*)createsql];
- }
- }
-
//inserttable
- -(voID)inserttable
- {
- char*errorMsg;
- constchar*insertsql="inserTintopersons(NAME)values('田周辉')";
- if(sqlite3_exec(database,insertsql,&errorMsg)==@R_755_5607@OK)
- {
- NSLog(@"insertok.");
- }
- else
- {
- NSLog(@"cAnnoTinsertit@R_644_10586@ble");
- [selfErrorReport:(NsString*)insertsql];
- }
- }
//error
- -(voID)ErrorReport:(NsString*)item
- {
- char*errorMsg;
- if(sqlite3_exec(database,(constchar*)item,&errorMsg)==@R_755_5607@OK)
- {
- NSLog(@"%@ok.",item);
- }
- else
- {
- NSLog(@"error:%s",errorMsg);
- sqlite3_free(errorMsg);
- }
- }
//query
-(voID)querytable
{
constchar*SELEctsql="SELEctID,namefrompersons";
sqlite3_stmt*statement;
if(sqlite3_prepare_v2(database,SELEctsql,-1,&statement,nil)==@R_755_5607@OK)
{
NSLog(@"SELEctok.");
while(sqlite3_step(statement)==@R_755_5607@ROW)//@R_755_5607@OK@R_755_5607@ROW
{
int_ID=sqlite3_column_int(statement,0);
NsString*name=[[NsStringalloc]initWithCString:(char*)sqlite3_column_text(statement,1)enCoding:NSUTF8StringEnCoding];
NSLog(@"row>>ID%i,name>>%@",_ID,name);
}
}
else
{
//error
[selfErrorReport:(NsString*)SELEctsql];
}
sqlite3_finalize(statement);
}
@H_46_618@//
delete
-(voID)deletetable
{
char*errorMsg;
[selfopenDataBase];
constchar*sql="deletEFROMpersonswherEID=24";
if(sqlite3_exec(database,sql,&errorMsg)==@R_755_5607@OK)
{
NSLog(@"deleteok.");
}
else
{
NSLog(@"cAnnotdeleteit");
[selfErrorReport:(NsString*)sql];
}
}
命令行:
sqlite3database//创建数据库
- cratetabletablefile(IDsmallint,file_namevarchar(256),up_statesmallint,file_sizesmallint);//创建一个表格
- insertintotablefilevalues(1,"200110101.rcu",100,4500);//插入数据
- 如何访问sqlite3数据库 ?
- sqlite3_open//打开数据库
- sqlite3_prepare//将uTF-8格式的sql语句转换为指向已备语句的指针
- sqlite3_column_String//返回某一行的字符串
- sqlite3_finalize//删除一条已备语句
- sqlite3_close//关闭数据库
sourcecode
- -(ID)lookupSingularsQL:(NsString*)sqlforType:(NsString*)rettype{
- sqlite3_stmt*statement;
- IDresult;
- if(statement=[selfprepare:sql]){
- if(sqlite3_step(statement)==@R_755_5607@ROW){
- if([rettypecompare:@"text"]==NSorderedSamE){
- chartemp_buf[256];
- @H_782_9@memset(temp_buf,256);
- sprintf(temp_buf,"%s%s%s%s",(char*)sqlite3_column_text(statement,0),(char*)sqlite3_column_text(statement,1),
- (char*)sqlite3_column_text(statement,2),3));
- result=[NsStringStringWithUTF8String:temp_buf];
- //result=[NsStringStringWithUTF8String:(char*)sqlite3_column_text(statement,0)];
- }elseif([rettypecompare:@"Integer"]==NSorderedSamE){
- result=(ID)sqlite3_column_int(statement,0);
- }
- }
- }
- sqlite3_finalize(statement);
- returnresult;
- }
应该有个成员变量,比如我的代码:
- @interfaceDetailVIEwController:UIVIEwController{
- UIPopoverController*popoverController;
- UIToolbar*toolbar;
- IDdetailitem;
- UILabel*detailDescriptionLabel;
- sqlite3*database;
打开数据库
sqlite数据库是文件数据库,是保存在文件系统中的。因此需要知道文件保存到哪里,可参见iOS中对文件的操作。比如本文保存到documents目录下。代码:
- NSArray*documentsPaths=NSSearchPathForDirectorIEsInDomains(NSdocumentDirectory,YES);
- NsString*databasefilePath=[[documentsPathsobjectATindex:0]StringByAppendingPathComponent:@"mydb"];
- if(sqlite3_open([databasefilePathUTF8String],&database)==@R_755_5607@OK)
- {
- NSLog(@"opensqlitedbok.");
- }
通过ssh查看documents目录,发现mydb文件已经创建。sqlite的策略是如果有该文件就打开,如果没有就创建文件,也就是创建数据库。
这里要注意,使用的是C语法,sqlite3_open传入的是database的地址。
关闭数据库
数据库使用完毕后,要关闭,比如退出应用的时候:
- -(voID)vIEwDIDUnload{
- //ReleaseanyretainedsubvIEwsofthemainvIEw.
- //e.g.self.myOutlet=nil;
- sqlite3_close(database);
- self.popoverController=nil;
- }
建表语句
数据库打开以后,如果没有表,建表:
- char*errorMsg;
- constchar*createsql="createtableifnotexistspersons(IDIntegerprimaryKeyautoincrement,nametext)";
- if(sqlite3_exec(database,&errorMsg)==@R_755_5607@OK){
- NSLog(@"createok.");
- }
这里要特别注意errorMsg传的是地址,因为该函数要通过地址引用来写报错字符信息。
向表中插入记录
和建表语句类似:
- constchar*insertsql="inserTintopersons(NAME)values(‘张三’)";
- if(sqlite3_exec(database,&errorMsg)==@R_755_5607@OK){
- NSLog(@"insertok.");
- }
错误信息的处理
如果在多个地方使用errorMsg,那么每次使用完毕要清空一下字串,比如这样:
- if(sqlite3_exec(database,&errorMsg)==@R_755_5607@OK){
- NSLog(@"createok.");
- }else{
- NSLog(@"error:%s",errorMsg);
- sqlite3_free(errorMsg);
- }
查询结果集
结果集的查询,需要用到statement:
- constchar*SELEctsql="SELEctID,namefrompersons";
- sqlite3_stmt*statement;
- if(sqlite3_prepare_v2(database,nil)==@R_755_5607@OK){
- NSLog(@"SELEctok.");
- }
- while(sqlite3_step(statement)==@R_755_5607@ROW){
- int_ID=sqlite3_column_int(statement,0);
- char*name=(char*)sqlite3_column_text(statement,1);
- NSLog(@"row>>ID%i,name%s",name);
- }
- sqlite3_finalize(statement);
不过这里有个问题,看看打印的日志:
乱码。因为直接用的char类型来做的。
解决办法是,用nsString替代char:
- while(sqlite3_step(statement)==@R_755_5607@ROW){
- int_ID=sqlite3_column_int(statement,0);
- NsString*name=[[NsStringalloc]initWithCString:(char*)sqlite3_column_text(statement,1)enCoding:NSUTF8StringEnCoding];
- NSLog(@"row>>ID%i,name%@",name);
- }
char生成nsString的时候做一次显式的编码。问题解决:
这说明:
写入数据库,用char的方式没有问题,写入数据库的编码是对的;
从库中取出,可能默认使用ascii解码,造成显示乱码。
@R_616_7251@te c API,要经常参考官方函数文档:http://www.sqlite.org/c3ref/funcList.HTML