swift3.0：sqlite3的使用

介绍

一、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类型的数据；

 

三、使用SQLiteStudio创建数据库，然后导出到桌面，再拖到项目中，最后通过代码拷贝到Documens下进行操作，并获取数据库路径

   

Person.swift

//  Person.swift//  swiftDemo////  Created by 夏远全 on 2017/2/20.//  Copyright © 2017年 夏远全. All rights reserved.//import UIKitclass Person: NSObject {    var name:String?    var password:String?    var email:String?    var age:Int?}

DatabaseOperation.swift

(1)打开数据库

    //不透明指针，对应C语言中的void *,这里指sqlit3指针    private var db:OpaquePointer? = nil        //初始化方法打开数据库    required init(dbPath:String) {        print("db path:" + dbPath)                //String类的路径，转换成cString        let cpath = dbPath.cString(using: .utf8)                //打开数据库        let error = sqlite3_open(cpath!,&db)                //数据库打开失败        if  error != SQLITE_OK {            sqlite3_close(db)        }    }

(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语句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 sql = "INSERT INTO UserTable(username,password,email,age) VALUES(?,?,?,?);";                //sql语句转换成cString类型        let cSql = sql.cString(using: .utf8)                //sqlite3_stmt指针        var stmt:OpaquePointer? = nil                //编译        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        }                //绑定参数        //第二个参数，索引从1开始        //最后一个参数为函数指针        sqlite3_bind_text(stmt, 1, user.name!.cString(using: .utf8), -1, nil)        sqlite3_bind_text(stmt, 2, user.password!.cString(using: .utf8), -1, nil)        sqlite3_bind_text(stmt, 3, user.email!.cString(using: .utf8), -1, nil)        sqlite3_bind_int(stmt, 4, Int32(Int(user.age!)))                //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    }

(5)查询数据

//查询数据    func readAllUsers() -> [Person] {                //声明一个Person对象数组（查询的信息会添加到数组中）        var userArr = [Person]()                //查询sql语句        let sql = "SELECT * FROM UserTable;";                //sqlite3_stmt指针        var stmt:OpaquePointer? = nil        let cSql = sql.cString(using: .utf8)                //编译        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)            }        }                //step        while (sqlite3_step(stmt) == SQLITE_ROW) {            let user = Person()                        //循环从数据库数据，添加到数组            let cName = UnsafePointer(sqlite3_column_text(stmt, 0))            let cPwd = UnsafePointer(sqlite3_column_text(stmt, 1))            let cEmail = UnsafePointer(sqlite3_column_text(stmt, 2))            let cAge = sqlite3_column_int(stmt, 3)                        user.name = String.init(cString: cName!)            user.password = String.init(cString: cPwd!)            user.email = String.init(cString: cEmail!)            user.age = Int(cAge)                        userArr += [user]        }                //finalize        sqlite3_finalize(stmt)                return userArr    }

(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    }

ViewController.swift测试

//  Created by 夏远全 on 2017/1/13.//  Copyright © 2017年 夏远全. All rights reserved.//import UIKitclass ViewController: UIViewController {    override func viewDidLoad() {        super.viewDidLoad()                //打开数据库        let path = DatabaseOperations.loadDBPath()        let dbOpearion = DatabaseOperations.init(dbPath:path)        print(path)                //添加一张表        let person:Person = Person()        person.name = "张三"        person.password = "123566"        person.email = "[e-mail]"        person.age = 30                //插入一条信息,通过Person对象来传值        let addBool = dbOpearion.addUser(user: person)        print(addBool)                //查询        let personArray:[Person] = dbOpearion.readAllUsers()        print("共搜索到:\(personArray.count) 条数据")                //更新        let updateBool = dbOpearion.updateUser(name: "张三", toName: "李四")        print(updateBool)                //删除        let deleteBool = dbOpearion.deleteUser(username: "李四")        print(deleteBool)                //关闭数据库        dbOpearion.closeDB()    }}