sqlite3数据库的基本操作

最新推荐文章于 2024-03-25 13:28:51 发布

中情

最新推荐文章于 2024-03-25 13:28:51 发布

阅读量352

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/zhongqing_fzq/article/details/48880711

本文介绍了一个用于操作SQLite数据库的Objective-C单例类实现，包括数据库的打开与关闭、表的创建、数据的增删改查等功能，并展示了如何使用该类进行具体操作。

3.一般把对数据库的操作单独作为一个类，并且该类是一个单例类

.h文件中的代码:

#import <Foundation/Foundation.h>
#import "Student.h"

@interface DBManager : NSObject

//创建一个单例对象
+(instancetype)shareDBManager;

#pragma mark-------------数据库的基本方法
//打开数据库
-(void)openDB;

//关闭数据库
-(void)closeDB;

//创建表
-(void)createTable;

//增
-(void)insertStudent:(Student *)student;

//删：一般不要根据主键删除，应为rowid要根据主键改变
-(void)deleteStudentWithName:(NSString *)name;

//删除17岁以下的学生
-(void)deleteStudentWithAge;

//改：根据姓名修改年龄
-(void)updateStudentAge:(NSInteger)age withName:(NSString *)name;

//查询全部
-(NSArray *)seleteAllStudent;

//根据条件查询
-(NSArray *)selectWithName:(NSString *)name;

.m中的主要代码:

#import "DBManager.h"

#pragma mark-----------导入包
#import <sqlite3.h>

@interface DBManager ()

@property(nonatomic,strong) NSMutableArray *array;

@end

@implementation DBManager

#pragma mark-------------伪单例的静态方法
+(instancetype)shareDBManager
{
    static DBManager *dbManager = nil;

#warning 判断条件一般放在前面
    if (nil == dbManager) {

        dbManager = [[DBManager alloc] init];

    }

    return dbManager;
}

#pragma mark-------------数据库的基本方法

//创建一个静态指针，表示唯一的
static sqlite3 *db = nil;

//打开数据库
-(void)openDB
{

    if (nil != db ) {
        return;
    }

    //创建路径
    NSString *path = NSHomeDirectory();
    path = [path stringByAppendingPathComponent:@"student.sqlite"];

  //打开数据库
-(void)openDB
{

    if (nil != db ) {
        return;
    }

    //创建路径
    NSString *path = NSHomeDirectory();
    path = [path stringByAppendingPathComponent:@"student.sqlite"];

    //打开数据库
int result = sqlite3_open([path UTF8String], &db);

    // SQLITE_OK:表示的是代码是否执行
      if (result == SQLITE_OK) {
        NSLog(@"哈哈,打开成功了");
    }
    else
    {
        NSLog(@"打开失败了,错误的操作数为%d",result);
    }
}

//关闭数据库
-(void)closeDB
{
    //调用关闭方法
    int result = sqlite3_close(db);

    if (result == SQLITE_OK) {

        //将数据库置空
        db = nil;
        NSLog(@"关闭成功");
    }
    else
    {
        NSLog(@"关闭失败,错误的操作数为%d",result);
    }

}

//创建表
-(void)createTable
{

    //准备sql语句
    NSString *createString = @"CREATE TABLE if not exists 'student' ('number' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE, 'name' TEXT NOT NULL, 'age' INTEGER NOT NULL, 'gender' TEXT DEFAULT male)";

    //创建表
    int result = sqlite3_exec(db,createString.UTF8String,NULL,NULL,NULL);

    if (result == SQLITE_OK) {
        NSLog(@"创建表成功了");
    }
    else
    {
        NSLog(@"创建表失败了，错误操作数为%d",result);
    }
}

//增
-(void)insertStudent:(Student *)student
{
    //准备
    NSString *insertString = [NSString stringWithFormat:@"insert into 'student'(name,age,gender)values('%@','%ld','%@')",student.name,student.age,student.gender];

    //执行
    int result = sqlite3_exec(db, insertString.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"插入数据成功");
    }
    else
    {
        NSLog(@"插入数据失败,错误操作数为%d",result);
    }


}

