sqlite3_demo

//记得先导入第三方类 sqlite3

#import <UIKit/UIKit.h>

#import <sqlite3.h>

@interface ViewController : UIViewController

{

    sqlite3 *_dataBase;

}

@property (retain, nonatomic) IBOutlet UITextView *textView;

@property (retain, nonatomic) IBOutlet UITextField *textFieldName;

@property (retain, nonatomic) IBOutlet UITextField *textFieldAge;

@property (retain, nonatomic) IBOutlet UITextField *textFieldSex;

@property (retain, nonatomic) IBOutlet UITextField *textFieldiID;

- (IBAction)saveButton:(id)sender;

- (IBAction)getButton:(id)sender;

- (BOOL)initDataBase:(NSString *)dbName;

- (BOOL)creatChannelsTable;

- (BOOL) insertOneChannel:(NSString *)name

                    cAge:(NSInteger )age

                 caddress:(NSString *)addr

                     cSex:(NSString *)sex;

//返回值 可变数组 （数组内存放多个字典 每个字典存储表中的一行的数据）

- (NSMutableArray *)getAlldata;

@end

#import "ViewController.h"

@interface ViewController ()

@end

@implementation ViewController

- (void)viewDidLoad

{

    [super viewDidLoad];

    //调用函数

    [self initDataBase:@"member.db"];

}

- (IBAction)saveButton:(id)sender

{

    NSString *pName = self.textFieldName.text;

    NSInteger pAge = [self.textFieldAge.text integerValue];

    NSString *pAD = self.textFieldiID.text;

    NSString *pSex = self.textFieldSex.text;

    

    [self insertOneChannel:pName cAge:pAge caddress:pAD cSex:pSex];

}

- (IBAction)getButton:(id)sender

{

  

    NSMutableString *pResultStr = [NSMutableString string];

    NSMutableArray *pArr = [self getAlldata];

    for (int i =0; i < pArr.count; i++)

    {

        NSMutableDictionary *pTempDic = [pArr objectAtIndex:i];

        [pResultStr appendFormat:@"%@",[pTempDic objectForKey:@"name"]];

        [pResultStr appendString:@"\t"];

        [pResultStr appendFormat:@"%@",[pTempDic objectForKey:@"age"]];

        [pResultStr appendString:@"\t"];

        [pResultStr appendFormat:@"%@",[pTempDic objectForKey:@"add"]];

        [pResultStr appendString:@"\t"];

        [pResultStr appendFormat:@"%@",[pTempDic objectForKey:@"sex"]];

        [pResultStr appendString:@"\n"];

    }

    self.textView.text = pResultStr;

}

/*

 接口说明

 返回值：数据是否创建成功

 参数：要创建的数据库名称

 */

- (BOOL)initDataBase:(NSString *)dbName

{

    //查找沙河路径

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

    NSString *filePath = [paths objectAtIndex:0];

    NSString *path = [filePath stringByAppendingPathComponent:dbName];

    NSLog(@"path = %@",path);

    //打开文件

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

    {

        NSAssert(NO, @"打开数据失败");

        return NO;

    }

    NSLog(@"打开数据成功");

    /*

     创建表 待实现

     */

    [self creatChannelsTable];

    return YES;

}

- (BOOL)creatChannelsTable

{

    /*创建表得sql语句*/

    char *sql = "create table if not exists member(id integer primary key autoincrement , name text , age integer , addr text , sex text)";

    sqlite3_stmt *stmt;

    //判断编译语句结果是不是成功

    if (sqlite3_prepare_v2(_dataBase, sql, -1, &stmt, nil) != SQLITE_OK)

    {

        NSLog(@"创建表的sql语句编译失败");

        return NO;

    }

    NSLog(@"创建表的sql语句编译成功");

    //结果集中返回得列数

    int success = sqlite3_step(stmt);

    //判断是不是完成执行

    if (success != SQLITE_DONE)

    {

        NSLog(@"创建表失败");

        return NO;

    }

    NSLog( @" 创建表成功");

    return YES;

    

}

/*

 返回值 ： 插入数据是不是成功

 参数：往表内要插入的数据 对应绑定参数

 */

- (BOOL) insertOneChannel:(NSString *)name

                    cAge:(NSInteger )age

                 caddress:(NSString *)addr

                     cSex:(NSString *)sex

{

    sqlite3_stmt *stmt;

    

    char *sql = "INSERT OR REPLACE INTO member(name,age,addr,sex) VALUES(?,?,?,?)";

    

    int success = sqlite3_prepare_v2(_dataBase, sql, -1, &stmt, nil);

    if (success != SQLITE_OK)

    {

        NSLog(@"插入参数的sql语句编译失败");

        return NO;

    }

    NSLog(@"插入参数的sql语句编译成功");

    sqlite3_bind_text(stmt, 1, [name UTF8String], -1, nil);

    sqlite3_bind_int(stmt, 2, age);

    sqlite3_bind_text(stmt, 3, [addr UTF8String], -1, nil);

    sqlite3_bind_text(stmt, 4, [sex UTF8String], -1, nil);

    

    success = sqlite3_step(stmt);

    //重新设置一个已经准备好得状态对象

    sqlite3_finalize(stmt);

    if (success == SQLITE_ERROR)

    {

        NSLog(@"插入数据失败") ;

        return NO;

    }

    

    NSLog(@"插入数据成功");

    return YES;

}

- (NSMutableArray *)getAlldata

{

    NSMutableArray *allDatas =[[[NSMutableArray alloc]init]autorelease];

    sqlite3_stmt *stmt = nil;

    char *sql = "SELECT * FROM member";

    if (sqlite3_prepare_v2(_dataBase, sql, -1, &stmt, nil) != SQLITE_OK)

    {

        NSLog(@"读取表中的sql文件编译失败");

        return nil;

    }

    NSLog(@"读取表中的sql文件编译成功");

    while (sqlite3_step(stmt) == SQLITE_ROW)

    {

        NSMutableDictionary *tempDic = [[NSMutableDictionary alloc]init];

        char *pName = (char *)sqlite3_column_text(stmt, 1);

        if (pName)

        {

            [tempDic setObject:[NSString stringWithUTF8String:pName] forKey:@"name"];

        }

        NSInteger nAge = (int)sqlite3_column_int(stmt, 2);

        if (nAge)

        {

            [tempDic setObject:[NSNumber numberWithInteger:nAge] forKey:@"age"];

        }

        char *pAdd = (char *)sqlite3_column_text(stmt, 3);

        if (pName)

        {

            [tempDic setObject:[NSString stringWithUTF8String:pAdd] forKey:@"add"];

        }

        char *pSex = (char *)sqlite3_column_text(stmt, 4);

        if (pName)

        {

            [tempDic setObject:[NSString stringWithUTF8String:pSex] forKey:@"sex"];

        }

        [allDatas addObject:tempDic];

        [tempDic release];

    }

    sqlite3_finalize(stmt);

    return allDatas;

}

- (void)touchesBegan:(NSSet *)touches withEvent:(UIEvent *)event

{

    [self.view endEditing:YES];

}

- (void)didReceiveMemoryWarning

{

    [super didReceiveMemoryWarning];

}

- (void)dealloc

{

    [_textFieldAge release];

    

    [_textFieldiID release];

    

    [_textFieldName release];

    

    [_textFieldSex release];

    

    [_textView release];

    [super dealloc];

}

@end