swift：sqlite3的使用

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了swift：sqlite3的使用，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含6878字，纯文字阅读大概需要10分钟。

内容图文

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

 

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

  

 

Person.swift

//  Person.swift
//  swiftDemo
//
//  Created by 夏远全 on 2017/2/20.
//  Copyright ? 2017年 夏远全. All rights reserved.
//

import UIKit

class 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 UIKit

class 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 = "zhangsan@163.com"
        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()
    }
}

 

swift：sqlite3的使用

标签：prepare   update   安装   document   nil   for   key   关闭   file   

本文系统来源：http://www.cnblogs.com/XYQ-208910/p/6421840.html

内容总结

以上是互联网集市为您收集整理的swift：sqlite3的使用全部内容，希望文章能够帮你解决swift：sqlite3的使用所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---