swift简易操作sqlite3

sqlite轻便小巧，底层采用C操作，跨平台在Linux和Android以及iOS甚至是嵌入式都通用，今天我们简易进行一些swift操作sqlite……

开始之前我们先简易的看看一些sqlite操作

接下来我们参看怎么操作sqlite3

1 创建和打开db

代码语言：javascript复制

open class SKSQLiteDatabase {
    public var handle : OpaquePointer?
    var dbFilePath: String
    var vFs: Int8 = 0
    var flags:Int32
    init(_ dbName: String, flags:Int32 = SQLITE_CREATE_TABLE | SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, extension ext: String = ".sqlite", vFs:   Int8 = 0) {
        self.flags = flags
        self.dbFilePath = dbHome   "/"   dbName   ext
        self.vFs = vFs
    }
    open func openConnection()->Bool{
       let ret = sqlite3_open_v2(self.dbFilePath, &handle, flags, &vFs)
        return ret == SQLITE_OK
    }
}

2 查询column和对应的数据类型

代码语言：javascript复制

open func dbName()-> String{
        lock.lock()
        let db =  sqlite3_column_database_name(stmt, 0)
        lock.unlock()
        return String(cString: db!)
    }
    open func columnCount()->Int32{
      return  sqlite3_column_count(stmt)
    }
    open func tableName()->String{
        lock.lock()
        let table = sqlite3_column_table_name(stmt, 0)
        lock.unlock()
        return String(cString: table!)
    }
    open func sqliteDataType( `for` index:Int32)->Int32{
      return  sqlite3_column_type(stmt, index)
    }

3 进行查询（多列多行查询）

代码语言：javascript复制

func execSql(_ query: String){
        var tail:UnsafePointer<Int8>?
        let ret =  sqlite3_prepare_v2(handle, query, -1, &stmt, &tail)
        
        var next:Bool = true
        var rowSet = [SKSQLiteRow]()
        while next {
            let row = SKSQLiteRow()
            for index in 0 ..< columnCount() {
                
                 let table = sqlite3_column_table_name(stmt, index)
                let column = sqlite3_column_name(stmt, index)
             let  dataType =   sqliteDataType(for: index)
                var value: Optional<SKSQLiteDataType> = NSNull()
                switch dataType {
                case SQLITE_INTEGER:
                  value =  sqlite3_column_int(stmt, index)
                case SQLITE_TEXT:
                    let tValue : UnsafePointer<UInt8> =  sqlite3_column_text(stmt, index)
                    value = String(cString: tValue)
                case SQLITE_BLOB:
                    let tValue = sqlite3_column_blob(stmt, index)
                    let pointer = tValue?.assumingMemoryBound(to: Int8.self)
                    value = String(cString: pointer!)
                default:
                    ""
                }
                let data = SKSQLiteColumnData.init(table: toStr(p: table!) , columnName: toStr(p: column!), dataType: dataType, value: value)
                row.add(data)
            }
            rowSet.append(row)
            next = sqlite3_step(stmt) == SQLITE_OK
        }
    }

4 表中对应的每列数据信息

代码语言：javascript复制

class SKSQLiteColumnData{
    var table: String
    var columnName: String
    var dataType: Int32
    var value: Optional<SKSQLiteDataType>
    init(table:String, columnName: String, dataType:Int32, value:Optional<SKSQLiteDataType>){
        self.table = table
        self.columnName = columnName
        self.dataType = dataType
        self.value = value
    }
}

5 对数据按行处理

代码语言：javascript复制

class SKSQLiteRow : IteratorProtocol{
    typealias Element = SKSQLiteColumnData
    
    var rows:[SKSQLiteColumnData] = [SKSQLiteColumnData]()
    init(_ rows:[SKSQLiteColumnData] = [SKSQLiteColumnData]()) {
        self.rows = rows
    }
    func add(_ data:SKSQLiteColumnData){
        rows.append(data)
    }
    func next() -> SKSQLiteColumnData? {
        return rows.removeFirst()
    }
}

6 对sqlite的数据类型进行统一整理

代码语言：javascript复制

protocol SKSQLiteDataType {}
extension Int32: SKSQLiteDataType{}
extension String: SKSQLiteDataType{}
extension NSNull: SKSQLiteDataType{}

sqlite android 数据库 sql

0 人点赞