SQLite初级操作

#import <Foundation/Foundation.h>

@interface LanOuStudent : NSObject

//名字
@property (nonatomic,retain)NSString *name;
//年龄
@property (nonatomic,assign)NSInteger age;
//性别
@property (nonatomic,retain)NSString *gender;
//学号
@property (nonatomic,assign)NSInteger number;
//图片的data
@property (nonatomic,retain)NSData *data;

@end

-----------------------------------------------------------------------

#import "LanOuStudent.h"

@implementation LanOuStudent


- (void)dealloc{
    [_name release];
    [_gender release];
    [_data release];
    [super dealloc];
}

@end

#import <Foundation/Foundation.h>
#import <sqlite3.h>
#import "LanOuStudent.h"

@interface SQLiteManager : NSObject

//使用SQLite数据库步骤
//1.引入libsqlite3.0 框架
//2.在数据库操作类中  引入<sqlite3.h>头文件
//注:咱们使用的方法  都在这个类中

//单例的初始化方法
+ (SQLiteManager *)shareManager;

// 插入数据
- (void)insertTableWithStudent:(LanOuStudent *)student;

//删除数据(根据一个条件删除)
- (void)deleteWithAge:(NSInteger )age;

//更新数据(根据一个条件来更新)
- (void)updateAge:(NSInteger)age byName:(NSString *)name;

//查询数据 (根据条件查询)
- (LanOuStudent *)selectStudentWithName:(NSString *)name age:(NSInteger)age;

// 查询所有(返回一个数组)

- (NSArray *)selectAllStudents;

@end

-----------------------------------------------------------------------

#import "SQLiteManager.h"

@implementation SQLiteManager

//单例的初始化方法
+ (SQLiteManager *)shareManager
{
    // 静态区指针
    static SQLiteManager *manager = nil;
    if (manager == nil) {
        manager = [[SQLiteManager alloc] init];
       
        //调用创建表的方法
        [manager createTable];
    }
   
    return manager;
}

//需要在静态区定义一个指针指向数据库 (让这个指针指向对象 从程序开始到结束 一直存在 程序结束后 被系统自动释放)

static sqlite3 *db =  nil;

//打开数据库
- (sqlite3 *)openDB
{
    // 判断数据库是否存在 如果存在 直接返回
   
    if (db != nil) {
        return db;
    }else
    {
        // 不存在 则创建一个并打开
       
        //获取当前documents的路径
        NSString *documents = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
       
        //拼接数据库文件路径(拼接的数据库文件的名字)
        NSString *dbPath = [documents stringByAppendingPathComponent:@"Student.sqlite"];
        NSLog(@"%@",dbPath);
       
        //创建或者打开数据库
        //参数1 文件的路径(需要把oc的字符串 转化成c语言的) dbPath.UTF8String把oc的转化成c语言
        //参数2 数据库的地址
        //接收一下返回值  判断是否打开成功
        int result = sqlite3_open(dbPath.UTF8String, &db);
       
        //SQLITE_OK 可以查表得到错误
        if (result == SQLITE_OK) {
            NSLog(@"打开成功");
        }else
        {
            NSLog(@"打开失败");
        }
        return db;
    }
   
}

//关闭数据库
- (void)closeDB
{
    int result = sqlite3_close(db);
    //判断是否关闭成功
    if (result == SQLITE_OK) {
        NSLog(@"关闭成功");
        //把数据库的指针 重置为空
        db = nil;
       
    }else
    {
        NSLog(@"关闭失败");
    }
}

//创建一张表
- (void)createTable
{
    //注意:一定在操作时  先打开数据库
    //操作完毕  关闭数据库
   
    //打开数据库
    db = [self openDB];
   
    //1.写SQL语句
    NSString *sql = @"create table IF NOT EXISTS lanOuStudent(number integer primary key not null,name text not null,gender text not null,age integer not null,data blob not null)";
    //2.执行sql语句
    //准备与执行语句的操作
   
    int result =  sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"建表成功");
    }else
    {
        NSLog(@"建表失败");
    }
   
    //关闭数据库
    [self closeDB];
   
}

