如何使用SQLite3操作数据库

来源：互联网发布：js改变当前url 不跳转编辑：程序博客网时间：2024/05/22 16:03

直接上代码：

#import "StudentController.h"#import "DatabaseHelper.h"#import "Student.h"@interface CLViewController ()@end@implementation CLViewController- (void)viewDidLoad{    [super viewDidLoad];    // Do any additional setup after loading the view, typically from a nib.    //数据库(Database): 存放数据的仓库, 存放的是一张的表, 特别像Excel, Numbers, 都以表格的形式存放数据, 可以创建多张表    //常见的数据库: sqlite, MySQL, SQLServer, Oracle, Access    //为什么要用数据库 1 文件读写和归档读取数据需要一次把数据全部读出来, 占用内存开销大 2 数据库数据效率高, 体现在增删改查    //SQL Structured Query Language 用于对数据库的操作语句 (增删改查)    //SQL 语句不区分大小写, 字符串需要加""或''    //主键: 是一条数据的唯一标示符, 一张表只能有一个主键, 主键不能够重复, 一般把主键名设为"id", 不需要赋值, 会自增    //*代表所有的字段    //where是条件    //创建表: creat table 表名 (字段名 字段数据类型 是否为主键, 字段名 字段数据类型, 字段名 字段数据类型...)    //查: select 字段名 (或者*) from 表名 where 字段名 = 值    //增: insert into 表名 (字段1, 字段2...) values (值1, 值2...)    //改: update 表名 set 字段 = 值 where 字段 = 值    //删: delete from 表名 where 字段 = 值}- (void)didReceiveMemoryWarning{    [super didReceiveMemoryWarning];    // Dispose of any resources that can be recreated.}//查询所有数据- (IBAction)selectAll:(id)sender {    NSMutableArray *array = [DatabaseHelper getAllStudents];    for (Student *stu in array) {        NSLog(@"%@", stu);    }}//查询某一条数据- (IBAction)selectOne:(id)sender {    Student *stu = [DatabaseHelper getStudentWithID:2];    NSLog(@"%@", stu);}//插入一条数据- (IBAction)insertOne:(id)sender {    Student *stu = [[Student alloc] init];    stu.name = @"vaercly";    stu.sex = @"man";    stu.age = 22;    BOOL result = [DatabaseHelper insertStudent:stu];    NSLog(@"%d", result);}//更新某一条数据- (IBAction)updateName:(id)sender {    [DatabaseHelper updateStudentName:@"张三" byID:5];}//删除某条数据- (IBAction)deleteOne:(id)sender {    [DatabaseHelper deleteStudentWithID:5];}@end

//数据库操作类

#define FILE_NAME       @"Database.sqlite"#import "Database.h"static sqlite3 *db = nil;@implementation Database//打开数据库+ (sqlite3 *)openDB{    if (!db) {        //1 获取document文件夹的路径        //参数1: 文件夹的名字 参数2: 查找域 参数3: 是否使用绝对路径        NSString *docPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) firstObject];        //获取数据库文件的路径        NSString *dbPath = [docPath stringByAppendingPathComponent:FILE_NAME];        //iOS 中管理文件的类, 负责复制文件, 删除文件, 移动文件        NSFileManager *fm = [NSFileManager defaultManager];        //判断document中是否有sqlite文件        if (![fm fileExistsAtPath:dbPath]) {            //获取在*.app中sqlite文件的路径            NSString *boundlePath = [[NSBundle mainBundle] pathForResource:@"Database" ofType:@"sqlite"];            NSError *error = nil;            //将*.app中sqlite文件复制一份到dbPath            BOOL result = [fm copyItemAtPath:boundlePath toPath:dbPath error:&error];            //若复制文件失败, 打印错误信息            if (!result) {                NSLog(@"%@", error);            }        }        //打开数据库 参数1: 文件路径(UTF8String可以将OC的NSString转为C中的char) 参数2: 接受数据库的指针        sqlite3_open([dbPath UTF8String], &db);    }    return db;}//关闭数据库+ (void)closeDB{    sqlite3_close(db);    db = nil;}@end

//增删改查封装类

