iOS中数据库的用法总结

之前写了关于单例类和数据库的博客,这里要用到。

数据库第一次使用觉得晦涩又难懂,其实数据库的形式比较死板,熟悉了后没那么难。

这里要举个例子,从创建数据库开始,然后到项目中获取数据库信息并显示出来,获取和显示要用到单例类。

首先用数据库软件MesaSQLite生成数据库 File-newDatabase并保存,然后建表:

 

然后开始加内容,选中city表,添加三行数据:

 

现在一个数据库文件就建立完成了,我起名为testDB,后缀名默认是rdb,当然后缀也可以改成db或者没有后缀。

然后就是在iOS中使用数据库文件了,一般我要创建三个文件,一个叫做SQL.h,只是用于存放SQL语句的字符串,在数据库函数调用的时候调用,相当于一个常量类。另外两个是一个单例类的.h文件和.m文件,负责数据库的操作,其全局方法可以在我想调用的地方随时调用。

首先是SQL.h(SQL语句是项目中用到的,示意作用,和之前建立的数据库无关):

char * const kCreateQatAlbumTable=”select count(id) from city”;//city表中id数

char * const kSqlGetALLCityList= “select DISTINCT cityName from city “;//city表中不重复的cityName项

char * const kSqlGetSystemListByCity = “select DISTINCT system from city where cityName=?”;//city表中cityName=?的system项

char * const kSqlGetShopTypeListByCityAndSystem= “select DISTINCT shopType from city where cityName=? and system=?”;//city表中cityName=? system=?的不重复的shopType项

char * const kSqlGetshopAddressByID= “select  shopAddress from city where id=?”;//city表中id=?的shopAddress项

char *const kSqlGetBySearch=”SELECT id,system FROM  city WHERE  (system like ?  or shopName like ?  ) and cityName = ?”;//city表中cityName=?情况下,system或者shopName表项里含?的id和system项

char * const kSqlGetHTMLByCity= “select cityName,maxNormalTempratureSalerNum,maxNormalTempratureSaler,maxNormalTempratureSalerPersonInCharge,systemshopnumber from city where  cityName=?”;

//city表中cityName=?情况下的cityName,maxNormalTempratureSalerNum,maxNormalTempratureSaler,maxNormalTempratureSalerPersonInCharge,systemshopnumber这些表项

定义完了SQL语句,要将这些语句获得的结果转换到objective-c可控的数据结构中,比如NSArray或者NSString。接下来的单例类就是起这个作用的。之前描述过单例类的作用,只创建一个对象,这个对象可以在全局调用并调用其方法,就像工具类,用起来不用重新初始化很方便,在数据库操作中用再合适不过了,首先是单例类的.h文件

#import <UIKit/UIKit.h>

#import <sqlite3.h>

@interface OperationalDatabase : NSObject{

sqlite3 *database;

}

+ (OperationalDatabase *) defaultManager;

//////////////////////////数据库的基本操作//////

-(void)openDatabase;

-(void) close;

///////////获取数据

-(NSArray *)getAreaArray;

-(NSString *)getAreaByCity:(NSString *)city;

-(NSArray *)getCityArray:(NSString *)area;

-(NSArray *)getAllCityArray;

-(NSArray *)getSystemArray:(NSString *)city;

-(NSArray *)getAllShopTypeArray:(NSString *)city;

可以看到有个成员是sqlite3,也就是数据库对象,还有defaultManager构造方法,基本操作包括打开数据库和关闭数据库,还有一系列的获取数据的方法,每个方法往往对应一个SQL语句,下面看.m文件里面的内容:

#import “OperationalDatabase.h”

#import “SQL.h”

#import “JSON.h”

@implementation OperationalDatabase

static OperationalDatabase *defaultManager;

//构造方法,调用单例类方法时候需要

+ (OperationalDatabase *) defaultManager{

if (!defaultManager) {

defaultManager=[[OperationalDatabase alloc] init];

[defaultManager openDatabase];

}

return defaultManager;

}

//打开数据库方法,路径是Documents下

