加入收藏 | 设为首页 | 会员中心 | 我要投稿 李大同 （https://www.lidatong.com.cn/）- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置： 首页 > 百科 > 正文

sqlite3多线程

发布时间：2020-12-12 20:41:52 所属栏目：百科 来源：网络整理
导读：做iphone项目半年来一直被sqlite3的多线程问题困扰，一直尝试在解决。 在项目中，我最开始使用的是sqlite3的activerecord持久化框架sqlite3persistent.framework。但是用于项目中时有以下问题： 1、在多线程查询、保存、修改数据时，经常会有sqlite3的代码执


做iphone项目半年来一直被sqlite3的多线程问题困扰，一直尝试在解决。

在项目中，我最开始使用的是sqlite3的activerecord持久化框架sqlite3persistent.framework。但是用于项目中时有以下问题：

1、在多线程查询、保存、修改数据时，经常会有sqlite3的代码执行crash掉（我没有做线程读写锁，并且对锁不熟悉）。

2、不利于大量保存数据。保存大量数据时，执行效率低，影响程序运行

3、若在主线程处理sqlite3，crash机率降低很多，但是会锁死界面。


今天测试了另一个sqlite3的框架fmdb,发现在多线程下表现比sqlite3persistent好很多。就测试而言，未发现在多线程下的crash。

准备更改项目底层数据库代码了。。


为此问题纠结了很久，希望可以帮到有这方面需要的同学，特分享sqlite3多线程代码。FMDB代码在github上有，是开源代码。点此下载。

测试代码如下：

- (void)viewDidLoad {
    [super viewDidLoad];
	opQueue = [[NSOperationQueue alloc] init];
	[NSTimer scheduledTimerWithTimeInterval:0.1 target:self selector:@selector(multiThread1:) userInfo:nil repeats:YES];
	[NSTimer scheduledTimerWithTimeInterval:0.15 target:self selector:@selector(multiThread2:) userInfo:nil repeats:YES];
	[NSTimer scheduledTimerWithTimeInterval:0.17 target:self selector:@selector(multiThread3:) userInfo:nil repeats:YES];
	//[NSTimer scheduledTimerWithTimeInterval:1.3 target:self selector:@selector(multiThread4:) userInfo:nil repeats:NO];
}
- (void) multiThread1:(NSTimer *)timer
{
	NSInvocationOperation * theOp1 = [[NSInvocationOperation alloc] initWithTarget:self selector:@selector(addObject) object:nil];
	[opQueue addOperation:theOp1];
	[theOp1 release];
	//[timer invalidate];
}

- (void) multiThread2:(NSTimer *)timer
{
	NSInvocationOperation * theOp2 = [[NSInvocationOperation alloc] initWithTarget:self selector:@selector(removeObject) object:nil];
	[opQueue addOperation:theOp2];
	[theOp2 release];
}

- (void) multiThread3:(NSTimer *)timer
{
	NSInvocationOperation * theOp3 = [[NSInvocationOperation alloc] initWithTarget:self selector:@selector(showObject) object:nil];
	[opQueue addOperation:theOp3];
	[theOp3 release];
}


- (void) addObject
{
	NSLog(@"---------------- addObject");
	ContactDao * cdao = [[ContactDao alloc] init];
	Contact * ct = [[Contact alloc] init];
	ct.userid = random();
	ct.friendid = random();
	ct.isrecommend = random()%2;
	ct.isfriend = random()%2;
	NSLog(@"addObject: %d_%d",ct.userid,ct.friendid);
	[cdao saveContact:ct];
	[ct release];
	[cdao release];
}
- (void) removeObject
{
	NSLog(@"---------------- removeObject,count:%D",[list count]);
	ContactDao * cdao = [[ContactDao alloc] init];
	Contact * ct = [cdao findFirstByCriteria:nil];
	NSLog(@"removeObject: %d_%d of index: %d",ct.friendid,ct.index);
	if (ct.index >= 0) {
		[cdao deleteAtIndex:ct.index];
	}
	[cdao release];
}

