SQLite代码实现

sqlite是纯C语言中底层的数据库，在OC和Swift中都是经常使用的数据库，在开发中，可以使用代码创建数据库，可以使用图形化界面创建数据库。例如SQLiteManager、SQLiteStudio等

对常用的一些方法进行解释如下：

OpaquePointer: *db,数据库句柄，跟文件句柄FIFL类似，这里是sqlite3指针；

sqlite3_stmt: *stmt,相当于ODBC的Command对象，用于保存编译好的SQL语句；

sqlite3_open(): 打开数据库，没有数据库时创建；

sqlite3_exec(): 执行非查询的SQL语句；

sqlite3_step(): 在调用sqlite3_prepare后，使用这个函数在记录集中移动；

sqlite3_close()：关闭数据库文件；

sqlite3_column_text()：取text类型的数据；

sqlite3_column_blob()：取blob类型的数据；

sqlite3_column_int()：取int类型的数据；

加入libsqlite3.tdb动态库

import UIKit

class Person: NSObject {

    var name:String?
    var password:String?
    var email:String?
    var age:Int?
}

(1)打开数据库

class SQLiteTool: NSObject {

    static let shareInstance = SQLiteTool()

    var db: COpaquePointer = nil

    override init() {
        super.init()
        // 1. 创建一个数据库
        // 打开一个指定的数据库, 如果数据库不存在 , 就创建 存在, 就直接打开, 并且, 赋值给参数2
        // 参数1: 数据库路径
        // 参数2: 一个已经打开的数据库(如果后期要执行sql语句, 都需要借助这个对象)
        // 关于sqlite 数据库文件的后缀名, 没有要求, abc, 123
        // sqlite db db3
        let path = "/Users/xiaomage/Desktop/dataBase/demo.sqlite"

        if  sqlite3_open(path, &db) == SQLITE_OK {
            print("执行成功")
            createTable()
        }else {
            print("执行失败")
        }


    }
}

(2)关闭数据库

//关闭数据库
deinit {
    self.closeDB()
}
func closeDB() -> Void {
    sqlite3_close(db)
}

(3)创建表

//创建表
func creatTable() -> Bool {
//sql语句
let sql = "CREATE TABLE UserTable(id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,username TEXT NOT NULL, password TEXT NOT NULL,email TEXT NOT NULL,age INTEGER)"

//执行sql语句
// 参数1: 已经打开的数据库
// 参数2: 需要执行的sql字符串
// 参数3: 执行回调
// 参数4: 参数3 参数1
// 参数5: 错误信息
let excuResult = sqlite3_exec(db, sql.cString(using: .utf8), nil, nil, nil)

//判断是否执行成功
if excuResult != SQLITE_OK {
     return false
   }
     return true
}

(4)插入数据

//插入数据
func addUser(user:Person) -> Bool {

    //sql语句
    let sqlStr = "insert into t_student (name, age, score) values ('\(name!)',\(age),\(score))"

    if sqlite3_exec(db!, sqlStr, nil, nil, nil) == SQLITE_OK {
        success(true)
    } else {
        success(false)
    }
}

(4.1)绑定插入

func bindInsert() -> () {
    let sql = "insert into t_stu(name, age, score) values (?, ?, ?)"
    // 根据sql字符串, 创建, 准备语句
    // 参数1: 一个已经打开的数据库
    // 参数2: sql 字符串 "123234324"
    // 参数3: 取出字符串的长度 "2"  -1 : 代表自动计算
    // 参数4: 预处理语句
    // 参数5: 根据参数3的长度, 取出参数2的值以后, 剩余的参数
    var db = SQLiteTool.shareInstance.db
    var stmt: COpaquePointer? = nil
    guard sqlite3_prepare_v2(db, sql, -1, &stmt, nil) == SQLITE_OK else{
        print("预处理失败")
        return
    }

    // 2. 绑定参数
    // 参数1: 准备语句
    // 参数2: 绑定值的索引 索引从1
    // 惨数3: 需要绑定的值
    sqlite3_bind_int(stmt, 2, 20)
    sqlite3_bind_double(stmt, 3, 59.9)

    // 绑定文本(姓名)
    // 参数1: 准备语句
    // 参数2: 绑定的索引 1
    // 参数3: 绑定的值 "123"
    // 参数4: 值取出多少长度 -1 , 取出所有
    // 参数5: 值的处理方式 
    // SQLITE_STATIC : 人为参数是一个常量, 不会被释放, 处理方案: 不做任何的引用
    // SQLITE_TRANSIENT: 会对参数, 进行一个引用

    let SQLITE_TRANSIENT = unsafeBitCast(-1, to:sqlite3_destructor_type.self)
    sqlite3_bind_text(stmt, 1, "zhangsan2", -1, SQLITE_TRANSIENT)

    // 3. 执行sql语句, 准备语句
    if sqlite3_step(stmt) == SQLITE_DONE {
        print("执行成功")
    }

    // 4. 重置语句
    sqlite3_reset(stmt)

    // 5. 释放准备语句
    sqlite3_finalize(stmt)
}

