iOS sql的简单封装

//

//  GroupDBManager.h

#import <Foundation/Foundation.h>

#import "GroupDBmodel.h"

@interface GroupDBManager : NSObject

//此方法返回DBManager的单例

+(GroupDBManager *)shareManager;

// 添加一条数据

- (void)insertDataWithModel:(GroupDBmodel *)model;

- (void)deleteMessageWithChatId:(NSString*)chatId;

// 修改数据库中的某一条数据

//- (void)updateWithModel:(GroupDBmodel *)model uuid:(int)uuid;

/** 更新数据的创建时间

 @pragma createTime     创建信息的时间

 @pragma status         发送状态（0 未发送 1 已发送）

 @pragma uuid           数据库表中信息的uuid

 */

//- (BOOL)updateModelWithCreateTime:(NSString *)createTime andStatus:(NSString *)status andUuid:(int)uuid;

// 获取全部数据

- (NSArray *)fetchAllgroupBulletin;

// 获取对应 questionID 下的所有数据

- (NSArray *)fetchAllHistoryWithQuestionID:(NSString *)questionID;

// 获取对应 questionID 下的最大时间

- (NSString *)findmaxtime:(NSString *)questionID;

// 获取一个新的uuid(uuid里的最大值)

- (int)findMaxUuid;

// 判断是否存在该条记录

- (BOOL)isExistModel:(NSString*)chatId;

// 删除整个表

- (void)deleteDBFile;

//

//  GroupDBManager.m

#import "GroupDBManager.h"

#import "FMDatabase.h"//第三方开源库，里面封装了对苹果原生sqlite3轻量级数据库的操作

@implementation GroupDBManager

{

FMDatabase *_dataBase;

}

//被 static 关键字修饰的变量，只执行一次初始化操作，并且变量会一直持有第一次初始化得到的对象

staticGroupDBManager *manager = nil;

+(GroupDBManager *)shareManager{

if (manager == nil) {

manager = [[GroupDBManageralloc] init];

    }

returnmanager;

}

//重写init方法

- (id)init{

self = [superinit];

if (self) {

//为数据库文件指定创建的路径

NSString *dbPath = [NSHomeDirectory() stringByAppendingFormat:@"/Documents/groupBulletin.db"];

//初始化FMDataBase对象，并传递数据库的路径

_dataBase = [[FMDatabasealloc] initWithPath:dbPath];

//open 两层含义:如果指定路径没有user.db的数据库文件，会创建此文件并打开;如果指定路径有数据库文件则直接打开

//如果创建并打开成功 return yes

        [selfcreateDBFile];

    }

returnself;

}

// 创建数据库文件

- (void)createDBFile{

if ([_dataBaseopen]) {

//创建一个表, blob 代表二进制的对象

NSString *createSql = @"CREATE TABLE IF NOT EXISTS groupBulletin (id integer primary key autoincrement , chatter varchar(256) , chatId varchar(256) , groupText varchar(256) , headPhoto varchar(256) , senderName varchar(256) , senderTime varchar(256))";

//executeUpdate 执行sql语句的方法,创建表、增、删、改的sql语句全用此方法执行

//执行sql语句成功返回YES

BOOL isSuccessed =[_dataBaseexecuteUpdate:createSql];

if (!isSuccessed) {

//创建表失败,lastErrorMessage 获取到出错的信息

NSLog(@"error:%@",_dataBase.lastErrorMessage);

        }

    }

}

//添加一条数据

- (void)insertDataWithModel:(GroupDBmodel *)model{

//        UIImage *headImage = model.img;

//    //转化成NSData (将png格式的图片转化成NSData，几乎是无损耗转化)

//        NSData *data = UIImagePNGRepresentation(headImage);

//    //sql语句中，用?作为占位符,不管是什么样类型的数据

//    [self createDBFile];

if ([selfisExistModel:model.chatId]) {

NSLog(@"存在该条");

return;

    }

else {

NSString * insertSQL = @"INSERT INTO groupBulletin(chatter,chatId,groupText,headPhoto,senderName , senderTime) VALUES(?,?,?,?,?,?)";

BOOL isSuccessed = [_dataBaseexecuteUpdate:insertSQL,model.chatter,model.chatId,model.groupText,model.headPhoto,model.senderName,model.senderTime];

if (!isSuccessed) {

NSLog(@"insertError:%@",_dataBase.lastErrorMessage);

        }

    }

}

//删除某条数据

- (void)deleteMessageWithChatId:(NSString*)chatId

{

NSString *deleteSQL=@"delete from groupBulletin where chatId = ?";

BOOL isSuccessed = [_dataBaseexecuteUpdate:deleteSQL,chatId];

if (!isSuccessed) {

NSLog(@"insertError:%@",_dataBase.lastErrorMessage);

    }

}

- (BOOL)isExistModel:(NSString*)chatId{

FMResultSet * set = [_dataBaseexecuteQuery:@"SELECT * FROM groupBulletin WHERE chatId = ?",[NSNumbernumberWithInt:[chatId intValue]]];

if ([set next]) {

returnYES;

    }

else {

returnNO;

    }

}

//修改数据库中的某一条数据

//- (void)updateWithModel:(GroupDBmodel *)model id:(int)id{

//