- (void) showObject
{
	NSLog(@"---------------- showObject,[list count]);
	ContactDao * cdao = [[ContactDao alloc] init];
	Contact * ct = [cdao findFirstByCriteria:nil];
	NSLog(@"showObject: %d_%d",ct.friendid);
	//[cdao deleteAtIndex:ct.index];
	[cdao release];
}

BaseDao类如下：

//
//  BaseDao.h
//  Taonan
//
//  Created by zengconggen on 11-8-30.
//  Copyright 2011 yongmengsoft. All rights reserved.
//

#import <Foundation/Foundation.h>


@class FMDatabase;


@interface BaseDao : NSObject {
	FMDatabase *db;
}


@property (nonatomic,retain) FMDatabase *db;

-(NSString *)SQL:(NSString *)sql inTable:(NSString *)table;

@end
//
//  BaseDao.m
//  Taonan
//
//  Created by zengconggen on 11-8-30.
//  Copyright 2011 yongmengsoft. All rights reserved.
//


#import "DB.h"
#import "BaseDao.h"
#import "FMDatabase.h"
#import "FMDatabaseAdditions.h"

#define DB_NAME @"shoujitaonan.db"

@implementation BaseDao

@synthesize db;

- (id)init{
	if(self = [super init])
	{

		BOOL success;
		NSError *error;
		NSFileManager *fm = [NSFileManager defaultManager];
		NSArray  *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask,YES);
		NSString *documentsDirectory = [paths objectAtIndex:0];
		NSString *writableDBPath = [documentsDirectory stringByAppendingPathComponent:DB_NAME];
		
		success = [fm fileExistsAtPath:writableDBPath];
		
		if(!success){
			NSString *defaultDBPath = [[[NSBundle mainBundle] bundlePath] stringByAppendingPathComponent:DB_NAME];
			NSLog(@"%@",defaultDBPath);
			success = [fm copyItemAtPath:defaultDBPath toPath:writableDBPath error:&error];
			if(!success){
				NSLog(@"error: %@",[error localizedDescription]);
			}
			success = YES;
		}
		
		if(success){
			db = [[FMDatabase databaseWithPath:writableDBPath] retain];
			if ([db open]) {
				[db setShouldCacheStatements:YES];
			}else{
				NSLog(@"Failed to open database.");
				success = NO;
			}
		}
	}
	
	return self;
}

-(NSString *)SQL:(NSString *)sql inTable:(NSString *)table {
	return [NSString stringWithFormat:sql,table];
}

- (void)dealloc {
	[db close];
	[db release];
	[super dealloc];
}

@end


ContactDao类如下：


//
//  ContactDao.h
//  Taonan
//
//  Created by zengconggen on 11-8-30.
//  Copyright 2011 yongmengsoft. All rights reserved.
//

#import <Foundation/Foundation.h>
#import "BaseDao.h"
#import "Contact.h"

@interface ContactDao : BaseDao {
	
}

- (id) init;

// SELECT
- (NSMutableArray *) findByCriteria:(NSString *)criteria;
- (Contact *) findFirstByCriteria:(NSString *)criteria;
- (NSInteger) countByCriteria:(NSString *)criteria;

// INSERT
- (void) saveContact:(Contact *)contact;
- (void) saveContacts:(NSArray *)contacts;

// UPDATE
- (BOOL) updateAtIndex:(int)index withContact:(Contact *)contact;

// DELETE
- (BOOL) deleteAtIndex:(int)index;


@end

//
//  ContactDao.m
//  Taonan
//
//  Created by zengconggen on 11-8-30.
//  Copyright 2011 yongmengsoft. All rights reserved.
//

#import "Contact.h"
#import "ContactDao.h"
#import "FMDatabase.h"
#import "FMDatabaseAdditions.h"

#define TABLE_NAME @"Contacts"

@implementation ContactDao

