swift 简易操作sqlite3 之 通用查询

上篇文章我们写了关于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参数名

有了上面的共识我们接着进行我们通用方法操作的过程，根据需要我们一共提供两种建议的操作

 func exec(query q:String, values: SQLiteDataType ... ) -> [SQLiteRow]? 

我们先来看看怎么使用：

exec(query: "select * from \(t) where age>:age or name =:name",                    values: Int32(1), "XX")

上述中我们实例中看到:age :name 是提供给SQLite进行prepare的形参，Int32(1), "xx"是提供的数据条件（需要注意的是形参和数据条件的顺序要一一对应）。

有了大概的认识之后我们看看内部是如何实现的

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

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进行数据绑定。

接下来我们看看另外一个通用的方法…

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……

两种通用的方法我们拿到了，接下来我们需要的就是如何拿到查询出来的数据啦

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操作库到时候希望大家多多支持…