SQLite3在swift3.0性能测试
来源：互联网 发布：淘宝流失金额多少正常 编辑：程序博客网 时间：2024/06/09 14:59
https://github.com/targetcloud/SQLite3DemoSwift3
结论：用BATCH性能最好（事务+预处理）,swift3.0的写法有许多地方需要注意，本文也包括了SQLite3基础的CRUD多种写法供读者参考
1、在工具类中批量插入时发现一个BUG，即Float与Double都需要判断，不然会插入空值
fileprivate func insertBind(_ stmt: OpaquePointer, values: [Any]) -> Bool { var index: Int32 = 1 for obj in values{ if obj is Int{ sqlite3_bind_int(stmt, index, Int32(obj as! Int)) } else if obj is Double{ sqlite3_bind_double(stmt, index, obj as! Double) } else if obj is Float{ sqlite3_bind_double(stmt, index, Double(obj as! Float))//MARK:- 重点！Float要判断 }else if obj is String{ sqlite3_bind_text(stmt, index, obj as! String, -1, SQLITE_TRANSIENT) }else { continue } index += 1 } return sqlite3_step(stmt) == SQLITE_DONE }2、取SQLITE_TEXT时的写法在swift3中如下
let value = String(cString:sqlite3_column_text(stmt, i)!)//MARK:-最新写法 let valueStr = String(cString: value, encoding: String.Encoding.utf8)
func queryAllStmt(_ sql :String) ->[[String:Any]] { var stmt: OpaquePointer? = nil var queryDataArrM = [[String:Any]]() if sqlite3_prepare_v2(appdb, sql, -1, &stmt, nil) != SQLITE_OK { print("prepare fail") return queryDataArrM } while sqlite3_step(stmt) == SQLITE_ROW { let count = sqlite3_column_count(stmt) var dict = [String:Any]() for i in 0..<count { let columnName = String(cString: sqlite3_column_name(stmt, i), encoding: String.Encoding.utf8) let type = sqlite3_column_type(stmt, i) if type == SQLITE_INTEGER { let value = sqlite3_column_int(stmt, i) dict[columnName!] = value print(columnName,value) } if type == SQLITE_FLOAT { let value = sqlite3_column_double(stmt, i) dict[columnName!] = value print(columnName,value) } if type == SQLITE_TEXT { let value = String(cString:sqlite3_column_text(stmt, i)!)//MARK:-最新写法 let valueStr = String(cString: value, encoding: String.Encoding.utf8) dict[columnName!] = valueStr print(columnName,valueStr) } } queryDataArrM.append(dict) } sqlite3_finalize(stmt) return queryDataArrM }3、批量INSERT时传入的是二维数组，定义与调用写法如下
定义
SQLite3Tool.swift
//批量INSERT func insertBatch(_ tableName: String, columnNameArray: [String], valuesBlock: () -> Array<Array<Any>>){ guard let stmt: OpaquePointer = getPrepareStmt(tableName, columnNameArray: columnNameArray) else { return} beginTransaction() for array in valuesBlock(){ insertBind(stmt, values: array) resetStmt(stmt) } commitTransaction() releaseStmt(stmt) }调用
User.swift
class func insertBatch(_ valuesBlock: @escaping () -> (Array<Array<Any>>)){ SQLite3Tool.shareInstance.insertBatch("t_user", columnNameArray: ["name", "score","age"], valuesBlock: { () -> Array<Array<Any>> in return valuesBlock() }) }调用
ViewController.swift
//时间测试4 @IBAction func timeTest4(){ let beginTime = CFAbsoluteTimeGetCurrent() let user = User(name: "时间测试4", age: 44, score: 44.43) User.insertBatch({() -> (Array<Array<Any>>) in var array = [[Any]]()//var array = Array<Array<Any>>() //MARK:- 重点！性能最好 for _ in 0...9999{ array.append([user.name, user.score,user.age]) } return array }) let endTime = CFAbsoluteTimeGetCurrent() timeLabel.text = "开事务+Batch\(endTime - beginTime)" }
附完整版的工具类
//// SQLite3Tool.swift// SQLite3Demo//// Created by targetcloud on 2016/12/7.// Copyright © 2016年 targetcloud. All rights reserved.//import Foundationclass SQLite3Tool: NSObject { static let shareInstance = SQLite3Tool() var appdb: OpaquePointer? = nil let SQLITE_TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self) override init() { super.init() // let path = "/Users/targetcloud/Desktop/appdb.sqlite"//实际中用下面的path let docDir: String! = NSSearchPathForDirectoriesInDomains(FileManager.SearchPathDirectory.documentDirectory, FileManager.SearchPathDomainMask.userDomainMask, true).first // let path = docDir + "/appdb.sqlite"//建议下面写法 // print(path) let DBPath = (docDir! as NSString).appendingPathComponent("appdb.sqlite") let path = DBPath.cString(using: String.Encoding.utf8) print(DBPath) /* /Users/targetcloud/Library/Developer/CoreSimulator/Devices/A2BB310A-7C11-4A30-B1DE-B3488B7D87D7/data/Containers/Data/Application/0E8D813A-BD6D-4E4F-B500-27C3B5E896DA/Documents/appdb.sqlite */ if sqlite3_open(path, &appdb) == SQLITE_OK { dropTable("t_user") createTable("create table if not exists t_user(id integer primary key autoincrement, name text not null, age integer, score real default 60, money real default 100 )") }else { print("open fail") } } func queryAllStmt(_ sql :String) ->[[String:Any]] { var stmt: OpaquePointer? = nil var queryDataArrM = [[String:Any]]() if sqlite3_prepare_v2(appdb, sql, -1, &stmt, nil) != SQLITE_OK { print("prepare fail") return queryDataArrM } while sqlite3_step(stmt) == SQLITE_ROW { let count = sqlite3_column_count(stmt) var dict = [String:Any]() for i in 0..<count { let columnName = String(cString: sqlite3_column_name(stmt, i), encoding: String.Encoding.utf8) let type = sqlite3_column_type(stmt, i) if type == SQLITE_INTEGER { let value = sqlite3_column_int(stmt, i) dict[columnName!] = value print(columnName,value) } if type == SQLITE_FLOAT { let value = sqlite3_column_double(stmt, i) dict[columnName!] = value print(columnName,value) } if type == SQLITE_TEXT { let value = String(cString:sqlite3_column_text(stmt, i)!)//MARK:-最新写法 let valueStr = String(cString: value, encoding: String.Encoding.utf8) dict[columnName!] = valueStr print(columnName,valueStr) } } queryDataArrM.append(dict) } sqlite3_finalize(stmt) return queryDataArrM } func queryAll(_ sql :String) { //参数: 一个打开的数据库、需要执行的SQL语句、查询结果回调(执行0次或多次)、回调函数的第一个值、错误信息 let result = sqlite3_exec(appdb, sql, { ( firstValue, columnCount, values , columnNames ) -> Int32 in // 参数: 外层的第4个参数的值、列的个数、结果值的数组所有列的名称数组。返回值: 0代表继续执行一直到结束, 1代表执行一次 let count = Int(columnCount) for i in 0..<count { let column = columnNames?[i] let columnNameStr = String(cString: column!, encoding: String.Encoding.utf8)//(columnNames?[i]!)! let value = values?[i] let valueStr = String(cString: value!, encoding: String.Encoding.utf8)//(values?[i]!)! print(columnNameStr! + "=" + valueStr!) } return 0//0代表继续执行一直到结束, 1代表执行一次 }, nil, nil) if result == SQLITE_OK { print("all query ok") }else { print("all query fail") } } //建表 fileprivate func createTable(_ sql: String) ->Bool { return execSQL(sql) } //删表 fileprivate func dropTable(_ tableName: String) ->Bool { let sql = "drop table if exists "+tableName return execSQL(sql) } //普通更新 func updateRecord(_ table: String, setStr: String, condition: String?) -> Bool { let whereCondition = (condition == nil) ? ("") : ("where \(condition!)") let sql = "update \(table) set \(setStr) \(whereCondition)" return execSQL(sql) } //普通INSERT func insert(_ tableName: String, columnNameArray: [String], values: CVarArg...) -> Bool { return insert(tableName, columnNameArray: columnNameArray, valueArray:values) } //普通INSERT func insert(_ tableName: String, columnNameArray: [String], valueArray: [Any]) -> Bool { if columnNameArray.count != valueArray.count{ return false } let tempColumnNameArray = columnNameArray as NSArray let columnNames = tempColumnNameArray.componentsJoined(by: ",") let tempValueArray = valueArray as NSArray let values = tempValueArray.componentsJoined(by: "\',\'") let sql = "INSERT INTO \(tableName)(\(columnNames)) values (\'\(values)\')" return execSQL(sql) } //批量INSERT func insertBatch(_ tableName: String, columnNameArray: [String], valuesBlock: () -> Array<Array<Any>>){ guard let stmt: OpaquePointer = getPrepareStmt(tableName, columnNameArray: columnNameArray) else { return} beginTransaction() for array in valuesBlock(){ insertBind(stmt, values: array) resetStmt(stmt) } commitTransaction() releaseStmt(stmt) } //step Stmt fileprivate func insertBind(_ stmt: OpaquePointer, values: [Any]) -> Bool { var index: Int32 = 1 for obj in values{ if obj is Int{ sqlite3_bind_int(stmt, index, Int32(obj as! Int)) } else if obj is Double{ sqlite3_bind_double(stmt, index, obj as! Double) } else if obj is Float{ sqlite3_bind_double(stmt, index, Double(obj as! Float))//MARK:- 重点！Float要分开判断 }else if obj is String{ sqlite3_bind_text(stmt, index, obj as! String, -1, SQLITE_TRANSIENT) }else { continue } index += 1 } return sqlite3_step(stmt) == SQLITE_DONE } //得到INSERT Stmt fileprivate func getPrepareStmt(_ tableName: String, columnNameArray: [String]) -> OpaquePointer? { let tempColumnNameArray = columnNameArray as NSArray let columnNames = tempColumnNameArray.componentsJoined(by: ",") var tempValues: Array = [String]() for _ in 0 ..< columnNameArray.count{ tempValues.append("?") } let valuesStr = (tempValues as NSArray).componentsJoined(by: ",") let prepareSql = "INSERT INTO \(tableName)(\(columnNames)) values (\(valuesStr))" var stmt: OpaquePointer? = nil if sqlite3_prepare_v2(appdb, prepareSql, -1, &stmt, nil) != SQLITE_OK{ print("prepare fail") sqlite3_finalize(stmt) return nil } return stmt! } //重置Stmt fileprivate func resetStmt(_ stmt: OpaquePointer) -> Bool { return sqlite3_reset(stmt) == SQLITE_OK } //释放Stmt fileprivate func releaseStmt(_ stmt: OpaquePointer) { sqlite3_finalize(stmt) } func execSqls(_ sqlarr : [String]) -> Bool { for item in sqlarr { if execSQL(item) == false { return false } } return true } func execSQL(_ sql : String) -> Bool { let errmsg : UnsafeMutablePointer<UnsafeMutablePointer<Int8>?>? = nil if sqlite3_exec(appdb, sql, nil, nil, errmsg) == SQLITE_OK { return true }else{ print("error \(errmsg)") return false } } //开启事务 func beginTransaction() -> Bool{ let sql = "begin transaction" return execSQL(sql) } //提交事务 func commitTransaction() -> Bool{ let sql = "commit transaction" return execSQL(sql) } //回滚事务 func rollbackTransaction() -> Bool{ let sql = "rollback transaction" return execSQL(sql) }}Model类
//// User.swift// SQLite3Demo//// Created by targetcloud on 2016/12/7.// Copyright © 2016年 targetcloud. All rights reserved.//import UIKitclass User: NSObject { var name: String = "" var age: Int = 0 var score: Float = 0.0 var money: Float = 0.0 init(name: String, age: Int, score: Float,money: Float = 100) { super.init() self.name = name self.age = age self.score = score self.money = money } init(dict : [String : Any]) { super.init() self.setValuesForKeys(dict) } class func allUserFromDB() -> [User]? { let sql = "SELECT name,age,score,money FROM t_User " let allUserDictArr = SQLite3Tool.shareInstance.queryAllStmt(sql) var userModelArrM = [User]() for dict in allUserDictArr { userModelArrM.append(User(dict: dict)) } return userModelArrM } override func setValue(_ value: Any?, forUndefinedKey key: String) { } //辅助查询测试1 class func queryAll() { SQLite3Tool.shareInstance.queryAll("select * from t_user") } //辅助查询测试2 class func queryAllStmt() { SQLite3Tool.shareInstance.queryAllStmt("select * from t_user") } //辅助事务测试 class func update(_ sql: String) -> Bool { return SQLite3Tool.shareInstance.execSQL(sql) } //辅助时间测试3 func bindInsertUserWithTransaction(_ times:Int) { let sql = "insert into t_user(name, age, score) values (?, ?, ?)" let db = SQLite3Tool.shareInstance.appdb var stmt: OpaquePointer? = nil if sqlite3_prepare_v2(db, sql, -1, &stmt, nil) != SQLITE_OK { print("prepare fail") return } SQLite3Tool.shareInstance.beginTransaction() for i in 0..<times { sqlite3_bind_int(stmt, 2, Int32(i))//索引从1开始,age使用i sqlite3_bind_double(stmt, 3, Double(self.score)) // let SQLITE_TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self) sqlite3_bind_text(stmt, 1, self.name, -1, SQLite3Tool.shareInstance.SQLITE_TRANSIENT) if sqlite3_step(stmt) == SQLITE_DONE { print("step success") } sqlite3_reset(stmt) } SQLite3Tool.shareInstance.commitTransaction() sqlite3_finalize(stmt) } //辅助时间测试2 func bindInsertUser() { let sql = "insert into t_user(name, age, score) values (?, ?, ?)" let db = SQLite3Tool.shareInstance.appdb var stmt: OpaquePointer? = nil if sqlite3_prepare_v2(db, sql, -1, &stmt, nil) != SQLITE_OK { print("prepare fail") return } sqlite3_bind_int(stmt, 2, Int32(self.age)) sqlite3_bind_double(stmt, 3, Double(self.score)) // let SQLITE_TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self) sqlite3_bind_text(stmt, 1, self.name, -1, SQLite3Tool.shareInstance.SQLITE_TRANSIENT) if sqlite3_step(stmt) == SQLITE_DONE { print("step success") } sqlite3_reset(stmt) sqlite3_finalize(stmt) } //辅助时间测试1 func insertUser() { let sql = "insert into t_user(name, age, score) values ('\(self.name)', \(self.age), \(self.score))" if SQLite3Tool.shareInstance.execSQL(sql) { print("insert success") } } class func deleteUser(_ name: String) { let sql = "delete from t_user where name = '\(name)'" if SQLite3Tool.shareInstance.execSQL(sql) { print("delete success") } } //插入方式2 class func insertBatch(_ valuesBlock: @escaping () -> (Array<Array<Any>>)){ SQLite3Tool.shareInstance.insertBatch("t_user", columnNameArray: ["name", "score","age"], valuesBlock: { () -> Array<Array<Any>> in return valuesBlock() }) } //插入方式1 func insert(){ if SQLite3Tool.shareInstance.insert("t_user", columnNameArray: ["name", "score","age"], valueArray: [self.name, self.score,self.age]){ print("insert success") } } func insert2(){ if SQLite3Tool.shareInstance.insert("t_user", columnNameArray: ["name", "score","age"], values: self.name, self.score,self.age){ print("insert2 success") } } //更新方式1 对象方法 func updateUser(_ newUser: User) { let sql = "update t_user set name = '\(newUser.name)', age = \(newUser.age), score = \(newUser.score) where name = '\(self.name)'" if SQLite3Tool.shareInstance.execSQL(sql) { print("update success") } } //更新方式2 类方法 class func updateRecord(_ setStr: String, condition: String)-> Bool { return SQLite3Tool.shareInstance.updateRecord("t_user", setStr: setStr, condition: condition) } }测试类
//// ViewController.swift// SQLite3Demo//// Created by targetcloud on 2016/12/7.// Copyright © 2016年 targetcloud. All rights reserved.//import UIKitclass ViewController: UIViewController { @IBOutlet weak var timeLabel: UILabel! override func viewDidLoad() { super.viewDidLoad() // Do any additional setup after loading the view, typically from a nib. } override func touchesBegan(_ touches: Set<UITouch>, with event: UIEvent?) { let userArr = User.allUserFromDB() print(userArr) timeLabel.text = "你查询到了 \(userArr?.count) 条记录" } //事务测试1 @IBAction func transaction(){ SQLite3Tool.shareInstance.beginTransaction() User.deleteUser("刘能") User.deleteUser("赵四") let user1 = User(name: "刘能", age:22, score: 100) let user2 = User(name: "赵四",age:33, score: 100) user1.insert() user2.insert2() let result1 = User.update("update t_user set money = money - 10 where name = '刘能'") let result2 = User.update("update t_user set money = money + 10 where name = '赵四'") if result1 && result2 { SQLite3Tool.shareInstance.commitTransaction() }else { SQLite3Tool.shareInstance.rollbackTransaction() } } //事务测试2 @IBAction func transaction2(){ SQLite3Tool.shareInstance.beginTransaction() User.deleteUser("铁娃") User.deleteUser("牛娃") let user1 = User(name: "铁娃", age:8, score: 60) let user2 = User(name: "牛娃",age:9, score: 50) user1.insertUser() user2.insert() let result1 = User.updateRecord("score = score - 10", condition: "name = '铁娃'") let result2 = User.updateRecord("score1 = score + 10", condition: "name = '牛娃'")//注意这里会失败，事务回滚了 if result1 && result2{ SQLite3Tool.shareInstance.commitTransaction() }else{ SQLite3Tool.shareInstance.rollbackTransaction() } } //时间测试1 没开事务+sqlite3_exec @IBAction func timeTest(){ let beginTime = CFAbsoluteTimeGetCurrent() let user = User(name: "时间测试1", age: 11, score: 11) for _ in 0..<10000 { user.insertUser() } let endTime = CFAbsoluteTimeGetCurrent() timeLabel.text = "没开事务+sqlite3_exec>\(endTime - beginTime)" } //时间测试2 没开事务+Stmt @IBAction func timeTest2(){ let beginTime = CFAbsoluteTimeGetCurrent() let user = User(name: "时间测试2", age: 22, score: 22) for _ in 0..<10000 { user.bindInsertUser() } let endTime = CFAbsoluteTimeGetCurrent() timeLabel.text = "没开事务+Stmt>\(endTime - beginTime)" } //时间测试3 开事务 @IBAction func timeTest3(){ let beginTime = CFAbsoluteTimeGetCurrent() let user = User(name: "时间测试3", age: 33, score: 33) user.bindInsertUserWithTransaction(10000) let endTime = CFAbsoluteTimeGetCurrent() timeLabel.text = "开事务>\(endTime - beginTime)" } //时间测试4 @IBAction func timeTest4(){ let beginTime = CFAbsoluteTimeGetCurrent() let user = User(name: "时间测试4", age: 44, score: 44.43) User.insertBatch({() -> (Array<Array<Any>>) in var array = [[Any]]()//var array = Array<Array<Any>>() //MARK:- 重点！性能最好 for _ in 0...9999{ array.append([user.name, user.score,user.age]) } return array }) let endTime = CFAbsoluteTimeGetCurrent() timeLabel.text = "开事务+Batch\(endTime - beginTime)" } //查询测试1 @IBAction func allQuery(){ User.queryAll() } //查询测试2 @IBAction func stmtQuery(){ User.queryAllStmt() }}效果图
0 0
- SQLite3在swift3.0性能测试
- swift3.0：sqlite3的使用
- Sqlite3性能测试
- 在arm上测试sqlite3
- linux下sqlite3插入性能测试
- LevelDB、TreeDB、SQLite3性能对比测试
- Sqlite3常用的插入方法及性能测试
- Sqlite3常用的插入方法及性能测试
- Sqlite3常用的插入方法及性能测试
- Sqlite3 和Sqlitedbms数据库环境搭建与性能测试
- 用Swift3和SQLite3实现课程表
- Swift3.0
- swift3.0
- Swift3.0
- SQLite3性能优化
- SQLite3性能优化
- SQLite3性能优化
- SQLite3性能优化
- 试着用宏拼接做stm32管脚电平控制
- Codeforces 741A Arpa's loud Owf and Mehrdad's evil plan(思维)
- lib dll
- 7. Reverse Integer
- CFF_201612-3_炉石传说
- SQLite3在swift3.0性能测试
- 第十五章（1）
- CFF_201612-3_最大波动
- CFF_201612-4_交通规划
- VR--虚拟现实是否会颠覆整个航空行业？
- CFF_201604-2_俄罗斯方块
- CFF_201604-1_折点计数
- 搜索引擎可能已经可以识别伪劣药物
- css优先级问题
