Advanced Search

iphone开发-SQLite数据库使用

/ Published in: iPhone
Save to your folder(s)

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  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


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.