Iphone——使用SQLite3进行简单的数据库操作

1.新建一个View－based Application项目。

2.右击项目中Frameworks文件夹，在其中添加libsqlite3.dylib库。

3.在.h头文件中声明一个类型为sqlite3的变量以及一个获取Documents文件夹下数据库文件目录的方法：

#import <UIKit/UIKit.h> #import "sqlite3.h" @interface SQLiteDataBase : UIViewController { sqlite3 *db;//数据库对象 } -(NSString *)getDocumentsPath;//获取Documents文件夹路径 @end
4.在.m文件中实现头文件中声明的getDocumentsPath方法：

//获取Documents文件夹中文件路径 -(NSString *)getDocumentsPath{ NSArray * paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES); NSString * documentsdir = [paths objectAtIndex:0]; return [documentsdir stringByAppendingPathComponent:@"database.sql"]; }
5.创建并打开数据库，在.m文件中添加如下方法：

//创建打开数据库 -(void) openDB{ if (sqlite3_open([[self getDocumentsPath] UTF8String], &db)!=SQLITE_OK) { sqlite3_close(db); NSAssert(0, @"数据库打开失败");//停止应用程序并关闭数据库连接 } }6.创建表，在.m文件中添加如下方法：

//创建表 -(void)createTableNamed:(NSString *) tableName withFiled1:(NSString *) field1 withField2:(NSString *)field2{ char *err; NSString *sql = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS '%@' ('%@' TEXT PRIMARY KEY, '%@' TEXT);",tableName,field1,field2];//创建表的sql语句 if (sqlite3_exec(db, [sql UTF8String], NULL, NULL, &err)!=SQLITE_OK) { sqlite3_close(db); NSAssert(0, @"表创建失败"); } }
7.插入记录，在.m文件中添加如下方法：

//插入记录 -(void)insertRecordIntoTableNamed:(NSString *)tableName withField1:(NSString *)field1 field1Value:(NSString *)field1Value addField2:(NSString *)field2 field2Value:(NSString *)field2Value{ //第一种方法，对于非查询SQL语句，最好使用这种方法 NSString *sql = [NSString stringWithFormat:@"INSERT OR REPLACE INTO '%@' ('%@','%@') VALUES ('%@','%@')",tableName,field1,field2,field1Value,field2Value]; char *err; if (sqlite3_exec(db, [sql UTF8String], NULL, NULL, &err)!=SQLITE_OK) { sqlite3_close(db); NSAssert(0, @"插入记录失败"); } //下面是另外一种方法 // NSString *sqltwo = [NSString stringWithFormat:@"INSERT OR REPLACE INTO '%@'('%@','%@') VALUES(?,?)",tableName,field1,field2]; // const char *sql = [sqltwo UTF8String]; // sqlite3_stmt *statement; // if (sqlite3_prepare_v2(db, sql, -1, &statement, nil)==SQLITE_OK) { // sqlite3_bind_text(statement, 1, [field1Value UTF8String], -1,NULL); // sqlite3_bind_text(statement, 2, [field2Value UTF8String], -1,NULL); // } // // if (sqlite3_step(statement)!=SQLITE_DONE) { // NSAssert(0, @"插入记录失败"); // sqlite3_finalize(statement); // } }
8.查询记录，在.m文件中添加如下方法：

//查询记录 -(void)getAllRowsFromTableNamed:(NSString *)tableName{ NSString *qsql = [NSString stringWithFormat:@"SELECT * FROM %@",tableName]; sqlite3_stmt *statement; if (sqlite3_prepare_v2(db, [qsql UTF8String], -1, &statement, nil)==SQLITE_OK) { while (sqlite3_step(statement)== SQLITE_ROW) { char *field1 = (char *)sqlite3_column_text(statement, 0); NSString *field1Str = [[NSString alloc]initWithUTF8String:field1]; char *field2 = (char *)sqlite3_column_text(statement, 1); NSString *field2Str = [[NSString alloc]initWithUTF8String:field2]; NSString *str = [[NSString alloc]initWithFormat:@"username:%@,password:%@",field1Str,field2Str]; NSLog(@"%@",str); [field1Str release]; [field2Str release]; [str release]; } sqlite3_finalize(statement); } }
9.最后在viewDidLoad方法里面添加如下代码，调用各个方法，使得一进入这个页面就可以看到打印出来的结果（运行结果在本文最后）：

- (void)viewDidLoad { //创建数据库 [self openDB]; //创建表 [self createTableNamed:@"Login" withFiled1:@"username" withField2:@"password"]; //插入记录 for(int i = 0;i<=2;i++){ NSString *username = [[NSString alloc]initWithFormat:@"helijun%d",i]; NSString *password = [[NSString alloc]initWithFormat:@"ameng%d",i]; [self insertRecordIntoTableNamed:@"Login" withField1:@"username" field1Value:username addField2:@"password" field2Value:password]; [username release]; [password release]; } //查询记录并打印出来 [self getAllRowsFromTableNamed:@"Login"]; //关闭数据库 sqlite3_close(db); [super viewDidLoad]; // Do any additional setup after loading the view from its nib. }
运行结果：