//    NSString *updateSql = @"update groupBulletin set chatId = ?  where id =?";

//    BOOL isSuccessed = [_dataBase executeUpdate:updateSql,model.chatId,[NSNumber numberWithInt:id]];

//    if (!isSuccessed) {

//        NSLog(@"update error:%@",_dataBase.lastErrorMessage);

//    }

//}

/** 更新数据的创建时间

 */

- (BOOL)updateModelWithCreateTime:(NSString *)createTime andStatus:(NSString *)status andUuid:(int)uuid{

NSString * updateSQL = @"UPDATE groupBulletin SET createTime = ?,status = ? WHERE uuid = ?";

BOOL isSucc = [_dataBaseexecuteUpdate:updateSQL,createTime,status,[NSNumbernumberWithInt:uuid]];

if (!isSucc) {

NSLog(@"update error:%@",_dataBase.lastErrorMessage);

returnNO;

    }

returnYES;

}

//获取全部数据

- (NSArray *)fetchAllgroupBulletin{

NSString *selectSql = @"select * from groupBulletin";

//executeQuery 用于执行查询的sql语句

//FMResultSet 用于存放查询结果的类

FMResultSet *set =[_dataBaseexecuteQuery:selectSql];

//next方法，从第一条数据开始取，一直取到最后一条，取到当前数据返回YES,取不到则返回NO

NSMutableArray *array = [NSMutableArrayarray];

while ([set next]) {

GroupDBmodel *model = [[GroupDBmodelalloc] init];

//stringForColumn 根据字段的名称，取到字段的值

        model.chatId = [set  stringForColumn:@"chatId"];

          }

return array;

}

// 获取对应 questionID 下的所有数据

- (NSArray *)fetchAllHistoryWithQuestionID:(NSString *)questionID{

NSString *selectSql = @"select * from groupBulletin WHERE chatter = ?";

//    //executeQuery 用于执行查询的sql语句

//    //FMResultSet 用于存放查询结果的类

FMResultSet *set =[_dataBaseexecuteQuery:selectSql,questionID];

//    //next方法，从第一条数据开始取，一直取到最后一条，取到当前数据返回YES,取不到则返回NO

NSMutableArray *array = [NSMutableArrayarray];

while ([set next]) {

GroupDBmodel *model = [[GroupDBmodelalloc] init];

//stringForColumn 根据字段的名称，取到字段的值

        model.chatId = [set stringForColumn:@"chatId"];

        model.chatter = [set stringForColumn:@"chatter"];

        model.groupText = [set stringForColumn:@"groupText"];

        model.headPhoto = [set stringForColumn:@"headPhoto"];

        model.senderName = [set stringForColumn:@"senderName"];

        model.senderTime = [set stringForColumn:@"senderTime"];

NSLog(@"%@----%@----%@----%@----%@----%@",model.chatId,model.chatter,model.groupText,model.headPhoto,model.senderName,model.senderTime);

        [array addObject:model];

    }

return array;

}

-(NSString *)findmaxtime:(NSString * )questionID{

//    NSString *selectSql = @"SELECT MAX(createTime) FROM gro upBulletin where questionUuid = ?";

NSString * selectSQL = @"SELECT * FROM groupBulletin WHERE questionUuid = ?";

FMResultSet *set =[_dataBaseexecuteQuery:selectSQL,questionID];

NSMutableArray * mArray = [[NSMutableArrayalloc] init];

while ([set next]) {

NSString * lastTime = [set stringForColumnIndex:6];

if (lastTime) {

            [mArray addObject:lastTime];

        }

    }

if (mArray.count > 0) {

NSString * str = mArray[0];

for (int i = 1 ; i < mArray.count; i++) {

if ([mArray[i] compare:str options:0] > 0) {

                str = mArray[i];

            }

        }

return str;

    }

else {

return@"0";

    }

}

//-(NSString *)findmaxtime:(NSString * )questionID{

//

//    NSString * selectSQL = @"SELECT * FROM groupBulletin WHERE questionUuid = ?";

//

//    FMResultSet *set =[_dataBase executeQuery:selectSQL,questionID];

//    NSMutableArray * mArray = [[NSMutableArray alloc] init];

//    while ([set next]) {

//        NSString * lastTime = [set stringForColumnIndex:6];

//        return lastTime;

//    }

//    return nil;

//}

- (int)findMaxUuid{

NSString * selectSQL = @"SELECT MAX(uuid) FROM groupBulletin";

int maxUuid = 0;

FMResultSet * set = [_dataBaseexecuteQuery:selectSQL];

while ([set next]) {

//        maxUuid = [set intForColumn:@"uuid"];

        maxUuid = [set intForColumnIndex:0];

    }

return maxUuid;

}

// 删除整个表

- (void)deleteDBFile{

NSString *dbPath = [NSHomeDirectory() stringByAppendingFormat:@"/Documents/groupBulletin.db"];

NSFileManager * fm = [NSFileManagerdefaultManager];

if ([fm fileExistsAtPath:dbPath]) {

if ([_dataBaseopen]) {

NSString * selectSQL = @"DELETE FROM groupBulletin";

            [_dataBaseexecuteUpdate:selectSQL];

            [_dataBaseclose];

        }

        [fm removeItemAtPath:dbPath error:nil];

    }

}

@end