IOS 中使用 SQLite

2022-02-26 本文已影响0人越天高

12-(掌握)代码实现SQLite-DDL

1. 创建一个Swift项目
2. 导入系统框架 sqlite3.tbd(sqlite3.dylib)
  在Xcode->build Phases->Link Binary With Libraries
3. 建立桥接文件, 导入头文件sqlite3.h
  1. 新建一个.h 头文件
  2. 设置为桥接文件
4.代码实现
1. 打开数据库

// 1. 打开数据库
        let docDir: String! = NSSearchPathForDirectoriesInDomains(NSSearchPathDirectory.DocumentDirectory, NSSearchPathDomainMask.UserDomainMask, true).first
        // SQlite3数据库文件的扩展名没有一个标准定义，比较流行的选择是.sqlite3、.db、.db3
        let fileName: String = docDir + "/demo.sqlite"
        /**
         *  sqlite3_open 使用这个函数打开一个数据库
         *  参数一: 需要打开的数据库文件路径
         *  参数二: 一个指向SQlite3数据结构的指针, 到时候操作数据库都需要使用这个对象
         *  功能作用: 如果需要打开数据库文件路径不存在, 就会创建该文件;如果存在, 就直接打开; 可通过返回值, 查看是否打开成功
         */
        if sqlite3_open(fileName, &db) != SQLITE_OK {
            print("打开数据库失败")
        }else {
            print("打开数据库成功")
        }

使用打开的数据库, 执行DDL语句, 创建一个数据库表

// 创建SQL语句
        let sql = "CREATE TABLE IF NOT EXISTS t_student (name TEXT, age INTEGER, score text, id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT)"
        // 执行SQL语句
        // 参数一: 数据库
        // 参数二: 需要执行的SQL语句
        // 参数三: 回调结果, 执行完毕之后的回调函数, 如果不需要置为NULL
        // 参数四: 参数三的第一个参数, 刻意通过这个传值给回调函数 如果不需要置为NULL
        // 参数五: 错误信息, 通过传递一个地址, 赋值给外界, 如果不需要置为NULL
        if sqlite3_exec(db, sql, nil, nil, nil) != SQLITE_OK
        {
            print("创建表失败")
        }else
        {
            print("创建表成功")
        }

4.3. 使用打开的数据库, 执行DDL语句, 删除一个数据库表

let sql = "DROP TABLE IF EXISTS t_student2"
        if sqlite3_exec(db, sql, nil, nil, nil) != SQLITE_OK
        {
            print("删除表失败")
        }else
        {
            print("删除表成功")
        }

4.4. 将数据库操作封装成一个工具类

class SqliteTool: NSObject {
    static let shareTool = SqliteTool()
    var db : OpaquePointer? = nil
    override init() {
        super.init()        
        let path = "/Users/luosu/Downloads/11资料/09实用技术学习/练习/sql/demo.sqlite"
        if sqlite3_open(path, &db) == SQLITE_OK
        {
            print("执行成功")
        }
        else
        {
            print("执行失败")
        }
    }
    func createTable(){
        let sql = "create table if not exists t_stu (id integer primary key autoincrement, name text not null, age integer, score real default 60)"
        if execute(sql: sql) {
            print("yes")
        }
    }
    func dropTable()
    {
        let sql = "drop table if exists t_stu"
        if execute(sql: sql) {
            print("yes")
        }
    }
    func execute(sql:String) -> Bool {
        return (sqlite3_exec(db, sql, nil, nil, nil) == SQLITE_OK)
    }
}

13-(掌握)代码实现DML语句-Insert

13.1. 创建一个Student类
属性
name
age
构造方法
init(name: String, age: Int)

class Student: NSObject {
    var name : String = ""
    var age :Int = 0
    var score: Float = 0.0
    init(name:String, age: Int, score:Float) {
        super.init()
        self.name = name
        self.age = age
        self.score = score
    }
    ///添加学生
    class func inertStudent(stu: Student)  {
        let sql = "insert into t_stu(name, age, score) values('\(stu.name)', \(stu.age), \(stu.score))"
        if SqliteTool.shareTool.execute(sql: sql)
        {
            print("添加学生成功")
        }
    }
    ///删除学生
    static func deleteStudent(name:String)
    {
        let sql = "delete from t_stu where name = '\(name)'"
        if SqliteTool.shareTool.execute(sql: sql)
        {
            print("删除学生成功")
        }
        
    }
    class func alterStudent(newStu:Student)
    {
        let sql = "update t_stu set name= '\(newStu.name)' , age= \(newStu.age), score = \(newStu.score) where name = '\(newStu.name)'"
        if SqliteTool.shareTool.execute(sql: sql)
        {
            print("修改学生成功")
        }   
    }
}

