• iOS SQLite增删改查(简单应用)


    
    
     // 注意: 在工程里导入libsqlite3.tbd库(Xcode7,如果Xcode7以下的版本则导入libsqlite3.dylib).




    #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 "PersonInfo.h"
    #import "SQLManager.h"
    
    @interface RootViewController ()
    
    
    @end
    
    @implementation RootViewController
    
    - (void)viewDidLoad {
        [super viewDidLoad];
        SQLManager *sqlManage = [[SQLManager alloc] init];
    
    //    PersonInfo *person1 = [[PersonInfo alloc] init];
    //    person1.identifierNumber = 0001;
    //    person1.name = @"lf";
    //    person1.hoby = @"basketball";
    //    person1.address = @"广州";
    //    person1.age = 21;
    //
    //    BOOL success = [sqlManage insertPersonInfoList:person1];
    //    if (success) {
    //        NSLog(@"成功存储");
    //    }
    //    PersonInfo *person2 = [[PersonInfo alloc] init];
    //    person2.identifierNumber = 0002;
    //    person2.name = @"gl";
    //    person2.hoby = @"sleep";
    //    person2.address = @"湖北";
    //    person2.age = 18;
    //    [sqlManage insertPersonInfoList:person2];
        
        PersonInfo *person3 = [[PersonInfo alloc] init];
        person3.identifierNumber = 0003;
        person3.name = @"wh";
        person3.hoby = @"smile";
        person3.address = @"广东";
        person3.age = 16;
    //    BOOL success =  [sqlManage updatePersonInfoList:person3];
    //    if (success) {
    //        NSLog(@"更新成功");
    //    }else{
    //        NSLog(@"更新失败");
    //    }
    //
    //    NSMutableArray *arr = [sqlManage getPersonInfoList];
    //    BOOL isDelete = [sqlManage deletePersonInfo:person3];
    //    if (isDelete) {
    //        NSLog(@"删除成功");
    //    }else{
    //        NSLog(@"删除失败");
    //    }
    //    
    //    NSMutableArray *Marr = [sqlManage getPersonInfoList];
    
        
    //    NSMutableArray *arr = [sqlManage getPersonInfoList];
    //    NSMutableArray *result = [sqlManage searchPersonInfoListByName:@"lf"];
    //    NSLog(@"查询的结果为:%@",result);
    }
    
    - (void)didReceiveMemoryWarning {
        [super didReceiveMemoryWarning];
        // Dispose of any resources that can be recreated.
    }
    
    
    
    @end
    #import <Foundation/Foundation.h>
    #import <sqlite3.h>
    
    @class PersonInfo;
    
    @interface SQLManager : NSObject
    
    @property (nonatomic) sqlite3 *database;
    
    // 创建数据库
    - (BOOL)createPersonInfoList:(sqlite3 *)db;
    
    // 插入数据
    - (BOOL) insertPersonInfoList:(PersonInfo *)personInfo;
    
    // 获取数据
    - (NSMutableArray *)getPersonInfoList;
    
    // 查询数据
    - (NSMutableArray *)searchPersonInfoListByName:(NSString *)name;
    
    // 更新数据
    - (BOOL)updatePersonInfoList:(PersonInfo *)updatePersonInfo;
    
    // 删除数据
    - (BOOL)deletePersonInfo:(PersonInfo *)deletePersonInfo;
    
    @end
    #import "SQLManager.h"
    #import "PersonInfo.h"
    
    #define FileName @"personInfo.sqlite" //这里很神奇,可以定义成任何类型的文件,也可以不定义成.db文件,任何格式都行,定义成.sb文件都行,达到了很好的数据隐秘性
    
    
    @implementation SQLManager
    
    @synthesize database;
    
    - (instancetype)init
    {
        self = [super init];
        if (self) {
            
        }
        return self;
    }
    
    // 获取document目录并返回数据库目录
    - (NSString *)dataFilePath{
        NSString *path = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
        NSLog(@"数据库当前路径为:%@",path);
        return [path stringByAppendingPathComponent:FileName];
    }
    
    // 创建,打开数据库
    - (BOOL)openDB{
        // 获取数据库路径
        NSString *path = [self dataFilePath];
        // 文件管理器
        NSFileManager *fileManager = [NSFileManager defaultManager];
        // 判断数据库是否存在
        BOOL find = [fileManager fileExistsAtPath:path];
        //如果数据库存在,则用sqlite3_open直接打开(不要担心,如果数据库不存在sqlite3_open会自动创建)
        if (find) {
            NSLog(@" 数据库文件已存在!");
            
            //打开数据库,这里的[path UTF8String]是将NSString转换为C字符串,因为SQLite3是采用可移植的C(而不是Objective-C)编写的,它不知道什么是NSString.
            if (sqlite3_open([path UTF8String], &database) != SQLITE_OK) {
                //如果打开数据库失败则关闭数据库
                sqlite3_close(database);
                NSLog(@"数据库打开失败!");
                return NO;
            }else{
                NSLog(@"数据库打开成功!");
                //  创建列表
                [self createPersonInfoList:database];
                return YES;
            }
        }
        //如果发现数据库不存在则利用sqlite3_open创建数据库(上面已经提到过),与上面相同,路径要转换为C字符串
        if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) {
            // 创建列表
            [self createPersonInfoList:database];
            return YES;
        }else{
            //如果创建并打开数据库失败则关闭数据库
            sqlite3_close(database);
            NSLog(@"数据库打开失败 ~~");
            return NO;
        }
        return NO;
    }
    - (BOOL)createPersonInfoList:(sqlite3 *)db{
        //这句是大家熟悉的SQL语句
        //把identifierNumber设置为主键,主键是唯一表示该对象(每个对象的主键不能相同)
        char *sql = "create table if not exists PersonInfoTable (identifierNumber INTEGER PRIMARY KEY AUTOINCREMENT,name text,address text, hoby text,age int)";// name是列名,text 是数据类型
        
        sqlite3_stmt *statement;
        //sqlite3_prepare_v2 接口把一条SQL语句解析到statement结构里去. 使用该接口访问数据库是当前比较好的的一种方法
        NSInteger sqlReturn = sqlite3_prepare_v2(database, sql, -1, &statement, nil);
        //第一个参数跟前面一样,是个sqlite3 * 类型变量,
        //第二个参数是一个 sql 语句。
        //第三个参数我写的是-1,这个参数含义是前面 sql 语句的长度。如果小于0,sqlite会自动计算它的长度(把sql语句当成以结尾的字符串)。
        //第四个参数是sqlite3_stmt 的指针的指针。解析以后的sql语句就放在这个结构里。
        //第五个参数是错误信息提示,一般不用,为nil就可以了。
        //如果这个函数执行成功(返回值是 SQLITE_OK 且 statement 不为NULL ),那么下面就可以开始插入二进制数据。
        
        //如果SQL语句解析出错的话程序返回
        if (sqlReturn != SQLITE_OK) {
            NSLog(@"创建数据库表失败");
            return NO;
        }
        
        // 执行SQL语句
        int success = sqlite3_step(statement);
        //释放sqlite3_stmt
        sqlite3_finalize(statement);
        
        //执行SQL语句失败
        if (success != SQLITE_DONE) {
            NSLog(@"没有成功创建数据库");
            return NO;
        }
        NSLog(@"成功创建数据库");
        return YES;
    }
    
    // 插入数据
    - (BOOL)insertPersonInfoList:(PersonInfo *)personInfo{
        // 先判断数据库是否打开
        if ([self openDB]) {
            sqlite3_stmt *statement;
            //这个 sql 语句特别之处在于 values 里面有个? 号。在sqlite3_prepare函数里,?号表示一个未定的值,它的值等下才插入。
            static char *sql = "INSERT INTO PersonInfoTable(identifierNumber,name,address,hoby,age) VALUES(?,?,?,?,?)";
            
            int success2 = sqlite3_prepare_v2(database, sql, -1, &statement, NULL);
            if (success2 != SQLITE_OK) {
                NSLog(@"数据插入失败!");
                sqlite3_close(database);
                return NO;
            }
            
            //这里的数字1,2,3代表上面的第几个问号,这里将三个值绑定到三个绑定变量
            //identifierNumber,name,address,hoby,age
            sqlite3_bind_int(statement, 1, personInfo.identifierNumber);
            sqlite3_bind_text(statement, 2, [personInfo.name UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(statement, 3, [personInfo.address UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(statement, 4, [personInfo.hoby UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_int(statement, 5, personInfo.age);
            
            //执行插入语句
            success2 = sqlite3_step(statement);
            //释放statement
            sqlite3_finalize(statement);
            
            //如果插入失败
            if (success2 == SQLITE_ERROR) {
                NSLog(@"数据插入失败");
                //关闭数据库
                sqlite3_close(database);
                return NO;
            }
            //关闭数据库
            sqlite3_close(database);
            return YES;
        }
        return NO;
    }
    
    // 获取数据
    - (NSMutableArray *)getPersonInfoList{
        NSMutableArray *array = [[NSMutableArray alloc] init];
        ////判断数据库是否打开
        if ([self openDB]) {
            
            sqlite3_stmt *statement = nil;
            //sql语句
            //identifierNumber,name,address,hoby,age
            char *sql = "SELECT identifierNumber,name,address,hoby,age FROM PersonInfoTable";//从PersonInfoTable这个表中获取 identifierNumber,name,address,hoby,age,若获取全部的话可以用*代替identifierNumber,name,address,hoby,age。
            
            if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) != SQLITE_OK) {
                NSLog(@"预编译失败");
                return nil;
            }else{
                //查询结果集中一条一条的遍历所有的记录,这里的数字对应的是列值,注意这里的列值,跟上面sqlite3_bind_text绑定的列值不一样!一定要分开,不然会crash,只有这一处的列号不同,注意!
                //identifierNumber,name,address,hoby,age
                while (sqlite3_step(statement) == SQLITE_ROW) {
                    PersonInfo *person = [[PersonInfo alloc] init];
                    person.identifierNumber = sqlite3_column_int(statement, 0);
                    char *name = (char *)sqlite3_column_text(statement, 1);
                    person.name = [NSString stringWithUTF8String:name];
                    char *address = (char *)sqlite3_column_text(statement, 2);
                    person.address = [NSString stringWithUTF8String:address];
                    char *hoby = (char *)sqlite3_column_text(statement, 3);
                    person.hoby = [NSString stringWithUTF8String:hoby];
                    person.age = sqlite3_column_int(statement, 4);
                    [array addObject:person];
                    NSLog(@"%d--%@--%@--%@--%d",person.identifierNumber,person.name,person.address,person.hoby,person.age);
                }
            }
            //清理statement对象
            sqlite3_finalize(statement);
            // 关闭数据库
            sqlite3_close(database);
        }
        return array;
    }
    
    - (NSMutableArray *)searchPersonInfoListByName:(NSString *)name{
        
        NSMutableArray *array = [[NSMutableArray alloc] init];
        //判断数据库是否打开
        if ([self openDB]) {
            
            sqlite3_stmt *statement = nil;
            //sql语句
            NSString *querySQL = [NSString stringWithFormat:@"SELECT * FROM PersonInfoTable WHERE name like "%@"",name];
            const char *sql = [querySQL UTF8String];
             //        char *sql = "SELECT * FROM PersonInfoTable WHERE name like ?";//这里用like代替=可以执行模糊查找,原来是"SELECT * FROM PersonInfoTable WHERE name = ?"
            
            if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) != SQLITE_OK) {
                NSLog(@"查询失败");
                sqlite3_close(database);
                return nil;
            }else{
                sqlite3_bind_text(statement, 2, [name UTF8String], -1, SQLITE_TRANSIENT);
                //查询结果集中一条一条的遍历所有的记录,这里的数字对应的是列值。
                while (sqlite3_step(statement) == SQLITE_ROW) {
                    PersonInfo *info = [[PersonInfo alloc] init];
                    info.identifierNumber = sqlite3_column_int(statement, 0);
                    char *name = (char *)sqlite3_column_text(statement, 1);
                    info.name = [NSString stringWithUTF8String:name];
                    char *address = (char *)sqlite3_column_text(statement, 2);
                    info.address = [NSString stringWithUTF8String:address];
                    char *hoby = (char *)sqlite3_column_text(statement, 3);
                    info.hoby = [NSString stringWithUTF8String:hoby];
                    info.age = sqlite3_column_int(statement, 4);
                    [array addObject:info];
                    NSLog(@"查询:%d--%@--%@--%@--%d",info.identifierNumber,info.name,info.address,info.hoby,info.age);
                }
            }
            //清理statement对象
            sqlite3_finalize(statement);
            // 关闭数据库
            sqlite3_close(database);
        }
        return array;
    }
    
    // 更新数据
    - (BOOL)updatePersonInfoList:(PersonInfo *)updatePersonInfo{
        if ([self openDB]) {
            
            sqlite3_stmt *statemnet ;//这相当一个容器,放转化OK的sql语句
            //组织SQL语句
            //identifierNumber,name,address,hoby,age
            char *sql = "REPLACE INTO PersonInfoTable (identifierNumber,name,address,hoby,age) VALUES(?,?,?,?,?)";
            
            //将SQL语句放入sqlite3_stmt中
            int success = sqlite3_prepare_v2(database, sql, -1, &statemnet, NULL);
            if (success != SQLITE_OK) {
                NSLog(@"数据更新失败");
                // 关闭数据库
                sqlite3_close(database);
                return NO;
            }
            
            //这里的数字1,2,3代表第几个问号。这里只有1个问号,这是一个相对比较简单的数据库操作,真正的项目中会远远比这个复杂
            //绑定text类型的数据库数据
            //identifierNumber,name,address,hoby,age
            sqlite3_bind_int(statemnet, 1, updatePersonInfo.identifierNumber);
            sqlite3_bind_text(statemnet, 2, [updatePersonInfo.name UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(statemnet, 3, [updatePersonInfo.address UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(statemnet, 4, [updatePersonInfo.hoby UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_int(statemnet, 5, updatePersonInfo.age);
            ;
            
            //执行SQL语句。这里是更新数据库
            success = sqlite3_step(statemnet);
            //释放statement
            sqlite3_finalize(statemnet);
            
            //如果执行失败
            if (success == SQLITE_ERROR) {
                NSLog(@"更新的新数据失败");
                sqlite3_close(database);
                return NO;
            }
            //执行成功后依然要关闭数据库
            sqlite3_close(database);
            return YES;
        }
        return NO;
    }
    
    // 删除数据
    - (BOOL)deletePersonInfo:(PersonInfo *)deletePersonInfo{
        if ([self openDB]) {
            sqlite3_stmt *statement;
            // SQL语句
            //identifierNumber,name,address,hoby,age
            static char  *sql = "delete from PersonInfoTable where identifierNumber = ? and name = ? and address = ? and hoby = ? and age = ?";
            //将SQL语句放入sqlite3_stmt中
            int success = sqlite3_prepare_v2(database, sql, -1, &statement, NULL);
            if (success != SQLITE_OK) {
                NSLog(@"删除数据失败");
                sqlite3_close(database);
                return NO;
            }
            //这里的数字1,2,3代表第几个问号。这里只有1个问号,这是一个相对比较简单的数据库操作,真正的项目中会远远比这个复杂
            sqlite3_bind_int(statement, 1, deletePersonInfo.identifierNumber);
            sqlite3_bind_text(statement, 2, [deletePersonInfo.name UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(statement, 3, [deletePersonInfo.address UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(statement, 4, [deletePersonInfo.hoby UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_int(statement, 5, deletePersonInfo.age);
            ;
            // 执行SQL语句。这里是删除数据
            success = sqlite3_step(statement);
            //释放statement
            sqlite3_finalize(statement);
            
            //如果执行失败
            if (success == SQLITE_ERROR) {
                NSLog(@"SQL 执行删除数据失败");
                // 关闭数据库
                sqlite3_close(database);
                return NO;
            }
            //执行成功后依然要关闭数据库
            sqlite3_close(database);
            return YES;
        }
        return NO;
    }
    
    @end
    #import <Foundation/Foundation.h>
    
    @interface PersonInfo : NSObject
    
    @property (strong, nonatomic) NSString *name;
    @property (assign, nonatomic) int age;
    @property (strong, nonatomic) NSString *address;
    @property (assign, nonatomic) int identifierNumber;
    @property (strong, nonatomic) NSString *hoby;
    
    
    @end
    #import "PersonInfo.h"
    
    @implementation PersonInfo
    
    @synthesize name;
    @synthesize address;
    @synthesize hoby;
    @synthesize age;
    @synthesize identifierNumber;
    
    -(void)dealloc{
        address = nil;
        hoby = nil;
        name = nil;
    }
    
    - (id)init
    {
        self = [super init];
        if (self) {
            name = @"";
            hoby = @"";
            address = @"";
            age = 0;
            identifierNumber = 0;
        }
        return self;
    }
    
    @end
  • 相关阅读:
    利用余数选择特殊位置元素
    CSS hack
    css选择器
    按yyyy-mm-dd格式输入一个日期,判断这是这一年的第几天
    输入不同year,month,打印月历
    java学习之多线程(二)
    java学习之多线程
    剑指offer--第一个只出现一次的字符
    剑指offer--两个链表的第一个公共结点
    剑指offer--最小的k个数
  • 原文地址:https://www.cnblogs.com/lantu1989/p/4949373.html
Copyright © 2020-2023  润新知