Posted By

iswear_wxp on 04/01/10


Tagged

SQLite


Versions (?)

Who likes this?

2 people have marked this snippet as a favorite

adison
nimit


iphone开发-SQLite数据库使用


 / Published in: iPhone
 

  1. 我现在要使用SQLite3.0创建一个数据库,然后在数据库中创建一个表格。
  2.  
  3. 首先要引入SQLite3.0的lib库。然后包含头文件#import <sqlite3.h>
  4.  
  5. 1】打开数据库,如果没有,那么创建一个
  6.  
  7. sqlite3* database_;
  8.  
  9. -(BOOL) open{
  10. NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
  11. NSString *documentsDirectory = [paths objectAtIndex:0];
  12. NSString *path = [documentsDirectory stringByAppendingPathComponent:@"mydb.sql"];
  13. NSFileManager *fileManager = [NSFileManager defaultManager];
  14. BOOL find = [fileManager fileExistsAtPath:path];
  15.  
  16. // 找到数据库文件mydb.sql
  17. if (find) {
  18. NSLog(@"Database file have already existed.");
  19. if(sqlite3_open([path UTF8String], &database_) != SQLITE_OK) {
  20. sqlite3_close(database_);
  21. NSLog(@"Error: open database file.");
  22. return NO;
  23. }
  24. return YES;
  25. }
  26. if(sqlite3_open([path UTF8String], &database_) == SQLITE_OK) {
  27. bFirstCreate_ = YES;
  28. [self createChannelsTable:database_];//在后面实现函数createChannelsTable
  29.  
  30. return YES;
  31. } else {
  32. sqlite3_close(database_);
  33. NSLog(@"Error: open database file.");
  34. return NO;
  35. }
  36. return NO;
  37. }
  38.  
  39. 2】创建表格
  40.  
  41. //创建表格,假设有五个字段,(id,cid,title,imageData ,imageLen )
  42.  
  43. //说明一下,id为表格的主键,必须有。
  44.  
  45. //cid, 和title都是字符串,imageData是二进制数据,imageLen 是该二进制数据的长度。
  46. - (BOOL) createChannelsTable:(sqlite3*)db{
  47. char *sql = "CREATE TABLE channels (id integer primary key, \
  48. cid text, \
  49. title text, \
  50. imageData BLOB, \
  51. imageLen integer)";
  52. sqlite3_stmt *statement;
  53. if(sqlite3_prepare_v2(db, sql, -1, &statement, nil) != SQLITE_OK) {
  54. NSLog(@"Error: failed to prepare statement:create channels table");
  55. return NO;
  56. }
  57. int success = sqlite3_step(statement);
  58. sqlite3_finalize(statement);
  59. if ( success != SQLITE_DONE) {
  60. NSLog(@"Error: failed to dehydrate:CREATE TABLE channels");
  61. return NO;
  62. }
  63. NSLog(@"Create table 'channels' successed.");
  64. return YES;
  65. }
  66.  
  67.  
  68.  
  69. 3】向表格中插入一条记录
  70.  
  71. 假设channle是一个数据结构体,保存了一条记录的内容。
  72.  
  73. - (BOOL) insertOneChannel:(Channel*)channel{
  74. NSData* ImageData = UIImagePNGRepresentation( channel.image_);
  75. NSInteger Imagelen = [ImageData length];
  76. sqlite3_stmt *statement;
  77. static char *sql = "INSERT INTO channels (cid,title,imageData,imageLen)\
  78. VALUES(?,?,?,?)";
  79.  
  80. //问号的个数要和(cid,title,imageData,imageLen)里面字段的个数匹配,代表未知的值,将在下面将值和字段关联。
  81. int success = sqlite3_prepare_v2(database_, sql, -1, &statement, NULL);
  82. if (success != SQLITE_OK) {
  83. NSLog(@"Error: failed to insert:channels");
  84. return NO;
  85. }
  86.  
  87.  
  88. //这里的数字1,2,3,4代表第几个问号
  89. sqlite3_bind_text(statement, 1, [channel.id_ UTF8String], -1, SQLITE_TRANSIENT);
  90. sqlite3_bind_text(statement, 2, [channel.title_ UTF8String], -1, SQLITE_TRANSIENT);
  91. sqlite3_bind_blob(statement, 3, [ImageData bytes], Imagelen, SQLITE_TRANSIENT);
  92. sqlite3_bind_int(statement, 4, Imagelen);
  93.  
  94.  
  95. success = sqlite3_step(statement);
  96. sqlite3_finalize(statement);
  97.  
  98. if (success == SQLITE_ERROR) {
  99. NSLog(@"Error: failed to insert into the database with message.");
  100. return NO;
  101. }
  102.  
  103. NSLog(@"Insert One Channel#############:id = %@",channel.id_);
  104. return YES;
  105. }
  106.  
  107.  
  108.  
  109. 4】数据库查询
  110.  
  111. 这里获取表格中所有的记录,放到数组fChannels中。
  112.  
  113. - (void) getChannels:(NSMutableArray*)fChannels{
  114. sqlite3_stmt *statement = nil;
  115. char *sql = "SELECT * FROM channels";
  116. if (sqlite3_prepare_v2(database_, sql, -1, &statement, NULL) != SQLITE_OK) {
  117. NSLog(@"Error: failed to prepare statement with message:get channels.");
  118. }
  119. //查询结果集中一条一条的遍历所有的记录,这里的数字对应的是列值。
  120. while (sqlite3_step(statement) == SQLITE_ROW) {
  121. char* cid = (char*)sqlite3_column_text(statement, 1);
  122. char* title = (char*)sqlite3_column_text(statement, 2);
  123. Byte* imageData = (Byte*)sqlite3_column_blob(statement, 3);
  124. int imageLen = sqlite3_column_int(statement, 4);
  125. Channel* channel = [[Channel alloc] init];
  126. if(cid)
  127. channel.id_ = [NSString stringWithUTF8String:cid];
  128. if(title)
  129. channel.title_ = [NSString stringWithUTF8String:title];
  130. if(imageData){
  131. UIImage* image = [UIImage imageWithData:[NSData dataWithBytes:imageData length:imageLen]];
  132. channel.image_ = image;
  133. }
  134. [fChannels addObject:channel];
  135. [channel release];
  136. }
  137. sqlite3_finalize(statement);
  138. }
  139.  
  140.  
  141.  
  142. 就简单说这些吧。

Report this snippet  

You need to login to post a comment.