iT邦幫忙

2018 iT 邦幫忙鐵人賽
DAY 26
0

上一篇講完了會用到的 SQL 語法,本篇會將前面講過的基本功能獨立出來寫成一個 class ,以便需要時可以重複使用。

首先,在 Swift 中要使用 SQLite 時須額外加入相關的函式庫。

import SQLite3

建立一個型態為 OpaquePointer 的變數用於與資料庫連線。

class Sqlite {
  var dbPtr: OpaquePointer? = nil
  ...
}

透過 class 的 init function 來建立存於裝置內的資料庫檔案,讓初始化此 class 時即可建立或開啟該檔案。

class Sqlite {
  ...
  init?(path: String) {
    sqlitePath = path
    dbPtr = self.openDatabase(path: sqlitePath)
  }
  
  func openDatabase(path: String) -> OpaquePointer {
    var connectdb: OpaquePointer? = nil
    var dbStatus: Int32 = SQLITE_ERROR
    
    dbStatus = sqlite3_open(path, &connectdb)
    if dbStatus != SQLITE_OK {
      print("Unable to open database. Error code:", dbStatus)
    }
    return connectdb!
  }
  ...
}

建立資料庫表單,執行建立表單之 SQL 語法是透過 sqlite3_exec(),該 function 會回傳執行結果,可用來判斷是否成功。

class Sqlite {
  ...
  func createTable(_ tableName: String, columnsInfo: [String]) -> Int32 {
    var dbStatus: Int32 = SQLITE_ERROR
    
    let sqlCmd: String = "create table if not exists \(tableName) "
                         + "(\(columnsInfo.joined(separator: ",")))"
                         
    dbStatus = sqlite3_exec(self.dbPtr, String(sqlCmd), nil, nil, nil)
    if dbStatus == SQLITE_OK {
      print("Create table success.")
    }
    return dbStatus
  }
  ...
}

新增資料至特定表單:

class Sqlite {
  ...
  func insert(_ tableName: String, rowInfo: [String: String]) -> Int32 {
    var statement: OpaquePointer? = nil
    var dbStatus: Int32 = SQLITE_ERROR
    
    let sqlCmd: String = "insert into \(tableName) "
                         + "(\(rowInfo.keys.joined(separator: ","))) "
                         + "values (\(rowInfo.values.joined(separator: ",")))"
    dbStatus = sqlite3_prepare_v2(self.dbPtr, String(sqlCmd), -1, &statement, nil)
    if dbStatus == SQLITE_OK {
      if sqlite3_step(statement) == SQLITE_DONE {
        print("Insert data success.")
        return dbStatus
      }
      sqlite3_finalize(statement)
    }
    return dbStatus
  }
  ...
}

讀取表單資料:

class Sqlite {
  ...
    func fetch(_ tableName: String, cond: String?, sortBy order: String?, offset: Int?) -> OpaquePointer {
    var statement: OpaquePointer? = nil
    var dbStatus: Int32 = SQLITE_ERROR
    
    var sqlCmd: String = "select * from \(tableName)"
    if let condition = cond {
      sqlCmd += " where \(condition)"
    }
    
    if let orderBy = order {
      sqlCmd += " order by \(orderBy)"
    }
    
    sqlCmd += " limit \(rowCount)"
    
    if let offsetNum = offset {
      sqlCmd += " OFFSET \(offsetNum)"
    }
    
    dbStatus = sqlite3_prepare_v2(self.dbPtr, String(sqlCmd), -1, &statement, nil)
    print("Fatch data status:", dbStatus)
    return statement!
  }
  ...  
}

更新資料:

class Sqlite {
  ...
  func update(_ tableName: String, cond: String?, rowInfo: [String: String]) -> Int32 {
    var statement: OpaquePointer? = nil
    var dbStatus: Int32 = SQLITE_ERROR
    
    var sqlCmd: String = "update \(tableName) set "
    
    // row info
    var info: [String] = []
    for (key, value) in rowInfo {
      info.append("\(key) = \(value)")
    }
    sqlCmd += info.joined(separator: ",")
    
    if let condition = cond {
      sqlCmd += " where \(condition)"
    }

    dbStatus = sqlite3_prepare_v2(self.dbPtr, String(sqlCmd), -1, &statement, nil)
    if dbStatus == SQLITE_OK && sqlite3_step(statement) == SQLITE_DONE {
      print("Updata data success.")
      return dbStatus
    }
    sqlite3_finalize(statement)
    return dbStatus
  }
  ...
}

刪除資料:

class Sqlite {
  ...
func delete(_ tableName: String, cond: String?) -> Int32 {
    var statement: OpaquePointer? = nil
    var dbStatus: Int32 = SQLITE_ERROR
    
    var sqlCmd: String = "delete from \(tableName)"
    
    if let condition = cond {
      sqlCmd += " where \(condition)"
    }

    dbStatus = sqlite3_prepare_v2(self.dbPtr, String(sqlCmd), -1, &statement, nil)
    if dbStatus == SQLITE_OK && sqlite3_step(statement) == SQLITE_DONE {
      print("Delete data success.")
      return dbStatus
    }
    sqlite3_finalize(statement)
    return dbStatus
  }
  ...
}

Additional
執行 SQL 語法時可以使用 sqlite3_exec() 或 sqlite3_prepare_v2()。前者其實是sqlite3_prepare_v2() , sqlite3_step() 和 sqlite3_finalize() 的封裝。
sqlite3_prepare_v2() 並不執行 SQL 指令,而是將資料庫轉換到 prepare statement。接下來再透過sqlite3_step() 得到執行 SQL 指令後的結果。而離開 prepare statement 的方式則是呼叫 sqlite3_finalize()。

Reference
SQLite


上一篇
iOS App 實作(19) SQLite(part 1)
下一篇
iOS App 實作(19) SQLite(part 3)
系列文
30天Swift入門學習30

尚未有邦友留言

立即登入留言