- (id) init
{
    if (self = [super init]) {
        //检测是否存在表，不存在则创建
        if (![db tableExists:TABLE_NAME]) {
            NSString * SQL = [self SQL:@"CREATE TABLE IF NOT EXISTS `%@` (`index` INTEGER PRIMARY KEY AUTOINCREMENT,`userid` INTEGER,`friendid` INTEGER,`isrecommend` smallint,`isfavorite` INTEGER,`isonline` smallint,`isblock` smallint,`isfriend` smallint,`lastlogintime` INTEGER,`lastintouchtime` INTEGER,`distance` INTEGER);" inTable:TABLE_NAME];
            NSLog(@"creating table:%@",SQL);
            BOOL result = [db executeUpdate:SQL];
			if (result) {
				NSString * SQL2 = [self SQL:@"CREATE UNIQUE INDEX IF NOT EXISTS `userid_friend` on %@ (`userid`,`friendid`)" inTable:TABLE_NAME];
				[db executeUpdate:SQL2];
				NSString * SQL3 = [self SQL:@"CREATE INDEX IF NOT EXISTS `lastintouchtime` on %@ (`lastintouchtime`)" inTable:TABLE_NAME];
				[db executeUpdate:SQL3];
			}
        }
		[db close];
    }
    return self;
}
// SELECT
-(NSMutableArray *)findByCriteria:(NSString *)criteria
{
	NSMutableArray *result = [[[NSMutableArray alloc] initWithCapacity:0] autorelease];
	
    NSMutableString * sql = [NSMutableString stringWithString:[self SQL:@"SELECT * FROM %@ " inTable:TABLE_NAME]];
    if (criteria != nil) {
        [sql appendString:criteria];
    }
    
	[db open];
	
	FMResultSet *rs = [db executeQuery:sql];
	while ([rs next]) {
		Contact * ct	= [[Contact alloc] init];
		ct.index			= [rs intForColumn:@"index"];
		ct.userid			= [rs intForColumn:@"userid"];
		ct.friendid			= [rs intForColumn:@"friendid"];
		ct.isrecommend		= [rs intForColumn:@"isrecommend"];
		ct.isfavorite		= [rs intForColumn:@"isfavorite"];
		ct.isonline			= [rs intForColumn:@"isonline"];
		ct.isblock			= [rs intForColumn:@"isblock"];
		ct.isfriend			= [rs intForColumn:@"isfriend"];
		ct.lastlogintime	= [rs intForColumn:@"lastlogintime"];
		ct.lastintouchtime	= [rs intForColumn:@"lastintouchtime"];
		ct.distance			= [rs intForColumn:@"distance"];
		[result addObject:ct];
		[ct release];
	}
	
	[rs close];
	[db close];
	
	return result;
}
// SELECT
-(Contact *)findFirstByCriteria:(NSString *)criteria
{
	NSMutableArray *result = [[[NSMutableArray alloc] initWithCapacity:0] autorelease];
	
    NSMutableString * sql = [NSMutableString stringWithString:[self SQL:@"SELECT * FROM %@ " inTable:TABLE_NAME]];
    if (criteria != nil) {
        [sql appendString:criteria];
    }
    [sql appendString:@" limit 1"];
    
	[db open];
	
	FMResultSet *rs = [db executeQuery:sql];
	while ([rs next]) {
		Contact * ct	= [[Contact alloc] init];
		ct.index			= [rs intForColumn:@"index"];
		ct.userid			= [rs intForColumn:@"userid"];
		ct.friendid			= [rs intForColumn:@"friendid"];
		ct.isrecommend		= [rs intForColumn:@"isrecommend"];
		ct.isfavorite		= [rs intForColumn:@"isfavorite"];
		ct.isonline			= [rs intForColumn:@"isonline"];
		ct.isblock			= [rs intForColumn:@"isblock"];
		ct.isfriend			= [rs intForColumn:@"isfriend"];
		ct.lastlogintime	= [rs intForColumn:@"lastlogintime"];
		ct.lastintouchtime	= [rs intForColumn:@"lastintouchtime"];
		ct.distance			= [rs intForColumn:@"distance"];
		[result addObject:ct];
		[ct release];
	}
	
	[rs close];
	
	[db close];
	
    if ([result count] > 0) {
        return [result objectAtIndex:0];
    }else {
        return nil;
    }
}

