SQLite数据库

准备工作:引入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];
    
    
}