在Sqlite数据库插入和读取图片数据

yuandan

在iOS下用sqlite数据库存储图片，先把你的图片转换成 NSData 形式，然后在数据库添加一行 blob 数据
假定数据库中存在表 test_table(name,image), 下面代码将图片文件test.png的二进制数据写到sqlite数据库：

例1

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49

CHAR*name="test"; NSString*nameString=[NSString stringWithCString:name encoding:NSUTF8StringEncoding]; NSString*filePath=[[NSBundle mainBundle]pathForResource:nameStringofType:@"png"]; IF([[NSFileManager defaultManager]fileExistsAtPath:filePath]) { NSData*imgData=UIImagePNGRepresentation([UIImage imageWithContentsOfFile:filePath]); const CHAR*sequel="insert into test_table(name,image) values(?,?)"; sqlite3_stmt*UPDATE; IF(sqlite3_prepare_v2(DATABASE,sequel,-1,&UPDATE,NULL)==SQLITE_OK) { sqlite3_bind_text(UPDATE,1,name,-1,NULL); sqlite3_bind_blob(UPDATE,2,[imgDatabytes],[imgDataLENGTH],NULL); IF(sqlite3_step(UPDATE)==SQLITE_DONE) { NSLog(@"已经写入数据"); } sqlite3_finalize(UPDATE); } } ELSE { NSLog(@"文件不存在"); } 下面代码从数据库中读取图片二进制数据，然后显示图片： constCHAR*sequel="select image from test_table where name=?"; sqlite3_stmt*getimg; IF(sqlite3_prepare_v2(DATABASE,sequel,-1,&getimg,NULL)==SQLITE_OK) { CHAR*name="test"; sqlite3_bind_text(UPDATE,1,name,-1,NULL); IF(sqlite3_step(getimg)==SQLITE_ROW) { INTbytes=sqlite3_column_bytes(getimg,0); Byte*VALUE=(Byte*)sqlite3_column_blob(getimg,1); IF(bytes !=0&&VALUE!=NULL) { NSData*DATA=[NSData dataWithBytes:VALUELENGTH:bytes]; UIImage*img=[UIImage imageWithData:DATA]; UIImageView*aview=[[UIImageView alloc]initWithFrame:                                     CGRectMake(0.0,0.0,IMAGE_WIDTH,IMAGE_HEIGHT)]; aview.image=img; [SELF.VIEWaddSubview:aview]; [aview release]; } } sqlite3_finalize(getimg); }

例2:存储图片

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

// Save Small Image Data by given main url -(void)SaveImagesToSql:(NSData*)imgData:(NSString*)mainUrl { NSLog(@"\n*****Save image to SQLite*****\n"); constchar*sqliteQuery="INSERT INTO IMAGES (URL, IMAGE)VALUES (?, ?)"; sqlite3_stmt*statement; if(sqlite3_prepare_v2(articlesDB, sqliteQuery,-1,&statement,NULL)==SQLITE_OK) { sqlite3_bind_text(statement,1,[mainUrl UTF8String],-1,SQLITE_TRANSIENT); sqlite3_bind_blob(statement,2,[imgData bytes],[imgDatalength], SQLITE_TRANSIENT); sqlite3_step(statement); } elseNSLog(@"SaveBody: Failed from sqlite3_prepare_v2. Error is: %s", sqlite3_errmsg(articlesDB)); // Finalize and close database. sqlite3_finalize(statement); }

读取图片:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

// Load images from data base with given image url -(NSData*)LoadImagesFromSql:(NSString*)imageLink { NSData*data=nil; NSString*sqliteQuery=[NSStringstringWithFormat:@"SELECTIMAGE FROM IMAGES WHERE URL = '%@'", imageLink];    sqlite3_stmt*statement; if(sqlite3_prepare_v2(articlesDB,[sqliteQuery UTF8String],-1,&statement,NULL)==SQLITE_OK) { if(sqlite3_step(statement)==SQLITE_ROW) { intlength=sqlite3_column_bytes(statement,0); data   =[NSDatadataWithBytes:sqlite3_column_blob(statement,0)length:length]; } } // Finalize and close database. sqlite3_finalize(statement); returndata; }