iOS sqlit3 数据库使用详情

iOS sqlit3 数据库使用详情

1.工具类

////<span style="color:#ff0000;">  newPeopleTool.h</span>//  test////  Created by mac10 on 15/11/23.//  Copyright © 2015年 apple. All rights reserved.//#import <Foundation/Foundation.h>@class People;@interface newPeopleTool : NSObject/** *  添加people * *  @param student 需要添加的people */+ (BOOL)addPeople:(People *)people;/** *  获得所有的people * *  @return 数组中装着都是people模型 */+ (NSArray *)peoples;/** *  更新数据 * *  @param people people */+(BOOL)updatePeople:(People *)people andindex:(int )index;/** *  删除people */+(BOOL)removePeopleindex:(int )index;@end

newPeopleTool.m

////  newPeopleTool.m//  test////  Created by mac10 on 15/11/23.//  Copyright © 2015年 apple. All rights reserved.//#import "newPeopleTool.h"#import <sqlite3.h>#import "People.h"@implementation newPeopleTool// static的作用：能保证_db这个变量只被IWStudentTool.m直接访问static sqlite3 *_db;+ (void)initialize{    // 0.获得沙盒中的数据库文件名    NSString *filename = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject] stringByAppendingPathComponent:@"people.sqlite"];    NSLog(@"%@",filename);    // 1.创建(打开)数据库（如果数据库文件不存在，会自动创建）    int result = sqlite3_open(filename.UTF8String, &_db);    if (result == SQLITE_OK) {        NSLog(@"成功打开数据库");                // 2.创表        const char *sql = "create table if not exists t_people(id integer primary key autoincrement,name text, tel text, peopleid text);";        char *errorMesg = NULL;        int result = sqlite3_exec(_db, sql, NULL, NULL, &errorMesg);        if (result == SQLITE_OK) {            NSLog(@"成功创建people表");        } else {            NSLog(@"创建people表失败:%s", errorMesg);        }    } else {        NSLog(@"打开数据库失败");    }}

//3.添加数据+ (BOOL)addPeople:(People *)people{    NSString *sql = [NSString stringWithFormat:@"insert into t_people (name, tel,peopleid) values( '%@','%@','%@');", people.name, people.tel,people.ID];        char *errorMesg = NULL;    int result = sqlite3_exec(_db, sql.UTF8String, NULL, NULL, &errorMesg);        return result == SQLITE_OK;}//4.查询所有的数据+ (NSArray *)peoples{    // 0.定义数组    NSMutableArray *peoples = nil;        // 1.定义sql语句    const char *sql = "select name, tel, peopleid from t_people;";        // 2.定义一个stmt存放结果集    sqlite3_stmt *stmt = NULL;        // 3.检测SQL语句的合法性    int result = sqlite3_prepare_v2(_db, sql, -1, &stmt, NULL);    if (result == SQLITE_OK) {       // NSLog(@"查询语句是合法的");       peoples = [NSMutableArray array];                // 4.执行SQL语句，从结果集中取出数据        while (sqlite3_step(stmt) == SQLITE_ROW) { // 真的查询到一行数据            //获得这行对应的数据            People *people= [[People alloc] init];                        // 获得第0列的id            const unsigned char *name = sqlite3_column_text(stmt, 0);            people.name = [NSString stringWithUTF8String:(const char *)name];                        // 获得第1列的name            const unsigned char *tel = sqlite3_column_text(stmt, 1);            people.tel = [NSString stringWithUTF8String:(const char *)tel];                        // 获得第2列的age            const unsigned char *id = sqlite3_column_text(stmt, 2);            people.ID = [NSString stringWithUTF8String:(const char *)id];                        //添加到数组            [peoples addObject:people];                    }    } else {        NSLog(@"查询语句非合法");    }        return peoples;}//5.更新数据+(BOOL)updatePeople:(People *)people andindex:(int )index{    NSString *sql = [NSString stringWithFormat:@"update t_people set name = '%@', tel ='%@',peopleid ='%@' where id ='%d';", people.name, people.tel,people.ID,index +1];     char *errorMesg = NULL;    int result = sqlite3_exec(_db, sql.UTF8String, NULL, NULL, &errorMesg);        return result == SQLITE_OK;         }//6.删除数据+(BOOL)removePeopleindex:(int )index{    NSString *sql = [NSString stringWithFormat:@"delete from t_people  where id = '%d';",index+1];    char *errorMesg = NULL;    int result = sqlite3_exec(_db, sql.UTF8String, NULL, NULL, &errorMesg);        return result == SQLITE_OK;}@end

//条件查询

