Swift与数据库之

SQLite的DDL使用和参数绑定

---

SQLiteManager的写法

代码如下：

//
//  SQLiteManager.swift
//  SQLite的DDL使用和参数绑定
//
//  Created by ErEr on 16/3/18.
//  Copyright © 2016年 ErEr. All rights reserved.
//

import UIKit

class SQLiteManager: NSObject {
    
    //1.单例
    static let shareInstance: SQLiteManager = SQLiteManager()
    
    
    //2.重写init方法，方法中创建数据库文件和表
    override init() {
        super.init()
        
        createDataBase()
        createTable()
        
    }
    
    //定义数据库
    var db: COpaquePointer = nil
    
    
    //2.1创建数据库方法
    func createDataBase(){
        
        //路径
        guard let path = NSSearchPathForDirectoriesInDomains(NSSearchPathDirectory.CachesDirectory, NSSearchPathDomainMask.UserDomainMask, true).first else{
            return
        }
        
        print("\(path)")
        
        let filePath = (path as NSString).stringByAppendingPathComponent("datebase.sqlite")
        guard let cFilePath = filePath.cStringUsingEncoding (NSUTF8StringEncoding) else{
            return
        }
        
        sqlite3_open(cFilePath, &db)
    }
    
    //2.2创建表方法，方法中要编写sql语句和执行sql语句
    //这里创建表是通过执行sql创建表
    func createTable(){
        let sql = "CREATE TABLE IF NOT EXISTS T_Student(id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT,age INTEGER);"
        execSQL(sql)
    }
    
    func execSQL(sql: String)->Bool{
        
        if sqlite3_exec(db, sql, nil, nil, nil) != SQLITE_OK{
            return false
        }
        return true
        
    }
    
    
    //3.执行sql语句
    //这里执行sql语句进行相应的增删改查、
    let SQLITE_TRANSIENT = unsafeBitCast(-1, sqlite3_destructor_type.self)
    
    func execSQL(sql: String, args: CVarArgType...)->Bool{
        //1.编写sql语句
        guard let csql = sql.cStringUsingEncoding(NSUTF8StringEncoding) else{
            return false
        }
        
        var stmt: COpaquePointer = nil
        
        //2.对sql进行预编译，检查sql语句是否有错误
        if  sqlite3_prepare_v2(db, csql, -1, &stmt, nil) != SQLITE_OK{
            return false
        }
        
        //3.遍历参数，将参数绑定在sql语句上
        var index: Int32 = 1
        for arg in args{
            if arg is Int{
                let temp = Int32(arg as! Int)
                sqlite3_bind_int(stmt, index, temp)
            }else if arg is Double{
                let temp = Double(arg as! Double)
                sqlite3_bind_double(stmt, index, temp)
            }else if arg is String{
                
                let temp = String(arg as! String)
                guard let cTemp = temp.cStringUsingEncoding(NSUTF8StringEncoding) else{
                    continue
                }
                sqlite3_bind_text(stmt, index, cTemp, -1, SQLITE_TRANSIENT)
            }else{
                print("Other")
            }
            index++
        }
        
        //4.执行sql语句
        if sqlite3_step(stmt) != SQLITE_DONE{
            return false
        }
        //5.关闭
        sqlite3_finalize(stmt)
        
        return true
    }
}

Student.swift中的写法

代码如下：

//
//  Student.swift
//  SQLite的DDL使用和参数绑定
//
//  Created by ErEr on 16/3/18.
//  Copyright © 2016年 ErEr. All rights reserved.
//

import UIKit

class Student: NSObject {
    
    var name: String
    var age: Int
    
    init(name: String, age: Int) {
        self.name = name
        self.age = age
    }
    
    
    func insertStudent(){
        //1.拿到数据库单例
        let manager = SQLiteManager.shareInstance
        //2.编写SQL插入语句
        let sql = "INSERT INTO T_Student (name, age) VALUES (?, ?);"
        
        //3.执行SQL语句
        manager.execSQL(sql, args: name, age)
    }
    

}

程序使用

import UIKit

class ViewController: UIViewController {

    override func viewDidLoad() {
        super.viewDidLoad()
    
    }
    
    override func touchesBegan(touches: Set<UITouch>, withEvent event: UIEvent?) {
        let stu1 = Student.init(name: "erer", age: 18)
        stu1.insertStudent()
        let stu2 = Student.init(name: "xiaoerer", age: 17)
        stu2.insertStudent()
    }


}

性能优化

代码如下：

 override func touchesBegan(touches: Set<UITouch>, withEvent event: UIEvent?) {
        
        // 开启事物
        SQLiteManager.shareInstance.beginTransaction()
        
        let start = CFAbsoluteTimeGetCurrent()
        
        for i in 0..<10000 {
            let stu = Student(name: "mm + \(i)", age: 100 + i)
            /*:
             每次只要执行SQL语句系统就会自动开启一个事物
             当SQL语句执行完毕系统就会自动提交事物
             但是, 只要我们自己手动开启了事物, 系统就不会再自动帮我们开启事物了
            只要我们自己提交了事物, 系统就不会再自动帮我们提交事物了
            */
            stu.insertStudent1()
//            stu.insertStudent2()
        }
        let end = CFAbsoluteTimeGetCurrent()
        
        // 提交事物
        SQLiteManager.shareInstance.commitTransaction()
        print("耗时\(end - start)秒")
    }