优化方案: 如果使用 sqlite3_exec 或者, sqlite3_step()来执行sql语句, 会自动开启一个"事务", 然后, 自动提交"事务"

最佳方案: 只需要手动开启事务, 手动提交事务, 这时候, 函数内部, 就不会自动开启 和提交事务，具体实现请看4.2分解绑定

4.2)分解绑定

func fenjieBind() -> () {

    let sql = "insert into t_stu(name, age, score) values (?, ?, ?)"
    var db = SQLiteTool.shareInstance.db
    var stmt: COpaquePointer? = nil
    if sqlite3_prepare_v2(db, sql, -1, &stmt, nil) != SQLITE_OK {
        print("预处理失败")
        return
    }

    // 手动开启事务
    beginTransaction()

    for i in 0..<100000 {
        //  绑定参数
        let value = Int32(i)
        sqlite3_bind_int(stmt, 2, value)
        sqlite3_bind_double(stmt, 3, 59.9)

        // 绑定文本(姓名)
        // SQLITE_STATIC : 人为参数是一个常量, 不会被释放, 处理方案: 不做任何的引用
        // SQLITE_TRANSIENT: 会对参数, 进行一个引用

        let SQLITE_TRANSIENT = unsafeBitCast(-1, sqlite3_destructor_type.self)
        sqlite3_bind_text(stmt, 1, "zhangsan2", -1, SQLITE_TRANSIENT)

        // 3. 执行sql语句, 准备语句
        if sqlite3_step(stmt) == SQLITE_DONE {
//                print("执行成功")
        }

        // 4. 重置语句
        sqlite3_reset(stmt)
    }

    // 提交事务
    commitTransaction()

    // 5. 释放准备语句
    sqlite3_finalize(stmt)

}

func transaction(sql: String) -> Bool {

    return (sqlite3_exec(db, sql, nil , nil, nil) == SQLITE_OK)
}

func beginTransaction() -> () {
    let sql = "begin transaction"
    transaction(sql)
}

func commitTransaction() -> () {
    let sql = "commit transaction"
    transaction(sql)
}

以上做法可以在很短的时间内完成复杂的数据库操作

(4.3)事务

事务（Transaction）是并发控制的单位，是用户定义的一个操作序列。这些操作要么都做，要么都不做，是一个不可分割的工作单位。通过事务，可以将逻辑相关的一组操作绑定在一起，保持数据的完整性。

事务通常是以BEGIN TRANSACTION开始，以COMMIT TRANSACTION或ROLLBACK TRANSACTION结束。

• COMMIT表示提交，即提交事务的所有操作。具体地说就是将事务中所有对数据库的更新写回到磁盘上的物理数据库中去，事务正常结束。
• ROLLBACK表示回滚，即在事务运行的过程中发生了某种故障，事务不能继续进行，系统将事务中对数据库的所有以完成的操作全部撤消，滚回到事务开始的状态。

SQLiteTool.shareInstance.beginTransaction()

// 张三-10
let result1 = Student.update("update t_stu set money = money - 10 where name = 'zhangsan'")

// 李四 + 10
let result2 = Student.update("update t_stu set money = money + 10 where name = 'lisi'")

if result1 && result2 {
    //只有在result1和result2都执行成功的时候提交
    let sql = "commit transaction"
    execute(sql)
}else {
    //否则回滚，就当前面的没执行过
    let sql = "rollback transaction"
    execute(sql)
}

(5)查询数据

方案一

func queryAll() -> () {
    let sql = "select * from t_stu"

    let db:COpaquePointer? = nil

    // 参数1: 一个打开的数据库
    // 参数2: sql语句
    // 参数3: 回调代码块
        // 参数1 传递过来的值
        // 参数2 列的个数
        // 参数3 值的数组
        // 参数4: 列名称的数组
        // 返回值: 0, 继续查询 1: 终止查询
    // 参数4: 传递到参数3里面的第一个参数
    // 参数5: 错误信息
    // char *
  let result = sqlite3_exec(db, sql, { (
    firstValue, columnCount, values , columnNames ) -> Int32 in

    let count = Int(columnCount)
    for i in 0..<count {
        // 列的名称
        let columnName = columnNames[i]
        let columnNameStr = String(CString: columnName, encoding: NSUTF8StringEncoding)
        // 值
        let value = values[i]
        let valueStr = String(CString: value, encoding: NSUTF8StringEncoding)

        print(columnNameStr, valueStr)
    }

    return 1

    }, nil, nil)

    if result == SQLITE_ABORT {
        print("查询成功")
    }else {
        print("查询失败")
    }


}

方案二

