SQLite数据库

来源：互联网发布：jquery 方法与js种方法编辑：程序博客网时间：2024/06/16 03:19

准备工作:引入libsqlite3.0.dylib框架

先建立一个Student类

声明:

@property(nonatomic,copy)NSString *name;
@property(nonatomic,copy)NSString *sex;
@property(nonatomic,copy)NSString *age;

建立SQLite工具类DataBaseHandle

#import <Foundation/Foundation.h>@class Student;@interface DataBaseHandle : NSObject+(DataBaseHandle *)sharDB;//获取documents路径-(NSString *)documentsPath;//打开数据库-(void)openDB;//关闭数据库-(void)closeDB;//创建表-(void)createTable;//插入信息-(void)insertStuden:(Student *)stu;//修改信息-(void)updateMessage;//删除信息-(void)deleteMessage;//查找全部-(void)selectAllStudent;//条件查找-(void)selectWithSex:(NSString *)sex;@end

实现 .m

#import "DataBaseHandle.h"#import <sqlite3.h>#import "Student.h"static DataBaseHandle *database=nil;@implementation DataBaseHandle#pragma mark 实现单例+(DataBaseHandle *)sharDB{    if (nil==database) {        database=[[DataBaseHandle alloc] init];            }    return database;}-(NSString *)documentsPath{    NSString *documentsPath=NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0];        return documentsPath;}//在操作数据库之前,引入框架//声明一个数据库的对象static sqlite3 *db=nil;#pragma mark 打开数据库-(void)openDB{    if (nil==db) {        //拼接数据库存放路径        NSString *dbPath=[[self documentsPath]stringByAppendingString:@"/student.sqlite"];        //根据路径打开数据库        //如果该路径下没有数据库就自动创建一个                //打开数据库      int result= sqlite3_open(dbPath.UTF8String, &db);        //判断是否执行成功        if (result==SQLITE_OK) {                       NSLog(@"数据库打开成功");        }        else{            NSLog(@"数据库打开失败");                }    }}//关闭数据库-(void)closeDB{    //执行数据库关闭   int result= sqlite3_close(db);    if (result==SQLITE_OK) {        //如果关闭成功,置为nil        db=nil;        NSLog(@"数据库关闭成功");            }    else{        NSLog(@"数据库关闭失败");        }}#pragma mark 创建表-(void)createTable{    //要创建表,就要使用sql语句    //sql语句在这是以字符串的形式存在的    NSString *createString=@"CREATE TABLE IF NOT EXISTS student (sid INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , sname TEXT, ssex TEXT, sage TEXT)";    //执行sql语句  int result=  sqlite3_exec(db, createString.UTF8String, NULL, NULL, NULL);    if (result ==SQLITE_OK) {        NSLog(@"建表成功");    }else{        NSLog(@"建表失败");    }  }#pragma mark 插入数据-(void)insertStuden:(Student *)stu{    //准备插入语句    NSString *insertString=[NSString stringWithFormat:@"INSERT INTO student (sname,ssex,sage) VALUES ('%@','%@','%@')",stu.name,stu.sex,stu.age];    //执行语句    int result=  sqlite3_exec(db, insertString.UTF8String, NULL, NULL, NULL);    if (result ==SQLITE_OK) {        NSLog(@"插入成功");    }else{        NSLog(@"插入失败");    }}#pragma mark 修改数据-(void)updateMessage{    //准备修改sql语句字符串    NSString *updateString=@"UPDATE student SET sage = '95' ,sname='陈贵阳' WHERE  sid = 2";    int result= sqlite3_exec(db, updateString.UTF8String, NULL, NULL, NULL);        if (result==SQLITE_OK) {        NSLog(@"修改成功");    }else{        NSLog(@"修改失败");    }        }#pragma mark 删除数据-(void)deleteMessage{    //删除语句    NSString *deleteString=@"delete from student where sid=1";    int result=sqlite3_exec(db, deleteString.UTF8String, NULL, NULL, NULL);    if (result==SQLITE_OK) {        NSLog(@"删除成功");    }else{        NSLog(@"删除失败");    }}#pragma mark 查找全部-(void)selectAllStudent{    //准备查找语句    NSString *selectAll=@"SELECT * FROM student";    //创建伴随指针    sqlite3_stmt *stmt=nil;        //预备执行    int result=sqlite3_prepare(db, selectAll.UTF8String, -1, &stmt, NULL);    if (result==SQLITE_OK) {        //在没有查找完之前一直循环执行的        while (sqlite3_step(stmt)==SQLITE_ROW) {            //取出sid(第0列)            NSInteger sid= sqlite3_column_int(stmt, 0);            //取出sname(第1列)            NSString *sname=[NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];            //取出ssex(第2列)             NSString *ssex=[NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 2)];            //取出sage(第3列)            NSString *sage=[NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 3)];            NSLog(@"sid:%d,sname:%@,ssex:%@,sage:%@",sid,sname,ssex,sage);             }                //关闭伴随指针      sqlite3_finalize(stmt);              NSLog(@"查询成功");            }else{        //结束伴随指针        sqlite3_finalize(stmt);        NSLog(@"查询失败");    }}#pragma mark 按性别查找-(void)selectWithSex:(NSString *)sex{    //准备查找语句    NSString *selectString=@"select * from student where ssex = ?";    //伴随指针    sqlite3_stmt *stmt=nil;    //预执行    int result=sqlite3_prepare(db, selectString.UTF8String, -1, &stmt, NULL);    if (result==SQLITE_OK) {        //绑定?的值        //1代表第一个?和参数绑定        sqlite3_bind_text(stmt, 1, sex.UTF8String, -1, NULL);                while (sqlite3_step(stmt)==SQLITE_ROW) {                        NSInteger sid=sqlite3_column_int(stmt, 0);            NSString *sname=[NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];            NSString *ssex=[NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 2)];            NSString *sage=[NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 3)];            NSLog(@"sid:%d,sname:%@,ssex%@,sage:%@",sid,sname,ssex,sage);                    }        sqlite3_finalize(stmt);                NSLog(@"查询成功");    }else{        //关闭伴随指针        sqlite3_finalize(stmt);        NSLog(@"查询失败");        }    }@end

控制器里边实现

- (void)viewDidLoad{    [super viewDidLoad];// Do any additional setup after loading the view, typically from a nib.    //打开数据库    [[DataBaseHandle sharDB] openDB];   // NSLog(@"%@",NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES));    //创建表 // [[DataBaseHandle sharDB]createTable];    //插入数据//    Student *s1=[[Student alloc] init];//    s1.name=@"王军1";//    s1.sex=@"女";//    s1.age=@"46";//   [[DataBaseHandle sharDB] insertStuden:s1];//        //修改数据    //[[DataBaseHandle sharDB] updateMessage];        //删除数据   // [[DataBaseHandle sharDB] deleteMessage];        //查找全部  //  [[DataBaseHandle sharDB] selectAllStudent];        //查询按性别    [[DataBaseHandle sharDB] selectWithSex:@"女"];            //关闭数据库    [[DataBaseHandle sharDB] closeDB];        }

0 0