iOS SQLite 增删改查的封装（关系型）

在工程里导入libsqlite3.tbd库(Xcode 7)

#import <UIKit/UIKit.h>

@interface AppDelegate : UIResponder <UIApplicationDelegate>

@property (strong, nonatomic) UIWindow *window;


@end

#import "AppDelegate.h"
#import "RootViewController.h"
@interface AppDelegate ()

@end

@implementation AppDelegate


- (BOOL)application:(UIApplication *)application didFinishLaunchingWithOptions:(NSDictionary *)launchOptions {
    self.window = [[UIWindow alloc] initWithFrame:[[UIScreen mainScreen] bounds]];
    // Override point for customization after application launch.
    self.window.backgroundColor = [UIColor whiteColor];
    
    self.window.rootViewController = [[RootViewController alloc] init];
    
    [self.window makeKeyAndVisible];
    return YES;
}


@end

#import <UIKit/UIKit.h>

@interface RootViewController : UIViewController



@end

#import "RootViewController.h"
#import "Goods.h"
#import "Company.h"
#import "Infomation.h"

@interface RootViewController ()

@end

@implementation RootViewController

- (void)viewDidLoad {
    [super viewDidLoad];
    Goods *goods1 = [[Goods alloc] init];
    goods1.name = @"奥利";
    goods1.size = 2;
    goods1.color = @"blue";
    
    Company *company1 = [[Company alloc] init];
    company1.goods = goods1;
    company1.name = @"影视有限公司";
    company1.address = @"广州";
    company1.numberOfPeople = 20;
    company1.rank = 2;
    
    Goods *goods2 = [[Goods alloc] init];
    goods2.name = @"好好";
    goods2.size = 3;
    goods2.color = @"棕色";
    
    Company *company2 = [[Company alloc] init];
    company2.goods = goods2;
    company2.name = @"食品有限公司";
    company2.address = @"北京";
    company2.numberOfPeople = 10;
    company2.rank = 21;
    
    Infomation *info = [[Infomation alloc] init];
    //数据如果没有创建，则创建（只创建一次）
    if (![info createTable]) {
        [info createTable];
    }
    // 插入数据
//    [info add:company1 andGoodsOfCompany:company1.goods];
//    [info add:company2 andGoodsOfCompany:company2.goods];
    // 查询所有数据
//    NSMutableArray *arr = [info getAllCompanyData];
    //更新数据
//    company2.name = @"广告股份有限公司";
//    [info updateData:company2 andGoodsOfCompany:company2.goods];
//    // 查询所有数据
//    arr = [info getAllCompanyData];
//    // 删除company2
//    [info deleteData:company2 andGoodsOfCompany:company2.goods];
    // 查询所有数据
//    arr = [info getAllCompanyData];
//    // 查询
//    [info fineData:@"影视有限公司"];
    // 删除所有
    [info deleteAllData];
    [info getAllCompanyData];
    
}

- (void)didReceiveMemoryWarning {
    [super didReceiveMemoryWarning];
    // Dispose of any resources that can be recreated.
}


@end

#import <Foundation/Foundation.h>
@class Goods;

@interface Company : NSObject

@property(nonatomic, strong) NSString *name;
@property(nonatomic, strong) Goods *goods;
@property(nonatomic, assign) int numberOfPeople;
@property(nonatomic, strong) NSString *address;
@property(nonatomic, assign) int rank;

@end

#import "Company.h"
#import "Goods.h"
@implementation Company

@synthesize name;
@synthesize goods;
@synthesize numberOfPeople;
@synthesize address;
@synthesize rank;

-(void)dealloc{
    goods = nil;
    address = nil;
    name = nil;
}

- (instancetype)init
{
    self = [super init];
    if (self) {
        name = @"";
        goods = [[Goods alloc] init];
        address = @"";
    }
    return self;
}

@end

#import <Foundation/Foundation.h>

@interface Goods : NSObject

@property (nonatomic, strong) NSString *name;
@property (nonatomic, assign) int size;
@property (nonatomic, strong) NSString *color;


@end

#import "Goods.h"

@implementation Goods

@synthesize name;
@synthesize size;
@synthesize color;

- (void)dealloc
{
    name = nil;
    color = nil;
}

- (instancetype)init
{
    self = [super init];
    if (self) {
        name = @"";
        size = 0;
        color = @"";
    }
    return self;
}

@end

#import <Foundation/Foundation.h>
#import <sqlite3.h>

@interface BaseDB : NSObject

/**
 * 创建表
 */
- (void)createTable:(NSString *)sql;

/**
 *增删改
 */
- (void)dealData:(NSString *)sql paramarray:(NSArray *)params;

/**
 * 查询
 */
- (NSMutableArray *)selectData:(NSString *)sql withParams:(NSArray *)params withColumns:(int)columns;