13.2. 创建数据库操作方法
数据库中, 对Student对象的操作封装
insertStudent()

14-(了解)代码实现DML语句-Insert绑定参数

准备语句（prepared statement）对象
准备语句（prepared statement）对象一个代表一个简单SQL语句对象的实例，这个对象通常被称为“准备语句”或者“编译好的SQL语句”或者就直接称为“语句”。
操作历程

使用sqlite3_prepare_v2或相关的函数创建这个对象
如果执行成功，则返回SQLITE_OK，否则返回一个错误码
使用sqlite3_bind_*()给宿主参数（host parameters）绑定值
sqlite3_bind_text()
参数1: 准备语句
参数2: 绑定的参数索引 (从1开始)
参数3: 绑定的参数内容
参数4: 绑定的参数长度 (-1代表自动计算长度)
参数5:参数的处理方式
SQLITE_TRANSIENT 会对字符串做一个 copy，SQLite 选择合适的机会释放
SQLITE_STATIC / nil 把它当做全局静态变量, 不会字符串做任何处理，如果字符串被释放，保存到数据库的内容可能不正确！
注意: swift中没有宏的概念
// 替换 sqlite3.h 中的宏
private let SQLITE_TRANSIENT = unsafeBitCast(-1, sqlite3_destructor_type.self)
通过调用sqlite3_step()一次或多次来执行这个sql
对于DML语句, 如果执行成功, 返回SQLITE_DONE
对于DQL语句, 通过多次执行获取结果集, 继续执行的条件是返回值 SQLITE_ROW
使用sqlite3_reset()重置这个语句，然后回到第2步，这个过程做0次或多次
使用sqlite3_finalize()销毁这个对象, 防止内存泄露


    class func binInserStudent(){
        let sql = "insert into t_stu(name, age, score) values(?, ?, ?)"
        //1.使用sqlite3_prepare_v2或相关的函数创建这个对象
        //参数3去除字符串的长度
        //参数4 预处理的语句
        //参数5，参数3取出之后剩余的参数
        var stmt :OpaquePointer? = nil
        if sqlite3_prepare_v2(SqliteTool.shareTool.db, sql, -1, &stmt, nil) != SQLITE_OK
        {
            print("创建准备语句失败")
        }
        
        //2.使用sqlite3_bind_*()给宿主参数（host parameters）绑定值
        //参数1 准备语句
        //参数2 绑定值的索引
        //参数3，需要绑定的值
        sqlite3_bind_int(stmt, 2, 33)
        sqlite3_bind_double(stmt, 3, 67.5)
        let SQLITE_TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self)
        sqlite3_bind_text(stmt, 1, "lilei", -1, SQLITE_TRANSIENT)
        //3.通过调用`sqlite3_step() `一次或多次来执行这个sql
        if sqlite3_step(stmt) == SQLITE_DONE {
            print("执行成功")
        }
        //4.使用sqlite3_reset()重置这个语句，然后回到第2步
        sqlite3_reset(stmt)
        //5.使用sqlite3_finalize()销毁这个对象, 防止内存泄露
        sqlite3_finalize(stmt)
    }

上面这种方式比原始的效率更高，但是把第一步和第五步分解出来，循环中间的3步

优化方案，如果使用的是sqlite3_exec或者是sqlite3——step这种方式执行语句，他会自动开启一个事务，然后自动提交这个事务
解决方案：我们要手动开始和关闭事务，这时候函数内部就不会自动开始和提交事务
经过计算可得普通的插入1000条数据大概16是，bind虚幻2、3、4步大概4秒，而手动开启和关闭事务之需要0.02秒


    class func binInserStudent(){
        let sql = "insert into t_stu(name, age, score) values(?, ?, ?)"
        //1.使用sqlite3_prepare_v2或相关的函数创建这个对象
        //参数3去除字符串的长度
        //参数4 预处理的语句
        //参数5，参数3取出之后剩余的参数
        var stmt :OpaquePointer? = nil
        if sqlite3_prepare_v2(SqliteTool.shareTool.db, sql, -1, &stmt, nil) != SQLITE_OK
        {
            print("创建准备语句失败")
        }
        SqliteTool.shareTool.beginTransaction()
        for _ in 0..<1000
        {
            //2.使用sqlite3_bind_*()给宿主参数（host parameters）绑定值
            //参数1 准备语句
            //参数2 绑定值的索引
            //参数3，需要绑定的值
            sqlite3_bind_int(stmt, 2, 33)
            sqlite3_bind_double(stmt, 3, 67.5)
            let SQLITE_TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self)
            sqlite3_bind_text(stmt, 1, "lilei", -1, SQLITE_TRANSIENT)
            //3.通过调用sqlite3_step() 一次或多次来执行这个sql
            if sqlite3_step(stmt) == SQLITE_DONE {
                print("执行成功")
            }
            //4.使用sqlite3_reset()重置这个语句，然后回到第2步
            sqlite3_reset(stmt)
        }
        SqliteTool.shareTool.commitTransaction()
        //5.使用sqlite3_finalize()销毁这个对象, 防止内存泄露
        sqlite3_finalize(stmt)
    }

