我现在要使用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.sqlite3
SQLite version 3.6.12
Enter ".help" for instructions
Enter 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-09
2|lisi|20|1980-10-05
3|wangwu|28|1985-12-20 SQLite函数知识准备sqlite3_open 打开数据库
sqlite3_close 关闭数据库
sqlite3_prepare 预编译SQL语句
sqlite3_step 执行预编译后的SQL
sqlite3_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] 1
2012-02-06 18:59:33.375 TestSQLite[4011:207] key
2012-02-06 18:59:33.377 TestSQLite[4011:207] name
2012-02-06 18:59:33.379 TestSQLite[4011:207] age
2012-02-06 18:59:33.380 TestSQLite[4011:207] birth
2012-02-06 18:59:33.384 TestSQLite[4011:207] 1|zhangsan|18|1980-01-09
2012-02-06 18:59:33.386 TestSQLite[4011:207] 2|lisi|20|1980-10-05
2012-02-06 18:59:33.387 TestSQLite[4011:207] 3|wangwu|28|1985-12-20
2012-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
}
} 完整代码 下载
|