sqlite系列(2)使用FMDB保存数据swift

1.创建一个数据库的工具类

import UIKit
import FMDB
class XBDataManger: NSObject {
    //单例
    static let shareInstance : XBDataManger = XBDataManger()
    //创建一个队列(线程安全的队列)
    var queue : FMDatabaseQueue?
    
    var db : FMDatabase?

     //打开数据库

    func openDB()
    {
        let path = NSSearchPathForDirectoriesInDomains(NSSearchPathDirectory.DocumentDirectory, NSSearchPathDomainMask.UserDomainMask, true).last!
        let filePath = (path as NSString).stringByAppendingPathComponent("status.db")
        
       //print(filePath)
        
        
        queue = FMDatabaseQueue(path: filePath)
        //创建表
        
        
        createTable()
    }
    private func createTable(){
        let sql = "CREATE TABLE 'T_status' ( 'status' text,'statusid' integer NOT NULL,'userid' integer,PRIMARY KEY('statusid'))"
        queue?.inDatabase({ (db) -> Void in
            //执行代码
            db.executeUpdate(sql, withArgumentsInArray: nil)
        })
        
    }
    
}

2.保存数据的接口

    func saveData(array : [[String : NSObject]])
    {
        let sql = "insert into T_status (statusId,status,userId) VALUES (?,?,?)"
        
        //遍历去取得数据
        for dic in array
        {
            let statusId = dic["idstr"]
            
            //把字典转换为字符串   先转换成data通过<span style="font-family: Arial, Helvetica, sans-serif;">NSJSONSerialization.dataWithJSONObject</span><span style="font-family: Arial, Helvetica, sans-serif;">来实现</span>
            let data = try! NSJSONSerialization.dataWithJSONObject(dic, options: NSJSONWritingOptions(rawValue: 0))
            let str = NSString(data: data, encoding: NSUTF8StringEncoding)
            
            
            //插入数据  支持事务
            XBDataManger.shareInstance.queue?.inTransaction({ (db, rollback) -> Void in
               if db.executeUpdate(sql, withArgumentsInArray: [statusId!,str!,XBUserViewModel.shareInstance.usermodel!.uid!])
               {
                print("插入成功!")
                }
               else {
                print("插入失败!")
                }
            })
            
        }
        
        
    }

3.加载数据库中数据

主要步骤  ： 拼接sql    执行sql    遍历结果集合   字典转模型

    func loadCacheStatus(since_id : Int,max_id : Int,finish : ([[String : NSObject]])->())
    {
        //根据sinceId he max_id 来进行 sql语句的拼接
        var sql = "SELECT statusId,status FROM T_status \n" +
        "WHERE userId = '\(XBUserViewModel.shareInstance.usermodel!.uid!)'\n"
        
        if(since_id > 0)
        {
            sql += "AND statusId > \(since_id) \n"
        }
        else if(max_id > 0)
        {
            sql += "AND statusId < \(since_id) \n"
        }
        
        sql += "ORDER BY statusId DESC LIMIT 20"
        
        //print(sql)
        //查询操作
        XBDataManger.shareInstance.queue?.inTransaction({ (db, _) -> Void in
            let result = db.executeQuery(sql, withArgumentsInArray: nil)
            var array = [[String : NSObject]]()
            while result.next()
            {
                let str = result.stringForColumn("status")
                //通过字符串来转换成字典  
                let data = str.dataUsingEncoding(NSUTF8StringEncoding)
                
                //转换成字典
                let dic = try! NSJSONSerialization.JSONObjectWithData(data!, options: NSJSONReadingOptions(rawValue: 0))
                array.append(dic as! [String : NSObject])
                
            }
            //完成回调
            finish(array)
        })
    }

4.  去网络加载数据接口

    func loadData(since_id : Int = 0,max_id : Int = 0,success : (count : Int)->(),fail : (error : NSError)->())
    {

<span style="white-space:pre">	</span>//先去数据库加载，然后再去网络中加载数据
         self.loadCacheStatus(since_id, max_id: max_id) { (dataArray) -> () in

<span style="white-space:pre">	</span>    
            if dataArray.count > 0
            {
                //遍历字典转模型 
                for dic in dataArray
                {
                   let statu = XBStatus(dic: dic)
                    self.StatusArray?.append(statu)
                }

                success(count: -1)
            }
            else
            {
                //去网络上加载数据
            }
        }
    }