可以进行数据库建表,以及增删改査等操作
使用前需要导入sqlite3数据库包
SandBoxPath.h
#import <Foundation/Foundation.h>
@interface SandBoxPath : NSObject
//沙盒的主路径
+ (NSString *)homePath;
//沙盒中Documents路径
+ (NSString *)documents;
//沙盒中libraray路径
+ (NSString *)library;
//libraryCaches路径
+ (NSString *)libraryCaches;
//temp路径
+ (NSString *)temp;
@end
SandBoxPath.m
#import "SandBoxPath.h"
@implementation SandBoxPath
//得到沙盒主目录
+ (NSString *)homePath
{
return NSHomeDirectory();
}
//得到Documents文件夹目录
+ (NSString *)documents
{
return NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES).firstObject;
}
//得到Library文件夹目录
+ (NSString *)library
{
return NSSearchPathForDirectoriesInDomains(NSLibraryDirectory, NSUserDomainMask, YES).firstObject;
}
//得到caches文件夹
+ (NSString *)libraryCaches
{
return NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES).firstObject;
}
//得到tem文件夹
+ (NSString *)temp
{
return NSTemporaryDirectory();
}
@end
DataBaseHelper.h
#import <Foundation/Foundation.h>
#import <sqlite3.h>
@interface DataBaseHelper : NSObject
+ (instancetype)sharedDatabaseHelper;
//创建数据库文件路径
- (void)dbFilePathWithFileName: (NSString *)fileName;
//除了查询语句以外的所有语句
- (void)operationSQLWithSqlString: (NSString *)sqlstr;
//查询语句
- (NSArray *)queryDataWithSqlstring: (NSString *)sqlString;
@end
DataBaseHelper.m
#import "DataBaseHelper.h"
#import "SandBoxPath.h"
@interface DataBaseHelper ()
@property (nonatomic,retain) NSString *fileName;
@end
@implementation DataBaseHelper
+ (instancetype)sharedDatabaseHelper
{
static DataBaseHelper *dataBaseHelper = nil;
@synchronized(self) {
if (!dataBaseHelper) {
dataBaseHelper = [[DataBaseHelper alloc] init];
}
return dataBaseHelper;
}
}
//创建数据库文件路径
- (void)dbFilePathWithFileName: (NSString *)fileName
{
//得到沙盒的documents路径
NSString *docuPath = [SandBoxPath documents];
//判断文件名是否带后缀,如果有直接使用,如果没有,内部为它拼接
if (![fileName containsString:@".sqlite"])
{
fileName = [fileName stringByAppendingString:@".sqlite"];
}
self.fileName = [docuPath
stringByAppendingPathComponent:fileName];
}
//打开或者创建数据库
- (sqlite3 *)openOrCreateDB
{
sqlite3 *sqlDB = NULL;
//打开数据库
int result = sqlite3_open(self.fileName.UTF8String, &sqlDB);
if (result == SQLITE_OK) {
NSLog(@"数据库打开成功");
return sqlDB;
}
else
{
NSLog(@"数据库打开失败");
return NULL;
}
}
//除了查询操作,其他操作所执行的方法
- (void)operationSQLWithSqlString: (NSString *)sqlstr
{
sqlite3 *sqlDB = [self openOrCreateDB];
int result = sqlite3_exec(sqlDB, sqlstr.UTF8String, NULL, NULL, NULL);
if (result == SQLITE_OK) {
NSLog(@"语句执行成功");
//每次执行完成之后,需要关闭数据库
sqlite3_close(sqlDB);
NSLog(@"%@",self.fileName);
}
else
{
NSLog(@"语句执行失败");
//每次执行完成之后,需要关闭数据库
sqlite3_close(sqlDB);
}
}
//数据库查询,通用
- (NSArray *)queryDataWithSqlstring: (NSString *)sqlString
{
//初始化可变数据,用来盛放所有记录
NSMutableArray *resultArray = [[NSMutableArray alloc] init];
//打开数据库
sqlite3 *sqlDB = [self openOrCreateDB];
//声明stmt指针,一会用来盛放
sqlite3_stmt *stament = NULL;
int result = sqlite3_prepare(sqlDB, sqlString.UTF8String, -1, &stament, NULL);
if (result == SQLITE_OK) {
//没执行一次step函数,stament中就会完整的保存一条记录
while (sqlite3_step(stament) == SQLITE_ROW) {
//确定该表有几个字段,其实就是有几列
int columnCount = sqlite3_column_count(stament);
NSMutableDictionary *mDict = [NSMutableDictionary dictionary];
//遍历所有的列
for (int i = 0; i < columnCount; i++) {
int type = sqlite3_column_type(stament, i);
//得到字段名称
const char *attName = sqlite3_column_name(stament, i);
NSString *keyString = [NSString stringWithCString:attName encoding:NSUTF8StringEncoding];
switch (type) {
case SQLITE_INTEGER:
{
int value = sqlite3_column_int(stament,i);
[mDict setObject:[NSNumber numberWithInt:value] forKey:keyString];
}
break;
case SQLITE_TEXT:
{
NSString *valueString = [NSString stringWithCString:(const char *)sqlite3_column_text(stament, i) encoding:NSUTF8StringEncoding];
[mDict setObject:valueString forKey:keyString];
}
break;
case SQLITE_FLOAT:
{
float value = sqlite3_column_int(stament,i);
[mDict setObject:[NSNumber numberWithFloat:value] forKey:keyString];
}
break;
default:
break;
}
}
[resultArray addObject:mDict];
}
}
sqlite3_close(sqlDB);
sqlite3_finalize(stament);
return resultArray;
}
@end