#import "DatabaseHelper.h"#import "Student.h"#import "Database.h"@implementation DatabaseHelper//查询所有学生+ (NSMutableArray *)getAllStudents{    //打开数据库    sqlite3 *db = [Database openDB];    //数据库操作指针 stmt:statement    sqlite3_stmt *stmt = nil;    //验证SQL的正确性 参数1: 数据库指针, 参数2: SQL语句, 参数3: SQL语句的长度 -1代表无限长(会自动匹配长度), 参数4: 返回数据库操作指针, 参数5: 为未来做准备的, 预留参数, 一般写成NULL    int result = sqlite3_prepare_v2(db, "select * from Student", -1, &stmt, NULL);    NSMutableArray *studentArr = [NSMutableArray array];    //判断SQL执行的结果    if (result == SQLITE_OK) {        while (sqlite3_step(stmt) == SQLITE_ROW) {//存在一行数据            //列数从0开始            int ID = sqlite3_column_int(stmt, 0);            const unsigned char *name = sqlite3_column_text(stmt, 1);            const unsigned char *sex = sqlite3_column_text(stmt, 2);            int age = sqlite3_column_int(stmt, 3);            //blob类型的获取            //1 获取长度            int length = sqlite3_column_bytes(stmt, 4);            //2 获取数据            const void *photo = sqlite3_column_blob(stmt, 4);            //3 转成NSData            NSData *photoData = [NSData dataWithBytes:photo length:length];            //4 转成UIImage            UIImage *image = [UIImage imageWithData:photoData];            //封装Student模型            Student *student = [[Student alloc] init];            student.ID = ID;            student.name = [NSString stringWithUTF8String:(const char *)name];            student.sex = [NSString stringWithUTF8String:(const char *)sex];            student.age = age;            student.photo = image;            //添加到数组            [studentArr addObject:student];        }    }    //释放stmt指针    sqlite3_finalize(stmt);    //关闭数据库    [Database closeDB];    return studentArr;}//查询单个学生+ (Student *)getStudentWithID:(NSInteger)aID{    sqlite3 *db = [Database openDB];    sqlite3_stmt *stmt = nil;    NSString *sqlStr = [NSString stringWithFormat:@"select * from Student where id = %d", aID];    int result = sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &stmt, NULL);    Student *student = nil;    if (result == SQLITE_OK) {        if (sqlite3_step(stmt) == SQLITE_ROW) {            int ID = sqlite3_column_int(stmt, 0);            const unsigned char *name = sqlite3_column_text(stmt, 1);            const unsigned char *sex = sqlite3_column_text(stmt, 2);            int age = sqlite3_column_int(stmt, 3);            int length = sqlite3_column_bytes(stmt, 4);            const unsigned char *photo = sqlite3_column_blob(stmt, 4);            NSData *photoData = [NSData dataWithBytes:photo length:length];            UIImage *image = [UIImage imageWithData:photoData];            student = [[Student alloc] init];            student.ID = ID;            student.name = [NSString stringWithUTF8String:(const char *)name];            student.sex = [NSString stringWithUTF8String:(const char *)sex];            student.age = age;            student.photo = image;        }    }    sqlite3_finalize(stmt);    [Database closeDB];    return student;}//添加一个新学生+ (BOOL)insertStudent:(Student *)aStudent{    sqlite3 *db = [Database openDB];    sqlite3_stmt *stmt = nil;    NSString *sqlStr = [NSString stringWithFormat:@"insert into Student (name, sex, age) values ('%@', '%@', '%d')", aStudent.name, aStudent.sex, aStudent.age];    int result = sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &stmt, NULL);    if (result == SQLITE_OK) {        //判断语句执行完成没有        if (sqlite3_step(stmt) == SQLITE_DONE) {            sqlite3_finalize(stmt);            [Database closeDB];            return YES;        }    }    sqlite3_finalize(stmt);    [Database closeDB];    return NO;}//修改学生的姓名+ (BOOL)updateStudentName:(NSString *)aName byID:(NSInteger)aID{    sqlite3 *db = [Database openDB];    sqlite3_stmt *stmt = nil;    NSString *sqlStr = [NSString stringWithFormat:@"update Student set name = '%@' where id = %d", aName, aID];    int result = sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &stmt, NULL);    if (result == SQLITE_OK) {        if (sqlite3_step(stmt) == SQLITE_ROW) {//觉的应加一个判断, 若有这一行则修改            if (sqlite3_step(stmt) == SQLITE_DONE) {                sqlite3_finalize(stmt);                [Database closeDB];                return YES;            }        }    }    sqlite3_finalize(stmt);    [Database closeDB];    return NO;}//删除一个学生+ (BOOL)deleteStudentWithID:(NSInteger)aID{    sqlite3 *db = [Database openDB];    sqlite3_stmt *stmt = nil;    NSString *sqlStr = [NSString stringWithFormat:@"delete from Student where id = %d", aID];    int result = sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &stmt, NULL);    if (result == SQLITE_OK) {        if (sqlite3_step(stmt) == SQLITE_ROW) {//觉的应加一个判断, 若有这一行则删除            if (sqlite3_step(stmt) == SQLITE_DONE) {                sqlite3_finalize(stmt);                [Database closeDB];                return YES;            }        }    }    sqlite3_finalize(stmt);    [Database closeDB];    return NO;}@end

model类

#import <Foundation/Foundation.h>#import <UIKit/UIKit.h>@interface Student : NSObject@property (nonatomic, assign) NSInteger ID;@property (nonatomic, retain) NSString *name;@property (nonatomic, retain) NSString *sex;@property (nonatomic, assign) NSInteger age;@property (nonatomic, retain) UIImage *photo;@end

0 0