iOS原生SQLite3使用

最新推荐文章于 2024-09-20 17:58:20 发布

学以致用-用以致富

最新推荐文章于 2024-09-20 17:58:20 发布

阅读量885

点赞数

分类专栏： iOS 文章标签： ios sqlite3

iOS 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

#import <Foundation/Foundation.h>

#import <sqlite3.h>

@interface Database : NSObject

+ (sqlite3 *)openDB;

+ (void)closeDB;

@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 *documentPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0];

//获取数据库文件的路径

NSString *dbPath = [documentPath stringByAppendingPathComponent:@"Database.sqlite"];

//iOS中管理文件的类,负责复制文件,删除文件,移动文件

// NSFileManager *fm = [NSFileManager defaultManager];

//判断document中是否有sqlite文件

int result = sqlite3_open([dbPath UTF8String], &db);

if (result == SQLITE_OK) {

NSLog(@"打开数据库");

}else{

NSLog(@"打开数据库失败");

}

// 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:文件路径(UIF8String 可以将OC的NSString转为c中的char)参数2:接受数据库的指针

}

return db;

}

+ (void)closeDB{

sqlite3_close(db);

db = nil;

}

@end

#import <Foundation/Foundation.h>

#import "Student.h"

@interface DatabaseHelper : NSObject

+ (NSMutableArray *)getAllStudents;

+ (Student *)getStudentWithID:(NSInteger)aID;

+ (BOOL)insertStudent:(Student *)aStudent;

+ (BOOL)updateStudentName:(NSString *)aName byID:(NSInteger)aID;

+ (BOOL)deleteStudentWithID:(NSInteger)aID;

+ (void)createTablels;

@end

#import "DatabaseHelper.h"

#import "Database.h"

#import <UIKit/UIKit.h>

@implementation DatabaseHelper

+ (void)createTablels{

sqlite3 *db = [Database openDB];

//create table if not exists t_persons (c_id integer primary key autoincrement,c_deviceType text,c_UDN text,c_friendlyName text,c_history_time integer,c_desc text)

char *sql = "create table if not exists t_persons (c_id integer primary key autoincrement,c_name text,c_sex text,c_age integer,c_desc text)";

char *error;

//执行创建语句并接受

int result = sqlite3_exec(db, sql, NULL, NULL, &error);

//判断是否创建成功

if (result != SQLITE_OK) {

NSLog(@"创建数据表失败 %s",error);

}else{

NSLog(@"数据库表创建成功");

}

[Database closeDB];

}

+ (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 t_persons", -1, &stmt, NULL);

NSMutableArray *studentArr = @[].mutableCopy;

//判断SQL执行的结果

if (result == SQLITE_OK) {

//存在一行数据

while (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);

//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 t_persons where c_id = %ld", (long)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;

//c_name c_sex c_age

NSString *sqlStr = [NSString stringWithFormat:@"insert into t_persons (c_name,c_sex,c_age,c_desc) values ('Silence of the Lambs, The', 'Jodie Foster', 1991, 'Jodie Foster');"];

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;

}