IOS多线程读写Sqlite问题解决

现在ios里使用的数据库一般都是Sqlite，但是使用Sqlite有个不太好的地方就是在多线程的时候，会出现问题，sqlite只能打开一个读或者写连结。这样的话多线程就会碰到资源占用的问题。

最开始是使用FMDB，FMDB的早期版本不能解决这个问题，后来FMDB更新了，新版本的FMDB能够很好的解决这个多线程使用Sqlite 。

FMDB github网址  https://github.com/ccgus/fmdb 最新版的请到github取下载。

本文演示了使用FMDB通过多线程来读和写数据库操作。

1.建立数据库表，我采用的是Firefox的Sqlite manager 来建立的。

  建表sql如下

 CREATE TABLE "tbl_user" ("_id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , "name" VARCHAR(30), "password" VARCHAR(30))

 2. 建立数据表的映射实体UserEntity

 

view source print ?

01. #import <Foundation/Foundation.h>

02.  

03. @interface UserEntity : NSObject

04. {

05. int _id;

06. NSString *name;

07. NSString *password;

08. }

09.  

10. @property (nonatomic, assign)int ID;

11. @property (nonatomic, retain) NSString *name;

12. @property (nonatomic, retain) NSString *password;

13. @end

3. 建立操作数据库的dao

 

view source print ?

01. //

02. //  DbDao.m

03. //  SqliteTest

04. //

05. //  Created by foxwang on 12-4-9.

06. //  Copyright (c) 2012年 __MyCompanyName__. All rights reserved.

07. //

08.  

09. #import "DbDao.h"

10. #import "DbFileManager.h"

11.  

12. #import "FMDatabase.h"

13. #import "FMDatabaseAdditions.h"

14. #import "FMDatabasePool.h"

15. #import "FMDatabaseQueue.h"

16. #import "UserEntity.h"

17.  

18. static DbDao *gSharedInstance = nil;

19.  

20. @implementation DbDao

21. @synthesize dbFile;

22. @synthesize dbQueue;

23.  

24. +(DbDao *)sharedInstance

25. {

26. @synchronized(self)

27. {

28. if (gSharedInstance == nil)

29. gSharedInstance = [[DbDao alloc] init];

30. }

31. return gSharedInstance;   

32. }

33.  

34. - (void)dealloc

35. {

36. [self.dbFile release];

37. self.dbQueue = nil;

38. [super dealloc];

39. }

40.  

41. - (id)init

42. {

43.  

44. self = [super init];

45. if (self)

46. {

47. self.dbFile = [DbFileManager dbFilePath];

48. self.dbQueue = [FMDatabaseQueue databaseQueueWithPath:self.dbFile];

49.  

50.  

51. }

52. return  self;

53. }

54.  

55. - (UserEntity *)rsToUser:(FMResultSet*)rs

56. {

57. UserEntity *user = [[[UserEntity alloc] init] autorelease];

58. user.ID = [rs intForColumn:@"_id"];

59. user.name = [rs stringForColumn:@"name"];

60. user.password = [rs  stringForColumn:@"password"];

61. return user;

62. }

63.  

64. - (void)addUser:(UserEntity *)user

65. {

66. [self.dbQueue inTransaction:^(FMDatabase *db, BOOL *rollback) {

67. [db open];

68. NSString *sql = @"insert into tbl_user(name, password) values (?, ?)";

69. [db executeUpdate:sql,user.name, user.password];

70. [db close];

71. }]; 

72. }

73.  

74. - (NSArray *)getUsers;

75. {

76. __block NSMutableArray *users = [[[NSMutableArray alloc] init] autorelease]; 

77. [self.dbQueue inDatabase:^(FMDatabase *db)   {

78. [db open];

79. NSString *sql = @"select * from tbl_user ";

80. FMResultSet *rs = [db executeQuery:sql];

81. while ([rs next])

82. {

83. [users addObject:[self rsToUser :rs]];

84. }

85. [db close];

86. }];

87. return users;

88. }

89. @end

4. 编写测试方法

在didFinishLaunchingWithOptions 方法里启动3个线程 ：2个线程写，1个线程读

 - (BOOL)application:(UIApplication *)application didFinishLaunchingWithOptions:(NSDictionary *)launchOptions

 

view source print ?

01. {

02. self.window = [[[UIWindow alloc] initWithFrame:[[UIScreen mainScreen] bounds]] autorelease];

03. // Override point for customization after application launch.

04. self.viewController = [[[ViewController alloc] initWithNibName:@"ViewController" bundle:nil] autorelease];

05. self.window.rootViewController = self.viewController;

06. [self.window makeKeyAndVisible];

07.  

08.  

09. [NSThread detachNewThreadSelector:@selector(writeDbOne) toTarget:self withObject:nil];

10.  

11. [NSThread detachNewThreadSelector:@selector(readDb) toTarget:self withObject:nil];

12.  

13. [NSThread detachNewThreadSelector:@selector(writeDbTwo) toTarget:self withObject:nil];

14.  

15. return YES;

16. }

view source print ?

01. - (void)writeDbOne

02. {

03. DbDao *dao = [DbDao  sharedInstance];

04. for (int i = 0; i < 500; i++)

05. {

06. @autoreleasepool

07. {

08. UserEntity *user = [[[UserEntity alloc] init] autorelease];

09. user.name = [NSString stringWithFormat:@"name %d", i];

10. user.password = [NSString stringWithFormat:@"password %d", i];

11. [dao addUser:user];

12. NSLog(@"writeDbOne %d ", i);

13. }

14.  

15. }

16. }

17.  

18. - (void)writeDbTwo

19. {

20. DbDao *dao = [DbDao  sharedInstance];

21. for (int i = 600; i < 1200; i++)

22. {

23. @autoreleasepool

24. {

25. UserEntity *user = [[[UserEntity alloc] init] autorelease];

26. user.name = [NSString stringWithFormat:@"name %d", i];

27. user.password = [NSString stringWithFormat:@"password %d", i];

28. [dao addUser:user];

29. NSLog(@"writeDbTwo %d ", i);

30. }

31.  

32. }

33. }

34.  

35. - (void)readDb

36. {

37. DbDao *dao = [DbDao  sharedInstance];

38. NSArray *users =   [dao getUsers];

39. NSLog(@"%@", users);

40. }

 

结论 ：使用新的FMDB ，很好的解决了多线程问题。

源码下载：http://down1.it165.net/pro/201204/0409SqliteTest.zip