- (NSInteger) countByCriteria:(NSString *)criteria
{
	NSInteger count = 0;
	
	NSMutableString * sql = [NSMutableString stringWithString:[self SQL:@"SELECT COUNT(`index`) AS total FROM %@ " inTable:TABLE_NAME]];
    if (criteria != nil) {
        [sql appendString:criteria];
    }
    
	[db open];
	
	FMResultSet *rs = [db executeQuery:sql];
	while ([rs next]) {
		count			= [rs intForColumn:@"total"];
	}
	
	[db close];
	
	return count;
}

- (void) saveContact:(Contact *)contact
{
    NSInteger index = contact.index;
    
    if (index >= 0) {
        [self updateAtIndex:index withContact:contact];
    }else {
		NSNumber * userid = [[NSNumber alloc] initWithInt:contact.userid];
		NSNumber * friendid = [[NSNumber alloc] initWithInt:contact.friendid];
		NSNumber * isrecommend = [[NSNumber alloc] initWithInt:contact.isrecommend];
		NSNumber * isfavorite = [[NSNumber alloc] initWithInt:contact.isfavorite];
		NSNumber * isonline = [[NSNumber alloc] initWithInt:contact.isonline];
		NSNumber * isblock = [[NSNumber alloc] initWithInt:contact.isblock];
		NSNumber * isfriend = [[NSNumber alloc] initWithInt:contact.isfriend];
		NSNumber * lastlogintime = [[NSNumber alloc] initWithInt:contact.lastlogintime];
		NSNumber * lastintouchtime = [[NSNumber alloc] initWithInt:contact.lastintouchtime];
		NSNumber * distance = [[NSNumber alloc] initWithInt:contact.distance];
		
		[db open];
		
        [db beginTransaction];
        NSString * insertSql = [self SQL:@"INSERT OR IGNORE INTO %@ (`userid`,`friendid`,`isrecommend`,`isfavorite`,`isonline`,`isblock`,`isfriend`,`lastlogintime`,`lastintouchtime`,`distance`) VALUES (?,?,?)" inTable:TABLE_NAME];
        [db executeUpdate:insertSql,userid,friendid,isrecommend,isfavorite,isonline,isblock,isfriend,lastlogintime,lastintouchtime,distance];
        NSInteger lastInsertId = [db lastInsertRowId];
        NSString * updateSql = [self SQL:@"UPDATE %@ SET userid=?,friendid=?,isrecommend=?,isfavorite=?,isonline=?,isblock=?,isfriend=?,lastlogintime=?,lastintouchtime=?,distance=? WHERE `index`=?" inTable:TABLE_NAME];
        [db executeUpdate:updateSql,distance,[NSNumber numberWithInt:lastInsertId]];
        [db commit];
		
		[db close];
		
		[userid release];
		[friendid release];
		[isrecommend release];
		[isfavorite release];
		[isonline release];
		[isblock release];
		[isfriend release];
		[lastlogintime release];
		[lastintouchtime release];
		[distance release];
    }
}