// 插入数据
- (void)insertTableWithStudent:(LanOuStudent *)student
{
    //打开数据库
    db = [self openDB];
    //写SQL语句
    NSString *sql = @"insert into LanOuStudent (number,name,gender,age,data) values (?,?,?,?,?)";
    //创建一个跟随指针(指令集)
    sqlite3_stmt *stmt = nil;
    //执行SQL语句
    //预执行函数 需要用跟随指针 绑定问号 然后一步一步执行(可以判断SQL是否正确)
    int result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
       
        // 绑定问号(根据SQL语句 来决定绑定的顺序)
        // 注意:绑定的顺序 从1开始
        // 参数2 绑定的顺序
        // 参数3 要插入的值
        sqlite3_bind_int(stmt, 1, (int)student.number);
        sqlite3_bind_text(stmt, 2, student.name.UTF8String, -1, NULL);
        sqlite3_bind_text(stmt, 3, student.gender.UTF8String, -1, NULL);
        sqlite3_bind_int(stmt, 4, (int)student.age);
       
        // 绑定二进制数据
        // [student.data bytes]  相当于获取到data数据的内容
        // (int)[student.data length]  相当于获取到data数据的长度
        sqlite3_bind_blob(stmt, 5, [student.data bytes], (int)[student.data length], NULL);
        //执行绑定的语句
        sqlite3_step(stmt);
       
        NSLog(@"插入成功");
    }else
    {
        NSLog(@"插入失败");
    }
    //不管插入是否成功  都要释放跟随指针
    sqlite3_finalize(stmt);
   
    //关闭数据库
    [self closeDB];
   
}

/*
 
 //如果有data数据进行插入的话
 //sqlite3_exec 这个函数 会把data数据转化成字符串存入数据库
 //如果插入的数据没有data数据的话 直接用sqlite3_exec 函数 插入数据就行  不用绑定了
 NSString *sql = @"insert into LanOuStudent (number,name,gender,age) values ('%ld','%@','%@','%ld')"
 


- (void)text:(LanOuStudent *)student
{
    NSString *sql = @"insert into LanOuStudent (number,name,gender,age) values ('%ld','%@','%@','%ld')";
    NSString *sqlNew = [NSString stringWithFormat:@"insert into LanOuStudent (number,name,gender,age) values ('%ld','%@','%@','%ld')",student.number,student.name,student.gender,student.age];
}
*/

//删除数据(根据一个条件删除)
- (void)deleteWithAge:(NSInteger )age
{
#pragma mark -- 不绑定删除 需要拼接SQL语句
    //打开数据库
//    [self openDB];
//    //写SQL语句
//    NSString *sql = [NSString stringWithFormat:@"delete from lanOuStudent where age > '%ld'",age];
//    //SQL语句
//    int result = sqlite3_exec(db, sql.UTF8String , NULL, NULL, NULL);
//    //判断一下执行结果
//    if (result == SQLITE_OK) {
//        NSLog(@"删除成功");
//    }else
//    {
//        NSLog(@"删除失败");
//    }
//   //关闭数据库
//    [self closeDB];
   
#pragma mark -- 通过绑定删除方法
   
    //打开数据库
    [self openDB];
    //写SQL语句
    NSString *sql = @"delete from lanouStudent where age > ?";
    //创建跟随指针
    sqlite3_stmt *stmt = nil;
    //预执行语句
    int result = sqlite3_prepare_v2(db, sql.UTF8String , -1, &stmt, NULL);
    if (result == SQLITE_OK) {
       
        //绑定问好
        sqlite3_bind_int(stmt, 1, (int)age);
       
        //执行跟随执行 绑定的语句
        sqlite3_step(stmt);
       
        NSLog(@"删除成功");
    }else
    {
        NSLog(@"删除失败");
    }
    //不管成功与否 都要释放跟随指针
    sqlite3_finalize(stmt);
   
    //关闭数据库
    [self closeDB];
   
}

//更新数据(根据一个条件来更新)
//update lanOuStudent set age = '%ld' where name = '%@'
- (void)updateAge:(NSInteger)age byName:(NSString *)name
{
    //打开数据库
    [self openDB];
    //写SQL语句
    NSString *sql = [NSString stringWithFormat:@"update lanouStudent set age = '%ld' where name = '%@'",age,name];
    //执行语句
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"更新成功");
    }else
    {
        NSLog(@"更新失败");
    }
   
    //关闭数据库
    [self closeDB];
   
}

