swift SQLite自封装

1.���据库封装类MySQLiteManager

import Foundationimport SQLite3//数据库封装类class MySQLiteManager {    private var db: OpaquePointer!        //重写构造函数    init(dbPath: String) {        let isSuccess = openDB(dbPath: dbPath)        if isSuccess {            print("打开数据库成功")        }else {            print("打开数据库失败")        }    }        //打开数据库    func openDB(dbPath: String) -> Bool {        let result = sqlite3_open(dbPath, &db)        if result != SQLITE_OK { return false }        return true    }        //关闭数据库    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? = nil        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("执行\(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 .. 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("执行\(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.创建表  create table if not exists t_student(id integer auto_increment primary key ,name  varchar(3) , age int); 删除表 drop table if exists t_student; 修改表结构 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.插入数据 insert into t_student(name,age,address) values ('xiaoM',12,'guangzhou'); 2.修改数据 update t_student set name='xiaoHuang' where id=1; 3.删除数据 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.查询数据个数 select count(*) from t_student; 5.查询age不为空值的个数 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.查询按分数升序，按年龄降序 select *from t_student order by score asc, age desc; */

2.针对表test写的类TestTableManager

import Foundationclass 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("表创建成功")        }else {            print("表创建失败")        }    }        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 UIKitclass 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)            }}