15-(掌握代码实现)DML语句-Insert插入数据优化

测试方式
循环插入10000条数据, 查看耗时
测试步骤
sqlite_exec 直接执行
未拆解"准备语句" 执行
拆解后的"准备语句" 执行
手动开启事务后:
sqlite_exec 直接执行
拆解后的"准备语句" 执行
CFAbsoluteTimeGetCurrent()获取当前时间对应的秒数
测试结果
1. 插入10000条数据, sqlite_exec 直接执行, 未拆解"准备语句" 执行, 拆解后的"准备语句" 执行, 分别耗时
sqlite_exec 直接执行:5.8080689907074
未拆解"准备语句" 执行:5.93309998512268
拆解后的"准备语句" 执行:4.91254101991653
结果分析
sqlite_exec 直接执行和未拆解"准备语句" 执行执行时间平均差不多
sqlite_exec 函数是对"准备语句"的封装 (预处理语句->绑定参数->执行语句->重置语句->释放语句)
拆解后的"准备语句" 执行, 效率明显高了一些
主要原因是真正遵循了"准备语句"的操作流程
虽然按步骤使用"准备语句", 但是执行效率,依然非常低, 达到了5秒左右, 不可原谅
原因分析
每当SQL调用执行方法执行一个语句时, 都会开启一个叫做"事务"的东西, 执行完毕之后再提交"事务";
也就是说, 如果执行了10000次SQL语句, 就打开和提交了10000次"事务", 所以造成耗时严重
解决方案
只要在执行多个SQL语句之前, 手动开启事务, 在执行完毕之后, 手动提交事务, 这样再调用SQL方法执行语句时, 就不会再自动开启和提交事务
优化后结果
插入10000条数据, 大概耗时0.07秒
得出结论

如果插入大量数据, 请务必手动开启/提交事务
根据不同情况, 选择使用sqlite3_exec或者 "准备语句"
单条语句, 前者, 因为使用简单
大批量插入, 选择后者

16-(掌握)代码实现-事务

概念
事务（Transaction）是并发控制的单位，是用户定义的一个操作序列。这些操作要么都做，要么都不做，是一个不可分割的工作单位。通过事务，可以将逻辑相关的一组操作绑定在一起，保持数据的完整性。
事务通常是以BEGIN TRANSACTION开始，以COMMIT TRANSACTION或ROLLBACK TRANSACTION结束。
COMMIT表示提交，即提交事务的所有操作。具体地说就是将事务中所有对数据库的更新写回到磁盘上的物理数据库中去，事务正常结束。
ROLLBACK表示回滚，即在事务运行的过程中发生了某种故障，事务不能继续进行，系统将事务中对数据库的所有以完成的操作全部撤消，滚回到事务开始的状态。
测试
修改两条记录, 一个成功, 一个失败测试

             XMGSQLTool.shareInstance.beginTransaction()
                   let result1 = Student.updateStudent("score = score - 10", condition: "name = 'zs'")
                   let result2 = Student.updateStudent("score1 = score + 10", condition: "name = 'wex'")
                    // 如果都执行成功再提交, 如果都不成功, 那就回滚
                    if result1 && result2
                    {
            XMGSQLTool.shareInstance.commitTransaction()
                    }else
                    {
            XMGSQLTool.shareInstance.rollBackTransaction()
                    }
//tool
 func beginTransaction()
    {
        let sql = "begin transaction"
        if execute(sql: sql) {
            print("事务开起成功")
        }
    }
    func commitTransaction()
    {
      let sql = "commit transaction"
        if execute(sql: sql) {
            print("事务提交成功")
        }
    }
    func rollBackTransaction()
    {
        let sql = "rollback transaction"
        if execute(sql: sql) {
            print("事务回转")
        }
    }