-(void)openDatabase

{

NSString *path=[[NSHomeDirectory() stringByAppendingPathComponent:@”Documents”] stringByAppendingPathComponent:@”mengniu”];

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

NSLog(@”DataBase Opened “);

}

}

//关闭数据库方法

-(void) close

{

sqlite3_close(database);

NSLog(@”OperationalDatabase db closed.”);

}

 上面是必须有的方法,包括构造函数和数据库的基本操作,下面举几个数据库操作方法的例子:
// kCreateQatAlbumTable=”select count(id) from city”;返回id数

-(int)getIdCount{

int countNumber=0;

sqlite3_stmt *statement;

if (sqlite3_prepare_v2(database, kCreateQatAlbumTable, -1, &statement, nil) == SQLITE_OK) {

while (sqlite3_step(statement)==SQLITE_ROW) {

if (!countNumber) {

countNumber=0;

}

countNumber=sqlite3_column_int(statement, 0);//取出数据

}

}

sqlite3_finalize(statement);

return countNumber;

}

//kSqlGetAreaList= “select DISTINCT area from city”;得到所有area数组

-(NSArray *)getAreaArray

{

NSMutableArray *array=nil;

sqlite3_stmt *statement;

if (sqlite3_prepare_v2(database, kSqlGetAreaList, -1, &statement, nil) == SQLITE_OK) {

while (sqlite3_step(statement)==SQLITE_ROW) {

if (!array) {

array = [NSMutableArray array];

}

char *areaName=(char *)sqlite3_column_text(statement, 0);

if (areaName!=NULL) {

NSString *area=[[NSString alloc] initWithCString:(char *)sqlite3_column_text(statement, 0) encoding:NSUTF8StringEncoding];

[array addObject:area];

}

}//while Ω· ¯

}

sqlite3_finalize(statement);

return array;

}

-(NSString *)getAreaByCity:(NSString *)city{

NSString *getArea=nil;

sqlite3_stmt *statement;

if (sqlite3_prepare_v2(database, kSqlGetAreaListByCity, -1, &statement, nil) == SQLITE_OK) {

sqlite3_bind_text(statement, 1, [city UTF8String], -1, SQLITE_TRANSIENT);

while (sqlite3_step(statement)==SQLITE_ROW) {

char *areaName=(char *)sqlite3_column_text(statement, 0);

getArea=[NSString stringWithCString:areaName encoding:NSUTF8StringEncoding];

}

}

sqlite3_finalize(statement);

return getArea;

 

}

//两个参数的时候注意写法-(NSArray *)getShopTypeArrayFromCityAndSystem:(NSString *)city System:(NSString *)system{

NSMutableArray *array=nil;

sqlite3_stmt *statement;

if (sqlite3_prepare_v2(database, kSqlGetShopTypeListByCityAndSystem, -1, &statement, nil) == SQLITE_OK) {

int index=1;

sqlite3_bind_text(statement, index++, [city UTF8String], -1, SQLITE_TRANSIENT);

sqlite3_bind_text(statement, index++, [system UTF8String], -1, SQLITE_TRANSIENT);

while (sqlite3_step(statement)==SQLITE_ROW) {

if (!array) {

array = [NSMutableArray array];

}

char *shopType=(char *)sqlite3_column_text(statement, 0);

if (shopType!=NULL) {

NSString *shopTypeItem=[NSString stringWithCString:shopType encoding:NSUTF8StringEncoding];

[array addObject:shopTypeItem];

}

}//while Ω· ¯

}

sqlite3_finalize(statement);

return array;

 

}

//获得多个数据的时候,这里存储形式为json-(NSString *)getHtmlJSON:(NSString *)city

