ios平台上sqlite3数据库简单实例~~~~~~

 

首先说一下本人不是从事软件行业，完全是因为兴趣在业余时间研究ios编程，由于周边没有一个编程的朋友完全是自己抱着书本和电脑一点点学起，大概短短的3个月吧从零到现在，感慨颇多。（希望大家不要拍我呵呵）

希望能得到高手的指点~~~~

别的不多说，把自己所学的讲解给大家，希望自己能坚持下去、一直更新。。。。。

 

这两天研究了一下多个图片拖动排列的东东、发现还是需要用数据库存储位置比较靠谱、于是就熬夜学习sqlite数据库：

先简单的介绍一些sql的知识：

一个数据库通常包括多个表，每个表都有个名字

例如：有表imageLocations

id	imageName	imagePointX	imagePointY	imageSizeHight	imageSizeWide
1	bear	10	10	10	10
2	dog	2	2	2	2

这个表有两条存储数据

SELECT:从表中选取列数据

SELECT imageName FROM imageLocations

结果：

imageName

bear

dog

其他关键字：

UPDATE -更新数据库表中的数据

DELETE -从数据库表中删除数据

INSERT INTO -向数据库表中插入数据

CREATE TABLE -创建新表

DROP TABLE -删除表

（反正现在这些关键字对于我来说够用了也没有研究的太深）

 

 

直接上实例测试过了代码没有问题:

第一步、ibsqlite3.0.dylib需要在框架加载这个文件（这个东西我都折腾了半天）

第二步、创建DataBaseList类文件可以叫做我需求存储数据类型封装

// DataBaseList.h

// Test20120301

//

// Created by wincomn on 12-3-1.

// Copyright 2012年 __MyCompanyName__. All rights reserved.

//

 

#import <Foundation/Foundation.h>

@interface DataBaseList : NSObject{

   NSInteger imageID;

   NSString *imageName;

   NSInteger imagePointX;

   NSInteger imagePointY;

   NSInteger imageSizeHight;

   NSInteger imageSizeWide;

   NSInteger imageCentreX;

   NSInteger imageCentreY;

   UIImage *imageContentData;

}

@property (readwrite,assign) NSInteger imageID;

@property (readwrite,retain)NSString *imageName;

@property (readwrite,assign) NSInteger imagePointX;

@property (readwrite,assign) NSInteger imagePointY;

@property (readwrite,assign) NSInteger imageSizeHight;

@property (readwrite,assign) NSInteger imageSizeWide;

@property (readwrite,assign) NSInteger imageCentreX;

@property (readwrite,assign) NSInteger imageCentreY;

@property (readwrite,copy) UIImage *imageContentData;

@end

#import "DataBaseList.h"

 

@implementation DataBaseList

@synthesize imageID;

@synthesize imageName;

@synthesize imagePointX;

@synthesize imagePointY;

@synthesize imageCentreX;

@synthesize imageCentreY;

@synthesize imageSizeWide;

@synthesize imageSizeHight;

@synthesize imageContentData;

- (id)init

{

   self = [super init];

   if (self) {

       imageName=@"";

       imageContentData=nil;

   }

   

   return self;

}

 

@end

 

//

第三步、创建DataBaseSQL类文件

 

// DataBaseSQL.h

// Test20120301

//

// Created by wincomn on 12-3-1.

// Copyright 2012年 __MyCompanyName__. All rights reserved.

//

 

#import <Foundation/Foundation.h>

#import "sqlite3.h"

#import "DataBaseList.h"

@interface DataBaseSQL : NSObject{

id theDelegate;

//习惯写类时加代理、发现ios程序总是要做好多回调方法（个人理解）

sqlite3* database_;

//声明一个名字为database_数据库

   BOOL bFirstCreate_;

   //判断是否为第一次创建该数据库

}

-(BOOL)open;

 

-(BOOL)createDataBaseListTable:(sqlite3*)db;

-(BOOL)createDataBaseListTable;

-(BOOL)insertOneDataBaseList:(DataBaseList*)datalist;

 

-(BOOL)updataOneDataBaseList:(DataBaseList*)datalist ID:(NSInteger)ID;

 

-(BOOL)delDataBaseListTable:(sqlite3*)db;

-(BOOL)delDataBaseListTable;

-(BOOL)close;

-(NSMutableArray*) getDataBaseList;

@end

//

// DataBaseSQL.m

// Test20120301

//

// Created by wincomn on 12-3-1.

// Copyright 2012年 __MyCompanyName__. All rights reserved.

//

 

#import "DataBaseSQL.h"

 

@implementation DataBaseSQL

 

- (id)init

{

   self = [super init];

   if (self) {

       // Initialization code here.

   }

   

   return self;

}

-(BOOL)open{

//获取sqlite3数据库文件路径

   NSArray *paths= NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);

   NSString *documentsDirectory = [paths objectAtIndex:0];

   NSString *path = [documentsDirectory stringByAppendingPathComponent:@"DataBaseListdb.sqlite"];

   NSFileManager *fileManager = [NSFileManager defaultManager];

