Sqlite3

#import <Foundation/Foundation.h>

#import "sqlite3.h"

@interface SqlUtility : NSObject

{

    sqlite3 *db;

}

- (NSString *) dataFilePath;

- (BOOL) openDB;

- (void) closeDB;

- (void) addCourse:(NSString *)name andTime:(NSString *)time andPlace:(NSString *)place;

- (NSMutableArray *) getCourse;

- (void) Clear;

- (void) Delete:(NSString *)delete_name;

@end

//  SqlUtility.m

#import "SqlUtility.h"

@implementation SqlUtility

//返回数据库的路径名，只要名字僞data.mysqlite3,就是指同一个数据库

- (NSString *) dataFilePath  

{  

    NSArray *path =NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask,YES);  

    NSString *documentsDirectory = [path objectAtIndex:0];  

    

    return [documentsDirectory stringByAppendingPathComponent:@"data.mysqlite3"];  

}

- (BOOL) openDB

{

    //NSLog(@"pass_openDB");

    //打开数据库

    if (sqlite3_open([[selfdataFilePath]UTF8String], &db) !=SQLITE_OK) {

        NSLog(@"打开数据库失败！");

        return NO;

    }

    else {

        NSLog(@"打开数据库成功！");

    }

    //错误信息

    char *errorMsg;  

    //创建表的sql语句

    //注意是exists，不是exist

    NSString *createSQL =@"create table if not exists monday (name text primary key,time text,place text);"; 

    //创建表

    if (sqlite3_exec(db, [createSQLUTF8String],NULL, NULL, &errorMsg)!= SQLITE_OK) {  

        sqlite3_close(db);

        NSString *error=[[NSStringalloc]initWithUTF8String:errorMsg];

        NSLog(@"创建表失败！%@",error);

    } 

    return YES;

}

-(void) closeDB

{

    sqlite3_close(db);

}

- (void) addCourse:(NSString *)name andTime:(NSString *)time andPlace:(NSString *)place

{

    NSString *update = [[NSStringalloc]initWithFormat:@"insert into monday(name,time,place) values ('%@','%@','%@');",name,time,place];  

    char *errorMsg;  

    if (sqlite3_exec(db, [updateUTF8String],NULL, NULL, &errorMsg) == SQLITE_OK) {  

        NSLog(@"数据插入成功！");  

        sqlite3_free(errorMsg);  

    }  

}

- (NSMutableArray *)getCourse

{

    //NSLog(@"pass_get");

    NSMutableArray *array=[[NSMutableArrayalloc]init];

    //开始查询

    NSString *query =@"select name,time,place from monday";  

    sqlite3_stmt *statement;  

    if (sqlite3_prepare_v2(db, [queryUTF8String], -1, &statement,nil) == SQLITE_OK) { 

        //NSLog(@"pass_query");

        //注意后面的参数是SQLITE_ROW

        while (sqlite3_step(statement) ==SQLITE_ROW) {

           // NSLog(@"pass_step");

            //对于每一行，读取列数据

            char *name = (char *)sqlite3_column_text(statement,0);

            char *time = (char *)sqlite3_column_text(statement,1); 

            char *place = (char *)sqlite3_column_text(statement,2); 

            //把char*类型转化成nsstring

            NSString *nameString=[[NSStringalloc]initWithUTF8String:name];

            NSString *timeString=[[NSStringalloc]initWithUTF8String:time];

            NSString *placeString=[[NSStringalloc]initWithUTF8String:place];

            //把3个数据封装在字典里

            NSMutableDictionary *dic=[[NSMutableDictionaryalloc]init];

            [dic setValue:nameString forKey:@"name"];

            [dic setValue:timeString forKey:@"time"];

            [dic setValue:placeString forKey:@"place"];

            [array addObject:dic]; 

        }  

        sqlite3_finalize(statement);  

    } 

    else {

        NSLog(@"fail!");

    }

return array;

}

- (void) Clear

{

    NSString *delete = [[NSStringalloc]initWithFormat:@"delete from monday;"];  

    char *errorMsg;  

    if (sqlite3_exec(db, [deleteUTF8String],NULL, NULL, &errorMsg) == SQLITE_OK) {  

        NSLog(@"数据清除成功！");  

        sqlite3_free(errorMsg); 

    }

}

- (void) Delete:(NSString *)delete_name

{

    NSString *delete = [[NSStringalloc]initWithFormat:@"delete from monday where name='%@';",delete_name];  

    char *errorMsg;  

    if (sqlite3_exec(db, [deleteUTF8String],NULL, NULL, &errorMsg) == SQLITE_OK) {  

        NSLog(@"删除成功！");  

        sqlite3_free(errorMsg); 

    }

}

@end