How to use iOS native sqlite3

// 
// sqlite3
//
// Created by Zhou Yu on 2017/12/13.// Copyright © 2017 guidekj. All rights reserved.//
#import ""
#import <>
#define KUIScreenWidth [UIScreen mainScreen].
#define KUIScreenHeight [UIScreen mainScreen].
#define FILE_NAME @""
static sqlite3 *db = nil;
@interface ViewController ()
@end

@implementation ViewController

- (void)viewDidLoad {
  [super viewDidLoad];
   = @"sqlite3";

  //DDB
//  [self openDB];
//  [self closeDB];

  //DDL
//  [self createTable];
//  [self dropTable];

  //DML
//  [self insertData];
//  [self updateData];
//  [self deleteData];

  //DQL
  [self queryData];
}

#pragma mark query- (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) {
    //Travel the query results    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(@"Query statement completed");
    }
  } else {
    NSLog(@"The query statement is illegal");
  }
  sqlite3_finalize(statement);
  [self closeDB];
}

#pragma mark Delete data records- (void)deleteData{
  sqlite3 *newDB = [self openDB];
  sqlite3_stmt *statement = nil;
  NSString *sqlStr = @"DELETE FROM SAAS_PERSON WHERE NAME = 'Wang Pengfei'";
  int result = sqlite3_prepare_v2(newDB, sqlStr.UTF8String, -1, &statement, NULL);
  if (result == SQLITE_OK) {
    if (sqlite3_step(statement) == SQLITE_DONE) {
      NSLog(@"Delete operation completed");
    }
  } else {
    NSLog(@"Delete operation is illegal");
  }
  sqlite3_finalize(statement);
  [self closeDB];
}

#pragma mark Update data records- (void)updateData{
  sqlite3 *newDB = [self openDB];
  sqlite3_stmt *statement = nil;
  NSString *sqlStr = @"UPDATE SAAS_PERSON SET DESCRIPTION = 'Like sports, travel' WHERE NAME = 'Zhou Yu'";
  int result = sqlite3_prepare_v2(newDB, sqlStr.UTF8String, -1, &statement, NULL);
  if (result == SQLITE_OK) {
    if (sqlite3_step(statement) == SQLITE_DONE) {
      NSLog(@"Update information is completed");
    }
  } else {
    //[logging] no such column: DESCRIPT (KEY spelling error) --- Update information is illegal    NSLog(@"Update information is illegal");
  }
  sqlite3_finalize(statement);
  [self closeDB];
}

#pragma mark Add new data record- (void)insertData{
  sqlite3 *newDB = [self openDB];
  sqlite3_stmt *statement = nil;
// NSString *sqlStr = @"INSERT INTO SAAS_PERSON (NAME , SEX , AGE , DESCRIPTION) VALUES('Zhou Yu','Male',28,'Cheerful and optimistic')";// NSString *sqlStr = @"INSERT INTO SAAS_PERSON (NAME , SEX , AGE , DESCRIPTION) VALUES('Wang Pengfei','female',27,'Cheerful and optimistic')";// NSString *sqlStr = @"INSERT INTO SAAS_PERSON (NAME , SEX , AGE , DESCRIPTION) VALUES('Li Mei','female',20,'Young and cute��')";  NSString *sqlStr = @"INSERT INTO SAAS_PERSON (NAME , SEX , AGE , DESCRIPTION) VALUES('sumit','male',15,'The age of the child')";
  //Check legality  int result = sqlite3_prepare_v2(newDB, sqlStr.UTF8String, -1, &statement, NULL);
  if (result == SQLITE_OK) {
    //The judgment statement has been executed    if (sqlite3_step(statement) == SQLITE_DONE) {
      NSLog(@"Inserted information is completed");
    }
  } else {
    NSLog(@"Inserted information is illegal");
  }
  sqlite3_finalize(statement);
  [self closeDB];
}

#pragma mark Create table- (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(@"Table creation succeeded");
  } else {
    NSLog(@"Failed to create table = %s",error);
  }
  [self closeDB];
}

#pragma mark Delete table- (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(@"Delete table successfully");
  } else {
    NSLog(@"Failed to delete the table = %s",error);
  }
  [self closeDB];
}

#pragma mark Open or create a database- (sqlite3 *)openDB {
  if (!db) {
    //1. Get the path to the document folder    // Parameter 1: The name of the folder Parameter 2: Find the domain Parameter 3: Whether to use an absolute path    NSString *documentPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0];
    //Get the path to the database file    NSString *dbPath = [documentPath stringByAppendingPathComponent:@""];
    NSLog(@"%@",dbPath);
    //Judge whether there is a sqlite file in the document    int result = sqlite3_open([dbPath UTF8String], &db);
    if (result == SQLITE_OK) {
      NSLog(@"Open Database");

    }else{
      [self closeDB];
      NSLog(@"Domain opening failed");
    }
  }
  return db;
}

#pragma mark Close the database- (void)closeDB{
  int result = sqlite3_close(db);
  if (result == SQLITE_OK) {
    NSLog(@"Database shutdown successfully");
    db = nil;
  } else {
    NSLog(@"Database shutdown failed");
  }
}
@end