+ (NSArray *)studentsWithCondition:(NSString *)condition{    // 0.定义数组    NSMutableArray *students = nil;        // 1.定义sql语句    const char *sql = "select id, name, age from t_student where name like ?;";        // 2.定义一个stmt存放结果集    sqlite3_stmt *stmt = NULL;        // 3.检测SQL语句的合法性    int result = sqlite3_prepare_v2(_db, sql, -1, &stmt, NULL);    if (result == SQLITE_OK) {        NSLog(@"查询语句是合法的");        students = [NSMutableArray array];                // 填补占位符的内容        NSString *newCondition = [NSString stringWithFormat:@"%%%@%%", condition];//        NSLog(@"%@", newCondition);        sqlite3_bind_text(stmt, 1, newCondition.UTF8String, -1, NULL);                // 4.执行SQL语句，从结果集中取出数据        while (sqlite3_step(stmt) == SQLITE_ROW) { // 真的查询到一行数据            // 获得这行对应的数据                        IWStudent *student = [[IWStudent alloc] init];                        // 获得第0列的id            student.ID = sqlite3_column_int(stmt, 0);                        // 获得第1列的name            const unsigned char *sname = sqlite3_column_text(stmt, 1);            student.name = [NSString stringWithUTF8String:(const char *)sname];                        // 获得第2列的age            student.age = sqlite3_column_int(stmt, 2);                        // 添加到数组            [students addObject:student];        }    } else {        NSLog(@"查询语句非合法");    }        return students;}

//模型people

#import <Foundation/Foundation.h>@interface People : NSObject//图标//@property (nonatomic,copy) NSString * iconView;@property (nonatomic,copy) NSString *name;@property (nonatomic,copy) NSString *tel;@property (nonatomic,copy) NSString *ID;////-(instancetype) initWithDict:(NSDictionary *) dict;//+(instancetype) peopleWithDict:(NSDictionary *) dict;@end

<span style="color:#ff0000;">#import "PeopleTableController.h"</span>#import "addPeopleController.h"#import "People.h"#import "peopleCell.h"#import "editViewController.h"#import "newPeopleTool.h"@interface PeopleTableController ()<addPeopleControllerDelegate,editViewControllerDelegate>@end@implementation PeopleTableController<span style="color:#cc0000;">- (NSMutableArray *)peoples{        return [newPeopleTool peoples];}</span>- (void)viewDidLoad {    [super viewDidLoad];    self.tableView.separatorStyle = UITableViewCellSeparatorStyleNone;    UIBarButtonItem *rightButton = [[UIBarButtonItem alloc] initWithTitle:@"添加"style:UIBarButtonItemStyleDone target:self action:@selector(addpeople)];    self.navigationItem.rightBarButtonItem = rightButton;    self.tableView.rowHeight = 80;    self.view.backgroundColor = [UIColor colorWithRed:235/255.0 green:235/255.0 blue:235/255.0 alpha:1.0];}-(void)creatNewpatient{    UIButton *button = [UIButton buttonWithType:UIButtonTypeCustom];    CGFloat buttonW = CGRectGetWidth(self.view.frame)  - 10 ;    CGFloat buttonX = ( CGRectGetWidth(self.view.frame) - buttonW ) * 0.5 ;    CGFloat buttonY = CGRectGetHeight(self.view.frame) * 0.6;    CGFloat buttonH = 50;    button.frame = CGRectMake(buttonX, buttonY, buttonW, buttonH);    button.backgroundColor = [UIColor blueColor];    button.layer.cornerRadius = 10.0;    [button setTitle:@"添加就诊人" forState:UIControlStateNormal];    [self.view addSubview:button];    }-(void)addpeople{    addPeopleController *addpeopleVc =[[addPeopleController alloc] init];    addpeopleVc.delegate =self;    [self.navigationController pushViewController:addpeopleVc animated:YES];    }#pragma mark - Table view data source<span style="color:#ff0000;">- (NSInteger)tableView:(UITableView *)tableView numberOfRowsInSection:(NSInteger)section{      return self.peoples.count; }- (UITableViewCell *)tableView:(UITableView *)tableView cellForRowAtIndexPath:(NSIndexPath *)indexPath{    // 1.创建cell   peopleCell *cell = [peopleCell cellWithTableView:tableView];        // 2.设置cell的数据    cell.people = self.peoples[indexPath.row];        return cell;}</span>-(void)tableView:(UITableView *)tableView didSelectRowAtIndexPath:(NSIndexPath *)indexPath{    [tableView deselectRowAtIndexPath:indexPath animated:NO];    editViewController *editVc=[[editViewController alloc] init];      <span style="color:#ff0000;">// 取得选中的那行    editVc.people = self.peoples[indexPath.row];    //editVc.IndexPath = indexPath;    self.index =indexPath.row;</span>        [self.navigationController pushViewController:editVc animated:YES];    editVc.delegate = self;}#pragma mark - addPeopleController的代理方法- (void)addPeopleController:(addPeopleController *)addVc didAddPeople:(People *)people{    //1.添加数据    if (people) {       <span style="color:#ff0000;"> [newPeopleTool addPeople:people];//添加数据</span>    }    // 2.刷新表格    [self.tableView reloadData];}#pragma mark - editViewController的代理方法-(void)editViewController:(editViewController *)editVc didSavePeople:(People *)people{    //1.更新数据库   <span style="color:#ff0000;"> [newPeopleTool updatePeople:people andindex:self.index];</span>    //2.刷新表格    [self.tableView reloadData];}-(void)editViewController:(editViewController *)editVc{       // 1.删除模型数据    //[self.peoples removeObjectAtIndex:IndexPath];    //1.从数据库中删除    <span style="color:#ff0000;">[newPeopleTool removePeopleindex:self.index];</span>        // 2.刷新表格    [self.tableView reloadData];    // 局部刷新某些行(使用前提:模型数据的行数不变)    //[self.tableView deleteRowsAtIndexPaths:IndexPath withRowAnimation:UITableViewRowAnimationTop];}@end