func queryAllStmt() {

    // 准备语句 历程
    let sql = "select * from t_stu;"
    // 1. 创建 "准备语句"
    // 参数1: 打开的数据库
    // 参数2: sql字符串
    // 参数3: 字符串, 取的长度 -1代表, 取出所有
    // 参数4: 准备语句的指针
    // 参数5: 剩余的sql字符串
    let db = SQLiteTool.shareInstance.db
    var stmt: COpaquePointer? = nil
    if sqlite3_prepare_v2(db, sql, -1, &stmt, nil) != SQLITE_OK {
        print("预处理失败")
        return
    }

    // 2. 绑定参数(这一步可以省略)

    // 3. 执行"准备语句"
    // sqlite3_step . 作用, 执行DQL, 语句时, 会把, 执行得到的结果, 放到"准备语句"stmt里面

    while sqlite3_step(stmt) == SQLITE_ROW {

        // 读取结果
        // 从准备语句里面进行读取
        // 1. 计算预处理语句里面得到的结果是多少列
        let count = sqlite3_column_count(stmt)
        for i in 0..<count {

            // 2. 取出列的名称
           let columnName = sqlite3_column_name(stmt, i)
           let columnNameStr = String(CString: columnName, encoding: NSUTF8StringEncoding)

            print(columnNameStr)
            // 3. 取出列的值
            // 不同的数字类型, 是通过不同的函数进行获取
            // 3.1 获取这一列的类型
            let type = sqlite3_column_type(stmt, i)
            // 3.2. 根据不同的类型, 使用不同的函数, 获取结果
            if type == SQLITE_INTEGER {
                let value = sqlite3_column_int(stmt, i)
                print(value)
            }
            if type == SQLITE_FLOAT {
                let value = sqlite3_column_double(stmt, i)
                print(value)
            }
            if type == SQLITE_TEXT {
                // UnsafePointer<UInt8>
                let value = sqlite3_column_text(stmt, i)
                let valueInt8 = UnsafePointer<CChar>(value)
                //
                let valueStr = String(CString: valueInt8, encoding: NSUTF8StringEncoding)
                print(valueStr)
            }

        }
    }

    // 4. 重置"准备语句"(这一步可以省略)

    // 5. 释放"准备语句"
    sqlite3_finalize(stmt)

}

(6)更新数据

//更新数据
func updateUser(name:String,toName:String) -> Bool {

    //更新sql语句
    let sql = "update UserTable set username = '\(toName)' where username = '\(name)'";

    //sqlite3_stmt指针
    var stmt:OpaquePointer? = nil
    let cSql = sql.cString(using: .utf8)

    //编译sql
    let prepare_result = sqlite3_prepare_v2(self.db, cSql!, -1, &stmt, nil)

    //判断如果失败，获取失败信息
    if prepare_result != SQLITE_OK {
        sqlite3_finalize(stmt)
        if (sqlite3_errmsg(self.db)) != nil {
            let msg = "SQLiteDB - failed to prepare SQL:\(sql)"
            print(msg)
        }
        return false
    }

    //step执行
    let step_result = sqlite3_step(stmt)

    //判断执行结果，如果失败，获取失败信息
    if step_result != SQLITE_OK && step_result != SQLITE_DONE {
        sqlite3_finalize(stmt)
        if (sqlite3_errmsg(self.db)) != nil {
            let msg = "SQLiteDB - failed to execute SQL:\(sql)"
            print(msg)
        }
        return false
    }

    //finalize
    sqlite3_finalize(stmt)

    return true
}

(7)删除数据

//删除数据
func deleteUser(username:String) -> Bool {

    //删除sql语句
    let sql = "delete from UserTable where username = '\(username)'";

    //sqlite3_stmt指针
    var stmt:OpaquePointer? = nil
    let cSql = sql.cString(using: .utf8)

    //编译sql
    let prepare_result = sqlite3_prepare_v2(self.db, cSql!, -1, &stmt, nil)

    //判断如果失败，获取失败信息
    if prepare_result != SQLITE_OK {
        sqlite3_finalize(stmt)
        if (sqlite3_errmsg(self.db)) != nil {
            let msg = "SQLiteDB - failed to prepare SQL:\(sql)"
            print(msg)
        }
        return false
    }

    //step执行
    let step_result = sqlite3_step(stmt)

    //判断执行结果，如果失败，获取失败信息
    if step_result != SQLITE_OK && step_result != SQLITE_DONE {
        sqlite3_finalize(stmt)
        if (sqlite3_errmsg(self.db)) != nil {
            let msg = "SQLiteDB - failed to execute SQL:\(sql)"
            print(msg)
        }
        return false
    }

    //finalize
    sqlite3_finalize(stmt)

    return true
}

(8)复制数据库路径

//将Bundle.main路径下的数据库文件复制到Documents下
class func loadDBPath() -> String {

    //声明一个Documents下的路径
    let dbPath = NSHomeDirectory() + "/Documents/RWDataTest.db"

    //判断数据库文件是否存在
    if !FileManager.default.fileExists(atPath: dbPath) {

        //获取安装包内是否存在
        let bundleDBPath = Bundle.main.path(forResource: "RWDataTest", ofType:"db")!

        //将安装包内的数据库到Documents目录下
        do {
            try FileManager.default.copyItem(atPath: bundleDBPath, toPath: dbPath)
        } catch let error as NSError {
            print(error)
        }
    }

    return dbPath
}