@end

#import "BaseDB.h"

#define kFileName @"test.sqlite"

@interface BaseDB ()
{
    sqlite3 *database;
}
@end

@implementation BaseDB

// 数据库文件路径
- (NSString *)dataFilePath{
    NSString *path = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    NSLog(@"数据库的路径为：%@",path);
    return [path stringByAppendingPathComponent:kFileName];
}

// 创建表
-(void)createTable:(NSString *)sql{
    //防止self对象在同一时间内被其它线程访问，起到线程的保护作用
    @synchronized(self) {
        //打开数据库，这里的[path UTF8String]是将NSString转换为C字符串，因为SQLite3是采用可移植的C(而不是Objective-C)编写的，它不知道什么是NSString.
        if (sqlite3_open([[self dataFilePath] UTF8String], &database) != SQLITE_OK) {
            NSLog(@"数据库打开失败");
            //如果打开数据库失败则关闭数据库
            sqlite3_close(database);
        }
        
        char *errorMsg;
        // 执行非查询的sql语句
        if (sqlite3_exec(database, [sql UTF8String], NULL, NULL, &errorMsg)!= SQLITE_OK) {
            NSLog(@"创建表失败：%s",errorMsg);
            // 关闭数据库
            sqlite3_close(database);
        }
    }
}

// 增删改
- (void)dealData:(NSString *)sql paramarray:(NSArray *)params{
    [self selectData:sql withParams:params withColumns:0];
}

// 查询
- (NSMutableArray *)selectData:(NSString *)sql withParams:(NSArray *)params withColumns:(int)columns{
    NSMutableArray *returndata = [[NSMutableArray alloc] init];
    //防止self对象在同一时间内被其它线程访问，起到线程的保护作用
    @synchronized(self) {
        
        if (sqlite3_open([[self dataFilePath] UTF8String], &database) == SQLITE_OK) {
            //相当于ODBC的Command对象，用于保存编译好的SQL语句
            sqlite3_stmt *statement = nil;
            
            //sqlite3_prepare_v2 接口把一条SQL语句解析到statement结构里去. 使用该接口访问数据库是当前比较好的的一种方法
            //第一个参数跟前面一样，是个sqlite3 * 类型变量，
            //第二个参数是一个 sql 语句。
            //第三个参数我写的是-1，这个参数含义是前面 sql 语句的长度。如果小于0，sqlite会自动计算它的长度（把sql语句当成以\0结尾的字符串）。
            //第四个参数是sqlite3_stmt 的指针的指针。解析以后的sql语句就放在这个结构里。
            //第五个参数是错误信息提示，一般不用,为nil就可以了。
            //如果这个函数执行成功（返回值是 SQLITE_OK 且 statement 不为NULL ），那么下面就可以开始插入二进制数据。
            if (sqlite3_prepare_v2(database, [sql UTF8String], -1, &statement, NULL) == SQLITE_OK) {
                if (params != nil && params.count != 0) {
                    for (int i = 0; i < params.count; i++) {
                        NSString *tmp = nil;
                        // 取出参数
                        id param = [params objectAtIndex:i];
                        // 判断参数的类型
                        if ([param isKindOfClass:[NSNumber class]]) {
                            // 转成字符串类型
                            tmp = [param stringValue];
                        }else{
                            tmp = param;
                        }
                        //这里的数字i+1代表values的第几个值（从1开始）
                        sqlite3_bind_text(statement, i+1, [tmp UTF8String], -1, SQLITE_TRANSIENT);
                    }
                }
                //在调用sqlite3_prepare后，使用这个函数在记录集中移动。
                int result = sqlite3_step(statement);
                if (columns != 0) {
                    // SQLITE_ROW: 返回一行结果
                    while (result == SQLITE_ROW) {
                        NSMutableArray *data = [[NSMutableArray alloc] init];
                        for (int i = 0; i < columns; i++) {
                            //这里的数字i对应的是数据库第几列的值（从零开始）
                            char *contentchar = (char *)sqlite3_column_text(statement, i);
                            if (contentchar) {
                                [data addObject:[NSString stringWithCString:contentchar encoding:NSUTF8StringEncoding]];
                            }else{
                                [data addObject:@""];
                            }
                        }
                        [returndata addObject:data];
                        //qlite3_step返回SQLITE_ROW，可以得到列数
                        result = sqlite3_step(statement);
                    }
                }
            }
            //清理statement对象
            sqlite3_finalize(statement);
        }
        sqlite3_close(database);
    }
    return returndata;
}

@end

#import "BaseDB.h"
@class Company;
@class Goods;
@interface Infomation : BaseDB


/**
 * 创建数据表格
 */
- (BOOL)createTable;

/**
 * 添加数据
 */
- (void)add:(Company *)aCompany andGoodsOfCompany:(Goods*)goodsFromCompany;

