首页 > 代码库 > iOS 中级数据持久化——简单的数据库（Sqlite3）
iOS 中级数据持久化——简单的数据库（Sqlite3）
sqlite是嵌入式的和轻量级的sql数据库。sqlite是由c实现的。广泛用于包括浏览器（支持html5的大部分浏览器，ie除外）、ios、android以及一些便携需求的小型web应用系统。
数据库无非就是增，删，改，查四种。除了查询以为，其他的三种方法比较类似
//使用数据库之前，打开数据库
- (void)openDB
{
if (db != nil) {
return;
}
//数据库存储在沙河中的caches文件夹下
NSString * cachesPath = [NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES)lastObject];
NSString * dbPath = [cachesPath stringByAppendingPathComponent:@"Class21.sqlite"];
//第一个参数填数据库的完整路径
//如果数据库文件已经存在的话是打开操作，如果数据库文件不存在，则先创建再打开
int result = sqlite3_open([dbPath UTF8String], &db);
if (result == SQLITE_OK) {
NSLog(@"打开数据库成功~");
//创建一个表的sql语句
NSString * createSql = @"CREATE TABLE Students (Number INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, Name TEXT NOT NULL, Gender TEXT NOT NULL, Age TEXT DEFAULT 18)";
//执行sql语句
sqlite3_exec(db, [createSql UTF8String], NULL, NULL, NULL);
}
}
//关闭数据库
- (void)closeDB
{
int result = sqlite3_close(db);
if (result == SQLITE_OK) {
NSLog(@"数据库关闭啦~");
db = nil;
}
}
//插入
- (void)insertNewStudent:(Student *)stu
{
//打开数据库
[self openDB];
//声明跟随指针
sqlite3_stmt * stmt = nil;
//写一个sql语句（插入）
NSString * sql = @"insert into Students (name,gender,age) values(?,?,?)";
//验证sql语句是否正确，-1表示不限制sql语句的长度
int result = sqlite3_prepare_v2(db, [sql UTF8String], -1, &stmt, NULL);
NSLog(@"result = %d",result);
if (SQLITE_OK == result) {
//如果sql语句无误，绑定数据(绑定的时sql语句中的?，即：将?替换为应该存入的值)
//绑定问号时，标记从1开始，不是0
sqlite3_bind_text(stmt, 1, [stu.name UTF8String], -1, NULL);
//绑定第二个问号
sqlite3_bind_text(stmt, 2, [stu.gender UTF8String], -1, NULL);
//绑定第三个问号
sqlite3_bind_int(stmt, 3, stu.age);
//执行sql语句，单步执行
sqlite3_step(stmt);
}
//释放,不释放会造成内存泄露
sqlite3_finalize(stmt);
}
//更新(修改)
- (void)updateStudentName:(NSString *)newName withNumber:(int)number
{
//打开数据库
[self openDB];
//声明跟随指针
sqlite3_stmt * stmt = nil;
//写sql语句（更新、修改）
NSString * sql = @"update Students set name = ? where number = ?";
//验证
int result = sqlite3_prepare_v2(db, [sql UTF8String], -1, &stmt, NULL);
if (SQLITE_OK == result) {
//绑定
sqlite3_bind_text(stmt, 1, [newName UTF8String], -1, NULL);
sqlite3_bind_int(stmt, 2, number);
//指定sql语句，单步执行
sqlite3_step(stmt);
}
//释放
sqlite3_finalize(stmt);
}
//删除
- (void)deleteStudentWithNumber:(int)number
{
//打开数据库
[self openDB];
//声明跟随指针
sqlite3_stmt * stmt = nil;
//写sql语句
NSString * sql= @"delete from Students where number = ?";
NSInteger result = sqlite3_prepare_v2(db, [sql UTF8String], -1, &stmt, NULL);
if (SQLITE_OK == result) {
sqlite3_bind_int64(stmt, 1, number);
sqlite3_step(stmt);
}
sqlite3_finalize(stmt);
}
//搜索
- (NSMutableArray *)selectAllStudents
{
//打开数据库
[self openDB];
//声明跟随指针
sqlite3_stmt * stmt = nil;
//写sql语句
NSString * sql = @"select * from Students";
//验证sql语句是否正确
int result = sqlite3_prepare_v2(db, [sql UTF8String], -1, &stmt, NULL);
if (SQLITE_OK == result) {
//创建一个数组
NSMutableArray * allStudent = [[NSMutableArrayalloc]init];
//执行sql语句，取值
//SQLITE_ROW 有另外一行准备好了
while (sqlite3_step(stmt) == SQLITE_ROW) {
//number name gender age
//取本次循环的结果
//从数据库中取值时，字段的标记从0开始
int number = sqlite3_column_int(stmt, 0);
NSString * name = [NSStringstringWithUTF8String:(constchar *) sqlite3_column_text(stmt, 1)];
NSString * gemder = [NSStringstringWithUTF8String:(constchar *) sqlite3_column_text(stmt, 2)];
int age = sqlite3_column_int(stmt, 3);
//用取出的数据，封装student对象，添加到数组中
Student * stu = [[Student alloc] initWithNumber:number name:name gender:gemder age:age];
[allStudent addObject:stu];
[stu release];
// NSLog(@"number = %d,name = %@,gemder = %@,age = %d",number,name,gemder,age);
}
sqlite3_finalize(stmt);
return allStudent;
}
//释放
sqlite3_finalize(stmt);
return nil;
}
- (NSMutableArray *)selectStudentsWithGender:(NSString *)gender
{
[self openDB];
sqlite3_stmt * stmt = nil;
NSString * sql = @"select name,age from Students where gender = ?";
int result = sqlite3_prepare_v2(db, [sql UTF8String], -1, &stmt, NULL);
NSLog(@"result = %d",result);
if (SQLITE_OK == result) {
NSMutableArray * stuArray = [NSMutableArrayarray];
//先绑定
sqlite3_bind_text(stmt, 1, [gender UTF8String], -1, NULL);
while (sqlite3_step(stmt) == SQLITE_ROW) {
//取值
NSString * name = [NSStringstringWithUTF8String:(constchar *)sqlite3_column_text(stmt, 0)];
int age = sqlite3_column_int(stmt, 1);
//封装student对象，添加到数组中区
Student * stu = [[Student alloc]initWithNumber:0 name:name gender:gender age:age];
[stuArray addObject:stu];
[stu release];
}
sqlite3_finalize(stmt);
return stuArray;
}
sqlite3_finalize(stmt);
return nil;
}