//删
-(void)deleteStudentWithName:(NSString *)name
{
    //准备
    NSString *deleteString = [NSString stringWithFormat:@"delete from 'student' where name = '%@' ",name ];

    int result = sqlite3_exec(db, deleteString.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    }
    else
    {
        NSLog(@"删除失败");
    }
}

//删除17岁以下的学生
-(void)deleteStudentWithAge
{
    //准备
    NSString *deleteStr = [NSString stringWithFormat:@"delete from 'student' where age < 17"];

    int result = sqlite3_exec(db, [deleteStr UTF8String], NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"小于17岁的删除成功");
    }
    else
    {
        NSLog(@"小于17岁的删除失败");
    }
}

//改：根据姓名修改年龄
-(void)updateStudentAge:(NSInteger)age withName:(NSString *)name
{
    //准备
    NSString *updateString = [NSString stringWithFormat:@"update 'student' set age = %ld where name = '%@'",age,name];
    //执行
    int result = sqlite3_exec(db, [updateString UTF8String], NULL, NULL, NULL);

    if (result == SQLITE_OK) {
        NSLog(@"修改成功");
    }
    else
    {
        NSLog(@"修改失败,错误为%d",result);
    }
}

//查

//查找全部的信息
/*-(NSArray *)seleteAllStudent
{
    //准备数组
    NSMutableArray *array = nil;

    //准备伴随指针
    sqlite3_stmt *stmt = nil;

    //准备sql语句
    NSString *selectString = @"select * from student";

    //准备执行：-1:读取的长度为最大值
    int result = sqlite3_prepare_v2(db, selectString.UTF8String, -1, &stmt, NULL);

    if (result == SQLITE_OK) {

        //给数组开辟空间
        array = [NSMutableArray arrayWithCapacity:20];

        while (sqlite3_step(stmt) == SQLITE_ROW) {

            //字段位置:number、name、age、gender
            Student *student = [[Student alloc] init];

            student.number = sqlite3_column_int(stmt, 0);

            //根据字段查询的字符串需要强转为const char *类型后，再转化成OC字符串
            student.name = [NSString stringWithUTF8String:(const char*)sqlite3_column_text(stmt, 1)];

            student.age = sqlite3_column_int(stmt, 2);


            student.gender = [NSString stringWithUTF8String:(const char*)sqlite3_column_text(stmt, 3)];

            //添加到数组中
            [array addObject:student];

        }


            }
    else
    {
        NSLog(@"查询失败,失败操作是%d",result);
    }

    //释放伴随指针
    sqlite3_finalize(stmt);

    return array;
}
*/
-(NSArray *)seleteAllStudent
{
    //准备存放数据的数组
    NSMutableArray *array = nil;
    //准备查询语句
    NSString *selectString = @"select * from student";
    //准备伴随指针
    sqlite3_stmt *stmt = nil;

    //执行
    //判断sql语句是否正确
    int result = sqlite3_prepare_v2(db,selectString.UTF8String , -1, &stmt, NULL);

    if (result == SQLITE_OK) {

       //为数组开辟空间
        array = [NSMutableArray arrayWithCapacity:4];

        //循环每一行
        while (sqlite3_step(stmt) == SQLITE_ROW) {

            //创建一个学生对象
            Student *student = [[Student alloc] init];

            //根据每个字段给对象赋值
            student.number = sqlite3_column_int(stmt, 0);
            student.name = [NSString stringWithUTF8String:(const char*)sqlite3_column_text(stmt, 1)];
            student.age = sqlite3_column_int(stmt, 2);
            student.gender = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 3)];

            //将学生对象加入数组中
            [array addObject:student];

        }




    }
    else
    {
        NSLog(@"查找失败，失败的原因是%d",result);
    }

    //释放伴随指针
    sqlite3_finalize(stmt);

    return array;

}

