// ViewController.m// sqlite3//// Created by 周玉 on 2017/12/13.// Copyright © 2017年 guidekj. All rights reserved.//#import "ViewController.h"#import <sqlite3.h>#define KUIScreenWidth [UIScreen mainScreen].bounds.size.width#define KUIScreenHeight [UIScreen mainScreen].bounds.size.height#define FILE_NAME @"saas.sqlite"static sqlite3 *db = nil;@interface ViewController ()@end@implementation ViewController- (void)viewDidLoad {  [super viewDidLoad];  self.title = @"sqlite3";  //DDB//  [self openDB];//  [self closeDB];  //DDL//  [self createTable];//  [self dropTable];  //DML//  [self insertData];//  [self updateData];//  [self deleteData];  //DQL  [self queryData];}#pragma mark 查詢- (void)queryData{  sqlite3 *newDB = [self openDB];  sqlite3_stmt *statement = nil;  NSString *sqlStr = @"SELECT * FROM SAAS_PERSON";  int result = sqlite3_prepare_v2(newDB, sqlStr.UTF8String, -1, &statement, NULL);  if (result == SQLITE_OK) {    //遍歷查詢結(jié)果    if (!(sqlite3_step(statement) == SQLITE_DONE)) {      while (sqlite3_step(statement) == SQLITE_ROW) {        int ID = sqlite3_column_int(statement, 0);        const unsigned char *name = sqlite3_column_text(statement, 1);        const unsigned char *sex = sqlite3_column_text(statement, 2);        int age = sqlite3_column_int(statement, 3);        const unsigned char *description = sqlite3_column_text(statement, 4);        NSLog(@"ID = %d , name = %@ , sex = %@ , age = %d , description = %@",ID,[NSString stringWithUTF8String:(const char *)name],[NSString stringWithUTF8String:(const char *)sex],age,[NSString stringWithUTF8String:(const char *)description]);      }    } else {      NSLog(@"查詢語(yǔ)句完成");    }  } else {    NSLog(@"查詢語(yǔ)句不合法");  }  sqlite3_finalize(statement);  [self closeDB];}#pragma mark 刪除數(shù)據(jù)記錄- (void)deleteData{  sqlite3 *newDB = [self openDB];  sqlite3_stmt *statement = nil;  NSString *sqlStr = @"DELETE FROM SAAS_PERSON WHERE NAME = '王鵬飛'";  int result = sqlite3_prepare_v2(newDB, sqlStr.UTF8String, -1, &statement, NULL);  if (result == SQLITE_OK) {    if (sqlite3_step(statement) == SQLITE_DONE) {      NSLog(@"刪除操作完成");    }  } else {    NSLog(@"刪除操作不合法");  }  sqlite3_finalize(statement);  [self closeDB];}#pragma mark 更新數(shù)據(jù)記錄- (void)updateData{  sqlite3 *newDB = [self openDB];  sqlite3_stmt *statement = nil;  NSString *sqlStr = @"UPDATE SAAS_PERSON SET DESCRIPTION = '喜歡運(yùn)動(dòng),旅游' WHERE NAME = '周玉'";  int result = sqlite3_prepare_v2(newDB, sqlStr.UTF8String, -1, &statement, NULL);  if (result == SQLITE_OK) {    if (sqlite3_step(statement) == SQLITE_DONE) {      NSLog(@"更新信息完成");    }  } else {    //[logging] no such column: DESCRIPT (KEY拼寫錯(cuò)誤) --- 更新信息不合法    NSLog(@"更新信息不合法");  }  sqlite3_finalize(statement);  [self closeDB];}#pragma mark 新增數(shù)據(jù)記錄- (void)insertData{  sqlite3 *newDB = [self openDB];  sqlite3_stmt *statement = nil;//  NSString *sqlStr = @"INSERT INTO SAAS_PERSON (NAME , SEX , AGE , DESCRIPTION) VALUES('周玉','男',28,'開(kāi)朗樂(lè)觀')";//  NSString *sqlStr = @"INSERT INTO SAAS_PERSON (NAME , SEX , AGE , DESCRIPTION) VALUES('王鵬飛','女',27,'開(kāi)朗樂(lè)觀')";//  NSString *sqlStr = @"INSERT INTO SAAS_PERSON (NAME , SEX , AGE , DESCRIPTION) VALUES('李梅','女',20,'年輕可愛(ài)��')";  NSString *sqlStr = @"INSERT INTO SAAS_PERSON (NAME , SEX , AGE , DESCRIPTION) VALUES('sumit','男',15,'小朋友的年紀(jì)')";  //檢驗(yàn)合法性  int result = sqlite3_prepare_v2(newDB, sqlStr.UTF8String, -1, &statement, NULL);  if (result == SQLITE_OK) {    //判斷語(yǔ)句執(zhí)行完畢    if (sqlite3_step(statement) == SQLITE_DONE) {      NSLog(@"插入的信息完成");    }  } else {    NSLog(@"插入的信息不合法");  }  sqlite3_finalize(statement);  [self closeDB];}#pragma mark 創(chuàng)建表- (void)createTable{  sqlite3 *newDB = [self openDB];  //  char *sql = "CREATE TABLE IF NOT EXISTS t_person (ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, NAME TEXT , SEX TEXT,AGE INTEGER,DESCRIPTION TEXT);";  NSString *sqlStr = @"CREATE TABLE IF NOT EXISTS SAAS_PERSON (ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, NAME TEXT , SEX TEXT,AGE INTEGER,DESCRIPTION TEXT);";  char *error = NULL;  int result = sqlite3_exec(newDB, sqlStr.UTF8String, NULL, NULL, &error);  if (result == SQLITE_OK) {    NSLog(@"創(chuàng)建表成功");  } else {    NSLog(@"創(chuàng)建表失敗 = %s",error);  }  [self closeDB];}#pragma mark 刪除表- (void)dropTable{  sqlite3 *newDB = [self openDB];  NSString *sqlStr = @"DROP TABLE t_person";  char *error = NULL;  int result = sqlite3_exec(newDB, sqlStr.UTF8String, NULL, NULL, &error);  if (result == SQLITE_OK) {    NSLog(@"刪除表成功");  } else {    NSLog(@"刪除表失敗 = %s",error);  }  [self closeDB];}#pragma mark 打開(kāi)或者創(chuàng)建數(shù)據(jù)庫(kù)- (sqlite3 *)openDB {  if (!db) {    //1.獲取document文件夾的路徑    //參數(shù)1:文件夾的名字 參數(shù)2:查找域 參數(shù)3:是否使用絕對(duì)路徑    NSString *documentPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0];    //獲取數(shù)據(jù)庫(kù)文件的路徑    NSString *dbPath = [documentPath stringByAppendingPathComponent:@"saas.sqlite"];    NSLog(@"%@",dbPath);    //判斷document中是否有sqlite文件    int result = sqlite3_open([dbPath UTF8String], &db);    if (result == SQLITE_OK) {      NSLog(@"打開(kāi)數(shù)據(jù)庫(kù)");    }else{      [self closeDB];      NSLog(@"打開(kāi)數(shù)據(jù)庫(kù)失敗");    }  }  return db;}#pragma mark 關(guān)閉數(shù)據(jù)庫(kù)- (void)closeDB{  int result = sqlite3_close(db);  if (result == SQLITE_OK) {    NSLog(@"數(shù)據(jù)庫(kù)關(guān)閉成功");    db = nil;  } else {    NSLog(@"數(shù)據(jù)庫(kù)關(guān)閉失敗");  }}@end