• oc中数据库使用详细解释(二)


    上一篇介绍了数据库的单例的创建.这一篇介绍如何使用.数据库中存放的是一个名为class13的table.对象为Person类,这里就不再粘贴Person的代码了.这里新建了一个PersonManage类的单例,用来处理数据库里面的数据,以及方便其他类的调用.本段程序为PersonManage.h和.m的程序.第一段注释较为详细,涉及到一些C语言中的方法.参数的设置大多已经注释.另外需要注意的是增删减和普通的查询的区别.

    #import "PersonManager.h"
    #import "DB.h"
    @implementation PersonManager
    
    static PersonManager *pm = nil;
    + (PersonManager *)sharedPersonManager
    {
        @synchronized(self){
            if (pm == nil) {
                pm = [[PersonManager alloc] init];
            }
        }
        return pm;
    }
    
    - (NSArray *)allPersons
    {
        //@"select * from class13"
        //打开数据库
        sqlite3 *db = [DB open];
        //stmt是 存放结果集 的对象。
        sqlite3_stmt *stmt = nil;
        //第一个参数代表数据库对象,第二个参数代表需要执行的操作.字符串类型的,第三个代表字符串长度,一般我们都填-1,代表不限制长度,第四个代表用来存放结果的结果集,第五个参数表示查询玩之后做什么事情,NULL标示不做任何事情.
        int flag = sqlite3_prepare_v2(db, "select * from class13", -1, &stmt, NULL);
        NSMutableArray *persons = nil;
        if (flag == SQLITE_OK) {//如果SQL语句没问题,数据库也打开了,我们要从stmt中拿出结果(比如结果显示到tableview上等等)
            //创建一个数组,用来接收生成的Person类对象
            persons = [NSMutableArray arrayWithCapacity:2];
            //检查语句是否有问题.查询时候用SQLITE_ROW,检测是否还有下一条数据
            while (sqlite3_step(stmt) == SQLITE_ROW) {
                
                //column是从0开始的,代表数据在数据库中一条数据的列数
                int ID = sqlite3_column_int(stmt, 0);
                const unsigned char *name = sqlite3_column_text(stmt, 1);
                const unsigned char *sex = sqlite3_column_text(stmt, 2);
                const unsigned char *phone = sqlite3_column_text(stmt, 4);
                int age = sqlite3_column_int(stmt, 3);
                //数据库中的数据都是C语言类型的.需要转化成oc中的字符串类型
                Person *p = [[Person alloc] initWithID:ID name:[NSString stringWithCString:(const char *)name encoding:NSUTF8StringEncoding] sex:[NSString stringWithCString:(const char *)sex encoding:NSUTF8StringEncoding] age:age phone:[NSString stringWithCString:(const char *)phone encoding:NSUTF8StringEncoding]];
                [persons addObject:p];
            }
        }
        sqlite3_finalize(stmt);//释放结果集占有的资源(内存)
        //[DB close];//此处没有关闭数据库,是想提高程序性能。避免任何一个小的操作都要 开关数据库。
        return persons;
    }
    
    
    - (Person *)personByID:(int)ID
    {
        sqlite3 *db = [DB open];
        sqlite3_stmt *stmt = nil;
        int flag = sqlite3_prepare_v2(db, "select * from class13 where id = ?", -1, &stmt, NULL);
        Person *p = nil;
        if (flag == SQLITE_OK) {
            //问号是从1开始的  用ID添补第一个?(问号)
            sqlite3_bind_int(stmt, 1, ID);
            if(sqlite3_step(stmt)==SQLITE_ROW)
            {
                int ID = sqlite3_column_int(stmt, 0);
                const unsigned char *name = sqlite3_column_text(stmt, 1);
                const unsigned char *sex = sqlite3_column_text(stmt, 2);
                const unsigned char *phone = sqlite3_column_text(stmt, 4);
                int age = sqlite3_column_int(stmt, 3);
                p = [[Person alloc] initWithID:ID name:[NSString stringWithCString:(const char *)name encoding:NSUTF8StringEncoding] sex:[NSString stringWithCString:(const char *)sex encoding:NSUTF8StringEncoding] age:age phone:[NSString stringWithCString:(const char *)phone encoding:NSUTF8StringEncoding]];
            }
        }
        sqlite3_finalize(stmt);
    //    [DB close]
        return p;
    }
    
    - (NSArray *)pesonsWithName:(NSString *)name
    {
        sqlite3 *db = [DB open];
        sqlite3_stmt *stmt = nil;
        int flag = sqlite3_prepare_v2(db, "select * from class13 where name = ?", -1, &stmt, NULL);
        
        NSMutableArray *persons = nil;
        if (flag == SQLITE_OK) {
            sqlite3_bind_text(stmt, 1, [name UTF8String], -1, NULL);
            persons = [NSMutableArray arrayWithCapacity:2];
            while(sqlite3_step(stmt) == SQLITE_ROW)
            {
                int ID = sqlite3_column_int(stmt, 0);
                const unsigned char *name = sqlite3_column_text(stmt, 1);
                const unsigned char *sex = sqlite3_column_text(stmt, 2);
                const unsigned char *phone = sqlite3_column_text(stmt, 4);
                int age = sqlite3_column_int(stmt, 3);
                Person *p = [[Person alloc] initWithID:ID name:[NSString stringWithCString:(const char *)name encoding:NSUTF8StringEncoding] sex:[NSString stringWithCString:(const char *)sex encoding:NSUTF8StringEncoding] age:age phone:[NSString stringWithCString:(const char *)phone encoding:NSUTF8StringEncoding]];
                [persons addObject:p];
     
            }
        }
        
        sqlite3_finalize(stmt);
    //    [DB close];
        return persons;
    }
    - (void)addPerson:(Person *)p
    {
        sqlite3 *db = [DB open];
        sqlite3_stmt *stmt = nil;
        int flag = sqlite3_prepare_v2(db, "insert into class13(name,sex,age,phone) values(?,?,?,?)", -1, &stmt, NULL);
        
        if (flag == SQLITE_OK) {
            sqlite3_bind_text(stmt, 1, [p.name UTF8String], -1, NULL);
            sqlite3_bind_text(stmt, 2, [p.sex UTF8String], -1, NULL);
            sqlite3_bind_int(stmt, 3, p.age);
            sqlite3_bind_text(stmt, 4, [p.phone UTF8String], -1, NULL);
            if(sqlite3_step(stmt)==SQLITE_DONE)
            {
                NSLog(@"插入成功");
            }else{
                NSLog(@"插入失败");
            }
        }
    }
    
    - (void)updatePerson:(Person *)p
    {
    
        sqlite3 *db = [DB open];
        sqlite3_stmt *stmt = nil;
        int flag = sqlite3_prepare_v2(db, "update class13 set name = ? , sex = ? , age = ? , phone = ? where id = ?", -1, &stmt, NULL);
        if (flag == SQLITE_OK) {
            sqlite3_bind_text(stmt, 1, [p.name UTF8String], -1, NULL);
            sqlite3_bind_text(stmt, 2, [p.sex UTF8String], -1, NULL);
            sqlite3_bind_text(stmt, 4, [p.phone UTF8String], -1, NULL);
            sqlite3_bind_int(stmt, 3, p.age);
            sqlite3_bind_int(stmt, 5, p.ID);
            if (sqlite3_step(stmt) == SQLITE_DONE) {
                NSLog(@"更新成功");
            }else{
                NSLog(@"更新失败");
            }
    
        }
    }
    
    - (void)deletePerson:(Person *)p
    {
        sqlite3 *db = [DB open];
        sqlite3_stmt *stmt = nil;
        int flag = sqlite3_prepare_v2(db, "delete from class13 where id = ?", -1, &stmt, NULL);
        if (flag == SQLITE_OK) {
            sqlite3_bind_int(stmt, 1, p.ID);
            if (sqlite3_step(stmt) == SQLITE_DONE) {
                NSLog(@"删除成功");
            }else{
                NSLog(@"删除失败");
            }
        }
    }
    
    @end
    



  • 相关阅读:
    docker 安装部署 mysql(配置文件启动)
    mac Charles抓包
    docker 安装部署 redis(配置文件启动)
    安装mysql5.6
    Linux命令
    git命令汇总
    Java程序占用的内存可能会大于Xmx
    排序
    二分查找
    Elasticsearch核心技术(一):Elasticsearch环境搭建
  • 原文地址:https://www.cnblogs.com/xukunhenwuliao/p/3576223.html
Copyright © 2020-2023  润新知