//根据条件查询
-(NSArray *)selectWithName:(NSString *)name
{
//    方法一：
//    NSString *selectString = [NSString stringWithFormat:@"select * from 'student' where name = '%@'",name];

    //方法二:绑定
    //查询字符串
    NSString *sqlWord = @"select * from student where name = ?";

    NSMutableArray *array = nil;
    //伴随指针
    sqlite3_stmt *stmt = nil;

int result = sqlite3_prepare(db, [sqlWord UTF8String], -1, &stmt, NULL);
    if(result == SQLITE_OK)
    {
        array = [NSMutableArray arrayWithCapacity:4];
        //1：是绑定的第几个问号，从1开始

        //绑定指针
        sqlite3_bind_text(stmt, 1, [name UTF8String], -1, NULL);

        while (sqlite3_step(stmt) == SQLITE_ROW) {

            Student *student = [Student new];
            student.number = sqlite3_column_int64(stmt, 0);
            student.name = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];
            student.gender = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 2)];
            student.age = sqlite3_column_int(stmt, 3);


            [array addObject:student];
            NSLog(@"查询成功");
        }
    }
    else
    {
        NSLog(@"查询失败");
    }

    sqlite3_finalize(stmt);

    return array;
}

//懒方法
-(NSMutableArray *)array
{
    if (_array == nil) {
        _array = [NSMutableArray array];
    }
    return _array;
}

@end

将图片存储到数库中，要转换成NSData数据类型存储在数据库中，数据库中用blob类型的数据

Model类的属性

@property (nonatomic, strong)NSString *name;
@property (nonatomic, assign)NSInteger age;
@property (nonatomic, strong)NSString *gender;
@property (nonatomic, strong)NSString *phone;
@property (nonatomic, strong)NSString *address;
@property (nonatomic, strong)NSData *photo;

转换为NSData类型

Person *per = [[Person alloc] init];
    per.name = @"Joke";
    per.age = 20;
    per.gender = @"f";
    per.phone = @"110";
    per.address = @"fff";
    per.photo = UIImageJPEGRepresentation([UIImage imageNamed:@"nvshen.png"], 1);

//插入数据
- (void)myInsertPerson:(Person *)person
{


    static sqlite3_stmt *stmt=nil;
    NSString *insertString = [NSString stringWithFormat:@"insert into 'person' ('name','age','gender','phone','address','photo') values (?,?,?,?,?,?)"];


    int result = sqlite3_prepare(db, insertString.UTF8String, -1, &stmt, nil);
    if (SQLITE_OK == result) {
        sqlite3_bind_text(stmt, 1, person.name.UTF8String, -1, nil);
        sqlite3_bind_int64(stmt, 2, person.age);
        sqlite3_bind_text(stmt, 3, person.gender.UTF8String, -1, nil);
        sqlite3_bind_text(stmt, 4, person.phone.UTF8String, -1, nil);
        sqlite3_bind_text(stmt, 5, person.address.UTF8String, -1, nil);
        sqlite3_bind_blob(stmt, 6, [person.photo bytes], (int)[person.photo length], nil);
        if (SQLITE_DONE == sqlite3_step(stmt)) {
            NSLog(@"ok");
        }
    }
    sqlite3_finalize(stmt);

}

查找

读取数据
- (NSArray *)selectData{

    NSMutableArray *array = [NSMutableArray array];
    NSString *selectString = @"SELECT * FROM 'STUDENT'";
    sqlite3_stmt *stmt = nil;
    //
    int result = sqlite3_prepare(db, selectString.UTF8String, -1, &stmt, NULL);

    if (result == SQLITE_OK) {
        while (sqlite3_step(stmt) == SQLITE_ROW) {

            NSMutableDictionary *dict = [NSMutableDictionary dictionary];

            dict[@"name"] = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 0)];

            //读取blob数据
            (Byte) *data = (Byte *)sqlite3_column_blob(stmt, 1);
            int size = sqlite3_column_bytes(stmt, 1);
            //转换为NSData
            NSData *imageData = [NSData dataWithBytes:data length:size];

            dict[@"image"] = imageData;

            [array addObject:dict];
        }
    }
    sqlite3_finalize(stmt);
    return array;
}