sqlite3多线程

Sqlite 前端之家
前端之家收集整理的这篇文章主要介绍了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

猜你在找的Sqlite相关文章

SQLite学习笔记
安装 在Windows上安装SQLite。 访问官网下载下Precompliled Binaries for Windows的两个压...
windows上如何安装Sqlite
一、安装 下载地址：http://www.sqlite.org/download.html 将Precompiled Binaries for Wi...
【Android数据存储】SQLite使用实例(附源码)
实例: 会员信息管理 功能：1.查看数据库 2.清空数据库 3.增加会员 4.删除会员 5.更新会员 ...
SQLite数据库管理
SQLite3常用命令
加密你的SQLite
关于SQLite SQLite是一个轻量的、跨平台的、开源的数据库引擎，它的在读写效率、消耗总量、...
SQLite这么娇小可爱，不多了解点都不行啊
在我眼里，MySQL和Oracle是这样的 而SQLite在是这样的 所以这么萌的数据库，我真的应该多了...
SQLite这么娇小可爱，不多了解点都不行啊
在我眼里，MySQL和Oracle是这样的 而SQLite在是这样的 所以这么萌的数据库，我真的应该多了...
SQLite 这么娇小可爱，不多了解点都不行啊
（点击上方蓝字，快速关注我们) 作者：空之境界 网址：http://supermao.cn/sqlitezhe-yao-...
SQLITE3 使用总结
SQLITE3 使用总结 投稿：hebedich 字体：[ 增加  减小] 类型：转载 时间：2014-08-19 Sql...
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问