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
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
