柚子快報激活碼778899分享：swift SQLite自封裝
柚子快報激活碼778899分享：swift SQLite自封裝
1.數(shù)據(jù)庫封裝類MySQLiteManager
import Foundation
import SQLite3
//數(shù)據(jù)庫封裝類
class MySQLiteManager {
private var db: OpaquePointer!
//重寫構(gòu)造函數(shù)
init(dbPath: String) {
let isSuccess = openDB(dbPath: dbPath)
if isSuccess {
print("打開數(shù)據(jù)庫成功")
}else {
print("打開數(shù)據(jù)庫失敗")
}
}
//打開數(shù)據(jù)庫
func openDB(dbPath: String) -> Bool {
let result = sqlite3_open(dbPath, &db)
if result != SQLITE_OK { return false }
return true
}
//關(guān)閉數(shù)據(jù)庫
func closeDB() {
sqlite3_close(db)
}
deinit {
sqlite3_close(db)
}
}
extension MySQLiteManager {
func createTable(sql: String) -> Bool {
guard sql.contains("create") else { return false }
return execSQL(sql: sql)
}
func dropTable(sql: String) -> Bool {
guard sql.contains("drop") else { return false }
return execSQL(sql: sql)
}
}
extension MySQLiteManager {
//MARK: - 插入
func insert(sql: String) -> Bool {
guard sql.contains("insert") else { return false }
return execSQL(sql: sql)
}
//MARK: - 刪除
func delete(sql: String) -> Bool {
guard sql.contains("delete") else { return false }
return execSQL(sql: sql)
}
//MARK: - 修改
func update(sql: String) -> Bool {
guard sql.contains("update") else { return false }
return execSQL(sql: sql)
}
func execSQL(sql: String) -> Bool {
var errMsg: UnsafeMutablePointer
let cSql = sql.cString(using: String.Encoding.utf8)!
if sqlite3_exec(db, cSql, nil, nil, &errMsg) == SQLITE_OK {
return true
}
let msg = String.init(cString: errMsg!)
print(msg)
return false
}
//MARK: - 查詢
func query(sql: String) -> [[String: Any]]? {
guard sql.contains("select") else {
print("sql語句有誤")
return nil
}
let cSql = sql.cString(using: String.Encoding.utf8)!
var statement: OpaquePointer? = nil
if sqlite3_prepare_v2(db, cSql, -1, &statement, nil) != SQLITE_OK {
sqlite3_finalize(statement)
print("執(zhí)行\(zhòng)(sql)錯誤\n")
let errmsg = sqlite3_errmsg(db)
if errmsg != nil {
print(errmsg!)
}
return nil
}
var rows = [[String: Any]]()
while sqlite3_step(statement) == SQLITE_ROW {
rows.append(record(statement: statement!))
}
sqlite3_finalize(statement)
return rows
}
private func record(statement: OpaquePointer) -> [String: Any] {
var row = [String: Any]()
for col in 0 ..< sqlite3_column_count(statement) {
let cName = sqlite3_column_name(statement, col)
let name = String(cString: cName!, encoding: String.Encoding.utf8)
var value: Any?
switch (sqlite3_column_type(statement, col))
{
case SQLITE_FLOAT:
value = sqlite3_column_double(statement, col) as Any
case SQLITE_INTEGER:
value = Int(sqlite3_column_int(statement, col)) as Any
case SQLITE_TEXT:
let cText = sqlite3_column_text(statement, col)
value = String.init(cString: cText!) as Any
case SQLITE_NULL:
value = nil
default:
print("")
}
row[name!] = value
}
return row
}
//存儲Blob數(shù)據(jù) Blob（Binary Large Object）表示二進制類型的大對象。在數(shù)據(jù)庫管理系統(tǒng)中，將二進制數(shù)據(jù)存儲為一個單一個體的集合。
func execSaveBlob(sql: String, blob: NSData) {
let csql = sql.cString(using: .utf8)!
var statement:OpaquePointer? = nil
if sqlite3_prepare_v2(db, csql, -1, &statement, nil) != SQLITE_OK {
sqlite3_finalize(statement)
print("Prepare error:\(sql)")
return
}
let paramsCnt = sqlite3_bind_parameter_count(statement)
if paramsCnt != 1 {
print("need only 1 parameter:\(sql)")
sqlite3_finalize(statement)
return
}
if sqlite3_bind_blob(statement, 1, blob.bytes, Int32(blob.length), nil) != SQLITE_OK {
print("bind blob error:\(sql)")
sqlite3_finalize(statement)
return
}
let rslt = sqlite3_step(statement)
if rslt != SQLITE_OK && rslt != SQLITE_DONE {
print("extue blob error:\(sql)")
sqlite3_finalize(statement)
return
}
sqlite3_finalize(statement)
return
}
//讀取Blob數(shù)據(jù)
func execLoadBlob(sql:String) -> Data? {
let csql = sql.cString(using: String.Encoding.utf8)!
var statement:OpaquePointer? = nil
if sqlite3_prepare_v2(db, csql, -1, &statement, nil) != SQLITE_OK {
sqlite3_finalize(statement)
print("執(zhí)行\(zhòng)(sql)錯誤\n")
if let errmsg = sqlite3_errmsg(db) {
print(errmsg)
}
return nil
}
while sqlite3_step(statement) == SQLITE_ROW {
if let dataBlob = sqlite3_column_blob(statement, 0) {
let dataBlobLength = sqlite3_column_bytes(statement, 0)
let data = Data(bytes: dataBlob, count: Int(dataBlobLength))
sqlite3_finalize(statement)
return data
}
}
sqlite3_finalize(statement)
return nil
}
}
//MARK: - sql語句
/**
1.DDL
1.創(chuàng)建表
create table if not exists t_student(id integer auto_increment primary key ,name
varchar(3) , age int);
刪除表
drop table if exists t_student;
修改表結(jié)構(gòu)
alter table t_student add address varchar(4);
alter table t_student modify address varchar(6) not null;
tip：
簡單約束：
name varchar(3) not null
age int default 66
主鍵約束：
id integer auto_increment primary key
2. DML
1.插入數(shù)據(jù)
insert into t_student(name,age,address) values ('xiaoM',12,'guangzhou');
2.修改數(shù)據(jù)
update t_student set name='xiaoHuang' where id=1;
3.刪除數(shù)據(jù)
delete from t_student where id= 1 ;
tip：
條件約束
delete from t_student where id is 1 or name = 'xiaoM';
3. DQL
查詢語句
1.查詢所有字段信息
select * from t_student;
2.查詢部分字段信息
select name,age from t_student;
3.查詢符合條件下的部分字段信息
select name,age from t_student where id=2;
4.查詢數(shù)據(jù)個數(shù)
select count(*) from t_student;
5.查詢age不為空值的個數(shù)
select count(age) from t_student;
6.查詢age平均值
select avg(age) from t_student;
7.查詢age總和
select sum(age) from t_student;
8.查詢age最大值
select max(age) from t_student;
9.查詢age最小值
select min(age) from t_student;
10.查詢按分數(shù)升序，按年齡降序
select *from t_student order by score asc, age desc;
*/
2.針對表test寫的類TestTableManager
import Foundation
class TestTableManager {
private var sqliteManager: MySQLiteManager!
static let shared = TestTableManager()
init() {
let dbPath = NSHomeDirectory() + "/Documents/testDB.sqlite"
sqliteManager = MySQLiteManager(dbPath: dbPath)
createTable()
}
private func createTable() {
let sql = "create table if not exists test (id integer auto_increment primary key ,name char(50) not null default '');"
if sqliteManager.createTable(sql: sql) {
print("表創(chuàng)建成功")
}else {
print("表創(chuàng)建失敗")
}
}
func dropTable() {
let sql = "drop table if exists test;"
if sqliteManager.dropTable(sql: sql) {
print("表刪除成功")
}else {
print("表刪除失敗")
}
}
func insert(name: String) {
let sql = "insert into test(name) values ('\(name)');"
let isSuccess = sqliteManager.insert(sql: sql)
if isSuccess {
print("插入成功")
}else {
print("插入失敗")
}
}
func quary() {
let sql = "select * from test;"
let data = sqliteManager.query(sql: sql)
print(data!)
}
}
3.測試代碼
import UIKit
class ViewController: UIViewController {
override func viewDidLoad() {
super.viewDidLoad()
//MARK: - sqlite自封裝
TestTableManager.shared.insert(name: "aaaaa")
TestTableManager.shared.insert(name: "bbbbb")
TestTableManager.shared.quary()
TestTableManager.shared.dropTable()
}
override func viewDidAppear(_ animated: Bool) {
super.viewDidAppear(animated)
}
}
柚子快報激活碼778899分享：swift SQLite自封裝
相關(guān)鏈接
本文內(nèi)容根據(jù)網(wǎng)絡(luò)資料整理，出于傳遞更多信息之目的，不代表金鑰匙跨境贊同其觀點和立場。
轉(zhuǎn)載請注明，如有侵權(quán)，聯(lián)系刪除。
Poshmark海外時尚秀