//查询数据 (根据条件查询)
//select name,imageData from lanOuStudent where name = ? and age = ?
- (LanOuStudent *)selectStudentWithName:(NSString *)name age:(NSInteger)age
{
    //打开数据库
    [self openDB];
    //写SQL语句
    NSString *sql = @"select * from lanouStudent where name = ? and age = ?";
    //创建跟随指针
    sqlite3_stmt *stmt = nil;
   
    //创建一个对象
    LanOuStudent *student = [[LanOuStudent alloc] init];
   
    //预执行语句
    int result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        //绑定问号
        sqlite3_bind_text(stmt, 1, name.UTF8String, -1, NULL);
        sqlite3_bind_int(stmt, 2, (int)age);
       
        // 查询时 如果下一行准备好了 返回 SQLITE_ROW 这时可以继续查询
        while (sqlite3_step(stmt) == SQLITE_ROW) {
           
            //读取数据
            //  <#int iCol#> 列数
            //如果你查询所有的字段的话 那么这个列数 就根据你创建表的顺序一样
            // 注意: 从0开始
           
            //如果你查询的是特定字段 那么这个列数 就要根据你的SQL语句来写
            //也是从0开
            int number = sqlite3_column_int(stmt, 0);
            char *name = (char *)sqlite3_column_text(stmt, 1);
            char *gender = (char *)sqlite3_column_text(stmt, 2);
            int age = sqlite3_column_int(stmt, 3);
           
            //读取二进制数据
            NSData *data = [NSData dataWithBytes:sqlite3_column_blob(stmt, 4)  length:sqlite3_column_bytes(stmt, 4)];
           
            //赋值model
            //[NSString stringWithUTF8String:<#(nonnull const char *)#>]; 把c语言字符串转化成oc字符串
            student.name = [NSString stringWithUTF8String:name];
            student.gender = [NSString stringWithUTF8String:gender];
            student.number = number;
            student.age = age;
            student.data = data;
           
           
        }
       
       
        NSLog(@"成功");
       
    }else
    {
        NSLog(@"失败");
    }
   
    //释放跟随指针
    sqlite3_finalize(stmt);
    //关闭数据库
    [self closeDB];
    //自动释放 对象
    return [student  autorelease];
   
   
}

// 查询所有(返回一个数组)
// select * from lanOuStudent
- (NSArray *)selectAllStudents
{
    //打开数据库
    [self openDB];
    //写SQL语句
    NSString *sql = @"select * from lanouStudent";
   
    //创建一个可变数组备用
    NSMutableArray *array = [NSMutableArray array];
   
    //创建跟随指针
    sqlite3_stmt *stmt = nil;
    //预执行语句
    int result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
      
       
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            LanOuStudent *student = [[LanOuStudent alloc] init];
           
            //读取数据
            int number = sqlite3_column_int(stmt, 0);
            char *name = (char *)sqlite3_column_text(stmt, 1);
            char *gender = (char *)sqlite3_column_text(stmt, 2);
            int age = sqlite3_column_int(stmt, 3);
            NSData *data = [NSData dataWithBytes:sqlite3_column_blob(stmt, 4) length:sqlite3_column_bytes(stmt, 4)];
           
            //赋值model
            student.number = number;
            student.name = [NSString stringWithUTF8String:name];
            student.gender = [NSString stringWithUTF8String:gender];
            student.age = age;
            student.data = data;
           
            //添加到数组
            [array addObject:student];

            [student release];

        }
       
        NSLog(@"成功");
    }else
    {
        NSLog(@"失败");
    }
   
    //释放跟随对象
    sqlite3_finalize(stmt);
    //关闭数据库
    [self closeDB];
    return array;
   
}

@end

-----------------------------------------------------------------------

- (void)viewDidLoad {

    [super viewDidLoad];
    // Do any additional setup after loading the view.
   
    SQLiteManager *manager = [SQLiteManager shareManager];
   
    //插入数据
    LanOuStudent *student = [[LanOuStudent alloc] init];
    student.name = @"王龙";
    student.gender = @"男";
    student.age = 11;
    student.number = 1;
    student.data = UIImagePNGRepresentation([UIImage imageNamed:@"21.jpg"]);
   
    //插入数据
    [manager insertTableWithStudent:student];
   
    //删除数据
    //[manager deleteWithAge:15];
   
    //更新数据
    [manager updateAge:18 byName:@"王龙"];
   
    //按要求查询
    //LanOuStudent *stu = [manager selectStudentWithName:@"王龙" age:18];
   
    //查询你所有
    NSArray *array = [manager selectAllStudents];
   

}