我现在要使用SQLite3.0创建一个数据库，然后在数据库中创建一个表格。
首先要引入SQLite3.0的lib库。然后包含头文件#import <sqlite3.h>
【1】打开数据库，如果没有，那么创建一个
sqlite3* database_;
-(BOOL) open{
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0];
NSString *path = [documentsDirectory stringByAppendingPathComponent:@"mydb.sql"];
NSFileManager *fileManager = [NSFileManager defaultManager];
BOOL find = [fileManager fileExistsAtPath:path];
//找到数据库文件mydb.sql
if (find) {
NSLog(@"Database file have already existed.");
if(sqlite3_open([path UTF8String], &database_) != SQLITE_OK) {
sqlite3_close(database_);
NSLog(@"Error: open database file.");
return NO;
}
return YES;
}
if(sqlite3_open([path UTF8String], &database_) == SQLITE_OK) {
bFirstCreate_ = YES;
[self createChannelsTable:database_];//在后面实现函数createChannelsTable
return YES;
} else {
sqlite3_close(database_);
NSLog(@"Error: open database file.");
return NO;
}
return NO;
}
数据库的名称不一定以.db结尾，也可以以.sqlite结尾
现在网站开发和软件开发，数据库的支持是少不了的；在iPhone开发中，作为数据持久化的解决方案中，SQLite是不错的选择，它既轻量占用资源少，又可以方便嵌入到程序中，在一些嵌入式设备中有着广泛使用。SQLite提供了命令行工具sqlite3，创建创建库。
cjdx@~/Desktop$ sqlite3 school.sqlite3SQLite version 3.6.12Enter ".help" for instructionsEnter SQL statements terminated with a ";"sqlite>
创建student表
sqlite> create table student (key integer primary key, name text, age integer, birth date);
插入三条数据
sqlite> insert into student (name, age, birth) values ('zhangsan', 18, '1980-01-09');sqlite> insert into student (name, age, birth) values ('lisi', 20, '1980-10-05');sqlite> insert into student (name, age, birth) values ('wangwu', 28, '1985-12-20');查询刚刚插入的数据
sqlite> select * from student;1|zhangsan|18|1980-01-092|lisi|20|1980-10-053|wangwu|28|1985-12-20
SQLite函数知识准备
sqlite3_open 打开数据库sqlite3_close 关闭数据库sqlite3_prepare 预编译SQL语句sqlite3_step 执行预编译后的SQLsqlite3_finalize 释放资源
打开Xcode，创建iPhone项目，基于“Single View Application”，并命名为TestSQLite，把刚才创建的school.sqlite3添加项目“Supporting Files”目录。
先写些测试代码，看看sqlite能不能正常工作，在ViewController.m中添加如下代码：
// ViewController.m #import "ViewController.h"#import "/usr/include/sqlite3.h" @implementation ViewController ... #pragma mark - View lifecycle - (void)viewDidLoad{ [super viewDidLoad]; // Do any additional setup after loading the view, typically from a nib. sqlite3 *db; char *szError = NULL; sqlite3_stmt *dbps; NSString *dbFile = [[NSBundle mainBundle] pathForResource:@"school" ofType:@"sqlite3"]; if (sqlite3_open([dbFile UTF8String], &db) != SQLITE_OK) { NSLog(@"failed to open db."); } NSString *sql_insert = @"insert into student (name, age, birth) values ('testdata', 16, '1987-09-18')"; if (sqlite3_exec(db, [sql_insert UTF8String], 0, 0, &szError) == SQLITE_OK) { NSLog(@"%d", sqlite3_changes(db)); } NSString *sql_select = @"SELECT * FROM student"; sqlite3_prepare_v2(db, [sql_select UTF8String], -1, &dbps, NULL); int nResult = sqlite3_step(dbps); for (int fld = 0; fld < sqlite3_column_count(dbps); fld++) { NSLog(@"%s", sqlite3_column_name(dbps, fld)); } while (nResult != SQLITE_DONE) { NSLog(@"%s|%s|%s|%s", sqlite3_column_text(dbps, 0), sqlite3_column_text(dbps, 1), sqlite3_column_text(dbps, 2), sqlite3_column_text(dbps, 3)); nResult = sqlite3_step(dbps); } sqlite3_close(db);}...@end把sqlite3连接库添加到项目
输出结果：
2012-02-06 18:59:33.372 TestSQLite[4011:207] 12012-02-06 18:59:33.375 TestSQLite[4011:207] key2012-02-06 18:59:33.377 TestSQLite[4011:207] name2012-02-06 18:59:33.379 TestSQLite[4011:207] age2012-02-06 18:59:33.380 TestSQLite[4011:207] birth2012-02-06 18:59:33.384 TestSQLite[4011:207] 1|zhangsan|18|1980-01-092012-02-06 18:59:33.386 TestSQLite[4011:207] 2|lisi|20|1980-10-052012-02-06 18:59:33.387 TestSQLite[4011:207] 3|wangwu|28|1985-12-202012-02-06 18:59:33.405 TestSQLite[4011:207] 4|testdata|16|1987-09-18
在Objc中直接用SQLite的C API写，如果每个查询都这样，太繁琐了，而且也不OO，所以还是要封装一下的，这样就可以把先前学过的知识串起来，写个小程序，用TableView来显示数据，支持数据的输入、删除。
- 1.创建Student模型类
- 2.创建StudentDB类
- 3.创建TableViewController用来显示数据
- 4.创建添加数据界面和代码的实现
- 5.删除代码的实现
接下来就按照这个步骤，一步步实现这些功能。
创建Student模型类
// Student.h #import <Foundation/Foundation.h> @interface Student : NSObject{ int uniqueId; NSString *name; int age; NSDate *birth;} @property (nonatomic, assign) int uniqueId;@property (nonatomic, retain) NSString *name;@property (nonatomic, assign) int age;@property (nonatomic, retain) NSDate *birth; - (id)initWithUniqueId:(int)uniqueId name:(NSString *)name age:(int)age birth:(NSDate *)birth; @end// Student.m #import "Student.h" @implementation Student @synthesize uniqueId, name, age, birth; - (id)initWithUniqueId:(int)uniqueId name:(NSString *)name age:(int)age birth:(NSDate *)birth{ self = [super init]; if (self) { self.uniqueId = uniqueId; self.name = name; self.age = age; self.birth = birth; } return self;} - (void)dealloc{ name = nil; birth = nil; [super dealloc];} @end创建StudentDB类
StudentDB类是sqlite的简单封装，获取的数据被包装到Student类中，有点ORM的感觉，代码长的就不贴代码，只贴一些关键代码。
// StudentDB.m #import "StudentDB.h"#import "Student.h" @implementation StudentDB @synthesize db; - (id)init{ self = [super init]; if (self) { NSString *dbFile = [[NSBundle mainBundle] pathForResource:@"school" ofType:@"sqlite3"]; if (sqlite3_open([dbFile UTF8String], &db) != SQLITE_OK) { NSLog(@"failed to open db."); } } return self;} -(int)getStudentsCount{...} - (NSMutableArray *)getAllStudent{ sqlite3_stmt *pStmt; NSMutableArray *studentArray = [[NSMutableArray alloc] init]; NSString *sql = @"SELECT * FROM student;"; sqlite3_prepare_v2(db, [sql UTF8String], -1, &pStmt, nil); while (SQLITE_ROW == sqlite3_step(pStmt)) { int uniqueId = sqlite3_column_int(pStmt, 0); NSString *name = [[NSString alloc] initWithUTF8String:(const char*)sqlite3_column_text(pStmt, 1)]; int age = sqlite3_column_int(pStmt, 2); NSDateFormatter *formate = [[NSDateFormatter alloc] init]; [formate setDateFormat:@"yyyy-MM-dd"]; NSDate *birth= [formate dateFromString:[[NSString alloc] initWithUTF8String:(const char *)sqlite3_column_text(pStmt, 3)]]; Student *student = [[Student alloc] initWithUniqueId:uniqueId name:name age:age birth:birth]; [studentArray addObject:student]; [formate release]; [name release]; [student release]; } sqlite3_finalize(pStmt); return studentArray;} - (void)removeStudent:(Student *)person{...} - (void)addStudent:(Student *)person{...} - (void)dealloc{ sqlite3_close(db); [super dealloc];} @end创建TableViewController用来显示数据
通过StudentDB类的getAllStudent方法获取所有学生的数组作为数据源
// StudentListViewController.h #import <UIKit/UIKit.h> @class StudentDB; @interface StudentListViewController : UITableViewController{ StudentDB *db; NSMutableArray *students;} @end#import "StudentListViewController.h"#import "StudentDB.h"#import "Student.h"#import "AddStudentViewController.h" @implementation StudentListViewController - (void)viewDidLoad{ // 初始化db db = [[StudentDB alloc] init]; [super viewDidLoad];} - (void)viewWillAppear:(BOOL)animated{ // 获取所有学生数据保存到students数组中 students = [db getAllStudent]; [self.tableView reloadData]; [super viewWillAppear:animated];} - (NSInteger)numberOfSectionsInTableView:(UITableView *)tableView{ return 1;} - (NSInteger)tableView:(UITableView *)tableView numberOfRowsInSection:(NSInteger)section{ // 返回行数 return students.count;} - (UITableViewCell *)tableView:(UITableView *)tableView cellForRowAtIndexPath:(NSIndexPath *)indexPath{ static NSString *CellIdentifier = @"Cell"; UITableViewCell *cell = [tableView dequeueReusableCellWithIdentifier:CellIdentifier]; if (cell == nil) { cell = [[[UITableViewCell alloc] initWithStyle:UITableViewCellStyleSubtitle reuseIdentifier:CellIdentifier] autorelease]; } // Configure the cell... Student *student = [students objectAtIndex:indexPath.row]; cell.textLabel.text = student.name; NSDateFormatter *dateFormatter = [[NSDateFormatter alloc] init]; [dateFormatter setDateFormat:@"yyyy-MM-dd"]; NSString *strBirth = [dateFormatter stringFromDate:student.birth]; NSString *strDescription = [[NSString alloc] initWithFormat:@"年龄:%d 生日:%s", student.age, [strBirth UTF8String]]; [cell.detailTextLabel setText:strDescription]; [dateFormatter release]; [strDescription release]; return cell;} - (void)dealloc{ [students release]; [db release]; [super dealloc];} @end数据显示效果图：
创建添加数据界面和代码的实现
通过点击导航右边的的“+”号按钮来显示，录入学生资料界面，界面通过纯代码创建，点击导航栏右边的”Done”来完成录入数据工作，然后返回学生列表界面执行reloadData操作，这样新录入的数据就能显示出来了
// AddStudentViewController.m // 执行添加数据到数据库中的操作，没有验证性操作- (void)doneButtonPushed:(id)sender{ StudentDB *db = [[StudentDB alloc] init]; NSString *strName = txtName.text; int age = [txtAge.text intValue]; NSString *strBirth = txtBirth.text; NSDateFormatter *dateFormatter = [[NSDateFormatter alloc] init]; [dateFormatter setDateFormat:@"yyyy-MM-dd"]; Student *student = [[Student alloc] initWithUniqueId:0 name:strName age:age birth:[dateFormatter dateFromString:strBirth]]; [db addStudent:student]; [student release]; [db release]; [dateFormatter release]; [self.navigationController popViewControllerAnimated:YES];}删除代码的实现
- (void)tableView:(UITableView *)tableView commitEditingStyle:(UITableViewCellEditingStyle)editingStyle forRowAtIndexPath:(NSIndexPath *)indexPath{ if (editingStyle == UITableViewCellEditingStyleDelete) { // Delete the row from the data source Student *student = [students objectAtIndex:indexPath.row]; //删除数据库中的数据 [db removeStudent:student]; //删除数组中的数据 [students removeObjectAtIndex:indexPath.row]; //删除TableView中的数据 [tableView deleteRowsAtIndexPaths:[NSArray arrayWithObject:indexPath] withRowAnimation:UITableViewRowAnimationFade]; } else if (editingStyle == UITableViewCellEditingStyleInsert) { // Create a new instance of the appropriate class, insert it into the array, and add a new row to the table view } }完整代码
下载