{

NSMutableDictionary *messDic=nil;

sqlite3_stmt *statement;

if (sqlite3_prepare_v2(database, kSqlGetHTMLByCity, -1, &statement, nil) == SQLITE_OK) {

int index=1;

sqlite3_bind_text(statement, index++, [city UTF8String], -1, SQLITE_TRANSIENT);

while (sqlite3_step(statement)==SQLITE_ROW) {

if (!messDic) {

messDic = [NSMutableDictionary dictionary];

}

char *city=(char *)sqlite3_column_text(statement, 0);

if (city!=NULL) {

NSString *shop=[NSString stringWithCString:city encoding:NSUTF8StringEncoding];

[messDic setObject:shop forKey:@”cityName”];

}

int maxNormalTempratureSalerNum=sqlite3_column_int(statement, 1);//statement index++

[messDic setObject:[NSString stringWithFormat:@”%d”,maxNormalTempratureSalerNum] forKey:@”maxNormalTempratureSalerNum”];

char *maxNormalTempratureSaler=(char *)sqlite3_column_text(statement, 2);

if (maxNormalTempratureSaler!=NULL) {

NSString *maxNormalTempratureSaler1=[NSString stringWithCString:maxNormalTempratureSaler encoding:NSUTF8StringEncoding];

[messDic setObject:maxNormalTempratureSaler1 forKey:@”maxNormalTempratureSaler”];

}

char *maxNormalTempratureSalerPersonInCharge=(char *)sqlite3_column_text(statement, 3);

if (maxNormalTempratureSalerPersonInCharge!=NULL) {

NSString *maxNormalTempratureSalerPersonInCharge1=[NSString stringWithCString:maxNormalTempratureSalerPersonInCharge encoding:NSUTF8StringEncoding];

[messDic setObject:maxNormalTempratureSalerPersonInCharge1 forKey:@”maxNormalTempratureSalerPersonInCharge”];

}

long long  maxNormalTempratureSalerPersonInChargeTele=sqlite3_column_int64(statement, 4);

 

[messDic setObject:[NSString stringWithFormat:@”%qi”,maxNormalTempratureSalerPersonInChargeTele] forKey:@”maxNormalTempratureSalerPersonInChargeTele”];

int maxLowTempratureSalerNum=sqlite3_column_int(statement, 5);//statement index++

[messDic setObject:[NSString stringWithFormat:@”%d”,maxLowTempratureSalerNum] forKey:@”maxLowTempratureSalerNum”];

char *maxLowTempratureSaler=(char *)sqlite3_column_text(statement, 6);

if (maxLowTempratureSaler!=NULL) {

NSString *maxLowTempratureSaler1=[NSString stringWithCString:maxLowTempratureSaler encoding:NSUTF8StringEncoding];

[messDic setObject:maxLowTempratureSaler1 forKey:@”maxLowTempratureSaler”];

}

char *maxLowTempratureSalerPersonInCharge=(char *)sqlite3_column_text(statement, 7);

if (maxLowTempratureSalerPersonInCharge!=NULL) {

NSString *maxLowTempratureSalerPersonInCharge1=[NSString stringWithCString:maxLowTempratureSalerPersonInCharge encoding:NSUTF8StringEncoding];

[messDic setObject:maxLowTempratureSalerPersonInCharge1 forKey:@”maxLowTempratureSalerPersonInCharge”];

}

long long maxLowTempratureSalerPersonInChargeTele=sqlite3_column_int64(statement, 8);

[messDic setObject:[NSString stringWithFormat:@”%qi”,maxLowTempratureSalerPersonInChargeTele] forKey:@”maxLowTempratureSalerPersonInChargeTele”];

char *systemshopnumber=(char *)sqlite3_column_text(statement, 9);

if (systemshopnumber!=NULL) {

NSString *systemshopnumber1=[NSString stringWithCString:systemshopnumber encoding:NSUTF8StringEncoding];

[messDic setObject:systemshopnumber1 forKey:@”systemshopnumber”];

}

}//while

}

sqlite3_finalize(statement);

return [messDic JSONFragment];

}

 大致就是这些,接下来是调用方法,比如我想查数据库shopname=?的location:

#import “OperationalDatabase.h”//引入头文件

NSArray *locationArray=[[OperationalDatabase defaultManager] getLocationByShopName:shopName];

然后就可以利用这些数据了

发表评论

电子邮件地址不会被公开。 必填项已用*标注