SQLite的使用—— Swift代码

一、添加”libsqlite3.dylib”库文件

写一个头文件SQLite_Bridge.h

将SQLite连接

二、代码结构

三、代码实现

1.AppDelegate.swift

import UIKit

@UIApplicationMain
class AppDelegate: UIResponder, UIApplicationDelegate {

    var window: UIWindow?


    func application(application: UIApplication, didFinishLaunchingWithOptions launchOptions: [NSObject: AnyObject]?) -> Bool {

        let flag = SQLiteManager.shareInstance().openDB()
        if flag {
            print("打开数据库成功")
        }

        return true
    }
}

2.ViewController.swift

import UIKit

/*
0.封装一个工具类,管理SQLite的数据库(单例)
1.创建数据库(文件)
2.创建表,用于存储数据
3.基本操作(增删改)
4.查询数据
*/

class ViewController: UIViewController {

    override func viewDidLoad() {
        super.viewDidLoad()
    }

    override func touchesBegan(touches: Set<UITouch>, withEvent event: UIEvent?) {

        // 1.开启事务
        SQLiteManager.shareInstance().beginTransaction()

        let time = CFAbsoluteTimeGetCurrent()

        // 2.执行操作
        for _ in 0..<10000 {
            let name = "ls\(arc4random_uniform(20))"
            let age = Int(arc4random_uniform(10) + 15)
            let stu = Student(age: age, name: name)
            stu.insertStudent()

//            if i == 50 {
//                // 回滚事务
//                SQLiteManager.shareInstance().rollBackTransaction()
//                break
//            }
        }

        let end = CFAbsoluteTimeGetCurrent();

        print(end - time)

        // 3.提交事务
        SQLiteManager.shareInstance().commitTransaction()

        // deleteData()
        // updateData()

//        let array = Student.loadData()
//        print(array);
    }

    func deleteData() {
        // 1.封装删除的sql
        let deleteSQL = "DELETE FROM t_student WHERE name = 'ls11';"

        // 2.执行sql
        if SQLiteManager.shareInstance().execSQL(deleteSQL) {
            print("删除成功")
        }
    }

    func updateData() {
        // 1.封装更新的sql
        let updateSQL = "UPDATE t_student SET name = 'lmj' WHERE name = 'ls1';"

        // 2.执行sql
        if SQLiteManager.shareInstance().execSQL(updateSQL) {
            print("更新成功")
        }
    }
}

3.SQLiteManager.swift

import UIKit

class SQLiteManager: NSObject {

    // 定义单例的属性,使用let定义的属性本身就是线程安全
    static let instance : SQLiteManager = SQLiteManager()

    // 对外提供一个接口,返回单例实例
    class func shareInstance() -> SQLiteManager {
        return instance
    }

    // MARK:- 创建数据库和表的方法
    var db : COpaquePointer = nil

    func openDB() -> Bool {
        // 1.获取数据库存放的路径
        var filePath = NSSearchPathForDirectoriesInDomains(NSSearchPathDirectory.DocumentDirectory, NSSearchPathDomainMask.UserDomainMask, true).first;
        filePath = (filePath! as NSString).stringByAppendingPathComponent("my.sqlite")
        let cFilePath = (filePath?.cStringUsingEncoding(NSUTF8StringEncoding))!

        print(filePath)

        // 2.打开数据库
        if sqlite3_open(cFilePath, &db) != SQLITE_OK {
            print("打开数据库失败");
            return false
        }

        // 3.创建表
        return createTable()
    }

    func createTable() -> Bool {
        // 1.封装创建表的SQL语句
        let createTableSQL = "CREATE TABLE IF NOT EXISTS 't_student' ('id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,'name' TEXT,'age' INTEGER);"

        // 2.执行SQL语句
        return execSQL(createTableSQL)
    }

    func execSQL(sql : String) -> Bool {
        // 1.将语句转成c语言字符串
        let cSQL = (sql.cStringUsingEncoding(NSUTF8StringEncoding))!

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

    // MARK:- 查询语句
    func querySQL(querySQL : String) -> [[String : AnyObject]]? {
        // 0.游标对象
        var stmt : COpaquePointer = nil

        // 1.将查询语句转成C语言的字符串
        let cQuerySQL = (querySQL.cStringUsingEncoding(NSUTF8StringEncoding))!

        // 2.准备工作
        if sqlite3_prepare_v2(db, cQuerySQL, -1, &stmt, nil) != SQLITE_OK {
            print("没有准备成功")
            return nil
        }

        // 3.查询数据
        var tempArray = [[String : AnyObject]]()
        let count = sqlite3_column_count(stmt)
        while sqlite3_step(stmt) == SQLITE_ROW {
            var dict = [String : AnyObject]()
            for i in 0..<count {
                // 1.取出key
                let cValue = UnsafePointer<Int8>(sqlite3_column_text(stmt, i))
                let value = String(CString: cValue, encoding: NSUTF8StringEncoding)!

                // 2.取出值
                let cKey = sqlite3_column_name(stmt, i)
                let key = String(CString: cKey, encoding: NSUTF8StringEncoding)!

                // 3.将键值对添加到字典中
                dict[key] = value
            }

            tempArray.append(dict)
        }

        return tempArray
    }

    // MARK:- 事务的封装
    func beginTransaction() {
        execSQL("BEGIN TRANSACTION;")
    }

    func rollBackTransaction() {
        execSQL("ROLLBACK TRANSACTION;")
    }

    func commitTransaction() {
        execSQL("COMMIT TRANSACTION;")
    }
}

4.Student.swift

import Foundation

class Student: NSObject {
    var name : String?
    var age : Int = 0

    init(age : Int, name : String) {
        self.name = name
        self.age = age
    }

    init(dict : [String : AnyObject]) {
        super.init()
        self.setValuesForKeysWithDictionary(dict)
    }

    func insertStudent() {
        // 1.拼接插入的sql语句
        let insertSQL = "INSERT INTO t_student (name, age) VALUES ('\(name!)', \(age));"

        // 2.执行sql
        if SQLiteManager.shareInstance().execSQL(insertSQL) {
        }
    }

    class func loadData() -> [Student]? {
        // 1.定义查询语句
        let querySQL = "SELECT name, age FROM t_student WHERE age >= 20 ORDER BY age;"

        // 2.执行查询语句
        let dictArray = SQLiteManager.shareInstance().querySQL(querySQL)

        // 3.遍历字典中所有的数据,转成模型对象
        var tempArray = [Student]()
        if let tempDictArray = dictArray {
            for dict in tempDictArray {
                tempArray.append(Student(dict: dict))
            }
        }

        return tempArray
    }
}

5.SQLite_Bridge.h

#import "SQLite3.h"