上篇文章我们写了关于SQLite的简单操作和一些基本常识，对此陌生的童鞋可以参考之前的文章(swift简易操作sqlite3)，今天我们在此基础上进一步加工处理，写出通用查询操作方法
关于查询语句中的通用常识：
select * from t where age > :age
对于这条语句我们我们要有个共识：:age 是进行SQLite内部prepare中的一个需要绑定的参数名(内部定义的格式)。我们再来看几个例子
select * from (t) where age>:age or name =:name
其中 :age :name是后续SQLite进行prepare的bind参数名
有了上面的共识我们接着进行我们通用方法操作的过程，根据需要我们一共提供两种建议的操作
代码语言：javascript复制 func exec(query q:String, values: SQLiteDataType ... ) -> [SQLiteRow]? 我们先来看看怎么使用：
代码语言：javascript复制exec(query: "select * from (t) where age>:age or name =:name",
values: Int32(1), "XX")上述中我们实例中看到:age :name 是提供给SQLite进行prepare的形参，Int32(1), "xx"是提供的数据条件（需要注意的是形参和数据条件的顺序要一一对应）。
有了大概的认识之后我们看看内部是如何实现的
代码语言：javascript复制func exec(query q:String, values: SQLiteDataType ... ) -> [SQLiteRow]? {
var stmt: OpaquePointer?
var tail: UnsafePointer<Int8>?
let h = handle
var result = sqlite3_prepare(h, q, -1, &stmt, &tail)
let parmmeterCount = sqlite3_bind_parameter_count(stmt)
var tmpValues = [SQLiteDataType]()
for value in values{
tmpValues.append(value)
}
for index in 0 ..< parmmeterCount {
let bindedParameterName: UnsafePointer<Int8>! = sqlite3_bind_parameter_name(stmt, index 1)
let bindResult = bind(tmpValues[Int(index)], for: stmt!, at: index 1)
if bindResult == false{
break
}
}
let targets = fetchData(from: stmt)
sqlite3_finalize(stmt)
return targets
}内部逻辑十分清晰：
1 获取到SQL中占位参数的数量
2遍历形参和数据参数进行数据的bind
上述代码很简单，我们具体来看看内部的数据bind
代码语言：javascript复制extension SQLiteStatement{
func bind(_ value: SQLiteDataType, `for` stmt: OpaquePointer, at index:Int32) -> Bool{
var result = SQLITE_OK
switch value {
case is Int32:
result = sqlite3_bind_int(stmt, index, value as! Int32)
break
case is String:
result = sqlite3_bind_text(stmt, index, value as! String, -2, { (ret:UnsafeMutableRawPointer?) in
})
break
case is Double:
result = sqlite3_bind_double(stmt, index, value as! Double)
break
default:
return false
}
return result == SQLITE_OK
}
}数据bind思路很清闲，根据获取多应column的数据类型和index进行数据绑定。
接下来我们看看另外一个通用的方法…
代码语言：javascript复制func exec(query q: String, values:Dictionary<String, SQLiteDataType>) -> [SQLiteRow]?{
var stmt: OpaquePointer?
var tail: UnsafePointer<Int8>?
// select a,b,c from T t where
let h = handle
let result = sqlite3_prepare(h, q, -1, &stmt, &tail)
if result != SQLITE_OK {
return nil
}
let parmmeterCount = sqlite3_bind_parameter_count(stmt)
for index in 0 ..< parmmeterCount {
let bindedParameterName: UnsafePointer<Int8>! = sqlite3_bind_parameter_name(stmt, index 1)
let strBindedParameterName = String(cString: bindedParameterName)
let key = (strBindedParameterName as NSString).substring(from: 1)
let value = values[key]
let bindResult = bind(value as! SQLiteDataType, for: stmt!, at: index 1)
if bindResult == false{
break
}
}
return fetchData(from: stmt)
}思路与之前的方法大致相仿，不同的是需要将对应占位参数转换为Dictionary的key，然后根据key取出数据value，最后拿着 index value进行数据bind拿到我们需要的Statement……
两种通用的方法我们拿到了，接下来我们需要的就是如何拿到查询出来的数据啦
代码语言：javascript复制extension SQLiteStatement {
func fetchData( from stmt: OpaquePointer?)-> [SQLiteRow]?{
let result = sqlite3_step(stmt)
if (result == SQLITE_ROW || result == SQLITE_DONE || result == SQLITE_OK ) == false {
return nil
}
let colCount = sqlite3_column_count(stmt)
let rowCount = sqlite3_data_count(stmt)
var next = true
next = (next ) || (result == SQLITE_ROW)
var queryResult: Array<SQLiteRow> = Array<SQLiteRow>.init()
while next && (rowCount > 0 ) {
var rowDataItems:[SQLiteColumnData] = [SQLiteColumnData]()
for i in 0 ..< colCount {
let type = sqlite3_column_type(stmt, i)
var value: Optional<SQLiteDataType> = NSNull()
switch type{
case SQLITE_INTEGER:
value = sqlite3_column_int(stmt, i)
case SQLITE_TEXT:
let tmpValue = sqlite3_column_text(stmt, i)
value = String.init(cString: tmpValue!)
case SQLITE_NULL: break
case SQLITE_FLOAT:
value = sqlite3_column_double(stmt, i)
case SQLITE_BLOB:
let tmpValue = sqlite3_column_blob(stmt, i)
let point = tmpValue?.assumingMemoryBound(to: UInt8.self)
let text = String.init(cString: point!)
value = text
case SQLITE_ANY:
let tmpValue:OpaquePointer = sqlite3_column_value(stmt, i)
default: break
}
let name:UnsafePointer<Int8> =
sqlite3_column_name(stmt, i)
let table = sqlite3_column_table_name(stmt, i)!
let db = sqlite3_column_database_name(stmt, i)
let columnName = String.init(cString: name)
let tableName = String.init(cString: table)
_ = String.init(cString: db!)
let item: SQLiteColumnData = SQLiteColumnData(table: tableName, column: columnName, value: value, dataType: type)
rowDataItems.append(item)
}
let row = SQLiteRow.init(items: rowDataItems)
queryResult.append(row)
next = (sqlite3_step(stmt) == SQLITE_ROW )
}
return queryResult
}
}对于statement来说内部存储的是查询多行查询数据，我们需要按照step进行按行遍历取到数据即可。
好了，通用的查询方法就基本完成，后续接着出通用数据插入方法，慢慢的把基本的CURD写完，之后会结合起来撸一个简易的SQLite操作库到时候希望大家多多支持…