BOOL find = [fileManager fileExistsAtPath:path];

//判断文件是否存在

   if (find) {

       NSLog(@"数据库文件已经存在");

//打开数据库、返回操作是否正确

       if(sqlite3_open([path UTF8String], &database_) != SQLITE_OK) {

           sqlite3_close(database_);

           NSLog(@"Error: open database file.");

           return NO;

       }

       return YES;

   }

   if(sqlite3_open([path UTF8String], &database_) == SQLITE_OK) {

       bFirstCreate_ = YES;

      //调用createDataBaseListTable创建数据库

       [self createDataBaseListTable:database_]; 

       

       return YES;

   } else {

       sqlite3_close(database_);

       NSLog(@"Error: open database file.");

       return NO;

   }

   return NO;

}

 

- (BOOL) createDataBaseListTable:(sqlite3*)db{

//声明SQL语句

   char *sql = "CREATE TABLEimageLocations(id integer primary key, \

   imageName text, \

   imagePointX integer, \

   imagePointY integer, \

   imageSizeHight integer, \

   imageSizeWide integer, \

   imageCentreX integer, \

   imageCentreYinteger, \

imageContentData BLOB)";

//是把SQL语句解析后的内容和操作封装起来了是个结构体

sqlite3_stmt *statement;

//下面是完成SQL语句的解析第一参数只数据库、第二个SQL语句、第三个SQL语句长度小于0时自动计算长度、第四个是解析后存放的数据内容

   if(sqlite3_prepare_v2(db, sql, -1, &statement, nil) != SQLITE_OK) {

       NSLog(@"Error: failed to prepare statement:create channels table");

       return NO;

}

//把SQL语句写入到解析的结构体中

int success = sqlite3_step(statement);

//析构结构体

   sqlite3_finalize(statement);

   if ( success != SQLITE_DONE) {

       NSLog(@"Error: failed to dehydrate:CREATE TABLE channels");

       return NO;

   }

   NSLog(@"Create table 'channels' successed.");

   return YES;

}

- (BOOL) createDataBaseListTable{

   [self createDataBaseListTable:database_];

   return YES;

}

 

-(BOOL) insertOneDataBaseList:(DataBaseList*)datalist{

//(datalist.imageContentData)是UIImage类型必须先转成NSData数据类型

   NSData *ImageData=UIImagePNGRepresentation(datalist.imageContentData);

   NSInteger imagelen=[ImageData length];

   sqlite3_stmt *statement;

   static char *sql = "INSERT OR REPLACE INTO imageLocations (imageName,imagePointX,imagePointY,imageSizeHight,imageSizeWide,imageCentreX,imageCentreY,imageContentData)\

   VALUES(?,?,?,?,?,?,?,?)";

//问号个数与插入的列字段个数相同   

int success = sqlite3_prepare_v2(database_, sql, -1, &statement, NULL);

   if (success != SQLITE_OK) {

       NSLog(@"Error: failed to insert:imageLocations");

       return NO;

   }

    //替换？号为指定的值

   sqlite3_bind_text(statement, 1, [datalist.imageName UTF8String],-1,SQLITE_TRANSIENT);

   sqlite3_bind_int(statement, 2, datalist.imagePointX);

   sqlite3_bind_int(statement, 3, datalist.imagePointY);

   sqlite3_bind_int(statement, 4, datalist.imageSizeHight);

   sqlite3_bind_int(statement, 5, datalist.imageSizeWide);

   sqlite3_bind_int(statement, 6, datalist.imageCentreX);

   sqlite3_bind_int(statement, 7, datalist.imageCentreY);

   sqlite3_bind_blob(statement, 8, [ImageData bytes],imagelen,SQLITE_TRANSIENT);

 

   

   success = sqlite3_step(statement);

   sqlite3_finalize(statement);

   

   if (success == SQLITE_ERROR) {

       NSLog(@"Error: failed to insert into the database with message.");

       return NO;

   }

   

   NSLog(@"数据成功写入！");

   return YES;

 

}

//和插入函数差不多、只是通过ID号来给数据表数据更新