/**
 * 删除全部数据
 */
- (void)deleteAllData;

/**
 * 获取所有数据
 */
- (NSMutableArray *)getAllCompanyData;

/**
 * 更新数据
 */
- (void)updateData:(Company *)aCompany andGoodsOfCompany:(Goods*)goodsFromCompany;

/**
 * 查询数据
 */
- (NSMutableArray *)fineData:(NSString *)nameOfCompany;

/**
 * 删除数据
 */
- (void)deleteData:(Company *)aCompany andGoodsOfCompany:(Goods*)goodsFromCompany;

@end

#import "Infomation.h"
#import "Company.h"
#import "Goods.h"

@implementation Infomation

//将数据转化成模型数据
- (NSMutableArray *)changeToModelDataWithArray:(NSArray *)dataArray{
    NSMutableArray *data = [NSMutableArray array];
    for (int i = 0; i < dataArray.count; i++) {
        NSArray *array = [dataArray objectAtIndex:i];
        Company *company = [[Company alloc] init];
        Goods *goods = [[Goods alloc] init];
        company.name = array[0];
        company.address = array[1];
        company.numberOfPeople = [array[2] intValue];
        NSLog(@"====%@",array[2]);
        company.rank = [array[3] intValue];
        goods.name = array[4];
        goods.size = [array[5] intValue];
        goods.color = array[6];
        company.goods = goods;
        NSLog(@"%@--%d--%@--%@--%@--%d--%d",company.goods.name,company.goods.size,company.goods.color,company.name,company.address,company.numberOfPeople,company.rank);
    }
    return data;
}

// 创建列表
- (BOOL)createTable{
    
    NSString *sql = @"create table if not exists CompanyTable( name varchar(50), address varchar(50), numberOfPeolpe int, rank int, nameOfGoods varchar(50), sizeOfGoods int, colorOfGoods varchar(20))";
    [self createTable:sql];
    return YES;
}

- (void)add:(Company *)aCompany andGoodsOfCompany:(Goods*)goodsFromCompany{
    NSString *sql = @"insert into CompanyTable(name,address,numberOfPeolpe,rank,nameOfGoods,sizeOfGoods,colorOfGoods) values (?,?,?,?,?,?,?)";
    
    NSArray *params = [NSArray arrayWithObjects:aCompany.name,aCompany.address,@(aCompany.numberOfPeople) ,@(aCompany.rank) ,goodsFromCompany.name,@(goodsFromCompany.size),goodsFromCompany.color, nil];
    [self dealData:sql paramarray:params];
}

// 删除全部数据
- (void)deleteAllData{
    NSString *sql = @"delete from CompanyTable";
    [self dealData:sql paramarray:nil];
}

//  获取所有数据
- (NSMutableArray *)getAllCompanyData{
    NSString *sql = @"select name,address,numberOfPeolpe,rank,nameOfGoods,sizeOfGoods,colorOfGoods from CompanyTable";
    NSMutableArray *arr = [self selectData:sql withParams:nil withColumns:7];
    NSMutableArray *backData = [self changeToModelDataWithArray:arr];
    return backData;
}

// 更新数据
- (void)updateData:(Company *)aCompany andGoodsOfCompany:(Goods*)goodsFromCompany{
    NSString *sql = @"replace into CompanyTable(name,address,numberOfPeolpe,rank,nameOfGoods,sizeOfGoods,colorOfGoods) values (?,?,?,?,?,?,?)";
    NSArray *params = [NSArray arrayWithObjects:aCompany.name,aCompany.address,@(aCompany.numberOfPeople) ,@(aCompany.rank) ,goodsFromCompany.name,@(goodsFromCompany.size),goodsFromCompany.color, nil];
    [self dealData:sql paramarray:params];
}


// 查询数据
- (NSMutableArray *)fineData:(NSString *)nameOfCompany{
    NSString *sql = [NSString stringWithFormat:@"select * from CompanyTable where name like \"%@\"",nameOfCompany];
    NSMutableArray *arr = [self selectData:sql withParams:nil withColumns:7];
    NSMutableArray *backData = [self changeToModelDataWithArray:arr];
    return backData;
}

// 删除数据
- (void)deleteData:(Company *)aCompany andGoodsOfCompany:(Goods*)goodsFromCompany{
    NSString *sql = @"delete from CompanyTable where name = ? and address = ? and numberOfPeolpe = ?and rank = ? and nameOfGoods = ? and sizeOfGoods = ? and colorOfGoods = ?";
     NSArray *params = [NSArray arrayWithObjects:aCompany.name,aCompany.address,@(aCompany.numberOfPeople) ,@(aCompany.rank) ,goodsFromCompany.name,@(goodsFromCompany.size),goodsFromCompany.color, nil];
    [self dealData:sql paramarray:params];
}

@end