// INSERT
-(void)saveContacts:(NSArray *)contacts
{
	NSMutableArray * values = [[NSMutableArray alloc] init];
	
	for (Contact * contact in contacts) {
		NSNumber * userid = [[NSNumber alloc] initWithInt:contact.userid];
		NSNumber * friendid = [[NSNumber alloc] initWithInt:contact.friendid];
		NSNumber * isrecommend = [[NSNumber alloc] initWithInt:contact.isrecommend];
		NSNumber * isfavorite = [[NSNumber alloc] initWithInt:contact.isfavorite];
		NSNumber * isonline = [[NSNumber alloc] initWithInt:contact.isonline];
		NSNumber * isblock = [[NSNumber alloc] initWithInt:contact.isblock];
		NSNumber * isfriend = [[NSNumber alloc] initWithInt:contact.isfriend];
		NSNumber * lastlogintime = [[NSNumber alloc] initWithInt:contact.lastlogintime];
		NSNumber * lastintouchtime = [[NSNumber alloc] initWithInt:contact.lastintouchtime];
		NSNumber * distance = [[NSNumber alloc] initWithInt:contact.distance];
		
		NSString * value = [[NSString alloc] initWithFormat:@"(%@,%@,%@)",distance];
		[values addObject:value];
		[value release];
		
		[userid release];
		[friendid release];
		[isrecommend release];
		[isfavorite release];
		[isonline release];
		[isblock release];
		[isfriend release];
		[lastlogintime release];
		[lastintouchtime release];
		[distance release];
	}

	if ([values count] > 0) {
		[db open];
		[db beginTransaction];
		NSString * insertSql = [self SQL:@"INSERT OR IGNORE INTO %@ (`userid`,`distance`) VALUES %@" inTable:TABLE_NAME];
		[db executeUpdate:insertSql,[values componentsJoinedByString:@","]];
		[db commit];
		[db close];
	}
	[values release];
}


// UPDATE
-(BOOL)updateAtIndex:(int)index withContact:(Contact *)contact
{
	BOOL success = YES;
	
	NSNumber * indexId = [[NSNumber alloc] initWithInt:index];
	NSNumber * userid = [[NSNumber alloc] initWithInt:contact.userid];
	NSNumber * friendid = [[NSNumber alloc] initWithInt:contact.friendid];
	NSNumber * isrecommend = [[NSNumber alloc] initWithInt:contact.isrecommend];
	NSNumber * isfavorite = [[NSNumber alloc] initWithInt:contact.isfavorite];
	NSNumber * isonline = [[NSNumber alloc] initWithInt:contact.isonline];
	NSNumber * isblock = [[NSNumber alloc] initWithInt:contact.isblock];
	NSNumber * isfriend = [[NSNumber alloc] initWithInt:contact.isfriend];
	NSNumber * lastlogintime = [[NSNumber alloc] initWithInt:contact.lastlogintime];
	NSNumber * lastintouchtime = [[NSNumber alloc] initWithInt:contact.lastintouchtime];
	NSNumber * distance = [[NSNumber alloc] initWithInt:contact.distance];
	
	
	[db open];
	
	[db executeUpdate:[self SQL:@"UPDATE %@ SET userid=?,distance=? WHERE `index`=?" inTable:TABLE_NAME],indexId];
	
	[db close];
	
	[indexId release];
	[userid release];
	[friendid release];
	[isrecommend release];
	[isfavorite release];
	[isonline release];
	[isblock release];
	[isfriend release];
	[lastlogintime release];
	[lastintouchtime release];
	[distance release];
	
	if ([db hadError]) {
		NSLog(@"Err %d: %@",[db lastErrorCode],[db lastErrorMessage]);
		success = NO;
	}
	
	return success;
}


// DELETE
- (BOOL)deleteAtIndex:(int)index
{
	BOOL success = YES;
	[db open];
	[db executeUpdate:[self SQL:@"DELETE FROM %@ WHERE `index` = ?" inTable:TABLE_NAME],[NSNumber numberWithInt:index]];
	if ([db hadError]) {
		NSLog(@"Err %d: %@",[db lastErrorMessage]);
		success = NO;
	}else {
		[db clearCachedStatements];
	}
	[db close];

	return success;
}



- (void)dealloc
{
	[super dealloc];
}


@end

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
    相关内容
      推荐文章
        站长推荐
        热点阅读

          【免责声明】本站内容转载自互联网，其发布内容言论不代表本站观点，如果其链接、内容的侵犯您的权益，烦请提交相关链接至邮箱bqsm@foxmail.com我们将及时予以处理。

          建议您使用1920×1080分辨率、谷歌浏览器Google Chrome、Microsoft Edge以获得本站的最佳浏览效果

          Copygight © 2008-2022 https://www.lidatong.com.cn/ All Rights Reserved. 李大同