iOS学习笔记---数据库SQLite3的基本操作并讲内容显示在tableView中

之前有做过一段时间的android，由于主要是做前端，对数据的了解就不是很深，而且觉得比较难，但是自己也清除SQLite是肯定要搞懂的，现在做IOS了，就想趁这个机会跨过这个大坑。让我没想到的是ios用的SQLite3比android用的SQLite还要麻烦。。。。

花了两天时间写了个基本操作的demo，并结合tableView显示数据，这样就不用使用那些什么数据库管理软件什么的来管理了。。。。感觉不能看的东西写起来真蛋疼~···

先看看效果图吧~···

·好了，废话不多说了，具体看代码吧····注释也应该够详细了······

主要的.m文件

//
//  ViewController.m
//  SQLite3Test
//
//  Created by yaodd on 13-7-9.
//  Copyright (c) 2013年 jitsun. All rights reserved.

//  数据库的基本操作，包括创建，插入，查询，删除
//  将数据库里的内容动态显示在tableView
//  点击按钮则添加一行数据，点击tableView中某一行则删除一条数据，同时如果按home键使程序在后台运行也会将当前数据添加进数据库中

#import "ViewController.h"
#import <sqlite3.h>
#import "cellItem.h"
#import "PeopleCell.h"

@implementation ViewController
@synthesize arrPeople;
@synthesize fieldAddress;
@synthesize fieldAge;
@synthesize fieldCity;
@synthesize fieldName;
@synthesize insertButton;
@synthesize PeopleTableView;
- (NSString *)dataFilePath
{
    NSArray *paths = NSSearchPathForDirectoriesInDomains(
                                                         NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    return [documentsDirectory stringByAppendingPathComponent:@"data.sqlite"];
}

- (void)viewDidLoad
{
    [super viewDidLoad];
    //tableView中显示的数据包装
    arrPeople = [[NSMutableArray alloc]init];
    
    //建立表（程序第一次运行时建立）
    sqlite3 *database;
    if (sqlite3_open([[self dataFilePath] UTF8String], &database)
        != SQLITE_OK) {
        sqlite3_close(database);
        NSAssert(0, @"Failed to open database");
    }
    //建立表的SQL语句，主键为ROW，自增；其他键为NAME,AGE,ADDRESS,CITY。
    NSString *createSQL = @"CREATE TABLE IF NOT EXISTS PEOPLE "
    "(ROW INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT, AGE INTEGER, ADDRESS TEXT, CITY TEXT);";
    //将出错信心保存在errorMsg中
    char *errorMsg;
    if (sqlite3_exec (database, [createSQL UTF8String],
                      NULL, NULL, &errorMsg) != SQLITE_OK) {
        sqlite3_close(database);
        //如果出错，则输出errorMsg
        NSAssert(0, @"Error creating table: %s", errorMsg);
    }
    //查询数据库，并以ROW排序
    NSString *query = @"SELECT ROW, NAME, AGE, ADDRESS, CITY FROM PEOPLE ORDER BY ROW";
    sqlite3_stmt *statement;//至于这个参数，网上的说法“这个相当于ODBC的Command对象，用于保存编译好的SQL语句”；
    if (sqlite3_prepare_v2(database, [query UTF8String],
                           -1, &statement, nil) == SQLITE_OK)
    {
        while (sqlite3_step(statement) == SQLITE_ROW) {//对表中的数据进行遍历，并转为item加入arrPeople中
            NSLog(@"%d",arrPeople.count);
            cellItem *item = [[cellItem alloc]init];
            int row = sqlite3_column_int(statement, 0);
            char *nameChar = (char *)sqlite3_column_text(statement, 1);
            int age = sqlite3_column_int(statement,2);
            char *addressChar = (char *)sqlite3_column_text(statement,3);
            char *cityChar = (char *)sqlite3_column_text(statement, 4);
            NSLog(@"row:%d name:%s age:%d add:%s city:%s",row,nameChar,age,addressChar,cityChar);
            item.row = [[NSString alloc] initWithFormat:@"%d",row];
            item.age = [[NSString alloc] initWithFormat:@"%d",age];
            item.name = [[NSString alloc] initWithUTF8String:nameChar];
            item.address = [[NSString alloc] initWithUTF8String:addressChar];
            item.city = [[NSString alloc] initWithUTF8String:cityChar];
            [arrPeople addObject:item];
            
        }
        sqlite3_finalize(statement);//结束之前清除statement对象，
    }
    sqlite3_close(database);//关闭数据库
    
    //这几行代码是当程序在后台运行时执行的函数
    UIApplication *app = [UIApplication sharedApplication];
    [[NSNotificationCenter defaultCenter]
     addObserver:self
     selector:@selector(applicationWillResignActive:)
     name:UIApplicationWillResignActiveNotification
     object:app];
    
    //插入数据的按钮；按多次就会插入多条
    [insertButton addTarget:self action:@selector(insertButtonPressed:) forControlEvents:UIControlEventTouchUpInside];
}
//后台运行时执行的函数，也是执行插入数据的操作
- (void)applicationWillResignActive:(NSNotification *)notification
{
    [self insertData];//插入数据函数
}
//插入数据的按钮响应器
- (void)insertButtonPressed:(id)sender
{
    [self insertData];//插入数据函数
}
//插入数据函数的实现，用的是绑定变量的方法。
- (void)insertData
{
    int count = arrPeople.count;
    sqlite3 *database;
    if (sqlite3_open([[self dataFilePath] UTF8String], &database)
        != SQLITE_OK) {
        sqlite3_close(database);
        NSAssert(0, @"Failed to open database");
    }
    cellItem *item = [[cellItem alloc] init];
    item.row = [[NSString alloc]initWithFormat:@"%d",count];
    item.name = fieldName.text;
    item.age = fieldAge.text;
    item.address = fieldAddress.text;
    item.city = fieldCity.text;
    //插入或更新一行，不指定主键ROW··则ROW会自增
    //INSERT OR REPLACE实现了插入或更新两个操作
    char *update = "INSERT OR REPLACE INTO PEOPLE (NAME, AGE, ADDRESS, CITY) "
    "VALUES (?, ?, ?, ?);";
    char *errorMsg = NULL;
    sqlite3_stmt *stmt;
    if (sqlite3_prepare_v2(database, update, -1, &stmt, nil)
        == SQLITE_OK) {
        
        sqlite3_bind_text(stmt, 1, [item.name UTF8String], -1, NULL);
        sqlite3_bind_int(stmt, 2, [item.age intValue]);
        sqlite3_bind_text(stmt, 3, [item.address UTF8String], -1, NULL);
        sqlite3_bind_text(stmt, 4, [item.city UTF8String], -1, NULL);
        
    }
    if (sqlite3_step(stmt) != SQLITE_DONE)
        NSAssert(0, @"Error updating table: %s", errorMsg);
    sqlite3_finalize(stmt);//结束之前清除statement变量
    
    sqlite3_close(database);//关闭数据库
    [arrPeople addObject:item];//tableView里的数据的增加
    [self.PeopleTableView reloadData];//动态更新tableView
    
    NSLog(@"insert %d",count);

}
//删除表中row = rowId的那一行
- (void)deleteData:(int)rowId
{
    sqlite3 *database;
    if (sqlite3_open([[self dataFilePath] UTF8String], &database)
        != SQLITE_OK) {
        sqlite3_close(database);
        NSAssert(0, @"Failed to open database");
    }
    char *errmsg;
    NSString *deleteRow = [[NSString alloc]initWithFormat:@"DELETE FROM PEOPLE WHERE ROW = %d",rowId];
    sqlite3_exec(database, [deleteRow UTF8String], NULL, NULL, &errmsg);
    NSLog(@"%s",errmsg);
    sqlite3_close(database);

}

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

#pragma mark -
#pragma mark - Table view data source

- (NSInteger)tableView:(UITableView *)tableView numberOfRowsInSection:(NSInteger)section
{
    return [arrPeople count];
}

- (UITableViewCell *)tableView:(UITableView *)tableView cellForRowAtIndexPath:(NSIndexPath *)indexPath
{
       
    static NSString *PeopleCellIdentifier = @"PeopleCellIdentifier";
    //自定义的TableViewCell
    PeopleCell *cell = [tableView dequeueReusableCellWithIdentifier:
                             PeopleCellIdentifier];
    if (cell == nil) {
        cell = [[PeopleCell alloc] initWithStyle:UITableViewCellStyleDefault reuseIdentifier: PeopleCellIdentifier];
    }
    
    NSInteger row = [indexPath row];
    cellItem *item = [arrPeople objectAtIndex:row];
    cell.row.text = [[NSString alloc]initWithFormat:@"%d",row];
    cell.name.text = item.name;
    cell.age.text = item.age;
    cell.address.text = item.address;
    cell.city.text = item.city;
    
    
    return  cell;
    
}



- (CGFloat)tableView:(UITableView *)tableView heightForRowAtIndexPath:(NSIndexPath *)indexPath
{
    
    return 67;
}

#pragma Table view delegate

- (UITableViewCellEditingStyle)tableView:(UITableView *)tableView editingStyleForRowAtIndexPath:(NSIndexPath *)indexPath
{
    return UITableViewCellEditingStyleNone;
}

- (void)tableView:(UITableView *)tableView didSelectRowAtIndexPath:(NSIndexPath *)indexPath
{
    //点击某一行则删除改行
    NSInteger row = [indexPath row];
    cellItem *item = [arrPeople objectAtIndex:row];
    [self deleteData:[item.row intValue]];//数据库中删除
    [arrPeople removeObjectAtIndex:row];//tableView数据中删除
    [self.PeopleTableView reloadData];//动态更新tableView
}

@end

由于这个demo还用到牵涉到了tableView，因此project里面还有一些数据类型文件和cell文件

完整工程包请移步 http://download.csdn.net/detail/kekeqiaokeli/5727291 下载。谢谢支持！

好好学习，天天向上~···