17-(掌握)代码实现DQL语句

sql返回结果会产生一个集合，所有他会执行返回代码块参数

方式1: sqlite3_exec
作用: 可以通过回调来获取结果, 步骤相对来说简单, 结果数据类型没有特定类型(id)
这里返回的数据没有类型，都是按照字符串来进行输出的
参数说明
// 参数1: 一个打开的数据库
// 参数2: 需要执行的SQL语句
// 参数3: 查询结果回调(执行0次或多次)
参数1: 参数4的值
参数2: 列的个数
参数3: 结果值的数组
参数4: 所有列的名称数组
返回值: 0代表继续执行一致到结束, 1代表执行一次
// 参数4: 回调函数的第一个值
// 参数5: 错误信息
demo

 class func queryAll()
    {
        let sql = "select * from t_stu"
        let db = SqliteTool.shareTool.db
        //参数1 要打开的数据库
        //参数2 要执行的语句
        //参数3，callback
              //参数1:传递过来的
             //参数2 ：列的个数
             //参数3:值的数组
             //参数4:列名称数组
       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!)
                let columnStr = String(cString: columnName!, encoding: String.Encoding.utf8)
                let value = values![i]
                let valueStr = String(cString: value!)
                print(columnNameStr, valueStr)
            }
            
            return 0
        }, nil, nil)
        print(result)
        if result == SQLITE_OK {
            print("查询学生信息成功")
        }
        else
        {
            print("查询学生信息失败")
        }
    }

方式2: 通过"准备语句"
作用: 可以处理不同特定类型, 步骤相对来说复杂
步骤:
2.1. 预处理函数获取"准备语句"
sqlite3_prepare
2.2. 不断执行"准备语句", 直到无结果集
while sqlite3_step(stmt) == SQLITE_ROW
2.3. 获取列的类型
sqlite3_column_type
2.4. 根据每列的类型取出不同的值

sqlite3_column_int64
SQLITE_INTEGER
sqlite3_column_double
SQLITE_FLOAT
sqlite3_column_text
SQLITE3_TEXT
需要转换下字符串
let cText =  UnsafePointer<Int8>(sqlite3_column_text(stmt, col))
let text = String(CString: cText, encoding: NSUTF8StringEncoding)
NSNull()
SQLITE_NULL

2.5. 释放资源
sqlite3_finalize`
demo

class func queryAllStmt(){
        //1.创建stmt语句
        let sql = "select * from t_stu"
        //参数1：查询的数据哭
        //参数2:执行的sql语句
        //参数3:对参数的处理-1默认自定哦
        //参数4:生成stmt语句地址
        //参数5:参数3 设置完参数的剩余参数
        var stmt : OpaquePointer? = nil
        if sqlite3_prepare_v2(SqliteTool.shareTool.db, sql, -1, &stmt, nil) != SQLITE_OK
        {
            print("准备语句失败")
        }
        //2.绑定类型(如果用不到，可以不写)
        //3. 执行step
        while sqlite3_step(stmt) == SQLITE_ROW // 表示还有其他的行
        {
            //1.计算预处理语句里面获取的结果一共列的数量
            let columnCount = sqlite3_column_count(stmt)
            for i in 0..<columnCount {
                // 2.取出列的名称
                let columnName = sqlite3_column_name(stmt, i)
                let columnNameStr = String(cString: columnName!, encoding: .utf8)
                print(columnNameStr!)
                //3.取出列的值
                //不同的数据类型，需要执行不同函数来获取
                //3.1获取每列的数据类型
                let columnType = sqlite3_column_type(stmt, i)
                //3.2 根据不同的数据类型，使用不同的函数，获取结果
                if columnType == SQLITE_INTEGER
                {
                    let value = sqlite3_column_int(stmt, i)
                    print(value)
                }
                if columnType == SQLITE_FLOAT {
                    let value = sqlite3_column_double(stmt, i)
                    print(value)
                }
                if columnType == SQLITE_TEXT
                {
                    let value = sqlite3_column_text(stmt, i)
                    //let valueInt8 = UnsafePointer<CChar>(value)
                    let valueStr = String(cString: value!)
                    print(valueStr)
                }
                
            }
        }
        //4.重置语句
        sqlite3_reset(stmt)
        //5.释放资源
        sqlite3_finalize(stmt)        
    }

使用预处理语句来操作可以方便拿到对应列的类型，比如插入的时候，我们可以绑定二进制的数据，但是如果是sql语句直接执行，无法绑定二进制数据，因为他默认会当成字符串来处理。