-(BOOL) updataOneDataBaseList:(DataBaseList*)datalist ID:(NSInteger)ID{

   NSData *ImageData=UIImagePNGRepresentation(datalist.imageContentData);

   NSInteger imagelen=[ImageData length];

   sqlite3_stmt *statement;

   NSString *SQL = @"UPDATE imageLocations SET imageName=?,imagePointX=?,imagePointY=?,imageSizeHight=?,imageSizeWide=?,imageCentreX=?,imageCentreY=?,imageContentData=? where id=";

 

   

   SQL=[SQL stringByAppendingString:[NSString stringWithFormat:@"%d",ID]];

   const char *sql =[SQL UTF8String];

   

   int success = sqlite3_prepare_v2(database_, sql, -1, &statement, NULL);

   if (success != SQLITE_OK) {

       NSLog(@"Error: failed to insert:imageLocations");

       return NO;

   }

     sqlite3_bind_text(statement, 1, [datalist.imageName UTF8String],-1,SQLITE_TRANSIENT);

   sqlite3_bind_int(statement, 2, datalist.imagePointX);

   sqlite3_bind_int(statement, 3, datalist.imagePointY);

   sqlite3_bind_int(statement, 4, datalist.imageSizeHight);

   sqlite3_bind_int(statement, 5, datalist.imageSizeWide);

   sqlite3_bind_int(statement, 6, datalist.imageCentreX);

   sqlite3_bind_int(statement, 7, datalist.imageCentreY);

   sqlite3_bind_blob(statement, 8, [ImageData bytes],imagelen,SQLITE_TRANSIENT);

 

   

   

   success = sqlite3_step(statement);

   sqlite3_finalize(statement);

   

   if (success == SQLITE_ERROR) {

       NSLog(@"Error: failed to insert into the database with message.");

       return NO;

   }

   

   NSLog(@"数据成功写入！");

   return YES;

 

}

//删除数据表

-(BOOL) delDataBaseListTable:(sqlite3*)db{

   char *sql = "DROP TABLE imageLocations";

   sqlite3_stmt *statement;

   if(sqlite3_prepare_v2(db, sql, -1, &statement, nil) != SQLITE_OK) {

       NSLog(@"Error: failed to prepare statement:create channels table");

       return NO;

   }

   int success = sqlite3_step(statement);

   sqlite3_finalize(statement);

   if ( success != SQLITE_DONE) {

       NSLog(@"Error: failed to dehydrate:CREATE TABLE imageLocations");

       return NO;

   }

   NSLog(@"Create table 'imageLocations' successed.");

   return YES;

 

}

-(BOOL) delDataBaseListTable{

   [self delDataBaseListTable:database_];

   return YES;

}

 

 

 

-(BOOL)close

{ sqlite3_close(database_);

   return YES;

}

//查询数据表把数据库所有的值赋值给可变数组

-(NSMutableArray*) getDataBaseList{

   NSMutableArray* getList;

   getList=[[NSMutableArray alloc] init];

   sqlite3_stmt *statement = nil;

   char *sql = "SELECT * FROM imageLocations";

   if (sqlite3_prepare_v2(database_, sql, -1, &statement, NULL) != SQLITE_OK) {

       NSLog(@"Error: failed to prepare statement with message:get channels.");

   }

   while (sqlite3_step(statement) == SQLITE_ROW){

      

       char*imageName =(char *)sqlite3_column_text(statement, 1);

       int imagePointX = sqlite3_column_int(statement, 2);

       int imagePointY = sqlite3_column_int(statement, 3);

       int imageSizeHight = sqlite3_column_int(statement, 4);

       int imageSizeWide = sqlite3_column_int(statement, 5);

       int imageCentreX=sqlite3_column_int(statement, 6);

       int imageCentreY = sqlite3_column_int(statement, 7);

       const void *test = sqlite3_column_blob(statement, 8);

       int size = sqlite3_column_bytes(statement, 8);

       NSData *imageContentData = [NSData dataWithBytes:test length:size]; 

       

       DataBaseList* tempDataBaseList=[[DataBaseList alloc]init];

       if (imageName) {

           tempDataBaseList.imageName=[NSString stringWithUTF8String:imageName];

       }

       if (imagePointX) {

           tempDataBaseList.imagePointX=imagePointX;

       }

       if (imagePointY) {

           tempDataBaseList.imagePointY=imagePointY;

       }

       if (imageSizeHight) {

           tempDataBaseList.imageSizeHight=imageSizeHight;

       }

       if (imageSizeWide) {

           tempDataBaseList.imageSizeWide=imageSizeWide;

       }

       if (imageCentreX) {

           tempDataBaseList.imageCentreX=imageCentreX;

       }

       if (imageCentreY) {

           tempDataBaseList.imageCentreY=imageCentreY;

       }

       if (imageContentData) {

           tempDataBaseList.imageContentData=[UIImage imageWithData:imageContentData];

       }

       

       [getList addObject:tempDataBaseList];

       [tempDataBaseList release];

   }

   sqlite3_finalize(statement);

   

   

   return getList;

}

@end

 

发现写点东西真的耗费时间，比编代码费时间多了- -！看来我的表达能力还是不行啊、坚持、贵在坚持。。。。。。希望上班不要太忙可以抽时间在继续学习呵呵~~~~~~改天把属性列表也贴出来。

在网上找资料学习期间、发现完整的简单代码少之又少，对于我们这些初学者来说真的是沉重的打击。高手认为很简单的东西就没贴出来，搞的我们这些初学者迷迷叨叨。所以我的原则是、及时讲的少也要把完整的代码贴出来。好了继续学习~~~~~~~~~~~

竟然发现刚申请博客3天内不让发表文章我擦擦擦~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~