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 Foundation
class 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 UIKit
class 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 UIKit
class 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()
}
}
效果图
1243
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
